Azure Synapse Analytics'te sunucusuz SQL havuzunu kullanarak JSON dosyalarını sorgulama

Bu makalede, Azure Synapse Analytics'te sunucusuz SQL havuzunu kullanarak sorgu yazmayı öğreneceksiniz. Sorgunun amacı , OPENROWSET kullanarak JSON dosyalarını okumaktır.

Birden çok JSON belgesinin JSON dizisi olarak depolandığı standart JSON dosyaları.
Satırla ayrılmış JSON dosyaları; burada JSON belgeleri yeni satır karakteriyle ayrılır. Bu tür dosyalar için yaygın uzantılar , ldjsonve ndjson'dirjsonl.

JSON belgelerini okuma

JSON dosyanızın içeriğini görmenin en kolay yolu dosya URL'sini OPENROWSET işlevine sağlamak, csv FORMAT belirtmek ve fieldterminator ile fieldquote için değerleri 0x0b ayarlamaktır. Satırla ayrılmış JSON dosyalarını okumanız gerekiyorsa bu yeterlidir. Klasik JSON dosyanız varsa, 0x0b değerlerini rowterminator olarak ayarlamanız gerekir. OPENROWSET işlevi JSON'ı ayrıştıracak ve her belgeyi aşağıdaki biçimde döndürecektir:

Doktor
{"date_rep":"2020-07-24","day":24,"month":7,"year":2020,"cases":3,"deaths":0,"geo_id":"AF"}
{"tarih_rpr":"2020-07-25","gün":25,"ay":7,"yıl":2020,"vakalar":7,"ölümler":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"}

Dosya genel kullanıma açıksa veya Microsoft Entra kimliğiniz bu dosyaya erişebiliyorsa, aşağıdaki örneklerde gösterildiği gibi sorguyu kullanarak dosyanın içeriğini görmeniz gerekir.

JSON dosyalarını okuma

Aşağıdaki örnek sorgu JSON ve satırla ayrılmış JSON dosyalarını okur ve her belgeyi ayrı bir satır olarak döndürür.

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

Yukarıdaki örnek sorgudaki JSON belgesi bir nesne dizisi içerir. Sorgu, her nesneyi sonuç kümesinde ayrı bir satır olarak döndürür. Bu dosyaya erişebildiğinizden emin olun. Dosyanız SAS anahtarı veya özel kimlikle korunuyorsa , SQL oturum açma bilgileri için sunucu düzeyinde kimlik bilgilerini ayarlamanız gerekir.

Veri kaynağı kullanımı

Önceki örnek dosyanın tam yolunu kullanır. Alternatif olarak, depolamanın kök klasörüne işaret eden konumuyla bir dış veri kaynağı oluşturabilir ve bu veri kaynağını ve işlevdeki OPENROWSET dosyanın göreli yolunu kullanabilirsiniz:

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

Bir veri kaynağı SAS anahtarı veya özel kimlikle korunuyorsa, veri kaynağını veritabanı kapsamlı kimlik bilgileriyle yapılandırabilirsiniz.

Aşağıdaki bölümlerde, çeşitli JSON dosyası türlerini sorgulamayı görebilirsiniz.

JSON belgelerini ayrıştırma

Önceki örneklerdeki sorgular, her JSON belgesini sonuç kümesinin ayrı bir satırında tek bir dize olarak döndürür. aşağıdaki örnekte gösterildiği gibi, JSON belgelerindeki değerleri ayrıştırmak ve ilişkisel değerler olarak döndürmek için ve işlevlerini JSON_VALUEOPENJSON kullanabilirsiniz:

tarih_temsel	vakalar	coğrafi_kimlik
2020-07-24	3	AF
2020-07-25	7	AF
2020-07-26	4	AF
2020-07-27	8	AF

Örnek JSON belgesi

Sorgu örnekleri, aşağıdaki yapıya sahip belgeler içeren json dosyalarını okur:

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

Uyarı

Eğer bu belgeler satırla sınırlandırılmış JSON olarak depolanıyorsa, FIELDTERMINATOR ve FIELDQUOTE'i 0x0b olarak ayarlamanız gerekir. Standart JSON biçiminiz varsa 0x0b olarak ayarlamanız ROWTERMINATOR gerekir.

JSON_VALUE kullanarak JSON dosyalarını sorgulama

Aşağıdaki sorgu, bir JSON belgelerinden skaler değerleri (date_rep, , countries_and_territoriescases) almak için JSON_VALUE nasıl kullanacağınızı gösterir:

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 belgesinden JSON özelliklerini ayıkladıktan sonra sütun diğer adlarını tanımlayabilir ve isteğe bağlı olarak metin değerini bir türe dönüştürebilirsiniz.

OPENJSON kullanarak JSON dosyalarını sorgulama

Aşağıdaki sorgu OPENJSON kullanır. Sırbistan'da bildirilen COVID istatistiklerini alacak:

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 işlevi kullanılarak döndürülen sonuçlarla sonuçlar işlevsel olarak aynıdır. Bazı durumlarda, OPENJSON ' JSON_VALUEden daha avantajlı olabilir:

WITH koşulunda, her özellik için sütun takma adlarını ve türlerini açıkça ayarlayabilirsiniz. CAST işlevini SELECT listesindeki her sütuna yerleştirmeniz gerekmez.
Eğer çok sayıda özelliği döndürüyorsanız, OPENJSON daha hızlı olabilir. Yalnızca 1-2 özellik döndürecekseniz, OPENJSON işlev ek yük olabilir.
OPENJSON işlevini, diziyi her belgeden ayrıştırıp üst satırla birleştirmeniz gerekiyorsa kullanmalısınız.

Sonraki adımlar

Bu serideki sonraki makalelerde aşağıdakilerin nasıl yapılacağını gösterilecektir:

Geri Bildirim

Bu sayfayı yararlı buldunuz mu?

Last updated on 2025-04-06