PolyBase 中的下推計算

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

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

注意

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

啟用下推計算

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

• 在 Hadoop 中啟用下推計算
• 設定 PolyBase 存取 Oracle 中的外部資料
• 設定 PolyBase 存取 Teradata 中的外部資料
• 設定 PolyBase 存取 MongoDB 中的外部資料
• 設定 PolyBase 以存取具有 ODBC 泛型型別的外部資料
• 設定 PolyBase 存取 SQL Server 中的外部資料

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

資料來源	聯結	投影	聚合	篩選器	統計資料
一般 ODBC	Yes	Yes	Yes	Yes	Yes
Oracle	是+	Yes	Yes	Yes	Yes
SQL 伺服器	Yes	Yes	Yes	Yes	Yes
Teradata	Yes	Yes	Yes	Yes	Yes
MongoDB*	否	Yes	是***	是***	Yes
Hadoop	否	Yes	一些**	一些**	Yes
Azure Blob 儲存體	否	否	否	否	Yes

* 透過適用於 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	不等於
總和	LessThan
平均	小於或等於
最大值	大於或等於
最小值	GreaterThan
Approx_Count_Distinct	是
	IsNot

下推計算的主要優點案例

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

SQL Server 可以向外部資料來源推送連接、投影、聚合與篩選，利用遠端運算並限制網路傳送的資料。

聯結運算下推

當您將兩個外部資料表在相同的外部資料來源上進行連接時，PolyBase 可以協助下推連接運算子，從而大幅提升效能。

當外部資料來源執行連接時，會減少資料移動並提升查詢效能。 若沒有連接推下，SQL Server 必須將兩個資料表的資料帶入 tempdb 本地，然後執行連接。

在 分散式加入 （將本地資料表與外部資料表連接）的情況下，除非你的過濾器適用於已加入的外部資料表，否則 SQL Server 必須將外部資料表的所有資料帶入 tempdb 本地才能執行連接操作。 例如，以下查詢對外部資料表的連接條件沒有過濾，導致讀取外部資料表中的所有資料。

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

由於 join 使用 E.id 外部資料表的欄位，當你在該欄位加入篩選條件時，SQL Server 可以推送過濾器，減少從外部資料表讀取的資料列數。

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 會啟動 MapReduce 作業，以預處理 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 是最終表達式。

藉由這樣的谓词組合，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 語法的下推支援。

含有變數的篩選子句

當你在篩選子句中指定變數時，SQL Server 預設不會推送該篩選子句。 例如，以下查詢不會下推篩選條件：

DECLARE @BusinessEntityID INT

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

要啟用變數的下推，請使用以下方法之一啟用查詢優化器的快速修正功能：

• 實例層級：啟用追蹤旗標 4199 作為實例的啟動參數。
• 資料庫層級：在包含 PolyBase 外部物件的資料庫中，執行 ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON。
• 查詢層級：使用 query hint OPTION (QUERYTRACEON 4199) 或 OPTION (USE HINT ('ENABLE_QUERY_OPTIMIZER_HOTFIXES'))。

此限制適用於sp_executesql的執行及filter子句中的某些函式。

SQL Server 2019 CU5 首次引入了變數下推的功能。

更多資訊請參見 sp_executesql。

定序衝突

Pushdown 可能無法處理有不同排序規則的資料。 COLLATE 之類的運算子也會干擾成果。 SQL Server 支援等值排序或二進位排序。 如需詳細資訊，請參閱如何判斷是否發生外部下推。

Parquet 檔案的下推機制

從 SQL Server 2022 (16.x) 開始，PolyBase 提供對 parquet 檔案的支援。 當使用 parquet 進行下推時，SQL Server 能夠同時執行資料列和資料行排除。

支援的外部資料來源

以下外部資料來源支援 Parquet pushdown：

• S3 相容的物件儲存
• Azure Blob 儲存體
• Azure Data Lake Storage Gen2

關於配置細節，請參見：

• 設定 PolyBase 以存取 S3 相容物件儲存體中的外部資料
• 使用 PolyBase 將 S3 相容物件儲存體中的 parquet 檔案虛擬化

下壓作業

SQL Server 可以透過 parquet 檔案推送這些操作：

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

以下項目防止拼花銼的向下推：

• 虛擬欄位。
• 欄位比較。
• 參數類型轉換。

支援的資料類型

• bit
• tinyint
• smallint
• bigint
• 真實
• 浮動
• varchar （Bin2Collation、CodePageConversion、BinCollation）
• nvarchar （Bin2Collation，BinCollation）
• binary
• DateTime2 （預設且為7位數精確度）
• date
• 時間 （預設且為7位數精確度）
• 數字 *

* 當參數縮放與欄位縮放對齊，或參數明確鑄造為 十進位時，支援此功能。

防止 Parquet 下推的資料類型

• 錢
• smallmoney
• datetime
• smalldatetime

使用資料夾結構消除分割區

PolyBase 可使用資料夾結構來消除分割區，減少查詢時掃描的資料量。 當你將parquet檔案組織在階層式資料夾中（例如按年、月份或其他分割鍵）時，PolyBase可以跳過與查詢條件不符的整個資料夾。

例如，如果你將資料結構化為：

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

你可以在表格中或外部表格位置使用通配字 OPENROWSET 查詢特定分割區：

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

為了消除動態資料夾，查詢更廣泛的資料夾路徑，並使用 filepath() 謂詞在執行時消除分割區：

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

此方法結合資料夾層級分割消除與 Parquet 檔案層級下推，以達到最佳查詢效能。 欲完整了解如何查詢使用資料夾模式的 parquet 檔案，請參見 在 S3 相容的物件存儲中使用 PolyBase 虛擬化 parquet 檔案。

範例

強制下推

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

停用推送

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

相關內容

• PolyBase 概觀
• 如何判斷是否發生外部下推
• 設定 PolyBase 以存取 S3 相容物件儲存體中的外部資料
• 使用 PolyBase 將 S3 相容物件儲存體中的 parquet 檔案虛擬化