> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-fix-nav-issues.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. > Загрузка данных в формате Tab Separated Value и запросы к ним за 5 шагов # Данные о жалобах NYPD Файлы в формате Tab Separated Value, или TSV, широко распространены и могут содержать имена полей в первой строке. ClickHouse умеет загружать TSV-файлы, а также выполнять запросы к ним без загрузки самих файлов. В этом руководстве рассматриваются оба варианта. Если вам нужно выполнять запросы к CSV-файлам или загружать их, подойдут те же методы — просто замените `TSV` на `CSV` в аргументах формата. В ходе работы с этим руководством вы: * **Исследуете**: выполните запросы к структуре и содержимому TSV-файла. * **Определите целевую схему ClickHouse**: выберите подходящие типы данных и сопоставьте с ними существующие данные. * **Создадите таблицу ClickHouse**. * **Предварительно обработаете и передадите потоково** данные в ClickHouse. * **Выполните несколько запросов** к ClickHouse. Набор данных, используемый в этом руководстве, предоставлен командой NYC Open Data и содержит данные обо «всех подтверждённых преступлениях категорий felony, misdemeanor и violation, о которых сообщалось в New York City Police Department (NYPD)». На момент написания файл данных имеет размер 166 МБ, но регулярно обновляется. **Источник**: [data.cityofnewyork.us](https://data.cityofnewyork.us/Public-Safety/NYPD-Complaint-Data-Current-Year-To-Date-/5uac-w243) **Условия использования**: [https://www1.nyc.gov/home/terms-of-use.page](https://www1.nyc.gov/home/terms-of-use.page)
## Необходимые условия
* Скачайте набор данных: перейдите на страницу [NYPD Complaint Data Current (Year To Date)](https://data.cityofnewyork.us/Public-Safety/NYPD-Complaint-Data-Current-Year-To-Date-/5uac-w243), нажмите кнопку Export и выберите **TSV for Excel**. * Установите [ClickHouse server and client](/ru/get-started/setup/install)
### Примечание о командах, описанных в этом руководстве
В этом руководстве используются два типа команд: * Некоторые команды выполняют запросы к TSV-файлам; их запускают в командной строке. * Остальные команды выполняют запросы к ClickHouse; их запускают в `clickhouse-client` или в интерфейсе Play. Примеры в этом руководстве предполагают, что вы сохранили файл в формате TSV по пути `${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv`; при необходимости скорректируйте команды.
## Ознакомьтесь с файлом в формате TSV
Прежде чем приступить к работе с базой данных ClickHouse, ознакомьтесь с данными.
### Посмотрите на поля в исходном файле в формате TSV
Это пример команды для выполнения запроса к файлу в формате TSV, но пока не запускайте её. ```sh title="Query" theme={null} clickhouse-local --query \ "describe file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')" ``` Пример ответа ```response theme={null} CMPLNT_NUM Nullable(Float64) ADDR_PCT_CD Nullable(Float64) BORO_NM Nullable(String) CMPLNT_FR_DT Nullable(String) CMPLNT_FR_TM Nullable(String) ``` В большинстве случаев приведённая выше команда покажет, какие поля во входных данных являются числовыми, какие — строковыми, а какие — кортежами. Но так бывает не всегда. Поскольку ClickHouse обычно работает с датасетами, содержащими миллиарды записей, по умолчанию для [определения схемы](/ru/guides/clickhouse/data-formats/json/inference) анализируется лишь ограниченное число строк (100), чтобы не выполнять парсинг миллиардов строк. Приведённый ниже результат может отличаться от того, что увидите вы, так как датасет обновляется несколько раз в год. Если заглянуть в словарь данных, можно увидеть, что CMPLNT\_NUM указан как текстовое, а не числовое поле. Переопределив значение по умолчанию — 100 строк для определения схемы — с помощью настройки `SETTINGS input_format_max_rows_to_read_for_schema_inference=2000`, можно получить более точное представление о содержимом. Примечание: начиная с версии 22.5 значением по умолчанию теперь являются 25 000 строк для определения схемы, поэтому изменяйте эту настройку только если используете более старую версию или если вам нужно, чтобы в выборку попало более 25 000 строк. Выполните эту команду в командной строке. Для запроса данных в загруженном файле в формате TSV вы будете использовать `clickhouse-local`. ```sh title="Query" theme={null} clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \ --query \ "describe file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames')" ``` ```response title="Response" theme={null} CMPLNT_NUM Nullable(String) ADDR_PCT_CD Nullable(Float64) BORO_NM Nullable(String) CMPLNT_FR_DT Nullable(String) CMPLNT_FR_TM Nullable(String) CMPLNT_TO_DT Nullable(String) CMPLNT_TO_TM Nullable(String) CRM_ATPT_CPTD_CD Nullable(String) HADEVELOPT Nullable(String) HOUSING_PSA Nullable(Float64) JURISDICTION_CODE Nullable(Float64) JURIS_DESC Nullable(String) KY_CD Nullable(Float64) LAW_CAT_CD Nullable(String) LOC_OF_OCCUR_DESC Nullable(String) OFNS_DESC Nullable(String) PARKS_NM Nullable(String) PATROL_BORO Nullable(String) PD_CD Nullable(Float64) PD_DESC Nullable(String) PREM_TYP_DESC Nullable(String) RPT_DT Nullable(String) STATION_NAME Nullable(String) SUSP_AGE_GROUP Nullable(String) SUSP_RACE Nullable(String) SUSP_SEX Nullable(String) TRANSIT_DISTRICT Nullable(Float64) VIC_AGE_GROUP Nullable(String) VIC_RACE Nullable(String) VIC_SEX Nullable(String) X_COORD_CD Nullable(Float64) Y_COORD_CD Nullable(Float64) Latitude Nullable(Float64) Longitude Nullable(Float64) Lat_Lon Tuple(Nullable(Float64), Nullable(Float64)) New Georeferenced Column Nullable(String) ``` На этом этапе следует проверить, что столбцы в файле в формате TSV соответствуют именам и типам, указанным в разделе **Столбцы в этом наборе данных** на [странице набора данных](https://data.cityofnewyork.us/Public-Safety/NYPD-Complaint-Data-Current-Year-To-Date-/5uac-w243). Типы данных здесь не слишком конкретны: для всех числовых полей задан `Nullable(Float64)`, а для всех остальных — `Nullable(String)`. При создании таблицы ClickHouse для хранения данных можно указать более подходящие и эффективные типы.
### Определите подходящую схему
Чтобы понять, какие типы следует использовать для полей, нужно знать, как выглядят данные. Например, поле `JURISDICTION_CODE` содержит числовое значение: следует ли использовать `UInt8`, `Enum` или подойдет `Float64`? ```sql title="Query" theme={null} clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \ --query \ "select JURISDICTION_CODE, count() FROM file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames') GROUP BY JURISDICTION_CODE ORDER BY JURISDICTION_CODE FORMAT PrettyCompact" ``` ```response title="Response" theme={null} ┌─JURISDICTION_CODE─┬─count()─┐ │ 0 │ 188875 │ │ 1 │ 4799 │ │ 2 │ 13833 │ │ 3 │ 656 │ │ 4 │ 51 │ │ 6 │ 5 │ │ 7 │ 2 │ │ 9 │ 13 │ │ 11 │ 14 │ │ 12 │ 5 │ │ 13 │ 2 │ │ 14 │ 70 │ │ 15 │ 20 │ │ 72 │ 159 │ │ 87 │ 9 │ │ 88 │ 75 │ │ 97 │ 405 │ └───────────────────┴─────────┘ ``` Ответ на запрос показывает, что `JURISDICTION_CODE` хорошо укладывается в `UInt8`. Аналогично посмотрите на некоторые поля типа `String` и проверьте, не лучше ли представить их как поля `DateTime` или [`LowCardinality(String)`](/ru/reference/data-types/lowcardinality). Например, поле `PARKS_NM` описывается как "Название парка, игровой площадки или зелёной зоны в Нью-Йорке, где произошло событие, если применимо (парки штата не включены)". Названия парков в Нью-Йорке могут быть хорошим кандидатом для `LowCardinality(String)`: ```sh title="Query" theme={null} clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \ --query \ "select count(distinct PARKS_NM) FROM file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames') FORMAT PrettyCompact" ``` ```response title="Response" theme={null} ┌─uniqExact(PARKS_NM)─┐ │ 319 │ └─────────────────────┘ ``` Обратите внимание на названия некоторых парков: ```sql title="Query" theme={null} clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \ --query \ "select distinct PARKS_NM FROM file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames') LIMIT 10 FORMAT PrettyCompact" ``` ```response title="Response" theme={null} ┌─PARKS_NM───────────────────┐ │ (null) │ │ ASSER LEVY PARK │ │ JAMES J WALKER PARK │ │ BELT PARKWAY/SHORE PARKWAY │ │ PROSPECT PARK │ │ MONTEFIORE SQUARE │ │ SUTTON PLACE PARK │ │ JOYCE KILMER PARK │ │ ALLEY ATHLETIC PLAYGROUND │ │ ASTORIA PARK │ └────────────────────────────┘ ``` Используемый на момент написания набор данных содержит лишь несколько сотен различных парков и игровых площадок в столбце `PARK_NM`. Это немного, если учитывать рекомендацию [LowCardinality](/ru/reference/data-types/lowcardinality#description) — не более 10 000 различных строковых значений в поле `LowCardinality(String)`.
### Поля DateTime
Судя по разделу **Columns in this Dataset** на [странице набора данных](https://data.cityofnewyork.us/Public-Safety/NYPD-Complaint-Data-Current-Year-To-Date-/5uac-w243), в нём есть поля даты и времени, указывающие начало и окончание зарегистрированного события. Просмотр минимальных и максимальных значений `CMPLNT_FR_DT` и `CMPLT_TO_DT` позволяет понять, всегда ли эти поля заполнены: ```sh title="Query" theme={null} clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \ --query \ "select min(CMPLNT_FR_DT), max(CMPLNT_FR_DT) FROM file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames') FORMAT PrettyCompact" ``` ```response title="Response" theme={null} ┌─min(CMPLNT_FR_DT)─┬─max(CMPLNT_FR_DT)─┐ │ 01/01/1973 │ 12/31/2021 │ └───────────────────┴───────────────────┘ ``` ```sh title="Query" theme={null} clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \ --query \ "select min(CMPLNT_TO_DT), max(CMPLNT_TO_DT) FROM file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames') FORMAT PrettyCompact" ``` ```response title="Response" theme={null} ┌─min(CMPLNT_TO_DT)─┬─max(CMPLNT_TO_DT)─┐ │ │ 12/31/2021 │ └───────────────────┴───────────────────┘ ``` ```sh title="Query" theme={null} clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \ --query \ "select min(CMPLNT_FR_TM), max(CMPLNT_FR_TM) FROM file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames') FORMAT PrettyCompact" ``` ```response title="Response" theme={null} ┌─min(CMPLNT_FR_TM)─┬─max(CMPLNT_FR_TM)─┐ │ 00:00:00 │ 23:59:00 │ └───────────────────┴───────────────────┘ ``` ```sh title="Query" theme={null} clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \ --query \ "select min(CMPLNT_TO_TM), max(CMPLNT_TO_TM) FROM file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames') FORMAT PrettyCompact" ``` ```response title="Response" theme={null} ┌─min(CMPLNT_TO_TM)─┬─max(CMPLNT_TO_TM)─┐ │ (null) │ 23:59:00 │ └───────────────────┴───────────────────┘ ```
## Составьте план
На основе приведённого выше анализа: * `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`. ```sh title="Query" theme={null} clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \ --query \ "select CMPLNT_FR_DT || ' ' || CMPLNT_FR_TM AS complaint_begin FROM file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames') LIMIT 10 FORMAT PrettyCompact" ``` ```response title="Response" theme={null} ┌─complaint_begin─────┐ │ 07/29/2010 00:01:00 │ │ 12/01/2011 12:00:00 │ │ 04/01/2017 15:00:00 │ │ 03/26/2018 17:20:00 │ │ 01/01/2019 00:00:00 │ │ 06/14/2019 00:00:00 │ │ 11/29/2021 20:00:00 │ │ 12/04/2021 00:35:00 │ │ 12/05/2021 12:50:00 │ │ 12/07/2021 20:30:00 │ └─────────────────────┘ ```
## Преобразование строки с датой и временем в тип DateTime64
Ранее в этом руководстве мы выяснили, что в файле в формате TSV есть даты до 1 января 1970 года, а это значит, что для них нужен 64-битный тип DateTime. Даты также нужно преобразовать из формата `MM/DD/YYYY` в формат `YYYY/MM/DD`. И то и другое можно сделать с помощью [`parseDateTime64BestEffort()`](/ru/reference/functions/regular-functions/type-conversion-functions#parseDateTime64BestEffort). ```sh title="Query" theme={null} clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \ --query \ "WITH (CMPLNT_FR_DT || ' ' || CMPLNT_FR_TM) AS CMPLNT_START, (CMPLNT_TO_DT || ' ' || CMPLNT_TO_TM) AS CMPLNT_END select parseDateTime64BestEffort(CMPLNT_START) AS complaint_begin, parseDateTime64BestEffortOrNull(CMPLNT_END) AS complaint_end FROM file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames') ORDER BY complaint_begin ASC LIMIT 25 FORMAT PrettyCompact" ``` Строки 2 и 3 выше содержат результат конкатенации из предыдущего шага, а строки 4 и 5 выше разбирают строки в `DateTime64`. Поскольку время окончания жалобы может отсутствовать, используется `parseDateTime64BestEffortOrNull`. ```response title="Response" theme={null} ┌─────────complaint_begin─┬───────────complaint_end─┐ │ 1925-01-01 10:00:00.000 │ 2021-02-12 09:30:00.000 │ │ 1925-01-01 11:37:00.000 │ 2022-01-16 11:49:00.000 │ │ 1925-01-01 15:00:00.000 │ 2021-12-31 00:00:00.000 │ │ 1925-01-01 15:00:00.000 │ 2022-02-02 22:00:00.000 │ │ 1925-01-01 19:00:00.000 │ 2022-04-14 05:00:00.000 │ │ 1955-09-01 19:55:00.000 │ 2022-08-01 00:45:00.000 │ │ 1972-03-17 11:40:00.000 │ 2022-03-17 11:43:00.000 │ │ 1972-05-23 22:00:00.000 │ 2022-05-24 09:00:00.000 │ │ 1972-05-30 23:37:00.000 │ 2022-05-30 23:50:00.000 │ │ 1972-07-04 02:17:00.000 │ ᴺᵁᴸᴸ │ │ 1973-01-01 00:00:00.000 │ ᴺᵁᴸᴸ │ │ 1975-01-01 00:00:00.000 │ ᴺᵁᴸᴸ │ │ 1976-11-05 00:01:00.000 │ 1988-10-05 23:59:00.000 │ │ 1977-01-01 00:00:00.000 │ 1977-01-01 23:59:00.000 │ │ 1977-12-20 00:01:00.000 │ ᴺᵁᴸᴸ │ │ 1981-01-01 00:01:00.000 │ ᴺᵁᴸᴸ │ │ 1981-08-14 00:00:00.000 │ 1987-08-13 23:59:00.000 │ │ 1983-01-07 00:00:00.000 │ 1990-01-06 00:00:00.000 │ │ 1984-01-01 00:01:00.000 │ 1984-12-31 23:59:00.000 │ │ 1985-01-01 12:00:00.000 │ 1987-12-31 15:00:00.000 │ │ 1985-01-11 09:00:00.000 │ 1985-12-31 12:00:00.000 │ │ 1986-03-16 00:05:00.000 │ 2022-03-16 00:45:00.000 │ │ 1987-01-07 00:00:00.000 │ 1987-01-09 00:00:00.000 │ │ 1988-04-03 18:30:00.000 │ 2022-08-03 09:45:00.000 │ │ 1988-07-29 12:00:00.000 │ 1990-07-27 22:00:00.000 │ └─────────────────────────┴─────────────────────────┘ ``` Показанные выше даты `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` создается по кортежу `PRIMARY KEY`, если он указан, иначе — по кортежу `ORDER BY` * Индекс `PRIMARY KEY` хранится в оперативной памяти Исходя из набора данных и вопросов, на которые можно будет ответить с его помощью, мы можем решить, что хотим анализировать типы зарегистрированных преступлений по времени в пяти боро Нью-Йорка. Тогда в `ORDER BY` можно включить следующие поля: | Столбец | Описание (из словаря данных) | | ---------- | --------------------------------------------------- | | OFNS\_DESC | Описание правонарушения, соответствующее коду ключа | | RPT\_DT | Дата, когда о событии сообщили в полицию | | BORO\_NM | Название боро, в котором произошел инцидент | Запросим файл в формате TSV, чтобы определить мощность трех столбцов-кандидатов: ```bash title="Query" theme={null} clickhouse-local --input_format_max_rows_to_read_for_schema_inference=2000 \ --query \ "select formatReadableQuantity(uniq(OFNS_DESC)) as cardinality_OFNS_DESC, formatReadableQuantity(uniq(RPT_DT)) as cardinality_RPT_DT, formatReadableQuantity(uniq(BORO_NM)) as cardinality_BORO_NM FROM file('${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv', 'TSVWithNames') FORMAT PrettyCompact" ``` ```response title="Response" theme={null} ┌─cardinality_OFNS_DESC─┬─cardinality_RPT_DT─┬─cardinality_BORO_NM─┐ │ 60.00 │ 306.00 │ 6.00 │ └───────────────────────┴────────────────────┴─────────────────────┘ ``` При сортировке по мощности выражение `ORDER BY` принимает вид: ```sql theme={null} ORDER BY ( BORO_NM, OFNS_DESC, RPT_DT ) ``` В таблице ниже будут использоваться более понятные имена столбцов, а приведённые выше имена будут сопоставлены с ```sql theme={null} ORDER BY ( borough, offense_description, date_reported ) ``` С учётом изменений в типах данных и кортежа `ORDER BY` структура таблицы будет такой: ```sql theme={null} CREATE TABLE NYPD_Complaint ( complaint_number String, precinct UInt8, borough LowCardinality(String), complaint_begin DateTime64(0,'America/New_York'), complaint_end DateTime64(0,'America/New_York'), was_crime_completed String, housing_authority String, housing_level_code UInt32, jurisdiction_code UInt8, jurisdiction LowCardinality(String), offense_code UInt8, offense_level LowCardinality(String), location_descriptor LowCardinality(String), offense_description LowCardinality(String), park_name LowCardinality(String), patrol_borough LowCardinality(String), PD_CD UInt16, PD_DESC String, location_type LowCardinality(String), date_reported Date, transit_station LowCardinality(String), suspect_age_group LowCardinality(String), suspect_race LowCardinality(String), suspect_sex LowCardinality(String), transit_district UInt8, victim_age_group LowCardinality(String), victim_race LowCardinality(String), victim_sex LowCardinality(String), NY_x_coordinate UInt32, NY_y_coordinate UInt32, Latitude Float64, Longitude Float64 ) ENGINE = MergeTree ORDER BY ( borough, offense_description, date_reported ) ```
### Поиск первичного ключа таблицы
База данных ClickHouse `system`, а точнее `system.table`, содержит всю информацию о только что созданной таблице. Этот запрос показывает `ORDER BY` (ключ сортировки) и `PRIMARY KEY`: ```sql theme={null} SELECT partition_key, sorting_key, primary_key, table FROM system.tables WHERE table = 'NYPD_Complaint' FORMAT Vertical ``` Ответ ```response theme={null} Query id: 6a5b10bf-9333-4090-b36e-c7f08b1d9e01 Row 1: ────── partition_key: sorting_key: borough, offense_description, date_reported primary_key: borough, offense_description, date_reported table: NYPD_Complaint 1 row in set. Elapsed: 0.001 sec. ```
## Предварительная обработка и импорт данных
Для предварительной обработки данных мы будем использовать инструмент `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`. ```sql theme={null} cat ${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv \ | clickhouse-local --table='input' --input-format='TSVWithNames' \ --input_format_max_rows_to_read_for_schema_inference=2000 \ --query " WITH (CMPLNT_FR_DT || ' ' || CMPLNT_FR_TM) AS CMPLNT_START, (CMPLNT_TO_DT || ' ' || CMPLNT_TO_TM) AS CMPLNT_END SELECT CMPLNT_NUM AS complaint_number, ADDR_PCT_CD AS precinct, BORO_NM AS borough, parseDateTime64BestEffort(CMPLNT_START) AS complaint_begin, parseDateTime64BestEffortOrNull(CMPLNT_END) AS complaint_end, CRM_ATPT_CPTD_CD AS was_crime_completed, HADEVELOPT AS housing_authority_development, HOUSING_PSA AS housing_level_code, JURISDICTION_CODE AS jurisdiction_code, JURIS_DESC AS jurisdiction, KY_CD AS offense_code, LAW_CAT_CD AS offense_level, LOC_OF_OCCUR_DESC AS location_descriptor, OFNS_DESC AS offense_description, PARKS_NM AS park_name, PATROL_BORO AS patrol_borough, PD_CD, PD_DESC, PREM_TYP_DESC AS location_type, toDate(parseDateTimeBestEffort(RPT_DT)) AS date_reported, STATION_NAME AS transit_station, SUSP_AGE_GROUP AS suspect_age_group, SUSP_RACE AS suspect_race, SUSP_SEX AS suspect_sex, TRANSIT_DISTRICT AS transit_district, VIC_AGE_GROUP AS victim_age_group, VIC_RACE AS victim_race, VIC_SEX AS victim_sex, X_COORD_CD AS NY_x_coordinate, Y_COORD_CD AS NY_y_coordinate, Latitude, Longitude FROM input" \ | clickhouse-client --query='INSERT INTO NYPD_Complaint FORMAT TSV' ```
## Проверьте данные
Набор данных обновляется один или несколько раз в год, поэтому ваши результаты могут не совпадать с приведёнными в этом документе. ```sql title="Query" theme={null} SELECT count() FROM NYPD_Complaint ``` ```text title="Response" theme={null} ┌─count()─┐ │ 208993 │ └─────────┘ 1 row in set. Elapsed: 0.001 sec. ``` Объём данных в ClickHouse составляет всего 12 % от размера исходного файла в формате TSV; сравните размер исходного файла в формате TSV с размером таблицы: ```sql title="Query" theme={null} SELECT formatReadableSize(total_bytes) FROM system.tables WHERE name = 'NYPD_Complaint' ``` ```text title="Response" theme={null} ┌─formatReadableSize(total_bytes)─┐ │ 8.63 MiB │ └─────────────────────────────────┘ ```
## Выполните несколько запросов
### Запрос 1. Сравните количество жалоб по месяцам
```sql title="Query" theme={null} SELECT dateName('month', date_reported) AS month, count() AS complaints, bar(complaints, 0, 50000, 80) FROM NYPD_Complaint GROUP BY month ORDER BY complaints DESC ``` ```response title="Response" theme={null} Query id: 7fbd4244-b32a-4acf-b1f3-c3aa198e74d9 ┌─month─────┬─complaints─┬─bar(count(), 0, 50000, 80)───────────────────────────────┐ │ March │ 34536 │ ███████████████████████████████████████████████████████▎ │ │ May │ 34250 │ ██████████████████████████████████████████████████████▋ │ │ April │ 32541 │ ████████████████████████████████████████████████████ │ │ January │ 30806 │ █████████████████████████████████████████████████▎ │ │ February │ 28118 │ ████████████████████████████████████████████▊ │ │ November │ 7474 │ ███████████▊ │ │ December │ 7223 │ ███████████▌ │ │ October │ 7070 │ ███████████▎ │ │ September │ 6910 │ ███████████ │ │ August │ 6801 │ ██████████▊ │ │ June │ 6779 │ ██████████▋ │ │ July │ 6485 │ ██████████▍ │ └───────────┴────────────┴──────────────────────────────────────────────────────────┘ 12 rows in set. Elapsed: 0.006 sec. Processed 208.99 thousand rows, 417.99 KB (37.48 million rows/s., 74.96 MB/s.) ```
### Запрос 2. Сравните общее число жалоб по боро
```sql title="Query" theme={null} SELECT borough, count() AS complaints, bar(complaints, 0, 125000, 60) FROM NYPD_Complaint GROUP BY borough ORDER BY complaints DESC ``` ```response title="Response" theme={null} Query id: 8cdcdfd4-908f-4be0-99e3-265722a2ab8d ┌─borough───────┬─complaints─┬─bar(count(), 0, 125000, 60)──┐ │ BROOKLYN │ 57947 │ ███████████████████████████▋ │ │ MANHATTAN │ 53025 │ █████████████████████████▍ │ │ QUEENS │ 44875 │ █████████████████████▌ │ │ BRONX │ 44260 │ █████████████████████▏ │ │ STATEN ISLAND │ 8503 │ ████ │ │ (null) │ 383 │ ▏ │ └───────────────┴────────────┴──────────────────────────────┘ 6 rows in set. Elapsed: 0.008 sec. Processed 208.99 thousand rows, 209.43 KB (27.14 million rows/s., 27.20 MB/s.) ```
## Следующие шаги
[Практическое введение в разреженные первичные индексы в ClickHouse](/ru/guides/clickhouse/data-modelling/sparse-primary-indexes) рассказывает о различиях между индексированием в ClickHouse и в традиционных реляционных базах данных, о том, как ClickHouse строит и использует разреженный первичный индекс, а также о лучших практиках индексирования.