Importing and exporting custom text data using Templates and Regex in ClickHouse

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/data-formats/templates-regex.md)

We often have to deal with data in custom text formats. That could be a non-standard format, invalid JSON, or a broken CSV. Using standard parsers like CSV or JSON won't work in all such cases. But ClickHouse has us covered here with powerful Template and Regex formats.

## Importing based on a template[​](#importing-based-on-a-template "Direct link to Importing based on a template")

Suppose we want to import data from the following [log file](/docs/assets/files/error-0106c8ba4ef13b82c19942935b0bbfb6.log):

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

We can use a [Template](/docs/interfaces/formats/Template) format to import this data. We have to define a template string with values placeholders for each row of input data:

```
<time> [error] client: <ip>, server: <host> "<request>"
```

Let's create a table to import our data into:

```
CREATE TABLE error_log
(
    `time` DateTime,
    `ip` String,
    `host` String,
    `request` String
)
ENGINE = MergeTree
ORDER BY (host, request, time)
```

To import data using a given template, we have to save our template string in a file ([row.template](/docs/assets/files/row-be2d6fe4aeee727e309436da64ba0bed.template) in our case):

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

We define a name of a column and escaping rule in a `${name:escaping}` format. Multiple options are available here, like CSV, JSON, Escaped, or Quoted, which implement [respective escaping rules](/docs/interfaces/formats/Template).

Now we can use the given file as an argument to the `format_template_row` settings option while importing data (*note, that template and data files **shouldn't have** an extra `\n` symbol at the end of file*):

```
INSERT INTO error_log FROM INFILE 'error.log'
SETTINGS format_template_row = 'row.template'
FORMAT Template
```

And we can make sure our data was loaded into the table:

```
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 │
└──────────────────────────────────────────────────┴─────────┘
```

### Skipping whitespaces[​](#skipping-whitespaces "Direct link to Skipping whitespaces")

Consider using [TemplateIgnoreSpaces](/docs/interfaces/formats/TemplateIgnoreSpaces), which allows skipping whitespaces between delimiters in a template:

```
Template:               -->  "p1: ${p1:CSV}, p2: ${p2:CSV}"
TemplateIgnoreSpaces    -->  "p1:${p1:CSV}, p2:${p2:CSV}"
```

## Exporting data using templates[​](#exporting-data-using-templates "Direct link to Exporting data using templates")

We can also export data to any text format using templates as well. In this case, we have to create two files:

[Result set template](/docs/assets/files/output-5ab840955a0c4f8bfec1a6a06faf4ef7.results), which defines the layout for the whole result set:

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

Here, `rows_read` and `time` are system metrics available for each request. While `data` stands for generated rows (`${data}` should always come as a first placeholder in this file), based on a template defined in a [**row template file**](/docs/assets/files/output-68f8800ebbb9177338c7584faaaf20a2.rows):

```
${ip:Escaped} generated ${total:Escaped} requests
```

Now let's use these templates to export the following query:

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

### Exporting to HTML files[​](#exporting-to-html-files "Direct link to Exporting to HTML files")

Template-based results can also be exported to files using an [`INTO OUTFILE`](/docs/sql-reference/statements/select/into-outfile) clause. Let's generate HTML files based on given [resultset](/docs/assets/files/html-d8814c6e44ab8251cbcdd933be21e432.results) and [row](/docs/assets/files/html-271182f13aac17f7b67e964914bb2159.row) formats:

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

### Exporting to XML[​](#exporting-to-xml "Direct link to Exporting to XML")

Template format can be used to generate all imaginable text format files, including XML. Just put a relevant template and do the export.

Also consider using an [XML](/docs/interfaces/formats/XML) format to get standard XML results including metadata:

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

## Importing data based on regular expressions[​](#importing-data-based-on-regular-expressions "Direct link to Importing data based on regular expressions")

[Regexp](/docs/interfaces/formats/Regexp) format addresses more sophisticated cases when input data needs to be parsed in a more complex way. Let's parse our [error.log](/docs/assets/files/error-0106c8ba4ef13b82c19942935b0bbfb6.log) example file, but capture the file name and protocol this time to save them into separate columns. First, let's prepare a new table for that:

```
CREATE TABLE error_log
(
    `time` DateTime,
    `ip` String,
    `host` String,
    `file` String,
    `protocol` String
)
ENGINE = MergeTree
ORDER BY (host, file, time)
```

Now we can import data based on a regular expression:

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

ClickHouse will insert data from each capture group into the relevant column based on its order. Let's check the data:

```
SELECT * FROM error_log LIMIT 5
```