Babelfish for Aurora PostgreSQL を（ワークショップで）触ってみた

# はじめに

2022/11/4 にAWSのセミナー「Babelfish for Aurora PostgreSQLのご紹介」を聞き、2022/11/5に [Babelfish for Aurora PostgreSQLのワークショップ]()に参加したので、備忘のため記録しておく。

# Babelfish とは？
Babelfish for PostgreSQL は、Apache 2.0 および PostgreSQL ライセンスの下で利用できる[オープンソースプロジェクト](https://babelfishpg.org/)になる。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 の名前の元ネタは、銀河ヒッチハイクガイドという小説に出てくる[万能翻訳魚](https://ja.wikipedia.org/wiki/%E3%83%90%E3%83%99%E3%83%AB%E3%83%95%E3%82%A3%E3%83%83%E3%82%B7%E3%83%A5)がモデルになっているとのこと。

# ワークショップの概要


ワークショップはBabelfishの機能をONにしたAurora for PostgreSQLに、SQL Serverクライアント(SQL Server Management Studio:SSMS)とPostgreSQLクライアント(psql)からアクセスしてみよう、というものになる。

ワークショップの資料はこちらの [github](https://raw.githubusercontent.com/harunobukameda/Babelfish-for-Aurora-PostgreSQL/main/Bablefish%20for%20Aurora%20Postgres%20%E3%83%AF%E3%83%BC%E3%82%AF%E3%82%B7%E3%83%A7%E3%83%83%E3%83%97.pdf)からダウンロードできる。

ワークショップの流れは以下、1時間弱で試せるのでちょっと時間があるときに試すことが可能。

1. SQL Serverクライアント用のインスタンスの作成：10分くらい
1. Babelfish for Aurora PostgreSQLの作成(Auroraインスタンスの作成)：20分くらい
1. psqlクライアント用の環境の作成：10分くらい
1. SQL Serverクライアント、psqlクライアントからそれぞれAuroraに接続：10分くらい

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2313926/f1298472-d4bd-30d6-cbac-3b590f18054f.png)


詳細な手順はgithubのワークショップ資料を確認の事。
なお、Babelfish for Aurora PostgreSQLの機能はAuroraの作成時にのみ選択可能のようで、現時点では作成後は変更できないので注意が必要。（Snapshotからの復元でもBabelfish自体の言及がないためNGと想定）
オフにする場合には[パラメータグループの変更](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/babelfish-remove.html)で可能。

#### 1. SQL Serverクライアント用のインスタンスの作成

SQL Serverクライアントとしての機能が欲しいので、SQL Serverがインストールされているイメージを利用してEC2を起動する。
Linuxでもインストールされているイメージがあるので、そちらを利用してもOKと思われるが、ワークショップの手順としてはWindowsを想定。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2313926/f4725708-5a56-bf9c-dea6-ce51476b8e6c.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2313926/07fbda93-e33e-784a-10a6-83ae0b5d7ed9.png)


#### 2. Babelfish for Aurora PostgreSQLの作成(Auroraインスタンスの作成)

Aurora for PostgreSQLを選択、Babelfish for PostgreSQL機能をサポートするバージョンを選択可能。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2313926/064456c2-608f-c7e8-76fc-6fcb582c7225.png)

Babelfish機能のONは作成時のみ。Babelfish用のパラメータグループが自動的に作成される。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2313926/7d6e4832-fe13-3388-0dff-cf874eecc255.png)

#### 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も変わる。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2313926/40a7bd2f-62ec-462d-da41-4a91ab607be3.png)
※「Babelfish for Aurora PostgreSQL のご紹介」より抜粋


# まとめ

Babelfishを利用することで、Aurora for PostgreSQLをSQL Serverライクに扱えることが確認できた。
現時点でのユースケースは具体的には思い浮かばないが、制限事項は多いのが検討の選択肢に入れておくことで今後の選択肢を広げられるようになるとは思われる。

Babelfishでは以下のDBの操作が可能。
- master / tempdb ユーザー DB / sysdatabase & sp_helpdb
- CREATE DATABASE / USE <database> / 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 構成時に [シングルデータベースモード か マルチデータベースモード](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/babelfish-architecture.html)を選択


###### 注意事項
- [バージョンごとに Babelfish でサポートされている機能](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/babelfish-compatibility.supported-functionality-table.html)
- [Babelfish でサポートされていない機能](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/babelfish-compatibility.tsql.limitations-unsupported.html)
- [Babelfish システムカタログからの情報の入手](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/babelfish-query-database.html)
- [Babelfish がサポートする照合順序](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/babelfish-collations.html)