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

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

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

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

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

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

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

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

подсказка

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

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

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

Для импорта данных из этого файла мы можем использовать формат CSVWithNames:

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

подсказка

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

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

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

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

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

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

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

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

подсказка

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

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

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

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

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

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

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

TSV (табулированные) файлы​

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

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

Чистый TSV​

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

Экспорт в CSV​

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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