PolyBase でのプッシュダウン計算
適用対象: SQL Server 2016 (13.x) 以降のバージョン
プッシュダウン計算を使用すると、外部データ ソースに対するクエリのパフォーマンスが向上します。 SQL Server 2016 (13.x) 以降では、プッシュダウン計算は Hadoop の外部データ ソースで使用できました。 SQL Server 2019 (15.x) で、他の種類の外部データ ソースのプッシュダウン計算が導入されました。
Note
PolyBase プッシュダウン計算によりクエリに対するベネフィットがあるかどうかを判断するには、「外部プッシュダウンが発生した場合の確認方法」を参照してください。
プッシュダウン計算を有効にする
次の記事には、特定の種類の外部データ ソース用のプッシュダウン計算の構成に関する情報が含まれています。
- Enable pushdown computation in Hadoop (Hadoop でのプッシュダウン計算を有効にする)
- Oracle 上の外部データにアクセスするための PolyBase の構成
- Teradata 上の外部データにアクセスするための PolyBase の構成
- MongoDB 上の外部データにアクセスするための PolyBase の構成
- ODBC ジェネリック型の外部データにアクセスするための PolyBase の構成
- SQL Server 上の外部データにアクセスするための 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 で導入されました。
+ Oracle では結合のプッシュダウンがサポートされていますが、プッシュダウンを実現するには、結合列の統計を作成する必要がある場合があります。
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 |
Max | 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
- 実績
- 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 によるデータ仮想化の概要」を参照してください
- 外部プッシュダウンが発生した場合の確認方法