LoginSignup
12
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

Day 11

BeeX Advent Calendar 2021

@Shmwa2(Shohei Miwa)in株式会社BeeX

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

Last updated at Posted at 2021-12-11

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

Athenaの構成

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

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

image.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

Step1

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

image.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

注意点としてS3へデータをエクスポートする際は、自己管理型のKMSキーが必須となります。
image.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

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

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

image.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というパッケージを使用してデプロイします。

デプロイする際には、環境変数を入力する必要があります。
image.png

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

なお、こちらで 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

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

image.png

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

image.png

上記が完了し、エディタ画面に戻ると、新しく追加されたデータソースが確認出来ます。
RDSに接続されたデータベースに対してクエリを実行すると、正常に14件のデータが返ってきます。
image.png

Athena Fedareted Queryの特性

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

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

image.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;

作成したビューは問題なく読み取りが可能でした。
image.png

Step4

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

image.png

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

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

image.png

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

image.png

image.png

image.png

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

以上となります。

12
2
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
12
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?