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

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

Файлы с разделителем табуляции или TSV распространены и могут содержать названия полей в первой строке файла. ClickHouse может принимать TSV и также может запрашивать 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

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

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

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

  • Некоторые команды запрашивают файлы 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 соответствуют именам и типам, указанным в разделе Столбцы в этом наборе данных на веб-странице набора данных. Типы данных не очень специфичны, все числовые поля установлены в 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​

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

Порядок и первичный ключ​

  • Кортеж 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 строит и использует разреженный первичный индекс, и лучшие практики по индексации.