PolyBase でのプッシュダウン計算

[アーティクル]
01/16/2024

適用対象: SQL Server 2016 (13.x) 以降のバージョン

プッシュダウン計算を使用すると、外部データソースに対するクエリのパフォーマンスが向上します。 SQL Server 2016 (13.x) 以降では、プッシュダウン計算は Hadoop の外部データソースで使用できました。 SQL Server 2019 (15.x) で、他の種類の外部データソースのプッシュダウン計算が導入されました。

Note

PolyBase プッシュダウン計算によりクエリに対するベネフィットがあるかどうかを判断するには、「外部プッシュダウンが発生した場合の確認方法」を参照してください。

プッシュダウン計算を有効にする

次の記事には、特定の種類の外部データソース用のプッシュダウン計算の構成に関する情報が含まれています。

この表には、さまざまな外部データソースに対するプッシュダウン計算のサポートがまとめてあります。

データソース	結合	プロジェクション	集計	フィルター	統計
汎用 ODBC	はい	はい	はい	はい	はい
Oracle	はい	はい	はい	はい	はい
SQL Server	はい	はい	はい	はい	はい
Teradata	はい	はい	はい	はい	はい
MongoDB*	いいえ	はい	あり***	あり***	はい
Hadoop	いいえ	はい	一部**	一部**	はい
Azure Blob Storage	いいえ	いいえ	いいえ	いいえ	はい

* Azure Cosmos DB プッシュダウンサポートは MongoDB 用 Cosmos DB API 経由で有効にします。

** 「プッシュダウン計算と Hadoop プロバイダー」を参照してください。

*** SQL Server 2019 用 MongoDB ODBC コネクタの集計とフィルターのプッシュダウンサポートは、SQL Server 2019 CU18 で導入されました。

Note

T-SQL 構文によってプッシュダウン計算はブロックできます。詳細については、「プッシュダウンを防ぐ構文」を参照してください。

プッシュダウン計算と Hadoop プロバイダー

PolyBase は現在、Hortonworks Data Platform (HDP) と Cloudera Distributed Hadoop (CDH) の 2 種類の Hadoop プロバイダーをサポートしています。プッシュダウン計算の観点からは、この 2 つの機能に違いはありません。

Hadoop で計算プッシュダウン機能を使用するには、ターゲットの Hadoop クラスターに、ジョブの履歴サーバーが有効になっている HDFS のコアコンポーネントの YARN と MapReduce がある必要があります。 PolyBase から MapReduce 経由でプッシュダウンクエリを送信し、ジョブの履歴サーバーからステータスをプルします。いずれかのコンポーネントがない場合、クエリは失敗します。

集計によっては、データが SQL Server に到達した後に実行される必要があります。ただし、集計の一部は、Hadoop で発生します。これは、超並列処理システムで一般的な集計の計算方法です。

Hadoop プロバイダーでは、次の集計とフィルターがサポートされます。

集計	フィルター (バイナリの比較)
Count_Big	NotEqual
SUM	LessThan
Avg	LessOrEqual
最大	GreaterOrEqual
分	GreaterThan
Approx_Count_Distinct	等しい
	IsNot

プッシュダウン計算の主な有益シナリオ

PolyBase プッシュダウン計算を使用すれば、計算タスクを外部データソースに委任できます。これにより、SQL Server インスタンス上のワークロードが軽減されるので、パフォーマンスが大幅に向上する可能性があります。

SQL Server では、リモートコンピューティングを利用してネットワーク経由で送信されるデータを制限するために、結合、プロジェクション、集計、およびフィルターを外部データソースにプッシュすることができます。

結合のプッシュダウン

多くの場合、PolyBase を使用すると、同じ外部データソース上の 2 つの外部テーブルを結合する結合演算子のプッシュダウンが容易になり、パフォーマンスが大幅に向上します。

外部データソースで結合を行うことができる場合、これによりデータ移動の量が減少し、クエリのパフォーマンスが向上します。結合プッシュダウンを使用しない場合は、結合対象のテーブルからのデータを 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 という 2 列のデータのみが SQL Server にコピーされるようにします。

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

基本的な式と演算子のプッシュダウン

SQL Server では、述語のプッシュダウンに次の基本的な式と演算子を使用できます。

数値、日付値、時間値の 2 項比較演算子 (<、>、=、!=、<>、>=、<=)。
算術演算子 (+、-、*、/、%)。
論理演算子 (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 にプッシュできる 2 項演算子と論理演算子とで構成されます。 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

FORMAT および TRIM 構文のプッシュダウンサポートは、SQL Server 2019 (15.x) CU10 で導入されました。

変数を含むフィルター句

フィルター句で変数を指定する場合、既定では、フィルター句のプッシュダウンが防止されます。たとえば、次のクエリを実行した場合、フィルター句はプッシュダウンされません。

DECLARE @BusinessEntityID INT

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

変数のプッシュダウンを実現するには、クエリオプティマイザーの修正プログラム機能を有効にする必要があります。これは、次のいずれかの方法で実行できます。

インスタンスレベル: インスタンスの起動時のパラメーターとしてトレースフラグ 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 ファイルのサポートが導入されました。 SQL Server では、Parquet を使用してプッシュダウンを実行するときに、行と列の両方の削除を実行できます。 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)
バイナリ
DateTime2 (既定および 7 桁の有効桁数)
日
時刻 (既定および 7 桁の有効桁数)
数値 *

* パラメーターの小数点以下桁数が列の小数点以下桁数と一致する場合、またはパラメーターが明示的に 10 進数にキャストされる場合にサポートされます。

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 によるデータ仮想化の概要」を参照してください