DMM.comにおけるビッグデータ処理のためのSQL活用術

/ 72

DMM.comにおけるビッグデータ処理のための

SQL活用術

ビッグデータ部加嵜長門鈴木翔太

2016年6月16日

Datapalooza

/ 72

自己紹介

•加嵜長門

• 2014年4月～ DMM.comラボ• Hadoop基盤構築

• Spark MLlib, GraphXを用いたレコメンド開発

•好きな言語• SQL

• Cypher

2

2016年4月29日発売下田倫大, 師岡一成，今井雄太, 石川有, 田中裕一, 小宮篤史, 加嵜長門『詳解 Apache Spark』

/ 72

自己紹介

•鈴木翔太

• 2015年4月〜 DMM.comラボ（2年目）• Node.js & Expressを利用した内製BIレポートツール開発

• アドホック分析のためのクエリエンジン(Presto) 導入

•利用している言語 / MW• Hadoopエコシステム

• Node.js

• Golang

• Docker

3

/ 72

ビッグデータ部の紹介

• 2015年12月～設立

•ミッション• データドリブンマーケティング

• マーケティングオートメーション

• DMMのさまざまなサービス• 独立した事業部

• 横断的なデータ収集・活用

4

ビッグデータ部

第1グループ

データドリブンマーケティング

データ収集・可視化・分析

第2グループ

マーケティングオートメーション

データ活用

第3グループ

事業サポート

鈴木加嵜

/ 72

「ビッグデータ処理のためのSQL活用術」

• なぜSQLか？

• 利用者が多く、汎用性が高い

• RDB

• DWH

• NoSQL

• 分析用途のクエリ拡張

• SQL:2003 ～

• アナリストでない人も含め、だれでもデータを分析できる基盤を作る

• ディレクター

• 経営者

5

The 9 Most In-Demand Programming Languages of 2016http://www.codingdojo.com/blog/9-most-in-demand-programming-languages-of-2016/

http://www.codingdojo.com/blog/9-most-in-demand-programming-languages-of-2016/

/ 72

システム概要

6

データマート

機械学習/バッチ処理

アドホッククエリ

ストリームデータ

マスタデータ

/ 72

システム概要

7

データマート

機械学習/バッチ処理

アドホッククエリ

ストリームデータ

マスタデータ

/ 72

要素技術紹介

• Hive• 大規模データに対するバッチ処理

• Sqoop• HadoopとRDBの接続

• データマート/DWH作成

• Presto• 大規模データに対するアドホッククエリ

8

/ 72

Hiveとは何か？

• SQLライクな問い合わせ(HiveQL)をMapReduceとして実行• MapReduceを直接実装するのは手間がかかる

•大規模な分散バッチ処理

•高スループット

9

/ 72

Hiveの使いドコロ

• Hiveの用途• 定期実行ジョブ

• あらかじめ登録したクエリテンプレートに、パラメタを渡して実行

• アドホッククエリ• HueのWeb UI から任意のクエリを実行

10

クエリテンプレート

INSERT OVERWRITE TABLE xxSELECT

*FROM

table1WHERE

dt = ${dt}AND category = ${category};

{ dt: '${today}', category: 'movie'}

{ dt: '${today}', category: 'app'}

{ dt: '${today}', category: 'book'}

パラメタを指定したJSON

定期実行

/ 72

Hiveの使いドコロ

• Hue• Hive のWeb UIを提供

• アナリスト/エンジニアがアドホック分析に利用している

• クエリ結果をグラフィカルなチャートで出すこともできる

11

/ 72

Hive on Sparkとは何か？

• Hive on Spark とは• https://issues.apache.org/jira/browse/HIVE-7292

• Hive の実行エンジンに、MapReduce ではなく Spark を使用• Hive テーブルを RDD として扱う

• 用途的には Hive on Tezと競合する

• Spark – RDD のメリット• 繰り返し処理に強い

12

https://issues.apache.org/jira/browse/HIVE-7292

/ 72

Hive on Sparkのメリット

• 3時間以上かかっていた Hive バッチ処理が1時間まで短縮• ジョブ数が多くなるクエリほど効果が高い傾向

• Hive クエリの書き換えが不要• パラメタ一つの変更で高速化できる

13

-- Hive on SparkSET hive.execution.engine=spark;

37% 19%

/ 72

Sqoopとは何か？

• Sqoopとは、RDBMS(MySQL / PostgreSQL等)とHadoop(HDFS)のデータ転送を行うためのツール

14

/ 72

Sqoopの使いドコロ

• Hiveだと遅いので…PostgreSQL / MySQLで分析したい• 例) データマートの作成

• 必要なデータをHiveで集計→ RDBへ転送

15

/ 72

Sqoopの使いドコロ

• PostgreSQL、MySQLで処理しきれない大量データがある…• 例) データウェアハウスの作成

• 定常バッチとして処理→ Hadoopにデータ転送

16

/ 72

Prestoとは何か？

•「分散(MPP)クエリエンジン」の一種

• Facebookが主体となって開発• 活発にアップデートされている

• 企業への採用実績も多数

• ANSI SQLを採用

17

/ 72

Prestoとは何か？

• Presto自身はデータを持たず、HadoopやMySQL / Postgresにあるデータにクエリをかけることができる

•大量データに低レイテンシで問い合わせ可（PB級データにも）

•様々なデータソースに対応• 最近、MongoDBに対応した

• 特徴的なものでKafkaなど

18

/ 72

•異なるデータソースをJOINできる• マスターデータがRDBにしかないケースでもクエリでJOINできる

• RDB ⇔ RDB間やHive ⇔ RDB間など自由自在

• Sqoopでの転送が不要に

Prestoの特徴

19

/ 72

Prestoが必要になった背景

セグメント分析案件がきっかけ

要件• データ・ウェアハウスに対し、生ログを含めたデータを高速に抽出した結果を提供する

• どんなに遅くても5分以内には結果が欲しい

現状の仕組みではこの要件を満たすのは厳しい場面もあった

20

/ 72

Prestoを採用した理由

•なぜHiveではダメなのか？• Hiveで抽出すると、時間がかかり要件を満たすことができない場面

• Prestoで性能検証すると、要件を満たすことができた

• Hive on Sparkはどちらかというとバッチ向き

•なぜデータマートではダメなのか？• データマートの特徴

• RDBなので、問い合わせは非常に早い

• 集約されたデータしかない

• 生ログもデータ抽出の対象であるので見送り

21

/ 72

Prestoを採用した理由

22

アドホック向けのMWの選定を行い、性能検証した結果としてPrestoを採用

/ 72

ビッグデータ処理に役立つ SQL 構文

• WITH句

• CASE式

• LATERAL句 ×テーブル関数

• WINDOW関数

23

/ 72


• WITH句

• CASE式


• WINDOW関数

24

/ 72

WITH句の活用

•共通表式WITH句• SQL99 で定義

•サブクエリに一時的なテーブル名を付与• サブクエリのネストをフラット化できる

• 可読性や保守性を高められる

• デバッグしやすくなる

25

/ 72

WITH句の活用

•ネストしたクエリは読みにくい（WITH句を使わない場合）

26

http://www.slideshare.net/MarkusWinand/modern-sql

--▽最後の処理SELECT a1, a2, b2, c1, c2FROM (--▽ 2番目の処理

SELECT a1, a2, b2FROM a

JOIN (--▽最初の処理SELECT b1, b2 FROM b

) AS d ON (a.a1 = d.b1)) AS e

JOIN (--▽ 3番目の処理

SELECT c1, c2 FROM c) AS f ON (e.a2 = f.c1);


/ 72

WITH句の活用


27









/ 72

WITH句の活用


28









/ 72

WITH句の活用


29









/ 72

WITH句の活用


30









/ 72

WITH句の活用

• WITH句を使って書き換え

31








WITH--▽最初の処理d AS ( SELECT b1, b2 FROM b ),--▽ 2番目の処理e AS (

SELECT a1, a2, b2FROM a JOIN d ON (a.a1 = d.b1)

),--▽ 3番目の処理f AS ( SELECT c1, c2 FROM c )--▽最後の処理SELECT a1, a2, b2, c1, c2FROM eJOIN f ON (e.a2 = f.c1);


/ 72

WITH句の活用


32












/ 72

WITH句の活用


33












/ 72

WITH句の活用


34












/ 72

WITH句の活用


35












/ 72


• WITH句

• CASE式


• WINDOW関数

36

/ 72

CASE式の活用

• CASE式• SQL92 で定義

•条件に従って値を返す式• IF文、CASE文とは異なる

•行持ちのデータを列持ちに変換できる• 異なる行のデータを一行に集約し、計算や比較ができる

37

/ 72

CASE式の活用

•例：CTR, CVR の集計（CASE式を使わない場合）

38

WITHaction_log AS( SELECT '2016-02-01' AS dt, 'view' AS actionUNION ALL SELECT '2016-02-01' AS dt, 'view' AS actionUNION ALL SELECT '2016-02-01' AS dt, 'view' AS actionUNION ALL SELECT '2016-02-01' AS dt, 'click' AS actionUNION ALL SELECT '2016-02-01' AS dt, 'click' AS actionUNION ALL SELECT '2016-02-01' AS dt, 'purchase' AS actionUNION ALL SELECT '2016-02-02' AS dt, 'view' AS actionUNION ALL SELECT '2016-02-02' AS dt, 'view' AS action

),t1 AS (SELECT dt, action, COUNT(*) AS ct FROM action_log GROUP BY dt, action

)SELECTv.dt, COALESCE(c.ct / v.ct, 0.0) AS ctr, COALESCE(p.ct / c.ct, 0.0) AS cvr

FROM t1 AS vLEFT OUTER JOIN t1 AS c ON v.dt = c.dt AND c.action = 'click'LEFT OUTER JOIN t1 AS p ON v.dt = p.dt AND p.action = 'purchase'

WHERE v.action = 'view';

dt action

2016-02-01 view

2016-02-01 view

2016-02-01 view

2016-02-01 click

2016-02-01 click

2016-02-01 purchase

2016-02-02 view

2016-02-02 view

action_log

/ 72

CASE式の活用


39






dt action ct

2016-02-01 view 3

2016-02-01 click 2

2016-02-01 purchase 1

2016-02-02 view 2

t1

/ 72

CASE式の活用


40






dt ctr cvr

2016-02-01 0.666 0.5

2016-02-02 0 0

/ 72

CASE式の活用

•例：CTR, CVR の集計（CASE式を使った場合）

41

t1 AS (SELECT

dt, SUM(CASE action WHEN 'view' THEN 1 END) AS view_ct, SUM(CASE action WHEN 'click' THEN 1 END) AS click_ct, SUM(CASE action WHEN 'purchase' THEN 1 END) AS purchase_ct

FROM action_logGROUP BY dt

)SELECT

dt, COALESCE( click_ct / view_ct, 0.0) AS ctr, COALESCE( purchase_ct / click_ct, 0.0) AS cvr

FROM t1;

dt CASE view CASE click CASE purchase

2016-02-01 1 NULL NULL

2016-02-01 1 NULL NULL

2016-02-01 1 NULL NULL

2016-02-01 NULL 1 NULL

2016-02-01 NULL 1 NULL

2016-02-01 NULL NULL 1

2016-02-02 1 NULL NULL

2016-02-02 1 NULL NULL

/ 72

CASE式の活用


42

t1 AS (SELECT



)SELECT


FROM t1;

dt view_ct click_ct purchase_ct

2016-02-01 3 2 1

2016-02-02 2 NULL NULL

t1

/ 72

CASE式の活用


43

t1 AS (SELECT



)SELECT


FROM t1;

dt ctr cvr

2016-02-01 0.666 0.5

2016-02-02 0 0

/ 72


• WITH句

• CASE式


• WINDOW関数

44

/ 72

LATERAL句 ×テーブル関数の活用

• LATERAL句• SQL99 で定義

• FROM の中で、あるサブクエリやテーブル関数の内側から、外側のリレーションの列を参照する

• NestLoop Join のようなことが実現できる※

• HiveQLでは LATERAL VIEW として実装

•列持ちのデータを行持ちに変換できる

45

※) http://lets.postgresql.jp/documents/technical/lateral/1

http://lets.postgresql.jp/documents/technical/lateral/1

/ 72


• explode関数• テーブル (rows) を返す関数

•配列や MAP を行に展開する

46

SELECT ARRAY(1, 2, 3);SELECT EXPLODE(ARRAY(1, 2, 3));

col

1

2

3

SELECT MAP('a', 1, 'b', 2, 'c', 3);SELECT EXPLODE(MAP('a', 1, 'b', 2, 'c', 3));

key Value

a 1

b 2

c 3

_c0

{"a":1,"b":2,"c":3}

_c0

[1,2,3]

/ 72


•ただし、以下のような書き方はできない• explode関数はテーブルを返すため

47

WITHt1 AS (

SELECT'2016-02-08' AS dt

, MAP('CTR', 0.03, 'CVR', 0.01, 'I2C', 0.0003) AS indicators)SELECT

dt, EXPLODE(indicators)

FROM t1;

t1.dt key value

2016-02-08 CTR 0.03

2016-02-08 CVR 0.01

2016-02-08 I2C 0.0003

dt indicators

2016-02-08 {"CTR":0.03,"CVR":0.01,"I2C":3.0E-4}

t1

/ 72


• LATERAL VIEW と組み合わせる• LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)* ※

48

WITHt1 AS (

SELECT'2016-02-08' AS dt

, MAP('CTR', 0.03, 'CVR', 0.01, 'I2C', 0.0003) AS indicators)SELECT

t1.dt, i.key, i.value

FROM t1LATERAL VIEW EXPLODE(indicators) i AS key, value;

t1.dt key value

2016-02-08 CTR 0.03

2016-02-08 CVR 0.01

2016-02-08 I2C 0.0003

dt indicators

2016-02-08 {"CTR":0.03,"CVR":0.01,"I2C":3.0E-4}

t1

※) https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LateralView

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LateralView

/ 72


• WITH句

• CASE式


• WINDOW関数

49

/ 72

WINDOW関数の活用

• WINDOW関数（OLAP関数）• SQL:2003 で定義

•コストの高い自己結合を排除

•異なる行のデータを、集約せずに参照できる

50

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics

/ 72


•例：時系列データの解析

51

WITHaccess_log AS (

SELECT '2016-02-01' AS dt, 'AAA' AS usernameUNION ALL SELECT '2016-02-02' AS dt, 'AAA' AS usernameUNION ALL SELECT '2016-02-03' AS dt, 'AAA' AS usernameUNION ALL SELECT '2016-02-07' AS dt, 'AAA' AS usernameUNION ALL SELECT '2016-02-01' AS dt, 'BBB' AS usernameUNION ALL SELECT '2016-02-03' AS dt, 'BBB' AS usernameUNION ALL SELECT '2016-02-05' AS dt, 'BBB' AS username

)SELECT

dt, username, LAG(dt) OVER(PARTITION BY username ORDER BY dt) AS last_access, DATEDIFF(dt, LAG(dt) OVER(PARTITION BY username ORDER BY dt)) AS access_span, COUNT(1) OVER(PARTITION BY username ORDER BY dt) AS cumulative_access

FROM access_log;

dt username

2016-02-01 AAA

2016-02-02 AAA

2016-02-03 AAA

2016-02-07 AAA

2016-02-01 BBB

2016-02-03 BBB

2016-02-05 BBB

/ 72



52

WITHaccess_log AS (


)SELECT


FROM access_log;

dt username

last_access

2016-02-01 AAA NULL

2016-02-02 AAA 2016-02-01

2016-02-03 AAA 2016-02-02

2016-02-07 AAA 2016-02-03

2016-02-01 BBB NULL

2016-02-03 BBB 2016-02-01

2016-02-05 BBB 2016-02-03

/ 72



53

WITHaccess_log AS (


)SELECT


FROM access_log;

dt username

last_access access_span

2016-02-01 AAA NULL NULL

2016-02-02 AAA 2016-02-01 1

2016-02-03 AAA 2016-02-02 1

2016-02-07 AAA 2016-02-03 4

2016-02-01 BBB NULL NULL

2016-02-03 BBB 2016-02-01 2

2016-02-05 BBB 2016-02-03 2

/ 72



54

WITHaccess_log AS (


)SELECT


FROM access_log;

dt username

last_access access_span

cumulative_acess

2016-02-01 AAA NULL NULL 1

2016-02-02 AAA 2016-02-01 1 2

2016-02-03 AAA 2016-02-02 1 3

2016-02-07 AAA 2016-02-03 4 4

2016-02-01 BBB NULL NULL 1

2016-02-03 BBB 2016-02-01 2 2

2016-02-05 BBB 2016-02-03 2 3

/ 72

事例

•レコメンド効果分析

•障害分析

•セグメント分析

55

/ 72

事例


•障害分析


56

/ 72

レコメンド効果分析

•項目• レコメンドアルゴリズムのABテスト

• KPIの移動平均

• イベントとの相関分析

• SQL• WINDOW関数をメインに使用

• テンプレート化して各サービスに適用

• JavaScript等で可視化

57

/ 72

レコメンド効果分析

•項目• ランキング集計

• トレンド分析

• 地域・デバイスなどのセグメント分析

• SQL• CASE式によるセグメント化

• CUBE, ROLLUPなどで多次元集計

• JavaScript等で可視化

58

/ 72

事例


•障害分析


59

/ 72

障害分析

背景

• ある日のこと…大規模な障害が起きた• 会社として判断が必要な出来事

• 該当時間のログの分析を依頼された• 今回はCouchDB(No SQL)に格納されたJSON形式のログ

要求

• 会社として判断をくだすために、今日中に結論が欲しい• 与えられた時間はデータ抽出~分析で6時間程度

60

/ 72

障害分析

分析依頼されたログについて

•今回はCouchDB(No SQL)のログ• CouchDBでは、SQLの世界は通用せず…

• JSON形式

•ログは1ファイルにつき、約1GB

61

/ 72

障害分析

Hadoopにデータを入れて、Hiveで分析しよう

主に、以下の2つのアプローチがある

① Json Tuple• 手間いらずで楽ちん (1行に1JSONに整形しておく)

② JSON Serde• 少し手間がかかる（jarを入れて、専用のテーブルを定義）

• JSONのデータを分析する機会が多い人にはおすすめ

62

/ 72

障害分析

•今回は①を採用した

•理由として…

• 時間がなかった

• もらったJSONデータをjqで整形すれば分析可能に

• 複雑な指標は求められず、json tupleで十分だと判断した

63

参考: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#Language

/ 72

障害分析

64

/ 72

障害分析

65

データ整形前1行1JSONに、データ整形後 (※実際は改行なし)

/ 72

障害分析

66

下記のようにJSONをLATERAL VIEWとして定義

/ 72

障害分析

JSONをSQLで扱えるようにしたメリット

•既存のデータ（マスタデータなど）とJOINできる

•分析的なSQLによるデータ操作ができる

67

/ 72

事例


•障害分析


68

/ 72

セグメント分析

•行動ログ等を活用し、ユーザーのセグメンテーション• 例) ◯◯サービスにおいて、特定の商品を見たユーザー

•元となるデータソース(＝行動ログ等)はビッグデータ部が保持しているもの

• 生ログを含めて使う

要件

•条件に合致するユーザーを5分以内で抽出する→ Prestoを採用

69

/ 72

セグメント分析

• Prestoの導入効果• アドホック分析に特化

• 集計結果を素早く事業部側に

提供する基盤ができた

•社内でのユースケース• 右図のように集計結果を

RDBMSへ格納して提供

70

/ 72

まとめ

•汎用性の高いSQL

•用途に応じてミドルウェアを選べる• バッチ/アドホック

• SQLは不変

•分析用のモダンなSQL• SQL:2003～

• JSON形式

71

/ 72

•ビッグデータ/SQLを扱う仕事に興味のある貴方は下記まで

最後に

72

http://recruit.dmm.com/

http://recruit.dmm.com/

DMM.comにおけるビッグデータ処理のためのSQL活用術

Technology

Transcript of DMM.comにおけるビッグデータ処理のためのSQL活用術