【AWS】Amazon Athena Federated Query を使用してQuickSightまでデータ分析基盤を検証構築してみた

```Amazon Athena Federated Query```は 2020年12月に東京リージョンで使用可能となりました。データソースに対してクエリを実行出来るという事なので、どこまで出来るか検証をしてみました。

Athenaの構成
=============
Athenaはデータの分析用途に使用出来るインタラクティブなクエリサービスです。
これまでは、S3のデータを```AWS Glue Datacatalog```から論理テーブルを作成しクエリを実行出来ましたが、Federated QueryがGAされた事で、様々なデータソースから直接クエリを実行できるようになりました。
***サーバーレスな構成を組めるため、分析ワークロードにおいて、使いやすいサービスかと思います。***

Athena のサービス構成を見ていきます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1597898/7a2765f5-fbbd-5fb9-8a40-bb56ee11d395.png)

- Athenaは```リージョン単位```で構成されます。
- Athena上に実データは存在せず、S3のデータおよびDataCatalogで作成されたスキーマ、あるいはその他データソースとなるデータベースに実データが存在します。つまり、Athenaは後述する論理グループの生成とクエリエンジンとしての機能を持ちます。
- Athena は```PowerBI```、```Tableau```、```Domo```、```QuickSight```などBIのコネクタを利用して連携が出来ます。
- クエリエンジンは現在```Presto0.217```をベースとした```Amazon Athena Engine Version2```を使用出来ます。
- クエリ結果は```S3```に保存されます。
- Athenaによって作成される論理グループは以下3つとなります。

   - ```データベース``` - Athenaで作成するテーブルをまとめた論理的なグループです。
   - ```テーブル``` - Data Catalogに保存します。
   - ```ビュー``` - ビューもテーブル同様、論理的なテーブルとなります。
 
作成されたテーブルは、クエリの実行時に使用します。
上述したように、Athenaにおけるデータベースとテーブルは、データと共に保存されません。Athenaは```schema-on-read```のクエリエンジンのため、Athenaがデータの読み取り時にスキーマを適用することを意味します。
***また、基盤となるデータに変更または書き直しが行われることはありません。***

データ分析基盤の構築検証
===========
今回は S3のデータとRDSのデータを結合した```VIEW```を作成し、QuickSightでリアルタイムな検索結果が得られるか簡易的に環境を構築してみました。

全体のイメージです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1597898/5176ba90-9b32-ae62-79cc-f5c81d7dd260.png)

Step1
------
```Data Source```の準備をします。
赤枠の部分が対象となります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1597898/b51bc398-cfd5-16ac-c66c-c1b15fa89bd3.png)

```Aurora Postgresql``` を作成し、EC2に```psqlクライアント```を導入した後、データを作成します。以下のように3列14行のテーブルを作成しました。

```
postgres=> select * from mytable;
    date    |  name  | complement
------------+--------+------------
 2021-12-08 | yoko   | manage
 2021-12-02 | yoko   | manage
 2021-12-05 | ryo    | manage
 2021-12-02 | ryo    | tester
 2021-12-05 | ryo    | tester
 2021-12-08 | yuji   | tester
 2021-12-01 | shohei | tester
 2021-12-08 | shohei | manage
 2021-12-08 | risa   | tester
 2021-12-02 | john   | tester
 2021-12-08 | john   | tester
 2021-12-02 | john   | tester
 2021-12-04 | john   | tester
 2021-12-06 | john   | tester
(14 rows)
```

この状態でRDS のマネージドサービスの```手動スナップショット```を取得します。
取得したスナップショットを、S3へエクスポートします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1597898/9597b606-0236-090f-179d-9d0e3b64cd7f.png)

***注意点としてS3へデータをエクスポートする際は、```自己管理型のKMSキー```が必須となります。***
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1597898/5597dd57-bb49-c4bd-fff1-bad1332e0b14.png)

エクスポートが完了すると、今度はAurora Postgresqlに戻ります。
先ほど作成した***テーブルのカラムを削除し、新規で別のカラムを1列追加***します。

```
ALTER TABLE mytable DROP COLUMN name;
ALTER TABLE mytable DROP COLUMN date;
ALTER TABLE mytable DROP COLUMN complement;
postgres=> select * from mytable;
--
(14 rows)

ALTER TABLE mytable ADD COLUMN motivation int;
DELETE FROM mytable Where motivation IS NULL;
INSERT INTO mytable VALUES.....
```
先ほど同様に14レコード分の値を入れて以下のような状態を作りました。

```
postgres=> select * from mytable;
 motivation
------------
         80
         50
        100
         20
        120
         20
         50
         80
        120
         50
        100
         80
        100
         20
(14 rows)
```

これでS3とRDS、２つのデータソースを作成しました。

Step2
------
Athena へ連携する Data Catalog の準備と、Athena Federated Query を実装するために必要な ```JDBC Driver```を```Lambda```で実装します。
赤枠の部分が対象となります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1597898/411676e7-0d8f-9239-3db6-0445549d7862.png)

先ほどS3にエクスポートした```Parquet形式```のデータを```cralwerの実行```により、テーブル作成していきます。
この作業においてポイントとなる点は、以下となります。

- ***S3にエクスポートしたバケットのパスを指定する事*** 
- ***サービスロールに必ず```KMSのDecrypt権限```を許可する事***

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1597898/1480debc-33df-253c-57ba-700a6cbad8fc.png)


``` kms-decrypt.json
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": "kms:Decrypt",
            "Resource": "*"
        }
    ]
}
```

一方、RDSは Federated Query を使用するため、JDBC接続をするLambdaを実装する必要があります。
ここでは、```AWS Serverless Application Repository```の```AthenaJdbcConnector```というパッケージを使用してデプロイします。

https://github.com/awslabs/aws-athena-query-federation

デプロイする際には、環境変数を入力する必要があります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1597898/4f46cc7c-75d2-b6dd-07a9-0bc714adedab.png)

どの項目に何を入れるかは、```README```に記載がありますので、こちらをご参考にいただければ使用出来るかと思います。

https://github.com/awslabs/aws-athena-query-federation/blob/master/athena-jdbc/README.md

なお、こちらで Postgresqlのユーザー情報を登録した```Secrets Manager```を使用します。
```SecretNamePrefix```にはSecret Managerの```arn```を入力します。
```DefaultConnectionString```には ```${Test/RDS/PostGres1}``` といったようなSecretManagerの変数を入れる事も可能ですが、```UserName/password``` を直入力しても問題はありません。

また、データの```スピル```が発生するために任意のbucketの指定が必要となります。
```VPC Lambda```を使用する場合は、```S3エンドポイント```、またはLambdaのサービスロールにS3のアクセス権限が必要となります。

Lambda の作成が完了したらAthenaへの繋ぎ込みの準備が完了しました。

Step3
------
Athenaでクエリを発行する準備が出来たので、Athena のコンソールを見ていきます。
赤枠の部分が対象となります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1597898/5c7dc76d-635f-73e4-b2c3-9066dcaf4ef0.png)

Crawlerから生成されたテーブルはAthenaから問題なく確認が出来ます。
クエリを実行した際も 14件でデータが返ってきます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1597898/6c857f89-3aee-7df8-e2ab-4f2025100876.png)

続いて、RDS から Federated Query を実行するためにデータソースの接続設定を実施します。
Athena のコンソールからデータソースの種別を選択し、先ほど作成した Lambda 関数を設定します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1597898/0458e83c-810d-1f89-37ae-1f6add05feb4.png)

上記が完了し、エディタ画面に戻ると、新しく追加されたデータソースが確認出来ます。
RDSに接続されたデータベースに対してクエリを実行すると、正常に14件のデータが返ってきます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1597898/77327551-be04-3f1a-ff18-fade50546106.png)

Athena Fedareted Queryの特性
---------------------------
ここからが重要ですが、検証の結果、SelectでS3のデータソースとRDSのデータソースをJOINした結果を返す事は可能です。
***しかし、VIEW の作成については、データソースを元テーブルとして使用する事が出来ません。つまりAthena 上に一度テーブルをコピー(CTAS)する必要がありました。*** 

元テーブルに対してVIEWを作成する事が可能であれば、様々なデータソースに対してデータマートのように専用のVIEWを作成し、Athenaのクエリを使用して、QuickSightへリアルタイムな反映をする事が可能であったかと考えていましたが、CTASで一時テーブルを作成する必要があるようです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1597898/da049a4c-4082-de1c-69e3-c8ed339f3150.png)

RDS用のテーブルをAthena上に作成します。

``` ctas.sql:
CREATE TABLE rdspostgresql AS SELECT * FROM postgresql.public.mytable;
```

Pestro ベースにVIEWを作成

``` createview.sql:
CREATE OR REPLACE VIEW AwsDataCatalog.rdss3exportdata.viewtable AS WITH postgres AS (SELECT motivation FROM postgresql.public.rdspostgresql), rdsexport AS (SELECT date,name,complement FROM AwsDataCatalog.rdss3exportdata.postgres) SELECT * FROM postgres,rdsexport;
```

https://prestodb.io/docs/current/sql/create-view.html

作成したビューは問題なく読み取りが可能でした。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1597898/ded22b9a-bfe2-b794-a517-e07a217450a4.png)


Step4
------
最後に ```QuickSight``` から表示します。
赤枠が対象の部分となります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1597898/499c0c95-16c7-0c73-2dd7-886109e77aa4.png)

***QuickSight から先ほどのデータを正しく表示するためには、事前にQuickSight のサービスロールに対して、データソースとAthenaに関連するS3バケットのアクセス権限、Lambdaの実行権限を付与します。***

権限の設定後、新しいデータセットからAthenaを選択し、先ほど作成したViewを選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1597898/79c4d6ce-c05b-0131-0549-bba8e9b8e242.png)

データセットの作成が完了すると、表示したい形式に合わせてビジュアルタイプを変更します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1597898/39a51da2-0912-b023-387c-17028dfe18b7.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1597898/133b6289-2c7b-24e4-98ce-93d66d0bff9b.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1597898/771fa69f-49ad-4754-7bfc-0d483b616f1f.png)

表示に関しては、問題なさそうでした。

以上となります。