在 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 的对象。

重要

此示例使用 COVID-19 开放式研究数据集中的文件。 在此处查看许可证和数据结构

先决条件

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

投影嵌套数据或重复数据

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 文本的形式返回。

DateStruct TimeStruct TimestampStruct DecimalStruct FloatStruct
{"Date":"2009-04-25"} {"Time":"20:51:54.3598000"} {"Timestamp":"5501-04-08 12:13:57.4821000"} {"Decimal":11143412.25350} {"Float":0.5}
{"Date":"1916-04-29"} {"Time":"00:16:04.6778000"} {"Timestamp":"1990-06-30 20:50:52.6828000"} {"Decimal":1963545.62800} {"Float":-2.125}

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

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

此查询将返回以下结果:

SimpleArray
[11,12,13]
[21,22,23]

从嵌套对象列中读取属性

使用 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;

结果如下表所示:

title first_author_name body_text complex_column
Supplementary Information An eco-epidemiolo... Julien - Figure S1 : Phylogeny of... { "paper_id": "000b7d1517ceebb34e1e3e817695b6de03e2fa78", "metadata": { "title": "Supplementary Information An eco-epidemiological study of Morbilli-related paramyxovirus infection in Madagascar bats reveals host-switching as the dominant macro-evolutionary mechanism", "authors": [ { "first": "Julien"

与 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];

结果如下:

SimpleArray FirstElement SecondElement ThirdElement
[11,12,13] 11 12 13
[21,22,23] 21 22 23

访问复杂列中的子对象

下面的查询将读取 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 文件