スキーマが異なるクラウドデータベース間のクエリ (プレビュー)

[アーティクル]
06/02/2023

異なるデータベースのテーブルにまたがるクエリ

列方向にパーティション分割されたデータベースでは、データベースごとに異なるテーブルセットを使用します。これは、異なるデータベースではスキーマが異なることを意味します。たとえば、あるデータベースに在庫に関するすべてのテーブルが含まれていて、別のデータベースには会計に関連するすべてのテーブルが含まれているケースが該当します。

前提条件

ユーザーは、ALTER ANY EXTERNAL DATA SOURCE アクセス許可を所有している必要があります。このアクセス許可は、ALTER DATABASE アクセス許可に含まれています。
ALTER ANY EXTERNAL DATA SOURCE アクセス許可は、基になるデータソースを参照するために必要です。

概要

注意

行方向のパーティション分割とは異なり、これらの DDL ステートメントは、Elastic Database クライアントライブラリを介したシャードマップを使ったデータ層の定義に依存しません。

データベーススコープのマスターキーと資格情報の作成

この資格情報は、リモートデータベースに接続するために、エラスティッククエリによって使用されます。

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'master_key_password';
CREATE DATABASE SCOPED CREDENTIAL [<credential_name>]  WITH IDENTITY = '<username>',  
SECRET = '<password>';

注意

<username> にサフィックス "@servername" が含まれていないことを確認してください。

外部データソースの作成

構文:

<External_Data_Source> ::=
CREATE EXTERNAL DATA SOURCE <data_source_name> WITH
    (TYPE = RDBMS,
    LOCATION = '<fully_qualified_server_name>',
    DATABASE_NAME = '<remote_database_name>',  
    CREDENTIAL = <credential_name>
    ) [;]

重要

TYPE パラメーターを RDBMS に設定する必要があります。

例

次の例では、外部データソースに対して CREATE ステートメントを使用する方法について説明します。

CREATE EXTERNAL DATA SOURCE RemoteReferenceData
   WITH
      (
         TYPE=RDBMS,
         LOCATION='myserver.database.windows.net',
         DATABASE_NAME='ReferenceData',
         CREDENTIAL= SqlUser
      );

現在の外部データソースの一覧を取得するには:

select * from sys.external_data_sources;

外部テーブル

構文:

CREATE EXTERNAL TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name  
    ( { <column_definition> } [ ,...n ])
    { WITH ( <rdbms_external_table_options> ) }
    )[;]

<rdbms_external_table_options> ::=
    DATA_SOURCE = <External_Data_Source>,
    [ SCHEMA_NAME = N'nonescaped_schema_name',]
    [ OBJECT_NAME = N'nonescaped_object_name',]

例

CREATE EXTERNAL TABLE [dbo].[customer]
   (
      [c_id] int NOT NULL,
      [c_firstname] nvarchar(256) NULL,
      [c_lastname] nvarchar(256) NOT NULL,
      [street] nvarchar(256) NOT NULL,
      [city] nvarchar(256) NOT NULL,
      [state] nvarchar(20) NULL
   )
   WITH
   (
      DATA_SOURCE = RemoteReferenceData
   );

次の例では、外部テーブルの一覧を現在のデータベースから取得する方法を示します。

select * from sys.external_tables;

解説

エラスティッククエリでは、既存の外部テーブル構文を拡張して、RDBMS 型の外部データソースを使用する外部テーブルを定義します。列方向のパーティション分割のための外部テーブルの定義は、次の側面に対応しています。

[スキーマ] : 外部テーブル DDL では、ご利用のクエリで使用できるスキーマが定義されます。外部テーブル定義に指定するスキーマは、実際のデータが格納されているリモートデータベース内のテーブルのスキーマに一致する必要があります。
リモートデータベース参照:外部テーブル DDL では、外部データソースが参照されます。外部データソースは、実際のテーブルデータが格納されているリモートデータベースのサーバー名とデータベース名を指定します。

前のセクションで説明したように外部データソースを使用する、外部テーブルを作成するための構文を次に示します。

DATA_SOURCE 句は、外部テーブルに使用される外部データソース (つまり、列方向のパーティション分割におけるリモートデータベース) を定義します。

SCHEMA_NAME 句と OBJECT_NAME 句を使用すると、外部テーブル定義をリモートデータベース上の別のスキーマのテーブルまたは別の名前を持つテーブルにそれぞれマップすることができます。このマッピングは、リモートデータベースのカタログビューまたは DMV に対して外部テーブルを定義する場合や、リモートテーブル名が既にローカルに取得されている場合に便利です。

次の DDL ステートメントは、ローカルカタログから既存の外部テーブル定義を削除します。リモートデータベースには影響しません。

DROP EXTERNAL TABLE [ [ schema_name ] . | schema_name. ] table_name[;]

CREATE/DROP EXTERNAL TABLE に対するアクセス許可: 外部テーブル DDL に ALTER ANY EXTERNAL DATA SOURCE アクセス許可が必要です。これは、基になるデータソースを参照する場合にも必要です。

セキュリティに関する考慮事項

外部テーブルへのアクセス権を持つユーザーは、外部データソース定義に指定された資格情報の下で、基になるリモートテーブルへのアクセス権を自動的に取得します。外部データソースの資格情報を介した特権の不要な昇格を回避するために、外部テーブルへのアクセスを慎重に管理してください。外部テーブルへのアクセスは、通常の SQL 権限を使用して、通常のテーブルの場合と同様に許可または禁止することができます。

例: 列方向にパーティション分割されたデータベースのクエリ

次のクエリは、注文と注文明細行用の 2 つのローカルテーブルと、顧客用のリモートテーブルの間で 3 方向の結合を実行します。これは、エラスティッククエリの参照データのユースケースの例を示します。

    SELECT
     c_id as customer,
     c_lastname as customer_name,
     count(*) as cnt_orderline,
     max(ol_quantity) as max_quantity,
     avg(ol_amount) as avg_amount,
     min(ol_delivery_d) as min_deliv_date
    FROM customer
    JOIN orders
    ON c_id = o_c_id
    JOIN  order_line
    ON o_id = ol_o_id and o_c_id = ol_c_id
    WHERE c_id = 100

T-SQL リモート実行のストアドプロシージャ: sp_execute_remote

エラスティッククエリには、リモートデータベースへの直接アクセスを提供するストアドプロシージャも導入されています。このストアドプロシージャは sp_execute_remote と呼ばれ、リモートデータベースでリモートストアドプロシージャまたは T-SQL コードを実行するときに使用できます。使用できるパラメーターは次のとおりです。

データソース名 (nvarchar): RDBMS 型の外部データソースの名前です。
クエリ (nvarchar): リモートデータベース上で実行する T-SQL クエリです。
パラメーター宣言 (nvarchar) (省略可能): (sp_executesql などの) クエリパラメーターで使用される、パラメーターのデータ型定義を含む文字列です。
パラメーター値のリスト (省略可能): (sp_executesql などの) パラメーター値のコンマ区切りリストです。

sp_execute_remote では、起動パラメーターで指定された外部データソースを使用して、指定された T-SQL ステートメントをリモートデータベースで実行します。リモートデータベースへの接続には、外部データソースの資格情報を使用します。

例:

    EXEC sp_execute_remote
        N'MyExtSrc',
        N'select count(w_id) as foo from warehouse'

ツールの接続性

通常の SQL Server 接続文字列を使用して、BI およびデータ統合ツールを、エラスティッククエリが有効でかつ外部テーブルが定義されているサーバー上のデータベースに接続できます。使用しているツールのデータソースとして SQL Server がサポートされていることを確認してください。次に、ツールを使用して接続する他の SQL Server データベースと同様に、エラスティッククエリデータベースと外部テーブルを参照します。

ベストプラクティス

Azure SQL Database のファイアウォール構成で Azure Services のアクセスを有効にすることで、エラスティッククエリエンドポイントデータベースにリモートデータベースへのアクセスが許可されていることを確認します。さらに、外部データソース定義に指定された資格情報を使ってリモートデータベースに正常にログインでき、リモートテーブルへのアクセス許可を取得できることを確認します。
エラスティッククエリは、計算の大部分をリモートデータベース上で実行できるクエリに最適です。通常、最適なクエリパフォーマンスが得られるのは、リモートデータベース上で評価可能な選択的なフィルター述語を使用した場合、またはリモートデータベース上で完全に実行できる結合を使用した場合となります。その他のクエリパターンでは、リモートデータベースから大量のデータを読み込むことが必要になる場合があり、パフォーマンスが低下する可能性があります。

次のステップ

エラスティッククエリの概要については、「Azure SQL Database エラスティックデータベースクエリの概要 (プレビュー)」をご覧ください。
エラスティッククエリの制限事項については、「プレビューの制限事項」を参照してください。
列方向のパーティション分割のチュートリアルについては、「クロスデータベースクエリの概要 (列方向のパーティション分割) (プレビュー)」をご覧ください。
行方向のパーティション分割 (シャード化) のチュートリアルについては、「スケールアウトされたクラウドデータベース全体のレポート (プレビュー)」をご覧ください。
行方向にパーティション分割されたデータの構文とサンプルクエリについては、「スケールアウトされたクラウドデータベース全体をレポートする (プレビュー)」をご覧ください。
行方向のパーティション分割方式でシャードとして機能する単一のリモート Azure SQL Database またはデータベースのセットに対して Transact-SQL ステートメントを実行するストアドプロシージャについては、「sp_execute _remote」を参照してください。

次の方法で共有

スキーマが異なるクラウドデータベース間のクエリ (プレビュー)

前提条件

概要

データベーススコープのマスターキーと資格情報の作成

外部データソースの作成

例

外部テーブル

例

解説

セキュリティに関する考慮事項

例: 列方向にパーティション分割されたデータベースのクエリ

T-SQL リモート実行のストアドプロシージャ: sp_execute_remote

ツールの接続性

ベストプラクティス

次のステップ

フィードバック

その他のリソース

次の方法で共有

スキーマが異なるクラウド データベース間のクエリ (プレビュー)

前提条件

概要

データベース スコープのマスター キーと資格情報の作成

外部データ ソースの作成

例

外部テーブル

例

解説

セキュリティに関する考慮事項

例: 列方向にパーティション分割されたデータベースのクエリ

T-SQL リモート実行のストアド プロシージャ: sp_execute_remote

ツールの接続性

ベスト プラクティス

次のステップ

フィードバック

その他のリソース

スキーマが異なるクラウドデータベース間のクエリ (プレビュー)

データベーススコープのマスターキーと資格情報の作成

外部データソースの作成

T-SQL リモート実行のストアドプロシージャ: sp_execute_remote

ベストプラクティス