スキーマが異なるクラウド データベース間のクエリ (プレビュー)Query across cloud databases with different schemas (preview)

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

列方向にパーティション分割されたデータベースでは、データベースごとに異なるテーブル セットを使用します。Vertically-partitioned databases use different sets of tables on different databases. これは、異なるデータベースではスキーマが異なることを意味します。That means that the schema is different on different databases. たとえば、あるデータベースに在庫に関するすべてのテーブルが含まれていて、別のデータベースには会計に関連するすべてのテーブルが含まれているケースが該当します。For instance, all tables for inventory are on one database while all accounting-related tables are on a second database.

前提条件Prerequisites

  • ユーザーは、ALTER ANY EXTERNAL DATA SOURCE アクセス許可を所有している必要があります。The user must possess ALTER ANY EXTERNAL DATA SOURCE permission. このアクセス許可は、ALTER DATABASE アクセス許可に含まれています。This permission is included with the ALTER DATABASE permission.
  • ALTER ANY EXTERNAL DATA SOURCE アクセス許可は、基になるデータ ソースを参照するために必要です。ALTER ANY EXTERNAL DATA SOURCE permissions are needed to refer to the underlying data source.

概要Overview

注意

行方向のパーティション分割とは異なり、これらの DDL ステートメントは、Elastic Database クライアント ライブラリを介したシャード マップを使ったデータ層の定義に依存しません。Unlike with horizontal partitioning, these DDL statements do not depend on defining a data tier with a shard map through the elastic database client library.

  1. CREATE MASTER KEYCREATE MASTER KEY
  2. CREATE DATABASE SCOPED CREDENTIALCREATE DATABASE SCOPED CREDENTIAL
  3. CREATE EXTERNAL DATA SOURCECREATE EXTERNAL DATA SOURCE
  4. CREATE EXTERNAL TABLECREATE EXTERNAL TABLE

データベース スコープのマスター キーと資格情報の作成Create database scoped master key and credentials

この資格情報は、リモート データベースに接続するために、エラスティック クエリによって使用されます。The credential is used by the elastic query to connect to your remote databases.

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

注意

<username> にサフィックス "@servername" が含まれていないことを確認してください。Ensure that the <username> does not include any "@servername" suffix.

外部データ ソースの作成Create external data sources

構文:Syntax:

<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 に設定する必要があります。The TYPE parameter must be set to RDBMS.

Example

次の例では、外部データ ソースに対して CREATE ステートメントを使用する方法について説明します。The following example illustrates the use of the CREATE statement for external data sources.

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

現在の外部データ ソースの一覧を取得するには:To retrieve the list of current external data sources:

select * from sys.external_data_sources; 

外部テーブルExternal Tables

構文:Syntax:

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',] 

Example

    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, 
        [country] nvarchar(50) NOT NULL, 
    ) 
    WITH 
    ( 
           DATA_SOURCE = RemoteReferenceData 
    ); 

次の例では、外部テーブルの一覧を現在のデータベースから取得する方法を示します。The following example shows how to retrieve the list of external tables from the current database:

select * from sys.external_tables; 

解説Remarks

エラスティック クエリでは、既存の外部テーブル構文を拡張して、RDBMS 型の外部データ ソースを使用する外部テーブルを定義します。Elastic query extends the existing external table syntax to define external tables that use external data sources of type RDBMS. 列方向のパーティション分割のための外部テーブルの定義は、次の側面に対応しています。An external table definition for vertical partitioning covers the following aspects:

  • スキーマ: 外部テーブル DDL では、ご利用のクエリで使用できるスキーマが定義されます。Schema: The external table DDL defines a schema that your queries can use. 外部テーブル定義に指定するスキーマは、実際のデータが格納されているリモート データベース内のテーブルのスキーマに一致する必要があります。The schema provided in your external table definition needs to match the schema of the tables in the remote database where the actual data is stored.
  • リモート データベース参照:外部テーブル DDL では、外部データ ソースが参照されます。Remote database reference: The external table DDL refers to an external data source. 外部データ ソースは、SQL Database サーバー名と、実際のテーブル データが格納されているリモート データベースのデータベース名を指定します。The external data source specifies the SQL Database server name and database name of the remote database where the actual table data is stored.

前のセクションで説明したように外部データ ソースを使用する、外部テーブルを作成するための構文を次に示します。Using an external data source as outlined in the previous section, the syntax to create external tables is as follows:

DATA_SOURCE 句は、外部テーブルに使用される外部データ ソース (つまり、列方向のパーティション分割の場合はリモート データベース) を定義します。The DATA_SOURCE clause defines the external data source (i.e. the remote database in case of vertical partitioning) that is used for the external table.

SCHEMA_NAME 句と OBJECT_NAME 句は、外部テーブル定義をリモート データベース上の別のスキーマのテーブルまたは別の名前を持つテーブルにマップする機能をそれぞれ提供します。The SCHEMA_NAME and OBJECT_NAME clauses provide the ability to map the external table definition to a table in a different schema on the remote database, or to a table with a different name, respectively. これは、リモート データベースのカタログ ビューまたは DMV に対して外部テーブルを定義する場合や、リモート テーブル名が既にローカルに取得されている場合に便利です。This is useful if you want to define an external table to a catalog view or DMV on your remote database - or any other situation where the remote table name is already taken locally.

次の DDL ステートメントは、ローカル カタログから既存の外部テーブル定義を削除します。The following DDL statement drops an existing external table definition from the local catalog. リモート データベースには影響しません。It does not impact the remote database.

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

CREATE/DROP EXTERNAL TABLE に対するアクセス許可: 外部テーブル DDL に ALTER ANY EXTERNAL DATA SOURCE アクセス許可が必要です。これは、基になるデータ ソースを参照する場合にも必要です。Permissions for CREATE/DROP EXTERNAL TABLE: ALTER ANY EXTERNAL DATA SOURCE permissions are needed for external table DDL which is also needed to refer to the underlying data source.

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

外部テーブルへのアクセス権を持つユーザーは、外部データ ソース定義に指定された資格情報の下で、基になるリモート テーブルへのアクセス権を自動的に取得します。Users with access to the external table automatically gain access to the underlying remote tables under the credential given in the external data source definition. 外部データ ソースの資格情報を介した特権の不要な昇格を回避するために、外部テーブルへのアクセスを慎重に管理する必要があります。You should carefully manage access to the external table in order to avoid undesired elevation of privileges through the credential of the external data source. 外部テーブルへのアクセスは、通常の SQL 権限を使用して、通常のテーブルの場合と同様に許可または禁止することができます。Regular SQL permissions can be used to GRANT or REVOKE access to an external table just as though it were a regular table.

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

次のクエリは、注文と注文明細行用の 2 つのローカル テーブルと、顧客用のリモート テーブルの間で 3 方向の結合を実行します。The following query performs a three-way join between the two local tables for orders and order lines and the remote table for customers. これは、エラスティック クエリの参照データのユース ケースの例を示します。This is an example of the reference data use case for elastic query:

    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_remoteStored procedure for remote T-SQL execution: sp_execute_remote

エラスティック クエリには、リモート データベースへの直接アクセスを提供するストアド プロシージャも導入されています。Elastic query also introduces a stored procedure that provides direct access to the remote database. このストアド プロシージャは sp_execute _remote と呼ばれ、リモート データベースでリモート ストアド プロシージャまたは T-SQL コードを実行するときに使用できます。The stored procedure is called sp_execute _remote and can be used to execute remote stored procedures or T-SQL code on the remote database. 使用できるパラメーターは次のとおりです。It takes the following parameters:

  • データ ソース名 (nvarchar): RDBMS 型の外部データ ソースの名前です。Data source name (nvarchar): The name of the external data source of type RDBMS.
  • クエリ (nvarchar): リモート データベース上で実行する T-SQL クエリです。Query (nvarchar): The T-SQL query to be executed on the remote database.
  • パラメーター宣言 (nvarchar) (省略可能): (sp_executesql などの) クエリ パラメーターで使用される、パラメーターのデータ型定義を含む文字列です。Parameter declaration (nvarchar) - optional: String with data type definitions for the parameters used in the Query parameter (like sp_executesql).
  • パラメーター値のリスト (省略可能): (sp_executesql などの) パラメーター値のコンマ区切りリストです。Parameter value list - optional: Comma-separated list of parameter values (like sp_executesql).

sp_execute_remote では、起動パラメーターで指定された外部データ ソースを使用して、指定された T-SQL ステートメントをリモート データベースで実行します。The sp_execute_remote uses the external data source provided in the invocation parameters to execute the given T-SQL statement on the remote database. リモート データベースへの接続には、外部データ ソースの資格情報を使用します。It uses the credential of the external data source to connect to the remote database.

例:Example:

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

ツールの接続性Connectivity for tools

通常の SQL Server 接続文字列を使用して、BI およびデータ統合ツールを、エラスティック クエリが有効でかつ外部テーブルが定義されている SQL DB サーバー上のデータベースに接続できます。You can use regular SQL Server connection strings to connect your BI and data integration tools to databases on the SQL DB server that has elastic query enabled and external tables defined. 使用しているツールのデータ ソースとして SQL Server がサポートされていることを確認してください。Make sure that SQL Server is supported as a data source for your tool. 次に、ツールを使用して接続する他の SQL Server データベースと同様に、エラスティック クエリ データベースと外部テーブルを参照します。Then refer to the elastic query database and its external tables just like any other SQL Server database that you would connect to with your tool.

ベスト プラクティスBest practices

  • SQL DB のファイアウォール構成で Azure Services のアクセスを有効にすることで、エラスティック クエリ エンドポイント データベースにリモート データベースへのアクセスが許可されていることを確認します。Ensure that the elastic query endpoint database has been given access to the remote database by enabling access for Azure Services in its SQL DB firewall configuration. さらに、外部データ ソース定義に指定された資格情報を使ってリモート データベースに正常にログインでき、リモート テーブルへのアクセス許可を取得できることを確認します。Also ensure that the credential provided in the external data source definition can successfully log into the remote database and has the permissions to access the remote table.
  • エラスティック クエリは、計算の大部分をリモート データベース上で実行できるクエリに最適です。Elastic query works best for queries where most of the computation can be done on the remote databases. 通常、最適なクエリ パフォーマンスが得られるのは、リモート データベース上で評価可能な選択的なフィルター述語を使用した場合、またはリモート データベース上で完全に実行できる結合を使用した場合となります。You typically get the best query performance with selective filter predicates that can be evaluated on the remote databases or joins that can be performed completely on the remote database. その他のクエリ パターンでは、リモート データベースから大量のデータを読み込むことが必要になる場合があり、パフォーマンスが低下する可能性があります。Other query patterns may need to load large amounts of data from the remote database and may perform poorly.

次の手順Next steps