اتصال به پایگاه داده در R

در این بخش بررسی می کنیم که چگونه در زبان R به پایگاه داده متصل شویم و برخی از مهم ترین ویژگی های SQL را جهت بازیابی اطلاعات را معرفی می کنیم. در پایان این آموزش شما می‌توانید مهم‌ترین مؤلفه‌های SQL را شناسایی کرده و با عملکرد آن ها آشنا شوید.

اگرچه فایل های صفحه گسترده (فایل های برنامه Excel و داده هایی که در قالب CSV ذخیره می شوند) کاربردهای زیادی در فرایندهای تحلیل داده دارند، معمولا به منظور نگهداری حجم عظیمی از داده ها از پایگاه های داده استفاده می شود. بنابراین به عنوان بخش مهمی از توانایی های ضروری برای انجام فرایندهای تحلیلی پیشرفته و در ابعاد تجاری، ضروری است که بدانیم چگونه به داده های موجود در پایگاه های داده دسترسی داشته باشیم.

از رایج ترین کتابخانه های R برای این منظور، کتابخانه (پکیج) های DBI و dbplyr می باشند که فرایندهای مدیریت اتصال به پایگاه داده و سپس بازیابی داده ها را برای ما تسهیل می کنند.

با اینحال، خبر خوب این است که ما قرار نیست تمام وقتمان را صرف کار را با SQL کنیم و در عوض بررسی می کنیم که چگونه می توان دستورات لازم را به زبان R نوشت و سپس به زبان SQL تبدیل کرد.

پیش نیازها

پکیج DBI یک رابط سطح پایین است که به پایگاه های داده متصل می شود و SQL را اجرا می کند. از سوی دیگر، پکیج dbplyr یک رابط سطح بالا است که کدهای dplyr را به پرس و جوهای های SQL ترجمه (تبدیل) می کند و سپس آنها را با کتابخانه DBI اجرا می کند.

در این آموزش از دو پایگاه داده محلی زیر استفاده خواهیم کرد:

• SQLite
• Duckdb

برای نصب پکیج های لازم از دستورات زیر استفاده خواهیم کرد:

install.packages("DBI")
install.packages("dbplyr")
install.packages("tidyverse")
install.packages("RSQLite")
install.packages("duckdb")

پس از نصب، باید کتابخانه های یاد شده درون حافظه فراخوانی شوند.

library(DBI)
library(dbplyr)
library(tidyverse)
library(duckdb)
library(RSQLite)
library(duckdb)

ابتدا با استفاده از Duckdb اولین پایگاه داده را با استفاده از دستور زیر ایجاد می کنیم.

con <- dbConnect(duckdb::duckdb(), dbdir = ":memory:")

جهت نمایش وضعیت پایگاه داده ایجاد شده از دستور summary استفاده می کنیم.

summary(con)
duckdb_connection
dbname = :memory:
db.version = 0.8.1
username = NA
host = NA
port = NA

برای ایجاد یک جدول (Table) در پایگاه داده ایجاد شده، از دستور زیر استفاده می شود:

dbExecute(con, "CREATE TABLE items(item VARCHAR, value DECIMAL(10,2), count INTEGER)")

# [1] 0

عدد صفر نشان دهنده آن است که جدول items با موفقیت ساخته شد. برای افزودن داده به این جدول از دستور زیر استفاده می کنیم:

 dbExecute(con, "INSERT INTO items VALUES ('Windows', 20.0, 1), ('Linux', 42.2, 2)")

به منظور واکشی (Fetch) اطلاعات ذخیره شده در جدول items از دستور زیر استفاده می شود:

 res <- dbGetQuery(con, "SELECT * FROM items")

جهت نمایش نتایج عملیات بالا در صفحه نمایش از دستور زیر استفاده می شود:

print(res)
#   item value count  
# 1 Windows 20.0 1
# 2  Linux  42.2  2

در صورتی که بخواهیم اطلاعات را به صورت پارامتریک و پویا در پایگاه داده ذخیره کنیم از دستور زیر استفاده می کنیم. طبیعی است که این روش میزان انعطاف پذیری فرایند اضافه کردن داده های جدید را به میزان قابل توجهی افزایش می دهد.

 dbExecute(con, "INSERT INTO items VALUES (?, ?, ?)", list('Ubuntu', 2000, 1))

اگر بخواهیم اطلاعات چند محصول (را در قالب چند سطر جدید) در جدول وارد کنیم، باید از دستور زیر استفاده شود:

stmt <- dbSendStatement(con, "INSERT INTO items VALUES (?, ?, ?)")
dbBind(stmt, list('Mac', 300, 2))
dbBind(stmt, list('android', 3.5, 1))
dbClearResult(stmt)

به منظور واکشی اطلاعات ذخیره شده از دستور زیر استفاده می کنیم:

res <- dbGetQuery(con, "SELECT * FROM items")
print(res)
# item value count
1# Windows 20.0 1
2# Linux 42.2 2
3# Ubuntu 2000.0 1
4# Mac 300.0 2
5# android 3.5 1

فرض کنید قرار است آیتم هایی که مقادیر بالای 400 دارند را بدست آوریم. به این درخواست فیلتر کردن اطلاعات در فرایند بازیابی اطلاعات می گویند. جهت انجام این درخواست از دستور زیر استفاده می کنیم:

res <- dbGetQuery(con, "SELECT item FROM items WHERE value > ?", list(400))
print(res)
#   item
#1 Ubuntu

دیتاست iris یکی از مجموعه داده های محبوب و پراستفاده در بین تحلیلگران داده است و در بسیاری از پروژه های پژوهشی و آموزشی از آن استفاده می شود. با استفاده از دستور زیر، از این دیتاست برای ایجاد یک جدول در پایگاه داده استفاده می کنیم.

 dbWriteTable(con, "iris_table", iris)

جهت فراخوانی یک سطر پایگاه داده فوق از دستور زیر استفاده می شود:

res <- dbGetQuery(con, "SELECT * FROM iris_table LIMIT 1")
 print(res)
# Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#1 5.1        3.5        1.4           0.2          setosa

ساخت پایگاه داده SQLite با دستور زیر انجام می شود:

 con <- dbConnect(RSQLite::SQLite(), ":memory:")

جهت نمایش فهرستی از جدول های موجود در پایگاه داده ایجاد شده از دستور زیر استفاده می شود:

dbListTables(con)
# character(0)

خروجی تولید شده نشان می دهد که هنوز هیچ جدولی در این پایگاه داده ساخته نشده است. در این مثال، برای ایجاد یک جدول از دیتاست mtcars استفاده می کنیم.

dbWriteTable(con, "mtcars", mtcars)

حال، دوباره فهرست جدول های ساخته شده را به وسیله دستور زیر بررسی می کنیم:

dbListTables(con)
# [1] "mtcars"

جهت واکشی اطلاعات هم دستور زیر را اجراء می کنیم:

res <- dbGetQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")

print(res)

   mpg cyl disp hp drat wt qsec vs am gear carb

1 22.8 4 108.0 93 3.85 2.320 18.61 1 1 4 1

2 24.4 4 146.7 62 3.69 3.190 20.00 1 0 4 2

3 22.8 4 140.8 95 3.92 3.150 22.90 1 0 4 2

4 32.4 4 78.7 66 4.08 2.200 19.47 1 1 4 1

5 30.4 4 75.7 52 4.93 1.615 18.52 1 1 4 2

6 33.9 4 71.1 65 4.22 1.835 19.90 1 1 4 1

7 21.5 4 120.1 97 3.70 2.465 20.01 1 0 3 1

8 27.3 4 79.0 66 4.08 1.935 18.90 1 1 4 1

9 26.0 4 120.3 91 4.43 2.140 16.70 0 1 5 2

10 30.4 4 95.1 113 3.77 1.513 16.90 1 1 5 2

11 21.4 4 121.0 109 4.11 2.780 18.60 1 1 4 2

تصور کنید که براساس نیازمندی های پروژه از ما خواسته شده تا درخواست زیر را انجام دهیم:

“میانگین متغیر mpg به تفکیک هر گروه”

برای اجرای چنین پرس و جویی (Query) به شیوه زیر عمل می کنیم.

summary <- mtcars %>% 
+   group_by(cyl) %>% 
+   summarise(mpg = mean(mpg, na.rm = TRUE)) %>% 
+   arrange(desc(mpg)) 
summary  
# A tibble: 3 × 2
#   cyl   mpg
#  <dbl> <dbl>
1#     4  26.7
2#     6  19.7
3#     8  15.1

درصورتی که بخواهیم کد SQL مربوط به این پرس و جو را دریافت (تولید) کنیم از تابع ()show_query به شکل زیر استفاده خواهیم کرد:

summary <- mtcars2 %>%
+ group_by(cyl) %>%
+ summarise(mpg = mean(mpg, na.rm = TRUE)) %>%
+ arrange(desc(mpg)) %>%
+ show_query()
# <SQL>
# SELECT `cyl`, AVG(`mpg`) AS `mpg`
# FROM `mtcars`
# GROUP BY `cyl`
# ORDER BY `mpg` DESC

تا کنون نتایج استخراج شده در متغیر res قرار می گرفت. جهت پاک کردن نتایج ذخیره شده در متغیر res از دستور زیر استفاده می کنیم:

dbClearResult(res)
 print(res)
# <SQLiteResult>
# EXPIRED

در انتها و پس از اتمام کار با پایگاه داده ساخته شده، می توانیم اتصال ایجاد شده را به وسیله دستور زیر پایان دهیم.

 dbDisconnect(con)