原文：DuckDB’s CSV Sniffer: Automatic Detection of Types and Dialects – DuckDB^[1]

翻译：Gemini Pro

校对：alitrack

日期：2023 年 10 月 27 日

作者: 佩德罗·奥兰达

DuckDB 的 CSV 嗅探器：自动检测类型和方言

探索DuckDB：DuckDB 的 CSV 嗅探器之自动检测类型和方言 — ducktetive

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

在存储数据时，用户可以选择许多不同的文件格式。例如，有面向性能的二进制格式，如 Parquet，其中数据以列式格式存储，划分为行组，并经过大量压缩。然而，Parquet 以其刚性而闻名，需要专门的系统来读写这些文件。

另一方面，还有采用 CSV（逗号分隔值）格式的文件，我喜欢称之为“数据伍德斯托克”。CSV 文件具有灵活性的优势；它们被构造为文本文件，允许用户使用任何文本编辑器对其进行操作，几乎任何数据系统都可以读取它们并对其执行查询。

然而，这种灵活性是有代价的。读取 CSV 文件并非一项简单的任务，因为用户需要大量关于该文件的先验知识。例如，DuckDB 的 CSV 读取器^[2]提供了 25 个以上的配置选项。我发现人们倾向于认为，如果我没有在每次发布中至少引入三个新选项，我就没有努力工作。开玩笑的。这些选项包括指定分隔符、引号和转义字符，确定 CSV 文件中的列数，以及标识是否存在标题，同时定义列类型。这可能会减慢交互式数据探索过程，并使分析新数据集成为一项繁琐且不那么愉快的工作。

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

自动检测 CSV 选项可能是一个艰巨的过程。不仅要调查许多选项，而且它们的组合很容易导致搜索空间爆炸。对于结构不佳的 CSV 文件尤其如此。有些人可能会争辩说 CSV 文件有一个规范^[3]，但事实是，只要有一个系统能够读取有缺陷的文件，“规范”就会发生变化。而且，天哪，在过去的几个月里，我遇到了很多半损坏的 CSV 文件，人们希望 DuckDB 能够读取这些文件。

DuckDB 实现了一个多假设 CSV 嗅探器^[4]，可以自动检测方言、标题、日期/时间格式、列类型，并标识要跳过的脏行。我们的最终目标是自动读取任何类似 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 嗅探器会对整体性能产生巨大影响（请参阅下面的基准部分）。在这些情况下，应将样本大小保持在合理水平。

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

方言检测

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

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

默认情况下，方言检测在 24 种不同的方言配置组合上运行。为了确定最有希望的配置，我们计算每个 CSV 元组在这些配置中的每一个下会生成多少列。结果产生最多列且行最一致的那个将被选中。

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

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

我喜欢我的 csv 文件带有注释，以便让方言检测变得更难
我也喜欢这样的逗号：,
A,B,C
1,2,3
4,5,6

在这里，嗅探器会检测到，当分隔符设置为 , 时，第一行有一列，第二行有两列，但其余行有 3 列。因此，如果 null_padding 设置为 false，它仍会选择 , 作为分隔符候选，假设最上面的行是脏注释。（相信我，CSV 注释是一件大事！）。导致以下表格：

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

第一行 [Name, Age] 将单独存储以进行标题检测阶段。第二行 [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 的自动检测的影响，我们在 NYC 出租车数据集^[5] 上执行嗅探器。该文件包含 19 列、10,906,858 个元组，大小为 1.72 GB。

嗅探方言列名和类型的成本约占加载数据总成本的 4%。

名称	时间 (秒)
嗅探	0.11
加载	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';

1. 高级标题检测。我们目前通过识别第一行有效行与 CSV 文件其余部分之间的类型不匹配来确定 CSV 是否具有标题。但是，如果 CSV 的所有列都是 VARCHAR 类型，则可能会产生误报。我们计划增强我们的标题检测，以与常用的标题名称进行匹配。
2. 添加准确性和速度基准。我们目前实施了许多准确性和回归测试；然而，由于 CSV 固有的灵活性，手动创建测试用例非常艰巨。未来的计划是使用 Pollock Benchmark^[6] 实现整个准确性和回归测试套件
3. 改进的采样。我们目前对数据顺序样本执行自动检测算法。但是，新的设置通常只在文件后面引入（例如，引号可能只在文件的最后 10% 中使用）。因此，能够在文件不同部分执行嗅探器可以提高准确性。
4. 多表 CSV 文件。多个表可以存在于同一个 CSV 文件中，这是将电子表格导出为 CSV 时常见的场景。因此，我们希望能够识别和支持这些。
5. 空字符串检测。我们目前没有算法来识别空字符串的表示。
6. 小数精度检测。我们也还没有自动检测小数精度。这是我们未来要解决的问题。
7. 并行化。尽管 DuckDB 的 CSV 读取器是完全并行化的，但嗅探器仍然仅限于单个线程。以类似于 CSV 读取器所做的方式对其进行并行化（描述将在未来的博客文章中提供）将显着提高嗅探性能并实现全文件嗅探。
8. 嗅探器作为独立函数。目前，用户可以使用 DESCRIBE 查询从嗅探器获取信息，但它只返回列名和类型。我们的目标是将嗅探算法公开为一个独立函数，提供嗅探器的完整结果。这将允许用户使用完全相同的选项轻松配置文件，而无需重新运行嗅探器。

引用链接

[1] DuckDB’s CSV Sniffer: Automatic Detection of Types and Dialects – DuckDB: https://duckdb.org/2023/10/27/csv-sniffer.html
[2] DuckDB 的 CSV 读取器: https://duckdb.org/docs/archive/0.9.1/data/csv/overview
[3] 规范: https://datatracker.ietf.org/doc/html/rfc4180
[4] 多假设 CSV 嗅探器: https://hannes.muehleisen.org/publications/ssdbm2017-muehleisen-csvs.pdf
[5] NYC 出租车数据集: https://www.kaggle.com/datasets/elemento/nyc-yellow-taxi-trip-data/
[6] Pollock Benchmark: https://www.vldb.org/pvldb/vol16/p1870-vitagliano.pdf