跳至主要内容

在 ClickHouse 中使用 CSV 和 TSV 数据

ClickHouse 支持从 CSV 文件导入数据和导出数据。由于 CSV 文件可能具有不同的格式规范,包括标题行、自定义分隔符和转义符号,ClickHouse 提供了格式和设置来有效地处理每种情况。

从 CSV 文件导入数据

在导入数据之前,让我们创建一个具有相关结构的表

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

要将数据从CSV 文件导入到sometable表中,我们可以将文件直接通过管道传输到 clickhouse-client

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

请注意,我们使用FORMAT CSV让 ClickHouse 知道我们正在摄取 CSV 格式的数据。或者,我们可以使用FROM INFILE子句从本地文件加载数据

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

在这里,我们使用FORMAT CSV子句,以便 ClickHouse 理解文件格式。我们还可以使用url()函数直接从 URL 加载数据,或使用s3()函数从 S3 文件加载数据。

提示

我们可以跳过file()INFILE/OUTFILE的显式格式设置。在这种情况下,ClickHouse 将根据文件扩展名自动检测格式。

包含标题的 CSV 文件

假设我们的CSV 文件包含标题

head data-small-headers.csv
"path","month","hits"
"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34

要从该文件导入数据,我们可以使用CSVWithNames格式

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

在这种情况下,ClickHouse 在从文件导入数据时会跳过第一行。

提示

从 23.1 版开始,ClickHouse 在使用CSV类型时会自动检测 CSV 文件中的标题,因此无需使用CSVWithNamesCSVWithNamesAndTypes

具有自定义分隔符的 CSV 文件

如果 CSV 文件使用逗号以外的分隔符,我们可以使用format_csv_delimiter选项设置相关符号

SET format_csv_delimiter = ';'

现在,当我们从 CSV 文件导入时,;符号将用作分隔符,而不是逗号。

跳过 CSV 文件中的行

有时,我们可能需要在从 CSV 文件导入数据时跳过一定数量的行。这可以通过使用input_format_csv_skip_first_lines选项来完成

SET input_format_csv_skip_first_lines = 10

在这种情况下,我们将跳过 CSV 文件中的前十行

SELECT count(*) FROM file('data-small.csv', CSV)
┌─count()─┐
│ 990 │
└─────────┘

文件有 1k 行,但 ClickHouse 只加载了 990 行,因为我们要求跳过前 10 行。

提示

使用file()函数时,对于 ClickHouse Cloud,您需要在文件所在的机器上在clickhouse 客户端中运行命令。另一种选择是使用clickhouse-local在本地浏览文件。

处理 CSV 文件中的 NULL 值

空值可以根据生成文件的应用程序以不同的方式编码。默认情况下,ClickHouse 在 CSV 中使用\N作为空值。但我们可以使用format_csv_null_representation选项更改它。

假设我们有以下 CSV 文件

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

如果我们从该文件加载数据,ClickHouse 将把Nothing视为字符串(这是正确的)

SELECT * FROM file('nulls.csv')
┌─c1──────┬─c2──────┐
│ Donald │ 90 │
│ Joe │ Nothing │
│ Nothing │ 70 │
└─────────┴─────────┘

如果我们希望 ClickHouse 将Nothing视为NULL,我们可以使用以下选项定义它

SET format_csv_null_representation = 'Nothing'

现在我们在预期的地方有了NULL

SELECT * FROM file('nulls.csv')
┌─c1─────┬─c2───┐
│ Donald │ 90 │
│ Joe │ ᴺᵁᴸᴸ │
│ ᴺᵁᴸᴸ │ 70 │
└────────┴──────┘

TSV(制表符分隔)文件

制表符分隔数据格式被广泛用作数据交换格式。要将数据从TSV 文件加载到 ClickHouse 中,使用TabSeparated格式

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

还有一个TabSeparatedWithNames格式允许处理包含标题的 TSV 文件。并且,与 CSV 一样,我们可以使用input_format_tsv_skip_first_lines选项跳过前 X 行。

原始 TSV

有时,TSV 文件在保存时不会转义制表符和换行符。我们应该使用TabSeparatedRaw来处理此类文件。

导出到 CSV

我们之前示例中的任何格式也可以用于导出数据。要将数据从表(或查询)导出到 CSV 格式,我们使用相同的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

要向 CSV 文件添加标题,我们使用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

将导出的数据保存到 CSV 文件

要将导出的数据保存到文件,我们可以使用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.)

请注意,ClickHouse 将 3600 万行数据保存到 CSV 文件仅用了约 1秒。

导出具有自定义分隔符的 CSV

如果我们希望使用逗号以外的分隔符,我们可以为此使用format_csv_delimiter设置选项

SET format_csv_delimiter = '|'

现在 ClickHouse 将使用|作为 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

为 Windows 导出 CSV

如果我们希望 CSV 文件在 Windows 环境中正常工作,我们应该考虑启用output_format_csv_crlf_end_of_line选项。这将使用\r\n作为换行符,而不是\n

SET output_format_csv_crlf_end_of_line = 1;

CSV 文件的模式推断

在很多情况下,我们可能需要处理未知的 CSV 文件,因此我们必须探索要为列使用哪些类型。Clickhouse 默认情况下会尝试根据其对给定 CSV 文件的分析来猜测数据格式。这称为“模式推断”。可以使用DESCRIBE语句与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) │ │ │ │ │ │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

在这里,ClickHouse 可以有效地猜测 CSV 文件的列类型。如果我们不希望 ClickHouse 猜测,我们可以使用以下选项禁用此功能

SET input_format_csv_use_best_effort_in_schema_inference = 0

在这种情况下,所有列类型都将被视为String

使用显式列类型导出和导入 CSV

ClickHouse 还允许在使用CSVWithNamesAndTypes(以及其他*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

此格式将包含两行标题 - 一行包含列名,另一行包含列类型。这将允许 ClickHouse(和其他应用程序)在从此类文件加载数据时识别列类型

DESCRIBE file('data_csv_types.csv', CSVWithNamesAndTypes)
┌─name──┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ path │ String │ │ │ │ │ │
│ month │ Date │ │ │ │ │ │
│ hits │ UInt32 │ │ │ │ │ │
└───────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

现在 ClickHouse 根据(第二行)标题行识别列类型,而不是猜测。

自定义分隔符、分隔符和转义规则

在复杂的情况下,文本数据可以以高度自定义的方式格式化,但仍然具有结构。ClickHouse 针对此类情况提供了一个特殊的 CustomSeparated 格式,允许设置自定义转义规则、分隔符、换行符以及起始/结束符号。

假设我们在文件中拥有以下数据

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

我们可以看到,各个行都包裹在 row() 中,行之间用 , 分隔,各个值用 ; 分隔。在这种情况下,我们可以使用以下设置从该文件读取数据

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

现在我们可以从我们自定义格式化的 文件 加载数据

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

我们还可以使用 CustomSeparatedWithNames 来正确导出和导入标题。探索 正则表达式和模板 格式来处理更复杂的情况。

处理大型 CSV 文件

CSV 文件可能很大,ClickHouse 可以高效地处理任何大小的文件。大型文件通常会被压缩,ClickHouse 支持这一点,无需在处理前进行解压缩。我们可以在插入过程中使用 COMPRESSION 子句

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

如果省略了 COMPRESSION 子句,ClickHouse 仍会尝试根据文件扩展名猜测文件压缩方式。同样的方法可以用于将文件直接导出到压缩格式

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

这将创建一个压缩的 data_csv.csv.gz 文件。

其他格式

ClickHouse 支持许多格式,包括文本格式和二进制格式,以涵盖各种场景和平台。在以下文章中探索更多格式和使用方法

还可以查看 clickhouse-local - 一个可移植的、功能齐全的工具,用于处理本地/远程文件,无需 Clickhouse 服务器。