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

發行項
06/01/2023

在本文中，您將瞭解如何使用 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
生態流行病學補充資訊...	朱利安	- 圖 S1：種系發生...	`{ "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 檔案，並顯示如何呈現巢狀資料行的元素。有兩種方式可參考巢狀值：

藉由在類型規格之後指定巢狀值路徑運算式。
藉由使用 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];

結果如下︰

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