在 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 文件。