> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.
# JupySQL وchDB
> كيفية تثبيت chDB for Bun
export const Image = ({img, alt, size = "lg"}) => {
const normalizedSize = ["sm", "md", "lg"].includes(size) ? size : "lg";
return
;
};
[JupySQL](https://jupysql.readthedocs.io/en/latest/quick-start.html) هي مكتبة Python تتيح لك تشغيل SQL في دفاتر Jupyter وواجهة IPython.
في هذا الدليل، سنتعلّم كيفية الاستعلام عن البيانات باستخدام chDB وJupySQL.
## الإعداد
لنبدأ أولًا بإنشاء بيئة افتراضية:
```bash theme={null}
python -m venv .venv
source .venv/bin/activate
```
ثم سنقوم بتثبيت JupySQL وIPython وJupyter Lab:
```bash theme={null}
pip install jupysql ipython jupyterlab
```
يمكننا استخدام JupySQL في IPython، ويمكننا تشغيله بتنفيذ:
```bash theme={null}
ipython
```
أو في Jupyter Lab، عبر تشغيل:
```bash theme={null}
jupyter lab
```
إذا كنت تستخدم Jupyter Lab، فستحتاج إلى إنشاء دفتر قبل متابعة بقية الدليل.
## تنزيل مجموعة بيانات
سنستخدم إحدى مجموعات بيانات [tennis\_atp الخاصة بـ Jeff Sackmann](https://github.com/JeffSackmann/tennis_atp)، والتي تتضمن بيانات وصفية عن اللاعبين وتصنيفاتهم بمرور الوقت.
لنبدأ بتنزيل ملفات التصنيف:
```python theme={null}
from urllib.request import urlretrieve
```
```python theme={null}
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:
```python theme={null}
from chdb import dbapi
```
وسننشىء اتصالًا بـ chDB.
ستُحفَظ أي بيانات نُخزّنها بشكل دائم في المجلد `atp.chdb`:
```python theme={null}
conn = dbapi.connect(path="atp.chdb")
```
لنحمّل الآن الأمر السحري `sql` وننشئ اتصالًا بـ chDB:
```python theme={null}
%load_ext sql
%sql conn --alias chdb
```
بعد ذلك، سنعرض حدّ العرض كي لا تُقتطع نتائج الاستعلامات:
```python theme={null}
%config SqlMagic.displaylimit = None
```
\## الاستعلام عن البيانات في ملفات CSV
نزّلنا مجموعة من الملفات ذات البادئة `atp_rankings`.
لنستخدم عبارة `DESCRIBE` لفهم المخطط:
```python theme={null}
%%sql
DESCRIBE file('atp_rankings*.csv')
SETTINGS describe_compact_output=1,
schema_inference_make_columns_nullable=0
```
```text theme={null}
+--------------+-------+
| name | type |
+--------------+-------+
| ranking_date | Int64 |
| rank | Int64 |
| player | Int64 |
| points | Int64 |
+--------------+-------+
```
يمكننا أيضًا كتابة استعلام `SELECT` مباشرةً على هذه الملفات لمعرفة شكل البيانات:
```python theme={null}
%sql SELECT * FROM file('atp_rankings*.csv') LIMIT 1
```
```text theme={null}
+--------------+------+--------+--------+
| ranking_date | rank | player | points |
+--------------+------+--------+--------+
| 20000110 | 1 | 101736 | 4135 |
+--------------+------+--------+--------+
```
تنسيق البيانات غريب بعض الشيء.
لنصحّح تنسيق ذلك التاريخ ونستخدم عبارة `REPLACE` لإرجاع `ranking_date` بعد تصحيحه:
```python theme={null}
%%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
```
```text theme={null}
+--------------+------+--------+--------+
| 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 هذه في جدول.
لا تحفظ قاعدة البيانات الافتراضية البيانات على القرص، لذا نحتاج أولًا إلى إنشاء قاعدة بيانات أخرى:
```python theme={null}
%sql CREATE DATABASE atp
```
والآن سننشئ جدولًا باسم `rankings`، وسيُستمد مخططه من بنية البيانات في ملفات CSV:
```python theme={null}
%%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
```
لنتحقق سريعًا من البيانات في جدولنا:
```python theme={null}
%sql SELECT * FROM atp.rankings LIMIT 10
```
```text theme={null}
+--------------+------+--------+--------+
| 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 واحد، لذا لننزّل هذا الملف:
```python theme={null}
_ = urlretrieve(
f"{base}/atp_players.csv",
"atp_players.csv",
)
```
ثم أنشئ جدولًا باسم `players` استنادًا إلى محتويات ملف CSV.
وسنُهيِّئ أيضًا الحقل `dob` ليصبح من النوع `Date32`.
> في ClickHouse، لا يدعم النوع `Date` إلا التواريخ بدءًا من عام 1970. وبما أن العمود `dob` يحتوي على تواريخ تسبق عام 1970، فسنستخدم النوع `Date32` بدلًا منه.
```python theme={null}
%%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
```
بعد اكتمال التشغيل، يمكننا إلقاء نظرة على البيانات التي استوردناها:
```python theme={null}
%sql SELECT * FROM atp.players LIMIT 10
```
```text theme={null}
+-----------+------------+-----------+------+------------+-----+--------+-------------+
| 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 أسبوعًا.
سنكتب استعلامًا يعثر على الحد الأقصى للنقاط التي جمعها كل لاعب، إلى جانب ترتيبه في ذلك الوقت:
```python theme={null}
%%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
```
```text theme={null}
+------------+-----------+-----------+------+------------+
| 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` أنه سيتم تخطي تنفيذ الاستعلام.
```python theme={null}
%%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) قبل تنفيذه.
في الاستعلام التالي، نحسب الحد الأقصى للنقاط التي حققها اللاعبون حين كانوا في المركز الأول:
```python theme={null}
%sql select * FROM best_points WHERE rank=1
```
```text theme={null}
+-------------+-----------+-----------+------+------------+
| 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 |
+-------------+-----------+-----------+------+------------+
```
## الاستعلام باستخدام المعلمات
يمكننا أيضًا استخدام المعلمات في استعلاماتنا.
المعلمات ليست سوى متغيرات عادية:
```python theme={null}
rank = 10
```
وبعد ذلك، يمكننا استخدام صيغة `{{variable}}` في الاستعلام.
يعثر الاستعلام التالي على اللاعبين الذين كانت لديهم أقل مدة، محسوبة بالأيام، بين أول مرة دخلوا فيها ضمن أفضل 10 وآخر مرة دخلوا فيها ضمن أفضل 10:
```python theme={null}
%%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
```
```text theme={null}
+------------+-----------+---------------+------------------+------+-------+
| 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 التي حققها كل لاعب.
وسيكون بإمكاننا استخدامه لإنشاء مدرج تكراري يحصي عدد اللاعبين الذين حققوا كل مركز:
```python theme={null}
%%sql --save players_per_rank --no-execute
select distinct player, rank
FROM atp.rankings
WHERE rank <= 100
```
يمكننا بعد ذلك إنشاء مدرج تكراري بتنفيذ ما يلي:
```python theme={null}
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)
)
```