Amazon Athenaについてまとめてみた

この記事はうるるAdvent Calendar 2022の16日目の記事です。

はじめに

2022年4月にうるるに入社した新卒エンジニアです。
実務では基本Laravelでバックエンド周りの開発をメインで行なっており、最近ちょこちょこインフラ領域にも手を伸ばしています。

今回の記事は、Amazon Athenaについて簡単にまとめていきます。
以前手探りでAthenaによるデータ抽出を行なった際、初めて知った構文があり、今後業務で触る機会が増えそうなので、この機会にAthenaについてまとめました。
※間違っている記載があるかもしれませんが、ご了承ください

この記事の目標

• Amazon Athenaについて学ぶ
• Athenaとはなんぞや？となっている同士エンジニアの助けとなる(記憶を飛ばすであろう未来の自分も含みますw)

Amazon Athenaについて

Amazon Athena(以降、Athenaと表記)に関連する情報についてまとめていきます。

Athenaとは何か？

Athenaは、標準的なSQLを利用してS3のデータに対して直接クエリを実行できるサービスです。直接クエリが発行できるため、データをAthenaにロードしたりする必要はありません。
また、Athenaはサーバレスのサービスであるため、インフラやの設定や管理は不要となっています。料金は、実行するクエリに対してのみ発生します。さらに大規模なデータも、数秒~数十秒で結果が表示されます。
つまり、大量のデータセットを迅速かつ簡単に分析を可能にしてくれるサービスです。

Athenaの特徴

1.　サーバーレスで、インフラストラクチャや管理は不要

Athena はサーバーレスであるため、インフラストラクチャを管理する必要がありません。設定、ソフトウェアの更新、障害、またはデータセットやユーザー数の拡大によるインフラストラクチャのスケーリングについて心配する必要はありません。

2.　大規模データでも高速なデータ分析

Athenaはクエリエンジンとして、「Presto」を利用しています。Prestoは、低レイテンシーでインタラクティブなデータ分析用に最適化された、オープンソースの分散SQLクエリエンジンであり、クエリを並列実行するため、膨大なデータを高速に処理します。

3.　クエリごとの従量課金

SQLクエリで実行した際にスキャンしたデータのみが課金対象となります。
スキャンされたデータ 1 TB あたり 5USDとなっております。最低サイズは10MBと定義されており、10MB未満のクエリは,10MBとして計算されます。

4.　BIツールとの連携

Athenaでは、JDBCやODBCドライバーで接続されたBIツールやSQLクライアントで、データを探索することができます。AWS内のサービスでは、「Amazon QuickSight」と統合でき、データ分析だけでなくデータの可視化も簡単に行うことができます。
※JDBC：Java言語でリレーショナルデータベースにアクセスするための標準的なAPI

Athenaでサポートされているデータ型

Athenaでテーブル作成する際、列名と、各列に含めることができるデータ型は以下になります。

カテゴリ	データ型
論理値型	boolean
整数型	tinyint,smallint,int,integer,bigint ※CREATE TABLE のようなDDLクエリでは、int SELECT * FROMのようなDMLクエリでは、integer
浮動小数点型	double,float
固定小数点型	decimal(precision,scale) precision:最大値38, scale：最大値38
文字列型	char,varchar,string,binary
日付型	date,timestamp
配列型	array
マップ型	map
struct型	struct

AWS Glueとの統合

AWSには「AWS Glue」というサービスが存在します。AWS Glueとは、フルマネージド型のETLサービスです。
AWS Glueには、Glue CrawlerやGlue Data Catalogという機能が備わっています。Crawlerは、データソースを自動的にスキャンし、データ形式を特定してスキーマを推論し、Data Catalogにメタデータを保存します。Data CatalogはS3等のデータストアに保存されているデータベースとテーブルに関するメタデータを管理します。

※ELTサービス：「Extract（抽出）」「Transform（変換）」「Load（書き出し）」を行うサービス

AWSではAthenaを利用する際、Glueとの統合を推奨されています。(AWSにてベストプラクティスとして使用されている組み合わせ)。Athenaでは、Data Catalogを使用して、S3上のデータベースとテーブルに関するメタデータやスキーマーを保存し、クエリを実行します。

Athenaを操作する上で、AWS Glueと統合しなくてもS3に対してクエリを実行することは可能です。その場合、以下の3つのステップで行います。

1. S3バケットの作成
2. S3バケットを参照し、データベース定義とテーブル作成
3. クエリ実行

このようにAthenaのみでも、クエリの実行は可能です。しかし2の工程が手間がかかるため、膨大かつ多種のデータを分析する際は非効率となります。そこでAWS GlueのようなELTサービスとの組み合わせが推奨されております。
AWSのアーキテクチャ設計は様々な組み合わせを考慮するため、他にもあるとは思いますが、今回の記事では触れません。(というよりまだ私では知識不足ですw)。
今後はそういった設計にも携われるよう日々頑張ります。

以上でAthenaについてのまとめとさせていただきます。次はAthenaのSQL構文について記載します。

AthenaのSQL構文

ここでは、以前AthenaでSQLを書いた際に初めて知ったPresto構文について記載していきます。
完全に自分への備忘録内容になりますが、もし記事を見ていて新たな学びになった場合は幸いです。
※ちなみにこの後記載される構文は、MySQLの場合バージョン8以降に追加されたものであるため、8以前のバージョンでは使用不可となっております。

Prestoとは？

Prestoとは、オープンソースの分散型SQLクエリエンジンであり、あらゆるサイズのデータに高速な分析クエリを実行ができます。
AthenaではクエリエンジンにPrestoが使用されています。そのため、AthenaのSQL構文は、Prestoの構文でありSQLを書く際はPrestoのドキュメントを確認する必要があります。またPrestoクエリエンジンを介して実行したクエリの結果は、Amazon QuickSightデータセットに変換が可能となっています。

WITH句

WITH句はサブクエリ結果に名前をつけ、クエリ実行中のみ有効な一時テーブルを作成する機能です。WITH句を使ってサブクエリの代わりを果たすことができます。またWITH句にあるSQL文は、その後のselect文より先に実行されます。
WITH句の大きなメリットとして可読性の向上という点が挙げられます。
一例として、下記サンプルコードの二つを記載しました。これはどちらとも同じ結果を得られます。

サブクエリの場合、FROM内にSELECT分が入り、さらには内部結合で新たなテーブルと紐づけられ、かなり可読性は高いとは言えません。

サブクエリ

SELECT
    sub1.a
    ,sub1.b
    ,sub2.i
    ,sub2.j
FROM
    (SELECT
        a
        ,b
    FROM
     tbl1
    ) sub1
INNER JOIN
    (SELECT
        i
        ,j
    FROM
        tbl2
    ) sub2
ON
    sub1.a = sub2.i
;

一方でwith句を使用した場合、先に二つのテーブルを一時作成し、その後内部結合するため、サブクエリと比較してかなり可読性は向上しています。

with句

WITH sub1 AS (
    SELECT
        a
        ,b
    FROM
        tbl1
)
,sub2 AS (
    SELECT
        i
        ,j
    FROM
        tbl2
)
SELECT
    sub1.a
    ,sub1.b
    ,sub2.i
    ,sub2.j
FROM
    sub1
INNER JOIN
    sub2
ON
    sub1.a = sub2.i
;

便利なWITH句ですが、デメリットもあります。WITH句で作成した一時テーブルはメモリ上で保持されることになり、そのクエリのセッション中はずっと保持されます。そのため膨大なデータ量の一時テーブルの作成をした場合や一時テーブルの数が多い場合、メモリを圧迫する恐れもあります。

とはいえ、注意点に気をつけながら使用すればとても便利な構文です。

WINDOW関数

WINDOW関数は、結果行の集約を行うことなく集計・分析のための計算を行う事のできる関数。
例えば下記のようなデータがあったとします。そして集約関数SUM()を使って、ユーザーごとに集計をします。

user_id	score
1	50
2	60
3	70
1	40
2	30

GROUP BYによる集計

サンプル

SELECT
  user_id,
  SUM(score) AS total
FROM
  scores
GROUP BY
  user_id;

サンプルコードのように、user_idでグルーピング化した場合、下記のようにuser_idで結果行が集約されます。

user_id	total
1	90
2	90
3	70

WINDOW関数による集計

サンプル

SELECT
  user_id,
  score,
  SUM(score) OVER (PARTITION BY user_id) AS total
FROM
  scores
GROUP BY
  user_id;

サンプルコードのように、PARTITION BY 句内でuser_idでグルーピング化した場合、下記のようにuser_idごとに合計が集計されますが、結果行の集約を行うことなく集計が行えます。
ちなみに、PARTITION BY 句とは、どのカラムをグループとするか意味しています。

user_id	score	total
1	50	90
2	60	90
3	70	70
1	40	90
2	30	90

Prestoの全ての集約関数はその後ろにOVER()をつけることで、WINDOW関数として動作させることができます。OVER() の中には他のWINDOW 関数と同じように、PARTITION BY と ORDER BY を指定できます。
PARTITION BY と ORDER BY を指定するかどうかで、下記のように集計範囲が変化します。

• 何もしてしない場合：SELECTされた全レコード
• PARTITION BYを指定した場合：指定されたグループ内のレコード
• ORDER BYを指定した場合：ソートされた先頭行〜現在行

先ほどのデータにcategoryを追加したデータを用意します。そして集約関数SUM()を使って、集計していきます。

user_id	category	score
1	A	50
2	A	60
3	A	70
1	B	40
2	B	30

サンプル

SELECT
  user_id,
  category,
  score,
  SUM(score) OVER () AS total_1,
  SUM(score) OVER (PARTITION BY category) AS total_2,
  SUM(score) OVER (PARTITION BY category ORDER BY id) AS total_3,
FROM
  scores
GROUP BY
  user_id;

サンプルコードの結果は下記になります。OVER（）内の指定方法によって、様々な値をSELECTすることができます。

user_id	category	score	total_1	total_2	total_3
1	A	50	250	180	50
2	A	60	250	180	110
3	A	70	250	180	180
1	B	40	250	70	40
2	B	30	250	70	70

今回はSUM()のみの紹介でしたが、他の集約関数でも同様にデータの抽出が可能です。WINDOW関数によってデータ抽出の幅がかなり広がるため、かなり便利な機能だと思います。

最後に

読んでいただきありがとうございます。
なかなか普段長文を書く機会がないので、拙い記事となったかと思いますw

今回のquita記事作成を通じて、Athenaについて理解が深まりましたし、アウトプットの重要性を学んだ気がします。
まだ学ぶべきところはかなりありますが、早速機会があればAthenaでデータ抽出・分析を行い、さらなる理解を深めようと思います。

この記事を読んだ方の少しでも参考になって頂けたら嬉しいです。
改めて、読んでいただきありがとうございました。

明日は@t_komiyaさんの記事です！お楽しみに！