Template

Input	Output	Alias
✔	✔

Description

For cases where you need more customization than other standard formats offer, the Template format allows the user to specify their own custom format string with placeholders for values, and specifying escaping rules for the data.

It uses the following settings:

Setting	Description
format_template_row	Specifies the path to the file which contains format strings for rows.
format_template_resultset	Specifies the path to the file which contains format strings for rows
format_template_rows_between_delimiter	Specifies the delimiter between rows, which is printed (or expected) after every row except the last one (\n by default)
format_template_row_format	Specifies the format string for rows in-line.
format_template_resultset_format	Specifies the result set format string in-line.
Some settings of other formats (e.g.output_format_json_quote_64bit_integers when using JSON escaping

Settings And Escaping Rules

format_template_row

The setting format_template_row specifies the path to the file which contains format strings for rows with the following syntax:

Where:

Part of syntax	Description
delimiter_i	A delimiter between values ($ symbol can be escaped as $$)
column_i	The name or index of a column whose values are to be selected or inserted (if empty, then the column will be skipped)
serializeAs_i	An escaping rule for the column values.

The following escaping rules are supported:

Escaping Rule	Description
CSV, JSON, XML	Similar to the formats of the same names
Escaped	Similar to TSV
Quoted	Similar to Values
Raw	Without escaping, similar to TSVRaw
None	No escaping rule - see note below

Note

If an escaping rule is omitted, then None will be used. XML is suitable only for output.

Let's look at an example. Given the following format string:

The following values will be printed (if using SELECT) or expected (if using INPUT), between columns Search phrase:, , count:, , ad price: $ and ; delimiters respectively:

• s (with escape rule Quoted)
• c (with escape rule Escaped)
• p (with escape rule JSON)

For example:

• If INSERTing, the line below matches the expected template and would read values bathroom interior design, 2166, $3 into columns Search phrase, count, ad price.
• If SELECTing the line below is the output, assuming that values bathroom interior design, 2166, $3 are already stored in a table under columns Search phrase, count, ad price.

format_template_rows_between_delimiter

The setting format_template_rows_between_delimiter setting specifies the delimiter between rows, which is printed (or expected) after every row except the last one (\n by default)

format_template_resultset

The setting format_template_resultset specifies the path to the file, which contains a format string for the result set.

The format string for the result set has the same syntax as a format string for rows. It allows for specifying a prefix, a suffix and a way to print some additional information and contains the following placeholders instead of column names:

• data is the rows with data in format_template_row format, separated by format_template_rows_between_delimiter. This placeholder must be the first placeholder in the format string.
• totals is the row with total values in format_template_row format (when using WITH TOTALS).
• min is the row with minimum values in format_template_row format (when extremes are set to 1).
• max is the row with maximum values in format_template_row format (when extremes are set to 1).
• rows is the total number of output rows.
• rows_before_limit is the minimal number of rows there would have been without LIMIT. Output only if the query contains LIMIT. If the query contains GROUP BY, rows_before_limit_at_least is the exact number of rows there would have been without a LIMIT.
• time is the request execution time in seconds.
• rows_read is the number of rows has been read.
• bytes_read is the number of bytes (uncompressed) has been read.

The placeholders data, totals, min and max must not have escaping rule specified (or None must be specified explicitly). The remaining placeholders may have any escaping rule specified.

Note

If the format_template_resultset setting is an empty string, ${data} is used as the default value.

For insert queries format allows skipping some columns or fields if prefix or suffix (see example).

In-line specification

Often times it is challenging or not possible to deploy the format configurations (set by format_template_row, format_template_resultset) for the template format to a directory on all nodes in a cluster. Furthermore, the format may be so trivial that it does not require being placed in a file.

For these cases, format_template_row_format (for format_template_row) and format_template_resultset_format (for format_template_resultset) can be used to set the template string directly in the query, rather than as a path to the file which contains it.

Note

The rules for format strings and escape sequences are the same as those for:

• format_template_row when using format_template_row_format.
• format_template_resultset when using format_template_resultset_format.

Example Usage

Let's look at two examples of how we can use the Template format, first for selecting data and then for inserting data.

Selecting Data

Result:

Inserting Data

PageViews, UserID, Duration and Sign inside placeholders are names of columns in the table. Values after Useless field in rows and after \nTotal rows: in suffix will be ignored. All delimiters in the input data must be strictly equal to delimiters in specified format strings.

In-line Specification

Tired of manually formatting markdown tables? In this example we'll look at how we can use the Template format and in-line specification settings to achieve a simple task - SELECTing the names of some ClickHouse formats from the system.formats table and formatting them as a markdown table. This can be easily achieved using the Template format and settings format_template_row_format and format_template_resultset_format.

In previous examples we specified the result-set and row format strings in separate files, with the paths to those files specified using the format_template_resultset and format_template_row settings respectively. Here we'll do it in-line because our template is trivial, consisting only of a few | and - to make the markdown table. We'll specify our result-set template string using the setting format_template_resultset_format. To make the table header we've added |ClickHouse Formats|\n|---|\n before ${data}. We use setting format_template_row_format to specify the template string |`{0:XML}`| for our rows. The Template format will insert our rows with the given format into placeholder ${data}. In this example we have only one column, but if you wanted to add more you could do so by adding {1:XML}, {2:XML}... etc to your row template string, choosing the escaping rule as appropriate. In this example we've gone with escaping rule XML.

Look at that! We've saved ourselves the trouble of having to manually add all those |s and -s to make that markdown table: