使用 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 |
---|---|---|---|
生態流行病學補充資訊... | 朱利安 | - 圖 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 檔案。