Athena

Athena FederatedQueryでAuroraと連携する

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 関数のレスポンスサイズ制限を超えるデータを保存する仕組みがあります。
(溢れはまだ未経験なのでどんな感じでデータが保存されるかはわかってないですが。。

そのためのバケットを用意します。

手順

それでは設定していきます。

コネクタとなる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側で連携するときに構文エラーとなってしまうので構文に沿った形で記載する必要があります。


ちなみにこの設定の仕様ですが、僕はドキュメントから読み取れなかったです。。
(僕の理解が間違ってたらtwitterや問い合わせでご指摘いただけると助かりますmm

こちらのソースが以下のような分岐で、実際に試してみたら上記のような挙動でした。

if (DEFAULT_CONNECTION_STRING_PROPERTY.equals(key.toLowerCase())) {


【 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


以上、基本的な設定、操作方法でした。

補足

ちょっとした注意点や気になったことメモです。

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がデフォルトで表示されたとき。

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 ] を test_db(自分で作成したもの)に切り替えて所持TBLの test_tbl と test_tbl_2 が一覧で表示されたとき。
ただ、すぐ別DB→またtest_dbのようにすぐ切り戻す(一覧表示の際のぐるぐるアイコンが表示されない)場合は、キャッシュにのってるのか何もクエリは発行されず。

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	


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 を実行

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

関連書籍