はじめに
株式会社ipocaでAthenaを扱ってきて、最初から知っておけばより早く、より効率的にやれたなと思うところをまとめました。なるべく公式ドキュメントを参照していますが、間違っている部分もあるかと思いますので、最後はドキュメントを確認していただきたいです。また間違った点は指摘いただけると幸いです。
本内容は2020年7月1日現在の内容となっています。
Athenaとは？
Amazon Athena は、標準 SQL を使用して Amazon Simple Storage Service (Amazon S3) でのデータの直接分析を簡易化するインタラクティブなクエリサービスです。AWS マネジメントコンソールでいくつかアクションを実行するだけで、Athena にデータの保存先の Amazon S3 を設定し、標準 SQL を使用してアドホッククエリの実行を開始できます。結果は数秒で返ります。
とありますが、わかりやすく言うと手持ちのCSVファイルなどをS3バケットにおいておくだけでそのままDBのように扱えるようになるサービスです。DBと言ってもデータの取り出しのみ（select）で、insertやupdateは扱えません。2019年よりINSERTが対応となりました
速度もインスタンスタイプにもよりますがRDSで組み込むよりも圧倒的に早く結果を得ることが可能となります。結果は数秒で返りますとありますが、これはもちろんデータ量に依存するので数秒で帰らないことは多々あります。また逆に言えば必ず数秒はかかることとなります。速度を求めるのであればRedShiftの導入のほうが良さそうです。
料金は後述しますが、従量課金で費用も抑えられます。使ってないときはS3の料金のみとなるので、利用方法によりますが、初めて使う人にも費用は優しいものと思われます。
料金
スキャンされたデータ 1 TB あたり 5.00USD
Athenaに関しては上記費用です。10 MB 未満のクエリは 10 MB と計算されるため、最低でも0.00005USD分はかかってしまう想定です。1円もかからないのでよっぽどのことがない限り無視できるかと思います。
またこれとは別にデータをS3に置いているのでその費用はかかってきます。単純なストレージ料金とリクエスト料金がかかります。これは公式の情報ではありませんが、1クエリに対して1ファイル2~3アクセスあるように見えました。これも基本的には誤差の範囲ですが、大規模なサービスになる場合は留意しておく必要があります。
弊社は当初、別システムで細切れに作成された数KBの数万以上のファイルを読み込んでいましたが、S3の費用が圧倒的にかかってしまい、対処が必要となりました。
後述するファイルサイズの最適化で詳しく述べます。
費用が怖い！が怖くない
Athenaは従量課金で開発当初、クエリの実行がかなり怖かったと記憶しています。
ただ実際はかなり安く済みました。Athenaはクエリのスキャン量にのみに費用がかかります。全体の費用で言えば残りはS3のストレージとリクエスト費用で、ここは開発・検討段階では無視できるくらいのサイズかと思います。
費用は例えばS3に10GB置いていたとしたらそのクエリ費用は最大で0.05USD、日本円で約7.5円です。
こう考えると、とりあえずどんなもんか試してみるハードルも低く感じるのではないでしょうか。
SQL
Amazon Athena では、データ定義言語 (DDL) とデータ操作言語 (DML) のステートメント、関数、演算子、およびデータ型のサブセットがサポートされています。いくつかの例外を除き、Athena DDL は HiveQL DDL に基づいており、Athena DML は Presto 0.172 に基づいています。
とあるようにSQLを書くときはPrestoの構文で書きます。
普段MySQLを触っている自分としては特に変わった所はないように感じましたが、ちょっとした関数の違いなどがあるので都度ドキュメントを参考にするといいと思います。
Athena エンジンバージョン 3からはtrinoでの記述に変わりました。といってもtrinoはprestosqlをフォークして始まったプロジェクトなのでほとんど書き方は変わりません。
エンジンバージョンはたまに更新があります。自動更新と手動更新がありますが、私は自動としています。
これまでは特にエンジンバージョンの切り替えに伴って困ったことや対応したことは個人的にはありませんでした。
ファイル形式
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ロケーションを指定できないのでその対応方法
バケッティング
パーティションはパスのプレフィックスでファイルを分割していましたが、どのファイルに何が入っているかをわからせておくことができます。spark等でバケッティングしておくことも可能ですが、CTASでバケッティングされたテーブルを作成することが可能です。CTASの構文は以下のようなものです。
CREATE TABLE table_name
WITH (
format = 'PARQUET',
external_location = 's3://path/to/table',
bucketed_by = ARRAY['hoge_column_name'], -- バケッティングの設定
bucket_count = 300) -- バケッティングの設定
AS
ここで重要なのはbucketed_byとbucket_countです。bucketed_byは何をキーにするのか、bucket_countは分割するファイル数です。
Bucketingのキーはカーディナリティが高く、かつカラムの値に偏りがないことが重要です。カーディナリティが低いとそもそも分割があまりできません。偏りがあっても同様にうまく分割ができません。
bucket_countは全体のファイルサイズやカーディナリティを考慮して決めましょう。全体で1GBのサイズでbucket_countを1000にしたとすると、１ファイルあたり1MBとなってしまいます。これは逆にバケッティングのキーを指定しない場合のスキャンが遅くなる可能性が大きいです。
またバケッティングされたテーブルはINSERT INTOができなくなります。つまりテーブルのメンテナンスがほとんどできません。
デメリットの多いバケッティングはどういう場合に使うのか？
INSERTもできず、CTASやSparkでしかデータを作れないとかなり不便に感じます（実際不便に感じています…）。
これの使い所はやはりカーディナリティが高いキーで検索したい場合です。パーティションは一気に作成できる数も決まっており、カーディナリティが高い場合に苦戦することが多いです。クエリを分けることもできますが、手間になります。
渡しの場合は、カーディナリティが高いとバケッティングを利用し、テーブルデータの入れ替えは全件入れ替えで対応しています。
バケッティングに関する補足は以下の記事で
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文は利用できません。基本的には今あるデータを閲覧すことがメインとなります。
ただ分析結果からあるデータを取り出し、さらにその結果を分析したい場合があります。2019年よりINSERTが対応となりました
これを都度サブクエリを増やしていってデータを分析していくと、処理時間が膨大となります。その場合にはクエリ結果からテーブルを作成する、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)
こうすることで時刻の比較が可能となります。
ちなみにtimestampやdateは
SELECT date '2024-05-11', timestamp '2024-05-11 21:43:00'
といった記述で生成もできます。dateやtimestampと記述してスペースして実際に時刻を書きます。castよりスッキリしますが、ここでの日付や時刻はハードコーディングしかできず、カラム指定はできません。
ORDER BY を最適化する
こちらにもありますが、ORDER BYは単一のワーカーで実行されるため、ソート対象が大きくなるとかなり時間がかかります。そして大きくなりすぎると処理ができなくなりエラーが吐かれるので注意です。上記リンクにはlimitをつけるという対処しか書かれておらず、データ量を減らすしか現状では対応できないようです。
上記リンクはクエリの最適化にかなり役立つので運用を続けるのであれば必読です。
WEBアプリケーションとの連携について
弊社では一部PHPと連携させてAthenaから結果を取得しています。aws-sdk-phpを利用するとあとはcredential設定のみで簡単に利用可能です。
ただその際にも最低でも必ずレスポンスが帰ってくるまで数秒はかかるので、C向けのサービス等では厳しいかもしれません。
例えばテーブル一覧の取得のSQLで、S3にアクセスしない場合でも3秒程度はかかっています。
INSERT INTO の対応
2019年よりINSERT INTO構文が対応となりました。
あるテーブルからあるテーブルに値を挿入する最も簡単な構文は
INSERT INTO hoge_table
SELECT a, b, c FROM ...
という記述です。カラム数が一致していればINSERTが実行され、S3にファイルが作成されます。ファイル形式はテーブルのファイル形式に合わされます。
またこの書き方だと、カラム名は無視されるので上のSQLを正とすると
INSERT INTO hoge_table
SELECT a, c, b FROM ...
のSQLを実行してしまうとbとcのカラムが逆に挿入されてしまって不整合なデータが作成されるので注意が必要です。
また、決まった値をいれるだけならINSERT INTO...VALUESを使うと有効です。
INSERT INTO destination_table [(col1,col2,...)]
VALUES (col1value,col2value,...)[,
(col1value,col2value,...)][,
...]
ワークグループ
SQLを実行するにはワークグループの設定が必要です。デフォルトではprimaryというグループです。
グループ単位でクエリ実行の設定が可能です。
- クエリ実行結果の保存場所
- クエリエンジンバージョン
- クエリの制限（最大スキャン量）
- この設定をしておけば、間違えてとんでもないスキャンを走らせるミスを減らせる
などなどです。仮にこれらの設定は気にしないという方でも、人やサービス単位でワークグループを分けておくことをおすすめします。分けておくことで、クエリ履歴が分かれるので誰がどのサービスがどのようなクエリを実行しているかが見えやすくなります。
最後に
まだまだやれていないこともたくさんあるかと思いますが、上記のことが実践した上でAWSエンジニアに現状を見てもらったところ、ほぼ完璧に組めているとお墨付きをもらいました。ただどれもが最初からできていたわけではなく、都度発生する課題に対してドキュメントを読み込んだり、様々な記事をたどっていった上でわかってきたことが多く、最初からわかっていればなぁと思ったことをまとめておきました。
間違っている部分も多かったり、より良い方法があったりすると思いますのでその点ありましたらご指摘いただけると助かります。