MongoDB 上の外部データにアクセスするための PolyBase の構成

[アーティクル]
05/30/2023

適用対象: SQL Server

この記事では、SQL Server インスタンス上で PolyBase を使用して、MongoDB 上の外部データに対してクエリを実行する方法について説明します。

前提条件

PolyBase をインストールしていない場合は、「PolyBase のインストール」をご覧ください。

データベーススコープ資格情報を作成する前に、データベースに資格情報を保護するためのマスターキーが必要です。詳細については、「CREATE MASTER KEY」を参照してください。

MongoDB の外部データソースを構成する

MongoDB データソースのデータに対してクエリを実行するには、外部テーブルを作成して外部データを参照する必要があります。このセクションでは、これらの外部テーブルを作成するサンプルコードを示します。

このセクションでは以下の 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 のサンプルデータセットレストランコレクションが評価されます。各レストランには、入れ子になった住所フィールドと、異なる日に割り当てられた採点の配列があります。次の図は、入れ子になった住所と入れ子になった繰り返しの採点がある一般的なレストランを示しています。

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 の概要に関する記事をご覧ください。

次の方法で共有

MongoDB 上の外部データにアクセスするための PolyBase の構成

前提条件

MongoDB の外部データソースを構成する

MongoDB の接続オプション

フラット化

Cosmos DB 接続

例

次のステップ

フィードバック

その他のリソース

次の方法で共有

MongoDB 上の外部データにアクセスするための PolyBase の構成

前提条件

MongoDB の外部データ ソースを構成する

MongoDB の接続オプション

フラット化

Cosmos DB 接続

例

次のステップ

フィードバック

その他のリソース

MongoDB の外部データソースを構成する