PolyBase 中的下推計算

文章
07/15/2024

適用於：SQL Server 2016 (13.x) 和更新版本

下推計算可改善外部資料來源的查詢效能。從 SQL Server 2016 (13.x) 開始，下推計算即可供 Hadoop 外部資料來源使用。 SQL Server 2019 (15.x) 引入適用於其他外部資料來源類型的下推計算。

注意

若要判斷 PolyBase 下推計算是否有利您的查詢，請參閱如何判斷是否發生外部下推。

啟用下推計算

下列文章包含針對特定外部資料來源類型設定下推計算的資訊：

下表摘要說明不同外部資料來源的下推計算支援：

資料來源	聯結	投影	彙總	篩選器	統計資料
一般 ODBC	是	是	是	是	是
Oracle	是+	是	是	是	是
SQL Server	是	是	是	是	是
Teradata	是	是	是	是	是
MongoDB*	否	是	是***	是***	是
Hadoop	否	是	Some**	Some**	是
Azure Blob 儲存體	No	No	No	No	是

* 透過適用於 MongoDB 的 Azure Cosmos DB API，啟用 Azure Cosmos DB 下推支援。

** 請參閱下推計算和 Hadoop 提供者。

*** SQL Server 2019 CU18 引進了適用於 SQL Server 2019 的 MongoDB ODBC 連接器執行彙總和篩選的下推支援。

+ Oracle 支援聯結的下推，但您可能需要在聯結資料行上建立統計資料，以達到下推。

注意

某些 T-SQL 語法可以封鎖下推計算。如需詳細資訊，請參閱防止下推的語法。

下推計算和 Hadoop 提供者

PolyBase 目前支援兩個 Hadoop 提供者，Hortonworks Data Platform (HDP) 和 Cloudera 分散式 Hadoop (CDH)。在下推計算方面，這兩個功能之間沒有任何差異。

若要與 Hadoop 搭配使用計算下推功能，目標 Hadoop 叢集必須具有 HDFS、YARN 與 MapReduce 的核心元件，並啟用作業記錄伺服器。 PolyBase 透過 MapReduce 來提交下推查詢，並從作業記錄伺服器提取狀態。如果沒有其中一個元件，則查詢會失敗。

部分彙總必須在資料到達 SQL Server 之後執行。但是一部分的彙總會在 Hadoop 中進行。這是大量平行處理系統計算彙總的常見方法。

Hadoop 提供者支援下列彙總和篩選。

彙總	篩選 (二進位比較)
Count_Big	NotEqual
Sum	LessThan
平均	LessOrEqual
最大值	GreaterOrEqual
最小值	GreaterThan
Approx_Count_Distinct	為
	IsNot

下推計算的主要優點案例

使用 PolyBase 下推計算，您可以將計算工作委派給外部資料來源。這可減少 SQL Server 執行個體上的工作負載，且可大幅提升效能。

SQL Server 可以將聯結、投影、彙總和篩選推送至外部資料來源，以利用遠端計算並限制透過網路傳送的資料。

聯結的下推

在許多情況下，PolyBase 可以促進聯結運算子的下推，以便在相同外部資料來源上聯結兩個外部表格，這將會大幅提升效能。

如果聯結可以在外部資料來源完成，這會減少資料移動量，並提升查詢的效能。如果沒有聯結下推，要聯結的資料表中的資料必須置於本機的 tempdb，然後聯結。

如果分散式聯結 (將本機資料表聯結至外部表格)，除非聯結外部表格有篩選，否則外部表格中的所有資料都必須帶入本機 tempdb，才能執行聯結作業。例如，下列查詢沒有外部表格聯結條件的篩選，會導致讀取外部表格的所有資料。

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

由於聯結位於外部表格的資料行 E.id 上，如果篩選條件加入至該資料行，則可以向下推入篩選，以減少從外部表格讀取的資料列數目。

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

選取資料列的子集

使用述詞下推，來改善從外部表格選取資料列子集之查詢的效能。

在此範例中，SQL Server 會起始 map-reduce 工作，以擷取 Hadoop 上符合 customer.account_balance < 200000 述詞的資料列。因為查詢可以順利完成，而不需要掃描資料表中的所有資料列，所以只會將符合述詞準則的資料列複製到 SQL Server。這可以節省大量時間，而且相較於帳戶餘額 > 200000 的客戶數目，客戶餘額數目 < 200000 需要較少的暫存儲存空間。

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

選取資料行的子集

使用述詞下推，來改善從外部表格選取資料行子集之查詢的效能。

在此查詢中，SQL Server 會起始 map-reduce 作業以前置處理 Hadoop 分隔符號文字檔，以便只將 customer.name 和 customer.zip_code 這兩個資料行的資料複製到 SQL Server。

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

基本運算式和運算子的下推

SQL Server 允許述詞下推的下列基本運算式和運算子。

數值、日期和時間值的二進位比較運算子 (<、>、=、!=、<>、>=、<=)。
算術運算子 (+、-、*、/、%)。
邏輯運算子 (AND、OR)。
一元運算子 (NOT、IS NULL、IS NOT NULL)。

運算子 BETWEEN、NOT、IN 和 LIKE 可能會下推。實際的行為取決於查詢最佳化工具如何將運算子運算式重新編寫為一系列使用基本關係運算子的陳述式。

此範例中的查詢有多個可下推到 Hadoop 的述詞。 SQL Server 可以將 map-reduce 工作推送到 Hadoop，以執行 customer.account_balance <= 200000 述詞。 BETWEEN 92656 AND 92677 運算式也是由可推送到 Hadoop 的二進位和邏輯作業組成。 customer.account_balance AND customer.zipcode 中的邏輯 AND 是最終運算式。

藉由這樣的述詞組合，map-reduce 工作就可以執行所有 WHERE 子句。只有符合 SELECT 準則的資料才會複製回 SQL Server。

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

支援的下推函式

SQL Server 允許在述詞下推中使用下列函式。

字串函數

CONCAT
DATALENGTH
LEN
LIKE
LOWER
LTRIM
RTRIM
SUBSTRING
UPPER

數學函數

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

一般函式

COALESCE *
NULLIF

* 與 COLLATE 搭配使用可能會防止在某些情況中下推。如需詳細資訊，請參閱定序衝突。

日期和時間函式

DATEADD
DATEDIFF
DATEPART

防止下推的語法

下列 T-SQL 函式或語法會防止下推計算：

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

SQL Server 2019 (15.x) CU10 建立了 FORMAT 和 TRIM 語法的下推支援。

含有變數的篩選子句

如果您要在篩選子句中指定變數，根據預設，這會防止篩選子句下推。例如，如果您執行下列查詢，篩選子句將不會向下推送：

DECLARE @BusinessEntityID INT

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

若要實現變數下推，您必須啟用查詢最佳化工具 Hotfix 功能。這可以透過下列任一方式來完成：

執行個體層級：啟用追蹤旗標 4199 作為執行個體的啟動參數
資料庫層級：在具有 PolyBase 外部物件的資料庫內容中，執行 ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON
查詢層級：使用查詢提示 OPTION (QUERYTRACEON 4199) 或 OPTION (USE HINT ('ENABLE_QUERY_OPTIMIZER_HOTFIXES'))

這項限制適用於執行 sp_executesql。此限制也適用於篩選子句中某些功能的使用率。

下推變數的功能最早是在 SQL Server 2019 CU5 中引進。

定序衝突

使用不同定序的資料可能無法下推。 COLLATE 之類的運算子也會干擾成果。支援相等定序或二進制定序。如需詳細資訊，請參閱如何辨識是否發生下推。

Parquet 檔案的下推

從 SQL Server 2022 (16.x) 開始，PolyBase 提供對 parquet 檔案的支援。使用 parquet 執行下推時，SQL Server 能夠同時執行資料列和資料行刪除。使用 parquet 檔案時，可以向下推送下列作業：

數值、日期和時間值的二進位比較運算子 (>, >=, <=, <)。
比較運算子的組合 (> AND <、>= AND <、> AND <=、<= AND = >=)。
在清單篩選中 (col1 = val1 OR col1 = val2 OR vol1 = val3)。
資料行的 IS NOT NULL。

存在下列項目可阻止 parquet 檔案下推：

虛擬資料行。
資料行比較。
參數類型轉換。

支援的資料類型

bit
TinyInt
SmallInt
BigInt
Real
Float
VARCHAR (Bin2Collation, CodePageConversion, BinCollation)
NVARCHAR (Bin2Collation, BinCollation)
Binary
DateTime2 (預設值和 7 位精確值)
Date
時間 (預設值和 7 位精確值)
數值 *

* 支援當參數小數位數與資料行小數字數對齊時，或當參數明確轉換成十進位時。

防止 Parquet 下推的資料類型

Money
SmallMoney
Datetime
SmallDateTime

範例

強制下推

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

停用下推

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

如需 PolyBase 的詳細資訊，請參閱介紹使用 PolyBase 進行資料虛擬化
如何判斷是否發生外部下推

分享方式：

PolyBase 中的下推計算

啟用下推計算

下推計算和 Hadoop 提供者

下推計算的主要優點案例

聯結的下推

選取資料列的子集

選取資料行的子集

基本運算式和運算子的下推

支援的下推函式

防止下推的語法

含有變數的篩選子句

定序衝突

Parquet 檔案的下推

支援的資料類型

防止 Parquet 下推的資料類型

範例

強制下推

停用下推

意見反映

更多資源

分享方式：

PolyBase 中的下推計算

啟用下推計算

下推計算和 Hadoop 提供者

下推計算的主要優點案例

聯結的下推

選取資料列的子集

選取資料行的子集

基本運算式和運算子的下推

支援的下推函式

防止下推的語法

含有變數的篩選子句

定序衝突

Parquet 檔案的下推

支援的資料類型

防止 Parquet 下推的資料類型

範例

強制下推

停用下推

相關內容

意見反映

更多資源