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

2025-04-12

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, , ldjsondan ndjson.

Membaca dokumen JSON

Cara term mudah untuk melihat konten file JSON Anda adalah dengan menyediakan URL file ke OPENROWSET fungsi, 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 harus mengatur nilai 0x0b untuk rowterminator. OPENROWSET fungsi akan mengurai JSON dan mengembalikan setiap dokumen dalam format berikut:

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

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.

Read JSON files

Contoh kueri berikut membaca file JSON dan 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 harus menyiapkan kredensial tingkat server untuk masuk sql.

Penggunaan sumber data

Contoh sebelumnya menggunakan jalur lengkap ke file. Sebagai alternatif, Anda dapat membuat sumber data eksternal dengan lokasi yang menunjuk ke folder akar penyimpanan, dan menggunakan sumber data tersebut dan jalur relatif ke file dalam OPENROWSET fungsi:

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 khusus, Anda bisa mengonfigurasi sumber data dengan kredensial yang dibatasi lingkupnya di database.

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

Parse JSON documents

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 ditunjukkan dalam contoh berikut:

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

Nota

Jika dokumen-dokumen ini disimpan sebagai JSON yang dibatasi baris, Anda perlu mengatur FIELDTERMINATOR dan FIELDQUOTE 0x0b. Jika Anda memiliki format JSON standar, Anda perlu mengatur ROWTERMINATOR ke 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_territoriescases) 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 mentransmisikan 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 JSON_VALUE fungsi . Dalam beberapa kasus, OPENJSON mungkin memiliki keuntungan daripada JSON_VALUE:

WITH Dalam klausa, Anda dapat secara eksplisit mengatur alias kolom dan jenis untuk setiap properti. Anda tidak perlu memasukkan fungsi CAST ke setiap kolom dalam daftar SELECT.
OPENJSON might be faster if you are returning a large number of properties. If you are returning just 1-2 properties, the OPENJSON function might be overhead.
Anda harus menggunakan OPENJSON fungsi jika Anda perlu mengurai array dari setiap dokumen, dan menggabungkannya dengan baris induk.

Langkah berikutnya

Artikel berikutnya dalam seri ini akan menunjukkan cara:

Bagikan melalui