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

Работа с 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 файла:

В файле 1k строк, но 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.