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

適用対象: 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 を実行します。
• 複数列の統計はサポートされていません。

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

適用対象: Azure SQL Managed Instance、Azure Synapse Analytics。

データがフォルダまたはファイル (通称パーティション) に編成されている場合は、パーティションの削除を使用して、特定のフォルダとファイルのみをクエリします。 パーティションの削除により、クエリで読み込んで処理する必要があるファイル数とデータ量が減り、パフォーマンスが向上します。

クエリの WHERE 句でメタデータ関数 filepath() を使用して、クエリの実行からパーティションを削除できます。

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

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 ノードの間の並列データ転送が可能になります。また、外部データに対する操作のためのコンピューティング リソースが追加されます。

関連するコンテンツ

• PolyBase のよく寄せられる質問
• SQL Server の PolyBase を使用したデータ仮想化