跳到主要内容
跳到主要内容
编辑此页

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

我们经常需要处理自定义文本格式的数据。这可能是非标准格式、无效的 JSON 或损坏的 CSV。在所有这些情况下，使用 CSV 或 JSON 等标准解析器都无法工作。但 ClickHouse 通过强大的模板和正则表达式格式为我们提供了解决方案。

基于模板导入​

假设我们要从以下日志文件导入数据

head error.log
2023/01/15 14:51:17 [error]  client: 7.2.8.1, server: example.com "GET /apple-touch-icon-120x120.png HTTP/1.1"
2023/01/16 06:02:09 [error]  client: 8.4.2.7, server: example.com "GET /apple-touch-icon-120x120.png HTTP/1.1"
2023/01/15 13:46:13 [error]  client: 6.9.3.7, server: example.com "GET /apple-touch-icon.png HTTP/1.1"
2023/01/16 05:34:55 [error]  client: 9.9.7.6, server: example.com "GET /h5/static/cert/icon_yanzhengma.png HTTP/1.1"

我们可以使用模板格式导入此数据。我们必须为输入数据的每一行定义带有值占位符的模板字符串

<time> [error] client: <ip>, server: <host> "<request>"

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

CREATE TABLE error_log
(
    `time` DateTime,
    `ip` String,
    `host` String,
    `request` String
)
ENGINE = MergeTree
ORDER BY (host, request, time)

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

${time:Escaped} [error]  client: ${ip:CSV}, server: ${host:CSV} ${request:JSON}

我们以 ${name:escaping} 格式定义列名和转义规则。这里有多个选项可用，例如 CSV、JSON、Escaped 或 Quoted，它们实现了各自的转义规则。

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

INSERT INTO error_log FROM INFILE 'error.log'
SETTINGS format_template_row = 'row.template'
FORMAT Template

我们可以确保数据已加载到表中

SELECT
    request,
    count(*)
FROM error_log
GROUP BY request
┌─request──────────────────────────────────────────┬─count()─┐
│ GET /img/close.png HTTP/1.1                      │     176 │
│ GET /h5/static/cert/icon_yanzhengma.png HTTP/1.1 │     172 │
│ GET /phone/images/icon_01.png HTTP/1.1           │     139 │
│ GET /apple-touch-icon-precomposed.png HTTP/1.1   │     161 │
│ GET /apple-touch-icon.png HTTP/1.1               │     162 │
│ GET /apple-touch-icon-120x120.png HTTP/1.1       │     190 │
└──────────────────────────────────────────────────┴─────────┘

跳过空格​

考虑使用 TemplateIgnoreSpaces，它允许跳过模板中分隔符之间的空格

Template:               -->  "p1: ${p1:CSV}, p2: ${p2:CSV}"
TemplateIgnoreSpaces    -->  "p1:${p1:CSV}, p2:${p2:CSV}"

使用模板导出数据​

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

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

== Top 10 IPs ==
${data}
--- ${rows_read:XML} rows read in ${time:XML} ---

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

${ip:Escaped} generated ${total:Escaped} requests

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

SELECT
    ip,
    count() AS total
FROM error_log GROUP BY ip ORDER BY total DESC LIMIT 10
FORMAT Template SETTINGS format_template_resultset = 'output.results',
                         format_template_row = 'output.rows';

== Top 10 IPs ==

9.8.4.6 generated 3 requests
9.5.1.1 generated 3 requests
2.4.8.9 generated 3 requests
4.8.8.2 generated 3 requests
4.5.4.4 generated 3 requests
3.3.6.4 generated 2 requests
8.9.5.9 generated 2 requests
2.5.1.8 generated 2 requests
6.8.3.6 generated 2 requests
6.6.3.5 generated 2 requests

--- 1000 rows read in 0.001380604 ---

导出到 HTML 文件​

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

SELECT
    ip,
    count() AS total
FROM error_log GROUP BY ip ORDER BY total DESC LIMIT 10
INTO OUTFILE 'out.html'
FORMAT Template
SETTINGS format_template_resultset = 'html.results',
         format_template_row = 'html.row'

导出到 XML​

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

还可以考虑使用 XML 格式来获取包含元数据的标准 XML 结果

SELECT *
FROM error_log
LIMIT 3
FORMAT XML
<?xml version='1.0' encoding='UTF-8' ?>
<result>
	<meta>
		<columns>
			<column>
				<name>time</name>
				<type>DateTime</type>
			</column>
			...
		</columns>
	</meta>
	<data>
		<row>
			<time>2023-01-15 13:00:01</time>
			<ip>3.5.9.2</ip>
			<host>example.com</host>
			<request>GET /apple-touch-icon-120x120.png HTTP/1.1</request>
		</row>
		...
	</data>
	<rows>3</rows>
	<rows_before_limit_at_least>1000</rows_before_limit_at_least>
	<statistics>
		<elapsed>0.000745001</elapsed>
		<rows_read>1000</rows_read>
		<bytes_read>88184</bytes_read>
	</statistics>
</result>

基于正则表达式导入数据​

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

CREATE TABLE error_log
(
    `time` DateTime,
    `ip` String,
    `host` String,
    `file` String,
    `protocol` String
)
ENGINE = MergeTree
ORDER BY (host, file, time)

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

INSERT INTO error_log FROM INFILE 'error.log'
SETTINGS
  format_regexp = '(.+?) \\[error\\]  client: (.+), server: (.+?) "GET .+?([^/]+\\.[^ ]+) (.+?)"'
FORMAT Regexp

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

SELECT * FROM error_log LIMIT 5
┌────────────────time─┬─ip──────┬─host────────┬─file─────────────────────────┬─protocol─┐
│ 2023-01-15 13:00:01 │ 3.5.9.2 │ example.com │ apple-touch-icon-120x120.png │ HTTP/1.1 │
│ 2023-01-15 13:01:40 │ 3.7.2.5 │ example.com │ apple-touch-icon-120x120.png │ HTTP/1.1 │
│ 2023-01-15 13:16:49 │ 9.2.9.2 │ example.com │ apple-touch-icon-120x120.png │ HTTP/1.1 │
│ 2023-01-15 13:21:38 │ 8.8.5.3 │ example.com │ apple-touch-icon-120x120.png │ HTTP/1.1 │
│ 2023-01-15 13:31:27 │ 9.5.8.4 │ example.com │ apple-touch-icon-120x120.png │ HTTP/1.1 │
└─────────────────────┴─────────┴─────────────┴──────────────────────────────┴──────────┘

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

SET format_regexp_skip_unmatched = 1;

其他格式​

ClickHouse 引入了对多种格式的支持，包括文本格式和二进制格式，以涵盖各种场景和平台。在以下文章中探索更多格式以及使用它们的方法

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