SQL Server の T-SQL 経由で、MongoDB のデータを参照するための方法はいくつかあると思いますが、MongoDB 社が提供している MongoDB BI Connector 経由でアクセスする方法をまとめてみたいと思います。
MongoDB BI Connector がサポートしている MongoDB のバージョンが 3.2 以降となっていますので、対応バージョンは、このバージョン以降となります。
Note
The MongoDB Connector for BI and associated utilities are compatible only with MongoDB server version 3.2 or greater.
今回の MongoDB BI Connector では「BI ツールに MongoDB のデータを読み取りアクセスさせるために利用」することとなり、変更系のアクセスはできません
MongoDB BI Connector ですが、MongoDB のデータベースを外部から参照させる際のブリッジとして使用することができるものです。
モジュールは こちら から入手することができ、インストーラーに従ってインストールを行うことでツールが「C:\Program Files\MongoDB\Connector for BI\2.1\bin」に導入されます。
MongoDB BI Connector は 2 種類のツール (mongodrdl.exe / mongosqld.exe) で構成されており、外部から MongoDB にアクセスを可能とさせるためにこれらのツールを使用します。
(以降の手順は「C:\Program Files\MongoDB\Connector for BI\2.1\bin」で実行を想定しています)
Contents
1. mongodrdl.exe による DRDL ファイルの作成
MongoDB のデータを参照させるために、最初に DRDL ファイルを作成し、MongoDB のドキュメントをリレーショナル DB のスキーマとして定義 を行います。
この時に使用するツールが「mongodrdl.exe」です。
このあたりの動作は、SQL Server 2019 の PolyBase で MongoDB のデータを参照させる際に、外部テーブルとして定義 するのと同じ考え方ですね。
mongodrdl.exe の実行のサンプルは次のような形式となります。
(コマンドラインオプションの詳細については、mongodrdl を参照してください)
mongodrdl.exe --host localhost --port 27017 --db TestDB -o c:\temp\TestDB.drdl -s 5000
これにより、指定した MongoDB のデータベースのコレクションのスキーマ定義を DRDL ファイルとして出力を行うことができます。
DRDL ファイルは次の画像のようなテキスト形式のファイルとなっており、これにより、どのようなスキーマとして参照させるかが制御されます。
「-s」(–sampleSize) オプションが、スキーマ定義を作成する際に、MongoDB 上のデータを何件サンプリングして作成するかを表すものとなります。
ドキュメントによって、構造が大きく異なる場合などは、サンプリングサイズを大きくすることで、作成される DRDL ファイルの正確性が向上することになるかと。
DRDL ファイルの作成が完了したら、MongoDB のデータを公開するために、「mongosqld.exe」の実行を行います。
2. mongosqld.exe による SQL で MongoDB にアクセスを行うためのインタフェースの起動
DRDL ファイルの作成が完了したら、作成した DRDL ファイルのスキーマ定義で MongoDB のコレクションを参照できるように します。
この時に使用するツールが「mongosqld.exe」です。
mongosqld.exe の実行のサンプルは次のような形式となります。
(コマンドラインオプションの詳細については、mongosqld を参照して下さい)
mongosqld --schema "C:\Temp\TestDB.drdl" --mongo-uri "mongodb://localhost:27017" --maxVarcharLength 8000 --addr 10.200.0.1
今回、最終的には、SQL Server 経由で MongoDB のデータにアクセスをさせるのですが、その時、文字列データの参照が「–maxVercharLength」を指定していないとうまく取得できなかったため、今回はこのオプションを指定しています。
また、「–addr」を指定しない場合、「127.0.0.1」で、外部インタフェースとしてのポートがリスニングされるため今回は明示的にリスニングさせる IP アドレスを指定しています。
正常に起動が完了すると、次の画像のように TCP 3307 として、MongoDB へのアクセスが可能となります。
「mongosqld.exe」がダウンしてしまいますと、MongoDB へのアクセス経路もなくなりますので、データの参照を行う際には、このプログラムは常時起動させておく必要があります。
3.mongosqld.exe により起動されたポート経由で MongoDB にアクセス
これでアクセスするための下準備が完了しました。
- mongodrdl.exe で DRDL ファイルを作成し、スキーマ定義を設定
- mongosqld.exe で DRDL ファイルのスキーマ定義でアクセスさせるためのインタフェースを起動
mongosqld.exe で起動したインタフェースですが、これは、MySQL のように振る舞いを行うことになります。
MySQL Workbench を使用して、mongosqld.exe で起動したポートに対して接続を行ってみます。
そうすると、次のように、MySQL に対してデータを検索するような形で MongoDB のデータを取り扱うことができます。
MongoDB の「Array」については、独立したテーブルとして認識されるようで、「Collection01_C6」というテーブルについては、Collection01 というコレクションの Array を使用した C6 の列が独立して認識したものとなります。
ここまでの動作ができていれば、SQL Server 経由でもアクセスを行うことができるのですが、SQL Server 経由でアクセスをさせるためには、MySQL の ODBC ドライバーが必要となります。
4. MySQL ODBC ドライバーのインストール
mongosqld.exe を使用することで、MySQL のインタフェースで、データを参照させることができます。
これを SQL Server から利用させる場合は、OLEDB Provider for ODBC Drivers 経由でのアクセスとなるため、MySQL の ODBC ドライバーをインストールしておきます。
ODBC ドライバーですが、次のどちらかを利用すれば問題ないかと思います。
MongoDB ODBC ドライバーは、MySQL ODBC ドライバーと類似のもののように見え、どちらを使っても接続はできるかと。
ODBC データソースを作成して接続させてもいいのですが、今回はリンクサーバーに、接続文字列を設定して接続を行ってみたいと思います。
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'MONGODB',
@srvproduct=N'',
@provider=N'MSDASQL',
@provstr=N'Driver={MongoDB ODBC 1.2.0 Unicode Driver};Server=10.200.0.1;Port=3307;Database=TestDB'
今回は、MongoDB ODBC ドライバーを使用しているため、「Driver={MongoDB ODBC 1.2.0 Unicode Driver}」と指定していますが、MySQL ODBC ドライバーを使用する場合は次のような設定になるかと。
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'MySQL',
@srvproduct=N'',
@provider=N'MSDASQL',
@provstr=N'Driver={MySQL ODBC 8.0 Unicode Driver};Server=10.200.0.1;Port=3307;Database=TestDB'
これで、SQL Server のリンクサーバーとして、MongoDB が接続できている状態となります。
(実際には、MySQL として認識させた MongoDB に接続している形になりますが)
現状、リンクサーバーとして認識ができていても、リンクサーバー経由でアクセスはできないかと思います。
[リンクサーバー名].[データベース名].[スキーマ名].[テーブル名] の 4 パート名で認識でいていればよいのですが、スキーマの認識がされていない状態となっているため、通常の SQL ではデータを参照させることができません。
そのため、アクセスをさせる場合には、OPENROWSET を用いることになるかと。
OPENROWSET 内に、MySQL のクエリを記述してデータにアクセスを行います。
SELECT
*
FROM
OPENROWSET('MSDASQL',
'Driver={MongoDB ODBC 1.2.0 Unicode Driver};Server=10.200.0.1;Port=3307;Database=TestDB',
'select * from Collection01 WHERE C2 IS NOT NULL LIMIT 100')
AS Mongon
サロゲートペアの文字列の認識ができていないのですが、そうでない Unicode の文字列 (今回だと?外の部分) は正常に認識できています。
厳密なデータを確認する場合には利用は難しいですが、厳密性を求めないデータ分析であれば、利用できるのではないでしょうか。
SQL Server 2019 であれば、PolyBase に MongoDB の ODBC ドライバーが含まれていますので、PolyBase 経由でアクセスを行うことができますが、それ以外のバージョンの SQL Server では、このような方法を使用した MongoDB のデータ参照を覚えておいてもよいかなと。
ちなみに、最初は、CosmosDB の MongoDB API で検証をしていたのですが、DRDL ファイルの作成はできたのですが、mongosqld.exe による接続がうまく行えず、CosmosDB には同様の方法での接続を実現することができませんでした。
CosmosDB 側でプレビュー機能の「MongoDB 3.4 ワイヤプロトコル」を有効にして、
mongodrdl.exe については「–authenticationMechanism MONGODB-CR」を指定することでスキーマ定義を作成することができたのですが、mongosqld.exe については、MONGODB-CR をサポートしていないようで、接続させることができませんでした。