在 Azure Synapse Analytics 中使用無伺服器 SQL 集區查詢 Parquet 和 JSON 檔案中的巢狀類型

在本文中，您將瞭解如何在 Azure Synapse Analytics 中使用無伺服器 SQL 集區撰寫查詢。 查詢會讀取 Parquet 巢狀類型。 巢狀類型是代表物件或陣列的複雜結構。 巢狀類型可以儲存在：

• Parquet，您可以在其中擁有包含數位和物件的多個複雜數據行。
• 階層式 JSON 檔案，您可以在其中將複雜的 JSON 檔讀取為單一數據行。
• Azure Cosmos DB 集合（目前處於封閉式公開預覽狀態），其中每個檔都可以包含複雜的巢狀屬性。

無伺服器 SQL 集區會將所有巢狀類型格式化為 JSON 物件和陣列。 因此，您可以使用 JSON 函 式擷取或修改複雜的物件，或使用 OPENJSON 函式剖析 JSON 數據。

以下是從 COVID-19 Open Research Dataset 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 檔案，並示範如何呈現巢狀數據行的專案。 有兩種方式可以參考巢狀值：

• 藉由在類型規格之後指定巢狀值路徑表達式。
• 藉由使用 do “.” 將資料行名稱格式化為巢狀路徑，以參考字段。

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 檔案。