Amazon Redshift Serverless から Aurora Serverless MySQL 互換バージョンにフェデレーテッドクエリしてみた

コーヒーが好きな木谷映見です。

先日こちらのブログで、Aurora Serverless PostgreSQL 互換バージョンにフェデレーテッドクエリする手順を記載しました。

blog.serverworks.co.jp

今回は Aurora Serverless MySQL 互換バージョンにフェデレーテッドクエリしてみます。

実施手順概要

東京リージョンの Amazon VPC 内に Aurora Serverless MySQL 互換エディションと Redshift Serverless を構築し、Redshift Serverless からフェデレーテッドクエリを実行します。
Aurora Serverless MySQL 互換エディションと Redshift Serverless に接続して操作を行うためのクライアントとして、パブリックサブネットに EC2 インスタンスを 1 台作成し、セッションマネージャーで手元の端末から接続できるようにしておきます。
Redshift Serverless から Aurora Serverless MySQL 互換エディションへ接続するための認証情報は AWS Secrets Manager に保存します。

AWS 検証環境の準備

EC2 インスタンスに psql をインストールして Redshift Serverless に接続する

EC2 インスタンスに psql をインストールして Redshift Serverless に接続するまでの手順は、以下のブログをご参照ください。 blog.serverworks.co.jp

Redshift Serverless に付与する IAM ロールには、信頼ポリシーのみ付与しておいてください。

• 信頼ポリシードキュメントで設定する「信頼されているエンティティ」
  • "redshift.amazonaws.com"
  • "redshift-serverless.amazonaws.com"

信頼関係タブの信頼されたエンティティはこうなります。

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": [
                    "redshift.amazonaws.com",
                    "redshift-serverless.amazonaws.com"
                ]
            },
            "Action": "sts:AssumeRole"
        }
    ]
}

実はフェデレーテッドクエリのために必要な許可は Secrets Manager へのアクセス許可だけなので、今は ID ベースの許可ポリシーは設定しなくても大丈夫です。 この IAM ロールはこの後 AWS Secret Manager の設定時に編集します。

Aurora Serverless MySQL 互換エディションを作成する

DB サブネットグループをあらかじめ作成しておきます。

設定項目	パラメータ	備考
DB サブネットグループ	今回は Redshift Servreless に合わせて 3 つの AZ にまたがる 3 つのサブネットを選択する	図を参照

以下のような Aurora Serverless MySQL 互換エディションを作成します。記載していない設定はデフォルトのままです。

設定項目	パラメータ	備考
エンジンのタイプ	Amazon Aurora
エディション	Amazon Aurora MySQL 互換エディション
エンジンバージョン	Aurora MySQL (compatible with MySQL 8.0.23)	「フィルターの非表示」で「Serverless v2 をサポートするバージョンを表示」にチェックすると出てくる
テンプレート	開発/テスト
DB クラスター識別子	database-3
マスターユーザー名	admin
マスターパスワード	************
DB インスタンスクラス	サーバーレス	容量の範囲 2 ~ 16 ACU
マルチ AZ 配置	Aurora レプリカを使用しない
VPC、サブネット	作成しておいた VPC と DB サブネットグループを選択	図を参照
セキュリティグループ	aurora-mysql-sg	Aurora/MySQL ポート 3306 client-sg からのインバウンド許可、Aurora/MySQL ポート 3306 redshift-serverless-sg からのインバウンド許可
AZ	今回は ap-northeast-1c	任意の AZ

セキュリティグループの確認

ここまでで、セキュリティグループの設定は以下のようになっています。

AWS Secrets Manager で Aurora Serverless MySQL データベース用のシークレットを設定

フェデレーテッドクエリを使用するためのシークレットと IAM ロールの作成 を参考に、Secrets Manager で Aurora Serverless MySQL データベース用のシークレットを設定します。 シークレットを設定したら、Redshift Serverless が Secrets Manager に保存されたシークレットにアクセスできるよう、 Redshift Serverless に付与している IAM ロールに権限を追加します。

AWS Secrets Manager で Aurora Serverless MySQL データベース用のシークレットを以下のように設定します。記載していない設定はデフォルトのままです。

設定項目	パラメータ	備考
シークレットのタイプ	Amazon RDS データベースの認証情報
ユーザー名	admin
パスワード	************
暗号化キー	aws/seretsmanager
データベース	database-3
シークレットの名前	dev/Redshift-FederatedQuery/AuroraMySQL3	任意の名前

Secrets Manager で Aurora Serverless MySQL データベース用のシークレットが作成できたら、シークレットの ARN をコピーして控えておきます。

IAM マネジメントコンソールに移動し、以下の JSON を使用して IAM カスタム管理ポリシーを作成します。"Sid": "AccessSecret" で始まる上部のポリシーの "Resource" に、控えておいたシークレットの ARN を貼り付けます。

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "AccessSecret",
            "Effect": "Allow",
            "Action": [
                "secretsmanager:GetResourcePolicy",
                "secretsmanager:GetSecretValue",
                "secretsmanager:DescribeSecret",
                "secretsmanager:ListSecretVersionIds"
            ],
            "Resource": "arn:aws:secretsmanager:ap-northeast-1:xxxxxxxxxxxx:secret:dev/Redshift-FederatedQuery/AuroraMySQL3-JfXW5s"
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": [
                "secretsmanager:GetRandomPassword",
                "secretsmanager:ListSecrets"
            ],
            "Resource": "*"
        }
    ]
}

IAM ポリシーが作成できたら、Redshift Serverless に付与されている IAM ロールに、作成した IAM ポリシーをアタッチします。
今回は IAM ロール yyyyMMdd-Redshift-ServerlessRole に、作成した IAM カスタム管理ポリシーをアタッチします。

注意：Redshift Serverless で拡張された VPC のルーティングを有効にしている場合

Redshift Serverless の設定で「拡張された VPC のルーティング」を有効にしている場合、Secrets Manager からシークレットを取得するために VPC エンドポイントが必要です。

Secrets Manager 用 VPC エンドポイントの設定例を以下に記載しておきます。

設定項目	パラメータ	備考
名前タグ	secrets-manager-endp	任意の名前
サービスカテゴリ	AWS のサービス
サービス	com.amazonaws.ap-northeast-1.secretsmanager
VPC	今回 Redshift Serverless を作成している VPC を選択
サブネット	今回 Redshift Serverless を作成しているサブネット 3 つを選択	インターフェイス型 VPC エンドポイントは AZ障害に備え冗長化しておくことが推奨される
セキュリティグループ	secrets-manager-endp-sg	HTTP ポート 443 redshift-serverlss-sg からのインバウンド許可
ポリシー	フルアクセス	今回はセキュリティグループで通信制御するため、エンドポイントポリシーは制限しない

拡張された VPC のルーティングについての詳細は、以下の記事をご参照ください。 blog.serverworks.co.jp

フェデレーテッドクエリをするための準備

AWS 検証環境の準備が完了したので、Redshift Serverless と Aurora Serverless にログインしてテーブルやスキーマを作成していきます。

Aurora Serverless MySQL でサンプルデータベースを作成

これからコマンドを用いて、Aurora Serverless MySQL で以下のように設定していきます。

設定項目	パラメータ	備考
サンプルデータベース名	auroramysqldb
スキーマ名	users

• age_table

id	name	age
1	aoki	20
2	iida	21
3	usui	22

• job_table

id	job
1	engineer
2	musician
3	driver

EC2 インスタンス に MySQL クライアントをインストールします。
MySQL 公式 yum リポジトリを追加します。

sudo yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm

MySQL クライアントをインストールします。

sudo yum install mysql-community-client -y

EC2 インスタンスから Aurora Serverless MySQL に以下ログインコマンドでログインします。

• 構文

mysql -h <Aurora Serverless MySQL クラスターエンドポイント or リーダーエンドポイント> -P 3306 -u admin -p

• 例

mysql -h database-3.cluster-cno8jxqxarf4.ap-northeast-1.rds.amazonaws.com -P 3306 -u admin -p

• ログイン後の表示

sh-4.2$ mysql -h database-3.cluster-cno8jxqxarf4.ap-northeast-1.rds.amazonaws.com -P 3306 -u admin -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 310
Server version: 8.0.23 Source distribution

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Aurora MySQL のデータベース auroramysqldb を作成します。

• 構文

CREATE DATABASE <データベース名>;

• 例

CREATE DATABASE auroramysqldb;

• 実行結果

mysql> create database auroramysqldb;
Query OK, 1 row affected (0.03 sec)

mysql>

データベース一覧を表示します。

• 構文

show databases;

• 実行結果

mysql> show databases;
+------------------------+
| Database               |
+------------------------+
| auroramysqldb |
| information_schema     |
| mysql                  |
| performance_schema     |
| sys                    |
+------------------------+
5 rows in set (0.03 sec)

mysql> create database auroramysqldb;
Query OK, 1 row affected (0.03 sec)

mysql>

データベースを選択します。

• 構文

use <データベース名>;

• 例

use auroramysqldb;

• 実行結果

mysql> use auroramysqldb;
Database changed
mysql>

今どのデータベースか確認

• 構文

SELECT database();

• 実行結果

mysql> SELECT database();
+---------------+
| database()    |
+---------------+
| auroramysqldb |
+---------------+
1 row in set (0.00 sec)

mysql>

テーブルを作成します。

• 構文

CREATE TABLE [テーブル名] (
  [フィールド名] [データ型] [オプション]
) ENGINE=[InnoDB/MyISAM] DEFAULT CHARSET=[文字コード];

• 例

 CREATE TABLE age_table (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `age` INT,
  PRIMARY KEY (`id`));

• 実行結果

mysql>  CREATE TABLE age_table (
    ->   `id` INT NOT NULL,
    ->   `name` VARCHAR(45) NOT NULL,
    ->   `age` INT,
    ->   PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.02 sec)

mysql>

desc <テーブル名>; で作成したテーブルを確認できます。

• 実行結果

mysql> desc age_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(45) | NO   |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql>

age_table テーブルにデータを登録します。

• 構文

INSERT INTO テーブル名 (カラム名1, カラム名2, ……)
VALUE(値a1， 値a2, ……),
(値b1， 値b2, ……),
……;

• 例

INSERT INTO age_table (id, name, age)
VALUE(1,'aoki',20),
(2,'iida',21),
(3,'usui',22);

• 実行結果

mysql> INSERT INTO age_table (id, name, age)
    -> VALUE(1,'aoki',20),
    -> (2,'iida',21),
    -> (3,'usui',22);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

select * from age_table; でテーブル内容を確認します。

• 実行結果

mysql> select * from age_table;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | aoki |   20 |
|  2 | iida |   21 |
|  3 | usui |   22 |
+----+------+------+
3 rows in set (0.00 sec)

mysql>

同様の手順で、job_table テーブルを作成します。

mysql>  CREATE TABLE job_table (
    ->   `id` INT NOT NULL,
    ->   `job` VARCHAR(45) NOT NULL,
    ->   PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.13 sec)

mysql> desc job_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| job   | varchar(45) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> INSERT INTO job_table (id, job)
    -> VALUE(1,'engineer'),
    -> (2,'musician'),
    -> (3,'driver');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from job_table;
+----+----------+
| id | job      |
+----+----------+
|  1 | engineer |
|  2 | musician |
|  3 | driver   |
+----+----------+
3 rows in set (0.00 sec)

mysql>

Redshift Serverless でサンプルデータベースを作成

Redshift Serverless でも同様にコマンドを用いて、以下のように設定していきます。

設定項目	パラメータ	備考
サンプルデータベース名	redshiftdb
スキーマ名	users

• food_table

id		food
1	onigiri
2	tamagoyaki
3	coffee

psql をインストールしておいた EC2 インスタンスから Redshift Serverless に以下ログインコマンドでログインします。

• 構文

psql -h <Redshift Serverless エンドポイント> -U <管理者ユーザー名> -d dev -p 5439

• 実行結果

sh-4.2$ psql -h 20221018-workgp.xxxxxxxxxxxx.ap-northeast-1.redshift-serverless.amazonaws.com -U rsadmin -d dev -p 5439
Password for user rsadmin:
psql (9.2.24, server 8.0.2)
WARNING: psql version 9.2, server version 8.0.
         Some psql features might not work.
SSL connection (cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.

dev=#

Redshift Serverless のデータベース redshiftdb を作成します。

CREATE DATABASE redshiftdb;

データベースを選択します。

\c redshiftdb

現在のスキーマを確認します。

select current_schema();

スキーマを作成します。

CREATE SCHEMA users;

\dn でスキーマ一覧を確認できます。 スキーマを変更します。

SET search_path = users;

現在のスキーマを再度確認し、users になっていることを確認します。

テーブル food_table を作成します。

CREATE TABLE food_table
 (id char(4) not null,
 food text not null,
 PRIMARY KEY(id));

food_table テーブルにデータを登録します。

INSERT INTO food_table
 (id,food)VALUES
 (1,'onigiri'),
 (2,'tamagoyaki'),
 (3,'coffee');

select * from food_table; でテーブル内容を確認します。

• 実行結果

redshiftdb=# select * from food_table;

  id  |    food
------+------------
 1    | onigiri
 2    | tamagoyaki
 3    | coffee
(3 rows)

redshiftdb=#

外部スキーマを使用して Aurora Serverless MySQL データベースに接続する

CREATE EXTERNAL SCHEMA - Amazon Redshift を参考に、現在のデータベースに新しい外部スキーマを作成します。この外部スキーマを使用して、Amazon RDS for MySQL または Amazon Aurora MySQL 互換エディションデータベースに接続できます。
この操作は Redshift 側でおこないます。

設定項目	パラメータ	備考
作成する外部スキーマ名	newusers2
接続するサービス	MYSQL
接続先のデータベース	auroramysqldb
接続先のURL	database-3.cluster-cno8jxqxarf4.ap-northeast-1.rds.amazonaws.com	Aurora Serverless MySQL クラスターエンドポイント or リーダーエンドポイント
Redshift に付与した IAM ロールの ARN	arn:aws:iam::xxxxxxxxxxxx:role/service-role/yyMMdd_RedshiftServerlessRole
シークレット（認証情報）の ARN	arn:aws:secretsmanager:ap-northeast-1:xxxxxxxxxxxx:secret:dev/Redshift-FederatedQuery/AuroraMySQL3-JfXW5s

• 構文

CREATE EXTERNAL SCHEMA [IF NOT EXISTS] local_schema_name
FROM MYSQL
URI 'hostname' [ PORT port_number ] 
IAM_ROLE { default | 'arn:aws:iam::<AWS アカウント-id>:role/<role-name>' }
SECRET_ARN 'ssm-secret-arn' 

• 例

CREATE EXTERNAL SCHEMA newusers2
FROM MYSQL
DATABASE 'auroramysqldb' 
URI 'database-3.cluster-ro-cno8jxqxarf4.ap-northeast-1.rds.amazonaws.com'
PORT 3306
IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/yyyyMMdd-Redshift-ServerlessRole'
SECRET_ARN 'arn:aws:secretsmanager:ap-northeast-1:xxxxxxxxxxxx:secret:dev/Redshift-FederatedQuery/AuroraMySQL3-JfXW5s';

• 実行結果

redshiftdb=# CREATE EXTERNAL SCHEMA newusers2
redshiftdb-# FROM MYSQL
redshiftdb-# DATABASE 'auroramysqldb'
redshiftdb-# URI 'database-3.cluster-ro-cno8jxqxarf4.ap-northeast-1.rds.amazonaws.com'
redshiftdb-# PORT 3306
redshiftdb-# IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/yyyyMMdd-Redshift-ServerlessRole'
redshiftdb-# SECRET_ARN 'arn:aws:secretsmanager:ap-northeast-1:xxxxxxxxxxxx:secret:dev/Redshift-FederatedQuery/AuroraMySQL3-JfXW5s';
CREATE SCHEMA
redshiftdb=#

SVV_EXTERNAL_SCHEMAS を使用して、外部スキーマの情報を表示します。

• 例

SELECT * FROM svv_external_schemas WHERE schemaname = 'newusers2';

• 実行結果

redshiftdb=# SELECT * FROM svv_external_schemas WHERE schemaname = 'newusers2';
 esoid  | eskind | schemaname | esowner | databasename  |
         esoptions
--------+--------+------------+---------+---------------+-----------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 159237 |      8 | newusers2  |     100 | auroramysqldb | {"URI":"database-3.cluster-ro-cno8jxqxarf4.ap-northeast-1.rds.amazonaws.com","PORT":3306,"IAM_ROLE":"arn:aws:iam::xxxxxxxxxxxx:rol
e/yyyyMMdd-Redshift-ServerlessRole","SECRET_ARN":"arn:aws:secretsmanager:ap-northeast-1:xxxxxxxxxxxx:secret:dev/Redshift-FederatedQuery/AuroraMySQL3-JfXW5s"}
(1 row)

redshiftdb=#

eskind は外部スキーマの外部カタログのタイプで、8 は Aurora Serverless MySQL へのフェデレーテッドクエリを示しています。

SVV_TABLES を使用して、作成した外部スキーマ newusers2 に接続し、参照できる外部テーブルを確認します。Aurora Serverless MySQL 上のテーブルが参照できたら接続は成功です。

• 例

SELECT * FROM svv_tables where table_type = 'EXTERNAL TABLE' AND table_schema = 'newusers2';

• 実行結果

redshiftdb=# SELECT * FROM svv_tables where table_type = 'EXTERNAL TABLE' AND table_schema = 'newusers2';
 table_catalog | table_schema | table_name |   table_type   | remarks
---------------+--------------+------------+----------------+---------
 redshiftdb    | newusers2    | age_table  | EXTERNAL TABLE |
 redshiftdb    | newusers2    | job_table  | EXTERNAL TABLE |
(2 rows)

redshiftdb=#

Redshift Serverless から、Aurora Serverless MySQL 上のテーブルが参照できました。

フェデレーテッドクエリの実行

Aurora Serverless MySQL の age_table テーブル内の行数を表示します。

• 例

SELECT count(*) FROM newusers2.age_table;

• 実行結果

redshiftdb=# SELECT count(*) FROM newusers2.age_table;
 count
-------
     3
(1 row)

redshiftdb=#

Aurora Serverless MySQL の age_table テーブルを表示します。

• 例

select * from newusers2.age_table;

• 実行結果

redshiftdb=# select * from newusers2.age_table;
  id  | name | age
------+------+-----
 1    | aoki |  20
 2    | iida |  21
 3    | usui |  22
(3 rows)

redshiftdb=#

Aurora Serverless MySQL の age_table テーブルと job_table から name, age, job を取り出して表示します。

• 例

SELECT name, age, job
  FROM newusers2.age_table,newusers2.job_table
  WHERE age_table.id = job_table.id;

• 実行結果

redshiftdb=# SELECT name, age, job
FROM newusers2.age_table,newusers2.job_table
WHERE age_table.id = job_table.id;
 name | age |   job
------+-----+----------
 iida |  21 | musician
 usui |  22 | driver
 aoki |  20 | engineer
(3 rows)

redshiftdb=# 

Aurora Serverless MySQL の age_table テーブルと Redshift Serverless の food_table から name, age, food を取り出して表示します。

• 例

SELECT name, age, food
  FROM newusers2.age_table,users.food_table
  WHERE age_table.id = food_table.id;

• 実行結果

redshiftdb=# SELECT name, age, food
redshiftdb-# FROM newusers2.age_table,users.food_table
redshiftdb-# WHERE age_table.id = food_table.id;
 name | age |    food
------+-----+------------
 aoki |  20 | onigiri
 iida |  21 | tamagoyaki
 usui |  22 | coffee
(3 rows)

redshiftdb=#

Redshift Serverless のテーブルと Aurora Serverless MySQL のテーブルを結合したクエリが実行できました。

参考

MySQL Yum リポジトリを使用して MySQL を Linux にインストールする

MySQL への横串検索の使用を開始する

emi kitani(執筆記事の一覧)

AS部LX課。2022/2入社、コーヒーとサウナが好きです。執筆活動に興味があります。AWS認定12冠。