Komputasi pushdown di PolyBase

Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru

Komputasi pushdown meningkatkan performa kueri pada sumber data eksternal. Dimulai di SQL Server 2016 (13.x), komputasi pushdown tersedia untuk sumber data eksternal Hadoop. SQL Server 2019 (15.x) memperkenalkan komputasi pushdown untuk jenis sumber data eksternal lainnya.

Catatan

Untuk menentukan apakah komputasi pushdown PolyBase menguntungkan kueri Anda atau tidak, lihat Cara mengetahui apakah pushdown eksternal terjadi.

Mengaktifkan komputasi pushdown

Artikel berikut ini mencakup informasi tentang mengonfigurasi komputasi pushdown untuk jenis sumber data eksternal tertentu:

• Mengaktifkan komputasi pushdown di Hadoop
• Mengonfigurasi PolyBase untuk mengakses data eksternal di Oracle
• Mengonfigurasi PolyBase untuk mengakses data eksternal di Teradata
• Mengonfigurasi PolyBase untuk mengakses data eksternal di MongoDB
• Mengonfigurasi PolyBase untuk mengakses data eksternal dengan jenis generik ODBC
• Mengonfigurasi PolyBase untuk mengakses data eksternal di SQL Server

Tabel ini meringkas dukungan komputasi pushdown pada sumber data eksternal yang berbeda:

Sumber data	Gabungan	Proyeksi	Agregasi	Filter	Statistik
Konektivitas Database Terbuka Generik	Ya	Ya	Ya	Ya	Ya
Oracle	Ya+	Ya	Ya	Ya	Ya
SQL Server	Ya	Ya	Ya	Ya	Ya
Teradata	Ya	Ya	Ya	Ya	Ya
MongoDB*	Tidak	Ya	Ya***	Ya***	Ya
Hadoop	Tidak	Ya	Beberapa**	Beberapa**	Ya
Penyimpanan Blob Azure	Tidak	Tidak	Tidak	Tidak	Ya

* Dukungan pushdown Azure Cosmos DB diaktifkan melalui API Azure Cosmos DB untuk MongoDB.

** Lihat Komputasi pushdown dan penyedia Hadoop.

Dukungan pushdown untuk agregasi dan filter untuk konektor ODBC MongoDB untuk SQL Server 2019 diperkenalkan dengan SQL Server 2019 CU18.

+ Oracle mendukung pushdown untuk gabungan tetapi Anda mungkin perlu membuat statistik pada kolom gabungan untuk mencapai pushdown.

Catatan

Komputasi pushdown dapat diblokir oleh beberapa sintaks T-SQL. Untuk informasi selengkapnya, tinjau Sintaks yang mencegah pushdown.

Komputasi pushdown dan penyedia Hadoop

PolyBase saat ini mendukung dua penyedia Hadoop: Hortonworks Data Platform (HDP) dan Cloudera Distributed Hadoop (CDH). Tidak ada perbedaan antara kedua penyedia dalam hal komputasi pushdown.

Untuk menggunakan fungsionalitas pushdown komputasi dengan Hadoop, kluster Hadoop target harus memiliki komponen inti: HDFS, YARN, dan MapReduce, dengan server riwayat pekerjaan diaktifkan. PolyBase mengirimkan kueri pushdown melalui MapReduce dan menarik status dari server riwayat pekerjaan. Tanpa salah satu komponen, kueri gagal.

Beberapa agregasi harus terjadi setelah data mencapai SQL Server. Tetapi sebagian agregasi terjadi di Hadoop. Metode ini umum dalam menghitung agregasi dalam sistem pemrosesan paralel secara besar-besaran.

Penyedia Hadoop mendukung agregasi dan filter berikut.

Agregasi	Filter (perbandingan biner)
Count_Big	NotEqual
Jumlah total	LessThan
Avg	LessOrEqual
Maks	GreaterOrEqual
Min	GreaterThan
Approx_Count_Distinct	Is
	IsNot

Skenario utama yang bermanfaat dari komputasi pushdown

Dengan komputasi pushdown PolyBase, Anda dapat mendelegasikan tugas komputasi ke sumber data eksternal. Ini mengurangi beban kerja pada instans SQL Server dan dapat secara signifikan meningkatkan performa.

SQL Server dapat mendorong gabungan, proyeksi, agregasi, dan filter ke sumber data eksternal, memanfaatkan komputasi jarak jauh dan membatasi data yang dikirim melalui jaringan.

Bergabung dengan pushdown

PolyBase dapat memfasilitasi pushdown operator penggabungan saat Anda menggabungkan dua tabel eksternal pada sumber data eksternal yang sama, yang secara signifikan meningkatkan performa.

Ketika penggabungan dilakukan oleh sumber data eksternal, itu mengurangi jumlah pergerakan data dan meningkatkan performa kueri. Tanpa join pushdown, SQL Server harus mengambil data dari kedua tabel secara lokal ke dalam tempdb dan kemudian melakukan penggabungan.

Dalam kasus gabungan terdistribusi (menggabungkan tabel lokal ke tabel eksternal), kecuali filter Anda berlaku untuk tabel eksternal yang digabungkan, SQL Server harus membawa semua data dari tabel eksternal ke dalam lokal untuk melakukan operasi gabungan. Misalnya, kueri berikut tidak memiliki pemfilteran pada kondisi gabungan tabel eksternal, yang menghasilkan pembacaan semua data dari tabel eksternal.

SELECT * FROM LocalTable L
JOIN ExternalTable E on L.id = E.id

Karena gabungan menggunakan E.id kolom tabel eksternal, saat Anda menambahkan kondisi filter ke kolom tersebut, SQL Server dapat menurunkan filter, mengurangi jumlah baris yang dibaca dari tabel eksternal.

SELECT * FROM LocalTable L
JOIN ExternalTable E on L.id = E.id
WHERE E.id = 20000

Pilih subset baris

Gunakan pushdown predikat untuk meningkatkan performa kueri yang memilih subset baris dari tabel eksternal.

Dalam contoh ini, SQL Server memulai pekerjaan pengurangan peta untuk mengambil baris yang cocok dengan predikat customer.account_balance < 200000 di Hadoop. Karena kueri berhasil diselesaikan tanpa memindai semua baris dalam tabel, hanya baris yang memenuhi kriteria predikat yang disalin ke SQL Server. Ini menghemat waktu yang signifikan dan membutuhkan lebih sedikit ruang penyimpanan sementara ketika jumlah saldo < pelanggan 200000 kecil dibandingkan dengan jumlah pelanggan dengan saldo >akun = 200000.

SELECT * FROM customer WHERE customer.account_balance < 200000;
SELECT * FROM SensorData WHERE Speed > 65;

Pilih subset kolom

Gunakan pushdown predikat untuk meningkatkan performa kueri yang memilih subset kolom dari tabel eksternal.

Dalam kueri ini, SQL Server memulai pekerjaan pengurangan peta untuk memproses file teks yang dibatasi Hadoop sehingga hanya data untuk dua kolom, customer.name dan customer.zip_code, yang akan disalin ke SQL Server.

SELECT customer.name, customer.zip_code
FROM customer
WHERE customer.account_balance < 200000;

Pushdown untuk ekspresi dan operator dasar

SQL Server memungkinkan ekspresi dan operator dasar ini untuk penerapan predikat:

• Operator perbandingan biner (<, , >, =!=, <>, >=, ) <=untuk nilai numerik, tanggal, dan waktu.
• Operator aritmatika (+, , -, */, %).
• Operator logis (AND, OR).
• Operator unary (NOT, IS NULL, IS NOT NULL).

Operator BETWEEN, , NOTIN, dan LIKE dapat didorong ke bawah tergantung pada bagaimana pengoptimal kueri menulis ulang ekspresi operator sebagai serangkaian pernyataan menggunakan operator relasional dasar.

Kueri dalam contoh ini memiliki beberapa predikat yang dapat didorong ke Hadoop. SQL Server dapat mendorong pekerjaan pengurangan peta ke Hadoop untuk melakukan predikat customer.account_balance <= 200000. Ekspresi BETWEEN 92656 AND 92677 ini juga terdiri dari operasi biner dan logis yang dapat didorong ke Hadoop. ANDadalah ekspresi akhir.

Mengingat kombinasi predikat ini, pekerjaan pengurangan peta dapat melakukan semua klausa WHERE. Hanya data yang memenuhi kriteria yang SELECT disalin kembali ke SQL Server.

SELECT * FROM customer
WHERE customer.account_balance <= 200000
AND customer.zipcode BETWEEN 92656 AND 92677;

Fungsi yang didukung untuk pushdown

SQL Server memungkinkan fungsi-fungsi ini untuk penurunan predikat:

Fungsi string:

• CONCAT
• DATALENGTH
• LEN
• LIKE
• LOWER
• LTRIM
• RTRIM
• SUBSTRING
• UPPER

Fungsi matematika:

• ABS
• ACOS
• ASIN
• ATAN
• CEILING
• COS
• EXP
• FLOOR
• POWER
• SIGN
• SIN
• SQRT
• TAN

Fungsi umum:

• COALESCE *
• NULLIF

* Menggunakan dengan COLLATE dapat mencegah pushdown dalam beberapa skenario. Untuk informasi selengkapnya, lihat Konflik kolabasi.

Fungsi tanggal & waktu:

• DATEADD
• DATEDIFF
• DATEPART

Sintaksis yang mencegah pushdown

Fungsi T-SQL atau item sintaksis ini mencegah komputasi pushdown:

• AT TIME ZONE
• CONCAT_WS
• TRANSLATE
• RAND
• CHECKSUM
• BINARY_CHECKSUM
• HASHBYTES
• ISJSON
• JSON_VALUE
• JSON_QUERY
• JSON_MODIFY
• NEWID
• STRING_ESCAPE
• COMPRESS
• DECOMPRESS
• GREATEST
• LEAST
• PARSE

Dukungan pushdown untuk FORMAT sintaks dan TRIM diperkenalkan di SQL Server 2019 (15.x) CU10.

Klausa filter dengan variabel

Saat Anda menentukan variabel dalam klausa filter, secara default SQL Server tidak mendorong ke bawah klausa filter. Misalnya, kueri berikut tidak mendorong ke bawah klausa filter:

DECLARE @BusinessEntityID INT

SELECT * FROM [Person].[BusinessEntity]
WHERE BusinessEntityID = @BusinessEntityID;

Untuk mengaktifkan pushdown variabel, aktifkan fungsionalitas pembaruan cepat pengoptimal kueri menggunakan salah satu metode berikut:

• Tingkat Instans: Aktifkan bendera pelacakan 4199 sebagai parameter startup untuk instans.
• Tingkat Database: Dalam konteks database yang memiliki objek eksternal PolyBase, jalankan ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON.
• Tingkat kueri: Gunakan petunjuk OPTION (QUERYTRACEON 4199) kueri atau OPTION (USE HINT ('ENABLE_QUERY_OPTIMIZER_HOTFIXES')).

Batasan ini berlaku untuk eksekusi sp_executesql dan untuk beberapa fungsi dalam klausa filter.

SQL Server 2019 CU5 pertama kali memperkenalkan kemampuan untuk menurunkan variabel.

Untuk informasi selengkapnya, lihat sp_executesql.

Konflik kolabasi

Pushdown mungkin tidak berfungsi dengan data yang memiliki kolasi yang berbeda. Operator seperti COLLATE juga dapat mengganggu hasilnya. SQL Server mendukung kolase yang sama atau kolase biner. Untuk informasi selengkapnya, lihat Cara mengetahui apakah pushdown eksternal terjadi.

Pushdown untuk file parket

Mulai SQL Server 2022 (16.x), PolyBase memperkenalkan dukungan untuk file parket. SQL Server mampu melakukan eliminasi baris dan kolom saat melakukan pushdown dengan parket.

Sumber data eksternal yang didukung

Dukungan pushdown untuk Parquet ada pada sumber data eksternal berikut:

• Penyimpanan objek yang kompatibel dengan S3
• Azure Blob Storage
• Azure Data Lake Storage Gen2

Untuk detail konfigurasi, lihat:

• Mengonfigurasi PolyBase untuk mengakses data eksternal di penyimpanan objek yang kompatibel dengan S3
• Memvirtualisasi file parquet dalam penyimpanan objek yang kompatibel dengan S3 menggunakan PolyBase

Operasi pushdown

SQL Server dapat memproses operasi ini dengan file parquet.

• Operator perbandingan biner (>, =, ><=, <) untuk nilai numerik, tanggal, dan waktu.
• Kombinasi operator perbandingan (> AND <, >= AND <, > AND <=, <= AND >=).
• Dalam filter daftar (col1 = val1 ATAU col1 = val2 OR vol1 = val3).
• TIDAK NULL di atas kolom.

Item-item ini mencegah pushdown untuk file parquet:

• Kolom virtual.
• Perbandingan kolom.
• Konversi jenis parameter.

Tipe data yang didukung

• bit
• tinyint
• smallint
• bigint
• real
• float
• varchar (Bin2Collation, CodePageConversion, BinCollation)
• nvarchar (Bin2Collation, BinCollation)
• binary
• datetime2 (presisi default dan 7 digit)
• tanggal
• waktu (presisi default dan 7 digit)
• Numerik *

* Didukung saat skala parameter selaras dengan skala kolom, atau ketika parameter secara eksplisit ditransmisikan ke desimal.

Jenis data yang mencegah pushdown parke

• uang
• smallmoney
• datetime
• smalldatetime

Penghapusan partisi dengan struktur folder

PolyBase dapat menggunakan struktur folder untuk penghapusan partisi, mengurangi jumlah data yang dipindai saat melakukan kueri. Saat Anda mengatur file Parquet di folder hierarkis (seperti menurut tahun, bulan, atau kunci partisi lainnya), PolyBase dapat melewati seluruh folder yang tidak cocok dengan predikat kueri Anda.

Misalnya, jika Anda menyusun data Anda sebagai:

/data/year=2024/month=01/*.parquet
/data/year=2024/month=02/*.parquet
/data/year=2025/month=01/*.parquet

Anda dapat melakukan kueri partisi tertentu menggunakan kartu bebas di OPENROWSET atau lokasi tabel eksternal.

-- Query only January 2025 data
SELECT *
FROM OPENROWSET(
    BULK '/data/year=2025/month=01/*.parquet',
    DATA_SOURCE = 's3_ds',
    FORMAT = 'PARQUET'
) AS [data];

Untuk penghapusan folder dinamis, jalankan kueri pada jalur folder yang lebih luas dan gunakan predikat filepath() untuk menghilangkan partisi selama waktu jalan.

SELECT
    r.filepath(1) AS [year],
    r.filepath(2) AS [month],
    COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
    BULK '/data/year=*/month=*/*.parquet',
    DATA_SOURCE = 's3_ds',
    FORMAT = 'PARQUET'
) WITH (
    customer_id INT,
    amount DECIMAL(10, 2)
) AS [r]
WHERE
    r.filepath(1) = '2025'
    AND r.filepath(2) = '01'
GROUP BY
    r.filepath(1),
    r.filepath(2);

Pendekatan ini menggabungkan eliminasi partisi tingkat folder dengan pushdown tingkat file parquet untuk performa kueri yang optimal. Untuk tutorial lengkap tentang mengkueri file parket dengan pola folder, lihat Virtualisasi file parket dalam penyimpanan objek yang kompatibel dengan S3 dengan PolyBase.

Contoh

Dorong paksa

SELECT * FROM [dbo].[SensorData]
WHERE Speed > 65
OPTION (FORCE EXTERNALPUSHDOWN);

Menonaktifkan pushdown

SELECT * FROM [dbo].[SensorData]
WHERE Speed > 65
OPTION (DISABLE EXTERNALPUSHDOWN);

Konten terkait

• Virtualisasi data dengan PolyBase di SQL Server
• Cara mengetahui apakah pushdown eksternal terjadi
• Mengonfigurasi PolyBase untuk mengakses data eksternal di penyimpanan objek yang kompatibel dengan S3
• Memvirtualisasi file parquet dalam penyimpanan objek yang kompatibel dengan S3 menggunakan PolyBase