使用模板和正则表达式在 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} 应始终作为此文件中的第一个占位符），基于在 行模板文件 中定义的模板：

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

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

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

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

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

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

ClickHouse 将根据捕获组的顺序将数据插入到相关列中。让我们查看数据：

默认情况下，如果有未匹配的行，ClickHouse 会引发错误。如果你想跳过未匹配的行，可以使用 format_regexp_skip_unmatched 选项来启用它：

ClickHouse 支持多种格式，包括文本和二进制格式，以覆盖各种场景和平台。在以下文章中探索更多格式和使用方式：

还可以查看 clickhouse-local - 一个便携式全功能工具，用于处理本地/远程文件，无需 Clickhouse 服务器。