DuckDB CSV 探测器：自动检测类型和方言详解

本文主要是介绍DuckDB CSV 探测器：自动检测类型和方言详解，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

---

DuckDB 主要关注性能，利用现代文件格式的功能。同时，我们也关注灵活的、非性能驱动的格式，例如 CSV 文件。为了在读取 CSV 文件时创造良好而愉快的体验，DuckDB 实现了 CSV 探测器，可以自动检测 CSV 方言选项、列类型，甚至跳过脏数据。探测过程允许用户有效地探索 CSV 文件，而无需提供有关文件格式的任何输入。

用户在存储数据时可以选择多种不同的文件格式。例如，有一些面向性能的二进制格式，例如 Parquet，其中数据以列格式存储、分区为行组并进行高度压缩。然而，Parquet 需要专门的系统来读取和写入这些文件。

另一方面，还有 CSV（逗号分隔值）格式的文件。CSV 文件具有灵活性的优点；它们被构造为文本文件，允许用户使用任何文本编辑器来操作它们，并且几乎任何数据系统都可以读取它们并对其执行查询。

然而，这种灵活性是有代价的。读取 CSV 文件并不是一项简单的任务，因为用户需要大量有关该文件的先验知识。例如，DuckDB 的 CSV 读取器[1]提供超过 25 个配置选项。这些选项包括指定分隔符、引号和转义字符，确定 CSV 文件中的列数，以及确定标题是否存在，同时定义列类型。这会减慢交互式数据探索过程，并使分析新数据集成为一项繁琐且不太有趣的任务。

DuckDB 存在的理由之一是令人愉快且易于使用，因此我们不希望用户必须手动摆弄 CSV 文件和输入选项。手动输入应仅保留用于其 CSV 方言（方言由用于创建该文件的分隔符、引号、转义符和换行符值的组合）选择相当不寻常的文件或用于指定列类型的文件。

自动检测 CSV 选项可能是一个艰巨的过程。不仅有很多选项可供研究，而且它们的组合很容易导致搜索空间爆炸。对于结构不完善的 CSV 文件尤其如此。有些人可能会认为 CSV 文件有一个规范[2]，但事实是，一旦单个系统能够读取有缺陷的文件，“规范”就会发生变化。而且在过去的几个月里，很多人们希望 DuckDB 读取的半损坏的 CSV 文件。

DuckDB 实现了多假设 CSV 探测器[3]，可自动检测方言、标题、日期/时间格式、列类型，并识别要跳过的脏行。我们的最终目标是自动读取任何类似于 CSV 文件的内容， 所有这些都是在读取 CSV 文件时无需产生大量初始成本即可实现的。在前沿版本中，默认情况下，探测器在读取 CSV 文件时运行。请注意探测器将始终优先考虑用户设置的任何选项（例如，如果用户设置 , 作为分隔符，则嗅探器不会尝试任何其他选项，并假设用户输入是正确的）。

这篇博文中将解释当前实现的工作原理，讨论其性能，并提供对接下来的内容的见解！

DuckDB的自动检测

解析CSV文件的过程如下图所示。目前它由五个不同的阶段组成，将在下一节中详细介绍。

概览示例中使用的 CSV 文件如下：

Name, Height, Vegetarian, Birthday
"Pedro", 1.73, False, 30-07-92
... imagine 2048 consistent rows ...
"Mark", 1.72, N/A, 20-09-92

在第一阶段，我们执行方言检测，选择在 CSV 文件中生成最多每行列的候选方言，同时保持一致性（即，整个文件中的列数不会出现显着变化）。在我们的示例中，我们可以观察到，在此阶段之后，探测器成功检测到分隔符、引号、转义符和换行符的必要选项。

第二阶段称为类型检测，涉及识别 CSV 文件中每一列的数据类型。在我们的示例中，我们的探测器识别四种列类型：VARCHAR、DOUBLE、BOOL 和 DATE。

第三步称为标头检测，用于确定我们的文件是否包含标头。如果存在标题，我们使用它来设置列名称；否则，我们会自动生成它们。在我们的示例中，有一个标题，每列的名称都在其中定义。

现在我们的列有了名称，我们进入第四个可选阶段：类型替换。DuckDB 的 CSV 阅读器为用户提供了按名称指定列类型的选项。如果指定了这些类型，我们会将检测到的类型替换为用户的规范。

最后，我们进入最后一个阶段，类型细化。在此阶段，我们分析文件的其他部分，以验证初始类型检测阶段确定的类型的准确性。如有必要，我们会对其进行完善。在我们的示例中，我们可以看到 Vegetarian 列最初被分类为 BOOL。然而经过进一步检查，发现它包含字符串 N/A，导致列类型升级为 VARCHAR 以容纳所有可能的值。

仅对 CSV 文件的连续样本执行自动检测。默认情况下，样本大小为 20,480 个元组（即 10 个 DuckDB 执行块）。这可以通过sample_size选项进行配置，如果用户想要嗅探完整的文件，可以将其设置为-1。由于使用各种选项重复读取相同的数据，并且用户可以扫描整个文件，因此嗅探期间生成的所有 CSV 缓冲区都会被缓存并进行有效管理，以确保高性能。

当然，在非常大的文件上运行 CSV 嗅探器将对整体性能产生巨大影响（请参阅下面的基准测试[4]部分）。在这些情况下，样本量应保持在合理的水平。

接下来的小节中将详细描述每个阶段。

方言检测

在方言检测中，我们识别 CSV 文件的分隔符、引号、转义符和换行符。

我们的分隔符搜索空间由以下分隔符组成：,, |, ;, \t。如果文件在搜索空间之外有分隔符，则必须由用户提供（例如 delim='?'）。我们的引号搜索空间是 ", ' 和 \0，其中 \0 是字符串终止符，表示不存在引号；同样，用户可以在搜索空间之外提供自定义字符（例如，quote='?'）。转义值取决于 quote 选项的值，但总而言之，它们与添加 \ 的引号相同，而且它们也可以由用户提供 (escape='?')。最后，最后一个 检测到的选项是新行分隔符；它们可以是 \r、\n、\r\n 以及所有内容的混合（我见过一个使用混合的真实 CSV 文件）。

默认情况下，方言检测在 24 种不同的方言配置组合上运行。为了确定最有希望的配置，我们计算每个 CSV 元组在每种配置下将生成的列数。将选择导致最多列和最一致行的那一个。

一致行的计算取决于其他用户定义的选项。例如，null_padding 选项将用 NULL 值填充缺失的列。因此，缺少列的行将用 NULL 填充缺少的列。

如果 null_padding 设置为 true，则仍会考虑具有不一致行的 CSV 文件，但将优先考虑最小化填充行出现的配置。如果 null_padding 设置为 false，方言检测器将跳过 CSV 文件开头不一致的行。作为示例，请考虑以下 CSV 文件。

I like my csv files to have notes to make dialect detection harder
I also like commas like this one : ,
A,B,C
1,2,3
4,5,6

在这里，探测器将检测到分隔符设置为,时，第一行有一列，第二行有两列，但其余行有 3 列。因此，如果 null_padding 设置为 false，它仍然会选择 ，作为分隔符候选。得出下表：

A,B,C
1, 2, 3
4, 5, 6

如果 null_padding 设置为 true，则所有行都将被接受，结果如下表：

'I like my csv files to have notes to make dialect detection harder', None, None
'I also like commas like this one : ', None, None
'A', 'B', 'C'
'1', '2', '3'
'4', '5', '6'

如果设置了ignore_errors选项，那么将选择产生最多列和最少不一致行的配置。

类型检测

决定使用的方言后，我们检测每列的类型。我们的类型检测考虑以下类型：SQLNULL、BOOLEAN、BIGINT、DOUBLE、TIME、DATE、TIMESTAMP、VARCHAR。这些类型按特定性排序，这意味着我们首先检查列是否为 SQLNULL；如果不是，如果它是 BOOLEAN，依此类推，直到它只能是 VARCHAR。DuckDB 的类型比默认使用的类型更多。用户还可以通过 auto_type_candidates 选项定义嗅探器应考虑的类型。

在此阶段，类型检测算法将检查第一个数据块（即 2048 个元组）。此过程从文件的第二个有效行（即不是注释）开始。第一行单独存储，不用于类型检测。稍后将检测第一行是否是标题。类型检测运行每列、每值转换试验过程来确定列类型。它从一个唯一的每列数组开始，其中包含要检查的所有类型。它尝试将列的值转换为该类型；如果失败，它会从数组中删除该类型，尝试使用新类型进行转换，然后继续该过程，直到整个块完成。

在此阶段，我们还确定 DATE 和 TIMESTAMP 列的格式。DATE 列考虑以下格式：%m-%d-%Y、%m-%d-%y、%d-%m-Y、%d-%m-%y、%Y-%m-%d 、%y-%m-%d，以及 TIMESTAMP 列的以下内容：%Y-%m-%dT%H:%M:%S.%f、%Y-%m-%d %H:%M :%S.%f、%m-%d-%Y %I:%M:%S %p、%m-%d-%y %I:%M:%S %p、%d-%m -%Y %H:%M:%S、%d-%m-%y %H:%M:%S、%Y-%m-%d %H:%M:%S、%y-% m-%d %H:%M:%S。对于使用此搜索空间之外的格式的列，必须使用 dateformat 和 timestampformat 选项定义它们。

作为示例，让我们考虑以下 CSV 文件。

Name, Age
,
Jack Black, 54
Kyle Gass, 63.2

第一行[姓名，年龄]将单独存储，用于标头检测阶段。第二行 [NULL, NULL] 将允许我们将第一列和第二列转换为 SQLNULL。因此，它们的类型候选数组将相同：[SQLNULL、BOOLEAN、BIGINT、DOUBLE、TIME、DATE、TIMESTAMP、VARCHAR]。

在第三排[Jack Black，54]，事情变得更加有趣。对于“Jack Black”，第 0 列的类型候选数组将排除具有更高特异性的所有值，因为“Jack Black”只能转换为 VARCHAR。第二列无法转换为 SQLNULL 或 BOOLEAN，但它将成功转换为 BIGINT。因此，第二列的候选类型将为 [BIGINT、DOUBLE、TIME、DATE、TIMESTAMP、VARCHAR]。

第四行是[Kyle Gass，63.2]。对于第一列，没有问题，因为它也是有效的 VARCHAR。但是，对于第二列，转换为 BIGINT 将失败，但转换为 DOUBLE 将成功。因此，第二列的新候选类型数组将是 [DOUBLE, TIME, DATE, TIMESTAMP, VARCHAR]。

标头检测

标头检测阶段只是获取 CSV 文件的第一个有效行，并尝试将其转换为我们列中的候选类型。如果存在强制转换不匹配，我们会将该行视为标题；如果没有，我们将第一行视为实际数据并自动生成标题。

在我们前面的示例中，第一行是 [Name, Age]，列候选类型数组是 [VARCHAR] 和 [DOUBLE, TIME, DATE, TIMESTAMP, VARCHAR]。Name 是一个字符串，可以转换为 VARCHAR。Age 也是一个字符串，尝试将其转换为 DOUBLE 将失败。由于转换失败，自动检测算法将第一行视为标题，导致第一列名为 Name，第二列名为 Age。

如果未检测到标题，将使用模式 column${x} 自动生成列名称，其中 x 表示列在 CSV 文件中的位置（从 0 开始的索引）。

类型更换

现在自动检测算法已经发现了标题名称，如果用户指定列类型，则嗅探器检测到的类型将在类型替换阶段被替换。例如，我们可以使用以下命令将 Age 类型替换为 FLOAT：

SELECT * FROM read_csv('greatest_band_in_the_world.csv', types = {'Age': 'FLOAT'})

此阶段是可选的，只有在存在手动定义的类型时才会触发。

类型细化

类型细化阶段执行与类型检测相同的任务；唯一的区别是铸造操作员所处理的数据的粒度，该粒度是出于性能原因而进行调整的。在类型检测期间，我们对每列、每个值进行强制转换检查。

在此阶段，我们过渡到更高效的向量化化算法。验证过程与类型检测中的过程相同，如果转换失败，则类型候选数组中的类型将被消除。

探测有多快？

为了分析运行 DuckDB 自动检测的影响，我们在纽约出租车数据集[5]上执行探测器。该文件由 19 列、10,906,858 个元组组成，大小为 1.72 GB。

探测方言列名称和类型的成本大约是加载数据总成本的 4%。

Name	Time (s)
Sniffing	0.11
Loading	2.43

不同的采样大小

有时 CSV 文件可能具有方言选项或更精细的类型，这些选项仅稍后出现在 CSV 文件中。在这些情况下，sample_size 选项成为用户确保探测器检查足够数据以做出正确决定的重要工具。然而增加sample_size也会导致探测器总运行时间的增加，因为它使用更多的数据来检测所有可能的方言和类型。

下面可以看到通过乘数增加默认样本大小（参见 X 轴）如何影响探测器在 NYC 数据集上的运行时间。正如预期的那样，探测所花费的总时间随着总样本量线性增加。

不同数量的列

影响自动检测的 CSV 文件的另一个主要特征是文件的列数。我们针对包含 10,906,858 个元组的文件中不同数量的 INTEGER 类型列测试探测器。结果如下图所示。我们可以看到，从一列到两列，运行时间急剧增加。这是因为，对于单列，由于缺少分隔符，我们有一个简化的方言检测。对于其他列，正如预期的那样，我们的运行时间有更线性的增加，具体取决于列的数量。

结论与未来工作

如果有不寻常的 CSV 文件并想要查询、清理或规范化它们，DuckDB 已经是可用的最佳解决方案之一。上手非常容易。要使用探测器读取 CSV 文件，可以简单地：

SELECT * FROM 'path/to/csv_file.csv';

DuckDB的CSV自动检测算法是促进CSV文件探索的重要工具。使用默认选项，它对加载和读取 CSV 文件的总成本影响很小。它的主要目标是始终能够读取文件，即使对于定义不明确的文件也能尽力而为。

我们有一个与探测器相关的要点列表，我们希望在未来改进这些点。

• • 高级标头检测。目前，我们通过识别第一个有效行与 CSV 文件其余部分之间的类型不匹配来确定 CSV 是否具有标头。但是，例如，如果 CSV 的所有列均为 VARCHAR 类型，则这可能会生成漏报。我们计划增强标头检测，以执行与标头常用名称的匹配。

• • 添加准确性和速度基准。我们目前实施了许多准确性和回归测试；然而，由于 CSV 固有的灵活性，手动创建测试用例是相当艰巨的。下一步的计划是使用 Pollock 基准[6]实施整个准确性和回归测试套件

• • 改进采样。我们目前对连续数据样本执行自动检测算法。但是，很常见的是，新设置仅在文件的后面引入（例如，引号可能仅在文件的最后 10% 中使用）。因此，能够在文件的不同部分执行探测器可以提高准确性。

• • 多表 CSV 文件。同一 CSV 文件中可以存在多个表，这是将电子表格导出到 CSV 时的常见情况。因此，我们希望能够识别并支持这些。

• • 空字符串检测。我们目前没有适当的算法来识别空字符串的表示。

• • 十进制精度检测。我们还不会自动检测小数精度。这是我们未来要解决的问题。

• • 并行化。尽管 DuckDB 的 CSV Reader 是完全并行化的，但探测器仍然仅限于单个线程。以与 CSV Reader 类似的方式对其进行并行化（将在未来的博客文章中进行描述）将显着增强探测性能并启用全文件探测。

• • 探测器作为一个独立的功能。目前用户可以使用DESCRIBE查询从探测器获取信息，但它仅返回列名和类型。我们的目标是将探测算法公开为一个独立的函数，提供探测器的完整结果。这将允许用户使用完全相同的选项轻松配置文件，而无需重新运行探测器。

这篇关于DuckDB CSV 探测器：自动检测类型和方言详解的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---