> ## 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. > إدخال بيانات القيم المفصولة بعلامات جدولة والاستعلام عنها في 5 خطوات # بيانات شكاوى NYPD تُعد ملفات القيم المفصولة بعلامات جدولة، أو TSV، شائعة، وقد تتضمن عناوين الحقول في السطر الأول من الملف. يمكن لـ ClickHouse إدخال ملفات TSV، كما يمكنه أيضًا الاستعلام عنها دون إدخالها. يغطي هذا الدليل كلتا الحالتين. وإذا كنت بحاجة إلى الاستعلام عن ملفات CSV أو إدخالها، فستنجح الأساليب نفسها؛ ما عليك سوى استبدال `TSV` بـ `CSV` في وسائط التنسيق. أثناء اتباع هذا الدليل، ستقوم بما يلي: * **الاستكشاف**: الاستعلام عن بنية ملف TSV ومحتواه. * **تحديد مخطط ClickHouse المستهدف**: اختيار أنواع البيانات المناسبة وربط البيانات الحالية بهذه الأنواع. * **إنشاء جدول ClickHouse**. * **المعالجة المسبقة** للبيانات و**بثّها** إلى ClickHouse. * **تشغيل بعض الاستعلامات** على ClickHouse. تأتي مجموعة البيانات المستخدمة في هذا الدليل من فريق NYC Open Data، وتتضمن بيانات عن "جميع جرائم الجنايات والجنح والمخالفات الصالحة المُبلّغ عنها إلى إدارة شرطة مدينة نيويورك (NYPD)". وعند كتابة هذا الدليل، كان حجم ملف البيانات 166 ميغابايت، لكنه يُحدَّث بانتظام. **المصدر**: [data.cityofnewyork.us](https://data.cityofnewyork.us/Public-Safety/NYPD-Complaint-Data-Current-Year-To-Date-/5uac-w243) **شروط الاستخدام**: [https://www1.nyc.gov/home/terms-of-use.page](https://www1.nyc.gov/home/terms-of-use.page)
## المتطلبات الأساسية
* نزّل مجموعة البيانات بزيارة صفحة [NYPD Complaint Data Current (Year To Date)](https://data.cityofnewyork.us/Public-Safety/NYPD-Complaint-Data-Current-Year-To-Date-/5uac-w243)، ثم انقر على زر Export واختر **TSV for Excel**. * ثبّت [ClickHouse server والعميل](/ar/get-started/setup/install)
### ملاحظة حول الأوامر الموضَّحة في هذا الدليل
يوجد نوعان من الأوامر في هذا الدليل: * بعض الأوامر تستعلم من ملفات TSV، وتُشغَّل من موجّه الأوامر. * أما بقية الأوامر فتستعلم من ClickHouse، وتُشغَّل في `clickhouse-client` أو في واجهة Play UI. تفترض الأمثلة في هذا الدليل أنك حفظت ملف TSV في `${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv`، لذا يُرجى تعديل الأوامر عند الحاجة.
## تعرّف على ملف TSV
قبل البدء في العمل مع قاعدة بيانات ClickHouse، اطّلع على البيانات.
### اطّلع على الحقول في ملف TSV المصدر
هذا مثال على أمر للاستعلام عن ملف TSV، ولكن لا تُشغِّله بعد. ```sh title="Query" theme={null} clickhouse-local --query \ "describe file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')" ``` مثال على الاستجابة ```response theme={null} CMPLNT_NUM Nullable(Float64) ADDR_PCT_CD Nullable(Float64) BORO_NM Nullable(String) CMPLNT_FR_DT Nullable(String) CMPLNT_FR_TM Nullable(String) ``` في معظم الأحيان، سيُبيّن لك الأمر أعلاه أي الحقول في بيانات الإدخال رقمية، وأيها سلاسل نصية، وأيها tuples. لكن هذا لا يحدث دائمًا. ولأن ClickHouse يُستخدم عادةً مع مجموعات بيانات تحتوي على مليارات السجلات، يوجد حد افتراضي لعدد الصفوف التي تُفحص (100 صف) من أجل [استنتاج المخطط](/ar/guides/clickhouse/data-formats/json/inference)، وذلك لتجنّب تحليل مليارات الصفوف لاستنتاج المخطط. قد لا تتطابق الاستجابة أدناه مع ما تراه لديك، لأن مجموعة البيانات تُحدَّث عدة مرات كل عام. وبالاطلاع على قاموس البيانات، يمكنك أن ترى أن CMPLNT\_NUM محدد كنص، وليس كقيمة رقمية. ومن خلال تجاوز القيمة الافتراضية البالغة 100 صف للاستدلال باستخدام الإعداد `SETTINGS input_format_max_rows_to_read_for_schema_inference=2000` يمكنك الحصول على فكرة أدق عن المحتوى. ملاحظة: اعتبارًا من الإصدار 22.5، أصبحت القيمة الافتراضية الآن 25,000 صف لاستنتاج المخطط، لذا لا تغيّر هذا الإعداد إلا إذا كنت تستخدم إصدارًا أقدم أو إذا كنت بحاجة إلى أخذ عينة تتجاوز 25,000 صف. نفّذ هذا الأمر في موجّه الأوامر. ستستخدم `clickhouse-local` لإجراء استعلام على البيانات الموجودة في ملف TSV الذي نزّلته. ```sh title="Query" theme={null} clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \ --query \ "describe file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')" ``` ```response title="Response" theme={null} CMPLNT_NUM Nullable(String) ADDR_PCT_CD Nullable(Float64) BORO_NM Nullable(String) CMPLNT_FR_DT Nullable(String) CMPLNT_FR_TM Nullable(String) CMPLNT_TO_DT Nullable(String) CMPLNT_TO_TM Nullable(String) CRM_ATPT_CPTD_CD Nullable(String) HADEVELOPT Nullable(String) HOUSING_PSA Nullable(Float64) JURISDICTION_CODE Nullable(Float64) JURIS_DESC Nullable(String) KY_CD Nullable(Float64) LAW_CAT_CD Nullable(String) LOC_OF_OCCUR_DESC Nullable(String) OFNS_DESC Nullable(String) PARKS_NM Nullable(String) PATROL_BORO Nullable(String) PD_CD Nullable(Float64) PD_DESC Nullable(String) PREM_TYP_DESC Nullable(String) RPT_DT Nullable(String) STATION_NAME Nullable(String) SUSP_AGE_GROUP Nullable(String) SUSP_RACE Nullable(String) SUSP_SEX Nullable(String) TRANSIT_DISTRICT Nullable(Float64) VIC_AGE_GROUP Nullable(String) VIC_RACE Nullable(String) VIC_SEX Nullable(String) X_COORD_CD Nullable(Float64) Y_COORD_CD Nullable(Float64) Latitude Nullable(Float64) Longitude Nullable(Float64) Lat_Lon Tuple(Nullable(Float64), Nullable(Float64)) New Georeferenced Column Nullable(String) ``` عند هذه المرحلة، ينبغي التحقق من أن الأعمدة في ملف TSV تطابق الأسماء والأنواع المحددة في قسم **الأعمدة في مجموعة البيانات هذه** ضمن [صفحة ويب مجموعة البيانات](https://data.cityofnewyork.us/Public-Safety/NYPD-Complaint-Data-Current-Year-To-Date-/5uac-w243). أنواع البيانات هنا ليست دقيقة جدًا؛ إذ إن جميع الحقول الرقمية معيّنة إلى `Nullable(Float64)`، وجميع الحقول الأخرى هي `Nullable(String)`. وعند إنشاء جدول ClickHouse لتخزين البيانات، يمكنك تحديد أنواع أكثر ملاءمة وأفضل أداءً.
### حدِّد المخطط المناسب
لتحديد الأنواع التي ينبغي استخدامها للحقول، لا بد من معرفة طبيعة البيانات. على سبيل المثال، الحقل `JURISDICTION_CODE` ذو قيمة رقمية: هل ينبغي أن يكون `UInt8`، أم `Enum`، أم أن `Float64` هو الأنسب؟ ```sql title="Query" theme={null} clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \ --query \ "select JURISDICTION_CODE, count() FROM file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames') GROUP BY JURISDICTION_CODE ORDER BY JURISDICTION_CODE FORMAT PrettyCompact" ``` ```response title="Response" theme={null} ┌─JURISDICTION_CODE─┬─count()─┐ │ 0 │ 188875 │ │ 1 │ 4799 │ │ 2 │ 13833 │ │ 3 │ 656 │ │ 4 │ 51 │ │ 6 │ 5 │ │ 7 │ 2 │ │ 9 │ 13 │ │ 11 │ 14 │ │ 12 │ 5 │ │ 13 │ 2 │ │ 14 │ 70 │ │ 15 │ 20 │ │ 72 │ 159 │ │ 87 │ 9 │ │ 88 │ 75 │ │ 97 │ 405 │ └───────────────────┴─────────┘ ``` تُظهر استجابة `query` أن `JURISDICTION_CODE` مناسب جدًا لـ `UInt8`. وبالمثل، انظر إلى بعض حقول `String` وتحقق مما إذا كانت أنسب لأن تكون من نوع `DateTime` أو [`LowCardinality(String)`](/ar/reference/data-types/lowcardinality). على سبيل المثال، يُوصَف الحقل `PARKS_NM` بأنه "اسم حديقة أو ساحة لعب أو مساحة خضراء في مدينة نيويورك مرتبطة بالواقعة، إن وُجدت (ولا تشمل حدائق الولاية)". وقد تكون أسماء الحدائق في مدينة نيويورك مرشحًا جيدًا لـ `LowCardinality(String)`: ```sh title="Query" theme={null} clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \ --query \ "select count(distinct PARKS_NM) FROM file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames') FORMAT PrettyCompact" ``` ```response title="Response" theme={null} ┌─uniqExact(PARKS_NM)─┐ │ 319 │ └─────────────────────┘ ``` ألقِ نظرة على بعض أسماء الحدائق: ```sql title="Query" theme={null} clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \ --query \ "select distinct PARKS_NM FROM file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames') LIMIT 10 FORMAT PrettyCompact" ``` ```response title="Response" theme={null} ┌─PARKS_NM───────────────────┐ │ (null) │ │ ASSER LEVY PARK │ │ JAMES J WALKER PARK │ │ BELT PARKWAY/SHORE PARKWAY │ │ PROSPECT PARK │ │ MONTEFIORE SQUARE │ │ SUTTON PLACE PARK │ │ JOYCE KILMER PARK │ │ ALLEY ATHLETIC PLAYGROUND │ │ ASTORIA PARK │ └────────────────────────────┘ ``` تتضمن مجموعة البيانات المستخدمة وقت كتابة هذا النص بضع مئات فقط من المتنزهات وملاعب الأطفال المختلفة في العمود `PARK_NM`. وهذا عدد قليل وفقًا لتوصية [LowCardinality](/ar/reference/data-types/lowcardinality#description) بأن يبقى عدد السلاسل النصية المميزة في الحقل `LowCardinality(String)` دون 10,000.
### حقول DateTime
استنادًا إلى قسم **الأعمدة في مجموعة البيانات هذه** في [صفحة ويب مجموعة البيانات](https://data.cityofnewyork.us/Public-Safety/NYPD-Complaint-Data-Current-Year-To-Date-/5uac-w243)، توجد حقول للتاريخ والوقت تمثل بداية الحدث المُبلّغ عنه ونهايته. ويعطي النظر إلى القيمتين الدنيا والعليا للحقلين `CMPLNT_FR_DT` و`CMPLT_TO_DT` فكرة عمّا إذا كانت هذه الحقول تُملأ دائمًا أم لا: ```sh title="Query" theme={null} clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \ --query \ "select min(CMPLNT_FR_DT), max(CMPLNT_FR_DT) FROM file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames') FORMAT PrettyCompact" ``` ```response title="Response" theme={null} ┌─min(CMPLNT_FR_DT)─┬─max(CMPLNT_FR_DT)─┐ │ 01/01/1973 │ 12/31/2021 │ └───────────────────┴───────────────────┘ ``` ```sh title="Query" theme={null} clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \ --query \ "select min(CMPLNT_TO_DT), max(CMPLNT_TO_DT) FROM file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames') FORMAT PrettyCompact" ``` ```response title="Response" theme={null} ┌─min(CMPLNT_TO_DT)─┬─max(CMPLNT_TO_DT)─┐ │ │ 12/31/2021 │ └───────────────────┴───────────────────┘ ``` ```sh title="Query" theme={null} clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \ --query \ "select min(CMPLNT_FR_TM), max(CMPLNT_FR_TM) FROM file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames') FORMAT PrettyCompact" ``` ```response title="Response" theme={null} ┌─min(CMPLNT_FR_TM)─┬─max(CMPLNT_FR_TM)─┐ │ 00:00:00 │ 23:59:00 │ └───────────────────┴───────────────────┘ ``` ```sh title="Query" theme={null} clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \ --query \ "select min(CMPLNT_TO_TM), max(CMPLNT_TO_TM) FROM file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames') FORMAT PrettyCompact" ``` ```response title="Response" theme={null} ┌─min(CMPLNT_TO_TM)─┬─max(CMPLNT_TO_TM)─┐ │ (null) │ 23:59:00 │ └───────────────────┴───────────────────┘ ```
## ضع خطة
استنادًا إلى الاستقصاء أعلاه: * يجب إجراء cast لـ `JURISDICTION_CODE` إلى `UInt8`. * يجب إجراء cast لـ `PARKS_NM` إلى `LowCardinality(String)` * يتم دائمًا ملء `CMPLNT_FR_DT` و `CMPLNT_FR_TM` (وربما بوقت افتراضي هو `00:00:00`) * قد يكون `CMPLNT_TO_DT` و `CMPLNT_TO_TM` فارغين * تُخزَّن التواريخ والأوقات في حقول منفصلة في المصدر * التواريخ بالتنسيق `mm/dd/yyyy` * الأوقات بالتنسيق `hh:mm:ss` * يمكن دمج التواريخ والأوقات في أنواع DateTime * توجد بعض التواريخ السابقة لـ 1 يناير 1970، ما يعني أننا بحاجة إلى DateTime ‏64 بت هناك تغييرات أخرى كثيرة يجب إجراؤها على الأنواع، ويمكن تحديدها كلها باتباع خطوات الاستقصاء نفسها. انظر إلى عدد السلاسل النصية المميزة في الحقل، وإلى الحدين الأدنى والأقصى للقيم العددية، ثم اتخذ قراراتك. يحتوي مخطط الجدول الوارد لاحقًا في الدليل على كثير من السلاسل النصية منخفضة الكاردينالية وحقول الأعداد الصحيحة غير الموقعة، وعدد قليل جدًا من القيم العددية ذات الفاصلة العائمة.
## دمج حقلي التاريخ والوقت
لدمج حقلي التاريخ والوقت `CMPLNT_FR_DT` و`CMPLNT_FR_TM` في قيمة `String` واحدة يمكن تحويلها إلى `DateTime`، حدِّد الحقلين مع ربطهما بعامل الدمج: `CMPLNT_FR_DT || ' ' || CMPLNT_FR_TM`. ويُتعامل مع الحقلين `CMPLNT_TO_DT` و`CMPLNT_TO_TM` بالطريقة نفسها. ```sh title="Query" theme={null} clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \ --query \ "select CMPLNT_FR_DT || ' ' || CMPLNT_FR_TM AS complaint_begin FROM file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames') LIMIT 10 FORMAT PrettyCompact" ``` ```response title="Response" theme={null} ┌─complaint_begin─────┐ │ 07/29/2010 00:01:00 │ │ 12/01/2011 12:00:00 │ │ 04/01/2017 15:00:00 │ │ 03/26/2018 17:20:00 │ │ 01/01/2019 00:00:00 │ │ 06/14/2019 00:00:00 │ │ 11/29/2021 20:00:00 │ │ 12/04/2021 00:35:00 │ │ 12/05/2021 12:50:00 │ │ 12/07/2021 20:30:00 │ └─────────────────────┘ ```
## حوِّل String الخاص بالتاريخ والوقت إلى النوع DateTime64
اكتشفنا سابقًا في هذا الدليل أن هناك تواريخ في ملف TSV تسبق 1 يناير 1970، ما يعني أننا بحاجة إلى نوع DateTime64 لهذه التواريخ. كما يجب أيضًا تحويل التواريخ من التنسيق `MM/DD/YYYY` إلى التنسيق `YYYY/MM/DD`. ويمكن تنفيذ الأمرين معًا باستخدام [`parseDateTime64BestEffort()`](/ar/reference/functions/regular-functions/type-conversion-functions#parseDateTime64BestEffort). ```sh title="Query" theme={null} clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \ --query \ "WITH (CMPLNT_FR_DT || ' ' || CMPLNT_FR_TM) AS CMPLNT_START, (CMPLNT_TO_DT || ' ' || CMPLNT_TO_TM) AS CMPLNT_END select parseDateTime64BestEffort(CMPLNT_START) AS complaint_begin, parseDateTime64BestEffortOrNull(CMPLNT_END) AS complaint_end FROM file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames') ORDER BY complaint_begin ASC LIMIT 25 FORMAT PrettyCompact" ``` يحتوي السطران 2 و3 أعلاه على ناتج الدمج من الخطوة السابقة، ويحوّل السطران 4 و5 أعلاه السلاسل النصية إلى `DateTime64`. ونظرًا لأن وقت انتهاء الشكوى ليس مضمونًا وجوده، يُستخدم `parseDateTime64BestEffortOrNull`. ```response title="Response" theme={null} ┌─────────complaint_begin─┬───────────complaint_end─┐ │ 1925-01-01 10:00:00.000 │ 2021-02-12 09:30:00.000 │ │ 1925-01-01 11:37:00.000 │ 2022-01-16 11:49:00.000 │ │ 1925-01-01 15:00:00.000 │ 2021-12-31 00:00:00.000 │ │ 1925-01-01 15:00:00.000 │ 2022-02-02 22:00:00.000 │ │ 1925-01-01 19:00:00.000 │ 2022-04-14 05:00:00.000 │ │ 1955-09-01 19:55:00.000 │ 2022-08-01 00:45:00.000 │ │ 1972-03-17 11:40:00.000 │ 2022-03-17 11:43:00.000 │ │ 1972-05-23 22:00:00.000 │ 2022-05-24 09:00:00.000 │ │ 1972-05-30 23:37:00.000 │ 2022-05-30 23:50:00.000 │ │ 1972-07-04 02:17:00.000 │ ᴺᵁᴸᴸ │ │ 1973-01-01 00:00:00.000 │ ᴺᵁᴸᴸ │ │ 1975-01-01 00:00:00.000 │ ᴺᵁᴸᴸ │ │ 1976-11-05 00:01:00.000 │ 1988-10-05 23:59:00.000 │ │ 1977-01-01 00:00:00.000 │ 1977-01-01 23:59:00.000 │ │ 1977-12-20 00:01:00.000 │ ᴺᵁᴸᴸ │ │ 1981-01-01 00:01:00.000 │ ᴺᵁᴸᴸ │ │ 1981-08-14 00:00:00.000 │ 1987-08-13 23:59:00.000 │ │ 1983-01-07 00:00:00.000 │ 1990-01-06 00:00:00.000 │ │ 1984-01-01 00:01:00.000 │ 1984-12-31 23:59:00.000 │ │ 1985-01-01 12:00:00.000 │ 1987-12-31 15:00:00.000 │ │ 1985-01-11 09:00:00.000 │ 1985-12-31 12:00:00.000 │ │ 1986-03-16 00:05:00.000 │ 2022-03-16 00:45:00.000 │ │ 1987-01-07 00:00:00.000 │ 1987-01-09 00:00:00.000 │ │ 1988-04-03 18:30:00.000 │ 2022-08-03 09:45:00.000 │ │ 1988-07-29 12:00:00.000 │ 1990-07-27 22:00:00.000 │ └─────────────────────────┴─────────────────────────┘ ``` التواريخ المعروضة أعلاه على أنها `1925` ناتجة عن أخطاء في البيانات. توجد عدة سجلات في البيانات الأصلية تحمل تواريخ في السنوات `1019` - `1022`، بينما يُفترض أن تكون `2019` - `2022`. ويجري تخزينها على أنها 1 يناير 1925، لأنه أقدم تاريخ يدعمه `DateTime` بسعة 64 بت.
## إنشاء جدول
تنعكس القرارات المتخذة أعلاه بشأن أنواع البيانات المستخدمة للأعمدة في مخطط الجدول أدناه. نحتاج أيضًا إلى تحديد `ORDER BY` و`PRIMARY KEY` المستخدمين في الجدول. يجب تحديد واحد على الأقل من `ORDER BY` أو `PRIMARY KEY`. فيما يلي بعض الإرشادات لتحديد الأعمدة التي ينبغي تضمينها في `ORDER BY`، وستجد مزيدًا من المعلومات في قسم *الخطوات التالية* في نهاية هذا المستند.
### عبارتا `ORDER BY` و`PRIMARY KEY`
* يجب أن يتضمن الـ tuple الخاص بـ `ORDER BY` الحقولَ المستخدمة في عوامل تصفية الاستعلام * لتحقيق أقصى ضغط على القرص، يجب ترتيب الـ tuple الخاص بـ `ORDER BY` حسب الكاردينالية تصاعديًا * إذا وُجد، فيجب أن يكون الـ tuple الخاص بـ `PRIMARY KEY` مجموعة فرعية من الـ tuple الخاص بـ `ORDER BY` * إذا جرى تحديد `ORDER BY` فقط، فسيُستخدم الـ tuple نفسه بوصفه `PRIMARY KEY` * يُنشأ فهرس المفتاح الأساسي باستخدام الـ tuple الخاص بـ `PRIMARY KEY` إذا كان محددًا، وإلا فباستخدام الـ tuple الخاص بـ `ORDER BY` * يُحتفَظ بفهرس `PRIMARY KEY` في الذاكرة الرئيسية عند النظر إلى مجموعة البيانات والأسئلة التي يمكن الإجابة عنها من خلال الاستعلام عنها، قد نقرر أننا سننظر في أنواع الجرائم المُبلَّغ عنها بمرور الوقت في أحياء بورو الخمسة لمدينة نيويورك. ويمكن عندئذٍ تضمين هذه الحقول في `ORDER BY`: | العمود | الوصف (من قاموس البيانات) | | ---------- | -------------------------------- | | OFNS\_DESC | وصف الجريمة المقابل لرمز المفتاح | | RPT\_DT | تاريخ الإبلاغ عن الواقعة للشرطة | | BORO\_NM | اسم البورو الذي وقع فيه الحادث | الاستعلام عن ملف TSV لمعرفة الكاردينالية للأعمدة الثلاثة المرشحة: ```bash title="Query" theme={null} clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \ --query \ "select formatReadableQuantity(uniq(OFNS_DESC)) as cardinality_OFNS_DESC, formatReadableQuantity(uniq(RPT_DT)) as cardinality_RPT_DT, formatReadableQuantity(uniq(BORO_NM)) as cardinality_BORO_NM FROM file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames') FORMAT PrettyCompact" ``` ```response title="Response" theme={null} ┌─cardinality_OFNS_DESC─┬─cardinality_RPT_DT─┬─cardinality_BORO_NM─┐ │ 60.00 │ 306.00 │ 6.00 │ └───────────────────────┴────────────────────┴─────────────────────┘ ``` عند الترتيب حسب الكاردينالية، يصبح `ORDER BY` كما يلي: ```sql theme={null} ORDER BY ( BORO_NM, OFNS_DESC, RPT_DT ) ``` سيستخدم الجدول أدناه أسماء أعمدة أسهل قراءةً، وستُطابَق الأسماء الواردة أعلاه مع ```sql theme={null} ORDER BY ( borough, offense_description, date_reported ) ``` بجمع التغييرات على أنواع البيانات مع Tuple الخاصة بـ `ORDER BY`، نحصل على بنية الجدول التالية: ```sql theme={null} CREATE TABLE NYPD_Complaint ( complaint_number String, precinct UInt8, borough LowCardinality(String), complaint_begin DateTime64(0,'America/New_York'), complaint_end DateTime64(0,'America/New_York'), was_crime_completed String, housing_authority String, housing_level_code UInt32, jurisdiction_code UInt8, jurisdiction LowCardinality(String), offense_code UInt8, offense_level LowCardinality(String), location_descriptor LowCardinality(String), offense_description LowCardinality(String), park_name LowCardinality(String), patrol_borough LowCardinality(String), PD_CD UInt16, PD_DESC String, location_type LowCardinality(String), date_reported Date, transit_station LowCardinality(String), suspect_age_group LowCardinality(String), suspect_race LowCardinality(String), suspect_sex LowCardinality(String), transit_district UInt8, victim_age_group LowCardinality(String), victim_race LowCardinality(String), victim_sex LowCardinality(String), NY_x_coordinate UInt32, NY_y_coordinate UInt32, Latitude Float64, Longitude Float64 ) ENGINE = MergeTree ORDER BY ( borough, offense_description, date_reported ) ```
### العثور على المفتاح الأساسي لجدول ما
تحتوي قاعدة البيانات `system` في ClickHouse، وتحديدًا `system.table`، على جميع المعلومات المتعلقة بالجدول الذي أنشأته للتو. يوضّح هذا الاستعلام `ORDER BY` (مفتاح الترتيب)، و`PRIMARY KEY`: ```sql theme={null} SELECT partition_key, sorting_key, primary_key, table FROM system.tables WHERE table = 'NYPD_Complaint' FORMAT Vertical ``` الاستجابة ```response theme={null} Query id: 6a5b10bf-9333-4090-b36e-c7f08b1d9e01 Row 1: ────── partition_key: sorting_key: borough, offense_description, date_reported primary_key: borough, offense_description, date_reported table: NYPD_Complaint 1 row in set. Elapsed: 0.001 sec. ```
## المعالجة المسبقة واستيراد البيانات
سنستخدم أداة `clickhouse-local` لإجراء المعالجة المسبقة للبيانات، و`clickhouse-client` لتحميلها.
### وسائط `clickhouse-local` المستخدمة
يظهر `table='input'` في وسائط `clickhouse-local` أدناه. يأخذ `clickhouse-local` الإدخال الموفَّر (`cat ${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv`) ويُدرجه في جدول. ويكون اسم الجدول افتراضيًا `table`. في هذا الدليل، ضُبط اسم الجدول على `input` لتوضيح تدفق البيانات بشكل أفضل. أما الوسيطة الأخيرة لـ `clickhouse-local` فهي استعلام يحدد البيانات من الجدول (`FROM input`)، ثم يُمرَّر إلى `clickhouse-client` لملء الجدول `NYPD_Complaint`. ```sql theme={null} cat ${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv \ | clickhouse-local --table='input' --input-format='TSVWithNames' \ --input_format_max_rows_to_read_for_schema_inference=2000 \ --query " WITH (CMPLNT_FR_DT || ' ' || CMPLNT_FR_TM) AS CMPLNT_START, (CMPLNT_TO_DT || ' ' || CMPLNT_TO_TM) AS CMPLNT_END SELECT CMPLNT_NUM AS complaint_number, ADDR_PCT_CD AS precinct, BORO_NM AS borough, parseDateTime64BestEffort(CMPLNT_START) AS complaint_begin, parseDateTime64BestEffortOrNull(CMPLNT_END) AS complaint_end, CRM_ATPT_CPTD_CD AS was_crime_completed, HADEVELOPT AS housing_authority_development, HOUSING_PSA AS housing_level_code, JURISDICTION_CODE AS jurisdiction_code, JURIS_DESC AS jurisdiction, KY_CD AS offense_code, LAW_CAT_CD AS offense_level, LOC_OF_OCCUR_DESC AS location_descriptor, OFNS_DESC AS offense_description, PARKS_NM AS park_name, PATROL_BORO AS patrol_borough, PD_CD, PD_DESC, PREM_TYP_DESC AS location_type, toDate(parseDateTimeBestEffort(RPT_DT)) AS date_reported, STATION_NAME AS transit_station, SUSP_AGE_GROUP AS suspect_age_group, SUSP_RACE AS suspect_race, SUSP_SEX AS suspect_sex, TRANSIT_DISTRICT AS transit_district, VIC_AGE_GROUP AS victim_age_group, VIC_RACE AS victim_race, VIC_SEX AS victim_sex, X_COORD_CD AS NY_x_coordinate, Y_COORD_CD AS NY_y_coordinate, Latitude, Longitude FROM input" \ | clickhouse-client --query='INSERT INTO NYPD_Complaint FORMAT TSV' ```
## تحقّق من البيانات
تتغير مجموعة البيانات مرة واحدة أو أكثر سنويًا، لذا قد لا تتطابق أعدادك مع ما يرد في هذا المستند. ```sql title="Query" theme={null} SELECT count() FROM NYPD_Complaint ``` ```text title="Response" theme={null} ┌─count()─┐ │ 208993 │ └─────────┘ 1 row in set. Elapsed: 0.001 sec. ``` حجم مجموعة البيانات في ClickHouse لا يزيد عن 12% من ملف TSV الأصلي، قارن حجم ملف TSV الأصلي بحجم الجدول: ```sql title="Query" theme={null} SELECT formatReadableSize(total_bytes) FROM system.tables WHERE name = 'NYPD_Complaint' ``` ```text title="Response" theme={null} ┌─formatReadableSize(total_bytes)─┐ │ 8.63 MiB │ └─────────────────────────────────┘ ```
## نفّذ بعض الاستعلامات
### الاستعلام 1. قارن عدد الشكاوى حسب الأشهر
```sql title="Query" theme={null} SELECT dateName('month', date_reported) AS month, count() AS complaints, bar(complaints, 0, 50000, 80) FROM NYPD_Complaint GROUP BY month ORDER BY complaints DESC ``` ```response title="Response" theme={null} Query id: 7fbd4244-b32a-4acf-b1f3-c3aa198e74d9 ┌─month─────┬─complaints─┬─bar(count(), 0, 50000, 80)───────────────────────────────┐ │ March │ 34536 │ ███████████████████████████████████████████████████████▎ │ │ May │ 34250 │ ██████████████████████████████████████████████████████▋ │ │ April │ 32541 │ ████████████████████████████████████████████████████ │ │ January │ 30806 │ █████████████████████████████████████████████████▎ │ │ February │ 28118 │ ████████████████████████████████████████████▊ │ │ November │ 7474 │ ███████████▊ │ │ December │ 7223 │ ███████████▌ │ │ October │ 7070 │ ███████████▎ │ │ September │ 6910 │ ███████████ │ │ August │ 6801 │ ██████████▊ │ │ June │ 6779 │ ██████████▋ │ │ July │ 6485 │ ██████████▍ │ └───────────┴────────────┴──────────────────────────────────────────────────────────┘ 12 rows in set. Elapsed: 0.006 sec. Processed 208.99 thousand rows, 417.99 KB (37.48 million rows/s., 74.96 MB/s.) ```
### الاستعلام 2. قارن إجمالي عدد الشكاوى حسب البورو
```sql title="Query" theme={null} SELECT borough, count() AS complaints, bar(complaints, 0, 125000, 60) FROM NYPD_Complaint GROUP BY borough ORDER BY complaints DESC ``` ```response title="Response" theme={null} Query id: 8cdcdfd4-908f-4be0-99e3-265722a2ab8d ┌─borough───────┬─complaints─┬─bar(count(), 0, 125000, 60)──┐ │ BROOKLYN │ 57947 │ ███████████████████████████▋ │ │ MANHATTAN │ 53025 │ █████████████████████████▍ │ │ QUEENS │ 44875 │ █████████████████████▌ │ │ BRONX │ 44260 │ █████████████████████▏ │ │ STATEN ISLAND │ 8503 │ ████ │ │ (null) │ 383 │ ▏ │ └───────────────┴────────────┴──────────────────────────────┘ 6 rows in set. Elapsed: 0.008 sec. Processed 208.99 thousand rows, 209.43 KB (27.14 million rows/s., 27.20 MB/s.) ```
## الخطوات التالية
تستعرض [مقدمة عملية عن الفهارس الأولية المتناثرة في ClickHouse](/ar/guides/clickhouse/data-modelling/sparse-primary-indexes) الاختلافات بين الفهرسة في ClickHouse والفهرسة في قواعد البيانات العلائقية التقليدية، وكيفية إنشاء ClickHouse لفهرس أولي متناثر واستخدامه، وأفضل ممارسات الفهرسة.