使用模板和正则表达式在 ClickHouse 中导入和导出自定义文本数据

我们经常需要处理自定义文本格式的数据。这可能是非标准格式、无效的 JSON 或损坏的 CSV。使用像 CSV 或 JSON 这样的标准解析器在所有情况下都不奏效。但 ClickHouse 提供了强大的模板和正则表达式格式来解决这个问题。

基于模板的导入

假设我们想从以下日志文件中导入数据：

我们可以使用模板格式导入这些数据。我们需要定义一个模板字符串，其中包含每行输入数据的值占位符：

让我们创建一个表来导入我们的数据：

要使用给定模板导入数据，我们必须将模板字符串保存在一个文件中（在我们的例子中为 row.template）：

我们在 ${name:escaping} 格式中定义了列的名称和转义规则。此处可以使用多种选项，如 CSV、JSON、Escaped 或 Quoted，它们实现了相应的转义规则。

现在我们可以在导入数据时将给定文件作为 format_template_row 设置选项的参数（注意，模板和数据文件不应在文件末尾有额外的 \n 符号）：

我们可以确认我们的数据已加载到表中：

可以考虑使用 TemplateIgnoreSpaces 来跳过模板中定界符之间的空格：

我们还可以使用模板将数据导出为任何文本格式。在这种情况下，我们需要创建两个文件：

结果集模板，定义整个结果集的布局：

这里，rows_read 和 time 是每个请求可用的系统指标。而 data 代表生成的行（${data} 应该始终是该文件中的第一个占位符），基于在 行模板文件 中定义的模板：

现在让我们使用这些模板导出以下查询：

基于模板的结果也可以使用 INTO OUTFILE 子句导出到文件。让我们基于给定的结果集和行格式生成 HTML 文件：

模板格式可以用来生成所有想象中的文本格式文件，包括 XML。只需放入相关模板并进行导出。

此外，还可以考虑使用 XML 格式以获取包括元数据在内的标准 XML 结果：

Regexp 格式处理输入数据需要以更复杂的方式进行解析的更复杂情况。让我们解析我们的 error.log 示例文件，但这次捕获文件名和协议，将其保存在单独的列中。首先，让我们为此准备一个新的表：

现在，我们可以基于正则表达式导入数据：

ClickHouse 将根据每个捕获组的顺序将数据插入到相应的列中。让我们检查数据：

默认情况下，如果存在不匹配的行，ClickHouse 将会引发错误。如果您想跳过不匹配的行，请使用 format_regexp_skip_unmatched 选项启用它：

ClickHouse 引入了对多种格式的支持，包括文本和二进制，以覆盖各种场景和平台。您可以在以下文章中探索更多格式及其工作方式：

另外，请查看 clickhouse-local - 一个可移植的功能齐全的工具，可用于处理本地/远程文件，无需 ClickHouse 服务器。