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

Работа с данными CSV и TSV в ClickHouse

ClickHouse поддерживает импорт данных из файлов CSV и экспорта в CSV. Поскольку файлы CSV могут иметь различные специфики формата, включая заголовки, пользовательские разделители и символы экранирования, ClickHouse предоставляет форматы и настройки для эффективного решения каждой задачи.

Импорт данных из CSV файла​

Перед импортом данных давайте создадим таблицу с соответствующей структурой:

CREATE TABLE sometable
(
    `path` String,
    `month` Date,
    `hits` UInt32
)
ENGINE = MergeTree
ORDER BY tuple(month, path)

Чтобы импортировать данные из CSV файла в таблицу sometable, мы можем передать наш файл напрямую в clickhouse-client:

clickhouse-client -q "INSERT INTO sometable FORMAT CSV" < data_small.csv

Обратите внимание, что мы используем FORMAT CSV, чтобы сообщить ClickHouse, что мы загружаем данные в формате CSV. В качестве альтернативы, мы можем загрузить данные из локального файла, используя оператор FROM INFILE:

INSERT INTO sometable
FROM INFILE 'data_small.csv'
FORMAT CSV

Здесь мы используем оператор FORMAT CSV, чтобы ClickHouse понимал формат файла. Мы также можем загружать данные непосредственно из URL, используя функцию url(), или из файлов S3, используя функцию s3().

подсказка

Мы можем пропустить явную настройку формата для file() и INFILE/OUTFILE. В этом случае ClickHouse автоматически определит формат на основе расширения файла.

CSV файлы с заголовками​

Предположим, наш CSV файл имеет заголовки:

head data-small-headers.csv

"path","month","hits"
"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34

Чтобы импортировать данные из этого файла, мы можем использовать формат CSVWithNames:

clickhouse-client -q "INSERT INTO sometable FORMAT CSVWithNames" < data_small_headers.csv

В этом случае ClickHouse пропускает первую строку при импорте данных из файла.

подсказка

Начиная с версии 23.1 version ClickHouse будет автоматически определять заголовки в CSV файлах, когда используется тип CSV, так что нет необходимости использовать CSVWithNames или CSVWithNamesAndTypes.

CSV файлы с пользовательскими разделителями​

В случае, если CSV файл использует разделитель, отличный от запятой, мы можем использовать опцию format_csv_delimiter для установки соответствующего символа:

SET format_csv_delimiter = ';'

Теперь, когда мы импортируем из CSV файла, символ ; будет использоваться в качестве разделителя вместо запятой.

Пропуск строк в CSV файле​

Иногда мы можем пропустить определенное количество строк при импорте данных из CSV файла. Это можно сделать, используя опцию input_format_csv_skip_first_lines:

SET input_format_csv_skip_first_lines = 10

В этом случае мы пропустим первые десять строк из CSV файла:

SELECT count(*) FROM file('data-small.csv', CSV)

┌─count()─┐
│     990 │
└─────────┘

В file 1000 строк, но ClickHouse загрузил только 990, так как мы попросили пропустить первые 10.

подсказка

При использовании функции file(), с ClickHouse Cloud вам нужно будет выполнять команды в clickhouse client на машине, где находится файл. Другой вариант - использовать clickhouse-local для работы с файлами локально.

Обработка NULL значений в CSV файлах​

NULL значения могут быть закодированы по-разному в зависимости от приложения, которое сгенерировало файл. По умолчанию ClickHouse использует \N как значение NULL в CSV. Но мы можем изменить это, используя опцию format_csv_null_representation.

Предположим, у нас есть следующий CSV файл:

> cat nulls.csv
Donald,90
Joe,Nothing
Nothing,70

Если мы загрузим данные из этого файла, ClickHouse будет считать Nothing строкой (что является правильным):

SELECT * FROM file('nulls.csv')

┌─c1──────┬─c2──────┐
│ Donald  │ 90      │
│ Joe     │ Nothing │
│ Nothing │ 70      │
└─────────┴─────────┘

Если мы хотим, чтобы ClickHouse считал Nothing за NULL, мы можем определить это, используя следующую опцию:

SET format_csv_null_representation = 'Nothing'

Теперь у нас есть NULL, где мы ожидаем его видеть:

SELECT * FROM file('nulls.csv')

┌─c1─────┬─c2───┐
│ Donald │ 90   │
│ Joe    │ ᴺᵁᴸᴸ │
│ ᴺᵁᴸᴸ   │ 70   │
└────────┴──────┘

TSV (Файлы с разделителями табуляции)​

Формат данных с разделителями табуляции широко используется в качестве формата обмена данными. Чтобы загрузить данные из TSV файла в ClickHouse, используется формат TabSeparated:

clickhouse-client -q "INSERT INTO sometable FORMAT TabSeparated" < data_small.tsv

Существует также формат TabSeparatedWithNames, который позволяет работать с TSV файлами, имеющими заголовки. И, как для CSV, мы можем пропустить первые X строк, используя опцию input_format_tsv_skip_first_lines.

Сырой TSV​

Иногда TSV файлы сохраняются без экранирования табуляций и переносов строк. Мы должны использовать TabSeparatedRaw, чтобы обрабатывать такие файлы.

Экспорт в CSV​

Любой формат из наших предыдущих примеров также может использоваться для экспорта данных. Чтобы экспортировать данные из таблицы (или запроса) в формате CSV, мы используем тот же оператор FORMAT:

SELECT *
FROM sometable
LIMIT 5
FORMAT CSV

"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34
"1971-72_Utah_Stars_season","2016-10-01",1
"2015_UEFA_European_Under-21_Championship_qualification_Group_8","2015-12-01",73
"2016_Greater_Western_Sydney_Giants_season","2017-05-01",86

Чтобы добавить заголовок в CSV файл, мы используем формат CSVWithNames:

SELECT *
FROM sometable
LIMIT 5
FORMAT CSVWithNames

"path","month","hits"
"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34
"1971-72_Utah_Stars_season","2016-10-01",1
"2015_UEFA_European_Under-21_Championship_qualification_Group_8","2015-12-01",73
"2016_Greater_Western_Sydney_Giants_season","2017-05-01",86

Сохранение экспортированных данных в CSV файл​

Чтобы сохранить экспортированные данные в файл, мы можем использовать оператор INTO...OUTFILE:

SELECT *
FROM sometable
INTO OUTFILE 'out.csv'
FORMAT CSVWithNames

36838935 rows in set. Elapsed: 1.304 sec. Processed 36.84 million rows, 1.42 GB (28.24 million rows/s., 1.09 GB/s.)

Обратите внимание, что ClickHouse понадобилось ~1 секунда, чтобы сохранить 36 миллионов строк в CSV файл.

Экспорт CSV с пользовательскими разделителями​

Если мы хотим использовать разделители, отличные от запятой, мы можем использовать опцию настройки format_csv_delimiter:

SET format_csv_delimiter = '|'

Теперь ClickHouse будет использовать | в качестве разделителя для формата CSV:

SELECT *
FROM sometable
LIMIT 5
FORMAT CSV

"Akiba_Hebrew_Academy"|"2017-08-01"|241
"Aegithina_tiphia"|"2018-02-01"|34
"1971-72_Utah_Stars_season"|"2016-10-01"|1
"2015_UEFA_European_Under-21_Championship_qualification_Group_8"|"2015-12-01"|73
"2016_Greater_Western_Sydney_Giants_season"|"2017-05-01"|86

Экспорт CSV для Windows​

Если мы хотим, чтобы CSV файл хорошо работал в среде Windows, нам следует учесть возможность включения опции output_format_csv_crlf_end_of_line. Это будет использовать \r\n в качестве переносов строк вместо \n:

SET output_format_csv_crlf_end_of_line = 1;

Вывод схемы для CSV файлов​

Мы можем работать с неизвестными CSV файлами во многих случаях, поэтому нам нужно исследовать, какие типы использовать для столбцов. ClickHouse, по умолчанию, попытается угадать форматы данных на основе его анализа данного CSV файла. Это называется "Вывод схемы". Обнаруженные типы данных можно изучить с помощью оператора DESCRIBE в паре с функцией file():

DESCRIBE file('data-small.csv', CSV)

┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(String) │              │                    │         │                  │                │
│ c2   │ Nullable(Date)   │              │                    │         │                  │                │
│ c3   │ Nullable(Int64)  │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Здесь ClickHouse смог эффективно угадать типы столбцов для нашего CSV файла. Если мы не хотим, чтобы ClickHouse делал предположения, мы можем отключить это с помощью следующей опции:

SET input_format_csv_use_best_effort_in_schema_inference = 0

Все типы столбцов будут рассматриваться как String в этом случае.

Экспорт и импорт CSV с явными типами столбцов​

ClickHouse также позволяет явно задавать типы столбцов при экспорте данных, используя формат CSVWithNamesAndTypes (и другие форматы из семейства *WithNames):

SELECT *
FROM sometable
LIMIT 5
FORMAT CSVWithNamesAndTypes

"path","month","hits"
"String","Date","UInt32"
"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34
"1971-72_Utah_Stars_season","2016-10-01",1
"2015_UEFA_European_Under-21_Championship_qualification_Group_8","2015-12-01",73
"2016_Greater_Western_Sydney_Giants_season","2017-05-01",86

Этот формат будет включать две строки заголовков - одну с названиями столбцов и другую с типами столбцов. Это позволит ClickHouse (и другим приложениям) определить типы столбцов при загрузке данных из таких файлов:

DESCRIBE file('data_csv_types.csv', CSVWithNamesAndTypes)

┌─name──┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ path  │ String │              │                    │         │                  │                │
│ month │ Date   │              │                    │         │                  │                │
│ hits  │ UInt32 │              │                    │         │                  │                │
└───────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Теперь ClickHouse определяет типы столбцов на основе (второй) строки заголовков, вместо того чтобы угадывать.

Пользовательские разделители, сепараторы и правила экранирования​

В сложных случаях текстовые данные могут быть отформатированы в очень пользовательском формате, но по-прежнему иметь структуру. ClickHouse имеет специальный формат CustomSeparated для таких случаев, который позволяет задать пользовательские правила экранирования, разделители, сепараторы строк, а также символы начала и конца.

Предположим, у нас есть следующие данные в файле:

row('Akiba_Hebrew_Academy';'2017-08-01';241),row('Aegithina_tiphia';'2018-02-01';34),...

Мы видим, что отдельные строки заключены в row(), строки разделены ,, а отдельные значения разделены ;. В этом случае мы можем использовать следующие настройки для чтения данных из этого файла:

SET format_custom_row_before_delimiter = 'row(';
SET format_custom_row_after_delimiter = ')';
SET format_custom_field_delimiter = ';';
SET format_custom_row_between_delimiter = ',';
SET format_custom_escaping_rule = 'Quoted';

Теперь мы можем загрузить данные из нашего пользовательски отформатированного файла:

SELECT *
FROM file('data_small_custom.txt', CustomSeparated)
LIMIT 3

┌─c1────────────────────────┬─────────c2─┬──c3─┐
│ Akiba_Hebrew_Academy      │ 2017-08-01 │ 241 │
│ Aegithina_tiphia          │ 2018-02-01 │  34 │
│ 1971-72_Utah_Stars_season │ 2016-10-01 │   1 │
└───────────────────────────┴────────────┴─────┘

Мы также можем использовать CustomSeparatedWithNames, чтобы заголовки были экспортированы и импортированы правильно. Исследуйте форматы regex и template для решения еще более сложных случаев.

Работа с большими CSV файлами​

CSV файлы могут быть большими, и ClickHouse эффективно работает с файлами любого размера. Обычно большие файлы приходят сжатые, и ClickHouse покрывает это без необходимости разжаловать перед обработкой. Мы можем использовать оператор COMPRESSION при вставке:

INSERT INTO sometable
FROM INFILE 'data_csv.csv.gz'
COMPRESSION 'gzip' FORMAT CSV

Если оператор COMPRESSION опущен, ClickHouse все равно попытается угадать сжатие файла на основе его расширения. Тот же подход может использоваться для экспорта файлов напрямую в сжатые форматы:

SELECT *
FROM for_csv
INTO OUTFILE 'data_csv.csv.gz'
COMPRESSION 'gzip' FORMAT CSV

Это создаст сжатый файл data_csv.csv.gz.

Другие форматы​

ClickHouse вводит поддержку многих форматов, как текстовых, так и бинарных, чтобы охватить различные сценарии и платформы. Изучите больше форматов и способы работы с ними в следующих статьях:

Также проверьте clickhouse-local - портативное полнофункциональное средство для работы с локальными/удаленными файлами без необходимости запуска сервера ClickHouse.