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

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

Файлы формата Tab Separated Value или TSV часто используются и могут содержать заголовки полей в первой строке файла. ClickHouse может принимать 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​

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

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

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

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