Lambda関数をコネクタとして、AthenaからAuroraへ接続してデータ取得ができるFederated Query（フェデレーテッドクエリ）なるものを使ってみます。
これを使ったきっかけは、アプリログをS3へ出力→Athena→QuickSightの王道KPI基盤を構築していたときに、アプリログには出力してなかった商品情報などをAuroraのマスタデータから引っ張って来る必要があったためでした。
僕が使い慣れてないせいもあるのですが、Glueを使用してのETLの一連の流れを設定するのは大変そうだったので、サクッとAuroraから直接データを取得できそうなフェデレーテッドクエリに飛びつきました。
前提
必要な関連リソース
あらかじめ必要な関連リソースを先にあげておきます。
Aurora
Auroraはプライベートサブネット (インターネットゲートウェイがアタッチされていないサブネット) に配置するものとします。（それが一般的な構成だと思うので）
SecretsManager
Auroraのuser,passwordは、SecretsManagerで管理します。
SecretsManagerのエンドポイント
コネクタとなるLambdaの環境変数に、Auroraへの接続情報を記載します。
この環境変数に平文でuser,passwordを記載することもできますが、セキュリティ上よろしくないのでSecretsManagerから取得します。
Lambdaからアクセスさせるためにエンドポイントが必要となります。
S3
Lambda 関数のレスポンスサイズ制限を超えるデータを保存する仕組みがあります。
（溢れはまだ未経験なのでどんな感じでデータが保存されるかはわかってないですが。。
そのためのバケットを用意します。
Athena FederatedQueryの構築手順
それでは設定していきます。
コネクタとなるLambdaを作成
Lambdaの初期設定
AWSコンソールにログイン後、 画面上部検索窓 [ Lambda ] で検索 → 当該サービスをクリック→ 画面左ペイン [ 関数 ] → 画面右上の [ 関数の作成 ] → [ Serverless Application Repository の参照 ] → [ カスタム IAM ロールまたはリソースポリシーを作成するアプリを表示する ] にチェックを入れ [ AthenaJdbcConnector ] で検索します。
表示された AthenaJdbcConnector をクリックし次の画面で各種設定を行います。
アプリケーションの設定
画面右下ペインの アプリケーションの設定 を入力します。
アプリケーション名以外の入力値は、作成されるLambdaやロールを直接編集することで、あとから変更可能です。
SecretNamePrefix
Auroraのパスワードを保存しているSecretManagerのprefixを指定します。
Lambdaアプリケーション作成時にIAMロールも同時に作成されるのですが、SecretManagerに関して以下のようなポリシーがアタッチされます。
{
"Version": "2012-10-17",
"Statement": [
{
"Action": [
"secretsmanager:GetSecretValue"
],
"Resource": "arn:aws:secretsmanager:ap-northeast-1:AWSアカウントID:secret:prefix入力値*",
"Effect": "Allow"
}
]
}DefaultConnectionString
デフォルトの接続情報を設定します。
## 構文
mysql://jdbc:mysql://auroraエンドポイント/DB名?${シークレット名}
## 例
mysql://jdbc:mysql://zoo200-aurora.cluster-ro-xxxxxxxxx.ap-northeast-1.rds.amazonaws.com/test_db?${/rds/zoo200/master-password}
## セキュリティ上よろしくはないですが、以下のように平文直書きもできます。
mysql://jdbc:mysql://zoo200-aurora.cluster-ro-xxxxxxxxx.ap-northeast-1.rds.amazonaws.com/test_db?user=ユーザー名&password=パスワード
この値はAthenaで設定するデータカタログ名が “default” の場合に使用される設定と思われます。
個人的には データカタログ名が “default” だと紛らわしいので、この設定は形式的に設定するものとして、実際に使用する接続情報は後述で追加する設定を利用するほうが良いと思います。
なお実際に使用しないとはいえ、適当な文字列だったりするとAthena側で連携するときに構文エラーとなってしまうので構文に沿った形で記載する必要があります。
なお、この設定の仕様はドキュメントから読み取れなかったのですが、こちらのソースが以下の分岐をしており、実際に試してみたら上記の挙動をした形になります。
...
public static final String DEFAULT_CONNECTION_STRING_PROPERTY = "default";
...
if (DEFAULT_CONNECTION_STRING_PROPERTY.equals(key.toLowerCase())) {
...（僕の理解が間違ってたらtwitterで、ご指摘いただけると助かりますmm）
SecurityGroupIds
auroraに接続できるセキュリティグループIDを直接入力します。以下のようにカンマで区切れます。
sg-abc,sg-xyz
SubnetIds
auroraに接続できるサブネットIDを直接入力します。以下のようにカンマで区切れます。
subnet-abc,subnet-xyz
Lambdaのデプロイ
上記を入力したら、 [ このアプリがカスタム IAM ロールを作成することを承認します。 ] にチェックを入れ [ デプロイ ] を押下します。
次の画面で、生成されるリソースのLambda関数とIAMロールへのリンクや、それらのデプロイの進捗が確認できます。
Create complete になるまで待ちましょう。
Lambdaコネクタへの設定追加
デプロイが完了したらLambdaの環境変数にコネクタ設定を追加します。
当該関数の環境変数に以下を設定します。
キー
Athenaカタログ名_connection_string
値
mysql://jdbc:mysql://auroraエンドポイント/DB名?${シークレット名}
（DefaultConnectionString に設定したものと同じ内容）
以下スクショでは、zoo200_aurora_connection_string というキーで設定しています。
Athenaでコネクタを設定
Lambdaの準備が終わったので、次にAthena側で設定をします。
画面上部検索窓 [ Athena ] で検索 → 当該サービスをクリック → [ Query editor ] タブ か [ Data sources ]タブの [ Connect data source ] をクリックします。
次の画面で [ Query a data source ] と [ MySQL ] にチェックを入れ次へ
次の画面で、[ Lambda function ] に先程作成したLambda関数名を、[ Catalog name ] に適当な名前を設定します。
ちなみに、Lambdaの環境変数は、「キーは文字で始まる必要があり、文字、数字、アンダースコアのみを使用できます。」という制約があるため、こちらのコネクション名で、ハイフンなど使用するとLambdaの環境変数で設定できないというジレンマに陥ります。。
フェデレーテッドクエリの確認
設定が完了したので、接続、データ取得の確認をします。
Athenaの [ Query editor ] タブ → [ Data source ] プルダウン選択肢に先程追加したコネクションが表示されるので、それを選択すると [ Database ] もAuroraのものが選択できるようになります。
以降は、通常のAthenaと同じように扱えます。
うまく行かない場合
コネクタ用のLambda関数で問題が発生している場合は、CloudWatchLogsでも確認できます。
Athena側に何もエラーメッセージが出力されない場合、[ CloudWatch ] → [ Log groups ] → [ /aws/lambda/関数名 ]のログをチェックしてみましょう。
例えば以下はSecrets Manager用のエンドポイントが存在しない場合に出力されるログです。
START RequestId: aaa-bbb-ccc-ddd Version: $LATEST
...
2021-07-16 13:36:35 aaa-bbb-ccc-ddd INFO JdbcMetadataHandler:119 - Using Secrets Manager.
2021-07-16 13:38:36 aaa-bbb-ccc-ddd WARN CompositeHandler:104 - handleRequest: Completed with an exception.
com.amazonaws.SdkClientException: Unable to execute HTTP request: Connect to secretsmanager.ap-northeast-1.amazonaws.com:443 [secretsmanager.ap-northeast-1.amazonaws.com/11.22.33.44, secretsmanager.ap-northeast-1.amazonaws.com/11.22.33.44, secretsmanager.ap-northeast-1.amazonaws.com/11.22.33.44] failed: connect timed out
...Athenaの他のデータとJOINする
Athenaは以下のようにデータソース名からピリオドでつなげることが可能です。
SELECT * FROM データソース名.データベース名.テーブル名
-- 例
SELECT * FROM zoo200_aurora.test_db.test_tbl
なので、以下のようにAthenaでTBL定義済みのS3データとJOINすることが可能です。
WITH
-- auroraを元に定義したAthenaテーブル
aurora AS (SELECT id,name FROM zoo200_aurora.test_db.test_tbl),
-- s3を元に定義したAthenaテーブル
s3 AS (SELECT id,score FROM AwsDataCatalog.s3.s3_tbl)
SELECT
aurora.id,aurora.name,s3.score
FROM
aurora ,s3
WHERE
aurora.id=s3.id
-- 結果
id name score
1 aaa 100
2 bbb 200がんばってViewとして使う
悲しいことにフェデレーテッドクエリで連携したデータは、そのままではViewとして使用できません。
以下のような前述のJOIN例を、そのままViewに利用しようとすると「 extraneous input ‘.’ 」とエラーになります。
-- CREATE OR REPLACE VIEW の一文を追加
CREATE OR REPLACE VIEW AwsDataCatalog.s3.aurora_view AS
WITH
-- auroraを元に定義したAthenaテーブル
aurora AS (SELECT id,name FROM zoo200_aurora.test_db.test_tbl),
-- s3を元に定義したAthenaテーブル
s3 AS (SELECT id,score FROM AwsDataCatalog.s3.s3_tbl)
SELECT
aurora.id,aurora.name,s3.score
FROM
aurora ,s3
WHERE
aurora.id=s3.id
AuroraのデータをViewで使用したい場合は、一度CTAS（CREATE TABLE AS SELECT）などでAuroraからAthenaのテーブルに変換（コピー）する必要があります。
CREATE TABLE AwsDataCatalog.s3.aurora_parquet
WITH
(
external_location = 's3://zoo200-test/aurora/parquet/',
format = 'Parquet',
parquet_compression = 'SNAPPY'
)
AS
SELECT id,name FROM zoo200_aurora.test_db.test_tbl
その後、変換されたAthenaのテーブルを元にViewを作成できます。
CREATE OR REPLACE VIEW AwsDataCatalog.s3.aurora_view AS
WITH
-- auroraからs3に変換したAthenaテーブル
aurora AS (SELECT id,name FROM AwsDataCatalog.s3.aurora_parquet),
-- s3を元に定義したAthenaテーブル
s3 AS (SELECT id,score FROM AwsDataCatalog.s3.s3_tbl)
SELECT
aurora.id,aurora.name,s3.score
FROM
aurora ,s3
WHERE
aurora.id=s3.id
-- Viewに対してSELECT
SELECT * FROM AwsDataCatalog.s3.aurora_view
-- 結果
id name score
1 aaa 100
2 bbb 200
以上、基本的な設定、操作方法でした。
Tips
ちょっとした注意点や気になったことをメモしておきます。
Readのみ可能
そもそも用途的にSELECT以外使わないと思いますが、SQLで使用できるINSERT，UPDATE，DELETEの更新系のDMLは使用できません。
なおAthenaクエリにはINSERTがありますが実行すると「This operation is currently not supported for external catalogs. 」とエラーになります。（コネクション設定にライトエンドポイントを指定しても）
Athena操作時にAurora側に発行されるクエリ
Athenaの操作時にAurora側でどのようなクエリが発行されるか確認してみました。
なおAuroraの自動＆定期的に発行されるシステムクエリのようなものが邪魔だったので、CloudWatchLogsのフィルタで以下文言を除外しました。
-"@@" -oscar_local_only_replica_host_status -rds_heartbeat2 -COMMIT -information_schema -INFORMATION_SCHEMA -"PURGE BINARY" -aurora_enable_global_diag -rds_replication_status -rds_configuration -rds_history -"Query SET" -"Query FLUSH"Data soureを切替後、Databaseにmysqlと表示されたとき
[ Data soure ] を切り替えて [ Database ] にシステムテーブルである mysqlとデフォルトで表示されたときは、以下のクエリが流れました。
2021-07-18T08:36:22.589920Z 23 Connect admin@10.1.3.7 on test_db using SSL/TLS
2021-07-18T08:36:22.596160Z 23 Query SHOW WARNINGS
2021-07-18T08:36:22.598111Z 23 Query SHOW DATABASES
2021-07-18T08:36:22.606061Z 23 Quit
2021-07-18T08:36:22.944910Z 24 Connect admin@10.1.3.7 on test_db using SSL/TLS
2021-07-18T08:36:22.953550Z 24 Query SHOW WARNINGS
2021-07-18T08:36:22.956002Z 24 Query SHOW DATABASES LIKE 'mysql'
2021-07-18T08:36:22.957618Z 24 Query SHOW FULL TABLES FROM `mysql`
2021-07-18T08:36:23.061154Z 24 Quit Databaseを自分で作成したDBに切替後、所持TBL一覧が表示されたとき
[ Database ] を test_db（自分で作成したDB）に切り替えて所持TBLの test_tbl と test_tbl_2 が一覧で表示されたときは、以下のクエリが流れました。
2021-07-18T08:42:42.880819Z 31 Connect admin@10.1.3.7 on test_db using SSL/TLS
2021-07-18T08:42:42.907097Z 31 Query SHOW WARNINGS
2021-07-18T08:42:42.909066Z 31 Query SHOW DATABASES LIKE 'test\\_db'
2021-07-18T08:42:42.911280Z 31 Query SHOW FULL TABLES FROM `test_db`
2021-07-18T08:42:42.913719Z 31 Quit
2021-07-18T08:42:43.017660Z 32 Connect admin@10.1.3.7 on test_db using SSL/TLS
2021-07-18T08:42:43.023474Z 32 Query SHOW WARNINGS
2021-07-18T08:42:43.109741Z 32 Query SHOW DATABASES LIKE 'test\\_db'
2021-07-18T08:42:43.111429Z 32 Query SHOW DATABASES LIKE 'test_db'
2021-07-18T08:42:43.112787Z 32 Query SHOW FULL TABLES FROM `test_db` LIKE 'test\_tbl'
2021-07-18T08:42:43.114241Z 32 Query SHOW FULL COLUMNS FROM `test_tbl` FROM `test_db`
2021-07-18T08:42:43.128952Z 32 Quit
2021-07-18T08:42:43.211736Z 33 Connect admin@10.1.3.7 on test_db using SSL/TLS
2021-07-18T08:42:43.216256Z 33 Query SHOW WARNINGS
2021-07-18T08:42:43.217377Z 33 Query SHOW DATABASES LIKE 'test\\_db'
2021-07-18T08:42:43.218937Z 33 Query SHOW DATABASES LIKE 'test_db'
2021-07-18T08:42:43.220255Z 33 Query SHOW FULL TABLES FROM `test_db` LIKE 'test\_tbl\_2'
2021-07-18T08:42:43.221422Z 33 Query SHOW FULL COLUMNS FROM `test_tbl_2` FROM `test_db`
2021-07-18T08:42:43.223079Z 33 Quit
ただ、すぐに別DBに切り替えて、またすぐに test_db に切り戻す（一覧表示の際のぐるぐるアイコンが表示されない）場合は、データがDBキャッシュにのっているためか何もクエリは発行されませんでした。
select * from test_tbl を実行したとき
select * from test_tbl を実行したときは、以下のクエリが流れました。
2021-07-18T08:49:48.468967Z 161 Connect admin@10.1.3.7 on test_db using SSL/TLS
2021-07-18T08:49:48.474934Z 161 Query SHOW WARNINGS
2021-07-18T08:49:48.475949Z 161 Query SHOW DATABASES LIKE 'test\\_db'
2021-07-18T08:49:48.477410Z 161 Query SHOW DATABASES LIKE 'test_db'
2021-07-18T08:49:48.478557Z 161 Query SHOW FULL TABLES FROM `test_db` LIKE 'test\_tbl'
2021-07-18T08:49:48.479669Z 161 Query SHOW FULL COLUMNS FROM `test_tbl` FROM `test_db`
2021-07-18T08:49:48.481059Z 161 Quit
2021-07-18T08:49:48.604657Z 162 Connect admin@10.1.3.7 on test_db using SSL/TLS
2021-07-18T08:49:48.610054Z 162 Query SHOW WARNINGS
2021-07-18T08:49:48.666343Z 162 Quit
2021-07-18T08:49:48.781611Z 163 Connect admin@10.1.3.7 on test_db using SSL/TLS
2021-07-18T08:49:48.791230Z 163 Query SHOW WARNINGS
2021-07-18T08:49:48.794558Z 163 Quit
2021-07-18T08:49:50.346253Z 164 Connect admin@10.1.3.7 on test_db using SSL/TLS
2021-07-18T08:49:50.362304Z 164 Query SHOW WARNINGS
2021-07-18T08:49:50.374406Z 164 Query SELECT `id`, `name` FROM `test_db`.`test_tbl`
2021-07-18T08:49:50.406126Z 164 Query commit
2021-07-18T08:49:50.408682Z 164 Query rollback
2021-07-18T08:49:50.410378Z 164 Quit select name from test_tbl を実行したとき
select name from test_tbl を実行したときは、以下のクエリが流れました。
2021-07-18T08:52:47.631833Z 169 Connect admin@10.1.3.7 on test_db using SSL/TLS
2021-07-18T08:52:47.638389Z 169 Query SHOW WARNINGS
2021-07-18T08:52:47.639383Z 169 Query SHOW DATABASES LIKE 'test\\_db'
2021-07-18T08:52:47.640772Z 169 Query SHOW DATABASES LIKE 'test_db'
2021-07-18T08:52:47.642357Z 169 Query SHOW FULL TABLES FROM `test_db` LIKE 'test\_tbl'
2021-07-18T08:52:47.644810Z 169 Query SHOW FULL COLUMNS FROM `test_tbl` FROM `test_db`
2021-07-18T08:52:47.646145Z 169 Quit
2021-07-18T08:52:47.728699Z 170 Connect admin@10.1.3.7 on test_db using SSL/TLS
2021-07-18T08:52:47.734488Z 170 Query SHOW WARNINGS
2021-07-18T08:52:47.737364Z 170 Quit
2021-07-18T08:52:47.823192Z 171 Connect admin@10.1.3.7 on test_db using SSL/TLS
2021-07-18T08:52:47.829448Z 171 Query SHOW WARNINGS
2021-07-18T08:52:47.833076Z 171 Quit
2021-07-18T08:52:49.294509Z 172 Connect admin@10.1.3.7 on test_db using SSL/TLS
2021-07-18T08:52:49.301694Z 172 Query SHOW WARNINGS
2021-07-18T08:52:49.305617Z 172 Query SELECT `name` FROM `test_db`.`test_tbl`
2021-07-18T08:52:49.307719Z 172 Query commit
2021-07-18T08:52:49.309466Z 172 Query rollback
2021-07-18T08:52:49.310293Z 172 Quit なお、INSERT はAthenaでエラーとなるので、何もクエリは発行されていませんでした。
今回は以上です〜ﾉｼ
参考
(´･ω･｀)ゞｱﾘｶﾞﾄｺﾞｻﾞｲﾏｽ.｡.･ﾟ
Amazon Athenaの新しいフェデレーテッド・クエリによる複数データソースの検索
Amazon Athena 横串検索の使用
GitHub Amazon Athena Lambda Jdbc Connector
