SE の雑記

SQL Server 2019 では、PolyBase が機能強化され、新機能のキーワードとして「データ仮想化」「データハブ」があります。

What is PolyBase? も随時更新されているようで、一度読んだ後も改めて確認するといろいろな更新がかかっているように見受けられます。
複数のデータソースに接続をしてみたので、軽くまとめて置こうかと。

新しくなった PolyBase で可能になった接続

今までの PolyBase では、HDFS と Azure BLOB Sotrage に接続が可能でした。
SQL Server 2019 では、上記の接続方法は、「HDFS データ ソースの Java コネクタ」として実装されており、オプション扱いとなりました。
標準の PolyBase の機能では、次のデータソースへの接続を行うことができるようになっています。

• Configure PolyBase to access external data in SQL Server
• Configure PolyBase to access external data in Oracle
• Configure PolyBase to access external data in Teradata
• Configure PolyBase to access external data in MongoDB

今後 ODBC による接続も使用できるようになるようではあるのですが、現状は、上記のデータソースに直接接続するためのライブラリが PolyBase の標準機能として導入が行われます。
以下は、Ignite 2018 のスライドからの引用となります。
新しくなった PolyBase では、Java を使用することなく、ネイティブに外部データソースに接続ができるようになっており、Big Data Cluster の場合は、SQL Server のストレージプールの HDFS にもアクセスができるようになっています。

CTP 2.0 リリース直後の時点では、PolyBase が使用できるのは、Windows のみとなっていますが、Big Data Cluster では、先行して Linux 版の PolyBase が実装されており、通常の SQL Server 2019 CTP 2.0 の on Linux の環境でも SFP (Single File Pack) は、PolyBase のものが含まれているようであり、今後 on Linux でも実装予定と Ignite 2018 のセッションでも公開されていましたので、この辺は OS の違いによる実装機能の差は出ないのではないでしょうか。
 

リンクサーバーとの違い

他のデータソースに SQL Server から接続する機能というと「リンクサーバー」が以前からありました。
リンクサーバーと今回の PolyBase との違いについても、Ignite 2018 ないで解説が行われており、次のスライドがその引用となります。

リンクサーバーについては、master に含まれている情報となり、AlwaysOn 可用性グループでは同期をとることができない設定のため、新しく外部データソースを追加した際には、可用性グループ内のノードにも別途必要となっていました。
PolyBase での接続は、データベーススコープのオブジェクトである「資格情報」「外部データソース」「外部テーブル」で構成が行われているため、全ての情報がデータベース内に完結するような構成となっています。
そのため、AlwaysOn 可用性グループのような、データベース単位で同期をとるような設定を使用すると、その同期の中で外部データソースとの連携のための情報がすべて含まれることになります。
他にもリンクサーバーは OLEDB でしたが、PolyBase は ODBC が使用されているというのも違いとしてあるようですね。
PolyBase はコンピュートノードをスケールアウトすることができますので、性能についても差は出るのかと。
 

使用されているディレクトリで軽く見ておいた方がよいもの

PolyBase を使用するに際して、以下のディレクトリは覚えておいた方がよいかと。

• C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\Polybase
• C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\Polybase\ODBC Drivers

最初のディレクトリは、PolyBase のログのディレクトリです。
エラーが出た場合、SQL の実行結果としてエラーが返ってきますが、詳細な内容についてはこのディレクトリ内のファイルに出力がされますので、うまく動作しないようなことがあった場合は、このディレクトリ内のファイルを見ておくとよいかと。
2 番目のディレクトリは、PolyBase の ODBC ドライバーとして使われているものになります。
具体的には次のようなファイル群が格納されています。

現状、任意の ODBC ドライバーを使う方法が公開されていないのですが、ドキュメントが提供されているものについては、このディレクトリ内に ODBC ドライバーのファイルが格納されています。
各接続でどのようなドライバーが使用されているか気になった場合は、この辺のファイルを眺めてみると良いのではないでしょうか。
 

データ仮想化を試してみる

基本は、冒頭で紹介した次のドキュメントを見るとよいかと。

• Configure PolyBase to access external data in SQL Server
• Configure PolyBase to access external data in Oracle
• Configure PolyBase to access external data in Teradata
• Configure PolyBase to access external data in MongoDB

Teradata は確認できていないのですが、ほかの 3 種類については接続を確認することができました。
SQL Server の接続
SQL Server の接続については、ドキュメントに書いている内容を確認すれば問題ないかと。
次のような形式のクエリを実行することでアクセスすることができます。
今回は SQL Server ではなく、SQL Database にアクセスをしてみたのですが、TCP 1433 でアクセスできれば、問題なく設定ができるかと。

CREATE DATABASE SCOPED CREDENTIAL SqlServerCredentials
WITH IDENTITY = '<sql Server ログイン>', Secret = '<sql Server ログイン パスワード>'
GO
CREATE EXTERNAL DATA SOURCE SqlServerInstance
WITH (
LOCATION = 'sqlserver://<sql Server サーバー名 or IP アドレス>',
CREDENTIAL = SqlServerCredentials -- 作成した資格情報名
)
GO
CREATE EXTERNAL TABLE AzureSQLDB_NATION
(
	[N_NATIONKEY] [int] NOT NULL,
	[N_NAME] [char](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[N_REGIONKEY] [int] NOT NULL,
	[N_COMMENT] [varchar](152) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
WITH
(
    LOCATION = 'tpch.dbo.NATION', 　-- <db 名>.<スキーマ名>.<テーブル名>
    DATA_SOURCE = SqlServerInstance -- 作成した外部データソース名
)

 
Oracle への接続
Oracle はドキュメントだけだと接続させるのが難しいかもしれません…。

Oracle に接続したい場合は、SQL Server big data clusters のサンプルを確認した方がよいかと思います。
LOCATION の形式が「<サービス名>.<スキーマ名>.<テーブル名>」となっているのですが、このフォーマットが記載されているのが GitHub のサンプルしかなかったのですよね。

CREATE DATABASE SCOPED CREDENTIAL OracleCredentials
WITH IDENTITY = '<oracle ログイン>', Secret = '<oracler ログインのパスワード>';
GO
CREATE EXTERNAL DATA SOURCE OracleInstance
WITH (
LOCATION = 'oracle://<サーバー名 or IP アドレス>',
CREDENTIAL = OracleCredentials -- 作成した資格情報名
)
GO
CREATE EXTERNAL TABLE Oracle_LINEITEM(
 [L_SHIPDATE] DATE,
 [L_ORDERKEY] FLOAT(53) NOT NULL,
 [L_DISCOUNT] FLOAT(53) NOT NULL,
 [L_EXTENDEDPRICE] FLOAT(53) NOT NULL,
 [L_SUPPKEY] FLOAT(53) NOT NULL,
 [L_QUANTITY] FLOAT(53) NOT NULL,
 [L_RETURNFLAG] CHAR(1) COLLATE Japanese_CI_AS,
 [L_PARTKEY] FLOAT(53) NOT NULL,
 [L_LINESTATUS] CHAR(1) COLLATE Japanese_CI_AS,
 [L_TAX] FLOAT(53) NOT NULL,
 [L_COMMITDATE] DATE,
 [L_RECEIPTDATE] DATE,
 [L_SHIPMODE] CHAR(10) COLLATE Japanese_CI_AS,
 [L_LINENUMBER] FLOAT(53) NOT NULL,
 [L_SHIPINSTRUCT] CHAR(25) COLLATE Japanese_CI_AS,
 [L_COMMENT] VARCHAR(44) COLLATE Japanese_CI_AS
)
WITH (
LOCATION='orcl.TPCH.LINEITEM',　-- <サービス名>.<スキーマ名>.<テーブル名>
DATA_SOURCE=OracleInstance -- 作成した外部データソース名
)
GO

接続先の Oracle のバージョンに寄るかと思いますが、Oracle 12c に接続する場合、Oracle サーバー側の「sqlnet.ora」に「SQLNET.ALLOWED_LOGON_VERSION_SERVER=11」の設定を追加しないと「ORA-28040: 一致する認証プロトコルがありません」のエラーが発生するかもしれません。
このエラーが発生した場合は接続を許可するバージョンの設定を見直してみるとよいかと。
MongoDB
Mongo の接続もドキュメントの内容で問題ないかと。

今回はネイティブな Mongo ではなく、CosmosDB を Mongo API を使用するもので試してみたのですが、Cosmos の Mongo API に対しても接続することができました。

CREATE DATABASE SCOPED CREDENTIAL MongoDBCredentials
WITH IDENTITY = '<ログイン名>', Secret = '<パスワード>
GO
CREATE EXTERNAL DATA SOURCE MongoDBInstance
WITH (
LOCATION = 'mongodb://<mongo DB サーバー名>',
CREDENTIAL = MongoDBCredentials -- 作成した資格情報
)
GO
CREATE EXTERNAL TABLE MongoDB_REGION
(
	[_id] NVARCHAR(24) COLLATE Japanese_CI_AS NOT NULL,
	[R_REGIONKEY] INT,
	[R_NAME] NVARCHAR(4000) COLLATE Japanese_CI_AS,
	[R_COMMENT] NVARCHAR(4000) COLLATE Japanese_CI_AS,
	[id] NVARCHAR(4000) COLLATE Japanese_CI_AS
)
WITH
(
    LOCATION = 'tpch.REGION',  -- <db 名>.<コレクション名>
    DATA_SOURCE = MongoDBInstance
)
GO

 
外部テーブルは検索のみが可能であり、データの追加 / 変更 / 削除はできないのですが、単一の接続により、様々なデータソースを T-SQL をインタフェースとして検索することが可能となります。

(INSERT INTO 句等のデータソースとして使用することは可能ですので、外部テーブルの内容を元にローカルテーブルとしてデータをキャッシュさせ、そのテーブルを変更するということは可能です)
次の区営rは、Mongo と Azure SQL Database のテーブルを JOIN して実行したものになるのですが、PolyBase を使用することで、SQL Server をデータハブとして使用しデータの仮想化を行い、データが格納されている場所を意識することなく、単一の接続で検索が実行できています。

USE [PolyBase]
GO
SELECT TOP 10
	M.R_REGIONKEY,
	M.R_NAME,
	M.R_COMMENT,
	A.N_NATIONKEY,
	A.N_NAME,
	A.N_COMMENT
FROM
	MongoDB_REGION AS M
	LEFT JOIN
	AzureSQLDB_NATION AS A
	ON
	M.R_REGIONKEY = N_REGIONKEY
ORDER BY
	M.R_REGIONKEY ASC

 

データ仮想化により、他のデータソースに格納されているデータを ETL により移動させてくることなく、利用することができるようになりますので、データのリアルタイム性や余計なストレージの消費をなくして、データを効率よく利用することが可能となります。
BI 製品で、データを内部的に統合して、複数のデータソースのデータに関連性を持たせて利用することもできますが、単一の SQL Server の接続で、T-SQL をインタフェースとして、複数のデータソースにアクセスすることができることのメリットも色々と考えられるのではないでしょうか。
SQL Server のデータ仮想化は「近代的なデータの取り扱い方法では、データに適した格納方法を利用し、データを検索する際には、PolyBase を使用して単一の接続 / インタフェースによる横断的な検索を行う」ためのデータハブとしての役目もあります。
この機能によりデータの取り扱い方に柔軟性が増し、価値のあるデータの活用が加速できると面白そうですね。