はじめに
2022/11/4 にAWSのセミナー「Babelfish for Aurora PostgreSQLのご紹介」を聞き、2022/11/5に Babelfish for Aurora PostgreSQLのワークショップに参加したので、備忘のため記録しておく。
Babelfish とは？
Babelfish for PostgreSQL は、Apache 2.0 および PostgreSQL ライセンスの下で利用できるオープンソースプロジェクトになる。1.0 が 2021/10/28 にリリースされているので比較的新しく、2022/11/4 に現時点で最新版の 2.1.2 がリリースされている。
BabelfishがSQL Server ワイヤプロトコルと T-SQL (Microsoft SQL Server クエリ言語) を理解することで、PostgreSQL が Microsoft SQL Server 用に作成されたアプリケーションからのクエリを実行できるようになる。
Babelfish for Aurora PostgreSQL は、Amazon Aurora PostgreSQL 互換エディションの機能であり、Aurora が Microsoft SQL Server 用に作成されたアプリケーションからのコマンドを理解できるようになる。
ありていに言うと、SQL Server向けのクエリがそのまま Aurora PostgreSQLでも実行できる、という形になる。
Babelfishの元ネタ
ちなみに Babelfish の名前の元ネタは、銀河ヒッチハイクガイドという小説に出てくる万能翻訳魚がモデルになっているとのこと。
ワークショップの概要
ワークショップはBabelfishの機能をONにしたAurora for PostgreSQLに、SQL Serverクライアント(SQL Server Management Studio:SSMS)とPostgreSQLクライアント(psql)からアクセスしてみよう、というものになる。
ワークショップの資料はこちらの githubからダウンロードできる。
ワークショップの流れは以下、1時間弱で試せるのでちょっと時間があるときに試すことが可能。
- SQL Serverクライアント用のインスタンスの作成：10分くらい
- Babelfish for Aurora PostgreSQLの作成(Auroraインスタンスの作成)：20分くらい
- psqlクライアント用の環境の作成：10分くらい
- SQL Serverクライアント、psqlクライアントからそれぞれAuroraに接続：10分くらい
詳細な手順はgithubのワークショップ資料を確認の事。
なお、Babelfish for Aurora PostgreSQLの機能はAuroraの作成時にのみ選択可能のようで、現時点では作成後は変更できないので注意が必要。（Snapshotからの復元でもBabelfish自体の言及がないためNGと想定）
オフにする場合にはパラメータグループの変更で可能。
1. SQL Serverクライアント用のインスタンスの作成
SQL Serverクライアントとしての機能が欲しいので、SQL Serverがインストールされているイメージを利用してEC2を起動する。
Linuxでもインストールされているイメージがあるので、そちらを利用してもOKと思われるが、ワークショップの手順としてはWindowsを想定。
2. Babelfish for Aurora PostgreSQLの作成(Auroraインスタンスの作成)
Aurora for PostgreSQLを選択、Babelfish for PostgreSQL機能をサポートするバージョンを選択可能。
Babelfish機能のONは作成時のみ。Babelfish用のパラメータグループが自動的に作成される。
3. psqlクライアント用の環境の作成
ワークショップではCloud9環境を利用しているが、PostgreSQLがインストールされたEC2があったのでそちらを利用。
4. SQL Serverクライアント、psqlクライアントからそれぞれAuroraに接続
SQL Serverクライアントから接続する場合にはSQL Serverのエンドポイントに、psqlクライアントから接続する場合にはPostgreSQL用のエンドポイントに接続する。
- SQL Server エンドポイントへの接続(ワークショップではSSMSを利用しているが、コマンドラインで十分なのでsqlcmdで接続)
PS C:\Windows\system32> sqlcmd -S database-2.cluster-xxxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com -U postgres -P xxxxxxx
1> select @@version;
2> go
version
---------------------------------------------------------------------------------------------------------------------
Babelfish for Aurora Postgres with SQL Server Compatibility - 12.0.2000.8
Sep 28 2022 00:39:06
Copyright (c) Amazon Web Services
PostgreSQL 13.7 on aarch64-unknown-linux-gnu
(1 rows affected)
1> SELECT CAST(SERVERPROPERTY('Babelfish') AS VARCHAR)
2> go
serverproperty
------------------------------
1
(1 rows affected)
- PostgreSQL エンドポイントへの接続
$ psql -h database-2.cluster-xxxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com -p 5432 -U postgres -d postgres
Password for user postgres:
psql (13.7)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
Type "help" for help.
postgres=>
- SQL Server 側でテストデータのインサート
1> USE [master]
2> DROP DATABASE IF EXISTS MySSMSTest
3> CREATE DATABASE MySSMSTest
4> Go
Changed database context to 'master'.
1>
2>
3>
4>
5> USE [MYSSMSTest]
6> CREATE TABLE [dbo].[Student] (
7> [StudentId] [int] NOT NULL,
8> [Name] [nvarchar](50) NOT NULL,
9> [Age] [tinyint] NOT NULL,
10> CONSTRAINT [PK_Student] PRIMARY KEY
11> (
12> [StudentId] ASC
13> )
14> ) ON [PRIMARY]
15> GO
Changed database context to 'myssmstest'.
1>
2>
3>
4>
5> INSERT INTO [dbo].[Student]([StudentId],[Name],[Age]) VALUES (1,'John',15)
6> INSERT INTO [dbo].[Student]([StudentId],[Name],[Age]) VALUES (2,'Kate',20)
7> INSERT INTO [dbo].[Student]([StudentId],[Name],[Age]) VALUES (3,'Sara',65)
8> INSERT INTO [dbo].[Student]([StudentId],[Name],[Age]) VALUES (4,'Mike',19)
9> INSERT INTO [dbo].[Student]([StudentId],[Name],[Age]) VALUES (5,'Ruby',35)
10> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
1> select * from [dbo].[Student]
2> go
StudentId Name Age
----------- -------------------------------------------------- ---
1 John 15
2 Kate 20
3 Sara 65
4 Mike 19
5 Ruby 35
(5 rows affected)
1>
- PostgreSQL 側での確認
postgres=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
--------------+----------+----------+-------------+-------------+--------------------------
babelfish_db | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres +
| | | | | sysadmin=C*T*c*/postgres+
| | | | | master_dbo=CTc/sysadmin +
| | | | | tempdb_dbo=CTc/sysadmin +
| | | | | msdb_dbo=CTc/sysadmin +
| | | | | dbo=CTc/sysadmin
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
rdsadmin | rdsadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | rdsadmin=CTc/rdsadmin
template0 | rdsadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin +
| | | | | rdsadmin=CTc/rdsadmin
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(5 rows)
postgres=> \c babelfish_db
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
You are now connected to database "babelfish_db" as user "postgres".
babelfish_db=> select * from dbo.student;
studentid | name | age
-----------+------+-----
1 | John | 15
2 | Kate | 20
3 | Sara | 65
4 | Mike | 19
5 | Ruby | 35
(5 rows)
エンドポイント毎に解釈されるSQLも変わる。
※「Babelfish for Aurora PostgreSQL のご紹介」より抜粋
まとめ
Babelfishを利用することで、Aurora for PostgreSQLをSQL Serverライクに扱えることが確認できた。
現時点でのユースケースは具体的には思い浮かばないが、制限事項は多いのが検討の選択肢に入れておくことで今後の選択肢を広げられるようになるとは思われる。
Babelfishでは以下のDBの操作が可能。
- master / tempdb ユーザー DB / sysdatabase & sp_helpdb
- CREATE DATABASE / USE / SELECT DB_NAME()
BabelfishのAurora PostgreSQL 上での実装は以下。(「Babelfish for Aurora PostgreSQL のご紹介」より抜粋)
- Aurora PostgreSQL 上に Babelfish 用の database : babelfish_db が作成される
- Babelfish エンドポイントからの接続は 自動的に babelfish_db に接続
- SQL Server の DB / スキーマ は babelfish_db 内の スキーマにマッピング
- 上記のマッピングは SQL Server エンドポイントを利用するアプリケーションからは透過的
- Babelfish 構成時に シングルデータベースモード か マルチデータベースモードを選択