Azure Blob Storage 上の外部データにアクセスするように PolyBase を構成するConfigure PolyBase to access external data in Azure Blob Storage

適用対象: はいSQL Server (Windows only) いいえAzure SQL Database いいえAzure Synapse Analytics (SQL DW) いいえParallel Data Warehouse APPLIES TO: YesSQL Server (Windows only) NoAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

この記事では、SQL Server インスタンスで PolyBase を使用し、Azure Blob Storage 上の外部データに対してクエリを実行する方法について説明します。The article explains how to use PolyBase on a SQL Server instance to query external data in Azure Blob Storage.

前提条件Prerequisites

PolyBase をインストールしていない場合は、「PolyBase のインストール」をご覧ください。If you haven't installed PolyBase, see PolyBase installation. インストールに関する記事では、前提条件について説明します。The installation article explains the prerequisites.

Azure Blob Storage の接続を構成するConfigure Azure blob storage connectivity

最初に、Azure Blob Storage を使用するように SQL Server PolyBase を構成します。First, configure SQL Server PolyBase to use Azure blob storage.

  1. 'hadoop connectivity' を Azure Blob Storage プロバイダーに設定して sp_configure を実行します。Run sp_configure with 'hadoop connectivity' set to an Azure Blob Storage provider. プロバイダーの値を見つけるには、PolyBase 接続構成 に関する記事を参照してください。To find the value for providers, see PolyBase Connectivity Configuration. 既定で、Hadoop 接続は 7 に設定されています。By Default, the Hadoop connectivity is set to 7.

    -- Values map to various external data sources.  
    -- Example: value 7 stands for Hortonworks HDP 2.1 to 2.6 on Linux,
    -- 2.1 to 2.3 on Windows Server, and Azure blob storage  
    sp_configure @configname = 'hadoop connectivity', @configvalue = 7;
    GO
    
    RECONFIGURE
    GO
    
  2. services.msc を使用して SQL Server を再起動する必要があります。You must restart SQL Server using services.msc. SQL Server を再起動すると、次のサービスが再起動します。Restarting SQL Server restarts these services:

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

    services.msc で PolyBase サービスを停止および開始するstop and start PolyBase services in services.msc

外部テーブルを構成するConfigure an external table

Hadoop データ ソース内のデータのクエリを実行するには、Transact-SQL クエリで使用する外部テーブルを定義する必要があります。To query the data in your Hadoop data source, you must define an external table to use in Transact-SQL queries. 次の手順では、外部テーブルを構成する方法を説明します。The following steps describe how to configure the external table.

  1. データベースにマスター キーを作成します。Create a master key on the database. これは、資格情報のシークレットの暗号化に必須です。This is required to encrypt the credential secret.

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S0me!nfo';  
    
  2. Azure Blob Storage 用のデータベース スコープ資格情報を作成します。Create a database scoped credential for Azure blob storage.

    -- IDENTITY: any string (this is not used for authentication to Azure storage).  
    -- SECRET: your Azure storage account key.  
    CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
    WITH IDENTITY = 'user', Secret = '<azure_storage_account_key>';
    
  3. CREATE EXTERNAL DATA SOURCE を使用して外部データ ソースを作成します。Create an external data source with CREATE EXTERNAL DATA SOURCE..

    -- LOCATION:  Azure account storage account name and blob container name.  
    -- CREDENTIAL: The database scoped credential created above.  
    CREATE EXTERNAL DATA SOURCE AzureStorage with (  
          TYPE = HADOOP,
          LOCATION ='wasbs://<blob_container_name>@<azure_storage_account_name>.blob.core.windows.net',  
          CREDENTIAL = AzureStorageCredential  
    );  
    
  4. CREATE EXTERNAL FILE FORMAT を使用して外部ファイル形式を作成します。Create an external file format with 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 ストレージに格納されているデータをポイントする外部テーブルを作成します。Create an external table pointing to data stored in Azure storage with CREATE EXTERNAL TABLE. この例では、外部データには車両センサー データが含まれています。In this example, the external data contains car sensor data.

    -- 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 on an external table.

    CREATE STATISTICS StatsForSensors on CarSensor_Data(CustomerKey, Speed)  
    

PolyBase クエリPolyBase queries

PolyBase が適している機能には、次の 3 つがあります。There are three functions that PolyBase is suited for:

  • 外部テーブルに対するアドホック クエリ。Ad-hoc queries against external tables.
  • データのインポート。Importing data.
  • データのエクスポート。Exporting data.

次のクエリでは、架空の車両センサー データの例を示します。The following queries provide example with fictional car sensor data.

アドホック クエリAd-hoc queries

次のアドホック クエリでは、Hadoop データを結合します。The following ad-hoc query joins relational with Hadoop data. 時速 35 マイルを越えて走行している顧客を選択し、SQL Server に格納されている構造化された顧客データと Hadoop に格納されている車両センサー データを結合します。It selects customers who drive faster than 35 mph,joining structured customer data stored in SQL Server with car sensor data stored in Hadoop.

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)  

データのインポートImporting data

次のクエリでは、外部データを SQL Server にインポートします。The following query imports external data into SQL Server. この例では、高速走行しているドライバーのデータを、さらに詳細な分析を実行するために SQL Server にインポートします。This example imports data for fast drivers into SQL Server to do more in-depth analysis. パフォーマンスを向上させるために、列ストア テクノロジが活用されています。To improve performance, it leverages Columnstore technology.

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;  

データのエクスポートExporting data

次のクエリは、SQL Server から Azure Blob Storage にデータをエクスポートします。The following query exports data from SQL Server to Azure Blob Storage. これを行うには、先に PolyBase のエクスポートを有効にする必要があります。To do this, you first have to enable PolyBase export. データをエクスポートする前に、エクスポート先の外部テーブルを作成します。The create an external table for the destination before exporting data to it.

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

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

SSMS では、外部テーブルが別のフォルダー [外部テーブル] に表示されます。In SSMS, external tables are displayed in a separate folder External Tables. 外部データ ソースおよび外部ファイル形式は、 [外部リソース] の下のサブフォルダーにあります。External data sources and external file formats are in subfolders under External Resources.

SSMS での PolyBase オブジェクト

次のステップNext steps

次の記事を参照して、PolyBase を使用して監視するための方法をさらに調べます。Explore more ways to use and monitor PolyBase in the following articles:

PolyBase スケールアウト グループPolyBase scale-out groups.
PolyBase のトラブルシューティングPolyBase troubleshooting.