Azure Synapse - 使用 OPENROWSET - FIELDTERMINATOR 值 0x0b 查询 JSON

Question

在 Azure Synapse 无服务器 SQL 池中，在通过 CSV 解析器使用 OPENROWSET 读取 JSON 文档时，我们必须将 FIELDTERMINATOR 和 FIELDQUOTE 指定为“0x0b”(垂直制表符)。有人可以帮助理解原因吗？

如果它是单行 JSON (JSONL)，则文件中的每一行都是一个 JSON 文档，我希望整行被读取为单个值，并且不需要指定 FIELDTERMINATOR 或 FIELDQUOTE。

我们是否只是覆盖字段终止符的默认值逗号 (,) 和字段的默认值双引号 (")？如果是这样，'0x0b' 的意义是什么？

文档在这里 - https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-json-files

文档中的示例代码如下

select top 10 *
from openrowset(
        bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.jsonl',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows

Answer 1

是的，我们正在覆盖 csv 默认参数以与 json 兼容。这些参数使 Synapse 读取文本文件中的行并正确处理 json/jsonl 内容。以下是一些背景原因:

OPENROWSET 是为了读取多种文件格式的文件而创建的。 Synapse Serverless 目前支持 csv、parquet 和 delta(Delta Lake)。 OPENROWSET 不支持 json 作为单独的格式。由于json和csv都是类似的文本文件，因此使用带有合适参数的csv格式来读取json。实际上，在这种情况下，OPENROWSET 本身并不将文件视为 json 或 jsonl。它将文件视为具有一列多行的 csv/文本文件。接下来的步骤，例如JSON_VALUE 或 OPENJSON，使 Synapse 将文本视为 json。

当您指定 format='csv' 时，fieldterminator 和 fieldquote 的默认值是逗号和双引号，正如您所说的那样。读取 json 时这些值会出现问题，因为您的 json 可以包含这些字符用于其他目的。这就是为什么在读取 json 文件时，您需要通过将字段终止符和字段引号设置为 json 不包含的某个值来使 openrowset 忽略它们。不幸的是，csv 格式没有其他方法可以关闭它们。

字符 0x0b 是一个垂直制表符，现在很少使用。因此，通常的做法是使用此字符使 openrowset 忽略这些参数。您还可以使用其他字符，只要它们未在您的 json 中使用即可。有关 VT 历史的更多信息可以在这里找到:What is a vertical tab?

您可以尝试在 json 中包含 0x0b 字符，并看到它会引发 Synapse 错误“由于 CSV 数据文件中的列值无效，批量加载失败”。