Kueri tipe berlapis di Parquet dan file JSON dengan menggunakan kumpulan SQL tanpa server di Azure Synapse Analytics

  • Artikel

Dalam artikel ini, Anda akan mempelajari cara membuat kueri menggunakan kumpulan SQL tanpa server di Azure Synapse Analytics. Kueri akan membaca Parquet tipe berlapis. Tipe berlapis adalah struktur kompleks yang mewakili objek atau array. Tipe berlapis dapat disimpan di:

  • Parquet, di mana Anda dapat memiliki beberapa kolom kompleks yang berisi array dan objek.
  • File JSON hierarkis, tempat Anda dapat membaca dokumen JSON kompleks sebagai satu kolom.
  • Koleksi Azure Cosmos DB (saat ini berada di bawah pratinjau publik yang terjaga), yang setiap dokumennya bisa berisi properti bertumpuk yang kompleks.

Kumpulan SQL tanpa server memformat semua tipe berlapis sebagai objek dan array JSON. Jadi Anda bisa mengekstrak atau memodifikasi objek kompleks dengan menggunakan fungsi JSON atau mengurai data JSON dengan menggunakan fungsi OPENJSON.

Berikut ini contoh kueri yang mengekstrak nilai skalar dan objek dari file JSON COVID-19 Open Research Dataset, yang berisi objek berlapis:

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;

Fungsi JSON_VALUE mengembalikan nilai skalar dari bidang pada jalur yang ditentukan. Fungsi JSON_QUERY mengembalikan objek yang diformat sebagai JSON dari bidang di jalur yang ditentukan.

Penting

Contoh ini menggunakan file dari Himpunan Data Riset Terbuka COVID-19. Lihat lisensi dan struktur data di sini.

Prasyarat

Langkah pertama adalah membuat database tempat datasouce akan dibuat. Anda kemudian akan menginisialisasi objek dengan menjalankan skrip setup pada database. Skrip setup ini akan membuat sumber data, info masuk yang tercakup database, dan format file eksternal yang digunakan dalam sampel ini.

Memproyeksikan data berlapis atau berulang

File Parquet bisa memiliki beberapa kolom dengan tipe kompleks. Nilai dari kolom ini diformat sebagai teks JSON dan akan dikembalikan sebagai kolom VARCHAR. Kueri berikut membaca file structExample.parquet dan memperlihatkan cara membaca nilai kolom berlapis:

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];

Kueri ini mengembalikan hasil berikut ini. Konten setiap objek berlapis dikembalikan sebagai teks JSON.

DateStruct TimeStruct TimestampStruct DecimalStruct FloatStruct
{"Date":"2009-04-25"} {"Time":"20:51:54.3598000"} {"Timestamp":"5501-04-08 12:13:57.4821000"} {"Decimal":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}

Kueri berikut ini membaca file justSimpleArray.parquet. Ini memproyeksikan semua kolom dari file Parquet, termasuk data yang berlapis dan berulang.

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

Kueri ini akan mengembalikan hasil berikut ini:

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

Membaca properti dari kolom objek berlapis

Fungsi JSON_VALUE memungkinkan Anda mengembalikan nilai dari kolom yang diformat sebagai teks JSON:

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;

Hasilnya ditampilkan dalam contoh berikut ini:

judul first_author_name body_text complex_column
Informasi Tambahan eko-epidemiolo... Julien - Gambar S1: Phylogeny dari... { "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"

Tidak seperti file JSON, yang pada kebanyakan kasus mengembalikan kolom tunggal yang berisi objek JSON kompleks, file Parquet dapat memiliki beberapa kolom kompleks. Anda dapat membaca properti kolom berlapis dengan menggunakan fungsi JSON_VALUE pada setiap kolom. OPENROWSET memungkinkan Anda untuk secara langsung menentukan jalur properti berlapis dalam WITH klausa. Anda dapat mengatur jalur sebagai nama kolom, atau Anda bisa menambahkan ekspresi jalur JSON setelah tipe kolom.

Kueri berikut membaca file structExample.parquet dan memperlihatkan cara memunculkan elemen kolom berlapis. Ada dua cara untuk mereferensikan nilai berlapis:

  • Dengan menentukan ekspresi jalur nilai berlapis setelah spesifikasi tipe.
  • Dengan memformat nama kolom sebagai jalur berlapis dengan menggunakan lakukan "." untuk mereferensikan bidang.
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];

Mengakses elemen dari kolom berulang

Kueri berikut membaca file justSimpleArray.parquet dan menggunakan JSON_VALUE untuk mengambil elemen skalar dari dalam kolom berulang, seperti array atau peta:

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];

Berikut ini hasilnya:

SimpleArray firstElement SecondElement ThirdElement
[11,12,13] 11 12 13
[21,22,23] 21 22 23

Mengakses sub-objek dari kolom kompleks

Kueri berikut membaca file mapExample.parquet dan menggunakan JSON_QUERY untuk mengambil elemen non-skalar dari dalam kolom berulang, seperti array atau peta:

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

Anda juga bisa secara eksplisit mereferensikan kolom yang ingin Anda kembalikan dalam klausa WITH:

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];

Struktur MapOfPersons dikembalikan sebagai kolom VARCHAR dan diformat sebagai string JSON.

Memproyeksikan nilai dari kolom berulang

Jika Anda memiliki array nilai skalar (misalnya [1,2,3]) di beberapa kolom, Anda bisa dengan mudah memperluasnya dan menggabungkannya dengan baris utama dengan menggunakan skrip ini:

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

Langkah berikutnya

Artikel berikutnya akan memperlihatkan kepada Anda cara Mengkueri file JSON.