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

Работа с данными 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 version 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.