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	No	No	No	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.

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 fitur 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	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 untuk memanfaatkan komputasi jarak jauh dan membatasi data yang dikirim melalui jaringan.

Pushdown gabungan

Dalam banyak kasus, PolyBase dapat memfasilitasi pushdown operator gabungan untuk gabungan dua tabel eksternal pada sumber data eksternal yang sama, yang akan sangat meningkatkan performa.

Jika gabungan dapat dilakukan di sumber data eksternal, ini mengurangi jumlah pergerakan data dan meningkatkan performa kueri. Tanpa pushdown gabungan, data dari tabel yang akan digabungkan harus dibawa secara lokal ke dalam tempdb, lalu digabungkan.

Perhatikan bahwa dalam kasus gabungan terdistribusi (menggabungkan tabel lokal ke tabel eksternal), kecuali ada beberapa kriteria pemfilteran pada tabel eksternal yang diterapkan ke kondisi gabungan, semua data dalam tabel eksternal harus dibawa secara tempdb lokal untuk melakukan operasi gabungan. Misalnya, kueri berikut tidak memiliki pemfilteran pada kondisi gabungan tabel eksternal, yang akan mengakibatkan semua data dari tabel eksternal dibaca.

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

Karena gabungan berada di E.id kolom tabel eksternal, jika kondisi filter ditambahkan ke kolom tersebut, filter dapat didorong ke bawah sehingga 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 sebelumnya 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 dasar dan operator berikut untuk pushdown 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 mungkin didorong ke bawah. Perilaku aktual bergantung pada bagaimana pengoptimal kueri menulis ulang ekspresi operator sebagai serangkaian pernyataan yang 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. ANDcustomer.account_balance AND customer.zipcode masuk logis adalah 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 berikut untuk pushdown 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 atau sintaks T-SQL berikut akan 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

Jika Anda menentukan variabel dalam klausa filter, secara default ini akan mencegah pushdown klausa filter. Misalnya, jika Anda menjalankan kueri berikut, klausa filter tidak akan didorong ke bawah:

DECLARE @BusinessEntityID INT

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

Untuk mencapai pushdown variabel, Anda perlu mengaktifkan fungsionalitas perbaikan pengoptimal kueri. Ini dapat dilakukan dengan salah satu cara 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 kueri OPTION (QUERYTRACEON 4199) atau OPTION (USE HINT ('ENABLE_QUERY_OPTIMIZER_HOTFIXES'))

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

Catatan: Kemampuan untuk mendorong mundur variabel pertama kali diperkenalkan di SQL Server 2019 CU5.

Konflik kolabasi

Saat bekerja dengan data dengan pushdown kolase yang berbeda mungkin tidak dimungkinkan, operator seperti COLLATE juga dapat mengganggu hasilnya. Kolatasi yang sama atau kolater biner didukung. Untuk informasi selengkapnya, lihat Cara mengetahui apakah pushdown 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. Saat bekerja dengan file parket, operasi berikut dapat didorong ke bawah:

• 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 atas kolom.

Kehadiran berikut mencegah pushdown untuk file parket:

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

Tipe data yang didukung

• Bit
• TinyInt
• SmallInt
• Bigint
• Riil
• Float
• VARCHAR (Bin2Collation, CodePageConversion, BinCollation)
• NVARCHAR (Bin2Collation, BinCollation)
• Biner
• DateTime2 (presisi default dan 7 digit)
• Date
• 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

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

Langkah berikutnya

• Untuk informasi selengkapnya tentang PolyBase, lihat Memperkenalkan virtualisasi data dengan PolyBase

Baca juga

• Cara mengetahui apakah pushdown eksternal terjadi