การเชื่อมต่อกับระบบฐานข้อมูลผ่าน RStudio ด้วย R Package "odbc"

การเชื่อมต่อกับระบบฐานข้อมูลผ่าน RStudio ด้วย R Package “odbc”

27 October 2022

ยอดเข้าชม 416

สำหรับผู้ใช้ภาษา R และโปรแกรม RStudio ในงานวิเคราะห์ข้อมูล บทความนี้จะขอแนะนำ R package ชื่อ odbc ที่เป็นหนึ่งใน package ที่นิยมในการเชื่อมต่อและ query ข้อมูลจากระบบฐานข้อมูล (database system) ที่เป็นที่นิยมและใช้กันอย่างแพร่หลาย อาทิเข่น SQL Server, Oracle, MySQL, PostgreSQL, SQLite, Hive, Impala เป็นต้น

ระบบฐานข้อมูลที่เชื่อมต่อได้ (ข้อมูล ณ เดือนมีนาคม 2565)
ที่มา: https://www.rstudio.com/products/drivers/

คำว่า ODBC ย่อมาจาก Open Database Connectivity ดังนั้น R package “odbc” จึงเป็น package ที่สามารถที่ใช้กับระบบฐานข้อมูลได้หลากหลายต่างจาก R package เช่น RPostgres RMariaDB, หรือ bigrquery ที่ใช้เฉพาะเจาะจงกับระบบฐานข้อมูลของตัวเองเท่านั้น จึงไม่เป็นที่น่าแปลกใจว่า R package “odbc” จึงเป็นที่นิยมสำหรับผู้ใช้ภาษา R

ภาพรวมของการเชื่อมต่อกับระบบฐานข้อมูล
ที่มา: https://db.rstudio.com/getting-started/overview/

สำหรับการใช้งาน R package “odbc” จะใช้ควบคู่กับ R package ตระกูล dplyr ที่ชื่อ “dbplyr” ซึ่งจะเป็น package หลักที่ใช้ในการปรับข้อมูลให้อยู่ในรูปแบบที่ต้องการ เพื่อทำการวิเคราะห์ข้อมูล โดยตัว R package “dbplyr” จะแปลงคำสั่งที่ใช้ในการปรับข้อมูลจากภาษา R เป็นภาษา SQL เพื่อส่งคำสั่งนั้นไปติดต่อกับระบบฐานข้อมูล ผู้ที่คุ้นเคยกับภาษา SQL สามารถที่จะเขียนคำสั่ง SQL โดยตรงผ่านโปรแกรม RStudio ได้อย่างสะดวก

เรามาเริ่มกันที่คำสั่งที่ต้องใช้ในการเรียกใช้ R package “odbc” และตัวอย่างคำสั่งที่ใช้ในการเชื่อมต่อกับระบบฐานข้อมูล พร้อมกับตัวอย่างคำสั่งอื่น ๆ ที่เกี่ยวข้องกับการเรียกข้อมูลในระบบฐานข้อมูล อาทิเช่น การลิสต์รายชื่อของชุดข้อมูลที่อยู่ในระบบฐานข้อมูล การลิสต์รายชื่อของตารางข้อมูลที่อยู่ในแต่ละชุดข้อมูล พร้อมกับตัวอย่างการ query ข้อมูลจากระบบฐานข้อมูล

1. การเรียกใช้ R package “odbc”

ในการเรียกใช้ R package “odbc” ผู้ใช้ต้องติดตั้ง R package “odbc” โดยใช้คำสั่ง install.packages() บนเครื่องก่อน แล้วจึงจะสามารถเรียกใช้ R package “odbc” นี้ได้โดยใช้คำสั่ง library()

# ติดตั้งหรือลง R package “odbc”:
install.packages("odbc")
# เรียกใช้ R package “odbc”:
library(odbc)

2. การเชื่อมต่อกับระบบฐานข้อมูล

ผู้ใช้สามารถเชื่อมต่อกับระบบฐานข้อมูลได้โดยใช้คำสั่ง dbConnect()

 # เชื่อมต่อกับระบบฐานข้อมูล (SQL Server):
con <- dbConnect(odbc(),
                    Driver   = "[your_driver_name]",
                    Server   = "[your_server_path]",
                    UID      = "Database_username"
                    PWD      = rstudioapi::askForPassword("Database_password"),
                    Port     = 1433)

โดยปกติเลข port ของระบบฐานข้อมูล จะใช้เป็นค่าดีฟอลต์ (Default) เช่น SQL Server จะใช้เลข 1433, Postgres จะใช้เลข 5432, Hive จะใช้เลข 10000 เป็นต้น ดังนั้น ผู้ใช้ต้องตรวจสอบเลข port ให้ถูกต้องเสมอ หากไม่สามารถ connect หรือเชื่อมต่อกับระบบฐานข้อมูลได้ ผู้ใช้อาจต้องตรวจสอบว่า ได้มีการติดตั้ง ODBC Data Source บนเครื่องคอมพิวเตอร์แล้วหรือยัง ผู้ใช้สามารถใช้คำสั่ง odbcListDataSources() เพื่อลิสต์ดูชื่อ ODBC Data Source ที่มีอยู่แล้วได้

3. ตัวอย่างคำสั่งอื่น ๆ ที่เกี่ยวข้องกับการเรียกข้อมูลในระบบฐานข้อมูล

ผู้ใช้สามารถใช้คำสั่ง เช่น odbcListObjects() เพื่อดูว่า ในระบบฐานชัอมูลมีชื่อชุดข้อมูล (schema) อะไรบ้างและเป็นตารางจริง (table) หรือเป็นเพียงแค่วิลล์ (view) เท่านั้น หรือใช้คำสั่ง odbcListObjects() และ odbcListColumns() เพื่อดูชื่อตารางที่อยู่ในแต่ละชุดข้อมูลและดูชื่อคอลัมน์ในตารางนั้น ๆ

# Top level objects
odbcListObjects(con)
# Tables in a schema
odbcListObjects(con, catalog="your_db", schema="dbo")
# Columns in a table
odbcListColumns(con, catalog="your_db ", schema="dbo", table="table_name_in_the_db")

4. ตัวอย่างการ query ข้อมูลจากระบบฐานข้อมูล

ผู้ใช้สามารถใช้คำสั่ง dbGetQuery() เพื่อใช้คำสั่ง SQL ในการจัดการกับข้อมูลบนระบบฐานข้อมูลได้ โดยระบุชื่อตารางใน FROM clause และต้องเป็นในรูปแบบ [database].[schema].[object] อาทิเช่น ชุดข้อมูลชื่อ “GBDi” ที่มี schema ชื่อ “dbo” มีตาราง 2 ตาราง ชื่อ “table_abc” และ ชื่อ “table_ xyz” และมีคอลัมน์ เช่น country, year, new_born และ population เป็นต้น

# นับจำนวน row จากตาราง “table_abc”
dbGetQuery(con, "SELECT count(*) FROM GBDi.dbo.table_abc")
# เลือกดูเฉพาะข้อมูล 50 row แรกจากตาราง “table_xyz”
dbGetQuery(con, "SELECT * FROM GBDi.dbo.table_xyz LIMIT 50")
# เลือกข้อมูลจากตาราง 2 ตารางเพื่อให้ได้ข้อมูลจำนวนเด็กเกิดใหม่ในประเทศอังกฤษตั้งแต่ปี 1995 และจำนวนประชากรในปีดังกล่าวจากอีกตารางหนึ่ง 
dbGetQuery(con, "SELECT table_abc.country, table_abc.year, table_abc.new_born, table_ xyz.population 
                FROM GBDi.dbo.table_ xyz, GBDi.dbo.table_abc
                WHERE table_abc.country = 'England' AND 
                table_abc.year > 1995 AND 
                table_abc.country = table_xyz.country AND 
                table_abc.year = table_xyz.year")

สำหรับคำสั่งอื่น ๆ เช่น การสร้างตารางบนระบบฐานข้อมูล ก็สามารถทำได้ผ่าน RStudio เช่นกัน โดยใช้คำสั่ง dbExecute() และ dbWriteTable() เพื่อสร้างตารางใหม่บนระบบฐานข้อมูล

ผู้เขียนหวังว่า บทความนี้จะช่วยให้นักวิเคราะห์ข้อมูลมีความมั่นใจมากขึ้น หากต้องทำการวิเคราะห์ข้อมูลจากระบบฐานข้อมูลต่าง ๆ ไม่ว่าจะเป็นข้อมูลแบบ Big Data หรือ Small Data โดยตัว R package “odbc” นี้ก็สามารถทำให้เราเชื่อมต่อ (Connect) ปรับข้อมูล (Transform) และดึงข้อมูลมาวิเคราะห์ (Analyze) บนเครื่องคอมพิวเตอร์ของเราได้อย่างสะดวกสบาย

หมายเหตุ: R package “odbc” กับ R package “RODBC” เป็น R package ที่แตกต่างกันแม้จะชื่อคล้ายกัน

ข้อมูลอ้างอิง:

https://cran.r-project.org/web/packages/odbc/odbc.pdf

https://db.rstudio.com/r-packages/odbc/

บทความโดย วรพิชญา ระเบียบโลก
ตรวจทานและปรับปรุงโดย ดวงใจ จิตคงชื่น