在 Azure Synapse Analytics 中使用无服务器 SQL 池查询 JSON 文件

本文介绍如何在 Azure Synapse Analytics 中使用无服务器 SQL 池编写查询。 查询的目标是使用 OPENROWSET 读取 JSON 文件。

• 将多个 JSON 文档存储为 JSON 数组的标准 JSON 文件。
• 行分隔的 JSON 文件，其中 JSON 文档用换行符分隔。 这些类型的文件的常见扩展为 jsonl、ldjson 和 ndjson。

要查看 JSON 文件的内容，最简单的方法是提供 OPENROWSET 函数的文件 URL，指定 csv FORMAT，并为 0x0b 和 fieldterminator 设置值 fieldquote。 如果需要读取行分隔的 JSON 文件，那么这就足够了。 如果有经典 JSON 文件，则需要为 0x0b 设置值 rowterminator。 OPENROWSET 函数将分析 JSON 并返回以下格式的每个文档：

如果文件公开可用，或者你的 Microsoft Entra 标识可以访问该文件，则应使用类似于以下示例所示查询的查询来查看该文件的内容。

以下示例查询读取 JSON 和行分隔的 JSON 文件，并将每个文档作为单独的行返回。

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
go
select top 10 *
from openrowset(
        bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.json',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b',
        rowterminator = '0x0b' --> You need to override rowterminator to read classic JSON
    ) with (doc nvarchar(max)) as rows

上述示例查询中的 JSON 文档包含大量对象。 查询将每个对象作为结果集中的单独行返回。 请确保你可以访问此文件。 如果文件使用 SAS 密钥或自定义标识进行保护，则需要 为 sql 登录设置服务器级凭据。

前面的示例使用文件的完整路径。 作为替代方案，可以创建一个指向存储根文件夹的外部数据源，并使用该数据源和 OPENROWSET 函数中的文件的相对路径。

create external data source covid
with ( location = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases' );
go
select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.jsonl',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
go
select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.json',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b',
        rowterminator = '0x0b' --> You need to override rowterminator to read classic JSON
    ) with (doc nvarchar(max)) as rows

如果数据源受到 SAS 密钥或自定义标识保护，则可以使用数据库范围的凭据配置数据源。

在下述部分中，可以了解如何查询不同类型的 JSON 文件。

上述示例中的查询将每个 JSON 文档作为结果集单独行中的单个字符串返回。 可以使用函数 JSON_VALUE 和分析 OPENJSON JSON 文档中的值，并将其作为关系值返回，如以下示例所示：

示例查询读取包含具有以下结构的文档的 json 文件：

{
    "date_rep":"2020-07-24",
    "day":24,"month":7,"year":2020,
    "cases":13,"deaths":0,
    "countries_and_territories":"Afghanistan",
    "geo_id":"AF",
    "country_territory_code":"AFG",
    "continent_exp":"Asia",
    "load_date":"2020-07-25 00:05:14",
    "iso_country":"AF"
}

以下查询演示如何使用 JSON_VALUE 从 JSON 文档检索标量值（date_rep、 countries_and_territories） cases：

select
    JSON_VALUE(doc, '$.date_rep') AS date_reported,
    JSON_VALUE(doc, '$.countries_and_territories') AS country,
    CAST(JSON_VALUE(doc, '$.deaths') AS INT) as fatal,
    JSON_VALUE(doc, '$.cases') as cases,
    doc
from openrowset(
        bulk 'latest/ecdc_cases.jsonl',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
order by JSON_VALUE(doc, '$.geo_id') desc

从 JSON 文档提取 JSON 属性后，可以定义列别名，并选择性地将文本值强制转换为某种类型。

以下查询使用 OPENJSON。 它将检索在塞尔维亚报告的新冠肺炎统计信息：

select
    *
from openrowset(
        bulk 'latest/ecdc_cases.jsonl',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
    cross apply openjson (doc)
        with (  date_rep datetime2,
                cases int,
                fatal int '$.deaths',
                country varchar(100) '$.countries_and_territories')
where country = 'Serbia'
order by country, date_rep desc;

结果在功能上与使用 JSON_VALUE 函数返回的结果相同。 在某些情况下， OPENJSON 可能具有优势 JSON_VALUE：

• 在 WITH 子句中，可以显式设置每个属性的列别名和类型。 无需在 CAST 列表的每一列中放入 SELECT 函数。
• OPENJSON 如果返回大量属性，则速度可能更快。 如果只返回 1-2 个属性，使用 OPENJSON 函数可能会导致不必要的复杂化。
• 如果需要分析每个文档中的数组，并将其与父行联接，则必须使用该 OPENJSON 函数。

本系列中的下一篇文章将演示如何：

• 查询文件夹和多个文件
• 创建和使用视图