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

Импорт и экспорт настраиваемых текстовых данных с использованием шаблонов и регулярных выражений в ClickHouse

Мы часто сталкиваемся с данными в нестандартных текстовых форматах. Это может быть нестандартный формат, недействительный JSON или поврежденный CSV. Использование стандартных парсеров, таких как CSV или JSON, не всегда подходит. Но ClickHouse предлагает мощные форматы шаблонов и регулярных выражений.

Импорт на основе шаблона​

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

Мы можем использовать формат Template для импорта этих данных. Нам нужно определить строку шаблона с заполнителями значений для каждой строки входных данных:

Давайте создадим таблицу для импорта наших данных:

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

Мы определяем имя колонки и правило экранирования в формате ${name:escaping}. Здесь доступны несколько вариантов, таких как CSV, JSON, Escaped или Quoted, которые реализуют соответствующие правила экранирования.

Теперь мы можем использовать данный файл в качестве аргумента для параметра настройки format_template_row во время импорта данных (обратите внимание, что файлы шаблона и данных не должны содержать дополнительный символ \n в конце файла):

И мы можем убедиться, что наши данные были загружены в таблицу:

Пропуск пробелов​

Рекомендуется использовать TemplateIgnoreSpaces, который позволяет пропускать пробелы между разделителями в шаблоне:

Экспорт данных с использованием шаблонов​

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

Шаблон результата, который определяет макет для всего набора результатов:

Здесь rows_read и time — это системные метрики, доступные для каждого запроса. В то время как data обозначает сгенерированные строки (${data} всегда должна быть первым заполнителем в этом файле), основываясь на шаблоне, определенном в файле шаблона строк:

Теперь давайте использовать эти шаблоны, чтобы экспортировать следующий запрос:

Экспорт в HTML файлы​

Результаты на основе шаблонов также могут быть экспортированы в файлы с использованием предложения INTO OUTFILE. Давайте сгенерируем HTML файлы на основе данных результатов и строк:

Экспорт в XML​

Шаблонный формат может использоваться для генерации всех вообразимых текстовых форматов файлов, включая XML. Просто вставьте соответствующий шаблон и выполните экспорт.

Также рассмотрите возможность использования формата XML, чтобы получить стандартные XML результаты, включая метаданные:

Импорт данных на основе регулярных выражений​

Формат Regexp учитывает более сложные случаи, когда входные данные нужно парсить более сложным образом. Давайте обработаем наш пример с error.log, но на этот раз захватим имя файла и протокол, чтобы сохранить их в отдельных колонках. Сначала давайте подготовим новую таблицу для этого:

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

ClickHouse вставит данные из каждой группы захвата в соответствующую колонку на основании ее порядка. Давайте проверим данные:

По умолчанию ClickHouse выдаст ошибку в случае несоответствующих строк. Если вы хотите пропустить несоответствующие строки, включите это с помощью параметра format_regexp_skip_unmatched:

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

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

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