CSV and TSV

更新时间：2025-08-21

CSV and TSV

ClickHouse 支持从 CSV 导入数据和将数据导出到 CSV。由于 CSV 文件可能具有不同的格式细节，包括标题行、自定义分隔符和转义符号，ClickHouse 提供了格式和设置来有效解决每种情况。

从CSV文件导入数据

在导入数据之前，创建一个具有相关结构的表：

Plain Text

1CREATE TABLE sometable
2(
3    `path` String,
4    `month` Date,
5    `hits` UInt32
6)
7ENGINE = MergeTree
8ORDER BY tuple(month, path)

要将数据从CSV 文件导入到sometable表中，我们可以将文件直接传输到 clickhouse-client：

Plain Text

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

注意，使用FORMAT CSV让 ClickHouse知道我们正在提取 CSV 格式的数据。或者，我们可以使用FROM INFILE句子从本地文件加载数据。这里，我们使用了FORMAT CSV句子，以便 ClickHouse 能够理解文件格式。我们还可以使用url()函数直接从 URL 加载数据，或者使用s3()函数从 S3 文件加载数据。

Plain Text

1INSERT INTO sometable
2FROM INFILE 'data_small.csv'
3FORMAT CSV

带标题的 CSV 文件

假设CSV文件包含标题：

Plain Text

1head data-small-headers.csv

Plain Text

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

要从此文件导入数据，我们可以使用CSVWithNames格式：

Plain Text

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

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

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

如果CSV文件使用逗号以外的分隔符，可以使用format_csv_delimiter选项来设置相关符号：

Plain Text

1SET format_csv_delimiter = ';'

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

跳过 CSV 文件中的行

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

Plain Text

1SET input_format_csv_skip_first_lines = 10

在这种情况下，将跳过 CSV 文件的前十行：

Plain Text

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

Plain Text

1┌─count()─┐
2│     990 │
3└─────────┘

该文件有1000行，但由于要求跳过前 10 行，因此ClickHouse仅加载了990 行。

处理CSV文件中的NULL值

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

假设有以下 CSV 文件：

Plain Text

1> cat nulls.csv
2Donald,90
3Joe,Nothing
4Nothing,70

2.如果我们从此文件加载数据，ClickHouse 将把它视为Nothing一个字符串（这是正确的）：

Plain Text

1SELECT * FROM file('nulls.csv')

Plain Text

1┌─c1──────┬─c2──────┐
2│ Donald  │ 90      │
3│ Joe     │ Nothing │
4│ Nothing │ 70      │
5└─────────┴─────────┘

如果希望ClickHouse将其视为Nothing，NULL可以使用以下选项定义：

Plain Text

1SET format_csv_null_representation = 'Nothing'

现在已经知道NULL它位于期望的位置了：

Plain Text

1SELECT * FROM file('nulls.csv')

Plain Text

1┌─c1─────┬─c2───┐
2│ Donald │ 90   │
3│ Joe    │ ᴺᵁᴸᴸ │
4│ ᴺᵁᴸᴸ   │ 70   │
5└────────┴──────┘

TSV（制表符分隔）文件

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

Plain Text

1clickhouse-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句子。

Plain Text

1SELECT *
2FROM sometable
3LIMIT 5
4FORMAT CSV

Plain Text

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

要向CSV文件添加标题，需要使用CSVWithNames格式：

Plain Text

1SELECT *
2FROM sometable
3LIMIT 5
4FORMAT CSVWithNames

Plain Text

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

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

要将导出的数据保存到文件，可以使用INTO…OUTFILE句子：

Plain Text

1SELECT *
2FROM sometable
3INTO OUTFILE 'out.csv'
4FORMAT CSVWithNames

Plain Text

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

使用自定义分隔符导出CSV

如果想要使用逗号以外的分隔符，我们可以使用format_csv_delimiter设置选项：

Plain Text

1SET format_csv_delimiter = '|'

现在ClickHouse将使用|作为 CSV 格式的分隔符：

Plain Text

1SELECT *
2FROM sometable
3LIMIT 5
4FORMAT CSV

Plain Text

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

导出适用于Windows的CSV

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

Plain Text

1SET output_format_csv_crlf_end_of_line = 1;

CSV文件的架构推断

在许多情况下，可能会使用未知的CSV文件，因此我们必须探索要使用哪些类型的列。默认情况下，Clickhouse 将尝试根据对给定CSV文件的分析来猜测数据格式。这称为“模式推断”。可以使用语句与file()DESCRIBE函数配对来探索检测到的数据类型：

Plain Text

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

Plain Text

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

ClickHouse可以有效地猜测CSV文件的列类型。如果不想让ClickHouse猜测，可以使用以下选项禁用此功能：

Plain Text

1SET input_format_csv_use_best_effort_in_schema_inference = 0

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

导出和导入具有明确列类型的CSV

ClickHouse还允许在使用CSVWithNamesAndTypes（和其他 *WithNames 格式系列）导出数据时明确设置列类型：

Plain Text

1SELECT *
2FROM sometable
3LIMIT 5
4FORMAT CSVWithNamesAndTypes

Plain Text

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

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

Plain Text

1DESCRIBE file('data_csv_types.csv', CSVWithNamesAndTypes)

Plain Text

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

现在 ClickHouse 根据（第二）标题行来识别列类型。

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

在复杂的情况下，文本数据可以以高度自定义的方式格式化，但仍具有结构。ClickHouse 针对这种情况具有特殊的CustomSeparated格式，允许设置自定义转义规则、分隔符、行分隔符和开始/结束符号。

假设文件中有以下数据：

Plain Text

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

可以看到，各行用包裹row()，各行用分隔;。在这种情况下，我们可以使用以下设置从此文件中读取数据：

Plain Text

1SET format_custom_row_before_delimiter = 'row(';
2SET format_custom_row_after_delimiter = ')';
3SET format_custom_field_delimiter = ';';
4SET format_custom_row_between_delimiter = ',';
5SET format_custom_escaping_rule = 'Quoted';

可以从自定义格式的文件中加载数据：

Plain Text

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

Plain Text

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

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

处理大型CSV文件

CSV文件可能很大，ClickHouse可以高效处理任何大小的文件。大型文件通常经过压缩，ClickHouse可以解决这个问题，无需在处理前解压。可以COMPRESSION在插入期间使用句子：

Plain Text

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

如果COMPRESSION省略子句，ClickHouse 仍将尝试根据文件扩展名猜测文件压缩。可以使用相同的方法将文件直接导出为压缩格式，这将创建一个压缩data_csv.csv.gz文件。

Plain Text

1SELECT *
2FROM for_csv
3INTO OUTFILE 'data_csv.csv.gz'
4COMPRESSION 'gzip' FORMAT CSV

SQL转储

快速入门

MapReduce BMR

MapReduce BMR

CSV and TSV

CSV and TSV

从CSV文件导入数据

带标题的 CSV 文件

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

跳过 CSV 文件中的行

处理CSV文件中的NULL值

TSV（制表符分隔）文件

原始TSV

导出至CSV

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

使用自定义分隔符导出CSV

导出适用于Windows的CSV

CSV文件的架构推断

导出和导入具有明确列类型的CSV

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

处理大型CSV文件