Данные о жалобах NYPD

Загрузка данных в формате Tab Separated Value и запросы к ним за 5 шагов

Файлы в формате Tab Separated Value, или TSV, широко распространены и могут содержать имена полей в первой строке. ClickHouse умеет загружать TSV-файлы, а также выполнять запросы к ним без загрузки самих файлов. В этом руководстве рассматриваются оба варианта. Если вам нужно выполнять запросы к CSV-файлам или загружать их, подойдут те же методы — просто замените TSV на CSV в аргументах формата.

В ходе работы с этим руководством вы:

• Исследуете: выполните запросы к структуре и содержимому TSV-файла.
• Определите целевую схему ClickHouse: выберите подходящие типы данных и сопоставьте с ними существующие данные.
• Создадите таблицу ClickHouse.
• Предварительно обработаете и передадите потоково данные в ClickHouse.
• Выполните несколько запросов к ClickHouse.

Набор данных, используемый в этом руководстве, предоставлен командой NYC Open Data и содержит данные обо «всех подтверждённых преступлениях категорий felony, misdemeanor и violation, о которых сообщалось в New York City Police Department (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 and client

​

Примечание о командах, описанных в этом руководстве

В этом руководстве используются два типа команд:

• Некоторые команды выполняют запросы к TSV-файлам; их запускают в командной строке.
• Остальные команды выполняют запросы к ClickHouse; их запускают в clickhouse-client или в интерфейсе Play.

Примеры в этом руководстве предполагают, что вы сохранили файл в формате 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)

В большинстве случаев приведённая выше команда покажет, какие поля во входных данных являются числовыми, какие — строковыми, а какие — кортежами. Но так бывает не всегда. Поскольку ClickHouse обычно работает с датасетами, содержащими миллиарды записей, по умолчанию для определения схемы анализируется лишь ограниченное число строк (100), чтобы не выполнять парсинг миллиардов строк. Приведённый ниже результат может отличаться от того, что увидите вы, так как датасет обновляется несколько раз в год. Если заглянуть в словарь данных, можно увидеть, что CMPLNT_NUM указан как текстовое, а не числовое поле. Переопределив значение по умолчанию — 100 строк для определения схемы — с помощью настройки SETTINGS input_format_max_rows_to_read_for_schema_inference=2000, можно получить более точное представление о содержимом.

Примечание: начиная с версии 22.5 значением по умолчанию теперь являются 25 000 строк для определения схемы, поэтому изменяйте эту настройку только если используете более старую версию или если вам нужно, чтобы в выборку попало более 25 000 строк.

Выполните эту команду в командной строке. Для запроса данных в загруженном файле в формате TSV вы будете использовать clickhouse-local.

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 │
└───────────────────┴─────────┘

Ответ на запрос показывает, что 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 — не более 10 000 различных строковых значений в поле LowCardinality(String).

​

Поля DateTime

Судя по разделу Columns in this Dataset на странице набора данных, в нём есть поля даты и времени, указывающие начало и окончание зарегистрированного события. Просмотр минимальных и максимальных значений 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          │
└───────────────────┴───────────────────┘

​

Составьте план

На основе приведённого выше анализа:

• JURISDICTION_CODE следует привести к типу UInt8.
• 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 года, а это значит, что нужен 64-битный DateTime

Нужно внести ещё много изменений в типы, и все их можно определить, выполнив те же шаги анализа. Посмотрите на количество различных строковых значений в поле, минимальные и максимальные числовые значения и примите решение. В схеме таблицы, приведённой далее в руководстве, много строковых полей низкой кардинальности и беззнаковых целочисленных полей, и очень мало чисел с плавающей запятой.

​

Объедините поля даты и времени

Чтобы объединить поля даты и времени 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 │
└─────────────────────┘

​

Преобразование строки с датой и временем в тип DateTime64

Ранее в этом руководстве мы выяснили, что в файле в формате TSV есть даты до 1 января 1970 года, а это значит, что для них нужен 64-битный тип DateTime. Даты также нужно преобразовать из формата 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 года, поскольку это самая ранняя дата, поддерживаемая 64-битным DateTime.

​

Создание таблицы

Принятые выше решения о типах данных, используемых для столбцов, отражены в приведённой ниже схеме таблицы. Нам также нужно определить ORDER BY и PRIMARY KEY для таблицы. Как минимум один из параметров ORDER BY или PRIMARY KEY должен быть указан. Ниже приведены рекомендации по выбору столбцов для включения в ORDER BY; более подробная информация приведена в разделе Следующие шаги в конце этого документа.

​

Секции ORDER BY и PRIMARY KEY

• Кортеж ORDER BY должен включать поля, используемые в фильтрах запроса
• Чтобы максимально увеличить сжатие на диске, кортеж ORDER BY следует упорядочить по возрастанию мощности
• Если PRIMARY KEY задан, этот кортеж должен быть подмножеством кортежа ORDER BY
• Если указан только ORDER BY, тот же кортеж будет использоваться как PRIMARY KEY
• Индекс PRIMARY KEY создается по кортежу PRIMARY KEY, если он указан, иначе — по кортежу 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 )

С учётом изменений в типах данных и кортежа 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 )

​

Поиск первичного ключа таблицы

База данных ClickHouse system, а точнее 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 строит и использует разреженный первичный индекс, а также о лучших практиках индексирования.