Mengkueri file JSON menggunakan kumpulan SQL tanpa server di Azure Synapse Analytics

Artikel
04/27/2023

Dalam artikel ini, Anda akan mempelajari cara menulis kueri menggunakan kumpulan SQL tanpa server di Azure Synapse Analytics. Tujuan kueri adalah membaca file JSON menggunakan OPENROWSET.

File JSON standar di mana beberapa dokumen JSON disimpan sebagai array JSON.
File JSON yang dibatasi garis, di mana dokumen JSON dipisahkan dengan karakter baris baru. Ekstensi umum untuk jenis file ini adalah jsonl, ldjson, dan ndjson.

Membaca dokumen JSON

Cara termudah untuk melihat konten file JSON Anda adalah dengan memberikan URL file ke fungsi OPENROWSET, menentukan csv FORMAT, dan mengatur nilai 0x0b untuk fieldterminator dan fieldquote. Jika Anda perlu membaca file JSON yang dibatasi baris, maka ini sudah cukup. Jika Anda memiliki file JSON klasik, Anda perlu menetapkan nilai 0x0b untuk rowterminator. Fungsi OPENROWSET akan mengurai JSON dan mengembalikan setiap dokumen dalam format berikut:

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

Jika file tersedia untuk umum, atau jika identitas Microsoft Entra Anda dapat mengakses file ini, Anda akan melihat konten file menggunakan kueri seperti yang diperlihatkan dalam contoh berikut.

Membaca file JSON

Contoh kueri berikut membaca JSON dan file JSON yang dibatasi baris, dan mengembalikan setiap dokumen sebagai baris terpisah.

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

Dokumen JSON dalam kueri sampel sebelumnya menyertakan array objek. Kueri mengembalikan setiap objek sebagai baris terpisah dalam kumpulan hasil. Pastikan Anda bisa mengakses file ini. Jika file Anda dilindungi dengan kunci SAS atau identitas kustom, Anda perlu menyiapkan info masuk tingkat server untuk masuk ke sql.

Penggunaan sumber data

Contoh sebelumnya menggunakan jalur lengkap ke file. Sebagai alternatif, Anda dapat membuat sumber data eksternal dengan lokasi yang mengarah ke folder akar penyimpanan, dan menggunakan sumber data tersebut dan jalur relatif ke file dalam fungsi 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

Jika sumber data dilindungi dengan kunci SAS atau identitas kustom, Anda bisa mengonfigurasi sumber data dengan info masuk lingkup database.

Di bagian berikut, Anda bisa melihat cara mengkueri berbagai jenis file JSON.

Mengurai dokumen JSON

Kueri dalam contoh sebelumnya mengembalikan setiap dokumen JSON sebagai string tunggal dalam baris terpisah dari kumpulan hasil. Anda dapat menggunakan fungsi JSON_VALUE dan OPENJSON untuk mengurai nilai dalam dokumen JSON dan mengembalikannya sebagai nilai relasional, seperti yang diperlihatkan dalam contoh berikut:

date_rep	kasus	geo_id
24-07-2020	3	AF
25-07-2020	7	AF
26-07-2020	4	AF
27-07-2020	8	AF

Contoh dokumen JSON

Contoh kueri membaca file json yang berisi dokumen dengan struktur berikut:

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

Catatan

Jika dokumen-dokumen ini disimpan sebagai JSON yang dibatasi baris, Anda perlu mengatur FIELDTERMINATOR dan FIELDQUOTE menjadi 0x0b. Jika Anda memiliki format JSON standar, Anda perlu mengatur ROWTERMINATOR menjadi 0x0b.

Kueri file JSON menggunakan JSON_VALUE

Kueri di bawah ini memperlihatkan kepada Anda cara menggunakan JSON_VALUE untuk mengambil nilai skalar (date_rep, countries_and_territories, cases) dari dokumen JSON:

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

Setelah Anda mengekstrak properti JSON dari dokumen JSON, Anda dapat menentukan alias kolom dan secara opsional melemparkan nilai tekstual ke beberapa jenis.

Kueri file JSON menggunakan OPENJSON

Kueri berikut menggunakan OPENJSON. Ini akan mengambil statistik COVID yang dilaporkan di Serbia:

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;

Hasilnya secara fungsional sama dengan hasil yang dikembalikan menggunakan fungsi JSON_VALUE. Dalam beberapa kasus, OPENJSON mungkin memiliki keuntungan atas JSON_VALUE:

Dalam klausa WITH Anda dapat secara eksplisit mengatur alias kolom dan jenis untuk setiap properti. Anda tidak perlu meletakkan fungsi CAST di setiap kolom dalam daftar SELECT.
OPENJSON mungkin lebih cepat jika Anda mengembalikan sejumlah besar properti. Jika Anda mengembalikan hanya 1-2 properti, fungsi OPENJSON mungkin berlebihan.
Anda harus menggunakan fungsi OPENJSON jika Anda perlu mengurai array dari setiap dokumen, dan menggabungkannya dengan baris induk.

Langkah berikutnya

Artikel berikutnya dalam seri ini akan menunjukkan cara: