Azure Synapse Analytics'te sunucusuz SQL havuzunu kullanarak Parquet ve JSON dosyalarında iç içe türleri sorgulama

Bu makalede, Azure Synapse Analytics'te sunucusuz SQL havuzunu kullanarak sorgu yazmayı öğreneceksiniz. Sorgu, Parquet'in iç içe geçmiş türlerini okuyacak. İç içe türler, nesneleri veya dizileri temsil eden karmaşık yapılardır. İç içe türler şunlarda depolanabilir:

Parquet, diziler ve nesneler içeren birden çok karmaşık sütuna sahip olabileceğiniz bir sistemdir.
Karmaşık bir JSON belgesini tek sütun olarak okuyabileceğiniz hiyerarşik JSON dosyaları.
Her belgenin karmaşık iç içe özellikler içerebildiği Azure Cosmos DB koleksiyonları (şu anda geçitli genel önizleme aşamasındadır).

Sunucusuz SQL havuzu tüm iç içe türleri JSON nesneleri ve dizileri olarak biçimlendirir. Böylece, JSON işlevlerini kullanarak karmaşık nesneleri ayıklayabilir veya değiştirebilir veya OPENJSON işlevini kullanarak JSON verilerini ayrıştırabilirsiniz.

İç içe nesneler içeren COVID-19 Açık Araştırma Veri Kümesi JSON dosyasından skaler ve nesne değerlerini ayıklayan bir sorgu örneği aşağıda verilmiştir:

SELECT
    title = JSON_VALUE(doc, '$.metadata.title'),
    first_author = JSON_QUERY(doc, '$.metadata.authors[0]'),
    first_author_name = JSON_VALUE(doc, '$.metadata.authors[0].first'),
    complex_object = doc
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/covid19temp/comm_use_subset/pdf_json/000b7d1517ceebb34e1e3e817695b6de03e2fa78.json',
        FORMAT='CSV', FIELDTERMINATOR ='0x0b', FIELDQUOTE = '0x0b', ROWTERMINATOR = '0x0b'
    )
    WITH ( doc varchar(MAX) ) AS docs;

İşlev, JSON_VALUE belirtilen yoldaki alandan bir skaler değer döndürür. İşlev, JSON_QUERY belirtilen yoldaki alandan JSON olarak biçimlendirilmiş bir nesne döndürür.

Önemli

Bu örnekte COVID-19 Açık Araştırma Veri Kümesi'nden bir dosya kullanılır. Burada verilerin lisansına ve yapısına bakın.

Önkoşullar

İlk adım, veri kaynağının oluşturulacağı bir veritabanı oluşturmaktır. Ardından veritabanında bir kurulum betiği çalıştırarak nesneleri başlatacaksınız. Kurulum betiği, örneklerde kullanılan veri kaynaklarını, veritabanı kapsamlı kimlik bilgilerini ve dış dosya biçimlerini oluşturur.

proje iç içe veya yinelenen veriler

Parquet dosyasının karmaşık türleri olan birden çok sütunu olabilir. Bu sütunlardaki değerler JSON metni olarak biçimlendirilir ve VARCHAR sütunları olarak döndürülür. Aşağıdaki sorgu structExample.parquet dosyasını okur ve iç içe sütunların değerlerinin nasıl okunmasını gösterir:

SELECT
    DateStruct, TimeStruct, TimestampStruct, DecimalStruct, FloatStruct
FROM
    OPENROWSET(
        BULK 'parquet/nested/structExample.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    )
    WITH (
        DateStruct VARCHAR(8000),
        TimeStruct VARCHAR(8000),
        TimestampStruct VARCHAR(8000),
        DecimalStruct VARCHAR(8000),
        FloatStruct VARCHAR(8000)
    ) AS [r];

Bu sorgu aşağıdaki sonucu döndürür. İç içe her nesnenin içeriği JSON metni olarak döndürülür.

DateStruct	TimeStruct	Zaman Damgası Yapısı	Ondalık Yapı	FloatStruct
{"Date":"2009-04-25"}	{"Time":"20:51:54.3598000"}	{"Timestamp":"5501-04-08 12:13:57.4821000"}	{"Ondalık":11143412.25350}	{"Float":0.5}
{"Date":"1916-04-29"}	{"Time":"00:16:04.6778000"}	{"Timestamp":"1990-06-30 20:50:52.6828000"}	{"Decimal":1963545.62800}	{"Float":-2.125}

Aşağıdaki sorgu justSimpleArray.parquet dosyasını okur. Parquet dosyasından, iç içe ve yinelenen veriler de dahil olmak üzere tüm sütunları belirler.

SELECT
    SimpleArray
FROM
    OPENROWSET(
        BULK 'parquet/nested/justSimpleArray.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) AS [r];

Bu sorgu aşağıdaki sonucu döndürür:

SimpleArray
[11,12,13]
[21,22,23]

İç içe nesne sütunlarından özellikleri okuma

işlevi, JSON_VALUE JSON metni olarak biçimlendirilmiş sütunlardan değerler döndürmenizi sağlar:

SELECT
    title = JSON_VALUE(complex_column, '$.metadata.title'),
    first_author_name = JSON_VALUE(complex_column, '$.metadata.authors[0].first'),
    body_text = JSON_VALUE(complex_column, '$.body_text.text'),
    complex_column
FROM
    OPENROWSET( BULK 'https://azureopendatastorage.blob.core.windows.net/covid19temp/comm_use_subset/pdf_json/000b7d1517ceebb34e1e3e817695b6de03e2fa78.json',
                FORMAT='CSV', FIELDTERMINATOR ='0x0b', FIELDQUOTE = '0x0b', ROWTERMINATOR = '0x0b' ) WITH ( complex_column varchar(MAX) ) AS docs;

Sonuç aşağıdaki tabloda gösterilmiştir:

başlık	first_author_name	ana metin	karmaşık_sütun
Ek Bilgi Bir eko-epidemiolo...	Julien	- Şekil S1 : Filogenisi...	`{ "paper_id": "000b7d1517ceebb34e1e3e817695b6de03e2fa78", "metadata": { "title": "Supplementary Information An eco-epidemiological study of Morbilli-related paramyxovirus infection in Madagascar bats reveals host-switching as the dominant macro-evolutionary mechanism", "authors": [ { "first": "Julien"`

Çoğu durumda karmaşık bir JSON nesnesi içeren tek bir sütun döndüren JSON dosyalarının aksine Parquet dosyaları birden çok karmaşık sütuna sahip olabilir. JSON_VALUE işlevini her sütunda kullanarak iç içe sütunların özelliklerini okuyabilirsiniz. OPENROWSET bir WITH yan tümcesinde iç içe özelliklerin yollarını doğrudan belirtmenizi sağlar. Yolları bir sütunun adı olarak ayarlayabilir veya sütun türünden sonra bir JSON yol ifadesi ekleyebilirsiniz.

Aşağıdaki sorgu structExample.parquet dosyasını okur ve iç içe bir sütunun öğelerinin nasıl yüzeylendiğini gösterir. İç içe bir değere başvurmanın iki yolu vardır:

Tür belirtiminin ardından iç içe değer yolu ifadesini belirterek.
Alanlara başvurmak için "." komutunu kullanarak sütun adını iç içe bir yol olarak biçimlendirin.

SELECT
    *
FROM
    OPENROWSET(
        BULK 'parquet/nested/structExample.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    )
    WITH (
        [DateValue] DATE '$.DateStruct.Date',
        [TimeStruct.Time] TIME,
        [TimestampStruct.Timestamp] DATETIME2,
        DecimalValue DECIMAL(18, 5) '$.DecimalStruct.Decimal',
        [FloatStruct.Float] FLOAT
    ) AS [r];

Yinelenen sütunlardan öğelere erişme

Aşağıdaki sorgu justSimpleArray.parquet dosyasını okur ve dizi veya harita gibi yinelenen bir sütundan skaler öğe almak için JSON_VALUE kullanır:

SELECT
    *,
    JSON_VALUE(SimpleArray, '$[0]') AS FirstElement,
    JSON_VALUE(SimpleArray, '$[1]') AS SecondElement,
    JSON_VALUE(SimpleArray, '$[2]') AS ThirdElement
FROM
    OPENROWSET(
        BULK 'parquet/nested/justSimpleArray.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) AS [r];

Sonuç şu şekildedir:

SimpleArray	FirstElement	İkinciÖğe	ÜçüncüÖğe
[11,12,13]	11	12	13
[21,22,23]	21	22	23

Karmaşık sütunlardan alt nesnelere erişme

Aşağıdaki sorgu mapExample.parquet dosyasını okur ve dizi veya harita gibi yinelenen bir sütun içinden skaler olmayan bir öğeyi almak için JSON_QUERY kullanır:

SELECT
    MapOfPersons,
    JSON_QUERY(MapOfPersons, '$."John Doe"') AS [John]
FROM
    OPENROWSET(
        BULK 'parquet/nested/mapExample.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) AS [r];

Bir WITH yan tümcesinde döndürmek istediğiniz sütunları da açıkça belirtebilirsiniz.

SELECT DocId,
    MapOfPersons,
    JSON_QUERY(MapOfPersons, '$."John Doe"') AS [John]
FROM
    OPENROWSET(
        BULK 'parquet/nested/mapExample.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) 
    WITH (DocId bigint, MapOfPersons VARCHAR(max)) AS [r];

Yapı MapOfPersons VARCHAR sütunu olarak döndürülür ve JSON dizesi olarak biçimlendirilir.

Yinelenen sütunlardan proje değerleri

Bazı sütunlarda bir dizi skaler değer (örneğin [1,2,3]) varsa, bu betiği kullanarak bunları kolayca genişletebilir ve ana satırla birleştirebilirsiniz:

SELECT
    SimpleArray, Element
FROM
    OPENROWSET(
        BULK 'parquet/nested/justSimpleArray.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) AS arrays
    CROSS APPLY OPENJSON (SimpleArray) WITH (Element int '$') as array_values

Sonraki adımlar

Sonraki makalede JSON dosyalarını sorgulama gösterilmektedir.

Geri Bildirim

Bu sayfayı yararlı buldunuz mu?

Last updated on 2025-04-07