[新機能] Amazon Redshift Federated QueryがGAになったので試してみました

石川覚

2020.04.19

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

昨年のre:Invent2019で発表されたAmazon RedshiftのFederated QueryがGA（Generally Available：正式リリース）になりました！クラスタバージョン1.0.14677から利用可能なので早速試してみました。

Amazon Redshift Federated Querying is Now Available!

1⃣What's New - https://t.co/dbZjTSTX2z

2⃣Use Cases - https://t.co/y8NS5JaMhC

3⃣Getting Started Using CloudFormation - https://t.co/Pz9wmqK29d pic.twitter.com/ghi8O9m6JS

— Jeff Barr ☁️ (@ ? ) (@jeffbarr) April 16, 2020

Amazon Redshift の Federated Queryとは

RDSとAurora PostgreSQLのテーブルにRedshiftから直接アクセスできるようになりました。いわゆる、RedshiftからPostgreSQLに対してデータベースリンクする機能です。

Federated Queryの特長

RedshiftによるPostgreSQLのデータ統合

Federated Queryを用いることで、Amazon RDS for PostgreSQLまたはAmazon Aurora with PostgreSQL compatibilityとデータを連携できます。これまで、Redshift/Redshift SpectrumのデータとPostgreSQL上のデータと組み合わせて分析するには、PostgreSQLのデータをS3経由でRedshiftにロードする必要がありました。これからはRedshiftからPostgreSQL内に保持するデータとRedshift/Redshift Spectrumのデータをクエリで統合できるようになります。

Federated Queryを使用して、OLTPのライブデータをビジネスインテリジェンス（BI）およびレポートアプリケーションの一部として組み込むことができます。例えば、以下のようなユースケースがあります。

RedshiftからOLTP用途の基幹データベースを直接クエリできる
RedshiftでOLTP用途のデータの変換をすばやく適用できます
ETLなしでOLTP用途のデータを直接Redshiftのターゲットテーブルに読み込めます

RDS/Aurora PostgreSQLによる並列分散

Federated Queryは、単純なデータベースリンクではなく、クエリの一部をPostgreSQL上で実行して処理負荷を分散、結果のみを送り返すことネットワーク上のデータ移動を減らしてパフォーマンスを向上させます。

Federated Queryを実行すると、RedshiftのリーダーノードからPostgreSQLのDBインスタンスへのクライアント接続を作成して、テーブルメタデータを取得します。Redshiftのコンピュートノードは、プリディケイトプッシュダウンされたサブクエリを発行し、結果のレコードを取得します。取得した結果のレコードはコンピュートノード間で分散して処理します。

Federated Queryの導入

導入の流れ

RedshiftからRDSまたはAurora PostgreSQLへ接続するための認証情報は、Secrets Managerのシークレットを用います。そのため、シークレットを作成して、Redshiftがシークレットを参照するためのIAMロールをRedshiftに付与します。あとは、RedshiftからRDSまたはAurora PostgreSQLへの外部スキーマを作成して完了です。もちろん、RedshiftからターゲットとなるRDSまたはAurora PostgreSQLへ接続するためのネットワークやセキュリティグループも設定してください。

０．導入環境

今回導入した環境はバージニア（us-east-1）のパブリックサブネットにあるRedshift（クラスタバージョン1.0.14677）とRDS PostgreSQL（エンジンバージョン11.5）間でFederated Queryを試しました。

検証に用いるデータは、AWS Redshift用のサンプルデータベースのデータです。色々と組み合わせを試したかったので、RedshiftとPostgreSQLにtickitスキーマとテーブルを準備しました。

Redshift
データベース名：cm-redshiftdb
スキーマ名：tickit
外部スキーマ名：pg
RDS PostgreSQL
データベース名：rdspg
スキーマ名：tickit

１．セキュリティグループの設定（落とし穴）

RedshiftからターゲットとなるRDS PostgreSQLへ接続できるようにセキュリティグループの設定が必要です。Redshiftは、ノードIPアドレスのパブリックIPアドレスからRDS PostgreSQLに接続します。そのため、RDS PostgreSQLのセキュリティグループにこれらのIPアドレス全てを指定します。ノードIPアドレスのパブリックIPアドレスは、クラスタの一覧からクラスタ名を選択した後、[プロパティ]タブの「接続状態の詳細」を開くと参照できます。

VPCのCIDRを全て許可したり、Redshiftのセキュリティグループを指定してもダメでした。マニュアルにも記載がなく、私はこれで一日潰しました。

２．PostgreSQLへ接続するシークレット（認証情報）の作成

RedshiftからRDSまたはAurora PostgreSQLへ接続するには、Secrets Managerにシークレット（認証情報）を登録します。（登録方法は、機密管理サービス AWS Secrets Manager で RDS のパスワードローテーションを試すをご覧ください）

３．AWS Secrets Managerを利用できるIAMロールを作成

Secrets Managerを利用するためのRedshiftのIAMロール（Secrets Manager）を作成します。Resourceにシークレットのarnを指定しています。

{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "AccessSecret",
"Effect": "Allow",
"Action": [
"secretsmanager:GetResourcePolicy",
"secretsmanager:GetSecretValue",
"secretsmanager:DescribeSecret",
"secretsmanager:ListSecretVersionIds"
],
"Resource": "arn:aws:secretsmanager:us-east-1:123456789012:secret:tickit-zLrq7N"
},
{
"Sid": "VisualEditor1",
"Effect": "Allow",
"Action": [
"secretsmanager:GetRandomPassword",
"secretsmanager:ListSecrets"
],
"Resource": "*"
}
]
}

４．IAMロールをRedshiftに付与

上記のIAMロールをRedshiftに適用することで、シークレット（認証情報）を用いて、ターゲットとなるRDSまたはAurora PostgreSQLに接続できるようになります。

５．外部スキーマの作成

RedshiftからRDS PostgreSQLデータベースに接続するには、RedshiftにRDS PostgreSQLのスキーマに対する外部スキーマを作成します。

作成する外部スキーマ名：pg
接続するサービス：POSTGRES
接続先のデータベース：rdspg
接続先のスキーマ：tickit
接続先のURL：arn:aws:iam::123456789012:role/redshift-federated-query-role
シークレット（認証情報）のARN：arn:aws:secretsmanager:us-east-1:123456789012:secret:tickit-zLrq7N

cm-redshiftdb=# CREATE EXTERNAL SCHEMA pg
cm-redshiftdb-# FROM POSTGRES
cm-redshiftdb-# DATABASE 'rdspg'
cm-redshiftdb-# SCHEMA 'tickit'
cm-redshiftdb-# URI 'rdspg.ct6wtpooxxxx.us-east-1.rds.amazonaws.com'
cm-redshiftdb-# PORT 5432
cm-redshiftdb-# IAM_ROLE 'arn:aws:iam::123456789012:role/redshift-federated-query-role'
cm-redshiftdb-# SECRET_ARN 'arn:aws:secretsmanager:us-east-1:123456789012:secret:tickit-zLrq7N';
CREATE SCHEMA

cm-redshiftdb=# ALTER SCHEMA pg owner to tickit;
ALTER SCHEMA

作成した外部スキーマを確認します。外部スキーマpgのesoptionsに接続情報が設定されていることが確認できます。

cm-redshiftdb=# SELECT * FROM svv_external_schemas WHERE schemaname = 'pg';
-[ RECORD 1 ]+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
esoid | 2320282
eskind | 3
schemaname | pg
esowner | 100
databasename | rdspg
esoptions | {"SCHEMA":"tickit","URI":"rdspg.ct6wtpooxxxx.us-east-1.rds.amazonaws.com","PORT":5432,"IAM_ROLE":"arn:aws:iam::123456789012:role/redshift-federated-query-role","SECRET_ARN":"arn:aws:secretsmanager:us-east-1:123456789012:secret:tickit-zLrq7N"}

また、外部スキーマpgに接続して、参照できる外部テーブルを確認します。PostgreSQL上のテーブルが参照できたら接続完了です。

cm-redshiftdb=# SELECT * FROM svv_tables where table_type = 'EXTERNAL TABLE' AND table_schema = 'pg';
table_catalog | table_schema | table_name | table_type | remarks
---------------+--------------+------------+----------------+---------
cm-redshiftdb | pg | category | EXTERNAL TABLE |
cm-redshiftdb | pg | date | EXTERNAL TABLE |
cm-redshiftdb | pg | event | EXTERNAL TABLE |
cm-redshiftdb | pg | listing | EXTERNAL TABLE |
cm-redshiftdb | pg | sales | EXTERNAL TABLE |
cm-redshiftdb | pg | users | EXTERNAL TABLE |
cm-redshiftdb | pg | venue | EXTERNAL TABLE |
(7 rows)

Federated Queryの実行

RedshiftからRDS PostgreSQLのテーブルに対してクエリを実行します。以下のようにネストした分析クエリが実行できます。

cm-redshiftdb=> -- Find total sales on a given calendar date.
cm-redshiftdb=> SELECT sum(qtysold)
cm-redshiftdb-> FROM pg.sales, pg.date
cm-redshiftdb-> WHERE sales.dateid = date.dateid
cm-redshiftdb-> AND caldate = '2008-01-05';
sum
-----
210
(1 row)

cm-redshiftdb=> -- Find top 10 buyers by quantity.
cm-redshiftdb=> SELECT firstname, lastname, total_quantity
cm-redshiftdb-> FROM (SELECT buyerid, sum(qtysold) total_quantity
cm-redshiftdb(> FROM pg.sales
cm-redshiftdb(> GROUP BY buyerid
cm-redshiftdb(> ORDER BY total_quantity desc limit 10) Q, pg.users
cm-redshiftdb-> WHERE Q.buyerid = userid
cm-redshiftdb-> ORDER BY Q.total_quantity desc;
firstname | lastname | total_quantity
-----------+----------+----------------
Jerry | Nichols | 67
Kameko | Bowman | 64
Armando | Lopez | 64
Kellie | Savage | 63
Penelope | Merritt | 60
Rhona | Sweet | 60
Herrod | Sparks | 60
Deborah | Barber | 60
Belle | Foreman | 60
Kadeem | Blair | 60
(10 rows)

cm-redshiftdb=> -- Find events in the 99.9 percentile in terms of all time gross sales.
cm-redshiftdb=> SELECT eventname, total_price
cm-redshiftdb-> FROM (SELECT eventid, total_price, ntile(1000) over(order by total_price desc) as percentile
cm-redshiftdb(> FROM (SELECT eventid, sum(pricepaid) total_price
cm-redshiftdb(> FROM pg.sales
cm-redshiftdb(> GROUP BY eventid)) Q, pg.event E
cm-redshiftdb-> WHERE Q.eventid = E.eventid
cm-redshiftdb-> AND percentile = 1
cm-redshiftdb-> ORDER BY total_price desc;
eventname | total_price
----------------------+-------------
Adriana Lecouvreur | 51846.00
Janet Jackson | 51049.00
Phantom of the Opera | 50301.00
The Little Mermaid | 49956.00
Citizen Cope | 49823.00
Sevendust | 48020.00
Electra | 47883.00
Mary Poppins | 46780.00
Live | 46661.00
(9 rows)

もちろん、RedshiftのテーブルとPostgreSQLのテーブルを結合したクエリも実行できます。次のクエリでは、レコード数の多いRedshift上のsalesテーブルと、レコード数の少ないPostgreSQL上のpg.dateテーブルとpg.usersテーブルを結合して集計するクエリを実行します。

cm-redshiftdb=> -- Finds the top five sellers in San Diego, based on the number of tickets sold in 2008
cm-redshiftdb=> select sellerid, username, (firstname ||' '|| lastname) as name,
cm-redshiftdb-> city, sum(qtysold)
cm-redshiftdb-> from sales, pg.date, pg.users
cm-redshiftdb-> where sales.sellerid = users.userid
cm-redshiftdb-> and sales.dateid = date.dateid
cm-redshiftdb-> and year = 2008
cm-redshiftdb-> and city = 'San Diego'
cm-redshiftdb-> group by sellerid, username, name, city
cm-redshiftdb-> order by 5 desc
cm-redshiftdb-> limit 5;
sellerid | username | name | city | sum
----------+----------+-------------------+-----------+-----
49977 | JJK84WTE | Julie Hanson | San Diego | 22
19750 | AAS23BDR | Charity Zimmerman | San Diego | 21
29069 | SVL81MEQ | Axel Grant | San Diego | 17
43632 | VAG08HKW | Griffin Dodson | San Diego | 16
18888 | KMQ52NVN | Joan Wright | San Diego | 14
(5 rows)

データ型の対応と相違点

Amazon Redshiftデータ型からAmazon RDS PostgreSQLおよびAurora PostgreSQLに対応するデータ型は以下のとおりです。

Amazon Redshift のデータ型	RDS PostgreSQL または Aurora PostgreSQL のデータ型	説明
SMALLINT	SMALLINT	符号付き2バイト整数
INTEGER	INTEGER	符号付き4バイト整数
BIGINT	BIGINT	符号付き8バイト整数
DECIMAL	DECIMAL	精度の選択が可能な真数
REAL	REAL	単精度浮動小数点数
DOUBLE PRECISION	DOUBLE PRECISION	倍精度浮動小数点数
BOOLEAN	BOOLEAN	論理ブール演算型 (true/false)
CHAR	CHAR	固定長文字列
VARCHAR	VARCHAR	制限付き可変長文字列
DATE	DATE	Calendar date (year, month, day)
TIMESTAMP	TIMESTAMP	日時（タイムゾーンなし）
TIMESTAMPTZ	TIMESTAMPTZ	日時（タイムゾーンあり）
GEOMETRY	PostGIS GEOMETRY	空間データ

以下のRDS PostgreSQLおよびAurora PostgreSQLデータ型は、Amazon RedshiftでVARCHAR（64K）に変換されます。

JSON, JSONB
Arrays
BIT, BIT VARYING
BYTEA
Composite types
Date and time types INTERVAL and TIME
Enumerated types
Monetary types
Network address types
Numeric types SERIAL, BIGSERIAL, SMALLSERIAL, and MONEY
Object identifier types
pg_lsn type
Pseudo-types
Range types
Text search types
TXID_SNAPSHOT
UUID
XML type

最後に

これまでは、RDS/Aurora PostgreSQLからデータを取得するためにS3を介してRedshiftやRedshift Spectrumと連携する必要がありましたので、ジョブフローが複雑になりがちでした。しかし、今後は何も考えずにRDS/Auroraの PostgreSQLのデータをRedshiftで組み合わせて分析できるようになります。「DWH」とは、サイロ化されたデータを誰もがいつでも利用できるように一元的に統合・管理することが主目的です。Redshiftは、単に大規模データを低価格・高性能・伸縮自在に扱えるだけではなく、S3上のデータ（データレイク）や他のデータベースをアドホックに統合できる分析プラットフォームに正常進化したと言えるでしょう。さらにRDS/Aurora MySQLなどのサポートを期待しています。

[新機能] Amazon Redshift Federated QueryがGAになったので試してみました

Amazon Redshift の Federated Queryとは

Federated Queryの特長

RedshiftによるPostgreSQLのデータ統合

RDS/Aurora PostgreSQLによる並列分散

Federated Queryの導入

導入の流れ

０．導入環境

１．セキュリティグループの設定（落とし穴）

２．PostgreSQLへ接続するシークレット（認証情報）の作成

３．AWS Secrets Managerを利用できるIAMロールを作成

４．IAMロールをRedshiftに付与

５．外部スキーマの作成

Federated Queryの実行

データ型の対応と相違点

最後に

参考

合わせて読みたい

イベント

EVENT【5/29（水）大阪】Alteryxの使い方から導入メリットまですべてがわかるセミナー＆ワークショップ

EVENT【5/15（水）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/8リモート】クラスメソッドのフリーランスエンジニア会社説明会〜フィンテック / リテール業界案件特集〜を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

[新機能] Amazon Redshift Federated QueryがGAになったので試してみました

Amazon Redshift の Federated Queryとは

Federated Queryの特長

RedshiftによるPostgreSQLのデータ統合

RDS/Aurora PostgreSQLによる並列分散

Federated Queryの導入

導入の流れ

０．導入環境

１．セキュリティグループの設定（落とし穴）

２．PostgreSQLへ接続するシークレット（認証情報）の作成

３．AWS Secrets Managerを利用できるIAMロールを作成

４．IAMロールをRedshiftに付与

５．外部スキーマの作成

Federated Queryの実行

データ型の対応と相違点

最後に

参考

合わせて読みたい

イベント

EVENT【5/29（水）大阪】Alteryxの使い方から導入メリットまですべてがわかるセミナー＆ワークショップ

EVENT【5/15（水）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/8リモート】クラスメソッドのフリーランスエンジニア会社説明会 〜フィンテック / リテール 業界案件特集〜 を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金） 名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

関連記事

【CDI】Informatica Cloud Data Integrationで、ソースのスキーマを自動的に読み込めるという「スキーマ変更処理」を検証してみた

EVENT【5/8リモート】クラスメソッドのフリーランスエンジニア会社説明会〜フィンテック / リテール業界案件特集〜を開催します

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！