Athenaで億単位のレコード数のテーブル同士をJOINしてみる

11711 ワード
Athena AWS AWS テキストリンク

これは何？

Athenaに思いのほかいろんな関数が実装されていた
のおまけ記事です。

前記事ではいろんな関数を使うことでクエリ１発でいろんな集計ができるかもしれないと書いたのですが、そのためにはデータ量の多い複数のテーブルを繋げるような処理が必要になることが考えられます。
普通に手元のMySQLでそういったクエリを投げると重すぎて最悪結果が返ってこなくなることもあるので、Athenaだとちゃんと現実的な時間で結果が返ってくるのか試して見ました。

問題設定

  • キャラクターをひたすら売却するシミュレーション
  • キャラクターは25種類いて、レア度(rariry)は[1,2,3,4,5]の5通り、売却価格は10^(rarity)
  • プレイヤーが1000人いて、それぞれがキャラクターを1000人持っている
  • 毎日ランダムに100000回プレイヤーを選び、手持ちのレア度[1,2,3,4,5]各一人をランダムに選んで売却し、同じレア度のキャラクターをランダムに選んで補充

これを数千日分シミュレーションし、日毎の「新たに生成されたキャラクター」と「プレイヤーが売却したキャラクター」のログをS3に上げていきます。十分ログが溜まったら合計の売却価格やプレイヤーごとの合計の売却価格を計算します。

データ形式

  • キャラクターマスタ(master_character)

25行です。

{"id":1,"rarity":5,"price":100000}
  • キャラクター生成ログ(character_log)

一日分は500000行です。

{"id":2000001,"master_character_id":1}
  • 売却ログ(sell_log)

一日分は100000行です。

{"player_id":533,"sell_character_ids":[1999466,1669252,1965108,1592234,532614]}

ポイント

  • 売却ログ(sell_log)からキャラクターの売却価格を知るためには、キャラクター生成ログ(character_log)を通してmaster_character_idを得て、そこからキャラクターマスタ(master_character)を引かなければならない

  • レア度[1,2,3,4,5]のキャラクターをそれぞれ売却するので、一人が一回で行う売却価格は必ず111110になる。後の集計で111110の倍数でない数字が出たら何かが間違っている。

  • 生成されてすぐに売却されたキャラクターは日付的に近いところに対応するログがあるが、生成された後でなかなか選ばれなかったデータはその分日付的に離れたところに対応するログがある。

実装

適当に実装します。せっかくなので業務では絶対に書かない闇の1行コードを書いたりしてみましたが地獄でした。
https://github.com/random25umezawa/athena_dataset

実装できたらEC2に乗せ、一日分シミュレーションを進めてログをgzip圧縮してS3に上げるパッチを数千回呼び出しました。

S3に上げる段階で/log_date=YYYY-MM-DD/というパスを含んで保存しているので、log_dateパーテーションを含むAthenaテーブル作成後にMSCK REPAIR TABL table_nameしてパーテーションの設定を済ませます。

出来上がったデータ

数時間シミュレーションを回し続けたところ、以下のようなデータが出来上がりました。

  • 圧縮後約20GBのログ(解凍すると10倍以上になる)

  • それぞれ約30億,約6億行のテーブル(sell_logは約6億行だが、長さ5の配列の展開をするので5倍になる)

  • 2019-01-01スタートで2035-06-06までのデータ(約6000日分)

投げるクエリ

事前に説明したとおり、sell_logにcharacter_logとmaster_characterをJOINします。

WITHの中でパーテーションの指定をすることで検索範囲を絞ります。最初は1日分のみで試し、可能そうなら徐々に範囲を拡げてみます。

WITH sell_log2 AS (
  SELECT
    *
  FROM
    sell_log
  WHERE
    log_date >= '2019-01-01'
  AND
    log_date <= '2019-01-01'
),
character_log2 AS (
  SELECT
    *
  FROM
    character_log
  WHERE
    log_date >= '2018-12-31'
  AND
    log_date <= '2019-01-01'
)

SELECT
  SUM(price) AS price_sum
FROM
  sell_log2
CROSS JOIN UNNEST (
  sell_character_ids
) as _ (sell_character_id)
INNER JOIN 
  character_log2
ON
  sell_character_id = character_log2.id
INNER JOIN 
  master_character
ON
  master_character_id = master_character.id

結果

期間 日数 合計値
~ 2019-01-01 1 11111000000 (Run time: 5.92 seconds, Data scanned: 6.93 MB)
~ 2019-01-10 10 111110000000 (Run time: 7.85 seconds, Data scanned: 39.28 MB)
~ 2019-03-31 90 999990000000 (Run time: 8.2 seconds, Data scanned: 330.22 MB)
~ 2021-06-30 912 10133232000000 (Run time: 20.22 seconds, Data scanned: 3.3 GB)
~ 2035-06-06 6001 66677111000000 (Run time: 1 minute 20 seconds, Data scanned: 21.92 GB)

1分程度で終わってしまった...。

蛇足

プレイヤーごとの集計もうまくできてそうか見てみます。
売却の合計値は111110の倍数のはずなので、結果のテーブルにさらにMOD(price_sum, 111110)を計算させると全て0になるはずです。

WITH sell_log2 AS (
  SELECT
    *
  FROM
    sell_log
  WHERE
    log_date >= '2019-01-01'
  AND
    log_date <= '2035-06-06'
),
character_log2 AS (
  SELECT
    *
  FROM
    character_log
  WHERE
    log_date >= '2018-12-31'
  AND
    log_date <= '2035-06-06'
),
result as (
  SELECT
    player_id,
    SUM(price) AS price_sum
  FROM
    sell_log2
  CROSS JOIN UNNEST (
    sell_character_ids
  ) as _ (sell_character_id)
  INNER JOIN 
    character_log2
  ON
    sell_character_id = character_log2.id
  INNER JOIN 
    master_character
  ON
    master_character_id = master_character.id
  GROUP BY
    player_id
)

SELECT
  player_id,
  price_sum,
  MOD(price_sum, 111110)
FROM
  result

集計できていそうです。

まとめ・感想

数分程度で結果が返ってきたうえに、結果も正しそうに見えます。

実際に別のデータでやる場合は、少ないデータ量で結果の整合性・処理時間などをテストした上で投げましょう。

さいごに

初心者なので何か間違いや注意点などあればご指摘ください。