بيانات شكاوى NYPD

إدخال بيانات القيم المفصولة بعلامات جدولة والاستعلام عنها في 5 خطوات

تُعد ملفات القيم المفصولة بعلامات جدولة، أو TSV، شائعة، وقد تتضمن عناوين الحقول في السطر الأول من الملف. يمكن لـ ClickHouse إدخال ملفات TSV، كما يمكنه أيضًا الاستعلام عنها دون إدخالها. يغطي هذا الدليل كلتا الحالتين. وإذا كنت بحاجة إلى الاستعلام عن ملفات CSV أو إدخالها، فستنجح الأساليب نفسها؛ ما عليك سوى استبدال TSV بـ CSV في وسائط التنسيق.

أثناء اتباع هذا الدليل، ستقوم بما يلي:

• الاستكشاف: الاستعلام عن بنية ملف TSV ومحتواه.
• تحديد مخطط ClickHouse المستهدف: اختيار أنواع البيانات المناسبة وربط البيانات الحالية بهذه الأنواع.
• إنشاء جدول ClickHouse.
• المعالجة المسبقة للبيانات وبثّها إلى ClickHouse.
• تشغيل بعض الاستعلامات على ClickHouse.

تأتي مجموعة البيانات المستخدمة في هذا الدليل من فريق NYC Open Data، وتتضمن بيانات عن “جميع جرائم الجنايات والجنح والمخالفات الصالحة المُبلّغ عنها إلى إدارة شرطة مدينة نيويورك (NYPD)”. وعند كتابة هذا الدليل، كان حجم ملف البيانات 166 ميغابايت، لكنه يُحدَّث بانتظام.

المصدر: data.cityofnewyork.us شروط الاستخدام: https://www1.nyc.gov/home/terms-of-use.page

​

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

• نزّل مجموعة البيانات بزيارة صفحة NYPD Complaint Data Current (Year To Date)، ثم انقر على زر Export واختر TSV for Excel.
• ثبّت ClickHouse server والعميل

​

ملاحظة حول الأوامر الموضَّحة في هذا الدليل

يوجد نوعان من الأوامر في هذا الدليل:

• بعض الأوامر تستعلم من ملفات TSV، وتُشغَّل من موجّه الأوامر.
• أما بقية الأوامر فتستعلم من ClickHouse، وتُشغَّل في clickhouse-client أو في واجهة Play UI.

تفترض الأمثلة في هذا الدليل أنك حفظت ملف TSV في ${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv، لذا يُرجى تعديل الأوامر عند الحاجة.

​

تعرّف على ملف TSV

قبل البدء في العمل مع قاعدة بيانات ClickHouse، اطّلع على البيانات.

​

اطّلع على الحقول في ملف TSV المصدر

هذا مثال على أمر للاستعلام عن ملف TSV، ولكن لا تُشغِّله بعد.

Query

clickhouse-local --query \
"describe file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')"

مثال على الاستجابة

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 صف) من أجل استنتاج المخطط، وذلك لتجنّب تحليل مليارات الصفوف لاستنتاج المخطط. قد لا تتطابق الاستجابة أدناه مع ما تراه لديك، لأن مجموعة البيانات تُحدَّث عدة مرات كل عام. وبالاطلاع على قاموس البيانات، يمكنك أن ترى أن CMPLNT_NUM محدد كنص، وليس كقيمة رقمية. ومن خلال تجاوز القيمة الافتراضية البالغة 100 صف للاستدلال باستخدام الإعداد SETTINGS input_format_max_rows_to_read_for_schema_inference=2000 يمكنك الحصول على فكرة أدق عن المحتوى.

ملاحظة: اعتبارًا من الإصدار 22.5، أصبحت القيمة الافتراضية الآن 25,000 صف لاستنتاج المخطط، لذا لا تغيّر هذا الإعداد إلا إذا كنت تستخدم إصدارًا أقدم أو إذا كنت بحاجة إلى أخذ عينة تتجاوز 25,000 صف.

نفّذ هذا الأمر في موجّه الأوامر. ستستخدم clickhouse-local لإجراء استعلام على البيانات الموجودة في ملف TSV الذي نزّلته.

Query

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

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 تطابق الأسماء والأنواع المحددة في قسم الأعمدة في مجموعة البيانات هذه ضمن صفحة ويب مجموعة البيانات. أنواع البيانات هنا ليست دقيقة جدًا؛ إذ إن جميع الحقول الرقمية معيّنة إلى Nullable(Float64)، وجميع الحقول الأخرى هي Nullable(String). وعند إنشاء جدول ClickHouse لتخزين البيانات، يمكنك تحديد أنواع أكثر ملاءمة وأفضل أداءً.

​

حدِّد المخطط المناسب

لتحديد الأنواع التي ينبغي استخدامها للحقول، لا بد من معرفة طبيعة البيانات. على سبيل المثال، الحقل JURISDICTION_CODE ذو قيمة رقمية: هل ينبغي أن يكون UInt8، أم Enum، أم أن Float64 هو الأنسب؟

Query

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

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

على سبيل المثال، يُوصَف الحقل PARKS_NM بأنه “اسم حديقة أو ساحة لعب أو مساحة خضراء في مدينة نيويورك مرتبطة بالواقعة، إن وُجدت (ولا تشمل حدائق الولاية)”. وقد تكون أسماء الحدائق في مدينة نيويورك مرشحًا جيدًا لـ LowCardinality(String):

Query

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

┌─uniqExact(PARKS_NM)─┐
│                 319 │
└─────────────────────┘

ألقِ نظرة على بعض أسماء الحدائق:

Query

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

┌─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 بأن يبقى عدد السلاسل النصية المميزة في الحقل LowCardinality(String) دون 10,000.

​

حقول DateTime

استنادًا إلى قسم الأعمدة في مجموعة البيانات هذه في صفحة ويب مجموعة البيانات، توجد حقول للتاريخ والوقت تمثل بداية الحدث المُبلّغ عنه ونهايته. ويعطي النظر إلى القيمتين الدنيا والعليا للحقلين CMPLNT_FR_DT وCMPLT_TO_DT فكرة عمّا إذا كانت هذه الحقول تُملأ دائمًا أم لا:

Query

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

┌─min(CMPLNT_FR_DT)─┬─max(CMPLNT_FR_DT)─┐
│ 01/01/1973        │ 12/31/2021        │
└───────────────────┴───────────────────┘

Query

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

┌─min(CMPLNT_TO_DT)─┬─max(CMPLNT_TO_DT)─┐
│                   │ 12/31/2021        │
└───────────────────┴───────────────────┘

Query

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

┌─min(CMPLNT_FR_TM)─┬─max(CMPLNT_FR_TM)─┐
│ 00:00:00          │ 23:59:00          │
└───────────────────┴───────────────────┘

Query

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

┌─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 بالطريقة نفسها.

Query

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

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

Query

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

┌─────────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 لمعرفة الكاردينالية للأعمدة الثلاثة المرشحة:

Query

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

┌─cardinality_OFNS_DESC─┬─cardinality_RPT_DT─┬─cardinality_BORO_NM─┐
│ 60.00                 │ 306.00             │ 6.00                │
└───────────────────────┴────────────────────┴─────────────────────┘

عند الترتيب حسب الكاردينالية، يصبح ORDER BY كما يلي:

ORDER BY ( BORO_NM, OFNS_DESC, RPT_DT )

سيستخدم الجدول أدناه أسماء أعمدة أسهل قراءةً، وستُطابَق الأسماء الواردة أعلاه مع

ORDER BY ( borough, offense_description, date_reported )

بجمع التغييرات على أنواع البيانات مع Tuple الخاصة بـ ORDER BY، نحصل على بنية الجدول التالية:

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:

SELECT
    partition_key,
    sorting_key,
    primary_key,
    table
FROM system.tables
WHERE table = 'NYPD_Complaint'
FORMAT Vertical

الاستجابة

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.

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'

​

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

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

Query

SELECT count()
FROM NYPD_Complaint

Response

┌─count()─┐
│  208993 │
└─────────┘

1 row in set. Elapsed: 0.001 sec.

حجم مجموعة البيانات في ClickHouse لا يزيد عن 12% من ملف TSV الأصلي، قارن حجم ملف TSV الأصلي بحجم الجدول:

Query

SELECT formatReadableSize(total_bytes)
FROM system.tables
WHERE name = 'NYPD_Complaint'

Response

┌─formatReadableSize(total_bytes)─┐
│ 8.63 MiB                        │
└─────────────────────────────────┘

​

نفّذ بعض الاستعلامات

​

الاستعلام 1. قارن عدد الشكاوى حسب الأشهر

Query

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

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. قارن إجمالي عدد الشكاوى حسب البورو

Query

SELECT
    borough,
    count() AS complaints,
    bar(complaints, 0, 125000, 60)
FROM NYPD_Complaint
GROUP BY borough
ORDER BY complaints DESC

Response

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 الاختلافات بين الفهرسة في ClickHouse والفهرسة في قواعد البيانات العلائقية التقليدية، وكيفية إنشاء ClickHouse لفهرس أولي متناثر واستخدامه، وأفضل ممارسات الفهرسة.