Template - ClickHouse Documentation

输入	输出	别名
✔	✔

说明

对于需要比其他标准格式提供更多自定义能力的场景， Template 格式允许用户通过值占位符指定自定义的格式字符串，并为数据指定转义规则。它使用以下设置：

Setting	Description
`format_template_row`	指定包含行格式字符串的文件路径。
`format_template_resultset`	指定包含行格式字符串的文件路径
`format_template_rows_between_delimiter`	指定行之间的分隔符；除最后一行外，会在每一行后输出 (或期望出现) 该分隔符 (默认为 `\n`)
`format_template_row_format`	内联指定行的格式字符串。
`format_template_resultset_format`	内联指定结果集的格式字符串。
其他格式的一些设置 (例如，使用 `JSON` 转义时的 `output_format_json_quote_64bit_integers`

设置与转义规则

format_template_row

设置 format_template_row 用于指定包含行格式字符串的文件路径，语法如下：

delimiter_1${column_1:serializeAs_1}delimiter_2${column_2:serializeAs_2} ... delimiter_N

其中：

语法部分	描述
`delimiter_i`	值之间的分隔符 (`$` 符号可转义为 `$$`)
`column_i`	要选择或插入的列名或列索引 (如果为空，则跳过该列)
`serializeAs_i`	列值的转义规则。

支持以下转义规则：

转义规则	描述
`CSV`, `JSON`, `XML`	与同名格式类似
`Escaped`	与 `TSV` 类似
`Quoted`	与 `Values` 类似
`Raw`	不转义，与 `TSVRaw` 类似
`None`	无转义规则，见下文说明

如果省略转义规则，则使用 None。XML 仅适用于输出。

来看一个示例。给定以下格式字符串：

Search phrase: ${s:Quoted}, count: ${c:Escaped}, ad price: $$${p:JSON};

以下值将被输出 (如果使用 SELECT) 或作为预期输入 (如果使用 INPUT) ，分别位于列 Search phrase:、, count:、, ad price: $ 与 ; 分隔符之间：

s (转义规则为 Quoted)
c (转义规则为 Escaped)
p (转义规则为 JSON)

例如：

如果执行 INSERT，下面这一行符合预期模板，并会将值 bathroom interior design、2166、$3 读入列 Search phrase、count、ad price。
如果执行 SELECT，则下面这一行就是输出结果，前提是值 bathroom interior design、2166、$3 已经存储在某个表的列 Search phrase、count、ad price 中。

Search phrase: 'bathroom interior design', count: 2166, ad price: $3;

format_template_rows_between_delimiter

设置 format_template_rows_between_delimiter 用于指定行与行之间的分隔符；除最后一行外，它会在每一行后输出 (或期望存在) 该分隔符 (默认为 \n)

format_template_resultset

设置 format_template_resultset 用于指定一个文件路径，该文件包含结果集的格式字符串。结果集的格式字符串与行的格式字符串语法相同。它支持指定前缀、后缀以及输出某些附加信息的方式，并使用以下占位符来代替列名：

data 表示采用 format_template_row 格式的数据行，并由 format_template_rows_between_delimiter 分隔。此占位符必须是格式字符串中的第一个占位符。
totals 表示采用 format_template_row 格式的总计值行 (使用 WITH TOTALS 时) 。
min 表示采用 format_template_row 格式的最小值行 (当 extremes 设置为 1 时) 。
max 表示采用 format_template_row 格式的最大值行 (当 extremes 设置为 1 时) 。
rows 表示输出的总行数。
rows_before_limit 表示如果没有 LIMIT，本应输出的最小行数。仅当查询包含 LIMIT 时才会输出。如果查询包含 GROUP BY，则 rows_before_limit_at_least 表示如果没有 LIMIT，本应输出的精确行数。
time 表示请求执行时间 (以秒为单位) 。
rows_read 表示已读取的行数。
bytes_read 表示已读取的字节数 (未压缩) 。

占位符 data、totals、min 和 max 不得指定转义规则 (或者必须显式指定 None) 。其余占位符可以指定任意转义规则。

如果 format_template_resultset 设置为空字符串，则默认值为 ${data}。

对于 insert 查询，这种格式允许在存在前缀或后缀时跳过某些列或字段 (参见示例) 。

内联指定

很多时候，要将 Template 格式的格式配置 (通过 format_template_row、format_template_resultset 设置) 部署到 cluster 中所有节点上的某个 directory，会很困难，甚至根本无法实现。此外，format 本身可能非常简单，简单到无需放在 file 中。在这种情况下，可以使用 format_template_row_format (用于 format_template_row) 和 format_template_resultset_format (用于 format_template_resultset) ，直接在查询中设置模板字符串，而不是将其指定为包含该字符串的 file path。

格式字符串和转义序列的规则与以下内容相同：

使用 format_template_row_format 时，与 format_template_row 的规则相同。
使用 format_template_resultset_format 时，与 format_template_resultset 的规则相同。

示例用法

我们来看两个 Template 格式的使用示例：先看如何查询数据，再看如何插入数据。

查询数据

Query

SELECT SearchPhrase, count() AS c FROM test.hits GROUP BY SearchPhrase ORDER BY c DESC LIMIT 5 FORMAT Template SETTINGS
format_template_resultset = '/some/path/resultset.format', format_template_row = '/some/path/row.format', format_template_rows_between_delimiter = '\n    '

/some/path/resultset.format

<!DOCTYPE HTML>
<html> <head> <title>Search phrases</title> </head>
 <body>
  <table border="1"> <caption>Search phrases</caption>
    <tr> <th>Search phrase</th> <th>Count</th> </tr>
    ${data}
  </table>
  <table border="1"> <caption>Max</caption>
    ${max}
  </table>
  <b>Processed ${rows_read:XML} rows in ${time:XML} sec</b>
 </body>
</html>

/some/path/row.format

<tr> <td>${0:XML}</td> <td>${1:XML}</td> </tr>

Response

<!DOCTYPE HTML>
<html> <head> <title>Search phrases</title> </head>
 <body>
  <table border="1"> <caption>Search phrases</caption>
    <tr> <th>Search phrase</th> <th>Count</th> </tr>
    <tr> <td></td> <td>8267016</td> </tr>
    <tr> <td>bathroom interior design</td> <td>2166</td> </tr>
    <tr> <td>clickhouse</td> <td>1655</td> </tr>
    <tr> <td>spring 2014 fashion</td> <td>1549</td> </tr>
    <tr> <td>freeform photos</td> <td>1480</td> </tr>
  </table>
  <table border="1"> <caption>Max</caption>
    <tr> <td></td> <td>8873898</td> </tr>
  </table>
  <b>Processed 3095973 rows in 0.1569913 sec</b>
 </body>
</html>

插入数据

Some header
Page views: 5, User id: 4324182021466249494, Useless field: hello, Duration: 146, Sign: -1
Page views: 6, User id: 4324182021466249494, Useless field: world, Duration: 185, Sign: 1
Total rows: 2

INSERT INTO UserActivity SETTINGS
format_template_resultset = '/some/path/resultset.format', format_template_row = '/some/path/row.format'
FORMAT Template

/some/path/resultset.format

Some header\n${data}\nTotal rows: ${:CSV}\n

/some/path/row.format

Page views: ${PageViews:CSV}, User id: ${UserID:CSV}, Useless field: ${:CSV}, Duration: ${Duration:CSV}, Sign: ${Sign:CSV}

占位符中的 PageViews、UserID、Duration 和 Sign 都是表中的列名。每行中 Useless field 之后的值，以及后缀中 \nTotal rows: 之后的值都会被忽略。输入数据中的所有分隔符都必须与指定格式字符串中的分隔符完全一致。

内联指定

还在为手动格式化 markdown 表格而头疼吗？在这个示例中，我们来看看如何使用 Template 格式和内联指定设置完成一个简单任务——从 system.formats 表中 SELECT 一些 ClickHouse 格式的名称，并将其格式化为 markdown 表格。使用 Template 格式以及 format_template_row_format 和 format_template_resultset_format 设置，即可轻松实现这一点。在前面的示例中，我们分别在单独的文件中指定了 result-set 和行的格式字符串，并通过 format_template_resultset 和 format_template_row 设置分别指定这些文件的路径。这里我们将以内联方式完成，因为我们的模板非常简单，只需要少量 | 和 - 就能构造出 markdown 表格。我们将使用 format_template_resultset_format 设置来指定 result-set 模板字符串。为了生成表头，我们在 ${data} 之前添加了 |ClickHouse Formats|\n|---|\n。我们使用 format_template_row_format 设置为各行指定模板字符串 |`{0:XML}`|。Template 格式会将按给定格式生成的各行插入到 ${data} 占位符中。在这个示例中，我们只有一列；但如果你想添加更多列，也可以在行模板字符串中加入 {1:XML}、{2:XML} 等，并根据需要选择合适的转义规则。本示例中使用的是 XML 转义规则。

Query

WITH formats AS
(
 SELECT * FROM system.formats
 ORDER BY rand()
 LIMIT 5
)
SELECT * FROM formats
FORMAT Template
SETTINGS
 format_template_row_format='|`${0:XML}`|',
 format_template_resultset_format='|ClickHouse Formats|\n|---|\n${data}\n'

看看！这样一来，我们就省去了手动添加所有那些 | 和 - 来生成 markdown 表格的麻烦：

Response

|ClickHouse Formats|
|---|
|`BSONEachRow`|
|`CustomSeparatedWithNames`|
|`Prometheus`|
|`DWARF`|
|`Avro`|

​说明

​设置与转义规则

​format_template_row

​format_template_rows_between_delimiter

​format_template_resultset

​内联指定

​示例用法

​查询数据

​插入数据

​内联指定

说明

设置与转义规则

format_template_row

format_template_rows_between_delimiter

format_template_resultset

内联指定

示例用法

查询数据

插入数据

内联指定