Como trabalhar com dados CSV e TSV no ClickHouse

O ClickHouse oferece suporte à importação de dados de arquivos CSV e à exportação para CSV. Como os arquivos CSV podem ter diferentes particularidades de formato, incluindo linhas de cabeçalho, delimitadores personalizados e símbolos de escape, o ClickHouse fornece formatos e configurações para lidar com cada caso com eficiência.

Importando dados de um arquivo CSV

Antes de importar os dados, vamos criar uma tabela com a estrutura adequada:

CREATE TABLE sometable
(
    `path` String,
    `month` Date,
    `hits` UInt32
)
ENGINE = MergeTree
ORDER BY tuple(month, path)

Para importar dados do arquivo CSV para a tabela sometable, podemos passar nosso arquivo diretamente para o clickhouse-client:

clickhouse-client -q "INSERT INTO sometable FORMAT CSV" < data_small.csv

Observe que usamos FORMAT CSV para informar ao ClickHouse que estamos fazendo a ingestão de dados no formato CSV. Como alternativa, podemos carregar dados de um arquivo local usando a cláusula FROM INFILE:

INSERT INTO sometable
FROM INFILE 'data_small.csv'
FORMAT CSV

Aqui, usamos a cláusula FORMAT CSV para que o ClickHouse entenda o formato do arquivo. Também podemos carregar dados diretamente de URLs usando a função url() ou de arquivos do S3 usando a função s3().

Podemos omitir a definição explícita do formato para file() e INFILE/OUTFILE. Nesse caso, o ClickHouse detectará automaticamente o formato com base na extensão do arquivo.

Arquivos CSV com cabeçalhos

Suponha que nosso arquivo CSV tenha cabeçalhos:

head data-small-headers.csv

"path","month","hits"
"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34

Para importar dados desse arquivo, podemos usar o formato CSVWithNames:

clickhouse-client -q "INSERT INTO sometable FORMAT CSVWithNames" < data_small_headers.csv

Nesse caso, o ClickHouse ignora a primeira linha ao importar os dados do arquivo.

A partir da versão 23.1, o ClickHouse detecta automaticamente cabeçalhos em arquivos CSV ao usar o formato CSV, portanto não é necessário usar CSVWithNames nem CSVWithNamesAndTypes.

Arquivos CSV com delimitadores personalizados

Se o arquivo CSV usar um delimitador diferente da vírgula, podemos usar a opção format_csv_delimiter para definir o símbolo correspondente:

SET format_csv_delimiter = ';'

Agora, ao importar de um arquivo CSV, o símbolo ; será usado como delimitador em vez de vírgula.

Ignorando linhas em um arquivo CSV

Às vezes, pode ser necessário ignorar um certo número de linhas ao importar dados de um arquivo CSV. Isso pode ser feito com a opção input_format_csv_skip_first_lines:

SET input_format_csv_skip_first_lines = 10

Neste caso, vamos pular as primeiras dez linhas do arquivo CSV:

SELECT count(*) FROM file('data-small.csv', CSV)

┌─count()─┐
│     990 │
└─────────┘

O arquivo tem 1 mil linhas, mas o ClickHouse carregou apenas 990, já que solicitamos que as 10 primeiras fossem ignoradas.

Ao usar a função file(), no ClickHouse Cloud você precisará executar os comandos no clickhouse client na máquina onde o arquivo está. Outra opção é usar o clickhouse-local para explorar arquivos localmente.

Tratamento de valores NULL em arquivos CSV

Os valores nulos podem ser codificados de formas diferentes, dependendo do aplicativo que gerou o arquivo. Por padrão, o ClickHouse usa \N como valor NULL em CSV. Mas podemos alterar isso usando a opção format_csv_null_representation. Suponha que temos o seguinte arquivo CSV:

> cat nulls.csv
Donald,90
Joe,Nothing
Nothing,70

Se carregarmos dados desse arquivo, o ClickHouse tratará Nothing como String (o que está correto):

SELECT * FROM file('nulls.csv')

┌─c1──────┬─c2──────┐
│ Donald  │ 90      │
│ Joe     │ Nothing │
│ Nothing │ 70      │
└─────────┴─────────┘

Se quisermos que o ClickHouse trate Nothing como NULL, podemos definir isso com a seguinte opção:

SET format_csv_null_representation = 'Nothing'

Agora temos NULL onde esperamos que esteja:

SELECT * FROM file('nulls.csv')

┌─c1─────┬─c2───┐
│ Donald │ 90   │
│ Joe    │ ᴺᵁᴸᴸ │
│ ᴺᵁᴸᴸ   │ 70   │
└────────┴──────┘

Arquivos TSV (separados por tabulação)

O formato de dados separados por tabulação é amplamente utilizado como formato de intercâmbio de dados. Para carregar dados de um arquivo TSV no ClickHouse, usa-se o formato TabSeparated:

clickhouse-client -q "INSERT INTO sometable FORMAT TabSeparated" < data_small.tsv

Há também o formato TabSeparatedWithNames, que permite trabalhar com arquivos TSV que têm cabeçalhos. E, assim como no caso do CSV, podemos pular as primeiras X linhas usando a opção input_format_tsv_skip_first_lines.

TSV bruto

Às vezes, arquivos TSV são salvos sem o escape de tabulações e quebras de linha. Devemos usar TabSeparatedRaw para lidar com esses arquivos.

Exportando para CSV

Qualquer um dos formatos dos exemplos anteriores também pode ser usado para exportar dados. Para exportar dados de uma tabela (ou de uma consulta) para o formato CSV, usamos a mesma cláusula FORMAT:

SELECT *
FROM sometable
LIMIT 5
FORMAT CSV

"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34
"1971-72_Utah_Stars_season","2016-10-01",1
"2015_UEFA_European_Under-21_Championship_qualification_Group_8","2015-12-01",73
"2016_Greater_Western_Sydney_Giants_season","2017-05-01",86

Para adicionar um cabeçalho ao arquivo CSV, usamos o formato CSVWithNames:

SELECT *
FROM sometable
LIMIT 5
FORMAT CSVWithNames

"path","month","hits"
"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34
"1971-72_Utah_Stars_season","2016-10-01",1
"2015_UEFA_European_Under-21_Championship_qualification_Group_8","2015-12-01",73
"2016_Greater_Western_Sydney_Giants_season","2017-05-01",86

Salvando dados exportados em um arquivo CSV

Para salvar os dados exportados em um arquivo, podemos usar a cláusula INTO…OUTFILE:

SELECT *
FROM sometable
INTO OUTFILE 'out.csv'
FORMAT CSVWithNames

36838935 rows in set. Elapsed: 1.304 sec. Processed 36.84 million rows, 1.42 GB (28.24 million rows/s., 1.09 GB/s.)

Observe que o ClickHouse levou ~1 segundo para salvar 36m linhas em um arquivo CSV.

Exportando CSV com delimitadores personalizados

Se quisermos usar delimitadores diferentes de vírgula, podemos usar a opção de configuração format_csv_delimiter:

SET format_csv_delimiter = '|'

Agora, o ClickHouse usará | como delimitador para o formato CSV:

SELECT *
FROM sometable
LIMIT 5
FORMAT CSV

"Akiba_Hebrew_Academy"|"2017-08-01"|241
"Aegithina_tiphia"|"2018-02-01"|34
"1971-72_Utah_Stars_season"|"2016-10-01"|1
"2015_UEFA_European_Under-21_Championship_qualification_Group_8"|"2015-12-01"|73
"2016_Greater_Western_Sydney_Giants_season"|"2017-05-01"|86

Exportando CSV para Windows

Se quisermos que um arquivo CSV funcione corretamente em um ambiente Windows, devemos considerar ativar a opção output_format_csv_crlf_end_of_line. Isso fará com que \r\n seja usado como quebra de linha em vez de \n:

SET output_format_csv_crlf_end_of_line = 1;

Inferência de esquema para arquivos CSV

Em muitos casos, podemos trabalhar com arquivos CSV desconhecidos, então precisamos identificar quais tipos usar nas colunas. Por padrão, o ClickHouse tentará deduzir os formatos de dados com base na análise de um determinado arquivo CSV. Isso é conhecido como “inferência de esquema”. Os tipos de dados detectados podem ser examinados usando a instrução DESCRIBE em conjunto com a função file():

DESCRIBE file('data-small.csv', CSV)

┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(String) │              │                    │         │                  │                │
│ c2   │ Nullable(Date)   │              │                    │         │                  │                │
│ c3   │ Nullable(Int64)  │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Aqui, o ClickHouse conseguiu inferir com eficiência os tipos das colunas do nosso arquivo CSV. Se não quisermos que o ClickHouse faça essa inferência, podemos desativá-la com a seguinte opção:

SET input_format_csv_use_best_effort_in_schema_inference = 0

Nesse caso, todos os tipos de coluna serão tratados como String.

Exportação e importação de CSV com tipos de coluna explícitos

O ClickHouse também permite definir explicitamente os tipos das colunas ao exportar dados usando CSVWithNamesAndTypes (e outros formatos da família WithNames):

SELECT *
FROM sometable
LIMIT 5
FORMAT CSVWithNamesAndTypes

"path","month","hits"
"String","Date","UInt32"
"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34
"1971-72_Utah_Stars_season","2016-10-01",1
"2015_UEFA_European_Under-21_Championship_qualification_Group_8","2015-12-01",73
"2016_Greater_Western_Sydney_Giants_season","2017-05-01",86

Esse formato incluirá duas linhas de cabeçalho: uma com os nomes das colunas e outra com os tipos das colunas. Isso permitirá que o ClickHouse (e outros aplicativos) identifiquem os tipos das colunas ao carregar dados de arquivos desse tipo:

DESCRIBE file('data_csv_types.csv', CSVWithNamesAndTypes)

┌─name──┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ path  │ String │              │                    │         │                  │                │
│ month │ Date   │              │                    │         │                  │                │
│ hits  │ UInt32 │              │                    │         │                  │                │
└───────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Agora, o ClickHouse identifica os tipos das colunas com base em uma (segunda) linha de cabeçalho, em vez de inferi-los.

Delimitadores personalizados, separadores e regras de escape

Em casos mais complexos, os dados de texto podem ser formatados de maneira altamente personalizada e, ainda assim, manter uma estrutura. O ClickHouse tem um formato especial CustomSeparated para esses casos, que permite definir regras de escape, delimitadores, separadores de linha e símbolos de início e fim personalizados. Suponha que tenhamos os seguintes dados no arquivo:

row('Akiba_Hebrew_Academy';'2017-08-01';241),row('Aegithina_tiphia';'2018-02-01';34),...

Podemos ver que cada linha é envolvida em row(), as linhas são separadas por , e os valores individuais são delimitados por ;. Nesse caso, podemos usar as seguintes configurações para ler dados deste arquivo:

SET format_custom_row_before_delimiter = 'row(';
SET format_custom_row_after_delimiter = ')';
SET format_custom_field_delimiter = ';';
SET format_custom_row_between_delimiter = ',';
SET format_custom_escaping_rule = 'Quoted';

Agora podemos carregar os dados do nosso arquivo com formatação personalizada:

SELECT *
FROM file('data_small_custom.txt', CustomSeparated)
LIMIT 3

┌─c1────────────────────────┬─────────c2─┬──c3─┐
│ Akiba_Hebrew_Academy      │ 2017-08-01 │ 241 │
│ Aegithina_tiphia          │ 2018-02-01 │  34 │
│ 1971-72_Utah_Stars_season │ 2016-10-01 │   1 │
└───────────────────────────┴────────────┴─────┘

Também podemos usar CustomSeparatedWithNames para garantir que os cabeçalhos sejam exportados e importados corretamente. Explore os formatos Regex e Template para lidar com casos ainda mais complexos.

Trabalhando com arquivos CSV grandes

Os arquivos CSV podem ser grandes, e o ClickHouse funciona de forma eficiente com arquivos de qualquer tamanho. Arquivos grandes geralmente vêm comprimidos, e o ClickHouse lida com isso sem necessidade de descompressão antes do processamento. Podemos usar uma cláusula COMPRESSION durante um insert:

INSERT INTO sometable
FROM INFILE 'data_csv.csv.gz'
COMPRESSION 'gzip' FORMAT CSV

Se a cláusula COMPRESSION for omitida, o ClickHouse ainda tentará identificar a compressão do arquivo com base na extensão. A mesma abordagem pode ser usada para exportar arquivos diretamente em formatos comprimidos:

SELECT *
FROM for_csv
INTO OUTFILE 'data_csv.csv.gz'
COMPRESSION 'gzip' FORMAT CSV

Isso criará um arquivo compactado data_csv.csv.gz.

Outros formatos

O ClickHouse oferece suporte a muitos formatos, tanto de texto quanto binários, para atender a vários cenários e plataformas. Explore mais formatos e formas 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 e remotos sem a necessidade de um servidor ClickHouse.

​Importando dados de um arquivo CSV

​Arquivos CSV com cabeçalhos

​Arquivos CSV com delimitadores personalizados

​Ignorando linhas em um arquivo CSV

​Tratamento de valores NULL em arquivos CSV

​Arquivos TSV (separados por tabulação)

​TSV bruto

​Exportando para CSV

​Salvando dados exportados em um arquivo CSV

​Exportando CSV com delimitadores personalizados

​Exportando CSV para Windows

​Inferência de esquema para arquivos CSV

​Exportação e importação de CSV com tipos de coluna explícitos

​Delimitadores personalizados, separadores e regras de escape

​Trabalhando com arquivos CSV grandes

​Outros formatos

Importando dados de um arquivo CSV

Arquivos CSV com cabeçalhos

Arquivos CSV com delimitadores personalizados

Ignorando linhas em um arquivo CSV

Tratamento de valores NULL em arquivos CSV

Arquivos TSV (separados por tabulação)

TSV bruto

Exportando para CSV

Salvando dados exportados em um arquivo CSV

Exportando CSV com delimitadores personalizados

Exportando CSV para Windows

Inferência de esquema para arquivos CSV

Exportação e importação de CSV com tipos de coluna explícitos

Delimitadores personalizados, separadores e regras de escape

Trabalhando com arquivos CSV grandes

Outros formatos