在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 文件导入到表中，我们可以将文件直接通过管道传输到 clickhouse-client：sometable
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
在这里，我们使用子句，以便ClickHouse理解文件格式。我们还可以使用 url（） 函数直接从 URL 加载数据，或使用 s3（） 函数从 S3 文件加载数据。FORMAT CSV
提示
我们可以跳过 和 / 的显式格式设置。 在这种情况下，ClickHouse将根据文件扩展名自动检测格式。file()INFILEOUTFILE
带标题的 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 会在使用 type 时自动检测 CSV 文件中的标头，因此无需使用 or .CSVCSVWithNamesCSVWithNamesAndTypes
具有自定义分隔符的 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 行。
提示
使用该功能时，使用ClickHouse Cloud时，您需要在文件所在的计算机上运行命令。另一种选择是使用 clickhouse-local 在本地浏览文件。file()clickhouse client
处理 CSV 文件中的 NULL 值
Null 值的编码方式可能不同，具体取决于生成文件的应用程序。默认情况下，ClickHouse 在 CSV 中用作 Null 值。但是我们可以使用 format_csv_null_representation 选项来更改它。\N
假设我们有以下 CSV 文件：
> cat nulls.csv
Donald,90
Joe,Nothing
Nothing,70
如果我们从这个文件加载数据，ClickHouse 会被视为一个 String（这是正确的）：Nothing
SELECT * FROM file('nulls.csv')
┌─c1──────┬─c2──────┐
│ Donald │ 90 │
│ Joe │ Nothing │
│ Nothing │ 70 │
└─────────┴─────────┘
如果我们希望 ClickHouse 将其视为 ，我们可以使用以下选项来定义它：NothingNULL
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 ~1 秒将 36m 行保存到 CSV 文件所花费的时间。
使用自定义分隔符导出 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 文件的分析来猜测数据格式。这称为“架构推理”。可以使用与 file（） 函数配对的语句来探索检测到的数据类型：DESCRIBE
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
在这种情况下，所有列类型都将被视为 a。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
如果省略了子句，ClickHouse仍将尝试根据其扩展名来猜测文件压缩。可以使用相同的方法将文件直接导出为压缩格式：COMPRESSION
SELECT *
FROM for_csv
INTO OUTFILE 'data_csv.csv.gz'
COMPRESSION 'gzip' FORMAT CSV
这将创建一个压缩文件。data_csv.csv.gz
其他格式
ClickHouse引入了对文本和二进制多种格式的支持，以覆盖各种场景和平台。在以下文章中探索更多格式和使用它们的方法：
CSV 和 TSV 格式
木条镶花之地板
JSON 格式
正则表达式和模板
本机和二进制格式
SQL 格式
还要检查 clickhouse-local - 一个便携式的全功能工具，无需 Clickhouse 服务器即可处理本地/远程文件。
编辑本页
7866
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
