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

Работа с данными 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, 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 содержит 1k строк, но 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 секунда, чтобы сохранить 36m строк в 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.