SQL Server 2019 の新しくなった PolyBase では、次のデータソースにネイティブに接続ができるようになっています。
これ以外に汎用的に ODBC を使用して外部データソースに接続し、データ仮想化を実施することができるようになっています。
リリース直後のドキュメントでは、ODBC 経由での接続方法の情報が公開されていなかったのですが、先日、ドキュメントの公開が行われました。
Configure PolyBase to access external data in SQL Server
これにより、上記に記載したデータソース以外についても ODBC ドライバーを使用することで、接続が可能となりました。
(PolyBase は、64 bit のプロセスなので、64 bit の ODBC ドライバーが必要になるかと)
基本的な構文としては、次のような 3 ステップとなります。
1. 資格情報の作成
CREATE DATABASE SCOPED CREDENTIAL <資格情報名> WITH IDENTITY = '<ユーザー名>', Secret = '<パスワード>'; GO
接続先によっては、資格情報が不要なこと (ファイル等を ODBC で操作する場合) もありますが、その場合でも資格情報の設定は必要なようです。
そういう場合には、ユーザー名に DUMMY や Annoymous 等の適当な名称を設定し、パスワードはブランクにしておけば問題ないかと。
2. 外部データソースの作成
CREATE EXTERNAL DATA SOURCE <データソース> WITH ( LOCATION = 'odbc://<接続先ホスト名 / IP アドレス>', CONNECTION_OPTIONS ='DRIVER={<odbc ドライバー名>};<接続文字列のオプション>, CREDENTIAL = <資格情報> ); GO
LOCATION については、ローカルのファイルや何かしらのサービスを使用する場合、接続文字列のオプション側で設定を行い、LOCATION の接続先には設定できないようなケースがあるかと思います。
そのような場合は、「127.0.0.1」を設定し、接続文字列のオプション側で細かな設定を実施する形でよいかと。
ODBC のドライバー名は、PolyBase をインストールしている環境の ODBC ドライバー名を指定することになります。
3.外部テーブル
CREATE EXTERNAL TABLE <外部テーブル名> ( <スキーマ定義> ) WITH ( DATA_SOURCE = <外部データソース名>, LOCATION = '<外部データソースでアクセスするテーブル名>' ); GO
最後に、ここまででで作成したオブジェクトを使用してテーブルを作成します。
テーブル名については使用するデータソースによっては「データベース名.テーブル名」もあれば「テーブル名」だけの場合もあるかと思いますので、この辺は使用する ODBC ドライバーによって指定方法を調整することになります。
この 3 ステップにより、ODBC ドライバーを使用した PolyBase の外部データソースの利用が可能です。
(冒頭で書いた新しく追加された、ネイティブに接続できるデータソースも実際には ODBC 経由で接続を行っているものになりますので、どの接続方法も結局は ODBC を使っているのですけどね)
SQL Server 2017 までの PolyBase は、Java を使用した HDFS / Azure BLOB ストレージへの接続に使用するものとなっていましたが、SQL Server 2019 では、ODBC に対応したことにより様々なデータソースに接続が可能となっています。
試しに Auzre Database for MySQL に Download Connector/ODBC のドライバーで接続してみたのですが、さくっとつながりました。
せっかく ODBC で接続するので、DB 以外との接続で試したいといった場合には、CData Software さんの ODBC ドライバー を使用するといろいろなデータ連携が試せるかと。
評価版が提供されていますので、PolyBase からいろいろなデータ連携が試せるかと。
Excel と接続したいのであれば、Excel ODBC Driver をダウンロード / インストールして、次のようなクエリで Excel の検索を PolyBase を使用して実施できます。
(ODBC の接続文字列は、「CONNECTION_OPTIONS」で指定するということが把握できていれば設定は問題ないかと。)
CREATE DATABASE SCOPED CREDENTIAL ODBC_Excel_Credentials WITH IDENTITY = 'Dummy', Secret = ''; GO CREATE EXTERNAL DATA SOURCE ODBC_Excel_Instance WITH ( LOCATION = 'odbc://127.0.0.1', CONNECTION_OPTIONS ='DRIVER={CData ODBC Driver for Excel};Excel File=C:\temp\ExcelBook.xlsx', CREDENTIAL = ODBC_Excel_Credentials ); GO CREATE EXTERNAL TABLE ODBC_Excel_ExcelTBL ( RowID int, C1 varchar(100), C2 varchar(100), C3 varchar(100) ) WITH ( LOCATION = 'Sheet1', DATA_SOURCE = ODBC_Excel_Instance ); GO
Excel の内容が PolyBase を使用して取得できていますね。
Excel の接続であれば Microsoft Access データベース エンジン 2010 再頒布可能コンポーネント という、Microsoft が標準で提供しているものもあるかと思いますが、軽く検証した限りでは、「Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)」では、外部テーブルを作成する際に「105082; 汎用 ODBC エラー: [Microsoft][ODBC Excel Driver]オプションの機能は実装されていません。 。」のエラーとなり、接続することはできませんでした。
外部データソースを作成する際ですが、「どのようなスキーマ構成になっているか」を確認し、そのスキーマに対応している外部テーブルのスキーマ定義となっているかを確認していたりするのですが、この辺の機能がうまく動いていないのかもしれないですね。
現時点では CData さんの ODBC ドライバーでしたら Excel の検索の検証は簡単にできましたので、Excel 検索をしたい場合は、まずはこちらのドライバーを使用してみるとよいかと。
ほかにも Twitter ODBC Driver も試してみたのですが、PolyBase 経由で情報を取得することが可能でした。
CData さんの場合、http://cdn.cdata.com/help/GTD/odbc/pg_alltables.htm のような形で、ODBC 経由でアクセス可能なテーブルのレイアウトが公開されているので、スキーマの定義はやりやすいかと。
(CREATE EXTERNAL TABLE で不適切なスキーマだと情報が帰ってくるので、そこから適切なスキーマを推定することもできますが)
PolyBase の外部テーブルについては、SELECT のみが実行可能であり、データの変更はできないはずですが、複数の PolyBase ノードを構築して、スケールアウトして検索を行わせることができるようになっています。
データ仮想化により SQL Server をデータハブとして使用することで、SQL Server に対して T-SQL を実行できれば様々なデータを検索することが可能となります。
これにより今までできなかったデータの取り扱いにより、新たな洞察が得られるシーンが増えると嬉しいですね。