在 Azure Synapse Analytics 中使用无服务器 SQL 池查询 Parquet 和 JSON 文件中的嵌套类型

本文介绍如何在 Azure Synapse Analytics 中使用无服务器 SQL 池编写查询。 此查询将读取 Parquet 嵌套类型。 嵌套类型是表示对象或数组的复杂结构。 嵌套类型可以存储在：

• Parquet，其中可以有多个包含数组和对象的复杂列。
• 分层 JSON 文件，可以在其中将复杂的 JSON 文档作为单列读取。
• Azure Cosmos DB 集合（目前处于封闭的公共预览阶段），其中每个文档都可以包含复杂的嵌套属性。

无服务器 SQL 池可将所有嵌套类型格式化为 JSON 对象和数组。 因此，你可以使用 JSON 函数提取或修改复杂对象，或者使用 OPENJSON 函数分析 JSON 数据。

下面是一个查询示例，该查询从包含嵌套对象的 COVID-19 开放式研究数据集 JSON 文件中提取标量和对象值：

SELECT
    title = JSON_VALUE(doc, '$.metadata.title'),
    first_author = JSON_QUERY(doc, '$.metadata.authors[0]'),
    first_author_name = JSON_VALUE(doc, '$.metadata.authors[0].first'),
    complex_object = doc
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/covid19temp/comm_use_subset/pdf_json/000b7d1517ceebb34e1e3e817695b6de03e2fa78.json',
        FORMAT='CSV', FIELDTERMINATOR ='0x0b', FIELDQUOTE = '0x0b', ROWTERMINATOR = '0x0b'
    )
    WITH ( doc varchar(MAX) ) AS docs;

JSON_VALUE 函数从指定路径处的字段返回一个标量值。 JSON_QUERY 函数从指定路径处的字段返回一个格式化为 JSON 的对象。

先决条件

第一步是创建将在其中创建数据源的数据库。 然后通过对数据库运行安装脚本来初始化这些对象。 此安装脚本将创建数据源、数据库范围的凭据以及在这些示例中使用的外部文件格式。

投影嵌套数据或重复数据

Parquet 文件可以包含多个具有复杂类型的列。 这些列中的值格式化为 JSON 文本，并作为 VARCHAR 列返回。 以下查询读取 structExample.parquet 文件，并演示如何读取嵌套列的值：

SELECT
    DateStruct, TimeStruct, TimestampStruct, DecimalStruct, FloatStruct
FROM
    OPENROWSET(
        BULK 'parquet/nested/structExample.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    )
    WITH (
        DateStruct VARCHAR(8000),
        TimeStruct VARCHAR(8000),
        TimestampStruct VARCHAR(8000),
        DecimalStruct VARCHAR(8000),
        FloatStruct VARCHAR(8000)
    ) AS [r];

此查询返回以下结果。 每个嵌套对象的内容均以 JSON 文本的形式返回。

以下查询读取 justSimpleArray.parquet 文件。 它将投影 Parquet 文件中的所有列（包括嵌套数据和重复数据）。

SELECT
    SimpleArray
FROM
    OPENROWSET(
        BULK 'parquet/nested/justSimpleArray.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) AS [r];

此查询将返回以下结果：

从嵌套对象列中读取属性

使用 JSON_VALUE 函数可以从格式化为 JSON 文本的列中返回值：

SELECT
    title = JSON_VALUE(complex_column, '$.metadata.title'),
    first_author_name = JSON_VALUE(complex_column, '$.metadata.authors[0].first'),
    body_text = JSON_VALUE(complex_column, '$.body_text.text'),
    complex_column
FROM
    OPENROWSET( BULK 'https://azureopendatastorage.blob.core.windows.net/covid19temp/comm_use_subset/pdf_json/000b7d1517ceebb34e1e3e817695b6de03e2fa78.json',
                FORMAT='CSV', FIELDTERMINATOR ='0x0b', FIELDQUOTE = '0x0b', ROWTERMINATOR = '0x0b' ) WITH ( complex_column varchar(MAX) ) AS docs;

结果如下表所示：

与 JSON 文件（大多数情况下返回包含复杂 JSON 对象的单个列）不同，Parquet 文件可以具有多个复杂列。 你可以通过对每个列使用 JSON_VALUE 函数来读取嵌套列的属性。 OPENROWSET 允许直接在 WITH 子句中指定嵌套属性的路径。 你可以将路径设置为列的名称，也可以在列类型后面添加 JSON 路径表达式。

下面的查询将读取 structExample.parquet 文件，并演示如何呈现嵌套列的元素。 可通过两种方式引用嵌套值：

• 在类型规范后面指定嵌套值路径表达式。
• 通过使用点“.”引用字段，将列名称格式化为嵌套路径。

SELECT
    *
FROM
    OPENROWSET(
        BULK 'parquet/nested/structExample.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    )
    WITH (
        [DateValue] DATE '$.DateStruct.Date',
        [TimeStruct.Time] TIME,
        [TimestampStruct.Timestamp] DATETIME2,
        DecimalValue DECIMAL(18, 5) '$.DecimalStruct.Decimal',
        [FloatStruct.Float] FLOAT
    ) AS [r];

访问重复列中的元素

下面的查询将读取 justSimpleArray.parquet 文件，并使用 JSON_VALUE 从重复列（如数组或映射）中检索标量元素：

SELECT
    *,
    JSON_VALUE(SimpleArray, '$[0]') AS FirstElement,
    JSON_VALUE(SimpleArray, '$[1]') AS SecondElement,
    JSON_VALUE(SimpleArray, '$[2]') AS ThirdElement
FROM
    OPENROWSET(
        BULK 'parquet/nested/justSimpleArray.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) AS [r];

结果如下：

访问复杂列中的子对象

下面的查询将读取 mapExample.parquet 文件，并使用 JSON_QUERY 从重复列（如数组或映射）中检索非标量元素：

SELECT
    MapOfPersons,
    JSON_QUERY(MapOfPersons, '$."John Doe"') AS [John]
FROM
    OPENROWSET(
        BULK 'parquet/nested/mapExample.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) AS [r];

你还可以在 WITH 子句中显式引用要返回的列：

SELECT DocId,
    MapOfPersons,
    JSON_QUERY(MapOfPersons, '$."John Doe"') AS [John]
FROM
    OPENROWSET(
        BULK 'parquet/nested/mapExample.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) 
    WITH (DocId bigint, MapOfPersons VARCHAR(max)) AS [r];

MapOfPersons 结构作为 VARCHAR 列返回，并格式化为 JSON 字符串。

投影重复列中的值

如果某些列中有标量值数组（例如 [1,2,3]），则可以使用以下脚本轻松扩展它们并将其与主行联接：

SELECT
    SimpleArray, Element
FROM
    OPENROWSET(
        BULK 'parquet/nested/justSimpleArray.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) AS arrays
    CROSS APPLY OPENJSON (SimpleArray) WITH (Element int '$') as array_values

后续步骤

下一篇文章将介绍如何查询 JSON 文件。