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

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

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

读取 JSON 文档

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

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

读取 JSON 文件

以下示例查询读取 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 文档作为结果集单独行中的单个字符串返回。 可以使用函数 JSON_VALUE 和分析 OPENJSON JSON 文档中的值，并将其作为关系值返回，如以下示例所示：

示例 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 文件

以下查询演示如何使用 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 查询 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 函数。

后续步骤

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

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