跳到主要内容
跳到主要内容

在 ClickHouse 中处理 CSV 和 TSV 数据

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

从 CSV 文件导入数据

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

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

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

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

提示

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

带标题的 CSV 文件

假设我们的 CSV 文件带有标题

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

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

提示

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

带自定义分隔符的 CSV 文件

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

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

跳过 CSV 文件中的行

有时,我们可能会在从 CSV 文件导入数据时跳过某些行。这可以使用 input_format_csv_skip_first_lines 选项来完成:

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

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

提示

使用 file() 函数时,使用 ClickHouse Cloud,您需要在文件所在的机器上使用 clickhouse client 运行命令。另一个选择是使用 clickhouse-local 在本地探索文件。

在 CSV 文件中处理 NULL 值

NULL 值的编码方式可能因生成文件的应用程序而异。默认情况下,ClickHouse 在 CSV 中使用 \N 表示 Null 值。但我们可以使用 format_csv_null_representation 选项来更改此行为。

假设我们有以下 CSV 文件:

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

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

现在我们在预期的位置得到了 NULL

TSV(制表符分隔)文件

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

还有一个 TabSeparatedWithNames 格式,可以处理带有标题的 TSV 文件。同样,我们可以使用 input_format_tsv_skip_first_lines 选项跳过前 X 行。

原始 TSV

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

导出到 CSV

我们之前示例中的任何格式也可以用于导出数据。要将数据从表(或查询)导出到 CSV 格式,我们使用相同的 FORMAT 子句:

要向 CSV 文件添加标题,我们使用 CSVWithNames 格式:

将导出数据保存到 CSV 文件

要将导出数据保存到文件中,我们可以使用 INTO...OUTFILE 子句:

请注意,ClickHouse 只需 ~1 秒即可将 3600 万行保存到 CSV 文件中。

导出带自定义分隔符的 CSV

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

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

为 Windows 导出 CSV

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

CSV 文件的模式推断

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

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

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

导入和导出具有显式列类型的 CSV

ClickHouse 还允许在使用 CSVWithNamesAndTypes (以及其他 *WithNames 格式系列)导出数据时显式设置列类型:

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

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

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

在复杂的情况下,文本数据可以以高度自定义的方式格式化,但仍然具有结构。ClickHouse 有一个特殊的 CustomSeparated 格式,用于此类情况,可以设置自定义转义规则、分隔符、行分隔符和开始/结束符号。

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

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

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

我们也可以使用 CustomSeparatedWithNames 以正确导出和导入标题。请探索 regex 和模板 格式以处理更复杂的情况。

处理大 CSV 文件

CSV 文件可能很大,ClickHouse 可以高效处理任何大小的文件。大文件通常是压缩的,ClickHouse 在处理之前无需解压缩。我们可以在插入时使用 COMPRESSION 子句:

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

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

其他格式

ClickHouse 引入了对多种格式的支持,包括文本和二进制,以覆盖各种场景和平台。请在以下文章中探索更多格式及其使用方法:

同时查看 clickhouse-local - 一款便携式全功能工具,可在无需 Clickhouse 服务器的情况下处理本地/远程文件。