Melakukan kueri pada file Parquet menggunakan kumpulan SQL tanpa server di Azure Synapse Analytics

Tip

Microsoft Fabric Data Warehouse adalah gudang relasional skala perusahaan pada fondasi data lake, dengan arsitektur siap masa depan, AI bawaan, dan fitur baru. Jika Anda baru menggunakan pergudangan data, mulailah dengan Fabric Data Warehouse. Beban kerja kumpulan SQL terdedikasi yang ada dapat ditingkatkan ke Fabric untuk mengakses kemampuan baru di seluruh ilmu data, analitik waktu nyata, dan pelaporan.

• Mulai uji coba gratis Fabric.
• Migration Assistant untuk Fabric Data Warehouse.

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

Contoh panduan cepat mulai

Fungsi OPENROWSET memungkinkan Anda membaca konten file parquet dengan menyertakan URL file Anda.

Membaca file parquet

Cara termudah untuk melihat konten file Anda PARQUET adalah dengan menyediakan 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 kolate, Anda akan mendapatkan peningkatan performa ekstra dibandingkan dengan kolate lainnya. Kolase Latin1_General_100_BIN2_UTF8 kompatibel dengan aturan pengurutan string parkket. Kumpulan data SQL dapat menghilangkan beberapa bagian file parquet yang tidak akan mengandung data yang diperlukan dalam kueri (pemangkasan file/segmen kolom). Jika Anda menggunakan kolase lain, semua data dari file parket akan dimuat ke Synapse SQL, dan pemfilteran terjadi dalam proses SQL. Kolase Latin1_General_100_BIN2_UTF8 memiliki pengoptimalan performa lain yang hanya berfungsi untuk parquet dan Cosmos DB. Kelemahannya adalah bahwa Anda kehilangan aturan perbandingan terperinci seperti tidak peka terhadap 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 khusus, Anda bisa mengonfigurasi sumber data dengan kredensial yang dibatasi lingkupnya di 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 secara eksplisit menentukan beberapa kolase UTF-8 (misalnya Latin1_General_100_BIN2_UTF8) untuk semua kolom string dalam WITH klausul 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 kolom, 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 menunjukkan spesifikasi membaca file Parquet.

Kueri kumpulan 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 klausa OPENROWSET WITH saat membaca file Parquet. Nama kolom dan jenis 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, gunakan klausa OPENROWSET WITH.

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

Catatan

Anda tidak perlu menentukan kolom dalam klausa 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

Kueri data yang telah dipartisi

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 tipe Parquet ke tipe asli SQL, periksa pemetaan tipe untuk Parquet.

Langkah selanjutnya

Cara menjalankan kueri pada tipe bersarang Parquet