AWS Athenaを使って 30TBのWeb行動ログ集計を試みた

(C) Recruit Technologies Co.,Ltd. All rights reserved.

AWS Athena を使って30TB の Web 行動ログ集計を試みた

BigData-JAWS 勉強会 #52017 年 2 月 1 日

株式会社　リクルートテクノロジーズビッグデータ部

渡部　徹太郎


BIG DATA Department自己紹介

{"ID" ： "fetaro" " 名前 " ： " 渡部　徹太郎 " " 研究 " ： " 東京工業大学でデータベースと情報検索の研究

　　　　 (@ 日本データベース学会 )" " 仕事 " ： { 前職 :[" 証券会社のオンライントレードシステムの Web 基

盤 ", 　　　 " オープンソースなら何でも。主に MongoDB,NoSQL"], 現職 :[" リクルートの横断分析基

盤 ,Exadata,Hortonworks,EMR ],　　　副業 :["MongoDB コンサルティング "]}

" エディタ " ： "emacs 派 " " 趣味 " ： [" 自宅サーバ "," 麻雀 "] " 好きな AWS のサービス " : "EMR"}

2


BIG DATA Department背景

業務内容リクルートの横断データ分析基盤「 Genesis 」 Genesis では、リクルートの各 Web サイトのログやデータを収集して、

ID ポイントで紐付けて、サイト横断で分析を実施

3


BIG DATA Department課題と目的

課題 S3→ オンプレへの生ログ転送がしんどいオンプレでの処理が高価（単位容量あたりの処理コストが高い）オンプレでの処理が時間がかかる

目的 AWS Athena で S3 上の生データを一次加工してから

オンプレに持ってこれないか？

4


BIG DATA Department対象データ

データの種類リクルート約 150 の Web 行動ログの生ログ

データ量 30T

データ構造 600 カラム

ファイル構造 Hive のデータ構造になっている

パーティション数 1,044,930

5

s3://backet/acc_log/　 sc=sc0001/ ← サイト単位　　　 ymdh=20161201000000/ ← 時間単位　　　　　 data.tar.gz 　 ←生データ（ TSV ＋ gzip 圧縮）　　　 ymdh=20161201010000/　　　・・・　 sc=sc0002/ 　　　 ymdh=20161201020000/　　　・・・


BIG DATA Department対象処理　

Web アクセス生ログの一次加工５日分前までのデータを加工必要なカラムの抜き出し

• 600 カラム→ 30 カラム

prop30 といった機械的な列名を、実際の意味のある列名に変更幾つかの列の加工「内部 ID のハッシュ値」を「内部 ID 」に変換する

• 具体的には LEFT OUTER JOIN で

6


BIG DATA Department作戦

Tokyo リージョンにある S3 をオレゴンの Athena の外部テーブルとして認識する

S3 のディレクトリ構造をそのままパーティションにする

7

CREATE EXTERNAL TABLE acc_log ( 600 カラム )PARTITIONED BY (`sc` string, `ymdh` bigint)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'LOCATION 's3://backet/acc_log/'

AthenaS3

acc_logメタストア

sc=sc0001 sc=sc0002

ymdh=20161201010000

ymdh=20161201010000

tsv.gztsv.gz・・・

・・・

東京リージョンオレゴンリージョン

パーティションロケーション

sc=sc0001,ymdh=20161201010000

s3://bucket/acc_log/sc=sc0001/ymdh=20161201010000


BIG DATA Departmentテーブルの作成（発生した問題１）

パーティションの認識が終わらない！最初は無邪気に 1,044,930 あるパーティションを一括で認識させようとした

• msck repair table acc_log; 数時間放置したが、全く応答がないので帰宅。翌日タイムアウトしている事がわかった

Java を書いて JDBC でパーティション追加するしかない ALTER TABLE ADD PARTITION にてパーティションを追加

一回に１つの HiveQL しか受け付けてくれないので、パーティションの数だけループを回す ";" が入った HiveQL は NG

8

ALTER TABLE acc_log ADD PARTITION (sc='sc0001' , ymdh='20161231230000') LOCATION 's3://bucket/acc_log/sc=sc0001/ymdh=20161231230000/';


BIG DATA Departmentテーブルの作成（発生した問題２）

手動でパーティション追加すると 16 日かかる一回の ADD PARTITION にかかる時間： 1.3 秒　（ athena 内部では 0.6

秒）パーティション数： 1,044,930 パーティション追加にかかる総時間 =387 時間 =16 日

そもそもパーティション数に 20000 の制限がある事がわかった http://

docs.aws.amazon.com/athena/latest/ug/known-limitations.html これでは 1 サイト 26 ヶ月分しか処理できない！

諦めて、レポートスイート sc0001 の 2016 年 12 月分だけにすることにした 720 パーティションで、 16 分程度でパーティション認識完了データ量が 210G に・・・

30T の処理を期待していたひと、ごめんなさい m(_ _)m9

http://docs.aws.amazon.com/athena/latest/ug/known-limitations.html



BIG DATA Departmentテーブルの作成（悲しかったこと１）

720 パーティションを手動で追加すると、History が全部が ADD PARTION で埋まってしまう！

重要なクエリが埋もれてしまう

10


BIG DATA Departmentクエリ実行（発生した問題１）

1 サイト 5 日分の生ログ加工の実施 HiveQL は「 3400万 LEFT OUTER JOIN 4千万 JOIN 10万」といった感じ

結果 30 分でタイムアウト！

ここでクエリの 30 分が上限であるとわかる http://

docs.aws.amazon.com/athena/latest/ug/known-limitations.html

11




BIG DATA Departmentクエリ実行　高速化作戦その１

クエリをチューニング EXPLAIN をつかってチューニングを試みる

結果 EXPLAIN をつけると Web コンソールから打つと何も返ってこない

History にも計上されない JDBC で打つとサポート外であることが分かる

• Caused by: com.amazonaws.athena.jdbc.shaded.com.amazonaws.services.athena.model.InvalidRequestException: Queries of this type are not supported (Service: AmazonAthena; Status Code: 400; Error Code: InvalidRequestException; Request ID: 6ed149d3-e723-11e6-968b-91cd0b25a162)

12


BIG DATA Departmentクエリ実行　高速化作戦その２

データをオレゴンに移動オレゴン Athena から東京の S3 にクエリをしているため、データ転送が遅い

のかと思い、データを東京に移動

結果は変わらず他のクエリでも試したが、 Athena のリージョンと S3 のリージョンが別れて

いても性能にインパクトは少ない

13


BIG DATA Departmentクエリ実行　高速化作戦その３　フォーマット変換

高速化を目指して列志向フォーマット (parquet) への変換とりあえず Athena 上で parquet テーブルへの SELECT INSERT を試

みる

結果は失敗 INSERT など S3 に変更を加える操作はサポートされていない

• http://docs.aws.amazon.com/athena/latest/ug/language-reference.html

つまり AWS Athena は S3 ビュワー

14

TSV+ gzip フォーマット

parquet

SELECT INSERT

http://docs.aws.amazon.com/athena/latest/ug/language-reference.html




BIG DATA Departmentクエリ実行　高速化作戦その３　フォーマット変換

ドキュメントに従い EMR でのフォーマット変換東京リージョンの EMR で、オレゴンの S3 上のデータを SELECT INSERT

※クラメソさんのブログでは Apache Drill でフォーマット変換をしていたが、データが多いので EMR を採用した

15

TSV+ gzipテーブル

Parquetテーブル

SELECT INSERT

EMR

S3

東京リージョンオレゴンリージョン


BIG DATA Departmentクエリの実行　リトライ（発生した問題１）

1 サイト 5 日分の生ログ加工（列志向フォーマット）の実施 3400万 LEFT OUTER JOIN 4千万 JOIN 10万

列志向フォーマットにして試しても 30 分でタイムアウト！

16

ここで残念ながら検証期間の終わりになしました


BIG DATA Department結果） Athena はどれくらいの速度で動いたか

オレゴン Athena→ 東京 S3

オレゴン Athena→ オレゴン S3

TSV + gzip TSV + gzip parquet

Select count(*)sc絞込ymdh レンジ絞込

52.76 秒スキャン量 6.89GB

51.08 秒スキャン量 6.89GB

22.3 秒スキャン量 0KB↑統計情報から読むため

Select 4 列sc絞込ymdh レンジ絞込＋２列で絞込

110 秒スキャン量 6.89GB


83 秒スキャン量 326MB↑読む列が少ないため

Select 28 列列の値変換多数sc絞込ymdh レンジ絞込他２列で絞込



442 秒スキャン量 6.15GB↑若干少なくなった

業務クエリ上記 +4000万件と 10万件のテーブルと join

30 分で Timeoutスキャン量 13.35GB



17

=

=

= >

>>>

>

>>>=

=

=

↑リージョンまたいでも大差なし


BIG DATA Departmentその他悲しかったこと

History の検索が怪しい "JOIN" や "MSCK" は検索しても表示されなかった

History への計上が怪しい異常終了時に計上されるエラーとされないエラーが有る。法則がわかない。

エラーがわかりにくい HiveQL の文法のどこが間違っていたかわかりにくい。インラインにでることもある Java のスタックトレースが出ない

制約が厳しいクエリ同時実行：５、クエリ時間：３０分、パーティション： 20000 はきつい仮に倍だとしても厳しい

";" で接続した複数の HiveQL は実行できない例 ALTER SESSION SET `store.format` = 'parquet'; CREATE TABLE ・・・

Hive のパラメータ指定はできない

ジョブの進捗がわからない 30 分後に NG の結果がわかるのみ。あと何分早くすればよいかわからない

18


BIG DATA Department嬉しかったこと

Twitter で日本語でぼやいたら、 AWS のシニアプロダクトマネージャから英語でレスが！リミットを上げてくれるという話になりましたが、今回の業務ではリミットを

上げても成り立たないので、お断りとなりました。しかし日本語のつぶやきを拾ってくれるとは、頭が下がります

19


BIG DATA Departmentまとめ

Athena はしばらく成長を見守る Athena は S3 のビュワー

• データ加工は EMR などでやる必要がある

クエリ時間制限があるため、ジョブの置き換えは出来ない分析者にアドホックにクエリを打ってもらうのちょっと厳しそう

• システム管理者側で性能問題の解析ができない

• 分析者が使うには UI がまだまだ成熟していない

API がない

利用できるシーンオンプレに持ってくる前に S3 にどんなデータがあるか確認する

自分の反省ハマりどころが多いので、事前にマニュアルを精読すべきだった

20


BIG DATA Department勉強会で出た意見

AWS のサービスがだすログが hive形式 (key=value/file) になっていないので、そのままは Athena テーブルに出来ない

EMR や RedShift とメタデータを共有してほしい AWS は API ファーストなのに、 Athena には API がない Athena でクエリをした結果を Athena で処理できないので、ジョブで

は使えない。ビュワーがメインだろう Group by で結果が重複する？？ Presto ベースなので JOIN は遅いだろう

とはいえ S3 に置いたままで加工がいらないのはとても魅力！懇親会の乾杯の挨拶「 Athena の今後の改善に期待して乾杯！」

21

AWS Athenaを使って 30TBのWeb行動ログ集計を試みた

Technology

Transcript of AWS Athenaを使って 30TBのWeb行動ログ集計を試みた