共用方式為


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

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

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

讀取 JSON 文件

查看 JSON 檔案內容最簡單的方式是將檔案 URL 提供給 OPENROWSET 函式並指定 csv FORMAT,並且針對 fieldterminatorfieldquote設定 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_VALUEOPENJSON 函式來剖析 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,您必須將 FIELDTERMINATORFIELDQUOTE 設定為0x0b。 如果您有標準 JSON 格式,您需要將 ROWTERMINATOR 設定為 0x0b。

使用 JSON_VALUE 查詢 JSON 檔案

下列查詢示範如何使用 JSON_VALUE,從 JSON 文件中擷取純量值 (date_repcountries_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 函式。

下一步

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