> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# 在 ClickHouse 中使用模板和 Regex 导入和导出自定义文本数据

> 介绍如何在 ClickHouse 中使用模板和 Regex 导入和导出自定义文本的页面

我们经常需要处理自定义文本格式的数据，比如非标准格式、无效的 JSON，或损坏的 CSV。对于这类情况，CSV 或 JSON 这类标准 parser 并不总是适用。不过，ClickHouse 提供了强大的 Template 和 Regex 格式来应对这些需求。

<div id="importing-based-on-a-template">
  ## 根据模板导入
</div>

假设我们要从以下[日志文件](https://clickhouse-docs-assets.s3.us-east-1.amazonaws.com/error.log)导入数据：

```bash theme={null}
head error.log
```

```response theme={null}
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"
```

我们可以使用 [Template](/docs/zh/reference/formats/Template/Template) 格式导入这些数据。我们需要定义一个模板字符串，为输入数据的每一行设置值占位符：

```response theme={null}
<time> [error] client: <ip>, server: <host> "<request>"
```

先创建一个表，用于导入数据：

```sql theme={null}
CREATE TABLE error_log
(
    `time` DateTime,
    `ip` String,
    `host` String,
    `request` String
)
ENGINE = MergeTree
ORDER BY (host, request, time)
```

要使用指定模板导入数据，我们需要先将模板字符串保存到文件中 (本例中为 [row.template](https://clickhouse-docs-assets.s3.us-east-1.amazonaws.com/row.template)) ：

```response theme={null}
${time:Escaped} [error]  client: ${ip:CSV}, server: ${host:CSV} ${request:JSON}
```

我们使用 `${name:escaping}` 格式来定义列名和转义规则。这里有多种可选项，如 CSV、JSON、Escaped 或 Quoted，它们分别实现了[对应的转义规则](/docs/zh/reference/formats/Template/Template)。

现在，在导入数据时，我们可以将给定文件作为 `format_template_row` Settings 选项的参数 (*注意，template 和 data files **不应** 在文件末尾额外带有 `\n` 符号*) ：

```sql theme={null}
INSERT INTO error_log FROM INFILE 'error.log'
SETTINGS format_template_row = 'row.template'
FORMAT Template
```

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

```sql theme={null}
SELECT
    request,
    count(*)
FROM error_log
GROUP BY request
```

```response theme={null}
┌─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 │
└──────────────────────────────────────────────────┴─────────┘
```

<div id="skipping-whitespaces">
  ### 跳过空白符
</div>

可考虑使用 [TemplateIgnoreSpaces](/docs/zh/reference/formats/Template/TemplateIgnoreSpaces)，它支持跳过模板中分隔符之间的空白符：

```text theme={null}
Template:               -->  "p1: ${p1:CSV}, p2: ${p2:CSV}"
TemplateIgnoreSpaces    -->  "p1:${p1:CSV}, p2:${p2:CSV}"
```

<div id="exporting-data-using-templates">
  ## 使用 Template 导出数据
</div>

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

[结果集模板](https://clickhouse-docs-assets.s3.us-east-1.amazonaws.com/output.results)，它定义了整个结果集的布局：

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

这里，`rows_read` 和 `time` 是每个请求都可用的系统指标。`data` 表示根据 [**行模板文件**](https://clickhouse-docs-assets.s3.us-east-1.amazonaws.com/output.rows) 中定义的模板生成的行 (在此文件中，`${data}` 必须始终作为第一个占位符出现) ：

```response theme={null}
${ip:Escaped} generated ${total:Escaped} requests
```

接下来，使用这些模板导出以下查询：

```sql theme={null}
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 ---
```

<div id="exporting-to-html-files">
  ### 导出为 HTML 文件
</div>

基于 Template 的结果也可以通过 [`INTO OUTFILE`](/docs/zh/reference/statements/select/into-outfile) 子句导出到文件中。下面让我们基于给定的 [resultset](https://clickhouse-docs-assets.s3.us-east-1.amazonaws.com/html.results) 和 [行](https://clickhouse-docs-assets.s3.us-east-1.amazonaws.com/html.row) 格式生成 HTML 文件：

```sql theme={null}
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'
```

<div id="exporting-to-xml">
  ### 导出为 XML
</div>

Template 格式可用于生成几乎任何文本格式的文件，包括 XML。只需提供相应的模板并执行导出即可。

你也可以考虑使用 [XML](/docs/zh/reference/formats/XML) 格式，以获得包含元数据的标准 XML 结果：

```sql theme={null}
SELECT *
FROM error_log
LIMIT 3
FORMAT XML
```

```xml theme={null}
<?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>

```

<div id="importing-data-based-on-regular-expressions">
  ## 基于正则表达式导入数据
</div>

[Regexp](/docs/zh/reference/formats/Regexp) 格式适用于更复杂的场景，即需要以更复杂的方式解析输入数据。下面继续解析示例文件 [error.log](https://clickhouse-docs-assets.s3.us-east-1.amazonaws.com/error.log)，但这次会捕获文件名和协议，并将它们分别保存到单独的列中。首先，为此准备一个新表：

```sql theme={null}
CREATE TABLE error_log
(
    `time` DateTime,
    `ip` String,
    `host` String,
    `file` String,
    `protocol` String
)
ENGINE = MergeTree
ORDER BY (host, file, time)
```

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

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

ClickHouse 会根据各个捕获组的顺序，将数据插入相应的列中。我们来检查一下数据：

```sql theme={null}
SELECT * FROM error_log LIMIT 5
```

```response theme={null}
┌────────────────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](/docs/zh/reference/settings/formats#format_regexp_skip_unmatched) 选项：

```sql theme={null}
SET format_regexp_skip_unmatched = 1;
```

<div id="other-formats">
  ## 其他格式
</div>

ClickHouse 支持多种格式，包括文本格式和二进制格式，以适用于各种场景和平台。你可以在以下文章中进一步了解更多格式及其使用方法：

* [CSV 和 TSV 格式](/docs/zh/guides/clickhouse/data-formats/csv-tsv)
* [Parquet](/docs/zh/guides/clickhouse/data-formats/parquet)
* [JSON 格式](/docs/zh/guides/clickhouse/data-formats/json/intro)
* **Regex 和 Template**
* [Native 和二进制格式](/docs/zh/guides/clickhouse/data-formats/binary)
* [SQL 格式](/docs/zh/guides/clickhouse/data-formats/sql)

此外，还可以查看 [clickhouse-local](https://clickhouse.com/blog/extracting-converting-querying-local-files-with-sql-clickhouse-local) —— 这是一款功能完整且便携的工具，可直接处理本地/远程文件，而无需 ClickHouse server。