MongoDB 上の外部データにアクセスするための PolyBase の構成
適用対象:
SQL Server
この記事では、SQL Server インスタンス上で PolyBase を使用して、MongoDB 上の外部データに対してクエリを実行する方法について説明します。
前提条件
PolyBase をインストールしていない場合は、「PolyBase のインストール」をご覧ください。
データベース スコープ資格情報を作成する前に、データベースに資格情報を保護するためのマスター キーが必要です。 詳細については、「CREATE MASTER KEY」を参照してください。
MongoDB の外部データ ソースを構成する
MongoDB データ ソースのデータに対してクエリを実行するには、外部テーブルを作成して外部データを参照する必要があります。 このセクションでは、これらの外部テーブルを作成するサンプル コードを示します。
このセクションでは以下の Transact-SQL コマンドが使用されます。
- CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)
- CREATE EXTERNAL DATA SOURCE (Transact-SQL)
- CREATE EXTERNAL TABLE (Transact-SQL)
- CREATE STATISTICS (Transact-SQL)
MongoDB ソースにアクセスするために、データベース スコープ資格情報を作成します。
次のスクリプトは、データベース スコープ資格情報を作成します。 スクリプトを実行する前に、お使いの環境に合わせて更新します。
<credential_name>を資格情報の名前に置き換えます。<username>を外部ソースのユーザー名に置き換えます。<password>を適切なパスワードに置き換えます。
CREATE DATABASE SCOPED CREDENTIAL [<credential_name>] WITH IDENTITY = '<username>', Secret = '<password>';重要
PolyBase 用の MongoDB ODBC コネクタでサポートされるのは、Kerberos 認証ではなく、基本認証のみです。
外部データ ソースを作成します。
次のスクリプトは、外部データ ソースを作成します。 参考情報については、「CREATE EXTERNAL DATA SOURCE」を参照してください。 スクリプトを実行する前に、お使いの環境に合わせて更新します。
- 場所を更新します。 お使いの環境に合わせて
<server>と<port>を設定します。 <credential_name>を前のステップで作成した資格情報の名前に置き換えます。- 外部ソースに対してプッシュダウン計算を指定する場合は、必要に応じて
PUSHDOWN = ONまたはPUSHDOWN = OFFを指定できます。
CREATE EXTERNAL DATA SOURCE external_data_source_name WITH (LOCATION = '<mongodb://<server>[:<port>]>' [ [ , ] CREDENTIAL = <credential_name> ] [ [ , ] CONNECTION_OPTIONS = '<key_value_pairs>'[,...]] [ [ , ] PUSHDOWN = { ON | OFF } ]) [ ; ]- 場所を更新します。 お使いの環境に合わせて
MongoDB の外部スキーマに対してクエリを実行します。
Azure Data Studio 用のデータ仮想化の拡張機能を使用して、MongoDB ドライバー用 PolyBase ODBC ドライバーによって検出されたスキーマに基づいて CREATE EXTERNAL TABLE ステートメントに接続し生成できます。 システム ストアド プロシージャ sp_data_source_objects (Transact-SQL) の出力に基づいてスクリプトを手動でカスタマイズすることもできます。 Azure Data Studio 用のデータ仮想化の拡張機能と
sp_data_source_table_columnsは同じ内部ストアド プロシージャを使用して、外部スキーマのクエリを実行します。配列を含む MongoDB コレクションに外部テーブルを作成するには、Azure Data Studio 用のデータ仮想化の拡張機能を使用することをお勧めします。 フラット化アクションは、ドライバーによって自動的に実行されます。
sp_data_source_table_columnsストアド プロシージャでは、MongoDB 用 PolyBase ODBC ドライバーによってフラット化も自動的に実行されます。外部テーブルを作成します。
Azure Data Studio 用のデータ仮想化の拡張機能を使用する場合は、CREATE EXTERNAL TABLE ステートメントが自動的に生成されるため、このステップをスキップできます。 スキーマを手動で指定するには、次のサンプル スクリプトを検討して外部テーブルを作成します。 参照情報については、「CREATE EXTERNAL TABLE」を確認してください。
スクリプトを実行する前に、お使いの環境に合わせて更新します。
- フィールドの名前と照合順序を更新し、コレクションの場合は、コレクション名とフィールド名を指定します。 この例では、
friendsはカスタム データ型です。 - 場所を更新します。 データベース名とテーブル名を設定します。 3 部構成の名前は使用できないため、
system.profileテーブル用には作成できません。 また、メタデータの取得元ではないという理由で、ビューを指定することもできません。 - 前の手順で作成した名前で、データ ソースを更新します。
CREATE EXTERNAL TABLE [MongoDbRandomData]( [_id] NVARCHAR(24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [RandomData_friends_id] INT, [RandomData_tags] NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS) WITH ( LOCATION='MyDb.RandomData', DATA_SOURCE=[MongoDb])- フィールドの名前と照合順序を更新し、コレクションの場合は、コレクション名とフィールド名を指定します。 この例では、
省略可能: 外部テーブルの統計を作成します。
最適なクエリのパフォーマンスを得るために、外部テーブルの列、特に結合、フィルター、集計に使用される列に対して統計を作成することをお勧めします。
CREATE STATISTICS statistics_name ON customer (C_CUSTKEY) WITH FULLSCAN;
重要
外部データ ソースを作成すると、CREATE EXTERNAL TABLE コマンドを使用して、そのソース上でクエリ可能なテーブルを作成することができます。
例については、MongoDB の外部テーブルの作成に関する記述を参照してください。
MongoDB の接続オプション
MongoDB の接続オプションの詳細については、接続文字列の URI 形式に関する MongoDB のドキュメントを照してください。
フラット化
フラット化は、MongoDB ドキュメント コレクションの入れ子になったデータと繰り返しデータに対して有効になります。 ユーザーは、入れ子になったデータや繰り返しデータを含む可能性のある MongoDB ドキュメント コレクションに対して create an external table を有効にし、リレーショナル スキーマを明示的に指定する必要があります。
JSON の入れ子になったデータ型/繰り返しデータ型は次のようにフラット化されます
オブジェクト: 中かっこで囲まれている順序付けられていないキー/値のコレクション (入れ子)
SQL Server では、各オブジェクト キーのテーブル列が作成されます
- 列名: objectname_keyname
配列: コンマで区切られ、角かっこで囲まれた、順序付けられた値 (繰り返し)
SQL Server では、配列項目ごとに新しいテーブル行が追加されます
SQL Server では、配列ごとの列を作成して配列項目のインデックスが格納されます
列名: arrayname_index
データ型: bigint
この手法にはいくつかの潜在的な問題があります。そのうちの 2 つを次に示します。
空の繰り返しフィールドは、同じレコードのフラット フィールドに含まれるデータを事実上マスクする
複数の繰り返しフィールドが存在すると、生成される行数が急増することがある
たとえば、SQL Server では、非リレーショナルの JSON 形式で格納されている MongoDB のサンプル データセット レストラン コレクションが評価されます。 各レストランには、入れ子になった住所フィールドと、異なる日に割り当てられた採点の配列があります。 次の図は、入れ子になった住所と入れ子になった繰り返しの採点がある一般的なレストランを示しています。
住所オブジェクトは次のようにフラット化されます。
- 入れ子になったフィールド
restaurant.address.buildingはrestaurant.address_buildingになります - 入れ子になったフィールド
restaurant.address.coordはrestaurant.address_coordになります - 入れ子になったフィールド
restaurant.address.streetはrestaurant.address_streetになります - 入れ子になったフィールド
restaurant.address.zipcodeはrestaurant.address_zipcodeになります
採点配列は次のようにフラット化されます。
| grades_date | grades_grade | games_score |
|---|---|---|
| 1393804800000 | A | 2 |
| 1378857600000 | A | 6 |
| 135898560000 | A | 10 |
| 1322006400000 | A | 9 |
| 1299715200000 | B | 14 |
Cosmos DB 接続
Cosmos DB の Mongo API および Mongo DB PolyBase コネクタを使用すると、Cosmos DB インスタンスの外部テーブルを作成することができます。 これは、上記と同じ手順に従って行います。 データベースのスコープ資格情報、サーバーのアドレス、ポート、場所の文字列が Cosmos DB サーバーのものを反映していることを確認してください。
例
次の例では、次のパラメーターを使用して外部データ ソースを作成します。
| パラメーター | 値 |
|---|---|
| 名前 | external_data_source_name |
| サービス | mongodb0.example.com |
| インスタンス | 27017 |
| レプリカ セット | myRepl |
| TLS | true |
| プッシュダウン計算 | On |
CREATE EXTERNAL DATA SOURCE external_data_source_name
WITH (LOCATION = 'mongodb://mongodb0.example.com:27017',
CONNECTION_OPTIONS = 'replicaSet=myRepl; tls=true',
PUSHDOWN = ON ,
CREDENTIAL = credential_name);
次のステップ
さまざまなデータ ソースへの外部データ ソースと外部テーブルの作成に関するその他のチュートリアルについては、「PolyBase Transact-SQL リファレンス」を参照してください。
PolyBase の詳細については、SQL Server PolyBase の概要に関する記事をご覧ください。
フィードバック
近日公開予定: 2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub イシューを段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、以下を参照してください: https://aka.ms/ContentUserFeedback。
フィードバックの送信と表示