SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server 2019 の PolyBase の ODBC を使用した接続のドキュメントが公開されました

without comments

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 ドライバー名を指定することになります。

image
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 を使用して取得できていますね。
imageimage
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 経由で情報を取得することが可能でした。
image
CData さんの場合、http://cdn.cdata.com/help/GTD/odbc/pg_alltables.htm のような形で、ODBC 経由でアクセス可能なテーブルのレイアウトが公開されているので、スキーマの定義はやりやすいかと。

(CREATE EXTERNAL TABLE で不適切なスキーマだと情報が帰ってくるので、そこから適切なスキーマを推定することもできますが)
PolyBase の外部テーブルについては、SELECT のみが実行可能であり、データの変更はできないはずですが、複数の PolyBase ノードを構築して、スケールアウトして検索を行わせることができるようになっています。
データ仮想化により SQL Server をデータハブとして使用することで、SQL Server に対して T-SQL を実行できれば様々なデータを検索することが可能となります。
これにより今までできなかったデータの取り扱いにより、新たな洞察が得られるシーンが増えると嬉しいですね。

Written by Masayuki.Ozawa

10月 27th, 2018 at 5:36 pm

Posted in SQL Server

Tagged with ,

Leave a Reply