この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
大阪オフィスの玉井です。
大人の事情かどうかはわかりませんが、何かとBigQuery前提なLooker Blocksを、Snowflake用に改造したので、その改造記録を残します。本当に正しい計算結果が保証されてるかどうかはわかりませんので、あくまで参考としてお読みください。
Looker Blocksとは
ざっくりいうと、「あるデータを前提として既に書かれているLookML群」という感じでしょうか。詳しくは下記の記事を読んでください。
複雑な構造をしているBlocksも
Blocksは大体Githubに上がっているので、そこからLookerのProjectにインポートすれば使えます。もしくは、Marketplaceから直接インストールするのもアリです。
ただ、Blocksによっては、非常に複雑な構造をしているのもあり、そのまま持ってくると改造するのには不都合という場合もあります。下記は複雑な構造をしているBlocksの一例です。
今回改造したのは「Zendesk By Fivetran」
今回改造したのは下記のBlocksになります（Marketplaceから入れることができる）。
「Fivetranで連携したZendeskのデータ」に対して、既にモデリングとダッシュボードが構築済になっているBlocksです。これを入れれば、Zendeskのデータがすぐに可視化されます。ただし、データウェアハウスがGoogle BigQuery前提となっています。同じFivetranを使っていても、連携先のDWHがRedshiftやSnowflakeの場合はうまく動きません。
私の部署ではZendeskを用いたテクニカルサポートを行っており、サポート業務の分析は必須なので、こちらを使用したいところですが、部署で使用しているDWHはSnowflakeです。ですので、このBlocksをSnowflakeでも動くように改造したいと思います。
ちなみに「Fivetranって何？」という方は下記をどうぞ。
やってみた
Market Placeからのインストールは「しない」
前述した通り、今回改造するZendesk By Fivetranは、Marketplaceからインストールした場合、結構複雑な構造をしています。
インストールすると、Projectが2つ作成されます。
- ユーザー側で編集できるconfig用のProject
- ユーザーでは編集できない（見るだけ）コアとなるProject
データを実際にモデリングしているのは後者のコアの方でやっています。Fivetranで連携したテーブルに対してLookMLが書かれているというわけです。
で、ここからがややこしいのですが、上記のコアとなる部分は、そのままExploreには使われず、一旦前者のconfig用Projectに継承されています。前述した通り、このconfig用のProjectはユーザーが編集できるのですが、config側で各種設定（Zendeskのドメイン名など）を上書きできるようになっています。
そして、ユーザー側の編集が適用されたconfig側のviewが、再びコア側で継承されています。実際のデータモデリング部分はコア側にかかれていて、それを一旦config側に継承し、ユーザ側の編集を載せた上で、再びコア側に戻ってくる…という感じになっています。
…が、今回は実際のデータモデリング部分だけパクりたいと思います。
本体部分だけ持ってくる
とりま、普通にコピってきます。データモデリング自体が行われているviewは「〜_core」って名前になってるので、そこだけ持ってきます。modelファイルは、そのまま持ってくるというよりは、explore単位でコピーして、こちらのmodelファイルに貼り付ける形をとりました。
初期状態で既にエラーが出ている部分を修正する
意外と初っ端からエラーが出るLookMLがあるので、そこを修正します。
user.view
is_agentというdimensionがないため追加（別の派生テーブルで使われているが、なぜか定義されていない）。
dimension: is_agent {
type:yesno
sql: ${TABLE}.role = 'agent' ;;
}ticket_comment_response_times.view
コメントに「If necessary, uncomment the line below to include explore_source.」と書いてあり、実際必要なので、モデルをincludeする部分のコメントを除く&編集（モデル名とフォルダ名は自分で適当に設定しています）。
include: "/prod/cm_zendesk.model.lkml"
view: ticket_comment_response_times {
...SQLの部分をSnowflake用に修正する
ここが一番時間がかかる部分です。BigQuery前提で作られているため、SQL部分が全部BQ用のクエリになっています。大体そのまま使えるのは…？と甘い思いを抱いていたのですが、意外と関数名とかがSnowflakeとは違っていて、そのままだと全然動きませんでした。というわけで、BQとSnowflakeのリファレンスを見比べながら、地道に改造していきます。
共通
Blocks開発中の事情で、日付型のdimentionのほとんどに、謎の時間ずらしが入っています（コメントあり）。
Things are dateshifted for dev purposes, but this should NOT be dateshifted (shoudl just be ${TABLE}.created_at
sql: TIMESTAMP(DATETIME_ADD(DATETIME(${TABLE}.created_at), INTERVAL 810 DAY)) ;;コメントに書いてあるとおり、本番では不要なので直します。
sql: ${TABLE}.created_at ;;810日スライドは、全部上記のように普通にカラムを参照する形に片っ端からなおします。
ticket_close_dates.view
Snowflakeだと下記が動きません。
sql: CONCAT(
CONCAT(CAST(FLOOR(${hours_to_resolution}/7/24) AS STRING), " Weeks "),
CONCAT(CAST(FLOOR(MOD(${hours_to_resolution},7*24)/24) AS STRING), " Days "),
CONCAT(CAST(MOD(MOD(${hours_to_resolution},7*24),24) AS STRING), " Hours"));;ダブルコーテーションをシングルコーテーションに修正します。後はダブルパイプで繋げるようにします。
sql: CONCAT(CAST(FLOOR(${hours_to_resolution}/7/24) AS STRING), ' Weeks ') ||
CONCAT(CAST(FLOOR(MOD(${hours_to_resolution},7*24)/24) AS STRING), ' Days ') ||
CONCAT(CAST(MOD(MOD(${hours_to_resolution},7*24),24) AS STRING), ' Hours ');;ticket_assignee_fact.view
Snowflakeだと下記が動きません。
1.0 * COUNT(*) / NULLIF(DATE_DIFF(CURRENT_DATE, MIN(EXTRACT(date from created_at)), day), 0) AS avg_tickets_per_dayDATEDIFFの書き方が異なるのでそれを修正。EXTRACTあたりも修正。
1.0 * COUNT(*) / NULLIF(DATEDIFF(day, MIN(created_at), CURRENT_DATE), 0) AS avg_tickets_per_dayticket.view
DATE_DIFFが6つあるので、全部Snowflakeの「DATEDIFF」に直します。
sql: 1.00 * DATE_DIFF(CURRENT_DATE(), ${last_updated_date}, DAY) ;;sql: 1.00 * DATEDIFF(day, ${last_updated_date}, CURRENT_DATE) ;;cm_zendesk.model
エージェント観点の分析を行うexploreにsql_always_whereがありますが、やはりBQとSnowflakeで微妙に書き方が異なるので修正。
sql_always_where: ${is_agent} IS TRUE ;;sql_always_where: ${is_agent} = TRUE ;;ticket_comment_response_times.view
最大の難関viewです。
ネイティブ派生テーブルが定義されているのですが、その中に定義されているderived_columnでめんくさいクエリが記述されています。
derived_column: next_agent_response_time {
sql: IF(CASE WHEN LAG(is_agent IS TRUE,1) OVER (PARTITION BY ticket_id ORDER BY created_time ASC) IS NULL
OR (LAG(is_agent IS TRUE,1) OVER (PARTITION BY ticket_id ORDER BY created_time ASC) IS TRUE AND is_agent IS FALSE)
THEN 1
ELSE 0
END = 1,
-- Can replace the first_value input with a "is_suppport_agent" flag after building DT
FIRST_VALUE(CASE WHEN is_agent IS TRUE THEN created_time ELSE NULL END IGNORE NULLS) OVER (PARTITION BY ticket_id ORDER BY created_time ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), NULL) ;;
}
derived_column: responding_agent_id {
sql: IF(CASE WHEN LAG(is_agent IS TRUE,1) OVER (PARTITION BY ticket_id ORDER BY created_time ASC) IS NULL
OR (LAG(is_agent IS TRUE,1) OVER (PARTITION BY ticket_id ORDER BY created_time ASC) IS TRUE AND is_agent IS FALSE)
THEN 1
ELSE 0
END = 1,
-- Can replace the first_value input with a "is_suppport_agent" flag after building DT
FIRST_VALUE(CASE WHEN is_agent IS TRUE THEN user_id ELSE NULL END IGNORE NULLS) OVER (PARTITION BY ticket_id ORDER BY created_time ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), NULL) ;;
}2つのderived_columnがありますが、前半の条件式は全く同一です。こちら、完璧に仕様を把握した！とまではいえませんが、同じチケットで前回の返答と今回の返答がエージェントかどうか、新規作成か否か、ということをLAG関数を用いて判断していると思われます。
そして、クエリをよく読むと、下記のようなコメントが書かれています。
Can replace the first_value input with a "is_suppport_agent" flag after building DT
「派生テーブル構築後、"first_value"の入力は"is_suppport_agent"に置き換えることができる」という感じですね。イマイチ意味が掴みづらいのですが、前半（IF〜）のクエリと後半（FIRST_VALUE）のクエリの内容が似ているということと「置き換える」という言葉を都合よく解釈し、前半をバッサリ削って後半のクエリのみにしました。そして細かいところはSnowflake仕様にします。
derived_column: next_agent_response_time {
sql: FIRST_VALUE(CASE WHEN is_agent = TRUE THEN created_time ELSE NULL END IGNORE NULLS) OVER (PARTITION BY ticket_id ORDER BY created_time ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) ;;
}
derived_column: responding_agent_id {
sql: FIRST_VALUE(CASE WHEN is_agent = TRUE THEN user_id ELSE NULL END IGNORE NULLS) OVER (PARTITION BY ticket_id ORDER BY created_time ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) ;;
}かなりバッサリいったので、念のため、Exploreでこれらのdimentionの動きを確認しました。下記は、とあるチケット1つにフィルタリングして、やり取り毎に計算されているnext_agent_response_timeを確認したものです。
そのレコードのnext_agent_response_timeに、ちゃんと次回返答日時が入っていますね。ちなみにコメント投稿日とnext_agent_response_timeが同一のレコードは、リクエスタ側のコメントになっているので、正しい動きになっています（このdimentionはエージェントを対象にしたものなので、リクエスタは省く必要がある）。
そして、これを使ってさらに計算が組まれているdimentionresponse_timeがあります。
# Needs refactoring, for now, we'll use the straight difference in timestamps
dimension: response_time {
label: "In Hours (includes weekends by default)"
type: number
sql: TIMESTAMP_DIFF(${next_agent_response_raw}, ${created_raw}, hour) ;;
# sql: (((UNIX_DATE(DATE(${next_agent_response_raw})) - UNIX_DATE(DATE(${created_raw}))) + 1)
# -((EXTRACT(WEEK FROM ${next_agent_response_raw}) - EXTRACT(WEEK FROM ${created_raw})) * 2)
# -(CASE WHEN EXTRACT(DAYOFWEEK FROM ${created_raw}) = 1 THEN 1 ELSE 0 END)*24.0
# -(CASE WHEN EXTRACT(DAYOFWEEK FROM ${next_agent_response_raw}) = 7 THEN 1 ELSE 0 END))*24.0
# +TIMESTAMP_DIFF(TIME${next_agent_response_raw}), TIME(${created_raw}), hour) ;;
}のっけからコメントで「リファクタリングが必要」というなかなかアレなコメントが書かれています。このdimentionは「エージェントが前回のコメントから今回の返答をするまでにかかった時間」なので、ひとまずコメント投稿日（作成日）と先ほどのnext_agent_response_timeの差分をとるdimentionにしましょう。Snowflake用にしたのが下記。
dimension: response_time {
type: number
sql: DATEDIFF(hr, ${created_raw}, ${next_agent_response_raw}) ;;
}ちなみに、これを用いたmeasureが既に定義されており、返答時間の平均や中央値がとれるようになっています。チケットの解決時間とかはよく見ると思いますが、返答の1つ1つの時間まで分析しているのは、なかなかないんじゃないでしょうか。
LookMLダッシュボードを持ってくる
一通りLookMLをSnowflake用に修正したところで、ついでにダッシュボードも持ってきたいと思います。せっかくダッシュボードまで構築されているのですから、そのまま使いたいですよね。
当Blocksに付属しているのはLookMLダッシュボードですので、ダッシュボードもLookMLで書かれています。というわけで、3つあるLookMLダッシュボードをそのままコピーしてきます。
こちらのLookMLダッシュボード、例えばexploreあたりの値が、Blocksデフォルトの名前のままなので、そこらへんは自分の環境に合わせた名前に修正する必要があります（バリデーションでエラーが出るので、修正部分はすぐわかると思います）。
ラベルとかをキレイにつける
最後に、各フィールドに日本語のlabelとdescriptionを追記します。これはこれで時間がかかる作業となりました。
ちゃんと分析できるか確認する
一通りSnowflakeで動くように改造したので、実際に分析できるか確かめてみましょう。
LookMLダッシュボード
Blocksに最初から構築されているやつです。3つあるのですが、ひとますSupport Overviewというもの（Zendeskの問い合わせ状況全般に関する情報がわかるダッシュボード）を見てみたところ、正しく描画されました。
Explore
続いて、ModelファイルにあるExploreを使ってみます。こちらもエラーが起こることなく使用できています（といっても、全部のフィールドを虱潰しに見たわけではないので、もしかしたらうまく動かないmeasureとかあるかもしれません）。
自分で作ったダッシュボード
せっかくなので、自分でもダッシュボードを作ってみました。リクエスタが所属する組織（会社）を観点としたダッシュボードです。
ちなみに、上記の組織名から、組織個別の分析ができるダッシュボードに遷移できるようになっています。
※:organization.viewというviewにあります。
dimension: name {
label: "会社名"
type: string
sql: ${TABLE}.name ;;
link: {
url: "/dashboards/xx?会社={{ value }}"
label: "顧客別ダッシュボードへ"
}
}おわりに
大変な部分もありましたが、大部分はLookMLでてきている（=SQLを抽象化しているのでDB別の方言に振り回されない）ので、SQLパラメータや派生テーブル内だけに改造部分の焦点を当てればよかったところは楽だったかなと思いました。
5
