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

適用対象:yesSQL 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) 以降
階層型名前空間はサポートされていません

ロケーション パス

  • <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 は省略可能ですが、セキュリティで保護された TLS/SSL 接続を使用してデータが送信されるため、Azure Storage アカウントにアクセスする場合には推奨されています。
  • 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 ストレージ アカウントのみに接続するときは、このオプションの値を構成します。

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

Resource Manager を指定しない場合、Hadoop への計算のプッシュが、PolyBase クエリに対して無効になります。

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

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

サポートされている Hadoop バージョンの完全な一覧については、「PolyBase 接続構成 (Transact-SQL)」を参照してください。

重要

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

プッシュダウンが有効になっている Hadoop を参照する外部データ ソースを作成する」では、具体的な例と追加のガイダンスを提供しています。

アクセス許可

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

ロック

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

セキュリティ

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

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

重要

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

適用対象:yesSQL 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) 以降
階層型名前空間はサポートされていません
一括操作 https <storage_account>.blob.core.windows.net/<container> SQL Server 2017 (14.x) 以降

ロケーション パス

  • <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 は省略可能ですが、セキュリティで保護された TLS/SSL 接続を使用してデータが送信されるため、Azure Storage アカウントにアクセスする場合には推奨されています。
  • Hadoop Namenode のフェールオーバー時に、PolyBase クエリを確実に成功させるため、Hadoop クラスターの Namenode に仮想 IP アドレスを使用することを検討してください。 使用しない場合は、ALTER EXTERNAL DATA SOURCE コマンドを実行して新しい場所を示します。

CREDENTIAL = credential_name

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

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

  • CREDENTIAL は、データがセキュリティ保護されている場合にのみ必須です。 匿名アクセスを許可するデータ セットには、CREDENTIAL は必要ありません。
  • TYPE = BLOB_STORAGE の場合、SHARED ACCESS SIGNATURE を ID として使用して資格情報を作成する必要があります。 さらに、SAS トークンを次のように構成する必要があります。
    • シークレットとして構成されている場合、先頭の ? を除外する
    • 読み込む必要のあるファイル (たとえば srt=o&sp=r) に対して少なくとも読み取りアクセス許可がある
    • 有効な有効期限を使用する (すべての日付が UTC 時間)。
    • TYPE = BLOB_STORAGE は一括操作でのみ許可されます。TYPE = BLOB_STORAGE を使って外部データ ソースに対して外部テーブルを作成することはできません。
  • WASB[s] コネクタ経由で Azure Storage に接続する場合は、Shared Access Signature (SAS) ではなく、ストレージ アカウント キーを使って認証を行う必要があることに注意してください。
  • TYPE = HADOOP の場合、ストレージ アカウント キーを SECRET として使って資格情報を作成する必要があります。

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 ストレージ アカウントのみに接続するときは、このオプションの値を構成します。

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

Resource Manager を指定しない場合、Hadoop への計算のプッシュが、PolyBase クエリに対して無効になります。

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

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

サポートされている Hadoop バージョンの完全な一覧については、「PolyBase 接続構成 (Transact-SQL)」を参照してください。

重要

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

プッシュダウンが有効になっている Hadoop を参照する外部データ ソースを作成する」では、具体的な例と追加のガイダンスを提供しています。

アクセス許可

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

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

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

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

注意

この構文は、SQL Server on Linux のバージョンによって異なります。 バージョン セレクターのドロップダウンを使用して適切なバージョンを選択します。 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) 以降
階層型名前空間はサポートされていません
SQL Server sqlserver <server_name>[\<instance_name>][:port] SQL Server 2019 (15.x) 以降
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) 以降
Azure Data Lake Storage Gen2 abfs[s] abfss://<container>@<storage _account>.dfs.core.windows.net SQL Server 2019 (15.x) CU11+ 以降。
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、または Azure Synapse Analytics に接続できます。
  • Driver={<Name of Driver>} 経由で接続する際に ODBC を指定します。
  • wasbs または abfss の使用は省略可能ですが、セキュリティで保護された 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 には含まれません。 詳細については、「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 などの他のオプションがあります。

詳細については、次を参照してください。

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 として使用して資格情報を作成する必要があります。 さらに、SAS トークンを次のように構成する必要があります。
    • シークレットとして構成する場合は、先頭の ? を除外する。
    • 読み込む必要のあるファイル (たとえば srt=o&sp=r) に対して少なくとも読み取りアクセス許可がある。
    • 有効な有効期限を使用する (すべての日付が UTC 時間)。
    • TYPE = BLOB_STORAGE は一括操作でのみ許可されます。TYPE = BLOB_STORAGE を使って外部データ ソースに対して外部テーブルを作成することはできません。
  • WASB[s] コネクタ経由で Azure Storage に接続する場合は、Shared Access Signature (SAS) ではなく、ストレージ アカウント キーを使って認証を行う必要があることに注意してください。
  • TYPE = HADOOP の場合、ストレージ アカウント キーを SECRET として使って資格情報を作成する必要があります。

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 には含まれません。 詳細については、「Microsoft SQL Server プラットフォームのビッグ データ オプション」を参照してください。

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

RESOURCE_MANAGER_LOCATION = 'ResourceManager_URI[:port]'

Cloudera CDH、Hortonworks HDP、または Azure ストレージ アカウントのみに接続するときは、このオプションの値を構成します。 SQL Server 2019 (15.x) においては、Cloudera CDH、Hortonworks HDP、Azure ストレージ アカウントに接続する場合を除き、RESOURCE_MANAGER_LOCATION を指定しないでください。

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

Resource Manager を指定しない場合、Hadoop への計算のプッシュが、PolyBase クエリに対して無効になります。

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

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

サポートされている Hadoop バージョンの完全な一覧については、「PolyBase 接続構成 (Transact-SQL)」を参照してください。

重要

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

プッシュダウンが有効になっている Hadoop を参照する外部データ ソースを作成する」では、具体的な例と追加のガイダンスを提供しています。

アクセス許可

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

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) 以降。

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

次のステップ

概要: SQL Server 2022

適用対象:yes SQL Server 2022 (16.x) プレビュー以降

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

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

注意

この構文は、SQL Server on Linux のバージョンによって異なります。 バージョン セレクターのドロップダウンを使用して適切なバージョンを選択します。 このコンテンツは、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 } ]
    [ [ , ] 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) abs abs://<container>@<storage_account>.blob.core.windows.net SQL Server 2022 (16.x) プレビュー以降
階層型名前空間がサポートされています
SQL Server sqlserver <server_name>[\<instance_name>][:port] SQL Server 2019 (15.x) 以降
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) 以降
Azure Data Lake Storage Gen2 adls adls://<container>@<storage _account>.dfs.core.windows.net SQL Server 2022 (16.x) プレビュー以降
SQL Server ビッグ データ クラスターのデータ プール sqldatapool sqldatapool://controller-svc/default SQL Server 2019 のビッグ データ クラスターでのみサポート
SQL Server ビッグ データ クラスターの記憶域プール sqlhdfs sqlhdfs://controller-svc/default SQL Server 2019 のビッグ データ クラスターでのみサポート
S3 互換オブジェクト ストレージ s3 s3://<server_name>:<port>/ SQL Server 2022 (16.x) プレビュー以降

場所のパス:

  • <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 サービスがある場合に使用されます。
  • <ip_address>:<port>= S3 互換オブジェクト ストレージの場合のみ (SQL Server 2022 (16.x) プレビュー以降)、S3 互換ストレージへの接続に使用されるエンドポイントとポート。

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

  • SQL Server データベース エンジンでは、オブジェクトの作成時に、外部データ ソースの存在が検証されません。 検証するには、外部データ ソースを使用して外部テーブルを作成します。
  • 一貫性のあるクエリ セマンティクスを確保するため、Hadoop をクエリする際は、すべてのテーブルに同じ外部データ ソースを使用します。
  • sqlserver コネクタを使用すれば、SQL Server 2019 (15.x) を別の SQL Server、Azure SQL Database、または Azure Synapse Analytics に接続できます。
  • Driver={<Name of Driver>} 経由で接続する際に ODBC を指定します。
  • プレフィックス adls を使用する Azure ストレージ アカウント (V2) の階層型名前空間オプションは、SQL Server 2022 (16.x) プレビューの Azure Data Lake Storage Gen2 でサポートされています。
  • Hadoop Namenode のフェールオーバー時に、PolyBase クエリを確実に成功させるため、Hadoop クラスターの Namenode に仮想 IP アドレスを使用することを検討してください。 使用しない場合は、ALTER EXTERNAL DATA SOURCE コマンドを実行して新しい場所を示します。
  • sqlhdfs および sqldatapool の型は、SQL Server 2019 ビッグ データ クラスターのマスター インスタンスと記憶域プールの間の接続においてサポートされています。 Cloudera CDH または Hortonworks HDP の場合は、hdfs を使用します。 sqlhdfs を使用して SQL Server ビッグ データ クラスター記憶域プールに対してクエリを実行する方法の詳細については、SQL Server 2019 ビッグ データ クラスター内の HDFS にクエリを実行するに関するページを参照してください。
  • SQL Server における HDFS Cloudera (CDP) と Hortonworks (HDP) の外部データ ソースのサポートは廃止され、SQL Server 2022 には含まれません。 詳細については、「Microsoft SQL Server プラットフォームのビッグ データ オプション」を参照してください。
  • 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 に変更されます。

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 などの他のオプションがあります。

詳細については、次を参照してください。

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 として使用して資格情報を作成する必要があります。 さらに、SAS トークンを次のように構成する必要があります。
    • シークレットとして構成する場合は、先頭の ? を除外する。
    • 読み込む必要のあるファイル (たとえば srt=o&sp=r) に対して少なくとも読み取りアクセス許可がある。
    • 有効な有効期限を使用する (すべての日付が UTC 時間)。
    • TYPE = BLOB_STORAGE は一括操作でのみ許可されます。TYPE = BLOB_STORAGE を使って外部データ ソースに対して外部テーブルを作成することはできません。
  • TYPE = HADOOP の場合、ストレージ アカウント キーを SECRET として使って資格情報を作成する必要があります。

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

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

データベース スコープ資格情報を作成するには、「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 には含まれません。 詳細については、「Microsoft SQL Server プラットフォームのビッグ データ オプション」を参照してください。
  • S3 互換オブジェクト ストレージには TYPE を指定しないでください。

RESOURCE_MANAGER_LOCATION = 'ResourceManager_URI[:port]'

Cloudera CDH、Hortonworks HDP、または Azure ストレージ アカウントのみに接続するときは、このオプションの値を構成します。 SQL Server 2019 (15.x) においては、Cloudera CDH、Hortonworks HDP、Azure ストレージ アカウントに接続する場合を除き、RESOURCE_MANAGER_LOCATION を指定しないでください。

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

Resource Manager を指定しない場合、Hadoop への計算のプッシュが、PolyBase クエリに対して無効になります。

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

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

サポートされている Hadoop バージョンの完全な一覧については、「PolyBase 接続構成 (Transact-SQL)」を参照してください。

重要

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

プッシュダウンが有効になっている Hadoop を参照する外部データ ソースを作成する」では、具体的な例と追加のガイダンスを提供しています。

アクセス許可

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

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. abs:// インターフェイスを使用して Azure Storage のデータにアクセスするための外部データ ソースを作成する

SQL Server 2022 (16.x) プレビュー以降では、Azure ストレージ アカウント v2 に新しいプレフィックス abs を使用します。 abs プレフィックスでは、Shared Access Signature を使用した認証もサポートされています。 このプレフィックスは、以前のバージョンで使用されていた wasb を置き換えます。 TYPE = HADOOP の指定は不要になりました。

次の例に示すように、このシナリオでは Azure ストレージ アカウント キーは不要になりました。

-- 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>.blob.core.windows.net/' ,
    CREDENTIAL = AzureStorageCredentialv2,
  ) ;

F. 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
) ;

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

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

適用対象: SQL Server 2022 (16.x) プレビュー以降

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

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

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

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
)

例 :一括操作

重要

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

J. 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_shared_access_signature>' ;

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

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

次のステップ

* SQL Database *  

 

概要:Azure SQL データベース

適用対象:YesAzure 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 として使用して資格情報を作成する必要があります。 さらに、SAS トークンを次のように構成する必要があります。
    • シークレットとして構成されている場合、先頭の ? を除外する
    • 読み込む必要のあるファイル (たとえば srt=o&sp=r) に対して少なくとも読み取りアクセス許可がある
    • 有効な有効期限を使用する (すべての日付が UTC 時間)。
    • TYPE = BLOB_STORAGE は一括操作でのみ許可されます。TYPE = BLOB_STORAGE を使って外部データ ソースに対して外部テーブルを作成することはできません。
  • WASB[s] コネクタ経由で Azure Storage に接続する場合は、Shared Access Signature (SAS) ではなく、ストレージ アカウント キーを使って認証を行う必要があることに注意してください。
  • TYPE = HADOOP の場合、ストレージ アカウント キーを SECRET として使って資格情報を作成する必要があります。

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

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://'
)
go

次のステップ

* Azure Synapse
Analytics *
 

 

概要:Azure Synapse Analytics

適用対象:yesAzure Synapse Analytics

PolyBase 用の外部データ ソースを作成します。 外部データ ソースは、接続を確立し、PolyBase を使用したデータ仮想化とデータ読み込みの主要なユース ケースをサポートするために使用されます。

重要

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[:port]>'

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

外部データ ソース コネクタの場所のプレフィックス ロケーション パス
Azure Data Lake Store Gen 1 adl <storage_account>.azuredatalake.net
Azure Data Lake Store Gen 2 abfs[s] <container>@<storage_account>.dfs.core.windows.net
Azure V2 ストレージ アカウント wasb[s] <container>@<storage_account>.blob.core.windows.net

場所のパス:

  • <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 をクエリする際は、すべてのテーブルに同じ外部データ ソースを使用します。
  • wasbs は、セキュリティで保護された TLS 接続を使用してデータが送信されるため、推奨されます。
  • wasb:// インターフェイスを使用して PolyBase 経由でデータにアクセスする場合、Azure V2 Storage アカウントでは階層型名前空間はサポートされません。

CREDENTIAL = credential_name

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

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

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

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

TYPE = HADOOP

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

外部データ ソースが Azure Storage、ADLS Gen 1、または ADLS Gen 2 の場合、HADOOP を使用します。

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

アクセス許可

データベースに対する 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.'

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

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

この例では、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 と Azure Active Directory アプリケーションのサービス プリンシパルに基づいています。 このアプリケーションの作成方法については、Active Directory を使用した 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 Azure Active Directory 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 Gen 2 - 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 2 account name and URI
-- CREDENTIAL: Provide the credential created in the previous step
CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH
  -- Please 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 Gen 2 を参照する外部データ ソースを作成する

-- 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 Gen 2 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 Gen 2 への PolyBase 接続を参照する外部データ ソースを作成する

マネージド 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) *
 

 

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

適用対象:yesAnalytics 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]'

Cloudera CDH、Hortonworks HDP、または Azure ストレージ アカウントのみに接続するときは、このオプションの値を構成します。

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

Resource Manager を指定しない場合、Hadoop への計算のプッシュが、PolyBase クエリに対して無効になります。

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

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

サポートされている Hadoop バージョンの完全な一覧については、「PolyBase 接続構成 (Transact-SQL)」を参照してください。

重要

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

プッシュダウンが有効になっている Hadoop を参照する外部データ ソースを作成する」では、具体的な例と追加のガイダンスを提供しています。

アクセス許可

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

適用対象:YesAzure SQL Managed Instance

外部データ ソース Azure SQL Managed Instance を作成します。

注意

PolyBase 機能の一部の機能は、Azure SQL マネージド インスタンスのプレビューに含まれており、これには Azure Data Lake Storage (ADLS) Gen2 で外部データ (Parquet ファイル) に対してクエリを実行する機能が含まれています。 詳細については、「Azure SQL Managed Instance によるデータ仮想化 (プレビュー)」を参照してください。

Azure SQL Managed Instance で、外部データ ソースを使用して接続を確立し、以下をサポートします。

  • BULK INSERT または OPENROWSET を使用した一括読み込み操作

構文

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

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

引数

data_source_name

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

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

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

外部データ ソース 場所プレフィックス ロケーション パス 可用性
一括操作 https <storage_account>.blob.core.windows.net/<container>

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

CREDENTIAL = credential_name

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

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

  • Azure Storage から Azure SQL Managed Instance にデータを読み込むには、Shared Access Signature (SAS トークン) を使用します。
  • CREDENTIAL は、データがセキュリティ保護されている場合にのみ必須です。 匿名アクセスを許可するデータ セットには、CREDENTIAL は必要ありません。
  • TYPE = BLOB_STORAGE の場合、SHARED ACCESS SIGNATURE を ID として使用して資格情報を作成する必要があります。 さらに、SAS トークンを次のように構成する必要があります。
    • シークレットとして構成されている場合、先頭の ? を除外する
    • 読み込む必要のあるファイル (たとえば srt=o&sp=r) に対して少なくとも読み取りアクセス許可がある
    • 有効な有効期限を使用する (すべての日付が UTC 時間)。
    • TYPE = BLOB_STORAGE は一括操作でのみ許可されます。TYPE = BLOB_STORAGE を使って外部データ ソースに対して外部テーブルを作成することはできません。
  • WASB[s] コネクタ経由で Azure Storage に接続する場合は、Shared Access Signature (SAS) ではなく、ストレージ アカウント キーを使って認証を行う必要があることに注意してください。

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

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

TYPE = [ BLOB_STORAGE ]

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

  • BULK INSERT または OPENROWSET を使用して一括操作を実行する場合は、BLOB_STORAGE を使用します。

アクセス許可

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

ロック

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

例: 一括操作

重要

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

A. 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」をご覧ください。

次のステップ