CREATE EXTERNAL DATA SOURCE (Transact-SQL)

SQL Server、Azure SQL Database、Azure SQL Managed Instance、Azure Synapse Analytics、Analytics Platform System (PDW)、または Azure SQL Edge を使用してクエリを実行するための外部データ ソースを作成します。

この記事では、選択した SQL 製品について、構文、引数、注釈、アクセス許可、例を紹介します。

製品を選択する

次の行で、興味のある製品の名前を選択すると、その製品の情報のみが表示されます。

概要: SQL Server 2016

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

PolyBase クエリ用の外部データ ソースを作成します。 外部データ ソースを使用して接続を確立し、次の主なユース ケースをサポートします。

  • PolyBase を使用したデータ仮想化とデータ読み込み
  • BULK INSERT または OPENROWSET を使用した一括読み込み操作

注意

この構文は、SQL Server のバージョンによって異なります。 バージョン セレクターのドロップダウンを使用して適切なバージョンを選択します。
SQL Server 2019 (15.x) の機能を表示するには、「CREATE EXTERNAL DATA SOURCE」を参照してください。 SQL Server 2022 (16.x) の機能を表示するには、CREATE EXTERNAL DATA SOURCE に関するページを参照してください。

SQL Server 2016 の構文

構文表記規則の詳細については、「Transact-SQL 構文表記規則」を参照してください。

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
  ( [ LOCATION = '<prefix>://<path>[:<port>]' ]
    [ [ , ] CREDENTIAL = <credential_name> ]
    [ [ , ] TYPE = { HADOOP } ]
    [ [ , ] RESOURCE_MANAGER_LOCATION = '<resource_manager>[:<port>]' )
[ ; ]

引数

data_source_name

データ ソースのユーザー定義の名前を指定します。 この名前は、SQL Server のデータベース内で一意になる必要があります。

LOCATION = '<prefix>://<path[:port]>'

接続プロトコルと外部データ ソースへのパスを指定します。

外部データ ソース コネクタの場所のプレフィックス ロケーション パス 製品/サービスでサポートされている場所 認証
Cloudera CDH または Hortonworks HDP hdfs <Namenode>[:port] SQL Server 2016 (13.x) から SQL Server 2019 (15.x) 匿名認証または基本認証
Azure Storage アカウント (V2) wasb[s] <container>@<storage_account>.blob.core.windows.net SQL Server 2016 (13.x) 以降
階層型名前空間はサポートされていません
Azure ストレージ アカウント キー

場所のパス:

  • <Namenode> = Hadoop クラスター内の Namenode のマシン名、ネーム サービス URI、または IP アドレス。 PolyBase では Hadoop クラスターで使用されているすべての DNS 名が解決される必要があります。
  • port = 外部データ ソースがリッスンしているポート。 Hadoop では、fs.defaultFS 構成パラメーターを使用してポートを見つけることができます。 既定値は 8020 です。
  • <container> = データを保持するストレージ アカウントのコンテナー。 ルート コンテナーは読み取り専用で、このコンテナーにデータを書き込むことはできません。
  • <storage_account> = Azure リソースのストレージ アカウント名。
  • <server_name> = ホスト名。
  • <instance_name> = SQL Server の名前付きインスタンスの名前。 ターゲット インスタンスで実行中の SQL Server Browser サービスがある場合に使用されます。

場所を設定する場合の追加の注意事項とガイダンス:

  • SQL Server データベース エンジンでは、オブジェクトの作成時に、外部データ ソースの存在が検証されません。 検証するには、外部データ ソースを使用して外部テーブルを作成します。
  • 一貫性のあるクエリ セマンティクスを確保するため、Hadoop をクエリする際は、すべてのテーブルに同じ外部データ ソースを使用します。
  • wasbs は省略可能ですが、SQL Server 2016 (13.x) では、セキュリティで保護された TLS/SSL 接続を使ってデータが送信されるため、Azure ストレージ アカウントにアクセスする場合に推奨されます。
  • Hadoop Namenode のフェールオーバー時に、PolyBase クエリを確実に成功させるため、Hadoop クラスターの Namenode に仮想 IP アドレスを使用することを検討してください。 使用しない場合は、ALTER EXTERNAL DATA SOURCE コマンドを実行して新しい場所を示します。

CREDENTIAL = credential_name

外部データ ソースへの認証の資格情報のデータベース スコープを指定します。

CREDENTIAL は、データがセキュリティ保護されている場合にのみ必須です。 匿名アクセスを許可するデータ セットには、CREDENTIAL は必要ありません。

データベース スコープ資格情報を作成するには、「CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)」を参照してください。

TYPE = [ HADOOP ]

構成されている外部データ ソースの種類を指定します。 SQL Server 2016 では、このパラメーターは常に必須であり、HADOOP としてのみ指定する必要があります。 Cloudera CDH、Hortonworks HDP、または Azure Storage アカウントへの接続をサポートします。 このパラメーターの動作は、新しいバージョンの SQL Server では異なります。

TYPE = HADOOP を使用して Azure Storage アカウントからデータを読み込む例については、「wasb:// インターフェイスを使用して Azure Storage のデータにアクセスするための外部データソースを作成する」を参照してください

RESOURCE_MANAGER_LOCATION = 'ResourceManager_URI[:port]'

Cloudera CDH、Hortonworks HDP、または Azure ストレージ アカウントのみに接続するときは、このオプションの値を構成します。 サポートされている Hadoop バージョンの完全な一覧については、「PolyBase 接続構成 (Transact-SQL)」を参照してください。

RESOURCE_MANAGER_LOCATION が定義されている場合、クエリ オプティマイザーでは、パフォーマンスを向上させるためにコストに基づいて決定が下されます。 MapReduce ジョブを使用して、Hadoop に計算をプッシュ ダウンできます。 RESOURCE_MANAGER_LOCATION を指定すると、Hadoop と SQL Server の間で転送されるデータ量が大幅に減少し、それによってクエリのパフォーマンスが向上する可能性があります。

Resource Manager を指定しない場合、Hadoop への計算のプッシュが、PolyBase クエリに対して無効になります。 「プッシュダウンが有効になっている Hadoop を参照する外部データ ソースを作成する」では、具体的な例と追加のガイダンスを提供しています。

RESOURCE_MANAGER_LOCATION 値は、外部データ ソースを作成するときに検証されません。 正しくない値を入力すると、指定された値で解決できないため、プッシュ ダウンが試行されるたびに実行時にクエリ エラーが発生する可能性があります。

PolyBase が Hadoop 外部データ ソースと正しく連携するためには、次の Hadoop クラスター コンポーネントのポートが開いている必要があります。

  • HDFS ポート
    • Namenode
    • Datanode
  • Resource Manager
    • ジョブの送信
    • ジョブ履歴

ポートが指定されていない場合、'hadoop connectivity' 構成の現在の設定を使用して、既定値が選択されます。

Hadoop Connectivity Resource Manager の既定のポート
1 50300
2 50300
3 8021
4 8032
5 8050
6 8032
7 8050
8 8032

次の表に、これらのコンポーネントの既定のポートを示します。 Hadoop バージョンの依存関係が存在するほか、カスタム構成で既定のポート割り当てが使用されていない可能性があります。

Hadoop クラスター コンポーネント [既定のポート]
NameNode 8020
DataNode (データ転送、非特権 IPC ポート) 50010
DataNode (データ転送、特権 IPC ポート) 1019
Resource Manager ジョブの送信 (Hortonworks 1.3) 50300
Resource Manager ジョブの送信 (Cloudera 4.3) 8021
Resource Manager ジョブの送信 (Windows では Hortonworks 2.0、Linux では Cloudera 5.x) 8032
Resource Manager ジョブの送信 (Linux では Hortonworks 2.x、3.0、Windows では Hortonworks 2.1-3) 8050
Resource Manager ジョブ履歴 10020

アクセス許可

SQL Server 内のデータベースに対する CONTROL アクセス許可が必要です。

ロック

EXTERNAL DATA SOURCE オブジェクトを共有ロックします。

セキュリティ

PolyBase では、ほとんどの外部データ ソースにプロキシ ベースの認証をサポートします。 データベース スコープ資格情報を作成して、プロキシ アカウントを作成します。

重要

PolyBase をインストールして有効にする方法については、「Windows への PolyBase のインストール」を参照してください

A. Hadoop を参照する外部データ ソースを作成する

Hortonworks HDP または Cloudera CDH Hadoop クラスターを参照する外部データ ソースを作成するには、Hadoop Namenode のマシン名または IP アドレスとポートを指定します。

CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    LOCATION = 'hdfs://10.10.10.10:8050',
    TYPE = HADOOP
);

B. プッシュダウンが有効になっている Hadoop を参照する外部データ ソースを作成する

RESOURCE_MANAGER_LOCATION オプションを指定して、PolyBase クエリの Hadoop への計算のプッシュダウンを有効にします。 有効にすると、PolyBase によって、クエリの計算を Hadoop にプッシュするかどうかがコストに基づいて決定されます。

CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    LOCATION = 'hdfs://10.10.10.10:8020',
    TYPE = HADOOP,
    RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);

C. Kerberos でセキュリティ保護された Hadoop を参照する外部データ ソースを作成する

Hadoop クラスターが Kerberos でセキュリティ保護されていることを確認するには、Hadoop core-site.xml で hadoop.security.authentication プロパティの値を確認します。 Kerberos でセキュリティ保護された Hadoop クラスターを参照するには、ご自分の Kerberos ユーザー名とパスワードを含むデータベース スコープの資格情報を指定する必要があります。 データベース マスター キーは、データベース スコープの資格情報シークレットの暗号化に使用されます。

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Kerberos user name and password.
CREATE DATABASE SCOPED CREDENTIAL HadoopUser1
    WITH IDENTITY = '<hadoop_user_name>',
    SECRET = '<hadoop_password>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    LOCATION = 'hdfs://10.10.10.10:8050',
    CREDENTIAL = HadoopUser1,
    TYPE = HADOOP,
    RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);

D. wasb:// インターフェイスを使用して Azure Storage のデータにアクセスするための外部データソースを作成する

この例では、外部データ ソースは、logs という名前の Azure V2 Storage アカウントです。 ストレージ コンテナーは daily という名前です。 Azure Storage の外部データ ソースはデータ転送専用です。 述語のプッシュ ダウンはサポートされません。 wasb:// インターフェイスを使用してデータにアクセスする場合、階層型名前空間はサポートされません。

この例では、Azure V2 Storage アカウントへの認証用にデータベース スコープ資格情報を作成する方法を示します。 データベースの資格情報シークレットで、Azure Storage アカウント キーを指定します。 Azure Storage への認証時に使用されないため、データベース スコープ資格情報 ID には任意の文字列を指定できます。 WASB[s] コネクタ経由で Azure Storage に接続する場合は、Shared Access Signature (SAS) ではなく、ストレージ アカウント キーを使って認証を行う必要があることに注意してください。

SQL Server 2016 (13.x) では、TYPE は、Azure Storage にアクセスする場合でも HADOOP に設定する必要があります。

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
    WITH IDENTITY = '<my_account>',
    SECRET = '<azure_storage_account_key>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
    LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
    CREDENTIAL = AzureStorageCredential,
    TYPE = HADOOP
);

次のステップ

概要: SQL Server 2017

適用対象: SQL Server 2017 (14.x) のみ

PolyBase クエリ用の外部データ ソースを作成します。 外部データ ソースを使用して接続を確立し、次の主なユース ケースをサポートします。

  • PolyBase を使用したデータ仮想化とデータ読み込み
  • BULK INSERT または OPENROWSET を使用した一括読み込み操作

注意

この構文は、SQL Server on Linux のバージョンによって異なります。 バージョン セレクターのドロップダウンを使用して適切なバージョンを選択します。
SQL Server 2019 (15.x) の機能を表示するには、「CREATE EXTERNAL DATA SOURCE」を参照してください。 SQL Server 2022 (16.x) の機能を表示するには、CREATE EXTERNAL DATA SOURCE に関するページを参照してください。

注意

この構文は、SQL Server のバージョンによって異なります。 バージョン セレクターのドロップダウンを使用して適切なバージョンを選択します。
SQL Server 2019 (15.x) の機能を表示するには、「CREATE EXTERNAL DATA SOURCE」を参照してください。 SQL Server 2022 (16.x) の機能を表示するには、CREATE EXTERNAL DATA SOURCE に関するページを参照してください。

SQL Server 2017 の構文

構文表記規則の詳細については、「Transact-SQL 構文表記規則」を参照してください。

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
  ( [ LOCATION = '<prefix>://<path>[:<port>]' ]
    [ [ , ] CREDENTIAL = <credential_name> ]
    [ [ , ] TYPE = { HADOOP | BLOB_STORAGE } ]
    [ [ , ] RESOURCE_MANAGER_LOCATION = '<resource_manager>[:<port>]' )
[ ; ]

引数

data_source_name

データ ソースのユーザー定義の名前を指定します。 この名前は、SQL Server のデータベース内で一意になる必要があります。

LOCATION = '<prefix>://<path[:port]>'

接続プロトコルと外部データ ソースへのパスを指定します。

外部データ ソース コネクタの場所のプレフィックス ロケーション パス 製品/サービスでサポートされている場所 認証
Cloudera CDH または Hortonworks HDP hdfs <Namenode>[:port] SQL Server 2016 (13.x) から SQL Server 2019 (15.x) のみ 匿名認証または基本認証
Azure Storage アカウント (V2) wasb[s] <container>@<storage_account>.blob.core.windows.net SQL Server 2016 (13.x) 以降
階層型名前空間はサポートされていません
Azure ストレージ アカウント キー
一括操作 https <storage_account>.blob.core.windows.net/<container> SQL Server 2017 (14.x) 以降 Shared Access Signature (SAS)

場所のパス:

  • <Namenode> = Hadoop クラスター内の Namenode のマシン名、ネーム サービス URI、または IP アドレス。 PolyBase では Hadoop クラスターで使用されているすべての DNS 名が解決される必要があります。
  • port = 外部データ ソースがリッスンしているポート。 Hadoop では、fs.defaultFS 構成パラメーターを使用してポートを見つけることができます。 既定値は 8020 です。
  • <container> = データを保持するストレージ アカウントのコンテナー。 ルート コンテナーは読み取り専用で、このコンテナーにデータを書き込むことはできません。
  • <storage_account> = Azure リソースのストレージ アカウント名。
  • <server_name> = ホスト名。
  • <instance_name> = SQL Server の名前付きインスタンスの名前。 ターゲット インスタンスで実行中の SQL Server Browser サービスがある場合に使用されます。

場所を設定する場合の追加の注意事項とガイダンス:

  • SQL Server データベース エンジンでは、オブジェクトの作成時に、外部データ ソースの存在が検証されません。 検証するには、外部データ ソースを使用して外部テーブルを作成します。
  • 一貫性のあるクエリ セマンティクスを確保するため、Hadoop をクエリする際は、すべてのテーブルに同じ外部データ ソースを使用します。
  • Driver={<Name of Driver>} 経由で接続する際に ODBC を指定します。
  • wasbs は省略可能ですが、SQL Server 2017 (14.x) では、セキュリティで保護された TLS/SSL 接続を使ってデータが送信されるため、Azure ストレージ アカウントにアクセスする場合に推奨されます。
  • Hadoop Namenode のフェールオーバー時に、PolyBase クエリを確実に成功させるため、Hadoop クラスターの Namenode に仮想 IP アドレスを使用することを検討してください。 使用しない場合は、ALTER EXTERNAL DATA SOURCE コマンドを実行して新しい場所を示します。

CREDENTIAL = credential_name

外部データ ソースへの認証の資格情報のデータベース スコープを指定します。

資格情報の作成時の追加の注意事項とガイダンス:

  • CREDENTIAL は、データがセキュリティ保護されている場合にのみ必須です。 匿名アクセスを許可するデータ セットには、CREDENTIAL は必要ありません。
  • TYPE = BLOB_STORAGE の場合、SHARED ACCESS SIGNATURE を ID として使用して資格情報を作成する必要があります。
  • TYPE = BLOB_STORAGE は一括操作でのみ許可されます。TYPE = BLOB_STORAGE を使って外部データ ソースに対して外部テーブルを作成することはできません。
  • WASB[s] コネクタ経由で Azure Storage に接続する場合は、Shared Access Signature (SAS) ではなく、ストレージ アカウント キーを使って認証を行う必要があることに注意してください。
  • TYPE = HADOOP の場合、ストレージ アカウント キーを SECRET として使って資格情報を作成する必要があります。

Shared Access Signature を作成する方法は複数あります。

  • SAS トークンを作成するには、Azure portal -><ご利用のストレージ アカウント> -> [Shared Access Signature] -> [アクセス許可の構成] -> [SAS と接続文字列を生成する] の順に移動します。 詳細については、Shared Access Signature の作成に関するページを参照してください。

  • Azure Storage Explorer を使用して SAS を作成および構成できます。

  • PowerShell、Azure CLI、.NET、REST API を使用すれば、SAS をプログラムで作成できます。 詳細については、「Shared Access Signatures (SAS) を使用して Azure Storage リソースへの制限付きアクセスを許可する」を参照してください。

  • SAS トークンは、次のように構成する必要があります。

    • SAS トークンが生成されると、トークンの先頭に疑問符 ("?") が含まれます。 SECRET として構成されている場合、先頭の ? を除外します。
    • 有効な有効期限を使用する (すべての日付が UTC 時間)。
    • 読み込む必要のあるファイル (たとえば srt=o&sp=r) に対して少なくとも読み取りアクセス許可を付与します。 さまざまなユース ケースに合わせて複数の Shared Access Signature を作成できます。 アクセス許可は次のように付与する必要があります。
    アクション 権限
    データをファイルから読み取る Read
    複数のファイルとサブフォルダーからデータを読み取る Read と List

SHARED ACCESS SIGNATURETYPE = BLOB_STORAGE で、CREDENTIAL を使用する例については、一括操作を実行し、Azure Storage から SQL Database にデータを取得するための外部データ ソースの作成に関するセクションを参照してください

データベース スコープ資格情報を作成するには、「CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)」を参照してください。

TYPE = [ HADOOP | BLOB_STORAGE ]

構成されている外部データ ソースの種類を指定します。 このパラメーターは、常に必要なわけではなく、Cloudera CDH、Hortonworks HDP、Azure ストレージ アカウント、または Azure Data Lake Storage Gen2 に接続するときにだけ指定する必要があります。

  • 外部データ ソースが Cloudera CDH、Hortonworks HDP、Azure ストレージ アカウント、または Azure Data Lake Storage Gen2 のときは、HADOOP を使用します。
  • BULK INSERT または OPENROWSET を使用して、Azure ストレージ アカウントから一括操作を実行する場合は、BLOB_STORAGE を使用します。 SQL Server 2017 (14.x) で導入されました。 Azure Storage に対して CREATE EXTERNAL TABLE を実行したいときは、HADOOP を使います。

注意

TYPE は、Azure Storage にアクセスする場合でも HADOOP に設定する必要があります。

TYPE = HADOOP を使用して Azure Storage アカウントからデータを読み込む例については、「wasb:// インターフェイスを使用して Azure Storage のデータにアクセスするための外部データソースを作成する」を参照してください

RESOURCE_MANAGER_LOCATION = 'ResourceManager_URI[:port]'

Cloudera CDH、Hortonworks HDP、または Azure ストレージ アカウントのみに接続するときは、このオプションの値を構成します。 サポートされている Hadoop バージョンの完全な一覧については、「PolyBase 接続構成 (Transact-SQL)」を参照してください。

RESOURCE_MANAGER_LOCATION が定義されている場合、クエリ オプティマイザーでは、パフォーマンスを向上させるためにコストに基づいて決定が下されます。 MapReduce ジョブを使用して、Hadoop に計算をプッシュ ダウンできます。 RESOURCE_MANAGER_LOCATION を指定すると、Hadoop と SQL Server の間で転送されるデータ量が大幅に減少し、それによってクエリのパフォーマンスが向上する可能性があります。

Resource Manager を指定しない場合、Hadoop への計算のプッシュが、PolyBase クエリに対して無効になります。 「プッシュダウンが有効になっている Hadoop を参照する外部データ ソースを作成する」では、具体的な例と追加のガイダンスを提供しています。

RESOURCE_MANAGER_LOCATION 値は、外部データ ソースを作成するときに検証されません。 正しくない値を入力すると、指定された値で解決できないため、プッシュ ダウンが試行されるたびに実行時にクエリ エラーが発生する可能性があります。

PolyBase が Hadoop 外部データ ソースと正しく連携するためには、次の Hadoop クラスター コンポーネントのポートが開いている必要があります。

  • HDFS ポート
    • Namenode
    • Datanode
  • Resource Manager
    • ジョブの送信
    • ジョブ履歴

ポートが指定されていない場合、'hadoop connectivity' 構成の現在の設定を使用して、既定値が選択されます。

Hadoop Connectivity Resource Manager の既定のポート
1 50300
2 50300
3 8021
4 8032
5 8050
6 8032
7 8050
8 8032

次の表に、これらのコンポーネントの既定のポートを示します。 Hadoop バージョンの依存関係と、既定のポート割り当てを使用しないカスタム構成の可能性があることに注意してください。

Hadoop クラスター コンポーネント [既定のポート]
NameNode 8020
DataNode (データ転送、非特権 IPC ポート) 50010
DataNode (データ転送、特権 IPC ポート) 1019
Resource Manager ジョブの送信 (Hortonworks 1.3) 50300
Resource Manager ジョブの送信 (Cloudera 4.3) 8021
Resource Manager ジョブの送信 (Windows では Hortonworks 2.0、Linux では Cloudera 5.x) 8032
Resource Manager ジョブの送信 (Linux では Hortonworks 2.x、3.0、Windows では Hortonworks 2.1-3) 8050
Resource Manager ジョブ履歴 10020

アクセス許可

SQL Server 内のデータベースに対する CONTROL アクセス許可が必要です。

ロック

EXTERNAL DATA SOURCE オブジェクトを共有ロックします。

セキュリティ

PolyBase では、ほとんどの外部データ ソースにプロキシ ベースの認証をサポートします。 データベース スコープ資格情報を作成して、プロキシ アカウントを作成します。

HADOOP 型の SAS トークンはサポートされていません。 それは、ストレージ アカウントのアクセス キーが代わりに使用される、type = BLOB_STORAGE でのみサポートされます。 HADOOP 型と SAS 資格情報で外部データ ソースを作成しようとすると、次のエラーで失敗します。

Msg 105019, Level 16, State 1 - EXTERNAL TABLE access failed due to internal error: 'Java exception raised on call to HdfsBridge_Connect. Java exception message: Parameters provided to connect to the Azure storage account are not valid.: Error [Parameters provided to connect to the Azure storage account are not valid.] occurred while accessing external file.'

重要

PolyBase をインストールして有効にする方法については、「Windows への PolyBase のインストール」を参照してください

A. Hadoop を参照する外部データ ソースを作成する

Hortonworks HDP または Cloudera CDH Hadoop クラスターを参照する外部データ ソースを作成するには、Hadoop Namenode のマシン名または IP アドレスとポートを指定します。

CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    LOCATION = 'hdfs://10.10.10.10:8050',
    TYPE = HADOOP
);

B. プッシュダウンが有効になっている Hadoop を参照する外部データ ソースを作成する

RESOURCE_MANAGER_LOCATION オプションを指定して、PolyBase クエリの Hadoop への計算のプッシュダウンを有効にします。 有効にすると、PolyBase によって、クエリの計算を Hadoop にプッシュするかどうかがコストに基づいて決定されます。

CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    LOCATION = 'hdfs://10.10.10.10:8020',
    TYPE = HADOOP,
    RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);

C. Kerberos でセキュリティ保護された Hadoop を参照する外部データ ソースを作成する

Hadoop クラスターが Kerberos でセキュリティ保護されていることを確認するには、Hadoop core-site.xml で hadoop.security.authentication プロパティの値を確認します。 Kerberos でセキュリティ保護された Hadoop クラスターを参照するには、ご自分の Kerberos ユーザー名とパスワードを含むデータベース スコープの資格情報を指定する必要があります。 データベース マスター キーは、データベース スコープの資格情報シークレットの暗号化に使用されます。

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Kerberos user name and password.
CREATE DATABASE SCOPED CREDENTIAL HadoopUser1
    WITH IDENTITY = '<hadoop_user_name>',
    SECRET = '<hadoop_password>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    LOCATION = 'hdfs://10.10.10.10:8050',
    CREDENTIAL = HadoopUser1,
    TYPE = HADOOP,
    RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);

D. wasb:// インターフェイスを使用して Azure Storage のデータにアクセスするための外部データソースを作成する

この例では、外部データ ソースは、logs という名前の Azure V2 Storage アカウントです。 ストレージ コンテナーは daily という名前です。 Azure Storage の外部データ ソースはデータ転送専用です。 述語のプッシュ ダウンはサポートされません。 wasb:// インターフェイスを使用してデータにアクセスする場合、階層型名前空間はサポートされません。 WASB[s] コネクタ経由で Azure Storage に接続する場合は、Shared Access Signature (SAS) ではなく、ストレージ アカウント キーを使って認証を行う必要があることに注意してください。

この例では、Azure V2 Storage アカウントへの認証用にデータベース スコープ資格情報を作成する方法を示します。 データベースの資格情報シークレットで、Azure Storage アカウント キーを指定します。 Azure Storage への認証時に使用されないため、データベース スコープ資格情報 ID には任意の文字列を指定できます。

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
    WITH IDENTITY = '<my_account>',
    SECRET = '<azure_storage_account_key>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
    LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
    CREDENTIAL = AzureStorageCredential,
    TYPE = HADOOP
);

例: 一括操作

重要

一括操作用の外部データ ソースの構成時に、LOCATION URL の末尾に、 / 、ファイル名、Shared Access Signature パラメーターを追加しないでください。

E. Azure Storage からデータを取得する一括操作用の外部データ ソースを作成する

適用対象: SQL Server 2017 (14.x) 以降。

BULK INSERT または OPENROWSET を使用する一括操作に対し、次のデータ ソースを使用します。 資格情報は、SHARED ACCESS SIGNATURE を ID として設定する必要があり、SAS トークンの先頭に ? があってはなりません。また、読み込む必要のあるファイル (たとえば srt=o&sp=r) に対して少なくとも読み取りアクセス許可が必要で、有効期限が有効である必要があります (すべての日付は UTC 時間です)。 Shared Access Signature に関する詳細については、「Shared Access Signature (SAS) を使用」を参照してください。

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

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
WITH (
    LOCATION = 'https://newinvoices.blob.core.windows.net/week3',
    CREDENTIAL = AccessAzureInvoices,
    TYPE = BLOB_STORAGE
);

この使用例については、BULK INSERT の例を参照してください。

次のステップ

概要: SQL Server 2019

適用対象: SQL Server 2019 (15.x) 以降

PolyBase クエリ用の外部データ ソースを作成します。 外部データ ソースを使用して接続を確立し、次の主なユース ケースをサポートします。

  • PolyBase を使用したデータ仮想化とデータ読み込み
  • BULK INSERT または OPENROWSET を使用した一括読み込み操作

注意

この構文は、SQL Server のバージョンによって異なります。 バージョン セレクターのドロップダウンを使用して適切なバージョンを選択します。
SQL Server 2022 (16.x) の機能を表示するには、CREATE EXTERNAL DATA SOURCE に関するページを参照してください。

注意

この構文は、SQL Server のバージョンによって異なります。 バージョン セレクターのドロップダウンを使用して適切なバージョンを選択します。
SQL Server 2022 (16.x) の機能を表示するには、CREATE EXTERNAL DATA SOURCE に関するページを参照してください。

SQL Server 2019 の構文

構文表記規則の詳細については、「Transact-SQL 構文表記規則」を参照してください。

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
  ( [ LOCATION = '<prefix>://<path>[:<port>]' ]
    [ [ , ] CONNECTION_OPTIONS = '<key_value_pairs>'[,...]]
    [ [ , ] CREDENTIAL = <credential_name> ]
    [ [ , ] PUSHDOWN = { ON | OFF } ]
    [ [ , ] TYPE = { HADOOP | BLOB_STORAGE } ]
    [ [ , ] RESOURCE_MANAGER_LOCATION = '<resource_manager>[:<port>]' )
[ ; ]

引数

data_source_name

データ ソースのユーザー定義の名前を指定します。 この名前は、SQL Server のデータベース内で一意になる必要があります。

LOCATION = '<prefix>://<path[:port]>'

接続プロトコルと外部データ ソースへのパスを指定します。

外部データ ソース コネクタの場所のプレフィックス ロケーション パス 製品/サービスでサポートされている場所 認証
Cloudera CDH または Hortonworks HDP hdfs <Namenode>[:port] SQL Server 2016 (13.x) から SQL Server 2019 (15.x) 匿名認証または基本認証
Azure Storage アカウント (V2) wasb[s] <container>@<storage_account>.blob.core.windows.net SQL Server 2016 (13.x) 以降
階層型名前空間はサポートされていません
Azure ストレージ アカウント キー
SQL Server sqlserver <server_name>[\<instance_name>][:port] SQL Server 2019 (15.x) 以降 SQL 認証のみ
Oracle oracle <server_name>[:port] SQL Server 2019 (15.x) 以降 基本認証のみ
Teradata teradata <server_name>[:port] SQL Server 2019 (15.x) 以降 基本認証のみ
MongoDB または MongoDB 用 Cosmos DB API mongodb <server_name>[:port] SQL Server 2019 (15.x) 以降 基本認証のみ
汎用 ODBC odbc <server_name>[:port] SQL Server 2019 (15.x) 以降 - Windows のみ 基本認証のみ
一括操作 https <storage_account>.blob.core.windows.net/<container> SQL Server 2017 (14.x) 以降 Shared Access Signature (SAS)
Azure Data Lake Storage Gen2 abfs[s] abfss://<container>@<storage _account>.dfs.core.windows.net SQL Server 2019 (15.x) CU11+ 以降。 Storage Access Key (ストレージ アクセス キー)
SQL Server ビッグ データ クラスターのデータ プール sqldatapool sqldatapool://controller-svc/default SQL Server 2019 のビッグ データ クラスターでのみサポート 基本認証のみ
SQL Server ビッグ データ クラスターの記憶域プール sqlhdfs sqlhdfs://controller-svc/default SQL Server 2019 のビッグ データ クラスターでのみサポート 基本認証のみ

場所のパス:

  • <Namenode> = Hadoop クラスター内の Namenode のマシン名、ネーム サービス URI、または IP アドレス。 PolyBase では Hadoop クラスターで使用されているすべての DNS 名が解決される必要があります。
  • port = 外部データ ソースがリッスンしているポート。 Hadoop では、fs.defaultFS 構成パラメーターを使用してポートを見つけることができます。 既定値は 8020 です。
  • <container> = データを保持するストレージ アカウントのコンテナー。 ルート コンテナーは読み取り専用で、このコンテナーにデータを書き込むことはできません。
  • <storage_account> = Azure リソースのストレージ アカウント名。
  • <server_name> = ホスト名。
  • <instance_name> = SQL Server の名前付きインスタンスの名前。 ターゲット インスタンスで実行中の SQL Server Browser サービスがある場合に使用されます。

場所を設定する場合の追加の注意事項とガイダンス:

  • SQL Server データベース エンジンでは、オブジェクトの作成時に、外部データ ソースの存在が検証されません。 検証するには、外部データ ソースを使用して外部テーブルを作成します。
  • 一貫性のあるクエリ セマンティクスを確保するため、Hadoop をクエリする際は、すべてのテーブルに同じ外部データ ソースを使用します。
  • sqlserver コネクタを使用すれば、SQL Server 2019 (15.x) を別の SQL Server、または Azure SQL Database に接続できます。
  • Driver={<Name of Driver>} 経由で接続する際に ODBC を指定します。
  • wasbs または abfss の使用は省略可能ですが、SQL Server 2019 (15.x) では、セキュリティで保護された TLS/SSL 接続を使ってデータが送信されるため、Azure ストレージ アカウントにアクセスする場合に推奨されます。
  • SQL Server 2019 (15.x) CU11 以降では、Azure ストレージ アカウントにアクセスするときに abfs または abfss API がサポートされます。 詳細については、Azure Blob File System ドライバー (ABFS) に関する記事を参照してください。
  • abfs[s] を使用する Azure ストレージ アカウント (V2) の階層型名前空間オプションは、SQL Server 2019 (15.x) CU11 以降の Azure Data Lake Storage Gen2 でサポートされています。 それ以外の場合、階層型名前空間オプションはサポートされておらず、このオプションは無効のままにする必要があります。
  • Hadoop Namenode のフェールオーバー時に、PolyBase クエリを確実に成功させるため、Hadoop クラスターの Namenode に仮想 IP アドレスを使用することを検討してください。 使用しない場合は、ALTER EXTERNAL DATA SOURCE コマンドを実行して新しい場所を示します。
  • sqlhdfs および sqldatapool の型は、ビッグ データ クラスターのマスター インスタンスと記憶域プールの間の接続においてサポートされています。 Cloudera CDH または Hortonworks HDP の場合は、hdfs を使用します。 sqlhdfs を使用して SQL Server ビッグ データ クラスター記憶域プールに対してクエリを実行する方法の詳細については、SQL Server 2019 ビッグ データ クラスター内の HDFS にクエリを実行するに関するページを参照してください。
  • SQL Server における HDFS Cloudera (CDP) と Hortonworks (HDP) の外部データ ソースのサポートは廃止され、SQL Server 2022 (16.x) には含まれません。 詳細については、「Microsoft SQL Server プラットフォームのビッグ データ オプション」を参照してください。

CONNECTION_OPTIONS = key_value_pair

SQL Server 2019 (15.x) 以降に対して指定します。 ODBC 経由での外部データ ソースへの接続時に、追加のオプションを指定します。 複数の接続オプションを使用するには、セミコロンで区切ります。

汎用の ODBC 接続だけでなく、SQL Server、Oracle、Teradata、MongoDB、Cosmos DB、MongoDB 用 Azure Cosmos DB API の組み込み ODBC コネクタにも適用されます。

key_value_pair は、キーワードであり、特定の接続オプションの値です。 使用できるキーワードと値は、外部データ ソースの種類によって異なります。 最低でもドライバーの名前が必要ですが、設定に便利で、トラブルシューティングに役立てることができる APP='<your_application_name>'ApplicationIntent= ReadOnly|ReadWrite などの他のオプションがあります。

使えるキーと値のペアは、外部データ ソース ベンダー用のプロバイダーに固有です。 各プロバイダーの詳細については、「CREATE EXTERNAL DATA SOURCE (Transact-SQL) の CONNECTION_OPTIONS」をご覧ください。

SQL Server 2019 (15.x) の累積的な更新プログラム 19 以降では、Oracle TNS ファイルをサポートするために追加のキーワードが導入されました。

  • キーワード TNSNamesFile を使用すると、Oracle サーバー上にある tnsnames.ora ファイルへのファイルパスを指定できます。
  • キーワード ServerName を使用すると、ホスト名とポートを置き換えるために使用される tnsnames.ora 内で使用されるエイリアスを指定できます。

PUSHDOWN = ON | OFF

SQL Server 2019 (15.x) にのみ指定します。 外部データ ソースに計算をプッシュ ダウンできるかどうかを示します。 既定でオンです。

PUSHDOWN は、外部データ ソース レベルで SQL Server、Oracle、Teradata、MongoDB、MongoDB 用 Azure Cosmos DB API、または ODBC に接続するときにサポートされます。

クエリ レベルでプッシュ ダウンを有効にするか、無効にするかは、ヒントによって実現します。

CREDENTIAL = credential_name

外部データ ソースへの認証の資格情報のデータベース スコープを指定します。

資格情報の作成時の追加の注意事項とガイダンス:

  • CREDENTIAL は、データがセキュリティ保護されている場合にのみ必須です。 匿名アクセスを許可するデータ セットには、CREDENTIAL は必要ありません。
  • TYPE = BLOB_STORAGE の場合、SHARED ACCESS SIGNATURE を ID として使用して資格情報を作成する必要があります。
    • TYPE = BLOB_STORAGE は一括操作でのみ許可されます。TYPE = BLOB_STORAGE を使って外部データ ソースに対して外部テーブルを作成することはできません。

Shared Access Signature を作成する方法は複数あります。

  • SAS トークンを作成するには、Azure portal -><ご利用のストレージ アカウント> -> [Shared Access Signature] -> [アクセス許可の構成] -> [SAS と接続文字列を生成する] の順に移動します。 詳細については、Shared Access Signature の作成に関するページを参照してください。

  • Azure Storage Explorer を使用して SAS を作成および構成できます。

  • PowerShell、Azure CLI、.NET、REST API を使用すれば、SAS をプログラムで作成できます。 詳細については、「Shared Access Signatures (SAS) を使用して Azure Storage リソースへの制限付きアクセスを許可する」を参照してください。

  • SAS トークンは、次のように構成する必要があります。

    • SAS トークンが生成されると、トークンの先頭に疑問符 ("?") が含まれます。 SECRET として構成されている場合、先頭の ? を除外します。
    • 有効な有効期限を使用する (すべての日付が UTC 時間)。
    • 読み込む必要のあるファイル (たとえば srt=o&sp=r) に対して少なくとも読み取りアクセス許可を付与します。 さまざまなユース ケースに合わせて複数の Shared Access Signature を作成できます。 アクセス許可は次のように付与する必要があります。
    アクション 権限
    データをファイルから読み取る Read
    複数のファイルとサブフォルダーからデータを読み取る Read と List

SHARED ACCESS SIGNATURETYPE = BLOB_STORAGE で、CREDENTIAL を使用する例については、一括操作を実行し、Azure Storage から SQL Database にデータを取得するための外部データ ソースの作成に関するセクションを参照してください

データベース スコープ資格情報を作成するには、「CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)」を参照してください。

TYPE = [ HADOOP | BLOB_STORAGE ]

構成されている外部データ ソースの種類を指定します。 このパラメーターは、常に必要なわけではなく、Cloudera CDH、Hortonworks HDP、Azure ストレージ アカウント、または Azure Data Lake Storage Gen2 に接続するときにだけ指定する必要があります。

  • SQL Server 2019 (15.x) においては、Cloudera CDH、Hortonworks HDP、Azure ストレージ アカウントに接続する場合を除き、TYPE を指定しないでください。
  • 外部データ ソースが Cloudera CDH、Hortonworks HDP、Azure ストレージ アカウント、または Azure Data Lake Storage Gen2 のときは、HADOOP を使用します。
  • SQL Server 2017 (14.x) で BULK INSERT または OPENROWSET を使用して、Azure ストレージ アカウントから一括操作を実行する場合は、BLOB_STORAGE を使用します。 Azure Storage に対して CREATE EXTERNAL TABLE を実行したいときは、HADOOP を使います。
  • SQL Server における HDFS Cloudera (CDP) と Hortonworks (HDP) の外部データ ソースのサポートは廃止され、SQL Server 2022 (16.x) には含まれません。 詳細については、「Microsoft SQL Server プラットフォームのビッグ データ オプション」を参照してください。

TYPE = HADOOP を使用して Azure Storage アカウントからデータを読み込む例については、「wasb:// インターフェイスを使用して Azure Storage のデータにアクセスするための外部データソースを作成する」を参照してください

RESOURCE_MANAGER_LOCATION = 'ResourceManager_URI[:port]'

SQL Server 2019 (15.x) においては、Cloudera CDH、Hortonworks HDP、Azure ストレージ アカウントに接続する場合を除き、RESOURCE_MANAGER_LOCATION を指定しないでください。

Cloudera CDH、Hortonworks HDP、または Azure ストレージ アカウントのみに接続するときは、このオプションの値を構成します。 サポートされている Hadoop バージョンの完全な一覧については、「PolyBase 接続構成 (Transact-SQL)」を参照してください。

RESOURCE_MANAGER_LOCATION が定義されている場合、クエリ オプティマイザーでは、パフォーマンスを向上させるためにコストに基づいて決定が下されます。 MapReduce ジョブを使用して、Hadoop に計算をプッシュ ダウンできます。 RESOURCE_MANAGER_LOCATION を指定すると、Hadoop と SQL Server の間で転送されるデータ量が大幅に減少し、それによってクエリのパフォーマンスが向上する可能性があります。

Resource Manager を指定しない場合、Hadoop への計算のプッシュが、PolyBase クエリに対して無効になります。 「プッシュダウンが有効になっている Hadoop を参照する外部データ ソースを作成する」では、具体的な例と追加のガイダンスを提供しています。

RESOURCE_MANAGER_LOCATION 値は、外部データ ソースを作成するときに検証されません。 正しくない値を入力すると、指定された値で解決できないため、プッシュ ダウンが試行されるたびに実行時にクエリ エラーが発生する可能性があります。

PolyBase が Hadoop 外部データ ソースと正しく連携するためには、次の Hadoop クラスター コンポーネントのポートが開いている必要があります。

  • HDFS ポート
    • Namenode
    • Datanode
  • Resource Manager
    • ジョブの送信
    • ジョブ履歴

ポートが指定されていない場合、'hadoop connectivity' 構成の現在の設定を使用して、既定値が選択されます。

Hadoop Connectivity Resource Manager の既定のポート
1 50300
2 50300
3 8021
4 8032
5 8050
6 8032
7 8050
8 8032

次の表に、これらのコンポーネントの既定のポートを示します。 Hadoop バージョンの依存関係と、既定のポート割り当てを使用しないカスタム構成の可能性があることに注意してください。

Hadoop クラスター コンポーネント [既定のポート]
NameNode 8020
DataNode (データ転送、非特権 IPC ポート) 50010
DataNode (データ転送、特権 IPC ポート) 1019
Resource Manager ジョブの送信 (Hortonworks 1.3) 50300
Resource Manager ジョブの送信 (Cloudera 4.3) 8021
Resource Manager ジョブの送信 (Windows では Hortonworks 2.0、Linux では Cloudera 5.x) 8032
Resource Manager ジョブの送信 (Linux では Hortonworks 2.x、3.0、Windows では Hortonworks 2.1-3) 8050
Resource Manager ジョブ履歴 10020

アクセス許可

SQL Server 内のデータベースに対する CONTROL アクセス許可が必要です。

ロック

EXTERNAL DATA SOURCE オブジェクトを共有ロックします。

セキュリティ

PolyBase では、ほとんどの外部データ ソースにプロキシ ベースの認証をサポートします。 データベース スコープ資格情報を作成して、プロキシ アカウントを作成します。

SQL Server 2019 ビッグ データ クラスターでストレージまたはデータ プールに接続すると、ユーザーの資格情報が、バックエンド システムに渡されます。 データ プール自体にログインを作成し、パススルー認証を有効にします。

HADOOP 型の SAS トークンはサポートされていません。 それは、ストレージ アカウントのアクセス キーが代わりに使用される、type = BLOB_STORAGE でのみサポートされます。 HADOOP 型と SAS 資格情報で外部データ ソースを作成しようとすると、次のエラーで失敗します。

Msg 105019, Level 16, State 1 - EXTERNAL TABLE access failed due to internal error: 'Java exception raised on call to HdfsBridge_Connect. Java exception message: Parameters provided to connect to the Azure storage account are not valid.: Error [Parameters provided to connect to the Azure storage account are not valid.] occurred while accessing external file.'

重要

PolyBase をインストールして有効にする方法については、「Windows への PolyBase のインストール」を参照してください

A. SQL Server 2019 で Oracle を参照する外部データ ソースを作成する

Oracle を参照する外部データ ソースを作成するには、データベース スコープ資格情報があることを確認します。 オプションで、このデータ ソースに対して計算のプッシュ ダウンを有効または無効にすることもできます。

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL OracleProxyAccount
    WITH IDENTITY = 'oracle_username',
    SECRET = 'oracle_password';

CREATE EXTERNAL DATA SOURCE MyOracleServer
WITH (
    LOCATION = 'oracle://145.145.145.145:1521',
    CREDENTIAL = OracleProxyAccount,
    PUSHDOWN = ON
);

必要に応じて、Oracle に対する外部データソースはプロキシ認証を使用して、きめ細かいアクセス制御を提供できます。 プロキシ ユーザーは、権限が借用されているユーザーと比較してアクセスが制限されるように構成することができます。

CREATE DATABASE SCOPED CREDENTIAL [OracleProxyCredential]
    WITH IDENTITY = 'oracle_username',
    SECRET = 'oracle_password';

CREATE EXTERNAL DATA SOURCE [OracleSalesSrvr]
WITH (
    LOCATION = 'oracle://145.145.145.145:1521',
    CONNECTION_OPTIONS = 'ImpersonateUser=%CURRENT_USER',
    CREDENTIAL = [OracleProxyCredential]
);

または、TNS 認証を使用することもできます。

SQL Server 2019 (15.x) の累積的な更新プログラム 19 以降では、Oracle との接続時の TNS ファイルの使用を CREATE EXTERNAL DATA SOURCE がサポートすることになりました。 CONNECTION_OPTIONS パラメーターが展開されるともに、このパラメーターによって、TNSNamesFileServerName を変数として使用して tnsnames.ora ファイルを参照することでサーバーとの接続が確立されるようになりました。

次の例では、TNSNamesFile によって指定された tnsnames.ora ファイルの場所と ServerName によって指定されたホストとネットワーク ポートの検索が、実行中に SQL Server によって行われます。

CREATE EXTERNAL DATA SOURCE [external_data_source_name]
WITH (
    LOCATION = N'oracle://XE',
    CREDENTIAL = [OracleCredentialTest],
    CONNECTION_OPTIONS = N'TNSNamesFile=C:\Temp\tnsnames.ora;ServerName=XE'
);

MongoDB などの他のデータ ソースの追加の例については、「MongoDB 上の外部データにアクセスするための PolyBase の構成」を参照してください。

B. Hadoop を参照する外部データ ソースを作成する

Hortonworks HDP または Cloudera CDH Hadoop クラスターを参照する外部データ ソースを作成するには、Hadoop Namenode のマシン名または IP アドレスとポートを指定します。

CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    LOCATION = 'hdfs://10.10.10.10:8050',
    TYPE = HADOOP
);

C. プッシュダウンが有効になっている Hadoop を参照する外部データ ソースを作成する

RESOURCE_MANAGER_LOCATION オプションを指定して、PolyBase クエリの Hadoop への計算のプッシュダウンを有効にします。 有効にすると、PolyBase によって、クエリの計算を Hadoop にプッシュするかどうかがコストに基づいて決定されます。

CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    LOCATION = 'hdfs://10.10.10.10:8020',
    TYPE = HADOOP,
    RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);

D. Kerberos でセキュリティ保護された Hadoop を参照する外部データ ソースを作成する

Hadoop クラスターが Kerberos でセキュリティ保護されていることを確認するには、Hadoop core-site.xml で hadoop.security.authentication プロパティの値を確認します。 Kerberos でセキュリティ保護された Hadoop クラスターを参照するには、ご自分の Kerberos ユーザー名とパスワードを含むデータベース スコープの資格情報を指定する必要があります。 データベース マスター キーは、データベース スコープの資格情報シークレットの暗号化に使用されます。

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Kerberos user name and password.
CREATE DATABASE SCOPED CREDENTIAL HadoopUser1
    WITH IDENTITY = '<hadoop_user_name>',
    SECRET = '<hadoop_password>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    LOCATION = 'hdfs://10.10.10.10:8050',
    CREDENTIAL = HadoopUser1,
    TYPE = HADOOP,
    RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);

E. wasb:// インターフェイスを使用して Azure Storage のデータにアクセスするための外部データソースを作成する

この例では、外部データ ソースは、logs という名前の Azure V2 Storage アカウントです。 ストレージ コンテナーは daily という名前です。 Azure Storage の外部データ ソースはデータ転送専用です。 述語のプッシュ ダウンはサポートされません。 wasb:// インターフェイスを使用してデータにアクセスする場合、階層型名前空間はサポートされません。 WASB[s] コネクタ経由で Azure Storage に接続する場合は、Shared Access Signature (SAS) ではなく、ストレージ アカウント キーを使って認証を行う必要があることに注意してください。

この例では、Azure V2 Storage アカウントへの認証用にデータベース スコープ資格情報を作成する方法を示します。 データベースの資格情報シークレットで、Azure Storage アカウント キーを指定します。 Azure Storage への認証時に使用されないため、データベース スコープ資格情報 ID には任意の文字列を指定できます。

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
    WITH IDENTITY = '<my_account>',
    SECRET = '<azure_storage_account_key>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
    LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
    CREDENTIAL = AzureStorageCredential,
    TYPE = HADOOP
);

F. PolyBase 接続を使用して SQL Server 名前付きインスタンスを参照する外部データソースを作成する

適用対象: SQL Server 2019 (15.x) 以降

SQL Server の名前付きインスタンスを参照する外部データ ソースを作成するには、CONNECTION_OPTIONS を使用してインスタンス名を指定します。

次の例では、WINSQL2019 がホスト名で、SQL2019 がインスタンス名になります。 'Server=%s\SQL2019' はキーと値のペアです。

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION = 'sqlserver://WINSQL2019',
    CONNECTION_OPTIONS = 'Server=%s\SQL2019',
    CREDENTIAL = SQLServerCredentials
);

また、ポートを使用して SQL Server の既定のインスタンスに接続することもできます。

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION = 'sqlserver://WINSQL2019:58137',
    CREDENTIAL = SQLServerCredentials
);

G. Always On 可用性グループの読み取り可能なセカンダリ レプリカを参照する外部データ ソース作成する

適用対象: SQL Server 2019 (15.x) 以降

SQL Server の読み取り可能なセカンダリ レプリカを参照する外部データ ソース作成するには、CONNECTION_OPTIONS を使用して ApplicationIntent=ReadOnly を指定します。

まず、データベース スコープの資格情報を作成して、SQL 認証ログインのための資格情報を保存します。 PolyBase 用の SQL ODBC コネクタでサポートされるのは基本認証のみです。 データベース スコープ資格情報を作成する前に、データベースに資格情報を保護するためのマスター キーが必要です。 詳細については、「CREATE MASTER KEY」を参照してください。 次の例では、データベース スコープの資格情報を作成し、自分のログインとパスワードを指定します。

CREATE DATABASE SCOPED CREDENTIAL SQLServerCredentials
    WITH IDENTITY = 'username',
    SECRET = 'password';

次に、新しい外部データ ソースを作成します。

ODBC Database パラメーターは必要ありません。代わりに、LOCATION パラメーター内の CREATE EXTERNAL TABLE ステートメントで 3 つの部分を含む名前を使用してデータベース名を指定します。 例については、CREATE EXTERNAL TABLE を参照してください。

次の例では、WINSQL2019AGL は可用性グループ リスナー名を、dbname は CREATE EXTERNAL TABLE ステートメントのターゲットとなるデータベースの名前を示しています。

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION = 'sqlserver://WINSQL2019AGL',
    CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly',
    CREDENTIAL = SQLServerCredentials
);

ApplicationIntent を指定して、システム ビュー sys.servers で外部テーブルを作成することにより、可用性グループのリダイレクト動作を実演できます。 次のサンプル スクリプトでは、2 つの外部データ ソースが作成され、それぞれに対して 1 つの外部テーブルが作成されます。 ビューを使用して、どのサーバーが接続に応答しているかをテストします。 読み取り専用ルーティング機能を使用しても同様の結果を得ることができます。 詳細については、「Always On 可用性グループの読み取り専用ルーティングの構成」を参照してください。

CREATE EXTERNAL DATA SOURCE [DataSource_SQLInstanceListener_ReadOnlyIntent]
WITH (
    LOCATION = 'sqlserver://WINSQL2019AGL',
    CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly',
    CREDENTIAL = [SQLServerCredentials]
);
GO

CREATE EXTERNAL DATA SOURCE [DataSource_SQLInstanceListener_ReadWriteIntent]
WITH (
    LOCATION = 'sqlserver://WINSQL2019AGL',
    CONNECTION_OPTIONS = 'ApplicationIntent=ReadWrite',
    CREDENTIAL = [SQLServerCredentials]
);
GO

可用性グループのデータベース内で、sys.servers とローカル インスタンスの名前を返すビューを作成します。これは、どのレプリカがクエリに応答しているかを特定するのに役立ちます。 詳細については、sys.servers を参照してください。

CREATE VIEW vw_sys_servers
AS
SELECT [name]
FROM sys.servers
WHERE server_id = 0;
GO

次に、ソース インスタンスに外部テーブルを作成します。

CREATE EXTERNAL TABLE vw_sys_servers_ro (name SYSNAME NOT NULL)
WITH (
    DATA_SOURCE = [DataSource_SQLInstanceListener_ReadOnlyIntent],
    LOCATION = N'dbname.dbo.vw_sys_servers'
);
GO

CREATE EXTERNAL TABLE vw_sys_servers_rw (name SYSNAME NOT NULL)
WITH (
    DATA_SOURCE = [DataSource_SQLInstanceListener_ReadWriteIntent],
    LOCATION = N'dbname.dbo.vw_sys_servers'
);
GO

SELECT [name]
FROM dbo.vw_sys_servers_ro;--should return secondary replica instance

SELECT [name]
FROM dbo.vw_sys_servers_rw;--should return primary replica instance
GO

例: 一括操作

重要

一括操作用の外部データ ソースの構成時に、LOCATION URL の末尾に、 / 、ファイル名、Shared Access Signature パラメーターを追加しないでください。

H. Azure Storage からデータを取得する一括操作用の外部データ ソースを作成する

適用対象: SQL Server 2017 (14.x) と SQL Server 2019 (15.x)

BULK INSERT または OPENROWSET を使用する一括操作に対し、次のデータ ソースを使用します。 資格情報は、SHARED ACCESS SIGNATURE を ID として設定する必要があり、SAS トークンの先頭に ? があってはなりません。また、読み込む必要のあるファイル (たとえば srt=o&sp=r) に対して少なくとも読み取りアクセス許可が必要で、有効期限が有効である必要があります (すべての日付は UTC 時間です)。 Shared Access Signature に関する詳細については、「Shared Access Signature (SAS) を使用」を参照してください。

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

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
WITH (
    LOCATION = 'https://newinvoices.blob.core.windows.net/week3',
    CREDENTIAL = AccessAzureInvoices,
    TYPE = BLOB_STORAGE
);

この使用例については、BULK INSERT の例を参照してください。

I. abfs:// インターフェイスを使用して Azure Storage のデータにアクセスするための外部データソースを作成する

適用対象: SQL Server 2019 (15.x) CU11 以降

この例では、外部データ ソースは、Azure Blob File System ドライバー (ABFS) を使用する Azure Data Lake Storage Gen2 アカウント logs です。 ストレージ コンテナーは daily という名前です。 Azure Data Lake Storage Gen2 外部データ ソースはデータ転送専用であり、述語のプッシュダウンはサポートされません。

この例では、Azure Data Lake Storage Gen2 アカウントへの認証用にデータベース スコープ資格情報を作成する方法を示します。 データベースの資格情報シークレットで、Azure Storage アカウント キーを指定します。 Azure Storage への認証時に使用されないため、データベース スコープ資格情報 ID には任意の文字列を指定できます。

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
    WITH IDENTITY = '<my_account>',
    SECRET = '<azure_storage_account_key>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
    LOCATION = 'abfss://daily@logs.dfs.core.windows.net/',
    CREDENTIAL = AzureStorageCredential,
    TYPE = HADOOP
);

J. 汎用 ODBC to PostgreSQL を使用して外部データ ソースを作成する

前の例と同様に、まずデータベース マスター キーとデータベース スコープ資格情報を作成します。 データベース スコープ資格情報は、外部データ ソース用に使用されます。 この例では、PostgreSQL 用の汎用 ODBC データ プロバイダーがサーバーにインストールされていることも前提としています。

この例では、汎用 ODBC データ プロバイダーを使用して、同じネットワーク内の PostgreSQL データベース サーバーに接続します。ここで、PostgreSQL サーバーの完全修飾ドメイン名は POSTGRES1 であり、TCP 5432 の既定のポートが使用されます。

CREATE EXTERNAL DATA SOURCE POSTGRES1
WITH (
    LOCATION = 'odbc://POSTGRES1.domain:5432',
    CONNECTION_OPTIONS = 'Driver={PostgreSQL Unicode(x64)};',
    CREDENTIAL = postgres_credential
);

次のステップ

概要: SQL Server 2022

適用対象: SQL Server 2022 (16.x) 以降

PolyBase クエリ用の外部データ ソースを作成します。 外部データ ソースを使用して接続を確立し、次の主なユース ケースをサポートします。

  • PolyBase を使用したデータ仮想化とデータ読み込み
  • BULK INSERT または OPENROWSET を使用した一括読み込み操作

注意

この構文は、SQL Server のバージョンによって異なります。 バージョン セレクターのドロップダウンを使用して適切なバージョンを選択します。 このコンテンツは、SQL Server 2022 (16.x) 以降に適用されます。

SQL Server 2022 以降の構文

構文表記規則の詳細については、「Transact-SQL 構文表記規則」を参照してください。

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
  ( [ LOCATION = '<prefix>://<path>[:<port>]' ]
    [ [ , ] CONNECTION_OPTIONS = '<key_value_pairs>'[,...]]
    [ [ , ] CREDENTIAL = <credential_name> ]
    [ [ , ] PUSHDOWN = { ON | OFF } ]
  )
[ ; ]

引数

data_source_name

データ ソースのユーザー定義の名前を指定します。 この名前は、SQL Server のデータベース内で一意になる必要があります。

LOCATION = '<prefix>://<path[:port]>'

接続プロトコルと外部データ ソースへのパスを指定します。

外部データ ソース コネクタの場所のプレフィックス ロケーション パス 製品/サービスでサポートされている場所 認証
Azure Storage アカウント (V2) abs abs://<container_name>@<storage_account_name>.blob.core.windows.net/
または
abs://<storage_account_name>.blob.core.windows.net/<container_name>
SQL Server 2022 (16.x) 以降
階層型名前空間がサポートされています。
Shared Access Signature (SAS)
Azure Data Lake Storage Gen2 adls adls://<container_name>@<storage_account_name>.dfs.core.windows.net/
または
adls://<storage_account_name>.dfs.core.windows.net/<container_name>
SQL Server 2022 (16.x) 以降 Shared Access Signature (SAS)
SQL Server sqlserver <server_name>[\<instance_name>][:port] SQL Server 2019 (15.x) 以降 SQL 認証のみ
Oracle oracle <server_name>[:port] SQL Server 2019 (15.x) 以降 基本認証のみ
Teradata teradata <server_name>[:port] SQL Server 2019 (15.x) 以降 基本認証のみ
MongoDB または MongoDB 用 Cosmos DB API mongodb <server_name>[:port] SQL Server 2019 (15.x) 以降 基本認証のみ
汎用 ODBC odbc <server_name>[:port] SQL Server 2019 (15.x) 以降 - Windows のみ 基本認証のみ
一括操作 https <storage_account>.blob.core.windows.net/<container> SQL Server 2017 (14.x) 以降 Shared Access Signature (SAS)
S3 互換オブジェクト ストレージ s3 - S3 互換: s3://<server_name>:<port>/
- AWS S3: s3://<bucket_name>.S3.amazonaws.com[:port]/<folder>
または s3://s3.amazonaws.com[:port]/<bucket_name>/<folder>
SQL Server 2022 (16.x) 以降 Basic またはパススルー (STS) *

* IDENTITY がハードコーディングされIDENTITY = 'S3 Access Key'、Standard Edition CRET 引数が形式= '<AccessKeyID>:<SecretKeyID>'またはパススルー (STS) 承認を使用する、データベース スコープの資格情報である必要があります。 詳しくは、「S3 互換オブジェクト ストレージの外部データにアクセスするように PolyBase を構成する」をご覧ください。

場所のパス:

  • port = 外部データ ソースがリッスンしているポート。 多くの場合、ネットワーク構成に応じて省略可能です。
  • <container_name> = データを保持するストレージ アカウントのコンテナー。 ルート コンテナーは読み取り専用で、このコンテナーにデータを書き込むことはできません。
  • <storage_account> = Azure リソースのストレージ アカウント名。
  • <server_name> = ホスト名。
  • <instance_name> = SQL Server の名前付きインスタンスの名前。 ターゲット インスタンスで実行中の SQL Server Browser サービスがある場合に使用されます。
  • <ip_address>:<port> = S3 互換オブジェクト ストレージの場合のみ (SQL Server 2022 (16.x) 以降)、S3 互換ストレージへの接続に使用されるエンドポイントとポート。
  • <bucket_name> = ストレージ プラットフォームに固有の S3 互換オブジェクト ストレージ (SQL Server 2022 (16.x) 以降) の場合のみ。
  • <region> = ストレージ プラットフォームに固有の S3 互換オブジェクト ストレージ (SQL Server 2022 (16.x) 以降) の場合のみ。
  • <folder> = ストレージ URL 内のストレージ パスの一部。

場所を設定する場合の追加の注意事項とガイダンス:

  • SQL Server データベース エンジンでは、オブジェクトの作成時に、外部データ ソースの存在が検証されません。 検証するには、外部データ ソースを使用して外部テーブルを作成します。
  • sqlserver コネクタを使用すれば、SQL Server 2019 (15.x) を別の SQL Server または Azure SQL Database に接続できます。
  • Driver={<Name of Driver>} 経由で接続する際に ODBC を指定します。
  • プレフィックス adls を使用する Azure ストレージ アカウント (V2) の階層型名前空間オプションは、SQL Server 2022 (16.x) の Azure Data Lake Storage Gen2 でサポートされています。
  • SQL Server における HDFS Cloudera (CDP) と Hortonworks (HDP) の外部データ ソースのサポートは廃止され、SQL Server 2022 (16.x) には含まれません。 SQL Server 2022 (16.x) では TYPE 引数を使う必要はありません。
  • SQL Server 2022 (16.x) 以降の S3 互換オブジェクト ストレージと PolyBase の詳細については、「S3 互換オブジェクト ストレージ内の外部データにアクセスするように PolyBase を構成する」を参照してください。 S3 互換オブジェクト ストレージ内の Parquet ファイルのクエリの例については、「PolyBase を使用して S3 互換オブジェクト ストレージ内の Parquet ファイルを仮想化する」を参照してください。
  • 以前のバージョンとは異なり、SQL Server 2022 (16.x) では、Azure ストレージ アカウント (v2) に使用されるプレフィックスが wasb[s] から abs に変更されます。
  • 以前のバージョンとは異なり、SQL Server 2022 (16.x) では、Azure Data Lake Storage Gen2 に使用されるプレフィックスが abfs[s] から adls に変更されます。
  • PolyBase を使って Azure Storage の CSV ファイルを仮想化する例については、「PolyBase を使って CSV ファイルを仮想化する」を参照してください。
  • PolyBase を使って ADLS Gen2 のデルタ テーブルを仮想化する例については、「PolyBase を使ってデルタ ファイルを仮想化する」を参照してください。
  • SQL Server 2022 (16.x) では、Azure Storage Account v2 (abs) と Azure Data Lake Gen2 (adls) の両方に対して 2 つの URL 形式が完全にサポートされています。
    • LOCATION パスには、形式 <container>@<storage_account_name>.. (推奨) または <storage_account_name>../<container> を使用できます。 例:
      • Azure Storage Account v2: abs://<container>@<storage_account_name>.blob.core.windows.net (推奨) または abs://<storage_account_name>.blob.core.windows.net/<container>
      • Azure Data Lake Gen2 では adls://<container>@<storage_account_name>.blob.core.windows.net (推奨) または adls://<storage_account_name>.dfs.core.windows.net/<container> をサポートしています。

CONNECTION_OPTIONS = key_value_pair

SQL Server 2019 (15.x) 以降に対して指定します。 ODBC 経由での外部データ ソースへの接続時に、追加のオプションを指定します。 複数の接続オプションを使用するには、セミコロンで区切ります。

汎用の ODBC 接続だけでなく、SQL Server、Oracle、Teradata、MongoDB、Cosmos DB、MongoDB 用 Azure Cosmos DB API の組み込み ODBC コネクタにも適用されます。

key_value_pair は、キーワードであり、特定の接続オプションの値です。 使用できるキーワードと値は、外部データ ソースの種類によって異なります。 最低でもドライバーの名前が必要ですが、設定に便利で、トラブルシューティングに役立てることができる APP='<your_application_name>'ApplicationIntent= ReadOnly|ReadWrite などの他のオプションがあります。

使えるキーと値のペアは、ドライバーに固有です。 各プロバイダーの詳細については、「CREATE EXTERNAL DATA SOURCE (Transact-SQL) の CONNECTION_OPTIONS」をご覧ください。

適用対象: SQL Server 2022 (16.x) の累積的な更新プログラム 2 以降では、Oracle TNS ファイルをサポートするために追加のキーワードが導入されました。

  • キーワード TNSNamesFile を使用すると、Oracle サーバー上にある tnsnames.ora ファイルへのファイルパスを指定できます。
  • キーワード ServerName を使用すると、ホスト名とポートを置き換えるために使用される tnsnames.ora 内で使用されるエイリアスを指定できます。

PUSHDOWN = ON | OFF

適用対象: SQL Server 2019 (15.x) 以降 外部データ ソースに計算をプッシュ ダウンできるかどうかを示します。 既定でオンです。

PUSHDOWN は、外部データ ソース レベルで SQL Server、Oracle、Teradata、MongoDB、MongoDB 用 Azure Cosmos DB API、または ODBC に接続するときにサポートされます。

クエリ レベルでプッシュ ダウンを有効にするか、無効にするかは、ヒントによって実現します。

CREDENTIAL = credential_name

外部データ ソースへの認証の資格情報のデータベース スコープを指定します。

資格情報の作成時の追加の注意事項とガイダンス:

Shared Access Signature を作成する方法は複数あります。

  • SAS トークンを作成するには、Azure portal -><ご利用のストレージ アカウント> -> [Shared Access Signature] -> [アクセス許可の構成] -> [SAS と接続文字列を生成する] の順に移動します。 詳細については、Shared Access Signature の作成に関するページを参照してください。

  • Azure Storage Explorer を使用して SAS を作成および構成できます。

  • PowerShell、Azure CLI、.NET、REST API を使用すれば、SAS をプログラムで作成できます。 詳細については、「Shared Access Signatures (SAS) を使用して Azure Storage リソースへの制限付きアクセスを許可する」を参照してください。

  • SAS トークンは、次のように構成する必要があります。

    • SAS トークンが生成されると、トークンの先頭に疑問符 ("?") が含まれます。 SECRET として構成されている場合、先頭の ? を除外します。
    • 有効な有効期限を使用する (すべての日付が UTC 時間)。
    • 読み込む必要のあるファイル (たとえば srt=o&sp=r) に対して少なくとも読み取りアクセス許可を付与します。 さまざまなユース ケースに合わせて複数の Shared Access Signature を作成できます。 アクセス許可は次のように付与する必要があります。
    アクション 権限
    データをファイルから読み取る Read
    複数のファイルとサブフォルダーからデータを読み取る Read と List
    Create External Table as Select (CETAS) を使う 読み取り、作成、一覧表示、書き込み
  • Azure Blob Storage と Azure Data Lake Gen 2 の場合:

    • 許可されるサービス: SAS トークンを生成するには、Blob を選択する必要があります
    • 許可されるリソースの種類: SAS トークンを生成するには、ContainerObject を選択する必要があります

CREDENTIAL を S3 互換オブジェクト ストレージと PolyBase と共に使用する例については、「S3 互換オブジェクト ストレージの外部データにアクセスするように PolyBase を構成する」を参照してください。

データベース スコープ資格情報を作成するには、「CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)」を参照してください。

アクセス許可

SQL Server 内のデータベースに対する CONTROL アクセス許可が必要です。

ロック

EXTERNAL DATA SOURCE オブジェクトを共有ロックします。

セキュリティ

PolyBase では、ほとんどの外部データ ソースにプロキシ ベースの認証をサポートします。 データベース スコープ資格情報を作成して、プロキシ アカウントを作成します。

SQL Server 2022 へのアップグレード

SQL Server 2022 (16.x) 以降、Hadoop 外部データ ソースはサポートされなくなります。 以前に TYPE = HADOOP で作成された外部データ ソースと、この外部データ ソースを使用する外部テーブルを、手動で作成し直す必要があります。

また、ユーザーは、Azure Storage に接続するときに新しいコネクタを使用するように外部データ ソースを構成する必要があります。

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

重要

PolyBase をインストールして有効にする方法については、「Windows への PolyBase のインストール」を参照してください

A. SQL Server で Oracle を参照する外部データ ソースを作成する

Oracle を参照する外部データ ソースを作成するには、データベース スコープ資格情報があることを確認します。 オプションで、このデータ ソースに対して計算のプッシュ ダウンを有効または無効にすることもできます。

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL OracleProxyAccount
    WITH IDENTITY = 'oracle_username',
    SECRET = 'oracle_password';

CREATE EXTERNAL DATA SOURCE MyOracleServer
WITH (
    LOCATION = 'oracle://145.145.145.145:1521',
    CREDENTIAL = OracleProxyAccount,
    PUSHDOWN = ON
);

必要に応じて、Oracle に対する外部データソースはプロキシ認証を使用して、きめ細かいアクセス制御を提供できます。 プロキシ ユーザーは、権限が借用されているユーザーと比較してアクセスが制限されるように構成することができます。

CREATE DATABASE SCOPED CREDENTIAL [OracleProxyCredential]
    WITH IDENTITY = 'oracle_username',
    SECRET = 'oracle_password';

CREATE EXTERNAL DATA SOURCE [OracleSalesSrvr]
WITH (
    LOCATION = 'oracle://145.145.145.145:1521',
    CONNECTION_OPTIONS = 'ImpersonateUser=%CURRENT_USER',
    CREDENTIAL = [OracleProxyCredential]
);

または、TNS を使用して認証することもできます。

適用対象: SQL Server 2022 (16.x) の累積的な更新プログラム 2 以降では、Oracle との接続時の TNS ファイルの使用を CREATE EXTERNAL DATA SOURCE がサポートすることになりました。 CONNECTION_OPTIONS パラメーターが展開されるともに、このパラメーターによって、TNSNamesFileServerName を変数として使用して tnsnames.ora ファイルを参照することでサーバーとの接続が確立されるようになりました。

次の例では、TNSNamesFile によって指定された tnsnames.ora ファイルの場所と ServerName によって指定されたホストとネットワーク ポートの検索が、実行中に SQL Server によって行われます。

CREATE EXTERNAL DATA SOURCE [external_data_source_name]
WITH (
    LOCATION = N'oracle://XE',
    CREDENTIAL = [OracleCredentialTest],
    CONNECTION_OPTIONS = N'TNSNamesFile=C:\Temp\tnsnames.ora;ServerName=XE'
);

B. PolyBase 接続を使用して SQL Server 名前付きインスタンスを参照する外部データソースを作成する

適用対象: SQL Server 2019 (15.x) 以降

SQL Server の名前付きインスタンスを参照する外部データ ソースを作成するには、CONNECTION_OPTIONS を使用してインスタンス名を指定します。

まず、データベース スコープの資格情報を作成して、SQL 認証ログインのための資格情報を保存します。 PolyBase 用の SQL ODBC コネクタでサポートされるのは基本認証のみです。 データベース スコープ資格情報を作成する前に、データベースに資格情報を保護するためのマスター キーが必要です。 詳細については、「CREATE MASTER KEY」を参照してください。 次の例では、データベース スコープの資格情報を作成し、自分のログインとパスワードを指定します。

CREATE DATABASE SCOPED CREDENTIAL SQLServerCredentials
    WITH IDENTITY = 'username',
    SECRET = 'password';

次の例では、WINSQL2019 がホスト名で、SQL2019 がインスタンス名になります。 'Server=%s\SQL2019' はキーと値のペアです。

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION = 'sqlserver://WINSQL2019',
    CONNECTION_OPTIONS = 'Server=%s\SQL2019',
    CREDENTIAL = SQLServerCredentials
);

また、ポートを使用して SQL Server の既定のインスタンスに接続することもできます。

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION = 'sqlserver://WINSQL2019:58137',
    CREDENTIAL = SQLServerCredentials
);

C. Always On 可用性グループの読み取り可能なセカンダリ レプリカを参照する外部データ ソース作成する

適用対象: SQL Server 2019 (15.x) 以降

SQL Server の読み取り可能なセカンダリ レプリカを参照する外部データ ソース作成するには、CONNECTION_OPTIONS を使用して ApplicationIntent=ReadOnly を指定します。

まず、データベース スコープの資格情報を作成して、SQL 認証ログインのための資格情報を保存します。 PolyBase 用の SQL ODBC コネクタでサポートされるのは基本認証のみです。 データベース スコープ資格情報を作成する前に、データベースに資格情報を保護するためのマスター キーが必要です。 詳細については、「CREATE MASTER KEY」を参照してください。 次の例では、データベース スコープの資格情報を作成し、自分のログインとパスワードを指定します。

CREATE DATABASE SCOPED CREDENTIAL SQLServerCredentials
    WITH IDENTITY = 'username',
    SECRET = 'password';

次に、新しい外部データ ソースを作成します。

ODBC Database パラメーターは必要ありません。代わりに、LOCATION パラメーター内の CREATE EXTERNAL TABLE ステートメントで 3 つの部分を含む名前を使用してデータベース名を指定します。 例については、CREATE EXTERNAL TABLE を参照してください。

次の例では、WINSQL2019AGL は可用性グループ リスナー名を、dbname は CREATE EXTERNAL TABLE ステートメントのターゲットとなるデータベースの名前を示しています。

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION = 'sqlserver://WINSQL2019AGL',
    CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly',
    CREDENTIAL = SQLServerCredentials
);

ApplicationIntent を指定して、システム ビュー sys.servers で外部テーブルを作成することにより、可用性グループのリダイレクト動作を実演できます。 次のサンプル スクリプトでは、2 つの外部データ ソースが作成され、それぞれに対して 1 つの外部テーブルが作成されます。 ビューを使用して、どのサーバーが接続に応答しているかをテストします。 読み取り専用ルーティング機能を使用しても同様の結果を得ることができます。 詳細については、「Always On 可用性グループの読み取り専用ルーティングの構成」を参照してください。

CREATE EXTERNAL DATA SOURCE [DataSource_SQLInstanceListener_ReadOnlyIntent]
WITH (
    LOCATION = 'sqlserver://WINSQL2019AGL',
    CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly',
    CREDENTIAL = [SQLServerCredentials]
);
GO

CREATE EXTERNAL DATA SOURCE [DataSource_SQLInstanceListener_ReadWriteIntent]
WITH (
    LOCATION = 'sqlserver://WINSQL2019AGL',
    CONNECTION_OPTIONS = 'ApplicationIntent=ReadWrite',
    CREDENTIAL = [SQLServerCredentials]
);
GO

可用性グループのデータベース内で、sys.servers とローカル インスタンスの名前を返すビューを作成します。これは、どのレプリカがクエリに応答しているかを特定するのに役立ちます。 詳細については、sys.servers を参照してください。

CREATE VIEW vw_sys_servers AS
SELECT [name] FROM sys.servers
WHERE server_id = 0;
GO

次に、ソース インスタンスに外部テーブルを作成します。

CREATE EXTERNAL TABLE vw_sys_servers_ro (name SYSNAME NOT NULL)
WITH (
    DATA_SOURCE = [DataSource_SQLInstanceListener_ReadOnlyIntent],
    LOCATION = N'dbname.dbo.vw_sys_servers'
);
GO

CREATE EXTERNAL TABLE vw_sys_servers_rw (name SYSNAME NOT NULL)
WITH (
    DATA_SOURCE = [DataSource_SQLInstanceListener_ReadWriteIntent],
    LOCATION = N'dbname.dbo.vw_sys_servers'
);
GO

SELECT [name]
FROM dbo.vw_sys_servers_ro;--should return secondary replica instance

SELECT [name]
FROM dbo.vw_sys_servers_rw;--should return primary replica instance
GO

D. PolyBase を使用して S3 互換オブジェクト ストレージ内の Parquet ファイルに対してクエリを実行する外部データ ソースを作成する

適用対象: SQL Server 2022 (16.x) 以降

次のサンプル スクリプトでは、SQL Server のソース ユーザー データベースに外部データ ソース s3_ds を作成します。 外部データ ソースは、s3_dc データベース スコープの資格情報を参照します。

CREATE DATABASE SCOPED CREDENTIAL s3_dc
    WITH IDENTITY = 'S3 Access Key', -- for S3-compatible object storage the identity must always be S3 Access Key
    SECRET = '<access_key_id>:<secret_key_id>' -- provided by the S3-compatible object storage
GO

CREATE EXTERNAL DATA SOURCE s3_ds
WITH (
    LOCATION = 's3://<ip_address>:<port>/',
    CREDENTIAL = s3_dc
);
GO

sys.external_data_sources を使用して新しい外部データ ソースを確認します。

SELECT * FROM sys.external_data_sources;

次の例では、T-SQL を使用して、OPENROWSET クエリを介して S3 互換オブジェクト ストレージに格納されている Parquet ファイルに対してクエリを実行する方法を示します。 詳細については、PolyBase を使用した S3 互換オブジェクト ストレージ内の Parquet ファイルの仮想化に関するページを参照してください。

SELECT *
FROM OPENROWSET (
    BULK '/<bucket>/<parquet_folder>',
    FORMAT = 'PARQUET',
    DATA_SOURCE = 's3_ds'
) AS [cc];

E. 汎用 ODBC to PostgreSQL を使用して外部データ ソースを作成する

前の例と同様に、まずデータベース マスター キーとデータベース スコープ資格情報を作成します。 データベース スコープ資格情報は、外部データ ソース用に使用されます。 この例では、PostgreSQL 用の汎用 ODBC データ プロバイダーがサーバーにインストールされていることも前提としています。

この例では、汎用 ODBC データ プロバイダーを使用して、同じネットワーク内の PostgreSQL データベース サーバーに接続します。ここで、PostgreSQL サーバーの完全修飾ドメイン名は POSTGRES1 であり、TCP 5432 の既定のポートが使用されます。

CREATE EXTERNAL DATA SOURCE POSTGRES1
WITH (
    LOCATION = 'odbc://POSTGRES1.domain:5432',
    CONNECTION_OPTIONS = 'Driver={PostgreSQL Unicode(x64)};',
    CREDENTIAL = postgres_credential
);

Azure Storage

Shared Access Signature を作成する

Azure Blob Storage と Azure Data Lake Gen2 の両方でサポートされる認証方法は Shared Access Signature (SAS) です。 Shared Access Signature トークンを生成する簡単な方法の 1 つは、次の手順を実行することです。 詳細については、資格情報に関するページを参照してください。

  1. Azure portal で目的のストレージ アカウントに移動します。
  2. [データ ストレージ] メニューから目的のコンテナーに移動します。
  3. [共有アクセス トークン] を選びます。
  4. 次の表を参照して、目的のアクションに応じて適切なアクセス許可を選びます。
アクション 権限
データをファイルから読み取る Read
複数のファイルとサブフォルダーからデータを読み取る Read と List
Create External Table as Select (CETAS) を使う Read、Create、Write
  1. トークンの有効期限を選びます。
  2. SAS トークンと URL を生成します。
  3. SAS トークンをコピーします。

F. abs:// インターフェイスを使用して Azure Blob Storage のデータにアクセスするための外部データ ソースを作成する

適用対象: SQL Server 2022 (16.x) 以降

SQL Server 2022 (16.x) 以降では、Azure ストレージ アカウント v2 に新しいプレフィックス abs を使用します。 abs プレフィックスは SHARED ACCESS SIGNATURE を使った認証をサポートしています。 abs プレフィックスは、以前のバージョンで使われていた wasb を置き換えます。 HADOOP はサポートされなくなりました。TYPE = BLOB_STORAGE を使う必要はありません。

Azure ストレージ アカウント キーは不要になり、次の例に示すように、代わりに SAS Token を使用します。

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
GO

CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredentialv2
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE', -- to use SAS the identity must be fixed as-is
    SECRET = '<Blob_SAS_Token>';
GO

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
    LOCATION = 'abs://<container>@<storage_account_name>.blob.core.windows.net/',
    CREDENTIAL = AzureStorageCredentialv2,
);

Azure Blob Storage に格納された CSV ファイルにアクセスする方法の詳細な例については、「PolyBase を使用して CSV ファイルを仮想化する」を参照してください。

G. Azure Data Lake Gen2 のデータにアクセスする外部データ ソースを作成する

適用対象: SQL Server 2022 (16.x) 以降のバージョン

SQL Server 2022 (16.x) 以降では、Azure Data Lake Gen2 に新しいプレフィックス adls を使用し、以前のバージョンで使用されていた abfs を置き換えます。 adls プレフィックスは、次の例に示すように、認証方法として SAS トークンもサポートしています。

--Create a database scoped credential using SAS Token
CREATE DATABASE SCOPED CREDENTIAL datalakegen2
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = '<DataLakeGen2_SAS_Token>';
GO

CREATE EXTERNAL DATA SOURCE data_lake_gen2_dfs
WITH (
    LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
    CREDENTIAL = datalakegen2
);

Azure Data Lake Gen2 に格納された差分ファイルにアクセスする方法の詳細な例については、「PolyBase を使用してデルタ テーブルを仮想化する」を参照してください。

例 :一括操作

重要

一括操作用の外部データ ソースの構成時に、LOCATION URL の末尾に、 / 、ファイル名、Shared Access Signature パラメーターを追加しないでください。

H. Azure Storage からデータを取得する一括操作用の外部データ ソースを作成する

適用対象: SQL Server 2022 (16.x) 以降。

BULK INSERT または OPENROWSET を使用する一括操作に対し、次のデータ ソースを使用します。 資格情報は、SHARED ACCESS SIGNATURE を ID として設定する必要があり、SAS トークンの先頭に ? があってはなりません。また、読み込む必要のあるファイル (たとえば srt=o&sp=r) に対して少なくとも読み取りアクセス許可が必要で、有効期限が有効である必要があります (すべての日付は UTC 時間です)。 Shared Access Signature に関する詳細については、「Shared Access Signature (SAS) を使用」を参照してください。

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

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
WITH (
    LOCATION = 'abs://<container>@<storage_account_name>.blob.core.windows.net/',
    CREDENTIAL = AccessAzureInvoices,
);

次のステップ

* SQL Database *  

 

概要:Azure SQL データベース

適用対象:Azure SQL Database

エラスティック クエリ用の外部データ ソースを作成します。 外部データ ソースを使用して接続を確立し、次の主なユース ケースをサポートします。

  • BULK INSERT または OPENROWSET を使用した一括読み込み操作
  • エラスティック クエリで SQL Database を使用してリモートの SQL Database または Azure Synapse インスタンスをクエリする
  • エラスティック クエリを使用してシャード化された SQL Database のクエリを実行する

構文

構文表記規則の詳細については、「Transact-SQL 構文表記規則」を参照してください。

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
  ( [ LOCATION = '<prefix>://<path>[:<port>]' ]
    [ [ , ] CREDENTIAL = <credential_name> ]
    [ [ , ] TYPE = { BLOB_STORAGE | RDBMS | SHARD_MAP_MANAGER } ]
    [ [ , ] DATABASE_NAME = '<database_name>' ]
    [ [ , ] SHARD_MAP_NAME = '<shard_map_manager>' ] )
[ ; ]

引数

data_source_name

データ ソースのユーザー定義の名前を指定します。 この名前は、SQL Database のデータベース内で一意になる必要があります。

LOCATION = '<prefix>://<path[:port]>'

接続プロトコルと外部データ ソースへのパスを指定します。

外部データ ソース コネクタの場所のプレフィックス ロケーション パス 可用性
一括操作 https <storage_account>.blob.core.windows.net/<container>
エラスティック クエリ (シャード) 必要なし <shard_map_server_name>.database.windows.net
エラスティック クエリ (リモート) 必要なし <remote_server_name>.database.windows.net
EdgeHub edgehub edgehub:// Azure SQL Edge で "のみ" 使用できます。 EdgeHub は Azure SQL Edge のインスタンスに対して常にローカルです。 そのため、パスまたはポート値を指定する必要はありません。
Kafka kafka kafka://<kafka_bootstrap_server_name_ip>:<port_number> Azure SQL Edge で "のみ" 使用できます。

場所のパス:

  • <shard_map_server_name> = シャード マップ マネージャーをホストしている Azure での論理サーバー名。 DATABASE_NAME 引数は、シャード マップをホストするために使用するデータベースを指定し、SHARD_MAP_NAME はシャード マップ自体に使用します。
  • <remote_server_name> = エラスティック クエリのターゲット論理サーバー名。 データベース名は、DATABASE_NAME 引数を使用して指定します。

場所を設定する場合の追加の注意事項とガイダンス:

  • データベース エンジンでは、オブジェクトの作成時に、外部データ ソースの存在が検証されません。 検証するには、外部データ ソースを使用して外部テーブルを作成します。

CREDENTIAL = credential_name

外部データ ソースへの認証の資格情報のデータベース スコープを指定します。

資格情報の作成時の追加の注意事項とガイダンス:

  • Azure Storage から Azure SQL Database にデータを読み込むには、Shared Access Signature (SAS トークン) を使用します。
  • CREDENTIAL は、データがセキュリティ保護されている場合にのみ必須です。 匿名アクセスを許可するデータ セットには、CREDENTIAL は必要ありません。
  • TYPE = BLOB_STORAGE の場合、SHARED ACCESS SIGNATURE を ID として使用して資格情報を作成する必要があります。
  • WASB[s] コネクタ経由で Azure Storage に接続する場合は、Shared Access Signature (SAS) ではなく、ストレージ アカウント キーを使って認証を行う必要があります。
  • TYPE = HADOOP の場合、ストレージ アカウント キーを SECRET として使って資格情報を作成する必要があります。
  • TYPE = BLOB_STORAGE は一括操作でのみ許可されます。TYPE = BLOB_STORAGE を使って外部データ ソースに対して外部テーブルを作成することはできません。

Shared Access Signature を作成する方法は複数あります。

  • SAS トークンを作成するには、Azure portal -><ご利用のストレージ アカウント> -> [Shared Access Signature] -> [アクセス許可の構成] -> [SAS と接続文字列を生成する] の順に移動します。 詳細については、Shared Access Signature の作成に関するページを参照してください。

  • Azure Storage Explorer を使用して SAS を作成および構成できます。

  • PowerShell、Azure CLI、.NET、REST API を使用すれば、SAS をプログラムで作成できます。 詳細については、「Shared Access Signatures (SAS) を使用して Azure Storage リソースへの制限付きアクセスを許可する」を参照してください。

  • SAS トークンは、次のように構成する必要があります。

    • SAS トークンが生成されると、トークンの先頭に疑問符 ("?") が含まれます。 SECRET として構成されている場合、先頭の ? を除外します。
    • 有効な有効期限を使用する (すべての日付が UTC 時間)。
    • 読み込む必要のあるファイル (たとえば srt=o&sp=r) に対して少なくとも読み取りアクセス許可を付与します。 さまざまなユース ケースに合わせて複数の Shared Access Signature を作成できます。 アクセス許可は次のように付与する必要があります。
    アクション 権限
    データをファイルから読み取る Read
    複数のファイルとサブフォルダーからデータを読み取る Read と List
    Create External Table as Select (CETAS) を使う Read、Create、Write

SHARED ACCESS SIGNATURETYPE = BLOB_STORAGE で、CREDENTIAL を使用する例については、一括操作を実行し、Azure Storage から SQL Database にデータを取得するための外部データ ソースの作成に関するセクションを参照してください

データベース スコープ資格情報を作成するには、「CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)」を参照してください。

TYPE = [ BLOB_STORAGE | RDBMS | SHARD_MAP_MANAGER]

構成されている外部データ ソースの種類を指定します。 このパラメーターは常に必要ではありません。

  • SQL Database からのエラスティック クエリを使用したクロスデータベース クエリには、RDBMS を使用します。
  • シャード化された SQL Database への接続時に外部データ ソースを作成する場合は、SHARD_MAP_MANAGER を使用します。
  • BULK INSERT または OPENROWSET を使用して一括操作を実行する場合は、BLOB_STORAGE を使用します。

重要

他の外部データ ソースを使用する場合は、TYPE を設定しないでください。

DATABASE_NAME = database_name

この引数は、TYPERDBMS または SHARD_MAP_MANAGER に設定されている場合に構成します。

TYPE DATABASE_NAME の値
RDBMS LOCATION を使用して指定されたサーバー上のリモート データベースの名前
SHARD_MAP_MANAGER シャード マップ マネージャーとして動作しているデータベースの名前

TYPE = RDBMS の外部データ ソースを作成する方法を示す例については、「RDBMS 外部データ ソースを作成する」を参照してください

SHARD_MAP_NAME = shard_map_name

TYPE 引数がシャード マップの名前を設定するためだけに SHARD_MAP_MANAGER に設定されている場合に使用します。

TYPE = SHARD_MAP_MANAGER の外部データ ソースを作成する方法を示す例については、「Shard Map Manager の外部データ ソースを作成する」を参照してください

アクセス許可

Azure SQL Database 内のデータベースに対する CONTROL アクセス許可が必要です。

ロック

EXTERNAL DATA SOURCE オブジェクトを共有ロックします。

A. Shard Map Manager の外部データ ソースを作成する

SHARD_MAP_MANAGER を参照する外部データ ソースを作成するには、SQL Database または仮想マシン上の SQL Server データベースで Shard Map Manager をホストする SQL Database サーバー名を指定します。

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

CREATE DATABASE SCOPED CREDENTIAL ElasticDBQueryCred
    WITH IDENTITY = '<username>',
    SECRET = '<password>';

CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc
WITH (
    TYPE = SHARD_MAP_MANAGER,
    LOCATION = '<server_name>.database.windows.net',
    DATABASE_NAME = 'ElasticScaleStarterKit_ShardMapManagerDb',
    CREDENTIAL = ElasticDBQueryCred,
    SHARD_MAP_NAME = 'CustomerIDShardMap'
);

チュートリアルについては、シャーディングのエラスティック クエリの概要 (行方向のパーティション分割) のトピックを参照してください。

B. RDBMS の外部データ ソースを作成する

RDBMS を参照する外部データ ソースを作成するには、SQL Database でリモート データベースの SQL Database サーバー名を指定します。

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

CREATE DATABASE SCOPED CREDENTIAL SQL_Credential
    WITH IDENTITY = '<username>',
    SECRET = '<password>';

CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc
WITH (
    TYPE = RDBMS,
    LOCATION = '<server_name>.database.windows.net',
    DATABASE_NAME = 'Customers',
    CREDENTIAL = SQL_Credential
);

RDBMS のチュートリアルについては、クロスデータベース クエリの概要 (列方向のパーティション分割) のトピックを参照してください。

例: 一括操作

重要

一括操作用の外部データ ソースの構成時に、LOCATION URL の末尾に、 / 、ファイル名、Shared Access Signature パラメーターを追加しないでください。

C. Azure Storage からデータを取得する一括操作用の外部データ ソースを作成する

BULK INSERT または OPENROWSET を使用する一括操作に対し、次のデータ ソースを使用します。 資格情報は、SHARED ACCESS SIGNATURE を ID として設定する必要があり、SAS トークンの先頭に ? があってはなりません。また、読み込む必要のあるファイル (たとえば srt=o&sp=r) に対して少なくとも読み取りアクセス許可が必要で、有効期限が有効である必要があります (すべての日付は UTC 時間です)。 Shared Access Signature に関する詳細については、「Shared Access Signature (SAS) を使用」を参照してください。

CREATE DATABASE SCOPED CREDENTIAL AccessAzureInvoices
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    -- Remove ? from the beginning of the SAS token
    SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z***************';

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
WITH (
    LOCATION = 'https://newinvoices.blob.core.windows.net/week3',
    CREDENTIAL = AccessAzureInvoices,
    TYPE = BLOB_STORAGE
);

この使用例については、「BULK INSERT」をご覧ください。

例: Azure SQL Edge

重要

Azure SQL Edge の外部データの構成の詳細については、「Azure SQL Edge でのデータ ストリーミング」を参照してください。

A. Kafka を参照する外部データ ソースを作成する

適用対象:Azure SQL Edge "のみ"

この例では、外部データ ソースは、IP アドレス xxx.xxx.xxx.xxx を持つ Kafka サーバーであり、ポート 1900 でリッスンします。 Kafka 外部データ ソースはデータ ストリーミング専用であり、述語のプッシュ ダウンはサポートされていません。

-- Create an External Data Source for Kafka
CREATE EXTERNAL DATA SOURCE MyKafkaServer
    WITH (LOCATION = 'kafka://xxx.xxx.xxx.xxx:1900');

B. EdgeHub を参照する外部データ ソースを作成する

適用対象:Azure SQL Edge "のみ"

この例では、外部データ ソースは、Azure SQL Edge と同じエッジ デバイスで実行されている EdgeHub になります。 EdgeHub 外部データ ソースはデータ ストリーミング専用であり、述語のプッシュ ダウンはサポートされていません。

-- Create an External Data Source for Kafka
CREATE EXTERNAL DATA SOURCE MyEdgeHub
    WITH (LOCATION = 'edgehub://');

次のステップ

* Azure Synapse
Analytics *
 

 

概要:Azure Synapse Analytics

適用対象:Azure Synapse Analytics

データ仮想化用の外部データ ソースを作成します。 外部データ ソースは、接続を確立し、データ仮想化と外部データ ソースからのデータ読み込みの主要なユース ケースをサポートするために使用されます。 詳しくは、「Synapse SQL で外部テーブルを使用する」を参照してください。

重要

Azure SQL Database とエラスティック クエリを使用して Azure Synapse Analytics リソースに対してクエリを実行するために外部データ ソースを作成するには、「SQL Database」を参照してください。

構文

構文表記規則の詳細については、「Transact-SQL 構文表記規則」を参照してください。

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
( [ LOCATION = '<prefix>://<path>[:<port>]' ]
  [ [ , ] CREDENTIAL = <credential_name> ]
  [ [ , ] TYPE = HADOOP ]
)
[ ; ]

引数

data_source_name

データ ソースのユーザー定義の名前を指定します。 この名前は、Azure Synapse Analytics の Azure SQL Database 内で一意になる必要があります。

LOCATION = '<prefix>://<path>'

接続プロトコルと外部データ ソースへのパスを指定します。

外部データ ソース コネクタの場所のプレフィックス ロケーション パス
Data Lake Storage* Gen1 adl <storage_account>.azuredatalake.net
Data Lake Storage Gen2 abfs[s] <container>@<storage_account>.dfs.core.windows.net
Azure Blob Storage wasbs <container>@<storage_account>.blob.core.windows.net
Azure Blob Storage https <storage_account>.blob.core.windows.net/<container>/subfolders
Data Lake Storage Gen1 http[s] <storage_account>.azuredatalakestore.net/webhdfs/v1
Data Lake Storage Gen2 http[s] <storage_account>.dfs.core.windows.net/<container>/subfolders
Data Lake Storage Gen2 wasb[s] <container>@<storage_account>.blob.core.windows.net

* Microsoft Azure Data Lake Storage Gen1 ではサポートが制限されています。すべての新しい開発には Gen2 をお勧めします。

外部データ ソース コネクタの場所のプレフィックス 専用 SQL プール: PolyBase 専用 SQL プール: ネイティブ* サーバーレス SQL プール
Data Lake Storage** Gen1 adl いいえ 番号 はい
Data Lake Storage Gen2 abfs[s] はい イエス はい
Azure Blob Storage wasbs はい あり*** はい
Azure Blob Storage https いいえ イエス はい
Data Lake Storage Gen1 http[s] いいえ 番号 はい
Data Lake Storage Gen2 http[s] はい イエス はい
Data Lake Storage Gen2 wasb[s] はい イエス はい

* Azure Synapse Analytics のサーバーレスおよび専用 SQL プールでは、データ仮想化に異なるコード ベースを使用します。 サーバーレス SQL プールでは、ネイティブ データ仮想化テクノロジがサポートされています。 専用 SQL プールでは、ネイティブと PolyBase の両方のデータ仮想化がサポートされています。 PolyBase データ仮想化は、EXTERNAL DATA SOURCE が TYPE=HADOOP で作成されるときに使用されます。

** Microsoft Azure Data Lake Storage Gen1 ではサポートが制限されています。すべての新しい開発には Gen2 をお勧めします。

*** より安全な wasbs コネクタが wasb よりも推奨されます。 専用 SQL プールのネイティブ データ仮想化 (TYPE が HADOOP と等しくない場合) でのみ、wasb がサポートされます。

場所のパス:

  • <container> = データを保持するストレージ アカウントのコンテナー。 ルート コンテナーは読み取り専用で、このコンテナーにデータを書き込むことはできません。
  • <storage_account> = Azure リソースのストレージ アカウント名。

場所を設定する場合の追加の注意事項とガイダンス:

  • 既定のオプションでは、Azure Data Lake Storage Gen2 のプロビジョニング時にenable secure SSL connections を使用します。 この設定を有効にした場合は、セキュリティで保護された TLS/SSL 接続を選択したときに abfss を使用する必要があります。 abfss は、セキュリティで保護されていない TLS 接続にも使用できる点に注意してください。 詳細については、Azure Blob File System ドライバー (ABFS) に関する記事を参照してください。
  • Azure Synapse では、オブジェクトの作成時に、外部データ ソースの存在が検証されません。 検証するには、外部データ ソースを使用して外部テーブルを作成します。
  • 一貫性のあるクエリ セマンティクスを確保するため、Hadoop をクエリする際は、すべてのテーブルに同じ外部データ ソースを使用します。
  • https: プレフィックスを使用すると、パスでサブフォルダーを使用できます。 https は、すべてのデータ アクセス方法で使用できるわけではありません。
  • wasbs は、セキュリティで保護された TLS 接続を使用してデータが送信されるため、推奨されます。
  • 階層型名前空間は、レガシ wasb:// インターフェイスを使ってデータにアクセスする場合、Azure V2 ストレージ アカウントではサポートされませんが、wasbs:// を使う場合は階層型名前空間がサポートされます。

CREDENTIAL = credential_name

省略可能。 外部データ ソースに対して認証するためのデータベース スコープ資格情報を指定します。 資格情報のない外部データ ソースは、パブリック ストレージ アカウントにアクセスしたり、呼び出し元の Microsoft Entra ID を使用して Azure Storage 上のファイルにアクセスしたりできます。

資格情報の作成時の追加の注意事項とガイダンス:

  • Azure Storage または Azure Data Lake Store (ADLS) Gen2 から Azure Synapse Analytics にデータを読み込むには、Azure Storage キーを使用します。
  • CREDENTIAL は、データがセキュリティ保護されている場合にのみ必須です。 匿名アクセスを許可するデータ セットには、CREDENTIAL は必要ありません。

データベース スコープ資格情報を作成するには、「CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)」を参照してください。

  • サーバーレス SQL プールでは、データベース スコープの資格情報で、ワークスペースのマネージド ID、サービス プリンシパル名、または Shared Access Signature (SAS) トークンを指定できます。 パブリックに使用可能なストレージへの匿名アクセスと同様に、ユーザー ID (Microsoft Entra パススルーとも呼ばれます) を介したアクセスは、データベース スコープの資格情報でも可能です。 詳しくは、「サポートされているストレージ承認の種類」を参照してください。

  • 専用 SQL プールでは、データベース スコープの資格情報で、Shared Access Signature (SAS) トークン、カスタム アプリケーション ID、ワークスペースマネージド ID、またはストレージ アクセス キーを指定できます。

TYPE = HADOOP

省略可能、推奨されません。

TYPE は専用 SQL プールでのみ指定できます。 指定する場合、HADOOP が許可される唯一の値です。 TYPE=HADOOP がある外部データ ソースは、専用 SQL プールでのみ利用できます。

従来の実装には HADOOP を使います。それ以外の場合は、新しいネイティブ データ アクセスを使うことをお勧めします。 新しいネイティブ データ アクセスを使う場合は、TYPE 引数を指定しないでください。

TYPE = HADOOP を使用して、Azure Storage からデータを読み込む例については、「サービス プリンシパルを使用して Azure Data Lake Store Gen 1 または 2 を参照する外部データ ソースを作成する」を参照してください。

Azure Synapse Analytics のサーバーレスおよび専用 SQL プールでは、データ仮想化に異なるコード ベースを使用します。 サーバーレス SQL プールでは、ネイティブ データ仮想化テクノロジがサポートされています。 専用 SQL プールでは、ネイティブと PolyBase の両方のデータ仮想化がサポートされています。 PolyBase データ仮想化は、EXTERNAL DATA SOURCE が TYPE=HADOOP で作成されるときに使用されます。

アクセス許可

データベースに対する CONTROL 権限が必要です。

ロック

EXTERNAL DATA SOURCE オブジェクトを共有ロックします。

セキュリティ

ほとんどの外部データ ソースではプロキシ ベースの認証がサポートされており、データベース スコープ資格情報を使ってプロキシ アカウントを作成します。

Shared Access Signature (SAS) キーは、Azure Data Lake Store Gen 2 ストレージ アカウントへの認証用にサポートされています。 Shared Access Signature を使用して認証を行うお客様は、IDENTITY = "Shared Access Signature" のデータベース スコープ資格情報を作成し、SAS トークンをシークレットとして入力する必要があります。

IDENTITY = "Shared Access Signature" のデータベース スコープ資格情報を作成し、ストレージ キーの値をシークレットとして使用すると、次のエラー メッセージが表示されます。

'HdfsBridge::isDirExist - Unexpected error encountered checking whether directory exists or not: AbfsRestOperationException: Operation failed: "Server failed to authenticate the request. Please refer to the information in the www-authenticate header.", 401, HEAD, [Storage path URL]'

A. wasb:// インターフェイスを使用して Azure Storage のデータにアクセスするための外部データソースを作成する

この例では、外部データ ソースは、logs という名前の Azure ストレージ アカウント V2 です。 ストレージ コンテナーは daily という名前です。 Azure Storage の外部データ ソースはデータ転送専用です。 述語のプッシュ ダウンはサポートされません。 wasb:// インターフェイスを使用してデータにアクセスする場合、階層型名前空間はサポートされません。 WASB[s] コネクタ経由で Azure Storage に接続する場合は、Shared Access Signature (SAS) ではなく、ストレージ アカウント キーを使って認証を行う必要があることに注意してください。

この例では、従来の HADOOP の Java ベースのアクセス方法を使用します。 次の例は、Azure Storage への認証用にデータベース スコープ資格情報を作成する方法を示しています。 データベースの資格情報シークレットで、Azure Storage アカウント キーを指定します。 Azure ストレージへの認証時に使用されないため、データベース スコープ資格情報 ID には任意の文字列を指定できます。

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
    WITH IDENTITY = '<my_account>',
    SECRET = '<azure_storage_account_key>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
    LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
    CREDENTIAL = AzureStorageCredential,
    TYPE = HADOOP
);

B. サービス プリンシパルを使用して、Azure Data Lake Store Gen 1 または 2 を参照する外部データ ソースを作成する

Azure Data Lake Store の接続は、ADLS URI と Microsoft Entra アプリケーションのサービス プリンシパルに基づいて行うことができます。 このアプリケーションを作成するためのドキュメントは、Microsoft Entra ID を使用した Data Lake Store 認証で確認できます。

-- If you do not have a Master Key on your DW you will need to create one.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- These values come from your Microsoft Entra application used to authenticate to ADLS
CREATE DATABASE SCOPED CREDENTIAL ADLS_credential
WITH
    -- IDENTITY = '<clientID>@<OAuth2.0TokenEndPoint>' ,
    IDENTITY = '536540b4-4239-45fe-b9a3-629f97591c0c@https://login.microsoftonline.com/42f988bf-85f1-41af-91ab-2d2cd011da47/oauth2/token',
    -- SECRET = '<KEY>'
    SECRET = 'BjdIlmtKp4Fpyh9hIvr8HJlUida/seM5kQ3EpLAmeDI=';

-- For Gen 1 - Create an external data source
-- TYPE: HADOOP - PolyBase uses Hadoop APIs to access data in Azure Data Lake Storage.
-- LOCATION: Provide Data Lake Storage Gen 1 account name and URI
-- CREDENTIAL: Provide the credential created in the previous step
CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH (
    LOCATION = 'adl://newyorktaxidataset.azuredatalakestore.net',
    CREDENTIAL = ADLS_credential,
    TYPE = HADOOP
);

-- For Gen2 - Create an external data source
-- TYPE: HADOOP - PolyBase uses Hadoop APIs to access data in Azure Data Lake Storage.
-- LOCATION: Provide Data Lake Storage Gen2 account name and URI
-- CREDENTIAL: Provide the credential created in the previous step
CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH (
    -- Note the abfss endpoint when your account has secure transfer enabled
    LOCATION = 'abfss://data@newyorktaxidataset.dfs.core.windows.net',
    CREDENTIAL = ADLS_credential,
    TYPE = HADOOP
);

C: ストレージ アカウント キーを使用して、Azure Data Lake Store Gen2 を参照する外部データ ソースを作成する

-- If you do not have a Master Key on your DW you will need to create one.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

CREATE DATABASE SCOPED CREDENTIAL ADLS_credential
WITH
    -- IDENTITY = '<storage_account_name>' ,
    IDENTITY = 'newyorktaxidata',
    -- SECRET = '<storage_account_key>'
    SECRET = 'yz5N4+bxSb89McdiysJAzo+9hgEHcJRJuXbF/uC3mhbezES/oe00vXnZEl14U0lN3vxrFKsphKov16C0w6aiTQ==';

-- Note this example uses a Gen2 secured endpoint (abfss)
CREATE EXTERNAL DATA SOURCE < data_source_name >
WITH (
    LOCATION = 'abfss://2013@newyorktaxidataset.dfs.core.windows.net',
    CREDENTIAL = ADLS_credential,
    TYPE = HADOOP
);

D. abfs:// を使って Azure Data Lake Store Gen2 に対する外部データ ソースを作成する

マネージド ID メカニズムで Azure Data Lake Store Gen2 アカウントに接続するとき、SECRET を指定する必要はありません。

-- If you do not have a Master Key on your DW you will need to create one
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

--Create database scoped credential with **IDENTITY = 'Managed Service Identity'**
CREATE DATABASE SCOPED CREDENTIAL msi_cred
WITH IDENTITY = 'Managed Service Identity';

--Create external data source with abfss:// scheme for connecting to your Azure Data Lake Store Gen2 account
CREATE EXTERNAL DATA SOURCE ext_datasource_with_abfss
WITH (
    TYPE = HADOOP,
    LOCATION = 'abfss://myfile@mystorageaccount.dfs.core.windows.net',
    CREDENTIAL = msi_cred
);

次のステップ

* Analytics
Platform System (PDW) *
 

 

概要:分析プラットフォーム システム

適用対象:Analytics Platform System (PDW)

PolyBase クエリ用の外部データ ソースを作成します。 外部データ ソースを使用して接続を確立し、次のユース ケースをサポートします。PolyBase を使用したデータ仮想化とデータ読み込み。

構文

構文表記規則の詳細については、「Transact-SQL 構文表記規則」を参照してください。

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
  ( [ LOCATION = '<prefix>://<path>[:<port>]' ]
    [ [ , ] CREDENTIAL = <credential_name> ]
    [ [ , ] TYPE = HADOOP ]
    [ [ , ] RESOURCE_MANAGER_LOCATION = '<resource_manager>[:<port>]' )
[ ; ]

引数

data_source_name

データ ソースのユーザー定義の名前を指定します。 名前は、Analytics Platform System (PDW) のサーバー内で一意である必要があります。

LOCATION = '<prefix>://<path[:port]>'

接続プロトコルと外部データ ソースへのパスを指定します。

外部データ ソース コネクタの場所のプレフィックス ロケーション パス
Cloudera CDH または Hortonworks HDP hdfs <Namenode>[:port]
Azure Storage アカウント wasb[s] <container>@<storage_account>.blob.core.windows.net

場所のパス:

  • <Namenode> = Hadoop クラスター内の Namenode のマシン名、ネーム サービス URI、または IP アドレス。 PolyBase では Hadoop クラスターで使用されているすべての DNS 名が解決される必要があります。
  • port = 外部データ ソースがリッスンしているポート。 Hadoop では、fs.defaultFS 構成パラメーターを使用してポートを見つけることができます。 既定値は 8020 です。
  • <container> = データを保持するストレージ アカウントのコンテナー。 ルート コンテナーは読み取り専用で、このコンテナーにデータを書き込むことはできません。
  • <storage_account> = Azure リソースのストレージ アカウント名。

場所を設定する場合の追加の注意事項とガイダンス:

  • PDW エンジンでは、オブジェクトの作成時に、外部データ ソースの存在が検証されません。 検証するには、外部データ ソースを使用して外部テーブルを作成します。
  • 一貫性のあるクエリ セマンティクスを確保するため、Hadoop をクエリする際は、すべてのテーブルに同じ外部データ ソースを使用します。
  • wasbs は、セキュリティで保護された TLS 接続を使用してデータが送信されるため、推奨されます。
  • 階層型名前空間は、wasb:// 経由で Azure Storage アカウントと一緒に使用する場合はサポートされません。
  • Hadoop Namenode のフェールオーバー時に、PolyBase クエリを確実に成功させるため、Hadoop クラスターの Namenode に仮想 IP アドレスを使用することを検討してください。 使用しない場合は、ALTER EXTERNAL DATA SOURCE コマンドを実行して新しい場所を示します。

CREDENTIAL = credential_name

外部データ ソースへの認証の資格情報のデータベース スコープを指定します。

資格情報の作成時の追加の注意事項とガイダンス:

  • Azure Storage から Azure Synapse または PDW にデータを読み込むには、Azure Storage キーを使用します。
  • CREDENTIAL は、データがセキュリティ保護されている場合にのみ必須です。 匿名アクセスを許可するデータ セットには、CREDENTIAL は必要ありません。

TYPE = [ HADOOP ]

構成されている外部データ ソースの種類を指定します。 このパラメーターは常に必要ではありません。

  • 外部データ ソースが Cloudera CDH、Hortonworks HDP、Azure Storage の場合は、HADOOP を使用します。

TYPE = HADOOP を使用して、Azure Storage からデータを読み込む例については、「Hadoop を参照する外部データ ソースを作成する」を参照してください。

RESOURCE_MANAGER_LOCATION = 'ResourceManager_URI[:port]'

SQL Server 2019 (15.x) においては、Cloudera CDH、Hortonworks HDP、Azure ストレージ アカウントに接続する場合を除き、RESOURCE_MANAGER_LOCATION を指定しないでください。

Cloudera CDH、Hortonworks HDP、または Azure ストレージ アカウントのみに接続するときは、このオプションの値を構成します。 サポートされている Hadoop バージョンの完全な一覧については、「PolyBase 接続構成 (Transact-SQL)」を参照してください。

RESOURCE_MANAGER_LOCATION が定義されている場合、クエリ オプティマイザーでは、パフォーマンスを向上させるためにコストに基づいて決定が下されます。 MapReduce ジョブを使用して、Hadoop に計算をプッシュ ダウンできます。 RESOURCE_MANAGER_LOCATION を指定すると、Hadoop と SQL の間で転送されるデータ量が大幅に減少し、それによってクエリのパフォーマンスが向上する可能性があります。

Resource Manager を指定しない場合、Hadoop への計算のプッシュが、PolyBase クエリに対して無効になります。 「プッシュダウンが有効になっている Hadoop を参照する外部データ ソースを作成する」では、具体的な例と追加のガイダンスを提供しています。

RESOURCE_MANAGER_LOCATION 値は、外部データ ソースを作成するときに検証されません。 正しくない値を入力すると、指定された値で解決できないため、プッシュ ダウンが試行されるたびに実行時にクエリ エラーが発生する可能性があります。

PolyBase が Hadoop 外部データ ソースと正しく連携するためには、次の Hadoop クラスター コンポーネントのポートが開いている必要があります。

  • HDFS ポート
    • Namenode
    • Datanode
  • Resource Manager
    • ジョブの送信
    • ジョブ履歴

ポートが指定されていない場合、'hadoop connectivity' 構成の現在の設定を使用して、既定値が選択されます。

Hadoop Connectivity Resource Manager の既定のポート
1 50300
2 50300
3 8021
4 8032
5 8050
6 8032
7 8050

次の表に、これらのコンポーネントの既定のポートを示します。 Hadoop バージョンの依存関係と、既定のポート割り当てを使用しないカスタム構成の可能性があることに注意してください。

Hadoop クラスター コンポーネント [既定のポート]
NameNode 8020
DataNode (データ転送、非特権 IPC ポート) 50010
DataNode (データ転送、特権 IPC ポート) 1019
Resource Manager ジョブの送信 (Hortonworks 1.3) 50300
Resource Manager ジョブの送信 (Cloudera 4.3) 8021
Resource Manager ジョブの送信 (Windows では Hortonworks 2.0、Linux では Cloudera 5.x) 8032
Resource Manager ジョブの送信 (Linux では Hortonworks 2.x、3.0、Windows では Hortonworks 2.1-3) 8050
Resource Manager ジョブ履歴 10020

アクセス許可

Analytics Platform System (PDW) のデータベースに対する CONTROL アクセス許可が必要です。

注意

PDW の以前のリリースでは、外部データ ソースの作成には、ALTER ANY EXTERNAL DATA SOURCE アクセス許可が必要でした。

ロック

EXTERNAL DATA SOURCE オブジェクトを共有ロックします。

セキュリティ

PolyBase では、ほとんどの外部データ ソースにプロキシ ベースの認証をサポートします。 データベース スコープ資格情報を作成して、プロキシ アカウントを作成します。

HADOOP 型の SAS トークンはサポートされていません。 それは、ストレージ アカウントのアクセス キーが代わりに使用される、type = BLOB_STORAGE でのみサポートされます。 HADOOP 型と SAS 資格情報で外部データ ソースを作成しようとすると、次のエラーで失敗します。

Msg 105019, Level 16, State 1 - EXTERNAL TABLE access failed due to internal error: 'Java exception raised on call to HdfsBridge_Connect. Java exception message: Parameters provided to connect to the Azure storage account are not valid.: Error [Parameters provided to connect to the Azure storage account are not valid.] occurred while accessing external file.'

A. Hadoop を参照する外部データ ソースを作成する

Hortonworks HDP または Cloudera CDH を参照する外部データ ソースを作成するには、Hadoop Namenode のマシン名または IP アドレスとポートを指定します。

CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    LOCATION = 'hdfs://10.10.10.10:8050',
    TYPE = HADOOP
);

B. プッシュダウンが有効になっている Hadoop を参照する外部データ ソースを作成する

RESOURCE_MANAGER_LOCATION オプションを指定して、PolyBase クエリの Hadoop への計算のプッシュダウンを有効にします。 有効にすると、PolyBase によって、クエリの計算を Hadoop にプッシュするかどうかがコストに基づいて決定されます。

CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    LOCATION = 'hdfs://10.10.10.10:8020',
    TYPE = HADOOP,
    RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);

C. Kerberos でセキュリティ保護された Hadoop を参照する外部データ ソースを作成する

Hadoop クラスターが Kerberos でセキュリティ保護されていることを確認するには、Hadoop core-site.xml で hadoop.security.authentication プロパティの値を確認します。 Kerberos でセキュリティ保護された Hadoop クラスターを参照するには、ご自分の Kerberos ユーザー名とパスワードを含むデータベース スコープの資格情報を指定する必要があります。 データベース マスター キーは、データベース スコープの資格情報シークレットの暗号化に使用されます。

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Kerberos user name and password.
CREATE DATABASE SCOPED CREDENTIAL HadoopUser1
    WITH IDENTITY = '<hadoop_user_name>',
    SECRET = '<hadoop_password>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    LOCATION = 'hdfs://10.10.10.10:8050',
    CREDENTIAL = HadoopUser1,
    TYPE = HADOOP,
    RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);

D. wasb:// インターフェイスを使用して Azure Storage のデータにアクセスするための外部データソースを作成する

この例では、外部データ ソースは、logs という名前の Azure V2 Storage アカウントです。 ストレージ コンテナーは daily という名前です。 Azure Storage の外部データ ソースはデータ転送専用です。 述語のプッシュ ダウンはサポートされません。 wasb:// インターフェイスを使用してデータにアクセスする場合、階層型名前空間はサポートされません。 WASB[s] コネクタ経由で Azure Storage に接続する場合は、Shared Access Signature (SAS) ではなく、ストレージ アカウント キーを使って認証を行う必要があることに注意してください。

この例では、Azure ストレージへの認証用にデータベース スコープ資格情報を作成する方法を示します。 データベースの資格情報シークレットで、Azure ストレージ アカウント キーを指定します。 Azure ストレージへの認証時に使用されないため、データベース スコープ資格情報 ID には任意の文字列を指定できます。

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
    WITH IDENTITY = '<my_account>',
        SECRET = '<azure_storage_account_key>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
    LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
    CREDENTIAL = AzureStorageCredential,
    TYPE = HADOOP
);

次のステップ

* SQL Managed Instance *  

概要:Azure SQL Managed Instance

適用対象:Azure SQL Managed Instance

Azure SQL Managed Instance で外部データ ソースを作成します。 詳細については、「Azure SQL Managed Instance によるデータ仮想化」を参照してください。

Azure SQL Managed Instance のデータ仮想化では、OPENROWSET T-SQL 構文または CREATE EXTERNAL TABLE T-SQL 構文を使用して、さまざまなファイル形式の外部データにアクセスできます。

構文

構文表記規則の詳細については、「Transact-SQL 構文表記規則」を参照してください。

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
  ( [ LOCATION = '<prefix>://<path>[:<port>]' ]
    [ [ , ] CREDENTIAL = <credential_name> ]
  )
[ ; ]

引数

data_source_name

データ ソースのユーザー定義の名前を指定します。 名前は、データベース内で一意である必要があります。

LOCATION = '<prefix>://<path[:port]>'

接続プロトコルと外部データ ソースへのパスを指定します。

外部データ ソース 場所プレフィックス ロケーション パス
Azure Blob Storage abs abs://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>
Azure Data Lake Service Gen2 adls adls://<container>@<storage_account>.dfs.core.windows.net/<path>/<file_name>

データベース エンジンでは、オブジェクトの作成時に、外部データ ソースの存在が検証されません。 検証するには、外部データ ソースを使用して外部テーブルを作成します。

一括操作用の外部データ ソースの構成時に、LOCATION URL の末尾に、 / 、ファイル名、Shared Access Signature パラメーターを追加しないでください。

CREDENTIAL = credential_name

外部データ ソースへの認証の資格情報のデータベース スコープを指定します。

資格情報の作成時の追加の注意事項とガイダンス:

  • Azure Storage から Azure SQL Managed Instance にデータを読み込むには、Shared Access Signature (SAS トークン) を使用します。
  • CREDENTIAL は、データがセキュリティ保護されている場合にのみ必須です。 匿名アクセスを許可するデータ セットには、CREDENTIAL は必要ありません。
  • 資格情報が必要な場合は、Managed Identity または SHARED ACCESS SIGNATURE を IDENTITY として使用して目的の資格情報を作成する必要があります。 データベース スコープ資格情報を作成するには、「CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)」を参照してください。

データベース スコープ資格情報にマネージド サービス ID を使用するには:

  • WITH IDENTITY = 'Managed Identity' を指定します

    • Azure SQL Managed Instance のシステム割り当てマネージド サービス ID を使用します。これは、この目的で使用する場合、有効にする必要があります。
  • 必要な Azure Blob Storage コンテナーに対する Azure SQL Managed Instance のシステム割り当てマネージド サービス ID に閲覧者 Azure RBAC ロールを付与します。 たとえば、Azure portal を使用する場合は、「Azure portal を使用して Azure ロールを割り当てる」を参照してください。

データベース スコープ資格情報の Shared Access Signature (SAS) を作成するには:

  • WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = ... を指定します

  • Shared Access Signature を作成する方法は複数あります。

  • SAS トークンは、次のように構成する必要があります。

    • SAS トークンが生成されると、トークンの先頭に疑問符 ("?") が含まれます。 SECRET として構成されている場合、先頭の ? を除外します。
    • 有効な有効期限を使用する (すべての日付が UTC 時間)。
    • 読み込む必要のあるファイル (たとえば srt=o&sp=r) に対して少なくとも読み取りアクセス許可を付与します。 さまざまなユース ケースに合わせて複数の Shared Access Signature を作成できます。 アクセス許可は次のように付与する必要があります。
    アクション 権限
    データをファイルから読み取る Read
    複数のファイルとサブフォルダーからデータを読み取る Read と List
    Create External Table as Select (CETAS) を使う Read、Create、Write

アクセス許可

Azure SQL Managed Instance 内のデータベースに対する CONTROL アクセス許可が必要です。

ロック

EXTERNAL DATA SOURCE オブジェクトを共有ロックします。

詳細については、「Azure SQL Managed Instance によるデータ仮想化」を参照してください。

A. OPENROWSET または外部テーブルを使用して Azure SQL Managed Instance から外部データに対してクエリを実行する

その他の例については、外部データ ソースの作成に関するページまたは「Azure SQL Managed Instance によるデータ仮想化」を参照してください。

  1. データベース マスター キーが存在しない場合は作成します。

    -- Optional: Create MASTER KEY if it doesn't exist in the database:
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Strong Password>'
    GO
    
  2. SAS トークンを使用して、データベース スコープ資格情報を作成します。 マネージド ID も使用できます。

    CREATE DATABASE SCOPED CREDENTIAL MyCredential
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = '<KEY>' ; --Removing leading '?'
    GO
    
  3. 資格情報を使用して外部データ ソースを作成します。

    --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]
    );
    
  4. OPENROWSET T-SQL 構文を使用して外部データ ソース内の parquet データ ファイルに対してクエリを実行します。スキーマの推論に依存することで、スキーマを知らなくてもデータをすばやく探索できます。

    --Query data with OPENROWSET, relying on schema inference.
    SELECT TOP 10 *
    FROM OPENROWSET (
        BULK 'bing_covid-19_data.parquet',
        DATA_SOURCE = 'MyExternalDataSource',
        FORMAT = 'parquet'
    ) AS filerows;
    
  5. または、クエリの実行コストが発生する可能性があるスキーマ推論に頼るのではなく、OPENROWSET と WITH 句を使用してデータに対してクエリを実行します。 CSV では、スキーマ推論はサポートされていません。

    --Or, query data using the WITH clause on a CSV, where schema inference is not supported
    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;
    
  6. または、EXTERNAL FILE FORMAT と EXTERNAL TABLE を作成し、ローカル テーブルとしてデータに対してクエリを実行します。

    -- Or, create an EXTERNAL FILE FORMAT and an EXTERNAL TABLE
    --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 = MyFileFormat\.\./\.\./\.\./azure-sql/
    );
    GO
    
    --Then, query the data via an external table with T-SQL:
    SELECT TOP 10 *
    FROM tbl_TaxiRides;
    GO
    

次の手順