title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/interfaces/formats/Template/Template.md)

|  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- |
| Input Output Alias|  |  |  | | --- | --- | --- | | ✔ ✔  | | | | | |

## Description[​](#description "Direct link to 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`](#format_template_row) Specifies the path to the file which contains format strings for rows.|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [`format_template_resultset`](#format_template_resultset) Specifies the path to the file which contains format strings for rows|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | [`format_template_rows_between_delimiter`](#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](#inline_specification).| `format_template_resultset_format` Specifies the result set format string [in-line](#inline_specification).| Some settings of other formats (e.g.`output_format_json_quote_64bit_integers` when using `JSON` escaping  | | | | | | | | | | | | | |

## Settings and escaping rules[​](#settings-and-escaping-rules "Direct link to Settings and escaping rules")

### format\_template\_row[​](#format_template_row "Direct link to format_template_row")

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

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

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:

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

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 `INSERT`ing, the line below matches the expected template and would read values `bathroom interior design`, `2166`, `$3` into columns `Search phrase`, `count`, `ad price`.
- If `SELECT`ing 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`.

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

### format\_template\_rows\_between\_delimiter[​](#format_template_rows_between_delimiter "Direct link to 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[​](#format_template_resultset "Direct link to 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[​](#inline_specification "Direct link to 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`](#format_template_row) when using `format_template_row_format`.
- [`format_template_resultset`](#format_template_resultset) when using `format_template_resultset_format`.

## Example usage[​](#example-usage "Direct link to 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[​](#selecting-data "Direct link to Selecting data")

```
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    '
```