昨年末にAthenaを業務適用した際の話を書いた。
testedquality-tech.hatenadiary.jp
その後リリースしたシステムは使われるようになり、バグもなく、利用者も想定通りの発生率。目標達成したので追加開発に入るとの話だった。そのため早速要件定義を始めたところ、レコード最大件数は変更しない状態で、出力項目数を最大6000カラムにしてほしいとの要件が入ってきた。前回の要件はこちら。
ヘッダが商品IDの1000カラム＋ID単位1以上の値を持つ場合の0/1フラグで合計2000カラム、出力行数は200万レコードが最大となる。
このヘッダ部分に商品IDごとの追加データを任意で最大４カラム追加となったのだ。前回Athenaを使った現行要件だと、2000カラムが限界値であると把握していたが、その3倍である。現行全く対応できない項目数。
- 単純に６０００カラム並べただけでAthenaのSQL文字数制限を大幅に超える。
- 最大メモリをさらに使うため同時処理数にさらに制限がかかる。
５JOBどころか全く処理できない！
２つの問題をに頭を抱えたまま、チームに持ち帰り対応を検討した。
最初はAmazon EMRにて処理することを考えた。既に２年近く業務利用していることもあり開発には問題がない。Athenaでできない部分はファイル処理でもなんとでもする方法だ。
ただし利用するたびにインスタンスを用意するのに８分程度かかる。それは現在遅くても３分内で検索完了する要件を満たせない。
続いて開発メンバーから「Athenaが一時テーブル利用ができるようになった」という話がでてきた。CTAS（Create Table As Select ）と呼ばれているこの機能はSelect内容を新テーブルとして作成する機能で2018年11月に追加された。
詳細についてはクラスメソッドさんの以下のブログがわかりやすくまとめられているので参照してほしい。
ここで実際にCTASを現行SQLで試しているうち「項目を分割して、複数の一時テーブルとして出力、最後にすべてを1テーブルにまとめる」というアイデアがでてきた。図にすると以下のようになる。
要件では
検索では件数と上位10件を表示、確認後OKならcsv出力を実施。
これより３．のタイミングで１０レコードに絞り込むことで３．の処理をAthenaのメモリ制限内に収めることが可能である。（１．の時点で件数は確定している）
【良かった点】
①カラム数が大幅に増えてもAthenaのまま対応できた
②出力項目が多い検索は既存より早くなった。
一定規模を超えた時、同一件数をSelectするよりCTASするほうが早くなるのが判明。
【悪かった点】
①少ないレコード数だと検索速度が現行DWHより悪くなった
これは一時テーブル作成のオーバーヘッドが出るため。
②CTASでの一時テーブル作成後はAthenaの制限で操作ができない場合がでる
CTASはメモリ利用がSlelectと異なるのかSelectではできなかった２０００カラム越えの一時テーブル作成も可能なことが分かったので対応できそうだと思った・・・が、一時テーブルとして実テーブル化してしまった３．のテーブルを再度並び替えなどは全く対応できない。それはAthenaの現行仕様なので当たり前であるが。
Order by はAthenaでシングルノード処理になる仕様のため、どうしても負荷がかかるのだと開発メンバーがPrestoのマニュアルで見つけたそうである。ということで、出力時の並び替え仕様が満たせない点、別途PandasでCSVの並び替えを行ってもらうことになった。
③CTASは無限ではない。メモリ制限でダメならファイル処理が必要
いろいろ処理の幅が広がったがCTASしても限界はある。３．作成において最大レコードでの２１００カラム以上はエラーとなってしまった。これより２．までで作成できた一時テーブルを使って３．を当初検討したAWS EMRで結合処理するように分岐させることにした。出力処理が遅い分には問題がないためである。
ということで２次開発が４月にリリースされ、とりあえず無事に動いている。EMRも結局対応しているので あるが、開発メンバーが能力高いので方針さえ決まれば対応してもらえるのが本当に有難い。
自分の反省としてはReactで少しは役割をもったものの、プログラマーとして力不足だったことが悔しかった。ので次期開発にむけてReactをGW中に再勉強。少しでも手が動くようになっておきたい。
