> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-fbfa8bee.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> JupySQL أداة متعددة المنصات لقواعد البيانات في Jupyter.

# استخدام JupySQL مع ClickHouse

export const CommunityMaintainedBadge = () => {
  return <div className="CommunityMaintainedBadge">
            <div className="CommunityMaintainedIcon">
            <svg xmlns="http://www.w3.org/2000/svg" width="20" height="20" fill="currentColor" viewBox="0 0 256 256">
                <path d="M244.8,150.4a8,8,0,0,1-11.2-1.6A51.6,51.6,0,0,0,192,128a8,8,0,0,1-7.37-4.89,8,8,0,0,1,0-6.22A8,8,0,0,1,192,112a24,24,0,1,0-23.24-30,8,8,0,1,1-15.5-4A40,40,0,1,1,219,117.51a67.94,67.94,0,0,1,27.43,21.68A8,8,0,0,1,244.8,150.4ZM190.92,212a8,8,0,1,1-13.84,8,57,57,0,0,0-98.16,0,8,8,0,1,1-13.84-8,72.06,72.06,0,0,1,33.74-29.92,48,48,0,1,1,58.36,0A72.06,72.06,0,0,1,190.92,212ZM128,176a32,32,0,1,0-32-32A32,32,0,0,0,128,176ZM72,120a8,8,0,0,0-8-8A24,24,0,1,1,87.24,82a8,8,0,1,0,15.5-4A40,40,0,1,0,37,117.51,67.94,67.94,0,0,0,9.6,139.19a8,8,0,1,0,12.8,9.61A51.6,51.6,0,0,1,64,128,8,8,0,0,0,72,120Z"></path>
            </svg>
        </div>
            تتم صيانته من قِبل المجتمع
        </div>;
};

export const Image = ({img, alt, size}) => {
  return <Frame>
      <img src={img} alt={alt} />
    </Frame>;
};

في هذا الدليل، سنستعرض تكاملًا مع ClickHouse.

سنستخدم JupySQL لتشغيل الاستعلامات على ClickHouse.
وبعد تحميل البيانات، سنعرضها بصريًا باستخدام الرسم البياني عبر SQL.

أصبح التكامل بين JupySQL وClickHouse ممكنًا بفضل استخدام مكتبة clickhouse\_sqlalchemy. تتيح هذه المكتبة التواصل بسهولة بين النظامين، وتمكّنك من الاتصال بـ ClickHouse وتحديد لهجة SQL. وبمجرد الاتصال، يمكنك تشغيل استعلامات SQL مباشرةً من واجهة المستخدم الأصلية لـ ClickHouse، أو مباشرةً من دفتر Jupyter.

```python theme={null}
# Install required packages
%pip install --quiet jupysql clickhouse_sqlalchemy
```

ملاحظة: قد تحتاج إلى إعادة تشغيل الـkernel لاستخدام الحزم المُحدَّثة.

```python theme={null}
import pandas as pd
from sklearn_evaluation import plot

# Import jupysql Jupyter extension to create SQL cells
%load_ext sql
%config SqlMagic.autocommit=False
```

**ستحتاج إلى التأكد من أن ClickHouse لديك يعمل ويمكن الوصول إليه إليه للمراحل التالية. يمكنك استخدام الإصدار المحلي أو الإصدار السحابي.**

**ملاحظة:** ستحتاج إلى تعديل سلسلة الاتصال وفقًا لنوع المثيل الذي تحاول الاتصال به (URL، المستخدم، كلمة المرور). في المثال أدناه، استخدمنا مثيلًا محليًا. ولمعرفة المزيد، اطّلع على [هذا الدليل](/ar/get-started/setup/install).

```python theme={null}
%sql clickhouse://default:@localhost:8123/default
```

```sql theme={null}
%%sql
CREATE TABLE trips
(
    `trip_id` UInt32,
    `vendor_id` Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
    `pickup_date` Date,
    `pickup_datetime` DateTime,
    `dropoff_date` Date,
    `dropoff_datetime` DateTime,
    `store_and_fwd_flag` UInt8,
    `rate_code_id` UInt8,
    `pickup_longitude` Float64,
    `pickup_latitude` Float64,
    `dropoff_longitude` Float64,
    `dropoff_latitude` Float64,
    `passenger_count` UInt8,
    `trip_distance` Float64,
    `fare_amount` Float32,
    `extra` Float32,
    `mta_tax` Float32,
    `tip_amount` Float32,
    `tolls_amount` Float32,
    `ehail_fee` Float32,
    `improvement_surcharge` Float32,
    `total_amount` Float32,
    `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
    `trip_type` UInt8,
    `pickup` FixedString(25),
    `dropoff` FixedString(25),
    `cab_type` Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
    `pickup_nyct2010_gid` Int8,
    `pickup_ctlabel` Float32,
    `pickup_borocode` Int8,
    `pickup_ct2010` String,
    `pickup_boroct2010` String,
    `pickup_cdeligibil` String,
    `pickup_ntacode` FixedString(4),
    `pickup_ntaname` String,
    `pickup_puma` UInt16,
    `dropoff_nyct2010_gid` UInt8,
    `dropoff_ctlabel` Float32,
    `dropoff_borocode` UInt8,
    `dropoff_ct2010` String,
    `dropoff_boroct2010` String,
    `dropoff_cdeligibil` String,
    `dropoff_ntacode` FixedString(4),
    `dropoff_ntaname` String,
    `dropoff_puma` UInt16
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(pickup_date)
ORDER BY pickup_datetime;
```

* clickhouse://default:\*\*\*@localhost:8123/default
  تم.

<table>
  <tr />
</table>

```sql theme={null}
%%sql
INSERT INTO trips
SELECT * FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_{1..2}.gz',
    'TabSeparatedWithNames', "
    `trip_id` UInt32,
    `vendor_id` Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
    `pickup_date` Date,
    `pickup_datetime` DateTime,
    `dropoff_date` Date,
    `dropoff_datetime` DateTime,
    `store_and_fwd_flag` UInt8,
    `rate_code_id` UInt8,
    `pickup_longitude` Float64,
    `pickup_latitude` Float64,
    `dropoff_longitude` Float64,
    `dropoff_latitude` Float64,
    `passenger_count` UInt8,
    `trip_distance` Float64,
    `fare_amount` Float32,
    `extra` Float32,
    `mta_tax` Float32,
    `tip_amount` Float32,
    `tolls_amount` Float32,
    `ehail_fee` Float32,
    `improvement_surcharge` Float32,
    `total_amount` Float32,
    `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
    `trip_type` UInt8,
    `pickup` FixedString(25),
    `dropoff` FixedString(25),
    `cab_type` Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
    `pickup_nyct2010_gid` Int8,
    `pickup_ctlabel` Float32,
    `pickup_borocode` Int8,
    `pickup_ct2010` String,
    `pickup_boroct2010` String,
    `pickup_cdeligibil` String,
    `pickup_ntacode` FixedString(4),
    `pickup_ntaname` String,
    `pickup_puma` UInt16,
    `dropoff_nyct2010_gid` UInt8,
    `dropoff_ctlabel` Float32,
    `dropoff_borocode` UInt8,
    `dropoff_ct2010` String,
    `dropoff_boroct2010` String,
    `dropoff_cdeligibil` String,
    `dropoff_ntacode` FixedString(4),
    `dropoff_ntaname` String,
    `dropoff_puma` UInt16
") SETTINGS input_format_try_infer_datetimes = 0
```

* clickhouse://default:\*\*\*@localhost:8123/default
  تم.

<table>
  <tr />
</table>

```python theme={null}
%sql SELECT count() FROM trips limit 5;
```

* clickhouse://default:\*\*\*@localhost:8123/default
  تم.

<table>
  <tr>
    <th>count()</th>
  </tr>

  <tr>
    <td>1999657</td>
  </tr>
</table>

```python theme={null}
%sql SELECT DISTINCT(pickup_ntaname) FROM trips limit 5;
```

* clickhouse://default:\*\*\*@localhost:8123/default
  تم.

<table>
  <tr>
    <th>pickup\_ntaname</th>
  </tr>

  <tr>
    <td>مورنينغسايد هايتس</td>
  </tr>

  <tr>
    <td>هدسون ياردز-تشيلسي-فلاتيرون-يونيون سكوير</td>
  </tr>

  <tr>
    <td>ميدتاون-ميدتاون ساوث</td>
  </tr>

  <tr>
    <td>سوهو-ترايبيكا-سيفيك سنتر-ليتل إيتالي</td>
  </tr>

  <tr>
    <td>موراي هيل-كيبْس باي</td>
  </tr>
</table>

```python theme={null}
%sql SELECT round(avg(tip_amount), 2) FROM trips
```

* clickhouse://default:\*\*\*@localhost:8123/default
  تم.

<table>
  <tr>
    <th>round(avg(tip\_amount), 2)</th>
  </tr>

  <tr>
    <td>1.68</td>
  </tr>
</table>

```sql theme={null}
%%sql
SELECT
    passenger_count,
    ceil(avg(total_amount),2) AS average_total_amount
FROM trips
GROUP BY passenger_count
```

* clickhouse://default:\*\*\*@localhost:8123/default
  تم.

<table>
  <tr>
    <th>passenger\_count</th>
    <th>average\_total\_amount</th>
  </tr>

  <tr>
    <td>0</td>
    <td>22.69</td>
  </tr>

  <tr>
    <td>1</td>
    <td>15.97</td>
  </tr>

  <tr>
    <td>2</td>
    <td>17.15</td>
  </tr>

  <tr>
    <td>3</td>
    <td>16.76</td>
  </tr>

  <tr>
    <td>4</td>
    <td>17.33</td>
  </tr>

  <tr>
    <td>5</td>
    <td>16.35</td>
  </tr>

  <tr>
    <td>6</td>
    <td>16.04</td>
  </tr>

  <tr>
    <td>7</td>
    <td>59.8</td>
  </tr>

  <tr>
    <td>8</td>
    <td>36.41</td>
  </tr>

  <tr>
    <td>9</td>
    <td>9.81</td>
  </tr>
</table>

```sql theme={null}
%%sql
SELECT
    pickup_date,
    pickup_ntaname,
    SUM(1) AS number_of_trips
FROM trips
GROUP BY pickup_date, pickup_ntaname
ORDER BY pickup_date ASC
limit 5;
```

* clickhouse://default:\*\*\*@localhost:8123/default
  اكتمل.

<table>
  <tr>
    <th>pickup\_date</th>
    <th>pickup\_ntaname</th>
    <th>number\_of\_trips</th>
  </tr>

  <tr>
    <td>2015-07-01</td>
    <td>Bushwick North</td>
    <td>2</td>
  </tr>

  <tr>
    <td>2015-07-01</td>
    <td>Brighton Beach</td>
    <td>1</td>
  </tr>

  <tr>
    <td>2015-07-01</td>
    <td>Briarwood-Jamaica Hills</td>
    <td>3</td>
  </tr>

  <tr>
    <td>2015-07-01</td>
    <td>Williamsburg</td>
    <td>1</td>
  </tr>

  <tr>
    <td>2015-07-01</td>
    <td>Queensbridge-Ravenswood-Long Island City</td>
    <td>9</td>
  </tr>
</table>

```python theme={null}
# %sql DESCRIBE trips;
```

```python theme={null}
# %sql SELECT DISTINCT(trip_distance) FROM trips limit 50;
```

```sql theme={null}
%%sql --save short-trips --no-execute
SELECT *
FROM trips
WHERE trip_distance < 6.3
```

* clickhouse://default:\*\*\*@localhost:8123/default
  جارٍ تخطي التنفيذ...

```python theme={null}
%sqlplot histogram --table short-trips --column trip_distance --bins 10 --with short-trips
```

```response theme={null}
<AxesSubplot: title={'center': "'trip_distance' from 'short-trips'"}, xlabel='trip_distance', ylabel='Count'>
```

<Image img="https://mintcdn.com/private-7c7dfe99-mintlify-fbfa8bee/amY-JDMREAaO7mx6/images/integrations/sql-clients/jupysql-plot-1.png?fit=max&auto=format&n=amY-JDMREAaO7mx6&q=85&s=f3924ba9bdb734674879987b5c9b20d0" size="md" alt="مُدرَّج تكراري يوضح توزيع مسافات الرحلات ضمن 10 فئات من مجموعة بيانات الرحلات القصيرة" border width="597" height="455" data-path="images/integrations/sql-clients/jupysql-plot-1.png" />

```python theme={null}
ax = %sqlplot histogram --table short-trips --column trip_distance --bins 50 --with short-trips
ax.grid()
ax.set_title("Trip distance from trips < 6.3")
_ = ax.set_xlabel("Trip distance")
```

<Image img="https://mintcdn.com/private-7c7dfe99-mintlify-fbfa8bee/amY-JDMREAaO7mx6/images/integrations/sql-clients/jupysql-plot-2.png?fit=max&auto=format&n=amY-JDMREAaO7mx6&q=85&s=72629de6610136783ecdb759d8077e4c" size="md" alt="مُدرَّج تكراري يوضح توزيع مسافات الرحلات باستخدام 50 فئة مع شبكة، بعنوان 'Trip distance from trips < 6.3'" border width="597" height="455" data-path="images/integrations/sql-clients/jupysql-plot-2.png" />