JupySQL وchDB

كيفية الاستعلام عن chDB باستخدام JupySQL في دفاتر Jupyter وIPython

JupySQL هي مكتبة Python تتيح لك تشغيل SQL في دفاتر Jupyter وواجهة IPython. في هذا الدليل، سنتعلّم كيفية الاستعلام عن البيانات باستخدام chDB وJupySQL.

​

الإعداد

لنبدأ أولًا بإنشاء بيئة افتراضية:

python -m venv .venv
source .venv/bin/activate

ثم سنقوم بتثبيت JupySQL وIPython وJupyter Lab:

pip install jupysql ipython jupyterlab

يمكننا استخدام JupySQL في IPython، ويمكننا تشغيله بتنفيذ:

ipython

أو في Jupyter Lab، عبر تشغيل:

jupyter lab

إذا كنت تستخدم Jupyter Lab، فستحتاج إلى إنشاء دفتر قبل متابعة بقية الدليل.

​

تنزيل مجموعة بيانات

سنستخدم مجموعة بيانات سيارات الأجرة في مدينة نيويورك، التي تتضمن نحو 3 ملايين رحلة، إلى جانب أجرة كل رحلة وإكراميتها وحيّ الانطلاق منها. تتوزع الرحلات على عدة ملفات TSV، لذا لنبدأ بتنزيلها:

from urllib.request import urlretrieve

base = "https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi"
for n in range(3):
  _ = urlretrieve(
    f"{base}/trips_{n}.gz",
    f"trips_{n}.gz",
  )

​

إعداد chDB وJupySQL

بعد ذلك، لنستورد الوحدة dbapi الخاصة بـ chDB:

from chdb import dbapi

وسننشىء اتصالًا بـ chDB. ستُحفَظ أي بيانات نُخزّنها بشكل دائم في المجلد taxi.chdb:

conn = dbapi.connect(path="taxi.chdb")

لنحمّل الآن الأمر السحري sql وننشئ اتصالًا بـ chDB:

%load_ext sql
%sql conn --alias chdb

بعد ذلك، سنعرض حدّ العرض كي لا تُقتطع نتائج الاستعلامات:

%config SqlMagic.displaylimit = None

​

الاستعلام عن البيانات في ملفات TSV

نزّلنا مجموعة من الملفات التي تبدأ بالبادئة trips_. لنستخدم عبارة DESCRIBE لفهم المخطط:

%%sql
DESCRIBE file('trips_*.gz')
SETTINGS describe_compact_output=1,
         schema_inference_make_columns_nullable=0

+--------------------+----------+
|        name        |   type   |
+--------------------+----------+
|      trip_id       |  Int64   |
|     vendor_id      |  Int64   |
|    pickup_date     |   Date   |
|  pickup_datetime   | DateTime |
|    dropoff_date    |   Date   |
|  dropoff_datetime  | DateTime |
| store_and_fwd_flag |  Int64   |
|    rate_code_id    |  Int64   |
+--------------------+----------+
(40 more rows)

يمكننا أيضًا كتابة استعلام SELECT مباشرةً على هذه الملفات لمعرفة شكل البيانات:

%%sql
SELECT trip_id, pickup_datetime, pickup_ntaname,
       trip_distance, fare_amount, tip_amount
FROM file('trips_*.gz')
LIMIT 3
SETTINGS schema_inference_make_columns_nullable=0

+------------+---------------------+----------------------------------------+---------------+-------------+------------+
|  trip_id   |   pickup_datetime   |             pickup_ntaname             | trip_distance | fare_amount | tip_amount |
+------------+---------------------+----------------------------------------+---------------+-------------+------------+
| 1199999902 | 2015-07-07 19:45:07 |      Lenox Hill-Roosevelt Island       |      2.59     |     14.5    |    3.26    |
| 1199999919 | 2015-07-07 20:26:29 |                Airport                 |      2.4      |      9      |     0      |
| 1199999944 | 2015-07-07 21:25:09 | SoHo-TriBeCa-Civic Center-Little Italy |      5.13     |      20     |     3      |
+------------+---------------------+----------------------------------------+---------------+-------------+------------+

إذا ألقينا نظرة على المخطط مرة أخرى، فسنرى أن بعض الأعمدة المتعلقة بالمبالغ المالية — trip_distance وfare_amount وtip_amount — استُنتجت كنوع String بدلًا من نوع رقمي. وسنصحّح ذلك عند استيراد البيانات إلى جدول.

​

استيراد ملفات TSV إلى chDB

سنخزّن الآن بيانات ملفات TSV هذه في جدول. لا تحتفظ قاعدة البيانات الافتراضية بالبيانات على القرص، لذا نحتاج أولًا إلى إنشاء قاعدة بيانات أخرى:

%sql CREATE DATABASE taxi

والآن سننشئ جدولًا باسم trips، يُستمد مخططه من بنية البيانات في ملفات TSV. سنستخدم عبارة REPLACE لتحويل الأعمدة المتعلقة بالمبالغ المالية إلى Float64، ودالة transform لتحويل العمود الرقمي pickup_borocode إلى اسم المنطقة الإدارية المقابل:

%%sql
CREATE TABLE taxi.trips
ENGINE = MergeTree
ORDER BY pickup_datetime AS
SELECT * REPLACE (
    toFloat64OrZero(trip_distance) AS trip_distance,
    toFloat64OrZero(fare_amount) AS fare_amount,
    toFloat64OrZero(tip_amount) AS tip_amount,
    toFloat64OrZero(total_amount) AS total_amount
  ),
  transform(pickup_borocode, [1, 2, 3, 4, 5],
            ['Manhattan', 'Bronx', 'Brooklyn', 'Queens', 'Staten Island'],
            'Unknown') AS pickup_borough
FROM file('trips_*.gz')
SETTINGS schema_inference_make_columns_nullable=0

لنتحقق سريعًا من البيانات في جدولنا:

%sql SELECT count() AS trips FROM taxi.trips

+---------+
|  trips  |
+---------+
| 3000317 |
+---------+

ما يزيد قليلًا على 3 ملايين رحلة — لنُضِف أيضًا جدولًا ثانيًا. تقسّم لجنة سيارات الأجرة والليموزين في مدينة نيويورك المدينة إلى مناطق لسيارات الأجرة، ويربط ملف lookup كل منطقة بمنطقتها الإدارية. لننزّل هذا الملف:

_ = urlretrieve(
    f"{base}/taxi_zone_lookup.csv",
    "taxi_zone_lookup.csv",
)

ثم أنشئ جدولًا باسم zones باستخدام محتوى ملف CSV:

%%sql
CREATE TABLE taxi.zones
ENGINE = MergeTree
ORDER BY LocationID AS
SELECT * FROM file('taxi_zone_lookup.csv')
SETTINGS schema_inference_make_columns_nullable=0

بعد اكتمال التشغيل، يمكننا إلقاء نظرة على البيانات التي استوردناها:

%sql SELECT * FROM taxi.zones LIMIT 5

+------------+---------------+-------------------------+--------------+
| LocationID |    Borough    |           Zone          | service_zone |
+------------+---------------+-------------------------+--------------+
|     1      |      EWR      |      Newark Airport     |     EWR      |
|     2      |     Queens    |       Jamaica Bay       |  Boro Zone   |
|     3      |     Bronx     | Allerton/Pelham Gardens |  Boro Zone   |
|     4      |   Manhattan   |      Alphabet City      | Yellow Zone  |
|     5      | Staten Island |      Arden Heights      |  Boro Zone   |
+------------+---------------+-------------------------+--------------+

​

الاستعلام عن chDB

اكتمل إدخال البيانات، والآن حان وقت الجزء الممتع: الاستعلام عنها!

ينقسم كل منطقة إدارية إلى عدد مختلف من مناطق سيارات الأجرة. سنكتب استعلامًا يربط بين الجدولين لمعرفة عدد الرحلات التي بدأت في كل منطقة إدارية، ومتوسط عدد الرحلات لكل منطقة سيارات أجرة:

%%sql
SELECT pickup_borough AS borough,
       zone_count,
       count() AS trips,
       round(count() / zone_count) AS trips_per_zone
FROM taxi.trips
JOIN (
    SELECT Borough, count() AS zone_count
    FROM taxi.zones
    GROUP BY Borough
) AS zones ON pickup_borough = zones.Borough
GROUP BY borough, zone_count
ORDER BY trips DESC

+---------------+------------+---------+----------------+
|    borough    | zone_count |  trips  | trips_per_zone |
+---------------+------------+---------+----------------+
|   Manhattan   |     69     | 2713990 |    39333.0     |
|     Queens    |     69     |  187737 |     2721.0     |
|    Brooklyn   |     61     |  52445  |     860.0      |
|    Unknown    |     2      |  43802  |    21901.0     |
|     Bronx     |     43     |   2300  |      53.0      |
| Staten Island |     20     |    43   |      2.0       |
+---------------+------------+---------+----------------+

تضم مانهاتن وكوينز العدد نفسه من مناطق سيارات الأجرة، لكن مانهاتن تسجّل أكثر من 14 ضعفًا من عمليات الركوب.

​

حفظ الاستعلامات

يمكننا حفظ الاستعلامات باستخدام المعلمة --save في السطر نفسه لأمر %%sql السحري. تعني المعلمة --no-execute تخطي تنفيذ الاستعلام.

%%sql --save tips_by_neighborhood --no-execute
SELECT pickup_ntaname AS neighborhood,
       count() AS trips,
       round(avg(tip_amount), 2) AS avg_tip
FROM taxi.trips
WHERE fare_amount > 0 AND pickup_ntaname != ''
GROUP BY neighborhood
ORDER BY avg_tip DESC

عند تشغيل استعلام محفوظ، يُحوَّل إلى تعبير جدول شائع (CTE) قبل تنفيذه. في الاستعلام التالي، نحسب الأحياء ذات أعلى متوسط للإكرامية:

%sql SELECT * FROM tips_by_neighborhood ORDER BY avg_tip DESC LIMIT 5

+-----------------------------------+-------+---------+
|            neighborhood           | trips | avg_tip |
+-----------------------------------+-------+---------+
| New Springville-Bloomfield-Travis |   2   |   35.0  |
|       New Dorp-Midland Beach      |   2   |  23.74  |
|      New Brighton-Silver Lake     |   3   |  16.67  |
|           Newark Airport          |  201  |  11.89  |
|   Grymes Hill-Clifton-Fox Hills   |   1   |   11.3  |
+-----------------------------------+-------+---------+

تتصدّر القائمة أحياء لم تُسجَّل فيها سوى بضع رحلات، لذا قد تؤدي رحلة واحدة مرتفعة التكلفة إلى انحراف المتوسط. لنستبعدها باستخدام عامل تصفية.

​

الاستعلام باستخدام المعلمات

يمكننا أيضًا استخدام المعلمات في استعلاماتنا. المعلمات هي مجرد متغيرات عادية:

min_trips = 10000

بعد ذلك، يمكننا استخدام صيغة {{variable}} في استعلامنا. يعرض الاستعلام التالي الأحياء التي لديها أعلى متوسط للإكرامية بين الأحياء التي تضم أكثر من 10,000 رحلة:

%%sql
SELECT * FROM tips_by_neighborhood
WHERE trips >= {{min_trips}}
ORDER BY avg_tip DESC
LIMIT 10

+----------------------------------------+--------+---------+
|              neighborhood              | trips  | avg_tip |
+----------------------------------------+--------+---------+
|                Airport                 | 151171 |   4.92  |
|   Battery Park City-Lower Manhattan    | 89110  |   2.16  |
|         North Side-South Side          | 11152  |   1.79  |
| SoHo-TriBeCa-Civic Center-Little Italy | 144887 |   1.65  |
|               Chinatown                | 54780  |   1.65  |
|            Lower East Side             | 15753  |   1.64  |
|              East Village              | 99881  |   1.61  |
|  Hunters Point-Sunnyside-West Maspeth  | 10054  |   1.58  |
|        Turtle Bay-East Midtown         | 197035 |   1.57  |
|              West Village              | 210369 |   1.54  |
+----------------------------------------+--------+---------+

تحظى رحلات الاستقبال من المطار بأعلى الإكراميات بفارق كبير، إذ إن الرحلات الطويلة إلى المدينة تتراكم تكلفتها.

​

رسم المدرجات التكرارية

يوفّر JupySQL أيضًا إمكانات محدودة لرسم المخططات. يمكننا إنشاء مخططات صندوقية أو مدرجات تكرارية.

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

%%sql --save trip_distances --no-execute
SELECT trip_distance
FROM taxi.trips
WHERE trip_distance > 0 AND trip_distance < 20

يمكننا بعد ذلك إنشاء مدرج تكراري بتنفيذ ما يلي:

from sql.ggplot import ggplot, geom_histogram, aes

plot = (
  ggplot(
    table="trip_distances",
    with_="trip_distances",
    mapping=aes(x="trip_distance", fill="#69f0ae", color="#fff"),
  ) + geom_histogram(bins=50)
)

معظم الرحلات قصيرة، وتتراوح بين ميل واحد وثلاثة أميال، مع امتداد طويل للرحلات المتجهة إلى المطار.

​

ذو صلة

• مقدمة إلى JupySQL مع chDB (YouTube)