Importação e exportação de dados de texto personalizados usando template e regex no ClickHouse

Muitas vezes, precisamos lidar com dados em formatos de texto personalizados. Isso pode incluir um formato fora do padrão, JSON inválido ou um CSV corrompido. Usar parsers padrão, como CSV ou JSON, não funciona em todos esses casos. Mas o ClickHouse resolve isso com os poderosos formatos template e Regex.

Importação baseada em um Template

Suponha que queiramos importar dados do seguinte arquivo de 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"

Podemos usar o formato Template para importar esses dados. Precisamos definir uma string de template com marcadores de valor para cada linha dos dados de entrada:

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

Vamos criar uma tabela para importar os dados:

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

Para importar dados usando um determinado template, precisamos salvar nossa string de template em um arquivo (row.template, no nosso caso):

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

Definimos o nome de uma coluna e a regra de escape no formato ${name:escaping}. Há várias opções disponíveis aqui, como CSV, JSON, Escaped ou Quoted, que implementam as regras de escape correspondentes.

Agora podemos usar o arquivo fornecido como argumento para a opção de configuração format_template_row ao importar dados (observe que os arquivos de template e de dados não devem ter um símbolo \n extra no fim do arquivo):

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

E podemos garantir que nossos dados foram carregados na tabela:

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

Ignorar espaços em branco

Considere usar TemplateIgnoreSpaces, que permite ignorar espaços em branco entre delimitadores no Template:

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

Exportando dados usando templates

Também podemos exportar dados para qualquer formato de texto usando templates. Nesse caso, temos que criar dois arquivos:

Template do conjunto de resultados, que define o layout de todo o conjunto de resultados:

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

Aqui, rows_read e time são métricas do sistema disponíveis para cada requisição. Já data representa as linhas geradas (${data} deve sempre aparecer como o primeiro placeholder neste arquivo), com base em um arquivo de template de linha:

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

Agora, vamos usar esses templates para exportar a seguinte consulta:

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

Exportando para arquivos HTML

Os resultados baseados em Template também podem ser exportados para arquivos usando a cláusula INTO OUTFILE. Vamos gerar arquivos HTML com base nos formatos resultset e row fornecidos:

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'

Exportando para XML

O formato Template pode ser usado para gerar arquivos de texto de todos os tipos imagináveis, incluindo XML. Basta fornecer um template adequado e fazer a exportação.

Considere também usar o formato XML para obter resultados XML padrão, incluindo metadados:

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>

Importando dados com base em expressões regulares

O formato Regexp atende a casos mais sofisticados em que os dados de entrada precisam ser processados de forma mais complexa. Vamos processar nosso arquivo de exemplo error.log, mas, desta vez, capturar o nome do arquivo e o protocolo para salvá-los em colunas separadas. Primeiro, vamos preparar uma nova tabela para isso:

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

Agora podemos importar dados com base em uma expressão regular:

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

O ClickHouse inserirá os dados de cada grupo de captura na coluna correspondente, com base na sua ordem. Vamos verificar os dados:

SELECT * FROM error_log LIMIT 5

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

Por padrão, o ClickHouse gerará um erro em caso de linhas sem correspondência. Se, em vez disso, você quiser ignorar as linhas sem correspondência, habilite a opção format_regexp_skip_unmatched:

SET format_regexp_skip_unmatched = 1;

Outros formatos

O ClickHouse oferece suporte a muitos formatos, tanto de texto quanto binários, para atender a diversos cenários e plataformas. Explore mais formatos e maneiras de trabalhar com eles nos artigos a seguir:

Confira também o clickhouse-local - uma ferramenta portátil e completa para trabalhar com arquivos locais/remotos sem precisar de um servidor ClickHouse.