التحليل الجغرافي المكاني باستخدام مجموعة بيانات سيارات الأجرة في نيويورك

ستتعرّف في هذا الدليل العملي على كيفية استخدام ClickHouse لتنفيذ استعلامات تحليلية على كميات ضخمة من البيانات. كما ستتعلّم كيفية استخدام قاموس لإثراء البيانات، وكيفية كتابة استعلامات الدمج (join).

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

ستحتاج في هذا الدليل العملي إلى ما يلي:

حساب على ClickHouse Cloud (تحصل على رصيد مجاني بقيمة 300 دولار عند التسجيل)
خدمة ClickHouse Cloud

أنشئ الجدول

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

اختر SQL console من القائمة الجانبية اليسرى
انقر على علامة التبويب + المجاورة لأيقونة الصفحة الرئيسية لإنشاء استعلام جديد
اكتب الاستعلام التالي في محرر SQL، ثم انقر على Run:

Expandable

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;

أدرج البيانات

بعد أن أنشأت الجدول، أضف بيانات سيارات الأجرة في مدينة نيويورك من ملفات CSV المخزّنة في S3.يُدرج الأمر التالي نحو 2,000,000 صف في جدول trips من ملفين مختلفين في S3، هما: trips_1.tsv.gz وtrips_2.tsv.gz:

Expandable

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

انتظر حتى يكتمل إدراج البيانات. سيجري تنزيل نحو 150MB من البيانات. بعد اكتمال إدراج البيانات، تحقّق من عدد الصفوف في جدول trips:

SELECT count() FROM trips

من المفترض أن تحصل على نتيجة تضم 1,999,657 صفًا

حلّل البيانات

بعد تحميل البيانات، يمكنك تشغيل بعض الاستعلامات لتحليلها.

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

SELECT round(avg(tip_amount), 2) FROM trips

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

SELECT
    passenger_count,
    ceil(avg(total_amount),2) AS average_total_amount
FROM trips
GROUP BY passenger_count

احسب العدد اليومي للرحلات التي تبدأ من كل حي:

SELECT
  pickup_date,
  pickup_ntaname,
  SUM(1) AS number_of_trips
FROM trips
GROUP BY pickup_date, pickup_ntaname
ORDER BY pickup_date ASC

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

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

اعرض عدد الرحلات التي تبدأ من كل حي موزّعةً على ساعات اليوم:

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

أنشئ قاموسًا

بعد ذلك، ستنشئ قاموسًا (وهو مجموعة من أزواج مفتاح-قيمة مخزّنة في الذاكرة) باسم taxi_zone_dictionary يربط بين معرّفات المواقع وأسماء الأقاليم الإدارية (boroughs) في مدينة نيويورك، ويستمد بياناته من ملف CSV يضم جميع أحياء مدينة نيويورك. تقابل هذه المعرّفات العمودين pickup_nyct2010_gid وdropoff_nyct2010_gid في جدول الرحلات.فيما يلي مقتطف من ملف CSV الذي تستخدمه، معروضًا على هيئة جدول. يقابل العمود LocationID في الملف العمودين pickup_nyct2010_gid وdropoff_nyct2010_gid في جدول trips لديك:

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

نفّذ أمر 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.

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

SELECT * FROM taxi_zone_dictionary

نفّذ الاستعلامات باستخدام القاموس

يمكنك استخدام الدالة dictGet (أو أحد أشكالها) لاسترداد قيمة من القاموس. مرِّر إليها اسم القاموس، والقيمة المطلوبة، والمفتاح (وهو في مثالنا العمود LocationID من taxi_zone_dictionary)، فتُعيد إليك القيمة المطابقة. على سبيل المثال، يُرجع الاستعلام التالي قيمة Borough التي تقابل LocationID ذا القيمة 132 (أي مطار JFK):

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

يقع مطار JFK في حي Queens. لاحظ أن الوقت المستغرق لاسترجاع القيمة يكاد يكون صفرًا:

┌─dictGet('taxi_zone_dictionary', 'Borough', 132)─┐
│ Queens                                          │
└─────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.004 sec.

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

SELECT dictHas('taxi_zone_dictionary', 132)

يُرجع الاستعلام التالي القيمة 0 لأن 4567 ليست من قيم LocationID في القاموس:

SELECT dictHas('taxi_zone_dictionary', 4567)

استخدم الدالة dictGet لاسترداد اسم الحي (borough) ضمن استعلام. على سبيل المثال:

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 لكل borough. تبدو النتيجة كما يلي، ويُلاحَظ وجود عدد كبير من الرحلات التي يكون فيها حي الانطلاق غير معروف:

┌─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.)

نفّذ عملية ضم

أخيرًا، اكتب بعض الاستعلامات التي تربط taxi_zone_dictionary بجدول trips لديك.ابدأ بعملية 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.

يُرجع هذا الاستعلام صفوف الرحلات الـ 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: اربط أداة واجهة المستخدم أو ذكاء الأعمال (BI) المفضلة لديك بـ ClickHouse.
مرجع SQL: تصفّح دوال SQL المتاحة في ClickHouse لتحويل البيانات ومعالجتها وتحليلها.

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

الإعداد

مجموعات بيانات نموذجية

الترحيل

التحليل الجغرافي المكاني باستخدام مجموعة بيانات سيارات الأجرة في نيويورك

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

أنشئ الجدول

أدرج البيانات

حلّل البيانات

أنشئ قاموسًا

نفّذ الاستعلامات باستخدام القاموس

نفّذ عملية ضم

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

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

أنشئ الجدول

أدرج البيانات

حلّل البيانات

أنشئ قاموسًا

نفّذ الاستعلامات باستخدام القاموس

نفّذ عملية ضم

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

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

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