コーヒーが好きな木谷映見です。
先日こちらのブログで、Aurora Serverless MySQL 互換バージョンにフェデレーテッドクエリする手順を記載しました。 blog.serverworks.co.jp 今回は、 Redshift クエリエディタ v2 でフェデレーテッドクエリを試してみます。
- Redshift クエリエディタ v2 とは
- 準備
- Redshift クエリエディタ v2 への接続
- Redshift Serverless でサンプルデータベースを作成
- 外部スキーマを使用して Aurora Serverless MySQL データベースに接続する
- フェデレーテッドクエリの実行
- 参考
Redshift クエリエディタ v2 とは
クエリエディタ v2 とは、Amazon Redshift データウェアハウスでクエリを作成および実行するために使用できる、ウェブベースの SQL クライアントアプリケーションです。
結果をグラフで視覚化し、チーム内の他のユーザーとクエリを共有することで共同作業を行うことも可能です。
![](https://cdn-ak.f.st-hatena.com/images/fotolife/s/swx-kitani/20221023/20221023231423.png)
クエリエディタ v2 へアクセスするのに必要な IAM 権限は、以下のドキュメントを参照ください。
準備
東京リージョンの Amazon VPC 内に Aurora Serverless MySQL 互換エディションと Redshift Serverless を構築し、Redshift Serverless からフェデレーテッドクエリを実行します。
以下のブログを参考に、3. フェデレーテッドクエリをするための準備 - Aurora Serverless MySQL でサンプルデータベースを作成 の終わりまで(3. フェデレーテッドクエリをするための準備 - Redshift Serverless でサンプルデータベースを作成 の前まで)を実施してください。 blog.serverworks.co.jp
上記ブログ手順では、Redshift Serverless に付与するセキュリティグループでクライアントとしている EC2 インスタンスからの Redshift 5439 インバウンドアクセスを許可していますが、本手順ではクライアントから Redshift Serverless には接続せず、Redshift クエリエディタv2 を使ってブラウザから Redshift Serverless にアクセスするため、このセキュリティグループルールは不要です。
Redshift クエリエディタ v2 への接続
Redshift コンソールで Redshift Serverless ダッシュボードにアクセスし、[クエリエディタ v2] をクリックすると、クエリエディタ v2 がブラウザの新しいタブで開きます。
画面左に、東京リージョンに存在するワークグループがグレーで表示されています。
アクセスしたいワークグループをクリックします。
ワークグループへの接続情報が求められます。今回は [Database user name and password] を選択し、Redshift Serverless の管理者ユーザー名とパスワードで接続します。
ワークグループに接続できると、ワークグループ内に存在するデータベースが表示できるようになります。
Redshift Serverless でサンプルデータベースを作成
Redshift Serverless でクエリエディタ v2 を用いて、以下のように設定していきます。
設定項目 | パラメータ | 備考 |
---|---|---|
サンプルデータベース名 | redshiftdb | |
スキーマ名 | users |
- food_table
id | food | |
---|---|---|
1 | onigiri | |
2 | tamagoyaki | |
3 | coffee |
データベースを作成します。左上の [Create] ボタンから [Database] をクリックします。
Create Database 画面が開いたら、以下のように設定します。
項目 | 設定値 | 備考 |
---|---|---|
Cluster or workgroup | 2022mmdd-workgroup | 作成したワークグループ名を選択 |
Database | redshiftdb | 作成するデータベース名 |
User and groups | rsadmin | 今回は管理者ユーザー名を選択 |
一番下の項目はデータ共有を作成する場合に設定する項目なので、今回はチェックしません。設定したら [Create Database] をクリックします。
データベース redshiftdb が作成できました。画面左メニューで redshiftdb を確認することができます。デフォルトのスキーマとして public が用意されています。
今度はスキーマを作成します。画面左上の [Create] ボタンから [Schema] をクリックします。
Create Schema 画面が開いたら、以下のように設定します。
項目 | 設定値 | 備考 |
---|---|---|
Cluster or workgroup | 2022mmdd-workgroup | 作成したワークグループ名を選択 |
Database | redshiftdb | 先ほど作成したデータベース名を選択 |
Schema | users | 作成するスキーマ名を入力 |
Schema type | Local | 今回は管理者ユーザー名を選択 |
設定したら [Create Schema] をクリックします。
スキーマ users が作成できました。画面左メニューで確認することができます。
ちなみに緑のバーは「×」をクリックすると消えます。
テーブル food_table を作成します。
クエリを実行してもいいのですが、画面操作でもテーブル作成できてしまうので、やってみましょう。画面左上の [Create] ボタンから [Table] をクリックします。
項目 | 設定値 | 備考 |
---|---|---|
Cluster or workgroup | 2022mmdd-workgroup | 作成したワークグループ名を選択 |
Database | redshiftdb | 先ほど作成したデータベース名を選択 |
Schema | users | 先ほど作成したスキーマ名を入力 |
Table | food_table | 作成するテーブル名を入力 |
Column name | id | Primary key |
Data type | CHAR | データタイプを選択。character(1) が設定される |
Column Options で Not NULL と Primary key をオンにしておきます。
続いて、[+ Add field] でフィールドを追加します。
項目 | 設定値 | 備考 |
---|---|---|
Column name | food | |
Data type | VARCHAR | データタイプを選択。character varying(256) が設定される |
Column Options で Not NULL をオンにしておきます。
[Create table] でテーブルを作成します。
テーブルが作成できました。
food_table テーブルにデータを登録します。
INSERT INTO food_table (id,food)VALUES (1,'onigiri'), (2,'tamagoyaki'), (3,'coffee');
エディタ部分にクエリを入力します。
右上で対象のワークグループ(今回は 2022mmdd-workgroup)と対象のデータベース(今回は redshiftdb)が選択されていることを確認します。
エディタ部分に入力したクエリを選択した状態で [Run] をクリックすると、選択した部分のクエリが実行されます。
select * from users.food_table;
でテーブル内容を確認します。
エディタ部分にクエリを入力します。
右上で対象のワークグループ(今回は 2022mmdd-workgroup)と対象のデータベース(今回は redshiftdb)が選択されていることを確認します。
エディタ部分に入力したクエリを選択した状態で [Run] をクリックすると、選択した部分のクエリが実行されます。
画面下部に、実行結果が表示されます。
外部スキーマを使用して Aurora Serverless MySQL データベースに接続する
CREATE EXTERNAL SCHEMA - Amazon Redshift を参考に、現在のデータベースに新しい外部スキーマを作成します。この外部スキーマを使用して、Amazon RDS for MySQL または Amazon Aurora MySQL 互換エディションデータベースに接続できます。
引き続きクエリエディタ v2 で操作します。
外部スキーマを作成します。画面左上の [Create] ボタンから [Schema] をクリックします。
Create Schema 画面が開いたら、以下のように設定します。
項目 | 設定値 | 備考 |
---|---|---|
Cluster or workgroup | 2022mmdd-workgroup | 作成したワークグループ名を選択 |
Database | redshiftdb | 先ほど作成したデータベース名を選択 |
Schema | newusers2 | 作成する外部スキーマ名を入力 |
Schema type | External | 外部スキーマを選択 |
接続するサービス | MYSQL | |
Source database name | auroramysqldb | 接続先データベース名を入力。今回は Aurora Serverless MySQL に作成したデータベース名を入力 |
URI of host | database-3.cluster-cno8jxqxarf4.ap-northeast-1.rds.amazonaws.com | 接続先のURL。Aurora Serverless MySQL クラスターエンドポイント or リーダーエンドポイント |
IAM role | arn:aws:iam::xxxxxxxxxxxx:role/service-role/yyMMdd_RedshiftServerlessRole | Redshift に付与した IAM ロールの ARN |
Secrets Amazon Resource Name (ARN) | arn:aws:secretsmanager:ap-northeast-1:xxxxxxxxxxxx:secret:dev/Redshift-FederatedQuery/AuroraMySQL3-JfXW5s | シークレット(認証情報)の ARN |
外部スキーマ newusers2 が作成できました。
展開すると、Aurora Serverless MySQL に作成したテーブルが表示されるのが確認できます。
SVV_EXTERNAL_SCHEMAS を使用して、外部スキーマの情報を表示します。
- 例
SELECT * FROM svv_external_schemas WHERE schemaname = 'newusers2';
- 実行結果
eskind
は外部スキーマの外部カタログのタイプで、8 は Aurora Serverless MySQL へのフェデレーテッドクエリを示しています。
SVV_TABLES を使用して、作成した外部スキーマ newusers2
に接続し、参照できる外部テーブルを確認します。Aurora Serverless MySQL 上のテーブルが参照でき、接続が成功していることが確認できます。
- 例
SELECT * FROM svv_tables where table_type = 'EXTERNAL TABLE' AND table_schema = 'newusers2';
- 実行結果
Redshift Serverless から、Aurora Serverless MySQL 上のテーブルが参照できました。
フェデレーテッドクエリの実行
Aurora Serverless MySQL の age_table テーブル内の行数を表示します。
- 例
SELECT count(*) FROM newusers2.age_table;
実行結果
Aurora Serverless MySQL の age_table テーブルを表示します。
例
select * from newusers2.age_table;
実行結果
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;
実行結果
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;
- 実行結果
Redshift Serverless のテーブルと Aurora Serverless MySQL のテーブルを結合したクエリが実行できました。
参考
Amazon Redshift クエリエディタ v2 を使用したデータベースのクエリの実行
emi kitani(執筆記事の一覧)
AS部LX課。2022/2入社、コーヒーとサウナが好きです。執筆活動に興味があります。AWS認定12冠。