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

Данные о жалобах 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 совпадают с именами и типами, указанными в разделе 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 на веб-странице набора данных, существуют поля даты и времени для начала и окончания сообщенного события. Просмотр min и max 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.
примечание

Существует много других изменений, которые необходимо внести в типы, они все могут быть определены, следуя тем же шагам исследования. Рассмотрите количество различных строк в поле, min и max чисел и примите свои решения. Структура таблицы, приведенная позже в руководстве, имеет много строк с низкой кардинальностью и полей беззнакового целого числа и очень мало числовых значений с плавающей точкой.

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

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