Bagikan melalui

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

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.

Read parquet file

The easiest way to see to the content of your PARQUET file is to provide file URL to OPENROWSET function and specify 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. The Latin1_General_100_BIN2_UTF8 collation is compatible with parquet string sorting rules. The SQL pool is able to eliminate some parts of the parquet files that won't contain data needed in the queries (file/column-segment pruning). Jika Anda menggunakan kolase lain, semua data dari file parket akan dimuat ke Synapse SQL, dan pemfilteran terjadi dalam proses SQL. The Latin1_General_100_BIN2_UTF8 collation has another performance optimization that works only for parquet and 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. Examples in this article show the specifics of reading Parquet files.

Query set of parquet files

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;

Automatic schema inference

You don't need to use the OPENROWSET WITH clause when reading Parquet files. Column names and data types are automatically read from Parquet files.

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

You don't have to specify columns in the OPENROWSET WITH clause when reading Parquet files. 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

How to query Parquet nested types