Azure Blob Storage 上の外部データにアクセスするように PolyBase を構成する

適用対象: SQL Server (Windows のみ) Azure SQL データベース Azure Synapse Analytics 分析プラットフォームシステム (PDW)

この記事では、SQL Server インスタンスで PolyBase を使用し、Azure Blob Storage 上の外部データに対してクエリを実行する方法について説明します。

前提条件

PolyBase をインストールしていない場合は、「PolyBase のインストール」をご覧ください。 インストールに関する記事では、前提条件について説明します。

SQL Server 2022

SQL Server 2022 (16.x) では、Azure Storage に接続するときに、外部データ ソースを構成して、新しいコネクタを使用します。 次の表は、変更点をまとめたものです。

外部データ ソース	ソース	終了
Azure Blob Storage	wasb[s]	abs
ADLS Gen 2	abfs[s]	adls

Azure Blob Storage の接続を構成する

最初に、Azure Blob Storage を使用するように SQL Server PolyBase を構成します。

1. services.msc を使用して SQL Server を再起動します。 SQL Server を再起動すると、次のサービスが再起動します。

   • SQL Server PolyBase Data Movement Service
   • SQL Server PolyBase エンジン

   

外部テーブルを構成する

Hadoop データ ソース内のデータのクエリを実行するには、Transact-SQL クエリで使用する外部テーブルを定義する必要があります。 次の手順では、外部テーブルを構成する方法を説明します。

1. データベースにマスター キーを作成します。 資格情報シークレットを暗号化するには、マスター キーが必要です。

   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';  
   

2. Shared Access Signature (SAS) を使用して Azure Blob Storage のデータベース スコープ資格情報を作成します。IDENTITY は、使用されないため、何でも指定できます。

   CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
   WITH
   IDENTITY = 'SHARED ACCESS SIGNATURE',
   -- Remove ? from the beginning of the SAS token
   SECRET = '<azure_shared_access_signature>' ;
   

3. CREATE EXTERNAL DATA SOURCE を使用して外部データ ソースを作成します。 WASB[s] コネクタ経由で Azure Storage に接続する場合は、Shared Access Signature (SAS) で認証を行うことに注意してください。

   -- LOCATION:  Azure account storage account name and blob container name.  
   -- CREDENTIAL: The database scoped credential created above.  
   CREATE EXTERNAL DATA SOURCE AzureStorage with (  
         LOCATION ='wasbs://<blob_container_name>@<azure_storage_account_name>.blob.core.windows.net',  
         CREDENTIAL = AzureStorageCredential  
   );  
   

4. CREATE EXTERNAL FILE FORMAT を使用して外部ファイル形式を作成します。

   -- FORMAT TYPE: Type of format in Hadoop (DELIMITEDTEXT,  RCFILE, ORC, PARQUET).
   CREATE EXTERNAL FILE FORMAT TextFileFormat WITH (  
         FORMAT_TYPE = DELIMITEDTEXT,
         FORMAT_OPTIONS (FIELD_TERMINATOR ='|',
               USE_TYPE_DEFAULT = TRUE))  
   

5. CREATE EXTERNAL TABLEを使用して、Azure ストレージに格納されているデータをポイントする外部テーブルを作成します。 この例では、外部データに車両センサー データが含まれています。この例では、以前に存在している必要がないため、LOCATION は、/ ではなく、/Demo/ となります。

   -- LOCATION: path to file or directory that contains the data (relative to HDFS root).  
   CREATE EXTERNAL TABLE [dbo].[CarSensor_Data] (  
         [SensorKey] int NOT NULL,
         [CustomerKey] int NOT NULL,
         [GeographyKey] int NULL,
         [Speed] float NOT NULL,
         [YearMeasured] int NOT NULL  
   )  
   WITH (LOCATION='/Demo/',
         DATA_SOURCE = AzureStorage,  
         FILE_FORMAT = TextFileFormat  
   );  
   

6. 外部テーブルの統計を作成します。

   CREATE STATISTICS StatsForSensors on CarSensor_Data(CustomerKey, Speed)  
   

PolyBase クエリ

PolyBase が適している機能には、次の 3 つがあります。

• 外部テーブルに対するアドホック クエリ。
• データのインポート。
• データのエクスポート。

次のクエリでは、架空の車両センサー データの例を示します。

アドホック クエリ

次のアドホック クエリでは、Hadoop データを結合します。 時速 35 マイルを越えて走行している顧客を選択し、Hadoop に格納されている車両センサー データを使用して SQL Server に格納されている構造化された顧客データに結合します。

SELECT DISTINCT Insured_Customers.FirstName,Insured_Customers.LastName,
       Insured_Customers. YearlyIncome, CarSensor_Data.Speed  
FROM Insured_Customers, CarSensor_Data  
WHERE Insured_Customers.CustomerKey = CarSensor_Data.CustomerKey and CarSensor_Data.Speed > 35
ORDER BY CarSensor_Data.Speed DESC  
OPTION (FORCE EXTERNALPUSHDOWN);   -- or OPTION (DISABLE EXTERNALPUSHDOWN)  

PolyBase を使ったデータのインポート

次のクエリでは、外部データを SQL Server にインポートします。 この例では、高速走行しているドライバーのデータを、さらに詳細な分析を実行するために SQL Server にインポートします。 パフォーマンスを向上させるために、列ストア テクノロジが活用されています。

SELECT DISTINCT
      Insured_Customers.FirstName, Insured_Customers.LastName,   
      Insured_Customers.YearlyIncome, Insured_Customers.MaritalStatus  
INTO Fast_Customers from Insured_Customers INNER JOIN   
(  
      SELECT * FROM CarSensor_Data where Speed > 35   
) AS SensorD  
ON Insured_Customers.CustomerKey = SensorD.CustomerKey  
ORDER BY YearlyIncome  
  
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FastCustomers ON Fast_Customers;  

PolyBase を使ったデータのエクスポート

次のクエリは、SQL Server から Azure Blob Storage にデータをエクスポートします。 まず、PolyBase エクスポートを有効にします。 次に、データをエクスポートする前に、エクスポート先の外部テーブルを作成します。

-- Enable INSERT into external table  
sp_configure 'allow polybase export', 1;  
reconfigure  
  
-- Create an external table.
CREATE EXTERNAL TABLE [dbo].[FastCustomers2009] (  
      [FirstName] char(25) NOT NULL,
      [LastName] char(25) NOT NULL,
      [YearlyIncome] float NULL,
      [MaritalStatus] char(1) NOT NULL  
)  
WITH (  
      LOCATION='/old_data/2009/customerdata',  
      DATA_SOURCE = HadoopHDP2,  
      FILE_FORMAT = TextFileFormat,  
      REJECT_TYPE = VALUE,  
      REJECT_VALUE = 0  
);  

-- Export data: Move old data to Hadoop while keeping it query-able via an external table.  
INSERT INTO dbo.FastCustomer2009  
SELECT T.* FROM Insured_Customers T1 JOIN CarSensor_Data T2  
ON (T1.CustomerKey = T2.CustomerKey)  
WHERE T2.YearMeasured = 2009 and T2.Speed > 40;  

この方法を使った PolyBase エクスポートでは、複数のファイルが作成される場合があります。

SSMS での PolyBase オブジェクトの表示

SSMS では、外部テーブルが別のフォルダー [外部テーブル]に表示されます。 外部データ ソースおよび外部ファイル形式は、 [外部リソース]の下のサブフォルダーにあります。

次のステップ

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

次の記事を参照して、PolyBase を使用して監視するための方法をさらに調べます。

• PolyBase スケールアウト グループ。
• PolyBase のトラブルシューティング。
• CREATE EXTERNAL DATA SOURCE