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

Makale
10/18/2023

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 işlevin dosya URL'sini OPENROWSET sağlamak, csv FORMATbelirtmek ve ve fieldquoteiçin fieldterminator değerleri 0x0b ayarlamaktır. Satırla ayrılmış JSON dosyalarını okumanız gerekiyorsa bu yeterlidir. Klasik JSON dosyanız varsa için değerleri 0x0b rowterminatorayarlamanı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"}
{"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"}

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_VALUE OPENJSON kullanabilirsiniz:

date_rep	vakalar	geo_id
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"
}

Dekont

Bu belgeler satırla sınırlandırılmış JSON olarak depolanıyorsa ve FIELDQUOTE 0x0b ayarlamanız FIELDTERMINATOR gerekir. Standart JSON biçiminiz varsa 0x0b olarak ayarlamanız ROWTERMINATOR gerekir.

JSON_VALUE kullanarak JSON dosyalarını sorgulama

Aşağıdaki sorguda, JSON belgelerinden skaler değerleri (date_rep, , casescountries_and_territories) almak için JSON_VALUE nasıl kullanılacağı gösterilmektedir:

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;

sonuçlar işlev olarak işlevi kullanılarak döndürülen sonuçlarla JSON_VALUE aynıdır. Bazı durumlarda, OPENJSON ' JSON_VALUEden daha avantajlı olabilir:

yan tümcesinde WITH , her özellik için sütun diğer adlarını ve türlerini açıkça ayarlayabilirsiniz. İşlevi CAST listedeki her sütuna SELECT yerleştirmeniz gerekmez.
OPENJSON çok sayıda özellik döndürecekseniz daha hızlı olabilir. Yalnızca 1-2 özellik döndürecekseniz, OPENJSON işlev ek yük olabilir.
Diziyi OPENJSON her belgeden ayrıştırıp üst satırla birleştirmeniz gerekiyorsa işlevini kullanmanız gerekir.

Sonraki adımlar

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

Aracılığıyla paylaş