Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

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

View as MarkdownEdit this pageReport an issueSet up docs MCP

Файлы в формате 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

Необходимые условия

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

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

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

Ознакомьтесь с файлом в формате TSV

Прежде чем приступить к работе с базой данных ClickHouse, ознакомьтесь с данными.

Посмотрите на поля в исходном файле в формате TSV

Это пример команды для выполнения запроса к файлу в формате TSV, но пока не запускайте её.

Querysh
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)

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

Querysh
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')"
Responseresponse
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?

Querysql
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"
Responseresponse
┌─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):

Querysh
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"
Responseresponse
┌─uniqExact(PARKS_NM)─┐
│                 319 │
└─────────────────────┘

Обратите внимание на названия некоторых парков:

Querysql
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"
Responseresponse
┌─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 позволяет понять, всегда ли эти поля заполнены:

Querysh
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"
Responseresponse
┌─min(CMPLNT_FR_DT)─┬─max(CMPLNT_FR_DT)─┐
│ 01/01/1973        │ 12/31/2021        │
└───────────────────┴───────────────────┘
Querysh
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"
Responseresponse
┌─min(CMPLNT_TO_DT)─┬─max(CMPLNT_TO_DT)─┐
│                   │ 12/31/2021        │
└───────────────────┴───────────────────┘
Querysh
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"
Responseresponse
┌─min(CMPLNT_FR_TM)─┬─max(CMPLNT_FR_TM)─┐
│ 00:00:00          │ 23:59:00          │
└───────────────────┴───────────────────┘
Querysh
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"
Responseresponse
┌─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.

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

Querysh
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.

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

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

Принятые выше решения о типах данных, используемых для столбцов, отражены в приведённой ниже схеме таблицы. Нам также нужно определить 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, чтобы определить мощность трех столбцов-кандидатов:

Querybash
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"
Responseresponse
┌─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 структура таблицы будет такой:

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

cat ${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv \

Проверьте данные

Querysql
SELECT count()
FROM NYPD_Complaint
Responsetext
┌─count()─┐
│  208993 │
└─────────┘

1 row in set. Elapsed: 0.001 sec.

Объём данных в ClickHouse составляет всего 12 % от размера исходного файла в формате TSV; сравните размер исходного файла в формате TSV с размером таблицы:

Querysql
SELECT formatReadableSize(total_bytes)
FROM system.tables
WHERE name = 'NYPD_Complaint'
Responsetext
┌─formatReadableSize(total_bytes)─┐
│ 8.63 MiB                        │
└─────────────────────────────────┘

Выполните несколько запросов

Запрос 1. Сравните количество жалоб по месяцам

Querysql
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
Responseresponse
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. Сравните общее число жалоб по боро

Querysql
SELECT
    borough,
    count() AS complaints,
    bar(complaints, 0, 125000, 60)
FROM NYPD_Complaint
GROUP BY borough
ORDER BY complaints DESC
Responseresponse
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 строит и использует разреженный первичный индекс, а также о лучших практиках индексирования.

Navigation