使用模板和正则表达式在 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
子句导出到文件。让我们根据给定的 结果集 和 行 格式生成 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 服务器即可处理本地/远程文件。