JupySQL وchDB - ClickHouse Documentation

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، فستحتاج إلى إنشاء دفتر قبل متابعة بقية الدليل.

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

سنستخدم إحدى مجموعات بيانات tennis_atp الخاصة بـ Jeff Sackmann، والتي تتضمن بيانات وصفية عن اللاعبين وتصنيفاتهم بمرور الوقت. لنبدأ بتنزيل ملفات التصنيف:

from urllib.request import urlretrieve

files = ['00s', '10s', '20s', '70s', '80s', '90s', 'current']
base = "https://raw.githubusercontent.com/JeffSackmann/tennis_atp/master"
for file in files:
  _ = urlretrieve(
    f"{base}/atp_rankings_{file}.csv",
    f"atp_rankings_{file}.csv",
  )

إعداد chDB وJupySQL

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

from chdb import dbapi

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

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

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

%load_ext sql
%sql conn --alias chdb

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

%config SqlMagic.displaylimit = None

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

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

+--------------+-------+
|     name     |  type |
+--------------+-------+
| ranking_date | Int64 |
|     rank     | Int64 |
|    player    | Int64 |
|    points    | Int64 |
+--------------+-------+

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

%sql SELECT * FROM file('atp_rankings*.csv') LIMIT 1

+--------------+------+--------+--------+
| ranking_date | rank | player | points |
+--------------+------+--------+--------+
|   20000110   |  1   | 101736 |  4135  |
+--------------+------+--------+--------+

تنسيق البيانات غريب بعض الشيء. لنصحّح تنسيق ذلك التاريخ ونستخدم عبارة REPLACE لإرجاع ranking_date بعد تصحيحه:

%%sql
SELECT * REPLACE (
  toDate(parseDateTime32BestEffort(toString(ranking_date))) AS ranking_date
)
FROM file('atp_rankings*.csv')
LIMIT 10
SETTINGS schema_inference_make_columns_nullable=0

+--------------+------+--------+--------+
| ranking_date | rank | player | points |
+--------------+------+--------+--------+
|  2000-01-10  |  1   | 101736 |  4135  |
|  2000-01-10  |  2   | 102338 |  2915  |
|  2000-01-10  |  3   | 101948 |  2419  |
|  2000-01-10  |  4   | 103017 |  2184  |
|  2000-01-10  |  5   | 102856 |  2169  |
|  2000-01-10  |  6   | 102358 |  2107  |
|  2000-01-10  |  7   | 102839 |  1966  |
|  2000-01-10  |  8   | 101774 |  1929  |
|  2000-01-10  |  9   | 102701 |  1846  |
|  2000-01-10  |  10  | 101990 |  1739  |
+--------------+------+--------+--------+

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

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

%sql CREATE DATABASE atp

والآن سننشئ جدولًا باسم rankings، وسيُستمد مخططه من بنية البيانات في ملفات CSV:

%%sql
CREATE TABLE atp.rankings
ENGINE=MergeTree
ORDER BY ranking_date AS
SELECT * REPLACE (
  toDate(parseDateTime32BestEffort(toString(ranking_date))) AS ranking_date
)
FROM file('atp_rankings*.csv')
SETTINGS schema_inference_make_columns_nullable=0

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

%sql SELECT * FROM atp.rankings LIMIT 10

+--------------+------+--------+--------+
| ranking_date | rank | player | points |
+--------------+------+--------+--------+
|  2000-01-10  |  1   | 101736 |  4135  |
|  2000-01-10  |  2   | 102338 |  2915  |
|  2000-01-10  |  3   | 101948 |  2419  |
|  2000-01-10  |  4   | 103017 |  2184  |
|  2000-01-10  |  5   | 102856 |  2169  |
|  2000-01-10  |  6   | 102358 |  2107  |
|  2000-01-10  |  7   | 102839 |  1966  |
|  2000-01-10  |  8   | 101774 |  1929  |
|  2000-01-10  |  9   | 102701 |  1846  |
|  2000-01-10  |  10  | 101990 |  1739  |
+--------------+------+--------+--------+

يبدو هذا جيدًا - فالنتيجة، كما هو متوقع، هي نفسها عند الاستعلام من ملفات CSV مباشرةً. سنتبع العملية نفسها مع البيانات الوصفية للاعب. هذه المرة، توجد جميع البيانات في ملف CSV واحد، لذا لننزّل هذا الملف:

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

ثم أنشئ جدولًا باسم players استنادًا إلى محتويات ملف CSV. وسنُهيِّئ أيضًا الحقل dob ليصبح من النوع Date32.

في ClickHouse، لا يدعم النوع Date إلا التواريخ بدءًا من عام 1970. وبما أن العمود dob يحتوي على تواريخ تسبق عام 1970، فسنستخدم النوع Date32 بدلًا منه.

%%sql
CREATE TABLE atp.players
Engine=MergeTree
ORDER BY player_id AS
SELECT * REPLACE (
  makeDate32(
    toInt32OrNull(substring(toString(dob), 1, 4)),
    toInt32OrNull(substring(toString(dob), 5, 2)),
    toInt32OrNull(substring(toString(dob), 7, 2))
  )::Nullable(Date32) AS dob
)
FROM file('atp_players.csv')
SETTINGS schema_inference_make_columns_nullable=0

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

%sql SELECT * FROM atp.players LIMIT 10

+-----------+------------+-----------+------+------------+-----+--------+-------------+
| player_id | name_first | name_last | hand |    dob     | ioc | height | wikidata_id |
+-----------+------------+-----------+------+------------+-----+--------+-------------+
|   100001  |  Gardnar   |   Mulloy  |  R   | 1913-11-22 | USA |  185   |    Q54544   |
|   100002  |   Pancho   |   Segura  |  R   | 1921-06-20 | ECU |  168   |    Q54581   |
|   100003  |   Frank    |  Sedgman  |  R   | 1927-10-02 | AUS |  180   |   Q962049   |
|   100004  |  Giuseppe  |   Merlo   |  R   | 1927-10-11 | ITA |   0    |   Q1258752  |
|   100005  |  Richard   |  Gonzalez |  R   | 1928-05-09 | USA |  188   |    Q53554   |
|   100006  |   Grant    |   Golden  |  R   | 1929-08-21 | USA |  175   |   Q3115390  |
|   100007  |    Abe     |   Segal   |  L   | 1930-10-23 | RSA |   0    |   Q1258527  |
|   100008  |    Kurt    |  Nielsen  |  R   | 1930-11-19 | DEN |   0    |   Q552261   |
|   100009  |   Istvan   |   Gulyas  |  R   | 1931-10-14 | HUN |   0    |    Q51066   |
|   100010  |    Luis    |   Ayala   |  R   | 1932-09-18 | CHI |  170   |   Q1275397  |
+-----------+------------+-----------+------+------------+-----+--------+-------------+

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

اكتملت عملية إدخال البيانات، والآن حان وقت الجزء الممتع: الاستعلام عن البيانات! يحصل لاعبو التنس على نقاط بناءً على مستوى أدائهم في البطولات التي يشاركون فيها. تُحتسب نقاط كل لاعب على أساس فترة متحركة تمتد 52 أسبوعًا. سنكتب استعلامًا يعثر على الحد الأقصى للنقاط التي جمعها كل لاعب، إلى جانب ترتيبه في ذلك الوقت:

%%sql
SELECT name_first, name_last,
       max(points) as maxPoints,
       argMax(rank, points) as rank,
       argMax(ranking_date, points) as date
FROM atp.players
JOIN atp.rankings ON rankings.player = players.player_id
GROUP BY ALL
ORDER BY maxPoints DESC
LIMIT 10

+------------+-----------+-----------+------+------------+
| name_first | name_last | maxPoints | rank |    date    |
+------------+-----------+-----------+------+------------+
|   Novak    |  Djokovic |   16950   |  1   | 2016-06-06 |
|   Rafael   |   Nadal   |   15390   |  1   | 2009-04-20 |
|    Andy    |   Murray  |   12685   |  1   | 2016-11-21 |
|   Roger    |  Federer  |   12315   |  1   | 2012-10-29 |
|   Daniil   |  Medvedev |   10780   |  2   | 2021-09-13 |
|   Carlos   |  Alcaraz  |    9815   |  1   | 2023-08-21 |
|  Dominic   |   Thiem   |    9125   |  3   | 2021-01-18 |
|   Jannik   |   Sinner  |    8860   |  2   | 2024-05-06 |
|  Stefanos  | Tsitsipas |    8350   |  3   | 2021-09-20 |
| Alexander  |   Zverev  |    8240   |  4   | 2021-08-23 |
+------------+-----------+-----------+------+------------+

من المثير للاهتمام حقًا أن بعض اللاعبين في هذه القائمة جمعوا عددًا كبيرًا من النقاط من دون أن يحتلوا المركز الأول بهذا الرصيد من النقاط.

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

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

%%sql --save best_points --no-execute
SELECT name_first, name_last,
       max(points) as maxPoints,
       argMax(rank, points) as rank,
       argMax(ranking_date, points) as date
FROM atp.players
JOIN atp.rankings ON rankings.player = players.player_id
GROUP BY ALL
ORDER BY maxPoints DESC

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

%sql select * FROM best_points WHERE rank=1

+-------------+-----------+-----------+------+------------+
|  name_first | name_last | maxPoints | rank |    date    |
+-------------+-----------+-----------+------+------------+
|    Novak    |  Djokovic |   16950   |  1   | 2016-06-06 |
|    Rafael   |   Nadal   |   15390   |  1   | 2009-04-20 |
|     Andy    |   Murray  |   12685   |  1   | 2016-11-21 |
|    Roger    |  Federer  |   12315   |  1   | 2012-10-29 |
|    Carlos   |  Alcaraz  |    9815   |  1   | 2023-08-21 |
|     Pete    |  Sampras  |    5792   |  1   | 1997-08-11 |
|    Andre    |   Agassi  |    5652   |  1   | 1995-08-21 |
|   Lleyton   |   Hewitt  |    5205   |  1   | 2002-08-12 |
|   Gustavo   |  Kuerten  |    4750   |  1   | 2001-09-10 |
| Juan Carlos |  Ferrero  |    4570   |  1   | 2003-10-20 |
|    Stefan   |   Edberg  |    3997   |  1   | 1991-02-25 |
|     Jim     |  Courier  |    3973   |  1   | 1993-08-23 |
|     Ivan    |   Lendl   |    3420   |  1   | 1990-02-26 |
|     Ilie    |  Nastase  |     0     |  1   | 1973-08-27 |
+-------------+-----------+-----------+------+------------+

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

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

rank = 10

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

%%sql
SELECT name_first, name_last,
       MIN(ranking_date) AS earliest_date,
       MAX(ranking_date) AS most_recent_date,
       most_recent_date - earliest_date AS days,
       1 + (days/7) AS weeks
FROM atp.rankings
JOIN atp.players ON players.player_id = rankings.player
WHERE rank <= {{rank}}
GROUP BY ALL
ORDER BY days
LIMIT 10

+------------+-----------+---------------+------------------+------+-------+
| name_first | name_last | earliest_date | most_recent_date | days | weeks |
+------------+-----------+---------------+------------------+------+-------+
|    Alex    | Metreveli |   1974-06-03  |    1974-06-03    |  0   |   1   |
|   Mikael   |  Pernfors |   1986-09-22  |    1986-09-22    |  0   |   1   |
|   Felix    |  Mantilla |   1998-06-08  |    1998-06-08    |  0   |   1   |
|   Wojtek   |   Fibak   |   1977-07-25  |    1977-07-25    |  0   |   1   |
|  Thierry   |  Tulasne  |   1986-08-04  |    1986-08-04    |  0   |   1   |
|   Lucas    |  Pouille  |   2018-03-19  |    2018-03-19    |  0   |   1   |
|    John    | Alexander |   1975-12-15  |    1975-12-15    |  0   |   1   |
|  Nicolas   |   Massu   |   2004-09-13  |    2004-09-20    |  7   |   2   |
|   Arnaud   |  Clement  |   2001-04-02  |    2001-04-09    |  7   |   2   |
|  Ernests   |   Gulbis  |   2014-06-09  |    2014-06-23    |  14  |   3   |
+------------+-----------+---------------+------------------+------+-------+

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

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

%%sql --save players_per_rank --no-execute
select distinct player, rank
FROM atp.rankings
WHERE rank <= 100

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

from sql.ggplot import ggplot, geom_histogram, aes

plot = (
  ggplot(
    table="players_per_rank",
    with_="players_per_rank",
    mapping=aes(x="rank", fill="#69f0ae", color="#fff"),
  ) + geom_histogram(bins=100)
)

​الإعداد

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

​إعداد chDB وJupySQL

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

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

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

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

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

الإعداد

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

إعداد chDB وJupySQL

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

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

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

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

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