次の方法で共有

スケールアウトされたクラウド データベース全体をレポートする (プレビュー)

適用対象:Azure SQL データベース

シャード化されたデータベースは、スケールアウトされたデータ層の全体に行を分散させます。 スキーマは、すべての参加データベース上で同じで、行方向のパーティション分割とも呼ばれます。 エラスティック クエリを使用すると、シャード化されたデータベース内のすべてのデータベースにまたがるレポートを作成できます。

シャード間でのクエリのしくみの図。

クイック スタートについては、 スケールアウトされたクラウド データベース全体のレポート (プレビュー) を参照してください。

シャード化されていないデータベースについては、「スキーマが 異なるクラウド データベース間のクエリ (プレビュー)」を参照してください。

前提条件

概要

これらのステートメントを使うと、エラスティック クエリ データベース内のシャーディングされたデータのメタデータ表現を作成できます。

  1. CREATE MASTER KEY
  2. データベース スコープの資格情報を作成する
  3. 外部データ ソースを作成する
  4. CREATE EXTERNAL TABLE（外部テーブルを作成）

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

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

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

注意

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

1.2 外部データ ソースの作成

構文:

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

例

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

現在の外部データ ソースの一覧を取得します。

select * from sys.external_data_sources;

外部データ ソースは、シャード マップを参照します。 エラスティック クエリは、外部データ ソースと基になるシャード マップを使用して、データ層にあるデータベースを列挙します。

シャード マップを読み取る場合と、エラスティック クエリの処理中にシャード上のデータにアクセスする場合は、同じ資格情報が使用されます。

1.3 外部テーブルの作成

構文:

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

例

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

外部テーブルの一覧を現在のデータベースから取得します。

SELECT * from sys.external_tables;

外部テーブルを削除するには

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

解説

DATA_SOURCE句は、外部テーブルに使用される外部データ ソース (シャード マップ) を定義します。

SCHEMA_NAME句と OBJECT_NAME 句は、外部テーブル定義を別のスキーマ内のテーブルにマップします。 これらを省略した場合、リモート オブジェクトのスキーマは dbo と見なされ、その名前は定義されている外部テーブルの名前と同一であると見なされます。 これは、リモート テーブルの名前が、外部テーブルを作成するデータベースで既に取得されている場合に便利です。 たとえば、スケールアウトされたデータ層のカタログ ビューまたは DMV の集計ビューを取得する外部テーブルを定義する場合が挙げられます。 カタログ ビューと DMV は既にローカルに存在するため、外部テーブルの定義にその名前を使うことはできません。 代わりに、別の名前を使用し、カタログ ビューまたは DMV の名前を SCHEMA_NAME 句または OBJECT_NAME 句で使用します。 (後の例を参照してください)。

DISTRIBUTION句は、このテーブルに使用されるデータ分散を指定します。 クエリ プロセッサは、 DISTRIBUTION 句で提供される情報を利用して、最も効率的なクエリ プランを構築します。

  1. SHARDED は、データがデータベース間で水平方向にパーティション分割されていることを意味します。 データ分散のパーティション分割キーは、<sharding_column_name> パラメーターです。
  2. REPLICATED は、テーブルのコピーが各データベースに存在することを意味します。 データベース間でレプリカが同じであることを自分で確認する必要があります。
  3. ROUND_ROBIN は、アプリケーション依存の分散方法を使用してテーブルが水平方向にパーティション分割されることを意味します。

データ層参照: 外部テーブル DDL では、外部データ ソースが参照されます。 外部データ ソースは、データ層のすべてのデータベースを見つけるために必要な情報を外部テーブルに提供するシャード マップを指定します。

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

外部テーブルへのアクセス権を持つユーザーは、外部データ ソース定義に指定された資格情報の下で、基になるリモート テーブルへのアクセス権を自動的に取得します。 外部データ ソースの資格情報による不要な特権の昇格を防ぎます。 外部テーブルに対して、通常のテーブルであるかのように GRANT または REVOKE を使用します。

外部データ ソースと外部テーブルを定義すると、外部テーブルに対して完全に T-SQL を使用できるようになります。

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

次のクエリでは、倉庫、注文、および注文明細行の間で 3 方向結合を実行し、いくつかの集計と選択的フィルターを使用します。 ここでは、(1) 行方向のパーティション分割 (シャーディング) のほか、(2) 倉庫、注文、および注文明細行が倉庫の ID 列でシャード化されること、エラスティック クエリがシャード上の結合を併置できること、クエリの負荷の高い部分をシャード上で並列に処理できることを想定しています。

    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_remote

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

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

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

例:

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

ツールの接続性

通常の SQL Server 接続文字列を使用して、アプリケーション、BI、およびデータ統合ツールを、外部テーブル定義を持つデータベースに接続できます。 使用しているツールのデータ ソースとして SQL Server がサポートされていることを確認してください。 次に、ツールに接続される他の SQL Server データベースと同様にエラスティック クエリ データベースを参照して、外部テーブルをローカル テーブルであるかのようにツールまたはアプリケーションから使用します。

ベスト プラクティス

  • エラスティック クエリ エンドポイント データベースに、SQL Database ファイアウォール経由でのシャード マップ データベースとすべてのシャードへのアクセスが確実に許可されているようにします。
  • 外部テーブルで定義されたデータ分散を検証または適用しません。 実際のデータ分布がテーブル定義で指定された分布と異なる場合、クエリによって予期しない結果が生じる可能性があります。
  • シャーディング キーによる述語で特定のシャードを処理から安全に除外できる場合、エラスティック クエリでは、現在のところ、シャードの除去を実行しません。
  • エラスティック クエリは、計算の大部分をシャード上で実行できるクエリに最適です。 通常、最適なクエリ パフォーマンスが得られるのは、シャード上で評価可能な選択的なフィルター述語を使用した場合、またはすべてのシャード上でパーティション分割方法により実行可能な、パーティション分割キーによる結合を使用した場合となります。 他のクエリ パターンでは、シャードからヘッド ノードに大量のデータを読み込む必要があり、パフォーマンスが低下する可能性があります。

関連コンテンツ