Bagikan melalui


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:

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