SQL Server 用 PolyBase のパフォーマンスに関する考慮事項

[アーティクル]
02/12/2024

適用対象: SQL Server 2016 (13.x) - Windows 以降のバージョンSQL Server 2017 (14.x) - Linux 以降のバージョンAzure Synapse Analytics

SQL Server用PolyBase において、クエリできるファイル数やデータ量に厳密な制限はありません。クエリのパフォーマンスは、データ量、データ形式、データの編成方法、およびクエリと結合の複雑さによって異なります。

この記事では、パフォーマンスに関する重要なトピックとガイダンスについて説明します。　

統計

外部データの統計を収集することは、クエリの最適化のために実行できる最も重要なことの1つです。インスタンスがデータについて知れば知るほど、クエリーを高速に実行することができます。 SQL エンジンのクエリオプティマイザーは、コストベースのオプティマイザーです。オプティマイザーでは、さまざまなクエリプランのコストが比較されて、最も低コストのプランが選択されます。多くの場合、最も高速に実行されるプランが選択されます。

統計の自動作成

SQL Server 2022 以降では、データベースエンジンは、受信したユーザークエリを分析して、欠落している統計がないかを調べます。統計が足りない場合、クエリプランに対するカーディナリティ評価を改善するために、クエリオプティマイザーによって、クエリ述語または結合条件内の個々の列に関して統計が自動的に作成されます。統計の自動作成は同期的に行われるため、列に統計がない場合、クエリのパフォーマンスが多少低下する可能性があります。 1 つの列の統計を作成する時間は、ターゲットとされるファイルのサイズに依存します。

OPENROWSET の手動統計を作成する

OPENROWSET パスの単一列統計は、sys.sp_create_openrowset_statistics ストアドプロシージャを使用して、パラメーターとして 1 つの列を含む選択クエリを渡すことで作成できます。

EXEC sys.sp_create_openrowset_statistics N' 
SELECT pickup_datetime 
FROM OPENROWSET( 
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'', 
 FORMAT = ''parquet'') AS filerows';

デフォルトでは、インスタンスはデータセットに指定されたデータの 100% を使用して統計を作成します。オプションで TABLESAMPLE オプションを使用して、サンプルサイズをパーセンテージとして指定できます。複数の列の単一列統計を作成するには、各列の sys.sp_create_openrowset_statistics を実行します。 OPENROWSET パスの複数列統計を作成することはできません。

既存の統計を更新するには、まずsys.sp_drop_openrowset_statisticsストアドプロシージャを使用して削除し、sys.sp_create_openrowset_statisticsを使用して再作成します。

EXEC sys.sp_drop_openrowset_statistics 
N'SELECT pickup_datetime 
FROM OPENROWSET( 
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'', 
 FORMAT = ''parquet'') AS filerows 
';

外部テーブルの手動統計を作成する

外部テーブルに統計を作成するための構文は、通常のユーザーテーブルに使用される構文に似ています。列の統計を作成するには、統計オブジェクトの名前と列の名前を指定します。

CREATE STATISTICS sVendor 
ON tbl_TaxiRides (vendorID) 
WITH FULLSCAN, NORECOMPUTE;

WITH オプションは必須であり、サンプルサイズの場合、FULLSCAN と SAMPLE n PERCENT のオプションが許可されています。

複数の列の単一列統計を作成するには、各列の CREATE STATISTICS を実行します。
複数列の統計はサポートされていません。

パーティション分割されたデータに対してクエリを実行する

データは、多くの場合、パーティションとも呼ばれるサブフォルダーに整理されます。特定のフォルダーやファイルに対してのみクエリを実行するように、SQL Server インスタンスに指示することができます。そうすれば、クエリで読み込んで処理する必要があるファイルの数とデータの量が減り、パフォーマンスが向上します。この種類のクエリ最適化は、パーティションプルーニングまたは パーティションの削除 と呼ばれます。クエリの filepath() 句でメタデータ関数 WHERE を使用して、クエリの実行からパーティションを削除できます。

まず、外部データソースを作成する。

CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net'
);
GO

次のクエリサンプルでは、2017 年の過去 3 か月間についてのみ、NYC イエロータクシーのデータファイルが読み取られます。

SELECT 
    r.filepath() AS filepath 
    ,r.filepath(1) AS [year] 
    ,r.filepath(2) AS [month] 
    ,COUNT_BIG(*) AS [rows] 
FROM OPENROWSET( 
        BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
        DATA_SOURCE = 'NYCTaxiExternalDataSource', 
        FORMAT = 'parquet' 
    ) 
WITH ( 
    vendorID INT 
) AS [r] 
WHERE 
    r.filepath(1) IN ('2017') 
    AND r.filepath(2) IN ('10', '11', '12') 
GROUP BY 
    r.filepath() 
    ,r.filepath(1) 
    ,r.filepath(2) 
ORDER BY filepath;

格納されているデータがパーティション分割されていない場合は、クエリのパフォーマンスを向上させるためにパーティション分割することを検討してください。

外部テーブルを使用している場合、関数 filepath() と filename() はサポートされていますが、WHERE 句ではサポートされていません。計算列で使用する場合は、filename と filepath でフィルター処理できます。この動作を次の例で示します。

CREATE EXTERNAL TABLE tbl_TaxiRides ( 
 vendorID VARCHAR(100) COLLATE Latin1_General_BIN2, 
 tpepPickupDateTime DATETIME2, 
 tpepDropoffDateTime DATETIME2, 
 passengerCount INT, 
 tripDistance FLOAT, 
 puLocationId VARCHAR(8000), 
 doLocationId VARCHAR(8000), 
 startLon FLOAT, 
 startLat FLOAT, 
 endLon FLOAT, 
 endLat FLOAT, 
 rateCodeId SMALLINT, 
 storeAndFwdFlag VARCHAR(8000), 
 paymentType VARCHAR(8000), 
 fareAmount FLOAT, 
 extra FLOAT, 
 mtaTax FLOAT, 
 improvementSurcharge VARCHAR(8000), 
 tipAmount FLOAT, 
 tollsAmount FLOAT, 
 totalAmount FLOAT, 
 [Year]  AS CAST(filepath(1) AS INT), --use filepath() for partitioning 
 [Month]  AS CAST(filepath(2) AS INT) --use filepath() for partitioning 
) 
WITH ( 
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = NYCTaxiExternalDataSource, 
 FILE_FORMAT = DemoFileFormat 
); 
GO 
 
SELECT * 
      FROM tbl_TaxiRides 
WHERE 
      [year]=2017             
      AND [month] in (10,11,12);

Hadoop に計算をプッシュする

適用対象 SQL Server 2016 (13.x)、SQL Server 2017 (14.x)、SQL Server 2019 (15.x) のみ

PolyBase により、クエリ全体を最適化するために計算の一部が外部ソースにプッシュされます。クエリオプティマイザーでは、クエリのパフォーマンスが向上する場合は Hadoop への計算のプッシュを行うためのコストベースの決定が行われます。クエリオプティマイザーでは、コストベースの決定に外部テーブルの統計が使用されます。計算のプッシュでは、MapReduce ジョブが作成され、Hadoop の分散コンピューティングリソースが活用されます。詳細については、「PolyBase でのプッシュダウン計算」を参照してください。

コンピューティングリソースをスケーリングする

適用対象 SQL Server 2016 (13.x)、SQL Server 2017 (14.x)、SQL Server 2019 (15.x) のみ

クエリのパフォーマンスを向上させるために、SQL Server PolyBase スケールアウトグループを使用できます。これにより、SQL Server インスタンスと Hadoop ノードの間の並列データ転送が可能になります。また、外部データに対する操作のためのコンピューティングリソースが追加されます。

重要

Microsoft SQL Server PolyBase スケールアウトグループは廃止されます。 SQL Server 2022 (16.x) では、スケールアウトグループ機能が製品から削除されます。 PolyBase データ仮想化は、SQL Server のスケールアップ機能として引き続き完全にサポートされます。詳細については、「Microsoft SQL Server プラットフォームのビッグデータオプション」を参照してください。

SQL Server 用 PolyBase のパフォーマンスに関する考慮事項

統計

統計の自動作成

OPENROWSET の手動統計を作成する

外部テーブルの手動統計を作成する

パーティション分割されたデータに対してクエリを実行する

Hadoop に計算をプッシュする

コンピューティングリソースをスケーリングする

フィードバック

フィードバック

その他のリソース

SQL Server 用 PolyBase のパフォーマンスに関する考慮事項

統計

統計の自動作成

OPENROWSET の手動統計を作成する

外部テーブルの手動統計を作成する

パーティション分割されたデータに対してクエリを実行する

Hadoop に計算をプッシュする

コンピューティング リソースをスケーリングする

関連するコンテンツ

フィードバック

フィードバック

その他のリソース

コンピューティングリソースをスケーリングする