Komputasi pushdown di PolyBase

Artikel
05/17/2024

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:

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.

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

Dalam kasus gabungan terdistribusi (menggabungkan tabel lokal ke tabel eksternal), kecuali ada filter pada tabel eksternal yang digabungkan, 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 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. AND customer.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 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 menentukan variabel dalam klausa filter, secara default ini 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: Menggunakan petunjuk OPTION (QUERYTRACEON 4199) kueri 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.

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

Konflik kolabasi

Pushdown mungkin tidak dimungkinkan dengan data dengan kolase yang berbeda. 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. 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)
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

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

Untuk informasi selengkapnya tentang PolyBase, lihat Memperkenalkan virtualisasi data dengan PolyBase
Cara mengetahui apakah pushdown eksternal terjadi

Bagikan melalui

Komputasi pushdown di PolyBase

Mengaktifkan komputasi pushdown

Komputasi pushdown dan penyedia Hadoop

Skenario utama yang bermanfaat dari komputasi pushdown

Pushdown gabungan

Pilih subset baris

Pilih subset kolom

Pushdown untuk ekspresi dan operator dasar

Fungsi yang didukung untuk pushdown

Sintaksis yang mencegah pushdown

Klausa filter dengan variabel

Konflik kolabasi

Pushdown untuk file parket

Tipe data yang didukung

Jenis data yang mencegah pushdown parke

Contoh

Dorong paksa

Menonaktifkan pushdown

Saran dan Komentar

Sumber Daya Tambahan:

Bagikan melalui

Komputasi pushdown di PolyBase

Mengaktifkan komputasi pushdown

Komputasi pushdown dan penyedia Hadoop

Skenario utama yang bermanfaat dari komputasi pushdown

Pushdown gabungan

Pilih subset baris

Pilih subset kolom

Pushdown untuk ekspresi dan operator dasar

Fungsi yang didukung untuk pushdown

Sintaksis yang mencegah pushdown

Klausa filter dengan variabel

Konflik kolabasi

Pushdown untuk file parket

Tipe data yang didukung

Jenis data yang mencegah pushdown parke

Contoh

Dorong paksa

Menonaktifkan pushdown

Konten terkait

Saran dan Komentar

Sumber Daya Tambahan: