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

この記事は[うるるAdvent Calendar 2022の16日目の記事](https://qiita.com/advent-calendar/2022/uluru)です。

# はじめに
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<br>※CREATE TABLE のようなDDLクエリでは、int<br>SELECT * FROMのようなDMLクエリでは、integer|
|浮動小数点型|double,float|
|固定小数点型|decimal(precision,scale)<br>precision:最大値38, scale：最大値38|
|文字列型|char,varchar,string,binary|
|日付型|date,timestamp|
|配列型|array<data_type>|
|マップ型|map<primitive_type,data_type>|
|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上のデータベースとテーブルに関するメタデータやスキーマーを保存し、クエリを実行します。

![スクリーンショット 2022-12-17 2.03.33.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2953307/011cf4d2-8e2e-5c60-b900-547c8d4c4787.png)
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分が入り、さらには内部結合で新たなテーブルと紐づけられ、かなり可読性は高いとは言えません。
```sql:サブクエリ
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句を使用した場合、先に二つのテーブルを一時作成し、その後内部結合するため、サブクエリと比較してかなり可読性は向上しています。
```sql: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による集計

```sql:サンプル
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関数による集計

```sql:サンプル
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|

```sql:サンプル
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さんの記事です！お楽しみに！