スケールアウトされたクラウド データベース全体をレポートする (プレビュー)Reporting across scaled-out cloud databases (preview)

シャード間のクエリ

シャード化されたデータベースは、スケールアウトされたデータ層の全体に行を分散させます。Sharded databases distribute rows across a scaled out data tier. スキーマは、すべての参加データベース上で同じで、行方向のパーティション分割とも呼ばれます。The schema is identical on all participating databases, also known as horizontal partitioning. エラスティック クエリを使用すると、シャード化されたデータベース内のすべてのデータベースにまたがるレポートを作成できます。Using an elastic query, you can create reports that span all databases in a sharded database.

クイック スタートについては、「 スケールアウトされたクラウド データベース全体をレポートする」を参照してください。For a quick start, see Reporting across scaled-out cloud databases.

シャード化されていないデータベースについては、「 Query across cloud databases with different schemas (スキーマが異なるクラウド データベース間のクエリ)」をご覧ください。For non-sharded databases, see Query across cloud databases with different schemas.

前提条件Prerequisites

概要Overview

これらのステートメントを使うと、エラスティック クエリ データベース内のシャーディングされたデータのメタデータ表現を作成できます。These statements create the metadata representation of your sharded data tier in the elastic query database.

  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

1.1 データベース スコープのマスター キーと資格情報の作成1.1 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 = 'password';
CREATE DATABASE SCOPED CREDENTIAL <credential_name>  WITH IDENTITY = '<username>',  
SECRET = '<password>'
[;]

注意

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

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

構文:Syntax:

<External_Data_Source> ::=    
CREATE EXTERNAL DATA SOURCE <data_source_name> WITH                                              
        (TYPE = SHARD_MAP_MANAGER,
                   LOCATION = '<fully_qualified_server_name>',
        DATABASE_NAME = ‘<shardmap_database_name>',
        CREDENTIAL = <credential_name>, 
        SHARD_MAP_NAME = ‘<shardmapname>’ 
               ) [;] 

Example

CREATE EXTERNAL DATA SOURCE MyExtSrc 
WITH 
( 
    TYPE=SHARD_MAP_MANAGER,
    LOCATION='myserver.database.windows.net', 
    DATABASE_NAME='ShardMapDatabase', 
    CREDENTIAL= SMMUser, 
    SHARD_MAP_NAME='ShardMap' 
);

現在の外部データ ソースの一覧を取得します。Retrieve the list of current external data sources:

select * from sys.external_data_sources; 

外部データ ソースは、シャード マップを参照します。The external data source references your shard map. エラスティック クエリは、外部データ ソースと基になるシャード マップを使用して、データ層にあるデータベースを列挙します。An elastic query then uses the external data source and the underlying shard map to enumerate the databases that participate in the data tier. シャード マップを読み取る場合と、エラスティック クエリの処理中にシャード上のデータにアクセスする場合は、同じ資格情報が使用されます。The same credentials are used to read the shard map and to access the data on the shards during the processing of an elastic query.

1.3 外部テーブルの作成1.3 Create external tables

構文:Syntax:

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

<sharded_external_table_options> ::= 
  DATA_SOURCE = <External_Data_Source>,       
  [ SCHEMA_NAME = N'nonescaped_schema_name',] 
  [ OBJECT_NAME = N'nonescaped_object_name',] 
  DISTRIBUTION = SHARDED(<sharding_column_name>) | REPLICATED |ROUND_ROBIN

Example

CREATE EXTERNAL TABLE [dbo].[order_line]( 
     [ol_o_id] int NOT NULL, 
     [ol_d_id] tinyint NOT NULL,
     [ol_w_id] int NOT NULL, 
     [ol_number] tinyint NOT NULL, 
     [ol_i_id] int NOT NULL, 
     [ol_delivery_d] datetime NOT NULL, 
     [ol_amount] smallmoney NOT NULL, 
     [ol_supply_w_id] int NOT NULL, 
     [ol_quantity] smallint NOT NULL, 
     [ol_dist_info] char(24) NOT NULL 
) 

WITH 
( 
    DATA_SOURCE = MyExtSrc, 
     SCHEMA_NAME = 'orders', 
     OBJECT_NAME = 'order_details', 
    DISTRIBUTION=SHARDED(ol_w_id)
); 

外部テーブルの一覧を現在のデータベースから取得します。Retrieve the list of external tables from the current database:

SELECT * from sys.external_tables; 

外部テーブルを削除するにはTo drop external tables:

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

解説Remarks

DATA_SOURCE 句は、外部テーブルに使用される外部データ ソース (シャード マップ) を定義します。The DATA_SOURCE clause defines the external data source (a shard map) that is used for the external table.

SCHEMA_NAME 句と OBJECT_NAME 句は、外部テーブルの定義を別のスキーマ内のテーブルにマップします。The SCHEMA_NAME and OBJECT_NAME clauses map the external table definition to a table in a different schema. これらを省略した場合、リモート オブジェクトのスキーマは "dbo" と見なされ、その名前は定義されている外部テーブルの名前と同一であると見なされます。If omitted, the schema of the remote object is assumed to be “dbo” and its name is assumed to be identical to the external table name being defined. これは、リモート テーブルの名前が、外部テーブルを作成するデータベースで既に取得されている場合に便利です。This is useful if the name of your remote table is already taken in the database where you want to create the external table. たとえば、スケールアウトされたデータ層のカタログ ビューまたは DMV の集計ビューを取得する外部テーブルを定義する場合が挙げられます。For example, you want to define an external table to get an aggregate view of catalog views or DMVs on your scaled out data tier. カタログ ビューと DMV は既にローカルに存在するため、外部テーブルの定義にその名前を使うことはできません。Since catalog views and DMVs already exist locally, you cannot use their names for the external table definition. 代わりに、別の名前を使用して、カタログ ビューまたは DMV の名前を SCHEMA_NAME 句または OBJECT_NAME 句で使用します Instead, use a different name and use the catalog view’s or the DMV’s name in the SCHEMA_NAME and/or OBJECT_NAME clauses. (次の例を参照してください)。(See the example below.)

DISTRIBUTION 句は、このテーブルに使用するデータ分散を指定します。The DISTRIBUTION clause specifies the data distribution used for this table. クエリ プロセッサは、DISTRIBUTION 句で提供される情報を使用して、最も効率的なクエリ プランを作成します。The query processor utilizes the information provided in the DISTRIBUTION clause to build the most efficient query plans.

  1. SHARDED は、データがデータベース間で行方向にパーティション分割されることを意味します。SHARDED means data is horizontally partitioned across the databases. データ分散のパーティション分割キーは、<sharding_column_name> パラメーターです。The partitioning key for the data distribution is the <sharding_column_name> parameter.
  2. REPLICATED は、テーブルの同一のコピーが各データベースに存在することを意味します。REPLICATED means that identical copies of the table are present on each database. データベース間でレプリカが同じであることを自分で確認する必要があります。It is your responsibility to ensure that the replicas are identical across the databases.
  3. ROUND_ROBIN は、テーブルがアプリケーションに依存する分散方法を使用して、行方向にパーティション分割されることを意味します。ROUND_ROBIN means that the table is horizontally partitioned using an application-dependent distribution method.

データ層参照: 外部テーブル DDL は、外部データ ソースを参照します。Data tier reference: The external table DDL refers to an external data source. 外部データ ソースは、データ層のすべてのデータベースを見つけるために必要な情報を外部テーブルに提供するシャード マップを指定します。The external data source specifies a shard map which provides the external table with the information necessary to locate all the databases in your data tier.

セキュリティに関する考慮事項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. 外部データ ソースの資格情報による不要な特権の昇格を防ぎます。Avoid undesired elevation of privileges through the credential of the external data source. 外部テーブルに対して、通常のテーブルであるかのように GRANT または REVOKE を使用します。Use GRANT or REVOKE for an external table just as though it were a regular table.

外部データ ソースと外部テーブルを定義すると、外部テーブルに対して完全に T-SQL を使用できるようになります。Once you have defined your external data source and your external tables, you can now use full T-SQL over your external tables.

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

次のクエリでは、倉庫、注文、および注文明細行の間で 3 方向結合を実行し、いくつかの集計と選択的フィルターを使用します。The following query performs a three-way join between warehouses, orders and order lines and uses several aggregates and a selective filter. ここでは、(1) 行方向のパーティション分割 (シャーディング) のほか、(2) 倉庫、注文、および注文明細行が倉庫の ID 列でシャード化されること、エラスティック クエリがシャード上の結合を併置できること、クエリの負荷の高い部分をシャード上で並列に処理できることを想定しています。It assumes (1) horizontal partitioning (sharding) and (2) that warehouses, orders and order lines are sharded by the warehouse id column, and that the elastic query can co-locate the joins on the shards and process the expensive part of the query on the shards in parallel.

select  
     w_id as warehouse,
     o_c_id as customer,
     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 warehouse 
join orders 
on w_id = o_w_id
join order_line 
on o_id = ol_o_id and o_w_id = ol_w_id 
where w_id > 100 and w_id < 200 
group by w_id, o_c_id 

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 shards. このストアド プロシージャは 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 databases. 使用できるパラメーターは次のとおりです。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 each shard.
  • パラメーター宣言 (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 databases. shardmap マネージャー データベースとリモート データベースへの接続には、外部データ ソースの資格情報を使用します。It uses the credential of the external data source to connect to the shardmap manager database and the remote databases.

例:Example:

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

ツールの接続性Connectivity for tools

通常の SQL Server 接続文字列を使用して、アプリケーション、BI、およびデータ統合ツールを、外部テーブル定義を持つデータベースに接続できます。Use regular SQL Server connection strings to connect your application, your BI and data integration tools to the database with your external table definitions. 使用しているツールのデータ ソースとして SQL Server がサポートされていることを確認してください。Make sure that SQL Server is supported as a data source for your tool. 次に、ツールに接続される他の SQL Server データベースと同様にエラスティック クエリ データベースを参照して、外部テーブルをローカル テーブルであるかのようにツールまたはアプリケーションから使用します。Then reference the elastic query database like any other SQL Server database connected to the tool, and use external tables from your tool or application as if they were local tables.

ベスト プラクティスBest practices

  • エラスティック クエリ エンドポイント データベースに、SQL データベース ファイアウォール経由でのシャード マップ データベースとすべてのシャードへのアクセスが許可されていることを確認します。Ensure that the elastic query endpoint database has been given access to the shardmap database and all shards through the SQL DB firewalls.
  • 外部テーブルで定義されたデータ分散を検証または適用しません。Validate or enforce the data distribution defined by the external table. 実際のデータ分散がテーブル定義に指定されたデータ分散と異なる場合、クエリが予期しない結果を生成する場合があります。If your actual data distribution is different from the distribution specified in your table definition, your queries may yield unexpected results.
  • シャーディング キーによる述語で特定のシャードを処理から安全に除外できる場合、エラスティック クエリでは、現在のところ、シャードの除去を実行しません。Elastic query currently does not perform shard elimination when predicates over the sharding key would allow it to safely exclude certain shards from processing.
  • エラスティック クエリは、計算の大部分をシャード上で実行できるクエリに最適です。Elastic query works best for queries where most of the computation can be done on the shards. 通常、最適なクエリ パフォーマンスが得られるのは、シャード上で評価可能な選択的なフィルター述語を使用した場合、またはすべてのシャード上でパーティション分割方法により実行可能な、パーティション分割キーによる結合を使用した場合となります。You typically get the best query performance with selective filter predicates that can be evaluated on the shards or joins over the partitioning keys that can be performed in a partition-aligned way on all shards. その他のクエリ パターンでは、シャードからヘッド ノードに大量のデータを読み込むことが必要になる場合があり、パフォーマンスが低下する可能性があります。Other query patterns may need to load large amounts of data from the shards to the head node and may perform poorly

次のステップNext steps