在 Azure Synapse 分析中使用無伺服器 SQL 池查詢 JSON 文件

Tip

Microsoft Fabric Data Warehouse 是一個企業規模的關聯式倉庫，建立在資料湖基礎上，具備未來準備架構、內建 AI 及新功能。 如果你是資料倉儲新手，建議先從Fabric Data Warehouse開始。 現有的 專用 SQL 工作負載可升級至 Fabric，以取得資料科學、即時分析與報告等多項新功能。

在本文中，你將學習如何在 Azure Synapse Analytics 中使用無伺服器 SQL 池來撰寫查詢。 查詢的目標是使用 OPENROWSET 讀取 JSON 檔案。

  • 標準的 JSON 檔案，將多個 JSON 文件儲存成 JSON 陣列。
  • 行分隔的 JSON 檔案，其中 JSON 文件以換行字元分隔。 這類檔案常見的副檔名有 jsonl、 ldjson、 ndjson和 。

閱讀 JSON 文件

查看 JSON 檔案內容最簡單的方法是提供檔案的 URLOPENROWSET給函式，指定為 csvFORMAT，並設定0x0b和fieldterminator的值fieldquote。 如果你需要讀取行分隔的 JSON 檔案，那這就足夠了。 如果你有傳統格式的 JSON 檔案，你需要將值設定為 0x0b 以應用於 rowterminator。 OPENROWSET 函式會解析 JSON，並以以下格式回傳每份文件：

文件
{“date_rep”：“2020-07-24”， “day”：24， “month”：7， “year”：2020， “cases”：3， “deaths”：0， “geo_id”：“AF”}
{“date_rep”：“2020-07-25”， “day”：25， “month”：7， “year”：2020， “cases”：7， “deaths”：0， “geo_id”：“AF”}
{“date_rep”：“2020-07-26”， “day”：26， “month”：7， “year”：2020， “cases”：4， “deaths”：0， “geo_id”：“AF”}
{“date_rep”：“2020-07-27”， “day”：27， “month”：7， “year”：2020， “cases”：8， “deaths”：0， “geo_id”：“AF”}

如果該檔案是公開的，或你的 Microsoft Entra 身份能存取此檔案，你應該會使用類似以下範例的查詢方式查看檔案內容。

讀取 JSON 檔案

以下範例查詢讀取 JSON 及行分隔 JSON 檔案，並將每份文件以獨立列回傳。

select top 10 *
from openrowset(
        bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.jsonl',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
go
select top 10 *
from openrowset(
        bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.json',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b',
        rowterminator = '0x0b' --> You need to override rowterminator to read classic JSON
    ) with (doc nvarchar(max)) as rows

前述範例查詢中的 JSON 文件包含一個物件陣列。 查詢會將每個物件作為結果集中的單獨資料列回傳。 確保你能存取這個檔案。 如果你的檔案是用 SAS 金鑰或自訂身份保護的，你需要設定 伺服器層級的憑證來登入 sql。

資料來源使用

前一個範例使用檔案的完整路徑。 另外，你可以建立一個指向儲存根資料夾的外部資料來源，然後在函式中使用該資料來源及檔案的相對路徑OPENROWSET。

create external data source covid
with ( location = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases' );
go
select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.jsonl',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
go
select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.json',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b',
        rowterminator = '0x0b' --> You need to override rowterminator to read classic JSON
    ) with (doc nvarchar(max)) as rows

如果資料來源以 SAS 密鑰或自訂身份保護，你可以設定 資料庫範圍憑證。

在接下來的章節中，你可以了解如何查詢各種類型的 JSON 檔案。

解析 JSON 文件

前述範例中的查詢會將每個 JSON 文件以單一字串的形式返回，並置於結果集的另一列。 你可以用函式 JSON_VALUE 和 OPENJSON 來解析 JSON 文件中的數值，並以關聯式值回傳，如下範例所示：

date_rep 案例 geo_id
2020-07-24 3 自動對焦
2020-07-25 7 自動對焦
2020-07-26 4 自動對焦
2020-07-27 8 自動對焦

範例 JSON 文件

查詢範例讀取包含以下結構文件的 json 檔案：

{
    "date_rep":"2020-07-24",
    "day":24,"month":7,"year":2020,
    "cases":13,"deaths":0,
    "countries_and_territories":"Afghanistan",
    "geo_id":"AF",
    "country_territory_code":"AFG",
    "continent_exp":"Asia",
    "load_date":"2020-07-25 00:05:14",
    "iso_country":"AF"
}

Note

如果這些文件是以行分隔 JSON 儲存，你需要設定 FIELDTERMINATOR 和 FIELDQUOTE 到 0x0b。 如果你有標準 JSON 格式，就需要設 ROWTERMINATOR 為 0x0b。

使用 JSON_VALUE 查詢 JSON 檔案

以下查詢說明如何使用 JSON_VALUE 從 JSON 文件中取得純量值（date_rep， countries_and_territories， cases）：

select
    JSON_VALUE(doc, '$.date_rep') AS date_reported,
    JSON_VALUE(doc, '$.countries_and_territories') AS country,
    CAST(JSON_VALUE(doc, '$.deaths') AS INT) as fatal,
    JSON_VALUE(doc, '$.cases') as cases,
    doc
from openrowset(
        bulk 'latest/ecdc_cases.jsonl',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
order by JSON_VALUE(doc, '$.geo_id') desc

一旦你從 JSON 文件中提取 JSON 屬性，就可以定義欄位別名，並可選擇將文字值鑄造成某種類型。

使用 OPENJSON 查詢 JSON 檔案

以下查詢使用 OPENJSON。 它將取得塞爾維亞報告的 COVID 統計數據：

select
    *
from openrowset(
        bulk 'latest/ecdc_cases.jsonl',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
    cross apply openjson (doc)
        with (  date_rep datetime2,
                cases int,
                fatal int '$.deaths',
                country varchar(100) '$.countries_and_territories')
where country = 'Serbia'
order by country, date_rep desc;

結果在功能上與使用該 JSON_VALUE 函數回傳的結果相同。 在某些情況下，可能 OPENJSON 比以下情況有優勢 JSON_VALUE：

  • 在這 WITH 句子裡，你可以明確設定每個屬性的欄位別名和類型。 你不需要把CAST 函式放在SELECT 清單的每一欄。
  • OPENJSON 如果你退回大量房產，可能會更快。 如果你只回傳 1-2 個屬性， OPENJSON 函式可能會造成額外負擔。
  • 如果你需要解析每個文件的陣列，並與父列合併，必須使用這個 OPENJSON 函式。

下一步

本系列接下來的文章將示範如何：

  • Last updated on