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

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

ClickHouse поддерживает импорт данных из файлов 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:

Файл file содержит 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 и template для работы с еще более сложными случаями.

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

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

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

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

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

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

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