ในบทความนี้ เราจะมาดู 4 ขั้นตอนในการเชื่อมต่อและทำงานกับ database ในภาษา R ด้วย DBI package กัน:
- Get started
- Explore the database
- Query the database
- Close the connection
ถ้าพร้อมแล้ว ไปเริ่มกันเลย
- 💻 Step 1. Get Started
- 👀 Step 2. Explore the Database
- 🔍 Step 3. Query the Database
- 🤚 Step 4. Close the Connection
- 💪 Summary
- 😺 GitHub
- 📃 References
- ✅ R Book for Psychologists: หนังสือภาษา R สำหรับนักจิตวิทยา
💻 Step 1. Get Started
📦 DBI Package
DBI (Database Interface) เป็น package สำหรับเชื่อมต่อกับ database ซึ่งทำให้เราทำงานกับ database ในภาษา R ได้โดยตรง
ในบทความนี้ เรามาลองดูการใช้งาน DBI ผ่านการทำงานกับ Chinook SQLite database กัน (เราสามารถโหลด Chinook เพื่อลองทำตามได้จาก GitHub)
.
⬇️ Install & Connect
ในขั้นแรกของการใช้งาน เราจะติดตั้งและโหลด DBI พร้อมกับ package สำหรับ database ที่เราจะทำงานด้วย
อย่างในกรณีนี้ เราจะติดตั้งและโหลด RSQLite package เพราะเราจะทำงานกับ SQLite database
Note: ถ้าเราทำงานกับ database อื่น เราจะต้องติดตั้งและโหลด package อื่น เช่น:
- MySQL →
RMySQL - PostgresSQL →
RPostgresSQL - Oracle →
ROracle
ติดตั้ง packages:
# Install
install.packages("DBI")
install.packages("RSQLite")
โหลด packages:
# Load
library(DBI)
library(RSQLite)
หลังติดตั้งและโหลด packages แล้ว เราจะเชื่อมต่อกับ database ด้วย dbConnect() แบบนี้:
# Connect to database
con <- dbConnect(RSQLite::SQLite(),
"chinook.sqlite")
Note: ในกรณีที่ database ไม่ได้อยู่ใน working directory เราจะต้องใช้ absolute file path แทนชื่อไฟล์ เช่น:
# Connect to database
con <- dbConnect(RSQLite::SQLite(),
"C:/Users/YourUser/Documents/R_Projects/my_data/chinook.sqlite")
เท่านี้ เราก็พร้อมที่จะทำงานกับ database กันแล้ว
👀 Step 2. Explore the Database
เริ่มแรก เราจะสำรวจ database เพื่อทำความเข้าใจโครงสร้างข้อมูลกันก่อน
เรามี 2 functions ที่ช่วยเราได้ ได้แก่:
dbListTables(): ดูรายชื่อ tables ทั้งหมดใน databasedbGetQuety(): ดู columns ใน table ที่ต้องการ
.
1️⃣ dbListTable()
ตัวอย่าง:
# List tables in the database
dbListTables(con)
ผลลัพธ์:
[1] "Album" "Artist" "Customer" "Employee"
[5] "Genre" "Invoice" "InvoiceLine" "MediaType"
[9] "Playlist" "PlaylistTrack" "Track"
.
2️⃣ dbGetQuery()
ตัวอย่าง:
# List columns in a table
dbGetQuery(con,
"PRAGMA table_info(Artist)")
ผลลัพธ์:
cid name type notnull dflt_value pk
1 0 ArtistId INTEGER 1 NA 1
2 1 Name NVARCHAR(120) 0 NA 0
ในกรณีที่เราต้องการดู columns ในทุก table เราสามารถใช้ for loop ช่วยได้แบบนี้:
# Get the table list
tables <- dbListTables(con)
# Get all columns
for (table_name in tables) {
# Print the table name
message(paste0("\\n👉 Table: ", table_name))
# Get the columns
column_info <- dbGetQuery(con,
paste0("PRAGMA table_info(",
table_name,
")"))
# Print the columns
print(column_info)
}
ผลลัพธ์:
👉 Table: Album
cid name type notnull dflt_value pk
1 0 AlbumId INTEGER 1 NA 1
2 1 Title NVARCHAR(160) 1 NA 0
3 2 ArtistId INTEGER 1 NA 0
👉 Table: Artist
cid name type notnull dflt_value pk
1 0 ArtistId INTEGER 1 NA 1
2 1 Name NVARCHAR(120) 0 NA 0
👉 Table: Customer
cid name type notnull dflt_value pk
1 0 CustomerId INTEGER 1 NA 1
2 1 FirstName NVARCHAR(40) 1 NA 0
3 2 LastName NVARCHAR(20) 1 NA 0
4 3 Company NVARCHAR(80) 0 NA 0
5 4 Address NVARCHAR(70) 0 NA 0
6 5 City NVARCHAR(40) 0 NA 0
7 6 State NVARCHAR(40) 0 NA 0
8 7 Country NVARCHAR(40) 0 NA 0
9 8 PostalCode NVARCHAR(10) 0 NA 0
10 9 Phone NVARCHAR(24) 0 NA 0
11 10 Fax NVARCHAR(24) 0 NA 0
12 11 Email NVARCHAR(60) 1 NA 0
13 12 SupportRepId INTEGER 0 NA 0
👉 Table: Employee
cid name type notnull dflt_value pk
1 0 EmployeeId INTEGER 1 NA 1
2 1 LastName NVARCHAR(20) 1 NA 0
3 2 FirstName NVARCHAR(20) 1 NA 0
4 3 Title NVARCHAR(30) 0 NA 0
5 4 ReportsTo INTEGER 0 NA 0
6 5 BirthDate DATETIME 0 NA 0
7 6 HireDate DATETIME 0 NA 0
8 7 Address NVARCHAR(70) 0 NA 0
9 8 City NVARCHAR(40) 0 NA 0
10 9 State NVARCHAR(40) 0 NA 0
11 10 Country NVARCHAR(40) 0 NA 0
12 11 PostalCode NVARCHAR(10) 0 NA 0
13 12 Phone NVARCHAR(24) 0 NA 0
14 13 Fax NVARCHAR(24) 0 NA 0
15 14 Email NVARCHAR(60) 0 NA 0
👉 Table: Genre
cid name type notnull dflt_value pk
1 0 GenreId INTEGER 1 NA 1
2 1 Name NVARCHAR(120) 0 NA 0
👉 Table: Invoice
cid name type notnull dflt_value pk
1 0 InvoiceId INTEGER 1 NA 1
2 1 CustomerId INTEGER 1 NA 0
3 2 InvoiceDate DATETIME 1 NA 0
4 3 BillingAddress NVARCHAR(70) 0 NA 0
5 4 BillingCity NVARCHAR(40) 0 NA 0
6 5 BillingState NVARCHAR(40) 0 NA 0
7 6 BillingCountry NVARCHAR(40) 0 NA 0
8 7 BillingPostalCode NVARCHAR(10) 0 NA 0
9 8 Total NUMERIC(10,2) 1 NA 0
👉 Table: InvoiceLine
cid name type notnull dflt_value pk
1 0 InvoiceLineId INTEGER 1 NA 1
2 1 InvoiceId INTEGER 1 NA 0
3 2 TrackId INTEGER 1 NA 0
4 3 UnitPrice NUMERIC(10,2) 1 NA 0
5 4 Quantity INTEGER 1 NA 0
👉 Table: MediaType
cid name type notnull dflt_value pk
1 0 MediaTypeId INTEGER 1 NA 1
2 1 Name NVARCHAR(120) 0 NA 0
👉 Table: Playlist
cid name type notnull dflt_value pk
1 0 PlaylistId INTEGER 1 NA 1
2 1 Name NVARCHAR(120) 0 NA 0
👉 Table: PlaylistTrack
cid name type notnull dflt_value pk
1 0 PlaylistId INTEGER 1 NA 1
2 1 TrackId INTEGER 1 NA 2
👉 Table: Track
cid name type notnull dflt_value pk
1 0 TrackId INTEGER 1 NA 1
2 1 Name NVARCHAR(200) 1 NA 0
3 2 AlbumId INTEGER 0 NA 0
4 3 MediaTypeId INTEGER 1 NA 0
5 4 GenreId INTEGER 0 NA 0
6 5 Composer NVARCHAR(220) 0 NA 0
7 6 Milliseconds INTEGER 1 NA 0
8 7 Bytes INTEGER 0 NA 0
9 8 UnitPrice NUMERIC(10,2) 1 NA 0
🔍 Step 3. Query the Database
หลังสำรวจ database แล้ว เราสามารถ query ข้อมูลได้ด้วย 3 functions ได้แก่:
dbReadTable()dbGetQuery()dbSendQuery()
.
1️⃣ dbReadTable()
เราจะใช้ dbReadTable() เมื่อต้องการดึงข้อมูลทั้งหมดมาจาก table ที่ต้องการ
ตัวอย่างเช่น ดูข้อมูลทั้งหมดใน Genre:
# Query with dbReadTable()
dbReadTable(con,
"Genre")
ผลลัพธ์:
GenreId Name
1 1 Rock
2 2 Jazz
3 3 Metal
4 4 Alternative & Punk
5 5 Rock And Roll
6 6 Blues
7 7 Latin
8 8 Reggae
9 9 Pop
10 10 Soundtrack
11 11 Bossa Nova
12 12 Easy Listening
13 13 Heavy Metal
14 14 R&B/Soul
15 15 Electronica/Dance
16 16 World
17 17 Hip Hop/Rap
18 18 Science Fiction
19 19 TV Shows
20 20 Sci Fi & Fantasy
21 21 Drama
22 22 Comedy
23 23 Alternative
24 24 Classical
25 25 Opera
.
2️⃣ dbGetQuery()
ในกรณีที่เราต้องการดูข้อมูลแบบเจาะจง เราจะใช้ dbGetQuery() ซึ่งต้องการ 2 arguments:
- Connection ที่เชื่อมต่อกับ database
- SQL query ที่กำหนดข้อมูลที่ต้องการจาก database
ตัวอย่างการใช้งาน #1 – ดึงข้อมูลลูกค้าที่มาจากบราซิล:
# Query with dbGetQuery() - example 1
dbGetQuery(con,
"SELECT
CustomerId,
FirstName,
LastName, Email
FROM
Customer
WHERE
country = 'Brazil';")
ผลลัพธ์:
CustomerId FirstName LastName Email
1 1 Luís Gonçalves luisg@embraer.com.br
2 10 Eduardo Martins eduardo@woodstock.com.br
3 11 Alexandre Rocha alero@uol.com.br
4 12 Roberto Almeida roberto.almeida@riotur.gov.br
5 13 Fernanda Ramos fernadaramos4@uol.com.br
ตัวอย่าง #2 – คำนวณยอดขายรวมของแต่ละประเทศ โดยเรียงจากมากไปน้อย:
# Query with dbGetQuery() - example 2
dbGetQuery(con,
"SELECT
BillingCountry,
SUM(Total) AS TotalSales
FROM
Invoice
GROUP BY
BillingCountry
ORDER BY
TotalSales DESC;")
ผลลัพธ์:
BillingCountry TotalSales
1 USA 523.06
2 Canada 303.96
3 France 195.10
4 Brazil 190.10
5 Germany 156.48
6 United Kingdom 112.86
7 Czech Republic 90.24
8 Portugal 77.24
9 India 75.26
10 Chile 46.62
11 Ireland 45.62
12 Hungary 45.62
13 Austria 42.62
14 Finland 41.62
15 Netherlands 40.62
16 Norway 39.62
17 Sweden 38.62
18 Spain 37.62
19 Poland 37.62
20 Italy 37.62
21 Denmark 37.62
22 Belgium 37.62
23 Australia 37.62
24 Argentina 37.62
ตัวอย่าง #3 – ดึงชื่อเพลงและชื่ออัลบัม 10 อันดับแรก:
# Query with dbGetQuery() - example 3
dbGetQuery(con,
"SELECT
T.Name AS TrackName,
A.Title AS AlbumTitle
FROM
Track AS T
JOIN
Album AS A ON T.AlbumID = A.AlbumID
LIMIT 10;")
ผลลัพธ์:
TrackName AlbumTitle
1 For Those About To Rock (We Salute You) For Those About To Rock We Salute You
2 Balls to the Wall Balls to the Wall
3 Fast As a Shark Restless and Wild
4 Restless and Wild Restless and Wild
5 Princess of the Dawn Restless and Wild
6 Put The Finger On You For Those About To Rock We Salute You
7 Let's Get It Up For Those About To Rock We Salute You
8 Inject The Venom For Those About To Rock We Salute You
9 Snowballed For Those About To Rock We Salute You
10 Evil Walks For Those About To Rock We Salute You
.
3️⃣ dbSendQuery()
dbSendQuery() ทำงานเหมือนกับ dbGetQuery() แต่ต่างกันที่ dbGetQuery() จะยังส่งข้อมูลใด ๆ กลับมาจนกว่าเราจะเรียกดูด้วย dbFetch()
ยกตัวอย่างเช่น ดูข้อมูลลูกค้า 10 รายชื่อแรกเมื่อเรียงตามนามสกุล:
# Send query
res <- dbSendQuery(con,
"SELECT
CustomerId,
LastName,
FirstName,
Email
FROM
Customer
ORDER BY
LastName
LIMIT 10;")
# Fetch results
dbFetch(res)
ผลลัพธ์:
CustomerId LastName FirstName Email
1 12 Almeida Roberto roberto.almeida@riotur.gov.br
2 28 Barnett Julia jubarnett@gmail.com
3 39 Bernard Camille camille.bernard@yahoo.fr
4 18 Brooks Michelle michelleb@aol.com
5 29 Brown Robert robbrown@shaw.ca
6 21 Chase Kathy kachase@hotmail.com
7 26 Cunningham Richard ricunningham@hotmail.com
8 41 Dubois Marc marc.dubois@hotmail.com
9 34 Fernandes João jfernandes@yahoo.pt
10 30 Francis Edward edfrancis@yachoo.ca
เมื่อเราเรียกดูข้อมูลทั้งหมดแล้ว เราจะไม่สามารถเรียกดูซ้ำได้:
# Fetch results
dbFetch(res)
ผลลัพธ์:
> dbFetch(res)
[1] CustomerId LastName FirstName Email
<0 rows> (or 0-length row.names)
ทั้งนี้ เราสามารถกำหนดจำนวนข้อมูลที่จะเรียกดูในแต่ละครั้งได้ เช่น:
# Send query
res <- dbSendQuery(con,
"SELECT
CustomerId,
LastName,
FirstName,
Email
FROM
Customer
ORDER BY
LastName
LIMIT 10;")
# Fetch five, twice
dbFetch(res, n = 5)
dbFetch(res, n = 5)
dbFetch(res, n = 5)
ผลลัพธ์:
> dbFetch(res, n = 5)
CustomerId LastName FirstName Email
1 12 Almeida Roberto roberto.almeida@riotur.gov.br
2 28 Barnett Julia jubarnett@gmail.com
3 39 Bernard Camille camille.bernard@yahoo.fr
4 18 Brooks Michelle michelleb@aol.com
5 29 Brown Robert robbrown@shaw.ca
> dbFetch(res, n = 5)
CustomerId LastName FirstName Email
1 21 Chase Kathy kachase@hotmail.com
2 26 Cunningham Richard ricunningham@hotmail.com
3 41 Dubois Marc marc.dubois@hotmail.com
4 34 Fernandes João jfernandes@yahoo.pt
5 30 Francis Edward edfrancis@yachoo.ca
> dbFetch(res, n = 5)
[1] CustomerId LastName FirstName Email
<0 rows> (or 0-length row.names)
เราสามารถใช้ dbSendQuery() และ dbFetch() เพื่อดูข้อมูลเป็นชุด ๆ แทนที่จะดูข้อมูลทั้งหมดในครั้งเดียวแบบ dbGetQuery()
Note: ในกรณีที่เราต้องการลบ query ที่เราส่งไป database ด้วย dbSendQuery() ให้เราใช้ dbClearResult():
# Clear results
dbClearResult(res)
🤚 Step 4. Close the Connection
สุดท้าย เมื่อเราทำงานกับ database เสร็จแล้ว เราต้องสิ้นสุดการเชื่อมต่อกับ database ด้วย dbDisconnect():
# Close the connection
dbDisconnect(con)
เป็นอันจบการทำงานกับ database ด้วย DBI
💪 Summary
ในบทความนี้ เราได้ไปดูวิธีการใช้งาน DBI เพื่อทำงานกับ database กัน:
เชื่อมต่อ database:
dbConnect()
สำรวจ database:
dbListTables()dbGetQuery()
Query ข้อมูล:
dbReadTable()dbGetQuery()dbSendQuery(),dbFetch(), และdbClearResult()
ปิดการเชื่อมต่อ:
dbDisconnect()
😺 GitHub
ดู code และ database ในบทความนี้ได้ที่ GitHub
📃 References
✅ R Book for Psychologists: หนังสือภาษา R สำหรับนักจิตวิทยา
📕 ขอฝากหนังสือเล่มแรกในชีวิตด้วยนะครับ 😆
🙋 ใครที่กำลังเรียนจิตวิทยาหรือทำงานสายจิตวิทยา และเบื่อที่ต้องใช้ software ราคาแพงอย่าง SPSS และ Excel เพื่อทำข้อมูล
💪 ผมขอแนะนำ R Book for Psychologists หนังสือสอนใช้ภาษา R เพื่อการวิเคราะห์ข้อมูลทางจิตวิทยา ที่เขียนมาเพื่อนักจิตวิทยาที่ไม่เคยมีประสบการณ์เขียน code มาก่อน
ในหนังสือ เราจะปูพื้นฐานภาษา R และพาไปดูวิธีวิเคราะห์สถิติที่ใช้บ่อยกัน เช่น:
- Correlation
- t-tests
- ANOVA
- Reliability
- Factor analysis
🚀 เมื่ออ่านและทำตามตัวอย่างใน R Book for Psychologists ทุกคนจะไม่ต้องพึง SPSS และ Excel ในการทำงานอีกต่อไป และสามารถวิเคราะห์ข้อมูลด้วยตัวเองได้ด้วยความมั่นใจ
แล้วทุกคนจะแปลกใจว่า ทำไมภาษา R ง่ายขนาดนี้ 🙂↕️
👉 สนใจดูรายละเอียดหนังสือได้ที่ meb:
Leave a reply to dbplyr: แนะนำ package และ 6 ขั้นตอนในการทำงานกับ database ด้วย dplyr syntax ในภาษา R — ตัวอย่างการทำงานกับ Chinook database – Shi no Cancel reply