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

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

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

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

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

Мы можем использовать формат 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.