２０１８年後半は社会人で初めて人手不足で忙しかった。人手は足りないのに仕事は増えるで必死に過ごした結果、初めて客先との会議中に寝る失敗をしたりとひどかったが無事２０１９年を迎えることができた。
夏からメインでやってたシステム、フロントエンドがReactでメインプログラマになり損ねたのであるが、こちらが無事リリースされた。バックエンド側も初めての構成だったがスケジュール費用とも想定内で収まり、かつ開発自体正直面白かった。そこでの知見を書く。
現在業務ではオンプレでSpark準拠のDWH（データウェアハウス）を利用している。UIはなくて各自SQLにてデータを取得する。このDWHのデータをUIで検索できるシステム提供が目的。SQLが書けない人にデータ提供したいのだ。
グループ内はAWSの各種システムが運用されているため、今回も経験者にてAWSでのシステム構築が検討された。しかし一番王道だと思われるAWS Redshiftは今回使えなかった。
aws.amazon.com
実証実験でDB構築して現行と同一速度になるまで課金した結果、月額換算で予算の５倍となることが判明したためである。今回の顧客はそこまでお金がないのでこの方法は取れなかった。そのため再検討でAthenaを利用する案が浮上した。
AWS Athena とはAWSが提供するサーバレスのS3検索システム。S3データをSQLで検索可能となる。
aws.amazon.com
詳細については色々ブログを探したが、こちらの方が2018年の記事でかつわかりやすかったので参考にしていただきたい。
www.casleyconsulting.co.jp
検証である程度の速度が担保できそうということで利用に踏み切った。
【現行システム概要】
データ内容
DWHの１テーブルにある１年間のユーザーアクセスログを検索する。
テーブルをtsv形式で出力して6.7G。年間で5700万レコード。tsvで6.8G。
テーブルのキーは日付、ユーザーID、商品ID。日付と商品IDを検索項目とする。
指定できる商品IDは最大1000。データ内のユーザIDは200万ID。
ユーザーごとに指定された商品IDでサマリー結果を出力する。
ヘッダが商品IDの1000カラム＋ID単位1以上の値を持つ場合の0/1フラグで合計2000カラム、出力行数は200万レコードが最大となる。
データ検索と出力イメージ
2019-01-01から01-03までの検索レコードが以下のオレンジテーブルだった場合、出力は緑のテーブルのようになる。今回だとヘッダは３ID,出力レコード数も3レコードだ。
要件
検索とcsv出力の2機能を搭載する。
検索は同時検索最大10人。かつ1日200回程度の検索を想定。
検索では件数と上位10件を表示、確認後OKならcsv出力を実施。
出力は非同期でOK。同時処理数は最大5JOB。
==================================================================
システム概要より最終的に構成を以下のように実施した。
【Athena構成】
①日付をキーとしたS3構成にする
現行DWHでのデータが日次処理であるため、検索条件と一致させるためS3の構成を 年→月→日 に設定。
②Apatch Parquet形式とする
データをAWSに送る際にはtsv.gzに変換して一日単位で持ってきていた。Athenaに利用するデータ形式は一番検索が早いということで、Apatch Parquetに変換。
parquet.apache.org
以下の記事を参考にしたと、提案してくれた人が教えてくれました。
dev.classmethod.jp
変換する際に1ファイルサイズを整えられるらしく、最初は128KBに分割されていた。しかし、試行錯誤中に海外の事例を見つけたメンバーが、1日1ファイルに1か月分を変換してして検索検証したところ、検索速度が半分以下に短縮された。
これよりデータをすべて作り直し1日1ファイルとしてS3に再配置を実施した。どうやらファイルサイズが小さすぎると検索のオーバーヘッドがあるらしい。
最終的に1年間で1.83G程度のデータ量となっていた。
【良かった点】
1.検索速度が現行DWHより早くなった
当初検索する商品ID指定の一覧表を実テーブル化、SQLでLeft join を行った。これは要求元の処理方法だった。
しかしAthenaではJOINするテーブルが増えると極端に速度が遅くなり、途中でメモリ不足となった。Athenaのメモリは提供されいている範囲のみで拡張はできない。要件を満たせないので、悩んだ末DWH利用者に最大IDの削減を相談した際にヒアリングさせてもらった。
そこでIDをSQLに直接指定する形で、集計関数を利用したSQLをExcelマクロで作成して検索していることが判明した。どうやら現場のみこのマクロを利用していたらしい。ヒアリング不足であった・・・・
この時のSQLを現行DWHで件数検索すると1000ID+フラグ1000カラムで5分かかることが判明した。
早速SQLを変更したところ、商品IDがSQLに記載されることもあり、AthenaのSQL文字数最大値「262,144 バイト」に限りなく近づいたが、1年間、1000IDの集計を1分40秒で完了することを確認した。
docs.aws.amazon.com
ぜひ実証実験を最初にやってめどをつけて利用したほうがよい。
２．運用時のアクセス量が少ない時に利用すると、費用対効果が高い
Athenaは従量課金である。それも検索する際のデータスキャン量による。
今回期間指定が最大1年間だが、DBの利用頻度が1日200回程度の検索が想定されている。全員が1年間検索した場合、
1.83*200=372G が1日のスキャン料金となる。
30日で10.96T。課金は1Tあたり5＄なので、10.96*5=54.8$ = 6000円弱（110円換算）
この金額でRedShiftを運用できない。RDSだと検索スピードがここまで早くできない。ので費用対効果は非常に良いといえる。
常時利用するようなシステムではないが、データ量は多いとき、Athenaは有効な選択肢だと思う。
３．S3を利用するのでデータ量増大でも費用を抑えられる
S3がストレージとして使えるので、DWHで利用しているすべてのデータが現在約１Tなのだがそれを全部検索対象にしても、現行オンプレの1/5以下に抑えられることが判明している。またストレージでのデータ損失についても安定度が高いこともあり安心できる。
【悪かった点】
１．更新処理ができない
SQLが使えるがUpdate文は使えない。だからデータ修正時にはParquet形式ファイルの作り直しになる。データ不備はありえるため、日次転送用プログラムとあわせてリカバリー用のプログラムを事前準備してもらった。どうしても更新が必要となるUIでの検索情報などの管理部分はAmazon RDSを利用している。
２．メモリを利用しすぎると同一処理数を４以下に削減されることがある
先ほどのサービス制限にAPIごとの「1 秒あたりのデフォルトの呼び出し数」があるが、検索に関わるAPIについては5回と記載がある。
これより同一処理は5件までできるのだと判断。同時処理を５JOBとして管理するように設計した。
開発後の負荷テストでは、検索での件数取得いわゆるcount(＊)や上位10件の表示は、前述の1年間1000IDを10人同時に実施して、順次10JOB が正常終了することを確認した。
しかしファイル出力の同時処理テストを実施した際に、1JOBづつのプロセスを５JOB用意し同時に処理させたところ、４JOBしか処理されない現象が発生した。
コンソールには4つのJOBしか出てこない。初めは「JOBを設定し損ねた？」とテスターが確認し、1JOB追加してみたがコンソールには出てこない。そのうちテストと平行で検索をしていた私は、画面での件数チェックでシステムが返答しなくなったのを確認して、Athenaがなんらかの障害を起こしていると判断した。
処理を一度中断させて、サポートにJOBの動作について確認を実施したところ、
「メモリ利用量によって、最大のJOB数を変更するのは仕様である。サービス制限のページに書かれている５JOBは、あくまでもデフォルトの呼び出し数であり、メモリ利用状況によってこの数を変更することはある。」との返答を頂いた。
５JOBの要求満たせないじゃん！
と返答のメールをみて開発者全員で天を仰いだ。
今回は４JOBでサービス制限がかかってしまい、Athenaのサービス全般が停止したのだ。これにより検索など画面に利用する部分の処理も待機、システムが停止することになったのだ。
サービス停止するJOB数を同時実行させるわけにはいかず、想定最大のSQLを全員が実行したときを考慮し、同時処理数を３JOBまで減らすことにした。お客様も普段AWSを利用していて状況を把握していただいたこと、出力処理が遅くても問題がないことよりご了承を頂けたのが幸いであった。負荷テストして本当に良かった。
Athenaのメモリ利用量の制限値は非公開のため、ここは負荷テストにて利用時必ずチェックしないといけない。
【まとめ】
ということで、Athena によってシステム構築をして満足している。DBよりチューニングできる箇所は少ないが、AWSが提供する潤沢なリソースを割安で利用できる点で検討に値すると思う。お客様からも早さと安さで高評価を受けていてちょっとうれしい。
あと、今回はAthenaを提案してくれたメンバーのおかげであるので、メンバーに感謝している。選択したとき、他部署の人はだれもそれをよしとはしなかっただけど、無事顧客に価値を提供できたのですべてよしである。
