SQL Server 2022 の PolyBase の概要

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

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

この記事では、SQL Server 2022 (16.x) で PolyBase を使用して複数のフォルダーとファイルを操作するチュートリアルについて説明します。この一連のチュートリアルクエリは、PolyBase のさまざまな機能を示します。

SQL Server の PolyBase を使用したデータ仮想化では、メタデータファイル関数を利用して、複数のフォルダー、ファイルに対してクエリを実行したり、フォルダーの削除を実行したりできます。スキーマ検出とフォルダーやファイルの削除の組み合わせは、SQL が Azure Storage アカウントまたは S3 互換オブジェクトストレージソリューションから必要なデータのみをフェッチできるようにする強力な機能です。

前提条件

このチュートリアルで PolyBase を使用する前に、次の操作を行う必要があります。　

Windows に PolyBase をインストールするか、Linux に PolyBase をインストールします。
必要に応じて、sp_configure で PolyBase を有効にします。
外部ネットワークアクセスを許可し、pandemicdatalake.blob.core.windows.net および azureopendatastorage.blob.core.windows.net で一般公開されている Azure Blob ストレージにアクセスします。

[サンプルデータセット]

データの仮想化が初めてで、機能をすばやくテストしたい場合は、匿名アクセスを許可する Bing COVID-19 データセットなど、Azure Open Datasets で利用可能なパブリックデータセットをクエリすることから始めます。

次のエンドポイントを使用して、Bing COVID-19 データセットにクエリを実行します。

Parquet: abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet
CSV: abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv

クイックスタートでは、この単純な T-SQL クエリを実行して、データセットに関する最初の分析情報を取得します。このクエリでは、OPENROWSET を使用して、公開されているストレージアカウントに保存されているファイルをクエリします。

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

最初のクエリの結果セットに基づいて、WHERE、GROUP BY およびその他の T-SQL 句を追加することで、データセットの探索を続行できます。

SQL Server インスタンスで最初のクエリが失敗した場合、パブリック Azure ストレージアカウントへのネットワークアクセスが阻止されている可能性があります。クエリを続行する前に、ネットワークの専門家に相談しアクセスを有効にしてください。

パブリックデータセットのクエリに慣れたら、資格情報の提供、アクセス権の付与、ファイアウォール規則の構成を必要とする非パブリックデータセットへの切り替えを検討してください。多くの実世界のシナリオでは、主にプライベートデータセットを操作します。

外部データソース

外部データソースは、複数のクエリ間でファイルの場所を簡単に参照できるように抽象化したものです。パブリックの場所に対してクエリを実行する場合、外部データソースの作成時に指定する必要があるのはファイルの場所だけです。

CREATE EXTERNAL DATA SOURCE MyExternalDataSource 
WITH ( 
    LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest' 
);

Note

エラーメッセージ 46530 が表示された場合は、External data sources are not supported with type GENERIC, は SQL Server インスタンスの構成オプションPolyBase Enabled をチェックします。 1 になっている必要があります。

SQL Server インスタンスで PolyBase を有効にするには、次を実行します。

EXEC sp_configure @configname = 'polybase enabled', @configvalue = 1;
RECONFIGURE;

非パブリックストレージアカウントにアクセスする場合は、場所と共に、カプセル化された認証パラメーターを使用してデータベーススコープの資格情報を参照する必要もあります。次のスクリプトは、ファイルパスを指し、データベーススコープの資格情報を参照する外部データソースを作成します。

--Create external data source pointing to the file path, and referencing database-scoped credential: 
CREATE EXTERNAL DATA SOURCE MyPrivateExternalDataSource 
WITH ( 
    LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest' 
        CREDENTIAL = [MyCredential]);

OPENROWSET を使用してデータソースのクエリを実行する

OPENROWSET 構文を使用すると、必要な最小限の数のデータベースオブジェクトだけを作成しながら、アドホッククエリをすぐに実行できます。

OPENROWSET は、外部ファイル形式と外部テーブル自体を必要とする外部テーブルアプローチとは対照的に、外部データソース（および場合によっては資格情報）を作成するだけで済みます。

DATA_SOURCEパラメーター値が BULK パラメーターの前に自動的に付加され、ファイルへの完全なパスが形成されます。

OPENROWSETを使用する場合は、次の例のように、ファイルの形式を指定して1 つのファイルに対してクエリを実行します。

SELECT TOP 10 * 
FROM OPENROWSET( 
 BULK 'bing_covid-19_data.parquet', 
 DATA_SOURCE = 'MyExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows;

複数のファイルおよびフォルダーに対してクエリを実行する

この OPENROWSET コマンドを使用すると、BULK パスでワイルドカードを使用して複数のファイルまたはフォルダーに対してクエリを実行することもできます。

次の例では、 NYC の黄色いタクシーの旅行レコードのオープンデータセットを使用します。

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

--Create the data source first
CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource 
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

これで、フォルダー内の .parquet 拡張子を持つすべてのファイルをクエリできます。たとえば、ここでは、名前パターンに一致するファイルのみをクエリします。

SELECT TOP 10 * 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows;

複数のファイルまたはフォルダーに対してクエリを実行する場合、1 つのOPENROWSETでアクセスされるファイルはすべて、同じ構造 (同じ数の列やデータ型など) を持っている必要があります。フォルダーを再帰的に走査することはできません。

スキーマ推論

スキーマの自動推論を使用すると、ファイルスキーマを知らなくてもクエリをすばやく作成し、データを探索することができます。スキーマ推論は parquet ファイルでのみ機能します。

便利ではありますが、推論されるデータ型は、適切なデータ型が使用されていることを確認するのに十分な情報がソースファイルに存在する可能性があるため、実際のデータ型よりも大きくなる可能性があります。これにより、クエリのパフォーマンスが低下する可能性があります。たとえば、Parquet ファイルは最大文字列長のメタデータを含まず、インスタンスはこれを varchar (8000) として推論します。

次の例のように sys.sp_describe_first_results_set ストアドプロシージャを使用して、クエリの結果のデータ型を確認します。

EXEC sp_describe_first_result_set N'
 SELECT 
 vendorID, tpepPickupDateTime, passengerCount 
 FROM 
 OPENROWSET( 
  BULK ''yellow/*/*/*.parquet'', 
  DATA_SOURCE = ''NYCTaxiExternalDataSource'', 
  FORMAT=''parquet'' 
 ) AS nyc';

データ型を確認したら、 WITH 句を使用してデータ型を指定し、パフォーマンスを向上させることができます。

SELECT TOP 100
 vendorID, tpepPickupDateTime, passengerCount 
FROM 
 OPENROWSET( 
  BULK 'yellow/*/*/*.parquet', 
  DATA_SOURCE = 'NYCTaxiExternalDataSource', 
  FORMAT='PARQUET' 
 ) 
WITH ( 
 vendorID varchar(4), -- we're using length of 4 instead of the inferred 8000 
 tpepPickupDateTime datetime2, 
 passengerCount int 
 ) AS nyc;

CSV ファイルのスキーマは自動的に決定できないので、WITH 句を使用して常に列を指定する必要があります。

SELECT TOP 10 id, updated, confirmed, confirmed_change 
FROM OPENROWSET( 
 BULK 'bing_covid-19_data.csv', 
 DATA_SOURCE = 'MyExternalDataSource', 
 FORMAT = 'CSV', 
 FIRSTROW = 2 
) 
WITH ( 
 id int, 
 updated date, 
 confirmed int, 
 confirmed_change int 
) AS filerows;

ファイルメタデータ関数

複数のファイルまたはフォルダーに対してクエリを実行する場合は、filepath() と filename() 関数を使用して、ファイルメタデータを読み取り、結果セット内の行の元のファイルのパスの一部または完全なパスと名前を取得できます。次の例では、各行のすべてのファイルとプロジェクトファイルのパスとファイル名の情報をクエリします。

--Query all files and project file path and file name information for each row: 

SELECT TOP 10 filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder], 
filerows.filename() as [File_name], filerows.filepath() as [Full_Path], * 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet') AS filerows; 

--List all paths: 
SELECT DISTINCT filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder] 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet') AS filerows;

パラメーターを指定せずに呼び出した場合、filepath()関数は行の元となるファイルパスを返します。 DATA_SOURCEがOPENROWSETで使用されている場合はDATA_SOURCEからの相対パスを返し、そうでない場合はファイルの完全なパスを返します。
filepath() 関数は、パラメーターを指定して呼び出すと、パラメーターで指定した位置にあるワイルドカードと一致するパスの一部を返します。たとえば、最初のパラメーター値は、最初のワイルドカードと一致するパスの一部を返します。

また、filepath()関数は行のフィルタリングや集計に使用することができます。

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

OPENROWSET の上にビューを作成する

OPENROWSET クエリをラップするビューを作成することで、基盤となるクエリを簡単に再利用できます。またビューにより、Power BI のようなレポートや分析ツールで OPENROWSET 結果を使用することもできます。

たとえば、OPENROWSET コマンドに基づいて次のビューを考えてみましょう。

CREATE VIEW TaxiRides AS 
SELECT * 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows;

また、filepath() 関数を使用して、ファイルの場所データを含む列をビューに追加して、より簡単でパフォーマンスの高いフィルタリングを行うことができます。ビューを使用すると、これらの列のいずれかでフィルタリングされたときに、ビューの上部にあるクエリが読み取って処理する必要のあるファイルの数とデータの量を減らすことができます。

CREATE VIEW TaxiRides AS 
SELECT * 
 , filerows.filepath(1) AS [year] 
 , filerows.filepath(2) AS [month] 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows;

外部テーブル

外部テーブルはファイルへのアクセスをカプセル化し、ユーザーテーブルに格納されたローカルリレーショナルデータへのクエリとほぼ同じ操作性を実現します。外部テーブルを作成するには、外部データソースと外部ファイル形式オブジェクトが存在する必要があります。

--Create external file format 
CREATE EXTERNAL FILE FORMAT DemoFileFormat 
WITH ( 
 FORMAT_TYPE=PARQUET 
) 
GO 
 
--Create external table: 
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 
) 
WITH ( 
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = NYCTaxiExternalDataSource, 
 FILE_FORMAT = DemoFileFormat 
);

外部テーブルが作成されると、他のテーブルと同様にクエリを実行できます。

SELECT TOP 10 * 
FROM tbl_TaxiRides;

OPENROWSETと同様に、外部テーブルを使用すると、ワイルドカードを使用して複数のファイルとフォルダーのクエリを実行できます。スキーマ推論は、外部テーブルではサポートされていません。

外部データソース

さまざまなデータソースへの外部データソースと外部テーブルの作成に関するその他のチュートリアルについては、「PolyBase Transact-SQL リファレンス」を参照してください。

さまざまな外部データソースに関するチュートリアルについては、以下を参照してください。

SQL Server 2022 の PolyBase の概要

前提条件

[サンプルデータセット]

外部データソース

OPENROWSET を使用してデータソースのクエリを実行する

複数のファイルおよびフォルダーに対してクエリを実行する

スキーマ推論

ファイルメタデータ関数

OPENROWSET の上にビューを作成する

外部テーブル

外部データソース

フィードバック

フィードバック

その他のリソース

SQL Server 2022 の PolyBase の概要

前提条件

[サンプル データ セット]

外部データ ソース

OPENROWSET を使用してデータ ソースのクエリを実行する

複数のファイルおよびフォルダーに対してクエリを実行する

スキーマ推論

ファイル メタデータ関数

OPENROWSET の上にビューを作成する

外部テーブル

外部データ ソース

関連するコンテンツ

フィードバック

フィードバック

その他のリソース

[サンプルデータセット]

外部データソース

OPENROWSET を使用してデータソースのクエリを実行する

ファイルメタデータ関数

外部データソース