在 Azure Synapse Analytics 中使用無伺服器 SQL 集區來查詢 JSON 檔案

文章
03/05/2024

在本文中，您將了解如何使用 Azure Synapse Analytics 中的無伺服器 SQL 集區來編寫查詢。查詢的目標是要使用 OPENROWSET讀取 JSON 檔案。

將多個 JSON 文件儲存為 JSON 陣列的標準 JSON 檔案。
以行分隔的 JSON 檔案，其中 JSON 文件會以新行字元分隔。這些檔案類型的常見延伸模組為 jsonl、ldjson 和 ndjson。

讀取 JSON 文件

查看 JSON 檔案內容最簡單的方式是將檔案 URL 提供給 OPENROWSET 函式並指定 csv FORMAT，並且針對 fieldterminator 和 fieldquote設定 0x0b 值。如果您需要讀取以行分隔的 JSON 檔案，這些已足夠。如果您有傳統 JSON 檔案，則需要針對 rowterminator 設定 0x0b 值。 OPENROWSET 函式會剖析 JSON，並以下列格式傳回每個文件：

doc
{"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	AF
2020-07-25	7	AF
2020-07-26	4	AF
2020-07-27	8	AF

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"
}

注意

如果這些文件儲存為以行分隔的 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 函式。

下一步

本系列的下一篇文章將示範如何：

分享方式：