SE の雑記

以前、Babelfish for Amazon Aurora PostgreSQL が面白そうなので調べてみる で書いた、Aurora PostgreSQL で SQL Server の TDS を処理することができる、Babelfish の一般提供が開始されました。

Babelfish を使用すると、Aurora PostgreSQL に、SSMS のクエリエディターで接続して @@version を実行したりと、SQL Server 向けのワークロードを PostgreSQL 上で実現することができるようになります。

次の記事で、なかなかにインパクトのあるタイトルでアナウンスが行われています。

• Goodbye Microsoft SQL Server, Hello Babelfish
• さようなら Microsoft SQL Server、こんにちは Babelfish

今では Private Preview でしか情報の公開が行われていませんでしたが、一般提供が開始されたことで、Babelfish for Aurora PostgreSQL の実際の利用 / 様々なドキュメント / Babelfish のソースコードの提供が開始されています。

ドキュメントについては、

• Babelfish for Aurora PostgreSQL
• Working with Babelfish for Aurora PostgreSQL

で公開されており、ソースコードや、ソースコードに関連するサイトについてはこちらになるかと。

• Babelfish for PostgreSQL accelerates your journey to migrate SQL Server applications to PostgreSQL
• babelfish-for-postgresql

Babelfish は Aurora PostgreSQL だけでなく、通常の PostgreSQL でも導入することができますので、今後どのように広がっていくのかが楽しみですね。

軽く触ってみながら、いくつか情報をまとめておきたいと思います。

Babelfish for Aurora PostgreSQL の利用を使ってみる

Aurora PostgreSQL を作成するときに、13.4 互換の環境を展開する際に、次のような設定を有効化することで、Babelfish が使用できるようになります。

「データベース移行モード」については、Working with Babelfish for Aurora PostgreSQL で記載されています。

Aurora 上で展開された Babelfish は、「bafelfish_db」というデータベース内に、SQL Server 互換の機能を利用できるようにして、SQL Server との互換性を提供することになります。

この時に、SQL Server 互換のデータベースをどのように提供するかをデータベース移行モードで設定することになります。

「Single Database Mode」であれば、SQL Server 互換のユーザーデータベースとして、CREATE DATABASE できるデータベースが一つのみとなり、「Multiple Database Mode」であれば、複数のデータベースを CREATE DATABASE することができます。

どちらのモードで作成を行ったとしても PostgreSQL 上は「babelfish_db」に対して操作が行われ、そのデータベース内の情報を SQL Server の TDS で接続が行われてきた場合は、SQL Server のデータベースのように見せて取り扱うことができます。

実際に、Single Database Mode のBabelfish を使用して、SQL Server で接続をして、sqldb01 という DB を作成した後に、その DB に対して、CREATE TABLE をしたものが下の画面になります。

PostgreSQL としては、babelfish_db の dbo スキーマの下に t1 / t2 というテーブルが作成されていますが、SQL Server で情報を見たときは、sqldb01 というデータベースの中に、t1 / t2 というテーブルが作成されていることが確認できます。

SSMS のクエリエディタで、Babelfish で接続している状態で、pg_stat_activity の情報を取得したものが以下の画像となります。
PostgreSQL に .Net SqlClient Data Provider に対して接続できていることが確認できますね。

Babelfish を使用することで、このように SQL Server のクライアントツール / ライブラリを使用して、PostgreSQL に対して接続を行いクエリの実行を行うことが可能となります。

基盤となるデータベースは PostgreSQL のデータベースを使用しながら、SQL Server のライブラリにより、SQL Server ライクな操作ができるのは面白いですね。

なお、SSMS での接続ですが、Goodbye Microsoft SQL Server, Hello Babelfish に

Important: Do not connect via the SSMS Object Explorer. Be sure to connect using the query editor via the New Query button. At this time, Babelfish supports the query editor, but not the Object Explorer.

と、記載されているようにオブジェクトエクスプーラーからの接続はサポートされておらず、クエリエディタでの接続のみがサポートされている形になります。

Babelfish のコアとなる機能

Babelfish ですが、PostgreSQL の Extension として、実装が行われているようです。

通常の DB では「plpgsql」のみが Extension として追加されていますが、Babelfish で利用される babelfish_db では、次の Extension が追加されています。

• babelfishpg_common
• babelfishpg_tds
• babelfishpg_tsql
• uuid-ossp

babelfish～ の Extension が Babelfish 用の Extension となり、これらの Extension を使用することで、SQL Server の TDS によるアクセスが可能になるようですね。

TDS を使用したデータアクセス / レスポンスと、T-SQL をパースすることができる Extension によって、Babelfish は構成されている感じなのでしょうかね。

babelfish_db 以外には、Babelfish 向けの Extension が含まれていませんので、それ以外の DB では SQL Server 互換の機能を使用することはできないようです。

SQL Server 固有のオブジェクトの互換性

SQL Server のライブラリで接続をして、babelfish_db にストアドプロシージャを作成することができ、

CREATE PROCEDURE dbo.usp_03
AS
set nocount on
DECLARE @datetime datetime2(3)
SET @datetime = GETDATE()
SELECT @datetime, NEWID()
create table #t(C1 int)

DECLARE @cnt int = 1
begin tran
WHILE(@cnt <= 10)
begin
print @cnt
insert into #t values(@cnt)
set @cnt += 1
end

rollback tran

select count(*) from #t

として、作成したストアドプロシージャは PostgreSQL 上では、次のように作成されています。

-- PROCEDURE: dbo.usp_03()
-- DROP PROCEDURE dbo.usp_03();

CREATE OR REPLACE PROCEDURE dbo.usp_03(
)
LANGUAGE 'pltsql'
AS $BODY$
set nocount on
DECLARE @datetime datetime2(3)
SET @datetime = GETDATE()
SELECT @datetime, NEWID()
create table #t(C1 int)

DECLARE @cnt int = 1
begin tran
WHILE(@cnt <= 10)
begin
print @cnt
insert into #t values(@cnt)
set @cnt += 1
end

rollback tran

select count(*) from #t
$BODY$;

SQL Server で作成したストアドについては、pltsql を使用して、クエリ実行が行われるように指定が行われていますね。

FUNCTION についても pltsql が使用される形で作成が行われるようです。

babelfish_db の sys スキーマ配下には、SQL Server のシステムビューをエミュレートするビューが作成されており、PostgreSQL の標準的な情報で、SQL Server のシステムビュー相当のビューを提供するということも行われているようです。

PostgreSQL で T-SQL を実行するという仕組みについては、pgTSQL というものがあるのですが、これと Babelfish に関係性があるのかはよくわからず。

クエリの互換性

SQL Server 向けの構文については、すべてがサポートされているわけではなく、構文のサポート状況については、Differences between Aurora PostgreSQL with Babelfish and SQL Server で公開されています。

対応している構文等については、この情報から確認できるかと。

対応していない機能の例としては「BULK INSERT」関連の機能などがあります。

基本的なアプローチとしては、PostgreSQL のデータベースエンジンの機能を使用して、SQL Server の構文を可能な範囲でサポートするというものとなっているため、PostgreSQL で実装されていない機能については、Babelfish を使用しても利用することは難しいのではないでしょうか。

日本語文字列の利用

SQL Server ベースのデータベースエンジンを使用する場合、照合順序も意識をする必要が出てくるのですが、Babelfish の照合順序については、Babelfish collation support で解説が行われています。

日本語照合順序がないので、SQL Server の char / varchar を利用した、日本語の取り扱いは、厳しそうな雰囲気が少しありました。

Babelfish では、SQL Server のライブラリで接続を行って、CREATE TABLE を実行することができます。

SQL Server で実行した CREATE TABLE は、PostgreSQL では次のように変換されて実行が行われます。

このようなテーブル定義でテーブルを作成した場合、rchar 型に日本語の文字列を挿入することはでき、PostgeSQL 上は正常にデータが投入されているのですが、TDS でデータを取得した場合は、日本語照合順序が指定できないため文字コード変換が正常に実施できず、文字化けしてしまうのですよね。

nvarchar として取り扱えば日本語の文字列も正常に取得できていました。

日本語の文字列を取り扱う場合、varchar ではなく、nvarchar を使用したほうが、データに対しての互換性が高いのではないでしょうか。

上記の場合、varchar については Code Page 1033 で解釈されるため、日本語の文字列は正しく処理を行うことができません。

Babelfish collation support で利用可能な照合順序は公開されているのですが 2021/11/2 時点では、日本語照合順序はサポートされていません。

現時点では、日本語のマルチバイト文字を使用する場合には、nvarchar を使用するのが推奨となると思いますが、どうしても varchar で表示できるようにしなくてはいけない場合「chinese_prc_ci_as」で、ある程度 (すべての文字は対応できません) は、日本語の文字に対応できそうでした。

「chinese_prc_ci_as」の照合順序を使用すると、varchar は CP 2052 で文字列をエンコードした結果を返すようになるのですが、CP 2052 でもある程度、日本語の文字が対応されているようで、varchar の日本語文字列の表示も対応できるようです。

Aurora 以外の PostgreSQL で Babelfish を使用する

Aurora 以外の PostgreSQL でも Babelfish を使用することができます。

試しに WSL2 上で試してみたのですが、PostgreSQL で Babelfish を有効にした環境を起動することができました。

Aurora 以外の PostgreSQL で実行した場合は「Babelfish for PostgreSQL with SQL Server Compatibility」として認識されるようですね。

ローカル環境に Babelfish を構築したい場合には、https://github.com/babelfish-for-postgresql/babelfish_extensions/tree/issue-12/contrib の内容に則って、セットアップを実施すれば、対応できるかと思います。

WSL 上に構築した PostgreSQL 環境に、babelfish_db が構築できていますね。

手軽に検証を実施するのであれば、AWS 上ではなく、ローカル環境に構築してしまってもよさそうですね。

まとめ

Babelfish は、データベースは PostgreSQL を使用しますので、既存の SQL Server の移行を検討する場合には、PostgreSQL のデータベースにデータを移行 (変換) しなくてはいけませんが、移行後は、SQL Server のライブラリからアクセスできる状態を維持しつつ、PostgreSQL のライブラリからもアクセスが可能なデータベースを作成することができるようになります。

• TDS 経由のアクセスは、透過的に babelfish_db に接続される
• PostgreSQL に SQL Server のライブラリにより TDS でのアクセスを可能とする
• SQL Server からのクエリ実行については、SQL Server 互換のクエリ実行を可能とする
  • 次のようなアクセスも可能
    • SQL Server のライブラリから PostgreSQL の統計情報コレクタにアクセス
    • PostgreSQL のライブラリから、SQL Server 互換用のエミュレーションビューにアクセス
• SQL Server のライブラリからの接続については、クエリの実行結果を TDS でレスポンスを可能とする

これらの機能を Aurora PostgreSQL で利用することができるようになります。

既存の SQL Server から移行を行う場合は、AWS Database Migration Service で、SQL Server のデータを Aurora の babelfish_db に移行して、SQL Server のライブラリで Aurora for PostgreSQL に接続を行うという方法になるのでしょうかね。

軽く触ったぐらいで、互換性や性能の検証はあまりできていないのですが、Babelfish は面白い機能ですね。