4月から取り組んできたLookerの導入から実装までのお話

Slide 1

Slide 1 text

4月から取り組んできた Lookerの導入から実装までのお話  2020-10-23 FFTT#414 @masutaka  Redashとも比較 

Slide 2

Slide 2 text

自己紹介  ● 増田貴士（@masutaka）  ● 株式会社フィードフォース  ● さすらいの（会社に席がない）エンジニア  ● 先月から月火金はFeedmaticでLooker、水木はDF/CF  ● 最近の趣味はリハビリとYouTube鑑賞（楽しいぃぃい）  https://www.feedforce.jp/  

Slide 3

Slide 3 text

Lookerとは  ● BIツールの一種。他にTableau、Googleデータポータル、Redashなどがある  ● LookMLと呼ばれるDSLでSQLをラッピングするのが特徴  ○ 最終的に実行されるSQLはLookerが自動生成する  ● LookMLがデータ分析のスケーラビリティを高め、ビジネスユーザの SQL学習も不要とする  ● 2019年6月にGoogleによる買収合意。2020年2月買収完了  https://jp.techcrunch.com/2020/02/14/2020-02-13-google-closes-2-6b-looker-acquisition/  

Slide 4

Slide 4 text

注: GCPのここからは使えません。 

Slide 5

Slide 5 text

気になる料金  ● 年間何百万円オーダー  ● 基本はユーザ数に応じた課金（SaaSモデル）  ● 導入時はスポットでいろいろかかる  ○ JumpStart（実質的に必須）  ○ RapidDeployment（今回未受講）  ○ Lookerロゴから会社ロゴに変える（今回は見送り）  ○ Staging用インスタンス（今回はなし）  ● 営業の方と調整して決まる 

Slide 6

Slide 6 text

BIツールとの関わり  ● Redashは割と真面目にHerokuやEC2で運用したことがある  ○ Heroku と Redash は相性が良いのでは？という話 / マスタカの ChangeLog メモ  ● Redashは開発系運用ダッシュボードとしては優秀だけど、ビジネス向けには厳しいという感想  ○ グラフやテーブルを作るためにはSQLのスキルが必要  ○ 管理するクエリが増えるとひたすら大変になる  ● TableauとGoogleデータポータルは少し検討したことがあるけど、私には難しすぎた  https://masutaka.net/chalow/2018-12-17-1.html  

Slide 7

Slide 7 text

Redashとの勝手な比較    Redash  Looker  SQLの習得が必要  Yes  LookML開発者のみ   学習コスト  低い（SQLのみ）  高い（LookML）  始め方  一人で始められる  フォームから連絡  料金  格安  月何十万オーダー以上   OSS？  Yes  No  大きなチーム向け？   No  Yes 

Slide 8

Slide 8 text

今回の対象データ  ● 広告媒体の数値  ○ Criteo, Facebook, Google, Indeed, LINE, RTB House, SmartNews, Yahoo! JAPAN, …  ● 計測ツールの数値  ○ Google Analytics  ● その他  Webアプリケーションが扱うデータと違って、正規化されていない汚いデータが多い。今のところETLは使っていない。 

Slide 9

Slide 9 text

最初の勝手なスケジュール感（4月時点）  6月からお客様に提供するぞー 

Slide 10

Slide 10 text

実際のスケジュール（10月現在）  今月中になんとかして数社に試験展開しよう... 

Slide 11

Slide 11 text

なぜなのか？ 

Slide 12

Slide 12 text

こんな理由です  ● LookerはSign upしてすぐに実装し、運用を始められる訳ではなかった  ● 4~6週間のPoCを経て、ほぼ必須である最大90日間のJumpStart を受講する必要があった  ● ビジネス職の方は片手間にならざるを得なかった  ○ エンジニア（私）は最近までボッチだけど専任だった。  ○ 少なくとも今回に関しては兼任は絶対無理 

Slide 13

Slide 13 text

今回の大雑把な流れ  1. https://ja.looker.com/demo からデモをリクエストし、営業の方からの連絡を待った（3月頃？）  2. 事前準備、PoCスコープ定義（3/30）  3. PoC（4/2~5/27）  4. JumpStart（6/16~9/23） 

Slide 14

Slide 14 text

PoC（4/2~5/27） 

Slide 15

Slide 15 text

Proof Of Concept（概念実証）    “新たな概念やアイデアの実現可能性を示すために、簡単かつ不完全な実現化（または概要）を行うこと。あるいは原理のデモンストレーションによって、ある概念や理論の実用化が可能であることを示すこと”  https://ja.wikipedia.org/wiki/概念実証  

Slide 16

Slide 16 text

やったこと  ● SEの方とペアプロして、LookML開発を体験する  ● 実際の広告数値を使って試作する  ● LookML質疑応答  ● ビジネスへの展開の認識合わせ 

Slide 17

Slide 17 text

今回のPoCタイムライン  4/15 9:00-10:30 Kick off兼初回ペアプロ 4/22 10:00-11:00 PoC 1st Check In 5/7 13:00-14:00 PoC 3rd Check In 4/30 13:00-14:00 PoC 2nd Check In 5/27 最終打ち合わせ (契約判断) 4/2 10:00-11:00 事前準備 PoCスコープ定義この間にLookerと BigQueryを連携しておく 5/19 15:00-16:00 PoC Final Check In ※Looker社からの資料を引用＆加筆  

Slide 18

Slide 18 text

PoCへの雑感  ● 社外の方と初めての言語でのペアプロはかなり緊張した。この後のJumpStartが本番とは知る由もなく...  ● 我々の導入はほぼ決まっていたため、PoCの期間はもう少し柔軟に減らして頂いても良かった気はする  ● すべての回をesa記事に残さなかったことは、少々悔やまれる（日報に書いてしまった） 

Slide 19

Slide 19 text

JumpStart（6/16~9/23） 

Slide 20

Slide 20 text

JumpStart前後の変化  ● 受講前の自分「本当に必要なの？ポジショントークなんじゃないの？」  ● 受講後の自分「受講してなかったら、とんでもないものを作っていた。危なかった...」  JumpStartを受けずに、あとで苦労した会社さんもいるみたい？ 

Slide 21

Slide 21 text

JumpStartの内容  ● 30時間が基本で、最長90日間  ● 主体は我々  ● 超優秀なコンサルタントの方が、一通りの説明をしてくれて、 LookMLのレビューや相談に乗ってくれる  ● ビジネスユーザー向けトレーニングもある  ● ちなみにRapidDeploymentは主体が逆らしい？ 

Slide 22

Slide 22 text

今回のJumpStartタイムライン  ※Looker社からの資料を引用  

Slide 23

Slide 23 text

JumpStartの料金  ● エンジニアを雇うくらいの料金はかかる  ● でも期間限定だし、効果や、受講しないデメリットを考えると安い気はする 

Slide 24

Slide 24 text

JumpStartで実践したこと  ● JumpStart前にLooker Training（英語）を全て受講した  ● ほぼ全部の回で前日に質問をSlackで送った  ● 全部の回でesa記事を作って、Q&Aや学んだことを記録した。全て録画を頂けたのはありがたかった  ● PoC当初から会社からLooker専任にして頂けたことは良かった。とは言え、最初はそこまでやることはないので、専任はPoC中盤くらいからでも良いかも  https://training.looker.com/  

Slide 25

Slide 25 text

LookML 

Slide 26

Slide 26 text

LookMLへの印象  ● PoC前: Cof○eeS○riptみたいな中途半端なやつなんじゃないの？SQLで全部書けるならそうしようかな？  ● PoC後: レイヤーが厚すぎず薄すぎず適切だ。設計を間違えなければ、SQLの能力を何倍にも高められる。SQLはアセンブラ 

Slide 27

Slide 27 text

LookMLの例  view: orders {  sql_table_name: `feedmatic-looker.sandbox_masutaka.orders` ;;     dimension: order_id {   primary_key: yes  type: number  sql: ${TABLE}.order_id ;;   }    measure: count {  type: count  drill_fields: [order_id]   }  }   

Slide 28

Slide 28 text

自動生成されるこんなSQLにも臆さなくなった 

Slide 29

Slide 29 text

軽くデモする 

Slide 30

Slide 30 text

Symmetric 集計 

Slide 31

Slide 31 text

Symmetric 集計  ● LookerをLookerたらしめている最大の機能（大げさ）  ● ビジネスユーザがSQLを書かずに済む  ● 正規化されていない汚いデータも扱える  ● 最初にこの仕組みを知った時は感動しました・・・！ 

Slide 32

Slide 32 text

https://help.looker.com/hc/en-us/articles/3600237 22974  頑張って説明する 

Slide 33

Slide 33 text

2つのテーブル  order_id user_id total order_date 1 100 $50.36 2017-12-01 2 101 $24.12 2017-12-02 3 137 $50.36 2017-12-02 $124.84 order_id item_id quantity unit_price 1 50 1 $23.00 1 63 2 $13.68 2 63 1 $13.68 2 72 1 $5.08 2 79 1 $5.36 3 78 1 $50.36 7 $111.16 order_items orders

Slide 34

Slide 34 text

order_idでJoinする  order_id user_id total order_date item_id quantity unit_price 1 100 $50.36 2017-12-01 50 1 $23.00 1 100 $50.36 2017-12-01 63 2 $13.68 2 101 $24.12 2017-12-02 63 1 $13.68 2 101 $24.12 2017-12-02 72 1 $5.08 2 101 $24.12 2017-12-02 79 1 $5.36 3 137 $50.36 2017-12-02 78 1 $50.36 増えてる $223.44 7 $111.16

Slide 35

Slide 35 text

Lookerだと？  増えてなーい！ (^^)v

Slide 36

Slide 36 text

なぜなのか？ 

Slide 37

Slide 37 text

ここのSQL 

Slide 38

Slide 38 text

Lookerが生成するSQL  SELECT COALESCE(ROUND(COALESCE(CAST( ( SUM(DISTINCT (CAST(ROUND(COALESCE(orders.total ,0)*(1/1000*1.0), 9) AS NUMERIC) + (cast(cast(concat('0x', substr(to_hex(md5(CAST(orders.order_id AS STRING))), 1, 15)) as int64) as numeric) * 4294967296 + cast(cast(concat('0x', substr(to_hex(md5(CAST(orders.order_id AS STRING))), 16, 8)) as int64) as numeric)) * 0.000000001 )) - SUM(DISTINCT (cast(cast(concat('0x', substr(to_hex(md5(CAST(orders.order_id AS STRING))), 1, 15)) as int64) as numeric) * 4294967296 + cast(cast(concat('0x', substr(to_hex(md5(CAST(orders.order_id AS STRING))), 16, 8)) as int64) as numeric)) * 0.000000001) ) / (1/1000*1.0) AS FLOAT64), 0), 6), 0) AS orders_total_total, COALESCE(SUM(order_items.quantity ), 0) AS order_items_total_quantity, COALESCE(SUM(order_items.unit_price ), 0) AS order_items_total_unit_price FROM `feedmatic-looker.sandbox_masutaka.orders` AS orders LEFT JOIN `feedmatic-looker.sandbox_masutaka.order_items` AS order_items ON orders.order_id = order_items.order_id LIMIT 1 これがポイント 

Slide 39

Slide 39 text

ポイントはSUM DISTINCT  WITH tmp AS ( SELECT 200 AS price UNION ALL SELECT 300 UNION ALL SELECT 300 ) SELECT SUM(price) from tmp -- -> 800 WITH tmp AS ( SELECT 200 AS price UNION ALL SELECT 300 UNION ALL SELECT 300 ) SELECT SUM(DISTINCT(price)) from tmp -- -> 500

Slide 40

Slide 40 text

理解してくれ  それぞれのTotalにユニーク性を与え、  Order Idが1と3の$50.36を区別して合計を出した。 

Slide 41

Slide 41 text

そもそも  ● Totalを使わずに、QuantityとUnit Priceで計算すればよいだけの話  ● でもLookerが扱うデータは常にきれいではないし、ユーザはデータ構造を把握し切れてもいない 

Slide 42

Slide 42 text

代わりに必要なこと（制約）  ● primary_key  ○ いわゆるidを必ず設定する  ○ 広告数値だとid的なカラムが存在しないことが多い  ○ データの理解とSQLでうまく確認すればなんとかなる  ● relationship  ○ 1:1, 1:Nのような関係を必ず設定する 

Slide 43

Slide 43 text

今回作ったLookML  https://gist.github.com/masutaka/6d5b7483337cfaff a661da95fce7e3cf 

Slide 44

Slide 44 text

おしまい 