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

  • Makale

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

  • Diziler ve nesneler içeren birden çok karmaşık sütuna sahip olabileceğiniz Parquet.
  • 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 ya da 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.

İç içe veya yinelenen verileri yansıtma

Parquet dosyası karmaşık türlerde birden çok sütuna sahip 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.

Tarih Yapısı 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"} {"Ondalık":1963545.62800} {"Float":-2.125}

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

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

JSON_VALUE işlevi, 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 body_text complex_column
Ek Bilgi Eko-epidemiolo... Julien - Şekil S1 : Phylogeny of... { "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 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. her sütunda işlevini kullanarak iç içe sütunların JSON_VALUE ö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üzey yapılacağını 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 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 SecondElement ThirdElement
[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ütundan 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];

Yan tümcesinde WITH döndürmek istediğiniz sütunlara da açıkça başvurabilirsiniz:

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 BIR VARCHAR sütunu olarak döndürülür ve JSON dizesi olarak biçimlendirilir.

Yinelenen sütunlardan değerleri yansıtma

Bazı sütunlarda skaler değerler diziniz (ö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 işleminin nasıl yapılacağı gösterilir.