21 قواعد البيانات

21.1 مقدمة

تعيش كمية هائلة من البيانات داخل قواعد البيانات، لذا من الضروري للغاية أن تعرف كيفية الوصول إليها. في بعض الأحيان، يمكنك أن تطلب من شخص ما تنزيل لقطة (snapshot) من البيانات في ملف .csv لأجلك، ولكن هذه العملية تصبح مزعجة وسريعة التعقيد: في كل مرة تحتاج فيها إلى إجراء تغيير، سيكون عليك التواصل مع شخص آخر. أنت تريد أن تكون قادرًا على الوصول إلى قاعدة البيانات مباشرة للحصول على البيانات التي تحتاجها، في الوقت الذي تحتاجها فيه.

في هذا الفصل، ستتعلم أولاً أساسيات حزمة DBI: كيفية استخدامها للاتصال بقاعدة بيانات ثم استرجاع البيانات باستخدام استعلام SQL¹. لغة SQL (وهي اختصار لـ structured query language أي لغة الاستعلام المهيكلة)، هي اللغة المشتركة (lingua franca) لقواعد البيانات، وتُعد لغة مهمة لجميع علماء البيانات لتعلمها. ومع ذلك، لن نبدأ بـ SQL بشكل مباشر، بل سنعلمك بدلاً من ذلك حزمة dbplyr، والتي يمكنها ترجمة كود dplyr الخاص بك إلى لغة SQL. سنستخدم ذلك كوسيلة لتعليمك بعض من أهم ميزات SQL. لن تصبح خبيرًا متمرسًا في SQL بنهاية هذا الفصل، ولكنك ستكون قادرًا على تحديد أهم المكونات وفهم ما تفعله.

21.1.1 المتطلبات المسبقة

في هذا الفصل، سنقدم حزمتي DBI و dbplyr. حزمة DBI هي واجهة منخفضة المستوى (low-level) تتصل بقواعد البيانات وتنفذ استعلامات SQL؛ بينما حزمة dbplyr هي واجهة عالية المستوى (high-level) تترجم كود dplyr الخاص بك إلى استعلامات SQL ثم تنفذها باستخدام DBI.

library(DBI)
library(dbplyr)
library(tidyverse)

21.2 أساسيات قواعد البيانات

في أبسط مستوياتها، يمكنك التفكير في قاعدة البيانات على أنها مجموعة من أطر البيانات (data frames)، وتسمى جداول (tables) في مصطلحات قواعد البيانات. مثل إطار البيانات، يتكون جدول قاعدة البيانات من مجموعة من الأعمدة المسمّاة، حيث كل قيمة في العمود تكون من نفس النوع. هناك ثلاثة فروق رئيسية عالية المستوى بين أطر البيانات وجداول قواعد البيانات:

تُخزَّن جداول قواعد البيانات على القرص الصلب ويمكن أن تكون كبيرة بأي حجم. بينما تُخزَّن أطر البيانات في الذاكرة العشوائية (RAM)، وتكون محدودة بشكل أساسي (على الرغم من أن هذا الحد ما يزال كبيراً جداً للعديد من المشكلات).
تحتوي جداول قواعد البيانات تقريبًا دائمًا على فهارس (indexes). تمامًا مثل فهرس الكتاب، يجعل فهرس قاعدة البيانات من الممكن العثور بسرعة على الصفوف المطلوبة دون الحاجة إلى الفحص في كل صف على حدة. لا تحتوي أطر البيانات وجداول tibbles على فهارس، لكن أطر data.tables تحتوي عليها، وهو أحد أسباب سرعتها الفائقة.
معظم قواعد البيانات التقليدية مصممة ومحسّنة لجمع البيانات بسرعة، وليس لتحليل البيانات الموجودة. تسمى قواعد البيانات هذه موجهة نحو الصفوف (row-oriented) لأن البيانات تُخزن صفًا تلو الآخر، وليس عمودًا تلو الآخر كما في R. مؤخرًا، كان هناك الكثير من التطوير لقواعد البيانات الموجهة نحو الأعمدة (column-oriented) والتي تجعل تحليل البيانات الموجودة أسرع بكثير.

يتم تشغيل قواعد البيانات بواسطة أنظمة إدارة قواعد البيانات (database management systems) (تسمى اختصارًا DBMS)، والتي تأتي في ثلاثة أشكال أساسية:

أنظمة العميل-الخادم (Client-server). تعمل على خادم مركزي قوي، تتصل به من جهاز الكمبيوتر الخاص بك (العميل). وهي ممتازة لمشاركة البيانات مع أشخاص متعددين في المؤسسة. تشمل أنظمة العميل-الخادم الشائعة: PostgreSQL و MariaDB و SQL Server و Oracle.
أنظمة السحابية (Cloud). مثل Snowflake و Amazon RedShift و Google BigQuery، وهي مشابهة لأنظمة العميل-الخادم، ولكنها تعمل في السحابة. هذا يعني أنه يمكنها التعامل بسهولة مع مجموعات البيانات الضخمة للغاية وتوفير المزيد من الموارد الحوسبية تلقائيًا حسب الحاجة.
أنظمة داخل العملية (In-process). مثل SQLite أو duckdb، تعمل بالكامل على جهاز الكمبيوتر الخاص بك. وهي ممتازة للعمل مع مجموعات البيانات الكبيرة عندما تكون أنت المستخدم الرئيسي.

21.3 الاتصال بقاعدة بيانات

للاتصال بقاعدة البيانات من R، ستستخدم زوجًا من الحزم:

ستستخدم دائمًا حزمة DBI (اختصار لـ database interface) لأنها توفر مجموعة من الدوال العامة التي تتصل بقاعدة البيانات، وتحمل البيانات، وتنفذ استعلامات SQL، وما إلى ذلك.
ستستخدم أيضًا حزمة مخصصة لنظام DBMS الذي تتصل به. تقوم هذه الحزمة بترجمة أوامر DBI العامة إلى التفاصيل المحددة المطلوبة لنظام DBMS معين. عادة ما تكون هناك حزمة واحدة لكل نظام DBMS، على سبيل المثال: RPostgres لنظام PostgreSQL و RMariaDB لنظام MySQL.

إذا لم تجد حزمة مخصصة لنظام DBMS الخاص بك، يمكنك عادةً استخدام حزمة odbc بدلاً من ذلك. تستخدم هذه الحزمة بروتوكول ODBC المدعوم من قبل العديد من أنظمة DBMS. تتطلب حزمة odbc إعدادًا أكثر قليلاً لأنك ستلزمك أيضًا بتثبيت برنامج تشغيل ODBC (ODBC driver) وإخبار حزمة odbc بمكان العثور عليه.

بشكل عملي، يمكنك إنشاء اتصال بقاعدة البيانات باستخدام DBI::dbConnect(). يحدد المعامل الأول نظام DBMS²، ثم تصف المعاملات الثانية والتالية كيفية الاتصال به (أي أين يقع واعتمادات الوصول التي تحتاجها للوصول إليه). يعرض الكود التالي زوجًا من الأمثلة النموذجية:

con <- DBI::dbConnect(
  RMariaDB::MariaDB(), 
  username = "foo"
)
con <- DBI::dbConnect(
  RPostgres::Postgres(), 
  hostname = "databases.mycompany.com", 
  port = 1234
)

تختلف التفاصيل الدقيقة للاتصال كثيرًا من نظام DBMS إلى آخر، لذا وللأسف لا يمكننا تغطية جميع التفاصيل هنا. هذا يعني أنك ستحتاج إلى إجراء القليل من البحث بنفسك. عادةً يمكنك سؤال علماء البيانات الآخرين في فريقك أو التحدث إلى مسؤول قاعدة البيانات (DBA وهو اختصار لـ database administrator). غالبًا ما يتطلب الإعداد الأولي القليل من التعديل (وربما بعض البحث على Google) لضبطه بشكل صحيح، ولكنك عمومًا لن تحتاج إلى القيام بذلك سوى مرة واحدة فقط.

21.3.1 في هذا الكتاب

سيكون إعداد نظام DBMS من نوع العميل-الخادم أو النظام السحابي أمرًا مرهقًا لهذا الكتاب، لذا سنستخدم بدلاً من ذلك نظام DBMS داخل العملية (In-process) يعيش بالكامل داخل حزمة R: وهو duckdb. بفضل سحر DBI، فإن الفارق الوحيد بين استخدام duckdb وأي نظام DBMS آخر هو كيفية الاتصال بقاعدة البيانات. هذا يجعله ممتازاً للتعليم لأنك تستطيع تشغيل هذا الكود بسهولة بالإضافة إلى أخذ ما تعلمته وتطبيقه في مكان آخر بسهولة.

الاتصال بـ duckdb بسيط للغاية لأن الإعدادات الافتراضية تنشئ قاعدة بيانات مؤقتة تُحذف عند الخروج من R. هذا أمر رائع للتعلم لأنه يضمن لك البدء من صفحة بيضاء في كل مرة تعيد فيها تشغيل R:

con <- DBI::dbConnect(duckdb::duckdb())
#> duckdb keeps downloaded extensions and secrets in a temporary directory:
#> ℹ /tmp/Rtmp64eVGg/duckdb
#> This is removed when the R session ends.
#> • Extensions are re-downloaded each session.
#> • Secrets are lost.
#> ℹ Run duckdb(shared_home = TRUE) (or create ~/.duckdb) to keep them (suitable for most users).
#> ℹ Run duckdb(shared_home = FALSE) to accept the temporary directory (and silence this message).
#> ℹ See ?duckdb_storage for details and alternatives.

duckdb هي قاعدة بيانات عالية الأداء ومصممة للغاية لتلبية احتياجات عالم البيانات. نستخدمها هنا لأن البدء بها سهل للغاية، ولكنها قادرة أيضًا على التعامل مع جيجابايت من البيانات بسرعات عالية. إذا كنت ترغب في استخدام duckdb لمشروع تحليل بيانات حقيقي، فستحتاج أيضًا إلى تقديم معامل dbdir لإنشاء قاعدة بيانات دائمة وإخبار duckdb بمكان حفظها. بافتراض أنك تستخدم مشروعًا (الفصل 6)، فمن المعقول تخزينها في المجلد duckdb للمشروع الحالي:

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

21.3.2 تحميل بعض البيانات

بما أن هذه قاعدة بيانات جديدة، نحتاج إلى البدء بإضافة بعض البيانات. هنا سنضيف مجموعتي البيانات mpg و diamonds من حزمة ggplot2 باستخدام DBI::dbWriteTable(). يتطلب الأسلوب الأبسط لاستخدام dbWriteTable() ثلاثة معاملات: اتصال قاعدة البيانات، اسم الجدول المراد إنشاؤه في قاعدة البيانات، وإطار البيانات الذي يحتوي على البيانات.

dbWriteTable(con, "mpg", ggplot2::mpg)
dbWriteTable(con, "diamonds", ggplot2::diamonds)

إذا كنت تستخدم duckdb في مشروع حقيقي، فإننا نوصي بشدة بالتعرف على duckdb_read_csv() و duckdb_register_arrow(). تمنحك هذه الدوال طرقًا قوية وعالية الأداء لتحميل البيانات بسرعة وبشكل مباشر إلى duckdb، دون الحاجة إلى تحميلها أولاً في R. سنعرض أيضًا تقنية مفيدة لتحميل ملفات متعددة في قاعدة بيانات في قسم 26.4.1.

21.3.3 أساسيات DBI

يمكنك التحقق من تحميل البيانات بشكل صحيح باستخدام بضع دوال أخرى من DBI: ترسل dbListTables() قائمة بجميع الجداول في قاعدة البيانات³ بينما تقوم dbReadTable() باسترجاع محتويات جدول معين.

dbListTables(con)
#> [1] "diamonds" "mpg"

con |> 
  dbReadTable("diamonds") |> 
  as_tibble()
#> # A tibble: 53,940 × 10
#>   carat cut       color clarity depth table price     x     y     z
#>   <dbl> <fct>     <fct> <fct>   <dbl> <dbl> <int> <dbl> <dbl> <dbl>
#> 1  0.23 Ideal     E     SI2      61.5    55   326  3.95  3.98  2.43
#> 2  0.21 Premium   E     SI1      59.8    61   326  3.89  3.84  2.31
#> 3  0.23 Good      E     VS1      56.9    65   327  4.05  4.07  2.31
#> 4  0.29 Premium   I     VS2      62.4    58   334  4.2   4.23  2.63
#> 5  0.31 Good      J     SI2      63.3    58   335  4.34  4.35  2.75
#> 6  0.24 Very Good J     VVS2     62.8    57   336  3.94  3.96  2.48
#> # ℹ 53,934 more rows

تعيد الدالة dbReadTable() كائنًا من نوع data.frame لذا نستخدم as_tibble() لتحويله إلى tibble بحيث يُطبع بشكل أنيق.

إذا كنت تعرف SQL بالفعل، يمكنك استخدام dbGetQuery() للحصول على نتائج تشغيل استعلام على قاعدة البيانات:

sql <- "
  SELECT carat, cut, clarity, color, price 
  FROM diamonds 
  WHERE price > 15000
"
as_tibble(dbGetQuery(con, sql))
#> # A tibble: 1,655 × 5
#>   carat cut       clarity color price
#>   <dbl> <fct>     <fct>   <fct> <int>
#> 1  1.54 Premium   VS2     E     15002
#> 2  1.19 Ideal     VVS1    F     15005
#> 3  2.1  Premium   SI1     I     15007
#> 4  1.69 Ideal     SI1     D     15011
#> 5  1.5  Very Good VVS2    G     15013
#> 6  1.73 Very Good VS1     G     15014
#> # ℹ 1,649 more rows

إذا لم تكن قد رأيت SQL من قبل، فلا تقلق! ستتعلم المزيد عنها قريباً. ولكن إذا قرأتها بتمعن، فقد تخمن أنها تحدد خمسة أعمدة من مجموعة بيانات diamonds وجميع الصفوف التي يزيد فيها السعر price عن 15,000.

21.4 أساسيات dbplyr

الآن بعد أن اتصلنا بقاعدة البيانات وقمنا بتحميل بعض البيانات، يمكننا البدء في التعرف على dbplyr. تُعد dbplyr بمثابة محرك خلفي (backend) لـ dplyr، مما يعني أنك تستمر في كتابة كود dplyr ولكن المحرك الخلفي ينفذه بشكل مختلف. في هذه الحالة، تترجم dbplyr الكود إلى SQL؛ وتشمل المحركات الخلفية الأخرى dtplyr والتي تترجم إلى data.table، و multidplyr والتي تنفذ كودك على نوى متعددة (multiple cores).

لاستخدام dbplyr، يجب عليك أولاً استخدام tbl() لإنشاء كائن يمثل جدول قاعدة البيانات:

diamonds_db <- tbl(con, "diamonds")
diamonds_db
#> # A query:  ?? x 10
#> # Database: DuckDB 1.5.5 [unknown@Linux 6.17.0-1022-azure:R 4.6.1/:memory:]
#>   carat cut       color clarity depth table price     x     y     z
#>   <dbl> <fct>     <fct> <fct>   <dbl> <dbl> <int> <dbl> <dbl> <dbl>
#> 1  0.23 Ideal     E     SI2      61.5    55   326  3.95  3.98  2.43
#> 2  0.21 Premium   E     SI1      59.8    61   326  3.89  3.84  2.31
#> 3  0.23 Good      E     VS1      56.9    65   327  4.05  4.07  2.31
#> 4  0.29 Premium   I     VS2      62.4    58   334  4.2   4.23  2.63
#> 5  0.31 Good      J     SI2      63.3    58   335  4.34  4.35  2.75
#> 6  0.24 Very Good J     VVS2     62.8    57   336  3.94  3.96  2.48
#> # ℹ more rows

هناك طريقتان شائعتان أخريان للتفاعل مع قاعدة بيانات. أولاً، تكتسب العديد من قواعد البيانات الخاصة بالشركات حجمًا ضخمًا، لذا فأنت بحاجة إلى التسلسل الهرمي للحفاظ على تنظيم جميع الجداول. في هذه الحالة، قد تحتاج إلى تقديم مخطط (schema)، أو دليل ومخطط (catalog and schema)، من أجل تحديد الجدول الذي تهتم به:

diamonds_db <- tbl(con, in_schema("sales", "diamonds"))
diamonds_db <- tbl(con, in_catalog("north_america", "sales", "diamonds"))

في أوقات أخرى، قد ترغب في استخدام استعلام SQL الخاص بك نقطةً للبداية:

diamonds_db <- tbl(con, sql("SELECT * FROM diamonds"))

هذا الكائن كسول (lazy)؛ عندما تستخدم أفعال dplyr عليه، لا تقوم dplyr بأي عمل فعلي: إنها تسجل فقط تسلسل العمليات التي تريد القيام بها ولا تنفذها إلا عند الحاجة. على سبيل المثال، خذ أنبوب التمرير (pipeline) التالي:

big_diamonds_db <- diamonds_db |> 
  filter(price > 15000) |> 
  select(carat:clarity, price)

big_diamonds_db
#> # A query:  ?? x 5
#> # Database: DuckDB 1.5.5 [unknown@Linux 6.17.0-1022-azure:R 4.6.1/:memory:]
#>   carat cut       color clarity price
#>   <dbl> <fct>     <fct> <fct>   <int>
#> 1  1.54 Premium   E     VS2     15002
#> 2  1.19 Ideal     F     VVS1    15005
#> 3  2.1  Premium   I     SI1     15007
#> 4  1.69 Ideal     D     SI1     15011
#> 5  1.5  Very Good G     VVS2    15013
#> 6  1.73 Very Good G     VS1     15014
#> # ℹ more rows

يمكنك معرفة أن هذا الكائن يمثل استعلام قاعدة بيانات لأنه يطبع اسم نظام DBMS في الأعلى، وبينما يخبرك بعدد الأعمدة، إلا أنه عادة لا يعرف عدد الصفوف. وذلك لأن معرفة العدد الإجمالي للصفوف تطلب عادةً تنفيذ الاستعلام بالكامل، وهو أمر نحاول تجنبه.

يمكنك رؤية كود SQL المولد بواسطة دالة dplyr عبر استخدام show_query(). إذا كنت تعرف dplyr، فهذه طريقة رائعة لتعلم SQL! اكتب بعض أكواد dplyr، واجعل dbplyr تترجمها إلى SQL، ثم حاول اكتشاف كيفية تطابق اللغتين.

big_diamonds_db |> 
  show_query()
#> <SQL>
#> SELECT carat, cut, color, clarity, price
#> FROM diamonds
#> WHERE (price > 15000.0)

لإعادة جميع البيانات إلى R، تقوم باستدعاء collect(). خلف الكواليس، يقود هذا إلى توليد كود SQL واستدعاء dbGetQuery() للحصول على البيانات، ثم تحويل النتيجة إلى tibble:

big_diamonds <- big_diamonds_db |> 
  collect()
big_diamonds
#> # A tibble: 1,655 × 5
#>   carat cut       color clarity price
#>   <dbl> <fct>     <fct> <fct>   <int>
#> 1  1.54 Premium   E     VS2     15002
#> 2  1.19 Ideal     F     VVS1    15005
#> 3  2.1  Premium   I     SI1     15007
#> 4  1.69 Ideal     D     SI1     15011
#> 5  1.5  Very Good G     VVS2    15013
#> 6  1.73 Very Good G     VS1     15014
#> # ℹ 1,649 more rows

عادةً، ستستخدم dbplyr لتحديد البيانات التي تريدها من قاعدة البيانات، مع إجراء التصفية والتجميع الأساسيين باستخدام الترجمات الموضحة أدناه. ثم، بمجرد أن تكون جاهزًا لتحليل البيانات باستخدام الدوال الفريدة لـ R، ستستخدم collect() للحصول على tibble في الذاكرة العشوائية، وتواصل عملك باستخدام كود R الخالص.

21.5 SQL

سيعلمك باقي الفصل القليل من SQL من خلال منظور dbplyr. إنها مقدمة غير تقليدية إلى حد ما لـ SQL ولكننا نأمل أن تجعلك تتعلم الأساسيات بسرعة. لحسن الحظ، إذا كنت تفهم dplyr فأنت في مكان ممتاز لالتقاط SQL بسرعة لأن العديد من المفاهيم هي نفسها.

سنستكشف العلاقة بين dplyr و SQL باستخدام بعض الأصدقاء القدامى من حزمة nycflights13: flights و planes. من السهل إدخال مجموعات البيانات هذه في قاعدة بيانات التعلم الخاصة بنا لأن dbplyr تأتي مع دالة تنسخ الجداول من nycflights13 إلى قاعدة البيانات الخاصة بنا:

dbplyr::copy_nycflights13(con)
#> Creating table: airlines
#> Creating table: airports
#> Creating table: flights
#> Creating table: planes
#> Creating table: weather
flights <- tbl(con, "flights")
planes <- tbl(con, "planes")

21.5.1 أساسيات SQL

تسمى المكونات عالية المستوى لـ SQL عبارات (statements). تشمل العبارات الشائعة CREATE لتحديد جداول جديدة، و INSERT لإضافة البيانات، و SELECT لاسترجاع البيانات. سنركز على عبارات SELECT، والتي تسمى أيضًا استعلامات (queries)، لأنها تقريبًا كل ما ستستخدمه كعالم بيانات.

يتكون الاستعلام من جمل (clauses). هناك خمس جمل مهمة: SELECT و FROM و WHERE و ORDER BY و GROUP BY. يجب أن يحتوي كل استعلام على جملتي SELECT ⁴ و FROM ⁵ وأبسط استعلام هو SELECT * FROM table والذي يحدد جميع الأعمدة من الجدول المحدد. هذا هو ما تولده dbplyr لجدول غير معدل:

flights |> show_query()
#> <SQL>
#> SELECT *
#> FROM flights
planes |> show_query()
#> <SQL>
#> SELECT *
#> FROM planes

تتحكم WHERE و ORDER BY في الصفوف التي يتم تضمينها وكيفية ترتيبها:

flights |> 
  filter(dest == "IAH") |> 
  arrange(dep_delay) |> 
  show_query()
#> <SQL>
#> SELECT *
#> FROM flights
#> WHERE (dest = 'IAH')
#> ORDER BY dep_delay

تحول GROUP BY الاستعلام إلى ملخص، مما يتسبب في حدوث التجميع (aggregation):

flights |> 
  group_by(dest) |> 
  summarize(dep_delay = mean(dep_delay, na.rm = TRUE)) |> 
  show_query()
#> <SQL>
#> SELECT dest, AVG(dep_delay) AS dep_delay
#> FROM flights
#> GROUP BY dest

هناك فرقان مهمان بين أفعال dplyr وجمل SELECT:

في SQL، حالة الأحرف لا تهم: يمكنك كتابة select أو SELECT أو حتى SeLeCt. في هذا الكتاب سنلتزم بالعرف الشائع المتمثل في كتابة الكلمات المفتاحية لـ SQL بأحرف كبيرة (UPPERCASE) لتمييزها عن أسماء الجداول أو المتغيرات.
في SQL، الترتيب يهم: يجب عليك دائمًا كتابة الجمل بالترتيب SELECT ثم FROM ثم WHERE ثم GROUP BY ثم ORDER BY. بشكل مربك، هذا الترتيب لا يطابق كيفية تقييم الجمل فعليًا وتنفيذها، حيث يتم البدء بـ FROM أولاً، ثم WHERE ثم GROUP BY ثم SELECT وأخيراً ORDER BY.

تستكشف الأقسام التالية كل جملة بمزيد من التفصيل.

لاحظ أنه على الرغم من أن SQL هي معيار قياسي، إلا أنها معقدة للغاية ولا توجد قاعدة بيانات تتبعها بالضبط. في حين أن المكونات الرئيسية التي سنركز عليها في هذا الكتاب متشابهة جدًا بين أنظمة DBMS، إلا أن هناك العديد من الاختلافات الطفيفة. لحسن الحظ، تم تصميم dbplyr للتعامل مع هذه المشكلة وتوليد ترجمات مختلفة لقواعد البيانات المختلفة. إنها ليست مثالية، ولكنها تتحسن باستمرار، وإذا واجهت مشكلة فيمكنك تقديم إبلاغ عن مشكلة (issue) على GitHub لمساعدتنا على تقديم أداء أفضل.

21.5.2 SELECT

تُعد جملة SELECT هي العمود الفقري للاستعلامات وتؤدي نفس الوظيفة التي تقوم بها select() و mutate() و rename() و relocate()، بالإضافة إلى summarize() كما ستتعلم في القسم التالي.

تمتلك select() و rename() و relocate() ترجمات مباشرة للغاية إلى SELECT لأنها تؤثر فقط على مكان ظهور العمود (إن وجد) إلى جانب اسمه:

planes |> 
  select(tailnum, type, manufacturer, model, year) |> 
  show_query()
#> <SQL>
#> SELECT tailnum, "type", manufacturer, model, "year"
#> FROM planes

planes |> 
  select(tailnum, type, manufacturer, model, year) |> 
  rename(year_built = year) |> 
  show_query()
#> <SQL>
#> SELECT tailnum, "type", manufacturer, model, "year" AS year_built
#> FROM planes

planes |> 
  select(tailnum, type, manufacturer, model, year) |> 
  relocate(manufacturer, model, .before = type) |> 
  show_query()
#> <SQL>
#> SELECT tailnum, manufacturer, model, "type", "year"
#> FROM planes

يوضح لك هذا المثال أيضاً كيفية قيام SQL وإجرائها لإعادة التسمية. في مصطلحات SQL، تُسمى إعادة التسمية بالإسناد المستعار (aliasing) وتتم باستخدام AS. لاحظ أنه على عكس mutate()، فإن الاسم القديم يكون على اليسار والاسم الجديد على اليمين.

في الأمثلة أعلاه، لاحظ أن "year" و "type" ملفوفان بعلامات تنصيص مزدوجة. ذلك لأنها كلمات محجوزة (reserved words) في duckdb، لذا تضع dbplyr علامات تنصيص حولها لتجنب أي إرباك محتمل بين أسماء الأعمدة/الجداول ومعاملات SQL.

عند العمل مع قواعد بيانات أخرى، من المرجح أن ترى كل اسم متغير محاطاً بعلامات تنصيص لأن عدداً قليلاً فقط من حزم العملاء، مثل duckdb، يعرف ما هي جميع الكلمات المحجوزة، لذا يضعون علامات تنصيص حول كل شيء ليكونوا في أمان.

SELECT "tailnum", "type", "manufacturer", "model", "year"
FROM "planes"

تستخدم بعض أنظمة قواعد البيانات الأخرى علامات التنصيص المائلة (backticks) بدلاً من علامات التنصيص المزدوجة:

SELECT `tailnum`, `type`, `manufacturer`, `model`, `year`
FROM `planes

تُعد ترجمات mutate() مباشرة بنفس القدر: حيث يصبح كل متغير تعبيراً جديداً داخل SELECT:

flights |> 
  mutate(
    speed = distance / (air_time / 60)
  ) |> 
  show_query()
#> <SQL>
#> SELECT *, distance / (air_time / 60.0) AS speed
#> FROM flights

سنعود إلى ترجمة المكونات الفردية (مثل /) في قسم 21.6.

21.5.3 FROM

تحدد جملة FROM مصدر البيانات. ستكون غير مثيرة للاهتمام لفترة قصيرة، لأننا نستخدم جداول فردية فقط. ستشاهد أمثلة أكثر تعقيداً بمجرد أن نصل إلى دوال الربط (join functions).

21.5.4 GROUP BY

تُترجم group_by() إلى جملة GROUP BY ⁶ بينما تُترجم summarize() إلى جملة SELECT:

diamonds_db |> 
  group_by(cut) |> 
  summarize(
    n = n(),
    avg_price = mean(price, na.rm = TRUE)
  ) |> 
  show_query()
#> <SQL>
#> SELECT cut, COUNT(*) AS n, AVG(price) AS avg_price
#> FROM diamonds
#> GROUP BY cut

سنعود إلى ما يحدث مع ترجمة n() و mean() في قسم 21.6.

21.5.5 WHERE

تُترجم filter() إلى جملة WHERE:

flights |> 
  filter(dest == "IAH" | dest == "HOU") |> 
  show_query()
#> <SQL>
#> SELECT *
#> FROM flights
#> WHERE (dest = 'IAH' OR dest = 'HOU')

flights |> 
  filter(arr_delay > 0 & arr_delay < 20) |> 
  show_query()
#> <SQL>
#> SELECT *
#> FROM flights
#> WHERE (arr_delay > 0.0 AND arr_delay < 20.0)

هناك بضع تفاصيل مهمة يجب ملاحظتها هنا:

تحول | إلى OR و & إلى AND.
تستخدم SQL الرمز = للمقارنة، وليس ==. لا تحتوي SQL على تعيين (assignment)، لذا لا يوجد مجال للإرباك هناك.
تستخدم SQL علامات التنصيص الفردية '' فقط للنصوص، وليس المزدوجة "". في SQL، تُستخدم "" لتحديد المتغيرات، مثل `` في R.

معامل SQL مفيد آخر هو IN، وهو قريب جداً من %in% في R:

flights |> 
  filter(dest %in% c("IAH", "HOU")) |> 
  show_query()
#> <SQL>
#> SELECT *
#> FROM flights
#> WHERE (dest IN ('IAH', 'HOU'))

تستخدم SQL القيمة NULL بدلاً من NA. تتصرف NULL بشكل مشابه لـ NA. الفرق الرئيسي هو أنه على الرغم من أنها “معدية” في المقارنات والعمليات الحسابية، إلا أنه يتم إسقاطها وتجاهلها بصمت عند التلخيص (summarizing). ستذكرك dbplyr بهذا السلوك في أول مرة تصادفه فيها:

flights |> 
  group_by(dest) |> 
  summarize(delay = mean(arr_delay))
#> Warning: Missing values are always removed in SQL aggregation functions.
#> Use `na.rm = TRUE` to silence this warning
#> This warning is displayed once every 8 hours.
#> # A query:  ?? x 2
#> # Database: DuckDB 1.5.5 [unknown@Linux 6.17.0-1022-azure:R 4.6.1/:memory:]
#>   dest  delay
#>   <chr> <dbl>
#> 1 ATL   11.3 
#> 2 CLT    7.36
#> 3 MCO    5.45
#> 4 MDW   12.4 
#> 5 HOU    7.18
#> 6 SDF   12.7 
#> # ℹ more rows

إذا كنت ترغب في لمعرفة المزيد حول كيفية عمل NULL، فقد تستمتع بقراءة مقال “المنطق ثلاثي القيم في SQL” للمؤلف ماركوس ويناند (Markus Winand).

بشكل عام، يمكنك العمل مع NULL باستخدام الدوال التي تستخدمها لـ NA في R:

flights |> 
  filter(!is.na(dep_delay)) |> 
  show_query()
#> <SQL>
#> SELECT *
#> FROM flights
#> WHERE (NOT((dep_delay IS NULL)))

يوضح استعلام SQL هذا أحد عيوب dbplyr: على الرغم من أن كود SQL صحيح، إلا أنه ليس بسيطاً كما لو كنت ستكتبه بيديك. في هذه الحالة، يمكنك إسقاط الأقواس واستخدام معامل خاص أسهل في القراءة:

WHERE "dep_delay" IS NOT NULL

لاحظ أنه إذا قمت باستخدام filter() على متغير أنشأته باستخدام التلخيص (summarize)، فستولد dbplyr جملة HAVING بدلاً من جملة WHERE. هذه واحدة من خصوصيات SQL: يتم تقييم WHERE قبل SELECT و GROUP BY، لذا تحتاج SQL إلى جملة أخرى يتم تقييمها بعد ذلك.

diamonds_db |> 
  group_by(cut) |> 
  summarize(n = n()) |> 
  filter(n > 100) |> 
  show_query()
#> <SQL>
#> SELECT cut, COUNT(*) AS n
#> FROM diamonds
#> GROUP BY cut
#> HAVING (COUNT(*) > 100.0)

21.5.6 ORDER BY

يتضمن ترتيب الصفوف ترجمة مباشرة من arrange() إلى جملة ORDER BY:

flights |> 
  arrange(year, month, day, desc(dep_delay)) |> 
  show_query()
#> <SQL>
#> SELECT *
#> FROM flights
#> ORDER BY "year", "month", "day", dep_delay DESC

لاحظ كيف تُترجم desc() إلى DESC: هذه واحدة من العديد من دوال dplyr التي استُلهم اسمها مباشرة من SQL.

21.5.7 الاستعلامات الفرعية (Subqueries)

في بعض الأحيان لا يكون من الممكن ترجمة أنبوب تمرير dplyr إلى عبارة SELECT واحدة وتحتاج إلى استخدام استعلام فرعي. الاستعلام الفرعي (subquery) هو مجرد استعلام يُستخدم كمصدر بيانات في جملة FROM، بدلاً من الجدول المعتاد.

تستخدم dbplyr عادة الاستعلامات الفرعية للالتفاف حول قيود SQL. على سبيل المثال، لا يمكن للتعبيرات الموجودة في جملة SELECT الإشارة إلى الأعمدة التي تم إنشاؤها للتو. هذا يعني أن أنبوب تمرير dplyr التالي (الافتراضي) يجب أن يحدث على خطوتين: الاستعلام الأول (الداخلي) يحسب year1 ثم الاستعلام الثاني (الخارجي) يمكنه حساب year2.

flights |> 
  mutate(
    year1 = year + 1,
    year2 = year1 + 1
  ) |> 
  show_query()
#> <SQL>
#> SELECT *, year1 + 1.0 AS year2
#> FROM (
#>   SELECT *, "year" + 1.0 AS year1
#>   FROM flights
#> ) AS q01

ستشاهد هذا أيضاً إذا حاولت تطبيق filter() على متغير أنشأته للتو. تذكر، أنه على الرغم من كتابة WHERE بعد SELECT، إلا أنه يتم تقييمها قبلها، لذا نحتاج إلى استعلام فرعي في هذا المثال (الافتراضي):

flights |> 
  mutate(year1 = year + 1) |> 
  filter(year1 == 2014) |> 
  show_query()
#> <SQL>
#> SELECT *
#> FROM (
#>   SELECT *, "year" + 1.0 AS year1
#>   FROM flights
#> ) AS q01
#> WHERE (year1 = 2014.0)

في بعض الأحيان ستنشئ dbplyr استعلاماً فرعياً حيث لا تكون هناك حاجة إليه لأنها لا تعرف بعد كيفية تحسين تلك الترجمة. مع تحسن dbplyr بمرور الوقت، ستصبح هذه الحالات أكثر ندرة ولكنها ربما لن تختفي تماماً أبداً.

21.5.8 عمليات الربط (Joins)

إذا كنت معتاداً على دوال الربط في dplyr، فإن عمليات الربط في SQL متشابهة جداً. إليك مثالاً بسيطاً:

flights |> 
  left_join(planes |> rename(year_built = year), join_by(tailnum)) |> 
  show_query()
#> <SQL>
#> SELECT
#>   flights.*,
#>   planes."year" AS year_built,
#>   "type",
#>   manufacturer,
#>   model,
#>   engines,
#>   seats,
#>   speed,
#>   engine
#> FROM flights
#> LEFT JOIN planes
#>   ON (flights.tailnum = planes.tailnum)

الشيء الرئيسي الذي يجب ملاحظته هنا هو بناء الجملة (syntax): تستخدم عمليات الربط في SQL جمل فرعية من جملة FROM لإحضار جداول إضافية، باستخدام ON لتحديد كيفية ارتباط الجداول.

أسماء dplyr لهذه الدوال مرتبطة بشكل وثيق بـ SQL لدرجة أنه يمكنك بسهولة تخمين كود SQL المكافئ لـ inner_join() و right_join() و full_join():

SELECT flights.*, "type", manufacturer, model, engines, seats, speed
FROM flights
INNER JOIN planes ON (flights.tailnum = planes.tailnum)

SELECT flights.*, "type", manufacturer, model, engines, seats, speed
FROM flights
RIGHT JOIN planes ON (flights.tailnum = planes.tailnum)

SELECT flights.*, "type", manufacturer, model, engines, seats, speed
FROM flights
FULL JOIN planes ON (flights.tailnum = planes.tailnum)

من المرجح أن تحتاج إلى العديد من عمليات الربط عند العمل مع البيانات من قاعدة بيانات. ذلك لأن جداول قواعد البيانات غالباً ما تُخزن في صيغة عالية التنظيم للغاية (highly normalized)، حيث تُخزن كل “حقيقة” في مكان واحد وللحفاظ على مجموعة بيانات كاملة للتحليل، تحتاج إلى التنقل عبر شبكة معقدة من الجداول المرتبطة بمفاتيح أساسية وأجنبية (primary and foreign keys). إذا واجهت هذا السيناريو، فإن حزمة dm، التي طورها توبياس شيفيرديكر (Tobias Schieferdecker) وكيريل مولر (Kirill Müller) وداركو بيرجانت (Darko Bergant)، تُعد منقذاً للحياة. يمكنها تلقائياً تحديد العلاقات بين الجداول باستخدام القيود التي يقدمها مسؤولو قواعد البيانات (DBAs) غالباً، وعرض العلاقات بصرية حتى تتمكن من رؤية ما يحدث، وتوليد عمليات الربط التي تحتاجها لربط جدول بآخر.

21.5.9 أفعال أخرى

تترجم dbplyr أيضاً أفعالاً أخرى مثل distinct() و slice_*() و intersect()، ومجموعة متزايدة من دوال tidyr مثل pivot_longer() و pivot_wider(). أسهل طريقة لرؤية المجموعة الكاملة لما هو متاح حالياً هي زيارة موقع dbplyr الإلكتروني: https://dbplyr.tidyverse.org/reference/.

21.5.10 تمارين

إلى ماذا تُترجم distinct()؟ وماذا عن head()؟
اشرح ما يفعله كل استعلام من استعلامات SQL التالية وحاول إعادة إنشائها باستخدام dbplyr.
```
SELECT * 
FROM flights
WHERE dep_delay < arr_delay

SELECT *, distance / (air_time / 60) AS speed
FROM flights
```

21.6 ترجمات الدوال

حتى الآن ركزنا على الصورة الكبيرة لكيفية ترجمة أفعال dplyr إلى جمل الاستعلام. الآن سنتعمق قليلاً ونتحدث عن ترجمة دوال R التي تعمل مع الأعمدة الفردية، على سبيل المثال، ماذا يحدث عندما تستخدم mean(x) داخل summarize()؟

للمساعدة في رؤية ما يحدث، سنستخدم زوجاً من الدوال المساعدة الصغيرة التي تشغل summarize() أو mutate() وتظهر كود SQL المولد. سيجعل ذلك استكشاف بعض الاختلافات ورؤية كيف يمكن أن تختلف الملخصات والتحويلات أسهل قليلاً.

summarize_query <- function(df, ...) {
  df |> 
    summarize(...) |> 
    show_query()
}
mutate_query <- function(df, ...) {
  df |> 
    mutate(..., .keep = "none") |> 
    show_query()
}

دعنا نغوص في بعض الملخصات! بالنظر إلى الكود أدناه ستلاحظ أن بعض دوال التلخيص، مثل mean()، لها ترجمة بسيطة نسبياً بينما البعض الآخر، مثل median()، أشد تعقيداً بكثير. تكون التعقيدات أعلى عادةً للعمليات الشائعة في الإحصاء ولكنها أقل شيوعاً في قواعد البيانات.

flights |> 
  group_by(year, month, day) |>  
  summarize_query(
    mean = mean(arr_delay, na.rm = TRUE),
    median = median(arr_delay, na.rm = TRUE)
  )
#> ! Grouped output by "year" and "month".
#> ℹ Override behaviour and silence this message with the `.groups` argument.
#> ℹ Or use `.by` instead of `group_by()`.
#> <SQL>
#> SELECT
#>   "year",
#>   "month",
#>   "day",
#>   AVG(arr_delay) AS mean,
#>   MEDIAN(arr_delay) AS median
#> FROM flights
#> GROUP BY "year", "month", "day"

تصبح ترجمة دوال التلخيص أكثر تعقيداً عندما تستخدمها داخل mutate() لأنها يجب أن تتحول إلى ما يُسمى بدوال النافذة (window). في SQL، تحول دالة التجميع العادية إلى دالة نافذة عن طريق إضافة OVER بعدها:

flights |> 
  group_by(year, month, day) |>  
  mutate_query(
    mean = mean(arr_delay, na.rm = TRUE),
  )
#> <SQL>
#> SELECT
#>   "year",
#>   "month",
#>   "day",
#>   AVG(arr_delay) OVER (PARTITION BY "year", "month", "day") AS mean
#> FROM flights

في SQL، تُستخدم جملة GROUP BY حصرياً للملخصات لذا يمكنك رؤية أن التجميع قد انتقل من جملة GROUP BY إلى OVER.

تتضمن دوال النافذة جميع الدوال التي تنظر إلى الأمام أو الخلف، مثل lead() و lag() والتي تنظر إلى القيمة “السابقة” أو “التالية” على التوالي:

flights |> 
  group_by(dest) |>  
  arrange(time_hour) |> 
  mutate_query(
    lead = lead(arr_delay),
    lag = lag(arr_delay)
  )
#> <SQL>
#> SELECT
#>   dest,
#>   LEAD(arr_delay, 1, NULL) OVER (PARTITION BY dest ORDER BY time_hour) AS lead,
#>   LAG(arr_delay, 1, NULL) OVER (PARTITION BY dest ORDER BY time_hour) AS lag
#> FROM flights
#> ORDER BY time_hour

هنا من المهم استخدام arrange() لترتيب البيانات، لأن جداول SQL ليس لها ترتيب جوهري. في الواقع، إذا لم تستخدم arrange() فقد تحصل على الصفوف بترتيب مختلف في كل مرة! لاحظ أنه بالنسبة لدوال النافذة، تتكرر معلومات الترتيب: فجملة ORDER BY في الاستعلام الرئيسي لا تنطبق تلقائياً على دوال النافذة.

دالة SQL مهمة أخرى هي CASE WHEN. تُستخدم كترجمة لـ if_else() و case_when()، دالة dplyr التي ألهمتها مباشرة. إليك زوجاً من الأمثلة البسيطة:

flights |> 
  mutate_query(
    description = if_else(arr_delay > 0, "delayed", "on-time")
  )
#> <SQL>
#> SELECT CASE WHEN (arr_delay > 0.0) THEN 'delayed' WHEN NOT (arr_delay > 0.0) THEN 'on-time' END AS description
#> FROM flights
flights |> 
  mutate_query(
    description = 
      case_when(
        arr_delay < -5 ~ "early", 
        arr_delay < 5 ~ "on-time",
        arr_delay >= 5 ~ "late"
      )
  )
#> <SQL>
#> SELECT CASE
#> WHEN (arr_delay < -5.0) THEN 'early'
#> WHEN (arr_delay < 5.0) THEN 'on-time'
#> WHEN (arr_delay >= 5.0) THEN 'late'
#> END AS description
#> FROM flights

تُستخدم CASE WHEN أيضاً لبعض الدوال الأخرى التي ليس لها ترجمة مباشرة من R إلى SQL. مثال جيد على ذلك هو cut():

flights |> 
  mutate_query(
    description =  cut(
      arr_delay, 
      breaks = c(-Inf, -5, 5, Inf), 
      labels = c("early", "on-time", "late")
    )
  )
#> <SQL>
#> SELECT CASE
#> WHEN (arr_delay <= -5.0) THEN 'early'
#> WHEN (arr_delay <= 5.0) THEN 'on-time'
#> WHEN (arr_delay > 5.0) THEN 'late'
#> END AS description
#> FROM flights

تترجم dbplyr أيضاً دوال المعالجة الشائعة للنصوص والتاريخ والوقت، والتي يمكنك التعرف عليها في vignette("translation-function", package = "dbplyr"). ترجمات dbplyr ليست مثالية بالتأكيد، وهناك العديد من دوال R التي لم تُترجم بعد، ولكن dbplyr تقوم بعمل جيد بشكل مثير للدهشة في تغطية الدوال التي ستستخدمها معظم الوقت.

21.7 ملخص

في هذا الفصل تعلمت كيفية الوصول إلى البيانات من قواعد البيانات. ركزنا على dbplyr، وهي “محرك خلفي” لـ dplyr يتيح لك كتابة كود dplyr الذي تألفه، وترجمته تلقائياً إلى SQL. استخدمنا تلك الترجمة لتعليمك القليل من SQL؛ ومن المهم تعلم بعض من SQL لأنها اللغة الأكثر استخداماً للعمل مع البيانات، ومعرفة البعض منها سيسهل عليك التواصل مع الأشخاص الآخرين المهتمين بالبيانات والذين لا يستخدمون R.

إذا أنهيت هذا الفصل وترغب في معرفة المزيد عن SQL، فنحن نوصي بمرجعين:

SQL لعلماء البيانات تأليف رينيه م. ب. تيت (Renée M. P. Teate)، وهو مقدمة لـ SQL مصممة خصيصاً لاحتياجات علماء البيانات، ويتضمن أمثلة على نوع البيانات مترابطة بشكل كبير والتي من المرجح أن تصادفها في المؤسسات الحقيقية.
Practical SQL تأليف أنتوني ديباروس (Anthony DeBarros)، وكُتب من منظور صحفي بيانات (عالم بيانات متخصص في سرد قصص مقنعة) ويتعمق أكثر في إدخال بياناتك إلى قاعدة بيانات وتشغيل نظام DBMS الخاص بك.

في الفصل التالي، نتعلم عن محرك خلفي آخر لـ dplyr للعمل مع البيانات الكبيرة: arrow. صُممت Arrow للعمل مع الملفات الكبيرة على القرص، وهي مكمل طبيعي لقواعد البيانات.

تٌنطق SQL إما كحروف منفصلة “إس-كيو-إل” أو ككلمة واحدة “sequel”.↩︎
عادةً ما تكون هذه هي الدالة الوحيدة التي ستستخدمها من حزمة العميل، لذا نوصي باستخدام :: لسحب هذه الدالة المحددة، بدلاً من تحميل الحزمة بالكامل باستخدام library().↩︎
على الأقل، جميع الجداول التي لديك إذن لرؤيتها.↩︎
بشكل مربك، واعتمادًا على السياق، تعتبر SELECT إما عبارة أو جملة. لتجنب هذا الارتباك، سنستخدم عمومًا استعلام SELECT بدلاً من عبارة SELECT.↩︎
حسناً، من الناحية الفنية، تعتبر SELECT فقط هي المطلوبة، حيث يمكنك كتابة استعلامات مثل SELECT 1+1 لإجراء حسابات أساسية. ولكن إذا كنت تريد العمل مع البيانات (كما تفعل دائمًا!) فستحتاج أيضًا إلى جملة FROM.↩︎
ليس هذا مجرد مصادفة: فقد استُلهم اسم دالة dplyr من جملة SQL.↩︎