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

Импорт и экспорт пользовательских текстовых данных с использованием шаблонов и регулярных выражений в 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.