SE の雑記

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

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

leave a comment

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

*