File kueri Parquet menggunakan kumpulan SQL tanpa server di Azure Synapse Analytics

Artikel
10/18/2023

Dalam artikel ini, Anda akan mempelajari cara menulis kueri menggunakan kumpulan SQL tanpa server yang akan membaca file Parquet.

Contoh mulai cepat

Fungsi OPENROWSET memungkinkan Anda membaca konten file parquet dengan memberikan URL ke file Anda.

Baca file parquet

Cara termudah untuk melihat konten file PARQUET Anda adalah dengan memberikan URL file ke fungsi OPENROWSET dan menentukan parquet FORMAT. Jika file tersedia untuk umum atau jika identitas Microsoft Entra Anda dapat mengakses file ini, Anda akan dapat melihat konten file menggunakan kueri seperti yang diperlihatkan dalam contoh berikut:

select top 10 *
from openrowset(
    bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.parquet',
    format = 'parquet') as rows

Pastikan Anda bisa mengakses file ini. Jika file Anda dilindungi dengan kunci SAS atau identitas Azure kustom, Anda harus menyiapkan kredensial tingkat server untuk masuk sql.

Penting

Pastikan Anda menggunakan kolase database UTF-8 (misalnya Latin1_General_100_BIN2_UTF8) karena nilai string dalam file PARQUET dikodekan menggunakan pengodean UTF-8. Ketidakcocokan antara pengodean teks dalam file PARQUET dan kolase dapat menyebabkan kesalahan konversi yang tidak terduga. Anda dapat dengan mudah mengubah kolase default database saat ini menggunakan pernyataan T-SQL berikut: ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8; Untuk informasi selengkapnya tentang kolase, lihat Jenis kolase yang didukung untuk Synapse SQL.

Jika Anda menggunakan Latin1_General_100_BIN2_UTF8 kolase Anda akan mendapatkan dorongan kinerja tambahan dibandingkan dengan kolase lainnya. Kolase Latin1_General_100_BIN2_UTF8 kompatibel dengan aturan penyortiran string parket. Kumpulan SQL mampu menghilangkan beberapa bagian file parket yang tidak akan berisi data yang dibutuhkan dalam kueri (pemangkasan segmen file/kolom). Jika Anda menggunakan kolase lain, semua data dari file parket akan dimuat ke synapse SQL dan penyaringan terjadi dalam proses SQL. Kolase Latin1_General_100_BIN2_UTF8 memiliki pengoptimalan performa tambahan yang hanya berfungsi untuk parquet dan Cosmos DB. Kelemahannya adalah bahwa Anda kehilangan aturan perbandingan terperinci seperti ketidakpekaan huruf besar atau kecil.

Penggunaan sumber data

Contoh sebelumnya menggunakan jalur lengkap ke file. Sebagai alternatif, Anda dapat membuat sumber data eksternal dengan lokasi yang mengarah ke folder akar penyimpanan, dan menggunakan sumber data tersebut dan jalur relatif ke file dalam fungsi OPENROWSET:

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.parquet',
        data_source = 'covid',
        format = 'parquet'
    ) as rows

Jika sumber data dilindungi dengan kunci SAS atau identitas kustom, Anda bisa mengonfigurasi sumber data dengan info masuk lingkup database.

Secara eksplisit menentukan skema

OPENROWSET memungkinkan Anda secara eksplisit menentukan kolom apa yang ingin Anda baca dari file menggunakan klausul WITH:

select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.parquet',
        data_source = 'covid',
        format = 'parquet'
    ) with ( date_rep date, cases int, geo_id varchar(6) ) as rows

Penting

Pastikan Anda kedaluwarsa menentukan beberapa kolase UTF-8 (misalnya) untuk Latin1_General_100_BIN2_UTF8 semua kolom string dalam kausul WITH atau mengatur beberapa kolase UTF-8 di tingkat database. Ketidakcocokan antara pengkodean teks dalam file dan kolase kolom string dapat menyebabkan kesalahan konversi yang tidak terduga. Anda dapat dengan mudah mengubah kolase default database saat ini menggunakan pernyataan T-SQL berikut: ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8; Anda dapat dengan mudah mengatur kolase pada jenis colum, misalnya: geo_id varchar(6) collate Latin1_General_100_BIN2_UTF8 Untuk informasi selengkapnya tentang kolase, lihat Jenis kolase yang didukung untuk Synapse SQL.

Di bagian berikut, Anda bisa melihat cara mengkueri berbagai jenis file PARQUET.

Prasyarat

Langkah pertama Anda adalah membuat database dengan sumber data yang mereferensikan akun penyimpanan Taksi Kuning NYC. Kemudian menginisialisasi objek dengan mengeksekusi skrip penyiapan pada database itu. Skrip penyetelan ini akan membuat sumber data, info masuk lingkup database, dan format file eksternal yang digunakan dalam sampel ini.

Dataset

Himpunan data Taksi Kuning NYC digunakan dalam sampel ini. Anda dapat mengueri file Parquet dengan cara yang sama seperti Anda membaca file CSV. Satu-satunya perbedaan adalah bahwa perameter FILEFORMAT harus diatur ke PARQUET. Contoh dalam artikel ini memperlihatkan secara spesifik pembacaan file Parquet.

Kumpulan kueri file parquet

Anda hanya bisa menentukan kolom minat saat Anda mengueri file Parquet.

SELECT
        YEAR(tpepPickupDateTime),
        passengerCount,
        COUNT(*) AS cnt
FROM  
    OPENROWSET(
        BULK 'puYear=2018/puMonth=*/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) WITH (
        tpepPickupDateTime DATETIME2,
        passengerCount INT
    ) AS nyc
GROUP BY
    passengerCount,
    YEAR(tpepPickupDateTime)
ORDER BY
    YEAR(tpepPickupDateTime),
    passengerCount;

Inferensi skema otomatis

Anda tidak perlu menggunakan klausul OPENROWSET WITH saat membaca file Parquet. Nama kolom dan tipe data secara otomatis dibaca dari file Parquet.

Perlu diingat bahwa jika Anda membaca jumlah file sekaligus, skema, nama kolom, dan jenis data akan disimpulkan dari layanan file pertama yang diperoleh dari penyimpanan. Ini dapat berarti bahwa beberapa kolom yang diharapkan dihilangkan, semua karena file yang digunakan oleh layanan untuk menentukan skema tidak berisi kolom ini. Untuk menentukan skema secara eksplisit, silakan gunakan klausa OPENROWSET WITH.

Sampel berikut menunjukkan kemampuan inferensi skema otomatis untuk file Parquet. Kemampuan tersebut mengembalikan jumlah baris pada September 2018 tanpa menentukan skema.

Catatan

Anda tidak harus menentukan kolom dalam klausul OPENROWSET WITH saat membaca file Parquet. Dalam hal ini, layanan kueri kumpulan SQL tanpa server menggunakan metadata dalam file Parquet dan mengikat kolom berdasarkan nama.

SELECT TOP 10 *
FROM  
    OPENROWSET(
        BULK 'puYear=2018/puMonth=9/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) AS nyc

Data yang dipartisi kueri

Kumpulan data yang disediakan dalam sampel ini dibagi (dipartisi) menjadi subfolder terpisah. Anda dapat menargetkan partisi tertentu menggunakan fungsi filepath. Contoh ini menunjukkan jumlah tarif menurut tahun, bulan, dan payment_type untuk tiga bulan pertama 2017.

Catatan

Kueri kumpulan SQL tanpa server kompatibel dengan skema partisi Apache Hive/Hadoop.

SELECT
        YEAR(tpepPickupDateTime),
        passengerCount,
        COUNT(*) AS cnt
FROM  
    OPENROWSET(
        BULK 'puYear=*/puMonth=*/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) nyc
WHERE
    nyc.filepath(1) = 2017
    AND nyc.filepath(2) IN (1, 2, 3)
    AND tpepPickupDateTime BETWEEN CAST('1/1/2017' AS datetime) AND CAST('3/31/2017' AS datetime)
GROUP BY
    passengerCount,
    YEAR(tpepPickupDateTime)
ORDER BY
    YEAR(tpepPickupDateTime),
    passengerCount;

Pemetaan jenis

Untuk pemetaan jenis Parquet ke jenis asli SQL cek pemetaan jenis untuk Parquet.

Langkah berikutnya

Lanjutkan ke artikel berikutnya untuk mempelajari cara Mengueri jenis Parquet bertumpuk.