Перейти к основному содержимому
Перейти к основному содержимому

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

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

Во время работы с этим гидом вы:

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

Данный набор данных был получен из команды NYC Open Data и содержит данные о "всех действительных delitos, проступках и правонарушениях, сообщённых в Полицейский департамент Нью-Йорка (NYPD)". На момент написания размер файла данных составляет 166 МБ, но он регулярно обновляется.

Источник: data.cityofnewyork.us
Условия использования: https://www1.nyc.gov/home/terms-of-use.page

Предварительные требования​

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

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

  • Некоторые команды запрашивают файлы TSV, эти команды выполняются в командной строке.
  • Остальные команды запрашивают ClickHouse, и эти команды выполняются в clickhouse-client или Play UI.
примечание

Примеры в этом руководстве предполагают, что вы сохранили файл TSV в ${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv, пожалуйста, при необходимости скорректируйте команды.

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

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

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

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

Пример результата

подсказка

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

Результат:

На этом этапе вам следует проверить, что колонки в файле TSV соответствуют именам и типам, указанным в разделе Columns in this Dataset на веб-странице набора данных. Типы данных не очень специфичны, все числовые поля установлены как Nullable(Float64), а все остальные поля — как Nullable(String). Когда вы создадите таблицу ClickHouse для хранения данных, вы сможете указать более подходящие и производительные типы.

Определите правильную схему​

Чтобы выяснить, какие типы следует использовать для полей, необходимо знать, как выглядят данные. Например, поле JURISDICTION_CODE является числовым: должен ли оно быть UInt8, или Enum, или же Float64 подходит?

Результат:

Ответ запроса показывает, что JURISDICTION_CODE хорошо помещается в UInt8.

Аналогично, посмотрите на некоторые поля String и проверьте, подходят ли они для типов DateTime или LowCardinality(String).

Например, поле PARKS_NM описывается как "Название парка, игровой площадки или зеленого пространства NYC, если применимо (государственные парки не включены)". Названия парков в Нью-Йорке могут быть хорошим кандидатом для LowCardinality(String):

Результат:

Посмотрите на некоторые названия парков:

Результат:

Набор данных, использованный на момент написания, содержит всего несколько сотен уникальных парков и игровых площадок в колонке PARK_NM. Это небольшое количество, согласно рекомендации LowCardinality оставаться ниже 10,000 уникальных строк в поле LowCardinality(String).

Поля DateTime​

Согласно разделу Columns in this Dataset на веб-странице набора данных, есть поля даты и времени для начала и окончания зарегистрированного события. Посмотрев на минимум и максимум полей CMPLNT_FR_DT и CMPLT_TO_DT, мы можем получить представление о том, всегда ли эти поля заполнены:

Результат:

Результат:

Результат:

Результат:

Постройте план​

Основываясь на вышеизложенном исследовании:

  • 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 обрабатываются аналогично.

Результат:

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

Ранее в руководстве мы обнаружили, что в файле TSV есть даты до 1 января 1970 года, что означает, что нам нужен 64-битный тип DateTime для хранения дат. Даты также необходимо преобразовать из формата MM/DD/YYYY в формат YYYY/MM/DD. Оба этих преобразования можно выполнить с помощью parseDateTime64BestEffort().

В строках 2 и 3 выше содержится конкатенация из предыдущего шага, а строки 4 и 5 выше разбирают строки в DateTime64. Поскольку время окончания жалобы не гарантировано, используется parseDateTime64BestEffortOrNull.

Результат:

примечание

Даты, показанные как 1925, являются следствием ошибок в данных. В оригинальных данных есть несколько записей с датами в годах 1019 - 1022, которые должны быть 2019 - 2022. Они хранятся как 1 января 1925 года, поскольку это самая ранняя дата с 64-битным DateTime.

Создайте таблицу​

Решения, принятые выше относительно типов данных для столбцов, отражены в схеме таблицы ниже. Нам также нужно решить, каковы будут ORDER BY и PRIMARY KEY, используемые для таблицы. Должен быть указан хотя бы один из ORDER BY или PRIMARY KEY. Вот некоторые рекомендации по выбору столбцов для включения в ORDER BY, а более подробная информация приведена в разделе Next Steps в конце этого документа.

Clauses ORDER BY и PRIMARY KEY​

  • Кортеж ORDER BY должен включать поля, которые используются в фильтрах запросов
  • Чтобы максимизировать сжатие на диске, кортеж ORDER BY должен быть упорядочен по возрастанию кардинальности
  • Если он существует, кортеж PRIMARY KEY должен быть подмножеством кортежа ORDER BY
  • Если указан только ORDER BY, тогда будет использоваться тот же самый кортеж как PRIMARY KEY
  • Индекс первичного ключа создаётся с использованием кортежа PRIMARY KEY, если он указан, в противном случае используется кортеж ORDER BY
  • Индекс PRIMARY KEY хранится в основной памяти

С учетом набора данных и вопросов, на которые можно ответить, запрашивая его, мы можем решить, что хотим рассмотреть виды преступлений, сообщаемых за всё время в пяти районах Нью-Йорка. Эти поля могут быть включены в ORDER BY:

СтолбецОписание (из словаря данных)
OFNS_DESCОписание правонарушения, соответствующее ключевому коду
RPT_DTДата, когда событие было сообщено полиции
BORO_NMНазвание района, в котором случился инцидент

Запрашивая файл TSV для кардинальности трёх кандидатов:

Результат:

Упорядочивая по кардинальности, ORDER BY становится:

примечание

Таблица ниже будет использовать более удобочитаемые названия колонок, вышеуказанные имена будут сопоставлены с

Собирая изменения в типах данных и кортеж ORDER BY, мы получаем такую структуру таблицы:

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

База данных ClickHouse system, в частности system.tables, содержит всю информацию о таблице, которую вы только что создали. Этот запрос показывает ORDER BY (ключ сортировки) и PRIMARY KEY:

Ответ

Предобработка и импорт данных​

Мы будем использовать инструмент 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.

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

примечание

Набор данных меняется один или несколько раз в год, ваши подсчёты могут не совпадать с тем, что указано в этом документе.

Запрос:

Результат:

Размер набора данных в ClickHouse составляет всего 12% от оригинального TSV-файла, сравните размер оригинального TSV-файла с размером таблицы:

Запрос:

Результат:

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

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

Запрос:

Результат:

Запрос 2. Сравните общее количество жалоб по району​

Запрос:

Результат:

Следующие шаги​

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