الدليل التعليمي المتقدم

تعرّف على كيفية إدخال البيانات والاستعلام عنها في ClickHouse باستخدام مجموعة بيانات نموذجية لسيارات الأجرة في مدينة نيويورك.

نظرة عامة

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

المتطلبات الأساسية

تحتاج إلى الوصول إلى خدمة ClickHouse عاملة لإكمال هذا الدليل. للاطلاع على التعليمات، راجع دليل البدء السريع.

1. أنشئ جدولًا جديدًا

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

   1. اتصل بـ SQL Console:

      • في ClickHouse Cloud، اختر خدمة من القائمة المنسدلة، ثم اختر SQL Console من قائمة التنقل اليمنى.
      • في Self-managed ClickHouse، اتصل بـ SQL Console على https://_hostname_:8443/play. راجع مسؤول ClickHouse لديك للحصول على التفاصيل.

   2. أنشئ جدول trips التالي في قاعدة البيانات default:

      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;
      
2. أضف مجموعة البيانات

   الآن بعد أن أنشأت جدولًا، أضف بيانات سيارات الأجرة لمدينة نيويورك من ملفات CSV في S3.

   1. يُدرج الأمر التالي حوالي 2,000,000 صف في جدول trips من ملفين مختلفين في S3: ‏trips_1.tsv.gz وtrips_2.tsv.gz:

      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
      

   2. انتظر حتى يكتمل INSERT. قد يستغرق تنزيل 150 MB من البيانات بعض الوقت.

   3. عند اكتمال الإدراج، تحقّق من نجاحه:

      SELECT count() FROM trips
      

      يجب أن يُرجع هذا query عددًا قدره 1,999,657 صفًا.

3. تحليل البيانات

   شغّل بعض الاستعلامات لتحليل البيانات. استكشف الأمثلة التالية أو جرّب استعلام SQL الخاص بك.

   • احسب متوسط قيمة الإكرامية:

     SELECT round(avg(tip_amount), 2) FROM trips
     
     الناتج المتوقّع

     ┌─round(avg(tip_amount), 2)─┐
     │                      1.68 │
     └───────────────────────────┘
     

   • احسب متوسط التكلفة حسب عدد الركاب:

     SELECT
         passenger_count,
         ceil(avg(total_amount),2) AS average_total_amount
     FROM trips
     GROUP BY passenger_count
     
     المخرجات المتوقعة

     تتراوح قيم passenger_count بين 0 و9:

     ┌─passenger_count─┬─average_total_amount─┐
     │               0 │                22.69 │
     │               1 │                15.97 │
     │               2 │                17.15 │
     │               3 │                16.76 │
     │               4 │                17.33 │
     │               5 │                16.35 │
     │               6 │                16.04 │
     │               7 │                 59.8 │
     │               8 │                36.41 │
     │               9 │                 9.81 │
     └─────────────────┴──────────────────────┘
     

   • احسب عدد عمليات الالتقاط اليومية لكل حي:

     SELECT
         pickup_date,
         pickup_ntaname,
         SUM(1) AS number_of_trips
     FROM trips
     GROUP BY pickup_date, pickup_ntaname
     ORDER BY pickup_date ASC
     
     الناتج المتوقع

     ┌─pickup_date─┬─pickup_ntaname───────────────────────────────────────────┬─number_of_trips─┐
     │  2015-07-01 │ Brooklyn Heights-Cobble Hill                             │              13 │
     │  2015-07-01 │ Old Astoria                                              │               5 │
     │  2015-07-01 │ Flushing                                                 │               1 │
     │  2015-07-01 │ Yorkville                                                │             378 │
     │  2015-07-01 │ Gramercy                                                 │             344 │
     │  2015-07-01 │ Fordham South                                            │               2 │
     │  2015-07-01 │ SoHo-TriBeCa-Civic Center-Little Italy                   │             621 │
     │  2015-07-01 │ Park Slope-Gowanus                                       │              29 │
     │  2015-07-01 │ Bushwick South                                           │               5 │
     

   • احسب مدة كل رحلة بالدقائق، ثم صنّف النتائج حسب مدة الرحلة:

     SELECT
         avg(tip_amount) AS avg_tip,
         avg(fare_amount) AS avg_fare,
         avg(passenger_count) AS avg_passenger,
         count() AS count,
         truncate(date_diff('second', pickup_datetime, dropoff_datetime)/60) as trip_minutes
     FROM trips
     WHERE trip_minutes > 0
     GROUP BY trip_minutes
     ORDER BY trip_minutes DESC
     
     الناتج المتوقَّع

     ┌──────────────avg_tip─┬───────────avg_fare─┬──────avg_passenger─┬──count─┬─trip_minutes─┐
     │   1.9600000381469727 │                  8 │                  1 │      1 │        27511 │
     │                    0 │                 12 │                  2 │      1 │        27500 │
     │    0.542166673981895 │ 19.716666666666665 │ 1.9166666666666667 │     60 │         1439 │
     │    0.902499997522682 │ 11.270625001192093 │            1.95625 │    160 │         1438 │
     │   0.9715789457909146 │ 13.646616541353383 │ 2.0526315789473686 │    133 │         1437 │
     │   0.9682692398245518 │ 14.134615384615385 │  2.076923076923077 │    104 │         1436 │
     │   1.1022105210705808 │ 13.778947368421052 │  2.042105263157895 │     95 │         1435 │
     

   • اعرض عدد مرات الالتقاط في كل حي موزعةً حسب ساعات اليوم:

     SELECT
         pickup_ntaname,
         toHour(pickup_datetime) as pickup_hour,
         SUM(1) AS pickups
     FROM trips
     WHERE pickup_ntaname != ''
     GROUP BY pickup_ntaname, pickup_hour
     ORDER BY pickup_ntaname, pickup_hour
     
     المخرجات المتوقعة

     ┌─pickup_ntaname───────────────────────────────────────────┬─pickup_hour─┬─pickups─┐
     │ Airport                                                  │           0 │    3509 │
     │ Airport                                                  │           1 │    1184 │
     │ Airport                                                  │           2 │     401 │
     │ Airport                                                  │           3 │     152 │
     │ Airport                                                  │           4 │     213 │
     │ Airport                                                  │           5 │     955 │
     │ Airport                                                  │           6 │    2161 │
     │ Airport                                                  │           7 │    3013 │
     │ Airport                                                  │           8 │    3601 │
     │ Airport                                                  │           9 │    3792 │
     │ Airport                                                  │          10 │    4546 │
     │ Airport                                                  │          11 │    4659 │
     │ Airport                                                  │          12 │    4621 │
     │ Airport                                                  │          13 │    5348 │
     │ Airport                                                  │          14 │    5889 │
     │ Airport                                                  │          15 │    6505 │
     │ Airport                                                  │          16 │    6119 │
     │ Airport                                                  │          17 │    6341 │
     │ Airport                                                  │          18 │    6173 │
     │ Airport                                                  │          19 │    6329 │
     │ Airport                                                  │          20 │    6271 │
     │ Airport                                                  │          21 │    6649 │
     │ Airport                                                  │          22 │    6356 │
     │ Airport                                                  │          23 │    6016 │
     │ Allerton-Pelham Gardens                                  │           4 │       1 │
     │ Allerton-Pelham Gardens                                  │           6 │       1 │
     │ Allerton-Pelham Gardens                                  │           7 │       1 │
     │ Allerton-Pelham Gardens                                  │           9 │       5 │
     │ Allerton-Pelham Gardens                                  │          10 │       3 │
     │ Allerton-Pelham Gardens                                  │          15 │       1 │
     │ Allerton-Pelham Gardens                                  │          20 │       2 │
     │ Allerton-Pelham Gardens                                  │          23 │       1 │
     │ Annadale-Huguenot-Prince's Bay-Eltingville               │          23 │       1 │
     │ Arden Heights                                            │          11 │       1 │
     

   7. استرجع الرحلات المتجهة إلى مطاري LaGuardia أو JFK:

      SELECT
          pickup_datetime,
          dropoff_datetime,
          total_amount,
          pickup_nyct2010_gid,
          dropoff_nyct2010_gid,
          CASE
              WHEN dropoff_nyct2010_gid = 138 THEN 'LGA'
              WHEN dropoff_nyct2010_gid = 132 THEN 'JFK'
          END AS airport_code,
          EXTRACT(YEAR FROM pickup_datetime) AS year,
          EXTRACT(DAY FROM pickup_datetime) AS day,
          EXTRACT(HOUR FROM pickup_datetime) AS hour
      FROM trips
      WHERE dropoff_nyct2010_gid IN (132, 138)
      ORDER BY pickup_datetime
      
      النتائج المتوقعة

      ┌─────pickup_datetime─┬────dropoff_datetime─┬─total_amount─┬─pickup_nyct2010_gid─┬─dropoff_nyct2010_gid─┬─airport_code─┬─year─┬─day─┬─hour─┐
      │ 2015-07-01 00:04:14 │ 2015-07-01 00:15:29 │         13.3 │                 -34 │                  132 │ JFK          │ 2015 │   1 │    0 │
      │ 2015-07-01 00:09:42 │ 2015-07-01 00:12:55 │          6.8 │                  50 │                  138 │ LGA          │ 2015 │   1 │    0 │
      │ 2015-07-01 00:23:04 │ 2015-07-01 00:24:39 │          4.8 │                -125 │                  132 │ JFK          │ 2015 │   1 │    0 │
      │ 2015-07-01 00:27:51 │ 2015-07-01 00:39:02 │        14.72 │                -101 │                  138 │ LGA          │ 2015 │   1 │    0 │
      │ 2015-07-01 00:32:03 │ 2015-07-01 00:55:39 │        39.34 │                  48 │                  138 │ LGA          │ 2015 │   1 │    0 │
      │ 2015-07-01 00:34:12 │ 2015-07-01 00:40:48 │         9.95 │                 -93 │                  132 │ JFK          │ 2015 │   1 │    0 │
      │ 2015-07-01 00:38:26 │ 2015-07-01 00:49:00 │         13.3 │                 -11 │                  138 │ LGA          │ 2015 │   1 │    0 │
      │ 2015-07-01 00:41:48 │ 2015-07-01 00:44:45 │          6.3 │                 -94 │                  132 │ JFK          │ 2015 │   1 │    0 │
      │ 2015-07-01 01:06:18 │ 2015-07-01 01:14:43 │        11.76 │                  37 │                  132 │ JFK          │ 2015 │   1 │    1 │
      

4. إنشاء قاموس

   القاموس (Dictionary) هو بنية بيانات تخزّن أزواج المفتاح والقيمة (key-value pairs) في الذاكرة. لمزيد من التفاصيل، راجع Dictionaries

   أنشئ قاموسًا مرتبطًا بجدول في خدمة ClickHouse الخاصة بك. يستند الجدول والقاموس إلى ملف CSV يحتوي على صف لكل حي من أحياء مدينة نيويورك.

   يتم تعيين الأحياء إلى أسماء الأقسام الإدارية الخمسة لمدينة نيويورك (Bronx وBrooklyn وManhattan وQueens وStaten Island)، إضافةً إلى مطار Newark (EWR).

   فيما يلي مقتطف من ملف CSV الذي تستخدمه بتنسيق جدولي. يُقابل عمود LocationID في الملف عمودَي pickup_nyct2010_gid وdropoff_nyct2010_gid في جدول trips الخاص بك:

   معرّف الموقع	الحي	المنطقة	منطقة_الخدمة
   1	EWR	مطار نيوارك	EWR
   2	كوينز	خليج جامايكا	منطقة بورو
   3	برونكس	أليرتون/بيلهام غاردنز	منطقة بورو
   4	مانهاتن	ألفابت سيتي	النطاق الأصفر
   5	جزيرة ستاتن	أردن هايتس	نطاق بورو

   1. نفّذ أمر SQL التالي، الذي ينشئ قاموسًا باسم taxi_zone_dictionary ويُعبِّئه من ملف CSV في S3. عنوان URL للملف هو https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv.

   CREATE DICTIONARY taxi_zone_dictionary
   (
     `LocationID` UInt16 DEFAULT 0,
     `Borough` String,
     `Zone` String,
     `service_zone` String
   )
   PRIMARY KEY LocationID
   SOURCE(HTTP(URL 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv' FORMAT 'CSVWithNames'))
   LIFETIME(MIN 0 MAX 0)
   LAYOUT(HASHED_ARRAY())
   

   يؤدي تعيين LIFETIME إلى 0 إلى تعطيل التحديثات التلقائية لتجنّب حركة البيانات غير الضرورية إلى حاوية S3 الخاصة بنا. وفي حالات أخرى، قد ترغب في ضبطه بشكل مختلف. لمزيد من التفاصيل، راجع تحديث بيانات القاموس باستخدام LIFETIME.

   3. تحقّق من نجاح العملية. يجب أن يُرجع ما يلي 265 صفًا، أي صفًا واحدًا لكل حي:

      SELECT * FROM taxi_zone_dictionary
      

   4. استخدم الدالة dictGet (أو صيغها المختلفة) لاسترجاع قيمة من قاموس. مرِّر اسم القاموس، والقيمة المطلوبة، والمفتاح (وهو في مثالنا العمود LocationID من taxi_zone_dictionary).

      على سبيل المثال، يُرجع الاستعلام التالي قيمة Borough الخاصة بـ LocationID ذي القيمة 132، وهو ما يقابل مطار JFK):

      SELECT dictGet('taxi_zone_dictionary', 'Borough', 132)
      

      يقع JFK في كوينز. لاحظ أن الوقت اللازم لاسترجاع القيمة يكاد يكون 0:

      ┌─dictGet('taxi_zone_dictionary', 'Borough', 132)─┐
      │ Queens                                          │
      └─────────────────────────────────────────────────┘
      
      1 rows in set. Elapsed: 0.004 sec.
      

   5. استخدم الدالة dictHas للتحقق مما إذا كان المفتاح موجودًا في القاموس. على سبيل المثال، يُرجع الاستعلام التالي 1 (وهي تعني “true” في ClickHouse):

      SELECT dictHas('taxi_zone_dictionary', 132)
      

   6. يعيد الاستعلام التالي القيمة 0 لأن 4567 ليس قيمةً لـ LocationID في القاموس:

      SELECT dictHas('taxi_zone_dictionary', 4567)
      

   7. استخدم الدالة dictGet لجلب اسم أحد الأحياء في استعلام. على سبيل المثال:

      SELECT
          count(1) AS total,
          dictGetOrDefault('taxi_zone_dictionary','Borough', toUInt64(pickup_nyct2010_gid), 'Unknown') AS borough_name
      FROM trips
      WHERE dropoff_nyct2010_gid = 132 OR dropoff_nyct2010_gid = 138
      GROUP BY borough_name
      ORDER BY total DESC
      

      يجمع هذا الاستعلام إجمالي عدد رحلات سيارات الأجرة في كل مقاطعة تنتهي في مطار LaGuardia أو JFK. وتبدو النتيجة كما يلي، ولاحظ أن هناك عددًا غير قليل من الرحلات التي يكون فيها حيّ الانطلاق غير معروف:

      ┌─total─┬─borough_name──┐
      │ 23683 │ Unknown       │
      │  7053 │ Manhattan     │
      │  6828 │ Brooklyn      │
      │  4458 │ Queens        │
      │  2670 │ Bronx         │
      │   554 │ Staten Island │
      │    53 │ EWR           │
      └───────┴───────────────┘
      
      7 rows in set. Elapsed: 0.019 sec. Processed 2.00 million rows, 4.00 MB (105.70 million rows/s., 211.40 MB/s.)
      
5. إجراء عملية ربط

   اكتب بعض الاستعلامات التي تُجري ربطًا بين taxi_zone_dictionary وجدول trips لديك.

   1. ابدأ بـ JOIN بسيط يعمل بطريقة مشابهة لاستعلام المطار السابق أعلاه:

      SELECT
          count(1) AS total,
          Borough
      FROM trips
      JOIN taxi_zone_dictionary ON toUInt64(trips.pickup_nyct2010_gid) = taxi_zone_dictionary.LocationID
      WHERE dropoff_nyct2010_gid = 132 OR dropoff_nyct2010_gid = 138
      GROUP BY Borough
      ORDER BY total DESC
      

      تبدو الاستجابة مطابقة لاستعلام dictGet:

      ┌─total─┬─Borough───────┐
      │  7053 │ Manhattan     │
      │  6828 │ Brooklyn      │
      │  4458 │ Queens        │
      │  2670 │ Bronx         │
      │   554 │ Staten Island │
      │    53 │ EWR           │
      └───────┴───────────────┘
      
      6 rows in set. Elapsed: 0.034 sec. Processed 2.00 million rows, 4.00 MB (59.14 million rows/s., 118.29 MB/s.)
      

   لاحظ أن ناتج استعلام JOIN أعلاه هو نفسه ناتج الاستعلام السابق الذي استخدم dictGetOrDefault (باستثناء أن قيم Unknown غير مضمّنة). في الخلفية، يستدعي ClickHouse فعليًا الدالة dictGet للقاموس taxi_zone_dictionary، لكن صياغة JOIN أكثر شيوعًا لدى مطوري SQL.

   2. يعرض هذا الاستعلام الصفوف الخاصة بأعلى 1000 رحلة من حيث قيمة الإكرامية، ثم يُجري inner join لكل صف مع القاموس:
      SELECT *
      FROM trips
      JOIN taxi_zone_dictionary
          ON trips.dropoff_nyct2010_gid = taxi_zone_dictionary.LocationID
      WHERE tip_amount > 0
      ORDER BY tip_amount DESC
      LIMIT 1000
      

   عمومًا، نتجنب الإكثار من استخدام SELECT * في ClickHouse. ينبغي لك جلب الأعمدة التي تحتاج إليها فعليًا فقط.

الخطوات التالية

تعرّف على المزيد حول ClickHouse من خلال الوثائق التالية:

• مقدمة إلى الفهارس الأساسية في ClickHouse: تعرّف على كيفية استخدام ClickHouse للفهارس الأساسية المتفرقة لتحديد البيانات ذات الصلة بكفاءة أثناء الاستعلامات.
• ربط مصدر بيانات خارجي: راجع خيارات تكامل مصادر البيانات، بما في ذلك الملفات وKafka وPostgreSQL ومسارات البيانات وغير ذلك الكثير.
• تصوّر البيانات في ClickHouse: صِل أداة UI/BI المفضلة لديك بـ ClickHouse.
• مرجع SQL: تصفّح دوال SQL المتاحة في ClickHouse لتحويل البيانات ومعالجتها وتحليلها.