TSV на CSV в аргументах формата.
В ходе работы с этим руководством вы:
- Исследуете: выполните запросы к структуре и содержимому TSV-файла.
- Определите целевую схему ClickHouse: выберите подходящие типы данных и сопоставьте с ними существующие данные.
- Создадите таблицу ClickHouse.
- Предварительно обработаете и передадите потоково данные в ClickHouse.
- Выполните несколько запросов к ClickHouse.
- Скачайте набор данных: перейдите на страницу NYPD Complaint Data Current (Year To Date), нажмите кнопку Export и выберите TSV for Excel.
- Установите ClickHouse server and client
- Некоторые команды выполняют запросы к TSV-файлам; их запускают в командной строке.
- Остальные команды выполняют запросы к ClickHouse; их запускают в
clickhouse-clientили в интерфейсе Play.
Примеры в этом руководстве предполагают, что вы сохранили файл в формате TSV по пути
${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv; при необходимости скорректируйте команды.Query
clickhouse-local.
Query
Response
Nullable(Float64), а для всех остальных — Nullable(String). При создании таблицы ClickHouse для хранения данных можно указать более подходящие и эффективные типы.
Чтобы понять, какие типы следует использовать для полей, нужно знать, как выглядят данные. Например, поле JURISDICTION_CODE содержит числовое значение: следует ли использовать UInt8, Enum или подойдет Float64?
Query
Response
JURISDICTION_CODE хорошо укладывается в UInt8.
Аналогично посмотрите на некоторые поля типа String и проверьте, не лучше ли представить их как поля DateTime или LowCardinality(String).
Например, поле PARKS_NM описывается как “Название парка, игровой площадки или зелёной зоны в Нью-Йорке, где произошло событие, если применимо (парки штата не включены)”. Названия парков в Нью-Йорке могут быть хорошим кандидатом для LowCardinality(String):
Query
Response
Query
Response
PARK_NM. Это немного, если учитывать рекомендацию LowCardinality — не более 10 000 различных строковых значений в поле LowCardinality(String).
Судя по разделу Columns in this Dataset на странице набора данных, в нём есть поля даты и времени, указывающие начало и окончание зарегистрированного события. Просмотр минимальных и максимальных значений CMPLNT_FR_DT и CMPLT_TO_DT позволяет понять, всегда ли эти поля заполнены:
Query
Response
Query
Response
Query
Response
Query
Response
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.
Query
Response
MM/DD/YYYY в формат YYYY/MM/DD. И то и другое можно сделать с помощью parseDateTime64BestEffort().
Query
DateTime64. Поскольку время окончания жалобы может отсутствовать, используется parseDateTime64BestEffortOrNull.
Response
Показанные выше даты
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создается по кортежуPRIMARY KEY, если он указан, иначе — по кортежуORDER BY - Индекс
PRIMARY KEYхранится в оперативной памяти
ORDER BY можно включить следующие поля:
Запросим файл в формате TSV, чтобы определить мощность трех столбцов-кандидатов:
Query
Response
ORDER BY принимает вид:
В таблице ниже будут использоваться более понятные имена столбцов, а приведённые выше имена будут сопоставлены с
ORDER BY структура таблицы будет такой:
system, а точнее system.table, содержит всю информацию о только что созданной таблице. Этот запрос показывает ORDER BY (ключ сортировки) и PRIMARY KEY:
clickhouse-local, а для их загрузки — clickhouse-client.
Набор данных обновляется один или несколько раз в год, поэтому ваши результаты могут не совпадать с приведёнными в этом документе.
Query
Response
Query
Response
Query
Response
Query
Response