Amazon Athenaを始めるときに知っておきたいこと

はじめに

株式会社ipocaでAthenaを扱ってきて、最初から知っておけばより早く、より効率的にやれたなと思うところをまとめました。なるべく公式ドキュメントを参照していますが、間違っている部分もあるかと思いますので、最後はドキュメントを確認していただきたいです。また間違った点は指摘いただけると幸いです。
本内容は2020年7月1日現在の内容となっています。

Athenaとは？

Amazon Athena とは

Amazon Athena は、標準 SQL を使用して Amazon Simple Storage Service (Amazon S3) でのデータの直接分析を簡易化するインタラクティブなクエリサービスです。AWS マネジメントコンソールでいくつかアクションを実行するだけで、Athena にデータの保存先の Amazon S3 を設定し、標準 SQL を使用してアドホッククエリの実行を開始できます。結果は数秒で返ります。

とありますが、わかりやすく言うと手持ちのCSVファイルなどをS3バケットにおいておくだけでそのままDBのように扱えるようになるサービスです。DBと言ってもデータの取り出しのみ（select）で、insertやupdateは扱えません。
速度もインスタンスタイプにもよりますがRDSで組み込むよりも圧倒的に早く結果を得ることが可能となります。結果は数秒で返りますとありますが、これはもちろんデータ量に依存するので数秒で帰らないことは多々あります。また逆に言えば必ず数秒はかかることとなります。速度を求めるのであればRedShiftの導入のほうが良さそうです。

料金は後述しますが、従量課金で費用も抑えられます。使ってないときはS3の料金のみとなるので、利用方法によりますが、初めて使う人にも費用は優しいものと思われます。

料金

Amazon Athena の料金

スキャンされたデータ 1 TB あたり 5.00USD

Athenaに関しては上記費用です。10 MB 未満のクエリは 10 MB と計算されるため、最低でも0.00005USD分はかかってしまう想定です。1円もかからないのでよっぽどのことがない限り無視できるかと思います。
またこれとは別にデータをS3に置いているのでその費用はかかってきます。単純なストレージ料金とリクエスト料金がかかります。これは公式の情報ではありませんが、1クエリに対して1ファイル2~3アクセスあるように見えました。これも基本的には誤差の範囲ですが、大規模なサービスになる場合は留意しておく必要があります。

弊社は当初別室テムで細切れに作成された数KBの数万以上のファイルを読み込んでいましたが、S3の費用が圧倒的にかかってしまい、対処が必要となりました。
後述するファイルサイズの最適化で詳しくのべます。

SQL

Amazon Athena の SQL リファレンス

Amazon Athena では、データ定義言語 (DDL) とデータ操作言語 (DML) のステートメント、関数、演算子、およびデータ型のサブセットがサポートされています。いくつかの例外を除き、Athena DDL は HiveQL DDL に基づいており、Athena DML は Presto 0.172 に基づいています。

とあるようにSQLを書くときはPrestoの構文で書きます。普段MySQLを触っている自分としては特に変わった所はないように感じましたが、ちょっとした関数の違いなどがあるので都度ドキュメントを参考にするといいと思います。

ファイル形式

サポートされる SerDes およびデータ形式

Athena でテーブルの作成とデータのクエリをサポートしているのは、CSV、TSV、カスタム区切り、JSON の各形式のデータ、Hadoop 関連形式 (ORC、Apache Avro、Parquet) のデータ、および Logstash ログ、AWS CloudTrail ログ、Apache WebServer ログです。

サポートしているファイル形式はいくらかありますが、自分はCSVとParquetの2種類なのでこの2種類に限って話をします。
まずCSVですが、これは普段から使うようなファイル形式なので使う機会が多いのではないでしょうか。CSVを使うときはファイルを圧縮しても可能なので必ずgzipなどで圧縮しましょう。圧縮するとスキャンデータ量が減るので費用を抑えることができます。当然S3の費用も抑えられます。zip形式は対応していないのでその点のみ注意です。

ただ弊社では基本Parquetファイルを利用しています。これは列指向データなので列を指定することでスキャン量を大幅に減らせます。大規模データになればなるほど正規化されていないデータが多くなるので列を絞ってクエリを実行することで速度アップ・費用を抑えることが可能です。

select * from users

ではなく

select id, name from users

というようなSQLにします（id、name以外にたくさんカラムがある想定）。またParquetはそれ自体圧縮されたものなので、圧縮する必要はありません。
Parquet形式のファイルはpython使うとCSVなどから変換できますが、Athenaを利用しても作成できます。
参考：AthenaでCSVファイルからParquet形式へ変換

ファイルサイズの最適化

Amazon Athena のパフォーマンスチューニング Tips トップ 10
S3に置いているファイル数が多いとリクエスト数が増えると上述しましたが、どれくらいのファイルサイズにまとめるべきか上記リンクに記載があります。

ただしファイルサイズが非常に小さい場合、特に 128MB 未満の場合には、実行エンジンは S３ファイルのオープン、ディレクトリのリスト表示、オブジェクトメタデータの取得、データ転送のセットアップ、ファイルヘッダーの読み込み、圧縮ディレクトリの読み込み、といった処理に余分な時間がかかります。

128MB以下のファイルだと速度が低下するようなのでそれ以上にすると良いみたいです。ただあまり大きいファイルにすると小さいクエリでもスキャン量が大きくなるので弊社では128MB程度のファイルにまとめています。

当初は小さいファイル（数KB~数MB）でクエリを実行していましたが、実際に実行時間は数分の1から十分の1程度まで下がった一方スキャン量は増えました。
Athenaの料金はいくらか上がりますが、小さい多くのファイルが対象だとS3へのリクエスト量が増え、S3料金が発生るるのでやはり小さいファイルにまとめるのは得策ではなさそうです。

パーティション

データが大きくなってくるとクエリの時間や費用が大きくなってきます。そういった場合パーティションを利用すると速度・費用ともに改善されます。パーティションは時刻やその他のグループで分けるなど、普段流すことの多いクエリによって変わっていますが、弊社では時間で区切っています。
パーティションはあとから加えることも可能ですが。S3のディレクトリ（実際にはディレクトリではありませんがここでは便宜的にディレクトリと呼びます）構成を事前に整えておくことでメンテナンスが用意になります。またパーティションはいつでも好きなように区切れるわけではなくS3のディレクトリ構成に依存するのでいくらか事前に分けておくことをおすすめします。

s3://elasticmapreduce/samples/hive-ads/tables/impressions/year=2020/month=01/
s3://elasticmapreduce/samples/hive-ads/tables/impressions/year=2020/month=02/

このようにyear=のような形式にしておくとパーティションの詳細を都度加える必要がなくなります。

MSCK REPAIR TABLE <table_name>

の実行のみ必要となります。もしこのようなディレクトリ構成にしていない場合はAlter table文で対応します。
参考：Athenaで後からパーティションを追加する

またパーティションを利用することで、本来の使い方ではないかもしれませんが、ごちゃごちゃになったディレクトリ構成でも単一のうまくデータを分けられることがあります。
参考：Athenaでは複数のS3ロケーションを指定できないのでその対応方法

TIMESTAMP型の結果が空になる

2020-07-01 15:54:12のようなYYYY-MM-DD HH:MM:SS形式のタイムスタンプをそのままAthenaに読ませるとエラーは出ないのですが、空欄で結果が返ってきます。
Amazon Athena のテーブルにクエリを実行すると、TIMESTAMP の結果が空になる
原因・対策は上記公式ドキュメントを参照すればOKなのですが、タイムスタンプ型を扱うにはYYYY-MM-DD HH:MM:SS.fffffffffという形式が必須となります。

上記リンクとは別の対処になるのですが、弊社では文字列型で一旦テーブルを作り、キャストしてcreate table as selectを利用しています。
キャストはcast(time_column as timestamp)でできるので、キャストしてテーブルを作り直す、またはキャストでタイムスタンプ型を都度作るようにしています。

ウィンドウ関数

大規模データを扱って基本的には何かを分析するものだと思われるのでウィンドウ関数は使えるとかなり便利かと思います。
ドキュメントは以下です。
Window Functions
たとえば特定のグループ順位をつけるためにROW_NUMBER関数を使ったり、前後の行を取得するためにLAG関数を利用したりしました。
参考：LAG関数利用例
ウィンドウ関数を普段から馴染みがある人は問題ありませんが、触ったことのない人は関数だけでもどのようなモノがあるか眺めておくと、必要なときに使えるかもしれません。

クエリ結果からのテーブルの作成

Athenaでは通常のDBにはあるinsertやupdate文は利用できません。基本的には今あるデータを閲覧すことがメインとなります。
ただ分析結果からあるデータを取り出し、さらにその結果を分析したい場合があります。
これを都度サブクエリを増やしていってデータを分析していくと、処理時間が膨大となります。その場合にはクエリ結果からテーブルを作成する、CREATE TABLE AS SELECT (CTAS) を利用することをおすすめします。
構文としては

CREATE TABLE <table_name>
    WITH (
    format='PARQUET',
    external_location='<s3://path/to/new/file/directory>'
) AS SELECT * FROM <exist_table_name>

最後のselect文で条件を指定したりなどをしてデータを抽出したものから新しいデータ・テーブルを作成します。上記例ではformatをParquetに指定しているので、元のファイルがCSVだったとしてもここでParquetに変換されます。
CATSを利用して
- カラム形式の変更
- データの絞り込み（不要データ削除）
- ファイル形式の変更
などを弊社では利用しており、頻繁に利用する機会があります。

タイムスタンプの比較

上述したようにタイムスタンプ型を利用するには少し注意が必要です。またさらに、タイムスタンプを比べるときのSQLにもひと工夫必要になります。
たとえばMySQLであれば

WHERE created_at > '2020-07-01 16:44:51'

というような構文が有効となります(created_atというカラムがタイムスタンプ型と想定)
しかしAthenaの場合、タイムスタンプ型と文字列型は比較できませんとエラーとなってしまいます。そのためキャストする必要があります。

WHERE created_at > cast('2020-07-01 16:44:51' as timestamp)

こうすることで時刻の比較が可能となります。

ORDER BY を最適化する

Amazon Athena のパフォーマンスチューニング Tips トップ 10
こちらにもありますが、ORDER BYは単一のワーカーで実行されるため、ソート対象が大きくなるとかなり時間がかかります。そして大きくなりすぎると処理ができなくなりエラーが吐かれるので注意です。上記リンクにはlimitをつけるという対処しか書かれておらず、データ量を減らすしか現状では対応できないようです。

WEBアプリケーションとの連携について

弊社では一部PHPと連携させてAthenaから結果を取得しています。aws-sdk-phpを利用するとあとはcredential設定のみで簡単に利用可能です。
ただその際にも最低でも必ずレスポンスが帰ってくるまで数秒はかかるので、C向けのサービス等ではかなりキビいいと思われます。
例えばテーブル一覧の取得のSQLで、S3にアクセスしない場合でも3秒程度はかかっています。

最後に

まだまだやれていないこともたくさんあるかと思いますが、上記のことが実践した上でAWSエンジニアに現状を見てもらったところ、ほぼ完璧に組めているとお墨付きをもらいました。ただどれもが最初からできていたわけではなく、都度発生する課題に対してドキュメントを読み込んだり、様々な記事をたどっていった上でわかってきたことが多く、最初からわかっていればなぁと思ったことをまとめておきました。
間違っている部分も多かったり、より良い方法があったりすると思いますのでその点ありましたらご指摘いただけると助かります。