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

適用対象: SQL Server 2016 (13.x) 以降 - Windows のみ

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

前提条件

PolyBase をインストールしていない場合は、「 Windows に 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 データ移動サービス
   • SQL Server PolyBase エンジン

   

外部テーブルを構成する

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

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

   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 (
       DATA_SOURCE = AzureStorage,
       LOCATION = '/Demo/',
       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
EXECUTE sp_configure 'allow polybase export', 1;
RECONFIGURE;
GO

-- 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 (
    DATA_SOURCE = HadoopHDP2,
    LOCATION = '/old_data/2009/customerdata',
    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 AS T1
     INNER JOIN CarSensor_Data AS T2
         ON (T1.CustomerKey = T2.CustomerKey)
WHERE T2.YearMeasured = 2009
      AND T2.Speed > 40;

このメソッドを使用した PolyBase エクスポートでは、複数のファイルが作成される場合があります。

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

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

関連コンテンツ

• PolyBase Transact-SQL リファレンス
• PolyBase スケールアウト グループ
• PolyBase の監視とトラブルシューティング
• 外部データソースの作成 (Transact-SQL)