Fivetran × Snowflake × LookerでZendeskデータ分析環境をお手軽に構築

データ分析の3種の神器やで

「カスタマーストーリー」を支えるツール特集

#ビッグデータ

#Zendesk

#ビジネス・アナリティクス

#Looker

#Snowflake

たまちゃん

2019.10.08

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんちわ。データアナリティクス事業本部@大阪オフィスの玉井です。

最近、データ分析界隈で話題の3サービスを連携させて、Zendeskデータを分析するための環境を構築してみたので、ご紹介します。

構築する環境

今回構築する環境の全体図はこちら。図といっても、非常に簡単なものとなっております。

多種多様なサービスのデータを自動連携「Fivetran」
クラウドベースのDWH「Snowflake」
次世代データプラットフォーム「Looker」

オールフルマネージドサービス

この環境の特徴は、使用する製品・サービスが全てフルマネージドサービスであることです。つまり、データ分析環境を構築するにあたり、インフラ側の設定・構築等の作業は一切不要となります。

また、今回は連携したいデータとしてZendesk（のデータ）を使用します。

相性の良さ

今回取り上げたサービスの3社それぞれがテクノロジーパートナーということで、連携が行いやすいようになっているというのがあります。また、2019年11月に開催される「Looker Join 2019」というイベントでは、FivetranとSnowflakeがそれぞれ最上位スポンサーとなっています。

今回の作業環境

Windows 10 Pro
Google chrome 77.0.3865.90
Fivetran 2019/10/08時点の最新
Snowflake 2019/10/08時点の最新
Looker 6.20
Zendesk 2019/10/08時点の最新

データを置く環境としてSnowflakeを設定する

下記を参考にトライアル環境を構築します。

この時点では、Snowflakeの環境を用意するだけで大丈夫です。設定は後から行います。

データをDBへ流す環境としてFivetranを設定する

データを流し込む器（Snowflake）を用意したので、今度はその器にデータを流し込むツールが必要です。今回はFivetranというデータパイプラインツールを使用します。

下記と同じ種類のツールです。

トライアルを申し込む

上記のFivetranのサイトから、2週間の無料トライアルに申し込みます。

データ連携先（Snowflake）を設定する

先に、SnowflakeでFivetran用の設定を行う

トライアルの申し込みが完了すると、Fivetranの設定画面に移動します…が、Fivetranの設定の前に、Snowflake側にFivetran用のユーザー等を用意する必要があります。といっても、それらを一発で作成するクエリをFivetran側が用意してくれているので、そちらを実行します。

下記のクエリをSnowflakeで実行するだけです。

-- change role to ACCOUNTADMIN for user / role steps
use role ACCOUNTADMIN;

-- create role for fivetran
create role if not exists fivetran_role;
grant role fivetran_role to role SYSADMIN;

-- create a user for fivetran
create user if not exists <USERNAME>;
alter user <USERNAME> set
default_role = fivetran_role
default_warehouse = <WAREHOUSE>
password = '<PASSWORD>';
grant role fivetran_role to user <USERNAME>;

-- change role to SYSADMIN for warehouse / database steps
use role SYSADMIN;

-- create a warehouse for fivetran
create warehouse if not exists <WAREHOUSE>
warehouse_size = xsmall
warehouse_type = standard
auto_suspend = 60
auto_resume = true
initially_suspended = true;

-- change role to ACCOUNTADMIN for user / role steps
use role ACCOUNTADMIN;

-- grant fivetran access to warehouse
grant all privileges
on warehouse <WAREHOUSE>
to role fivetran_role;

-- create database for fivetran
create database if not exists <DATABASE>;

-- grant fivetran access to database
grant all privileges
on database <DATABASE>
to role fivetran_role;

一部、自環境用に修正する必要があります。ちなみに上記ドキュメントにデフォルトの名前（デフォルトというか参考？）が紹介されているので、今回はそれに従います。パスワードは任意のものを設定しましょう。

USERNAME→fivetran_user
WAREHOUSE→fivetran_warehouse
DATABASE→fivetran

「このクエリはわかったが、実行するのはどこ？」って感じですが、それはSnowflakeのWorksheetsで実行します。

ここにクエリを貼り付けて実行するだけです。注意点としてはAll Queriesにチェックを入れておくことくらいですね。

実行すると、Fivetran用のユーザーやDBが作成されます。また、WarehousesもFivetran用のものが作成されます。これはSnowflake独自の部分になりますが、実際にDBに対する処理を行うのはこのWarehouses（コンピュートノード的な）になります。FivetranがSnowflakeにデータを流し込むとき、その処理を受け持つのはこのWarehousesになるというわけですね。ちなみに、Fivetran曰く、連携処理に高スペックなWarehouseは不要とのことで、デフォルトで設定されるサイズはX-Smallとなってます。

Fivetran側の設定

Snowflake側の受け入れ準備が整ったところで、やっとFivetranの設定を行います。

最初はデータ連携先（データを流し込む器）を選ぶ画面となります。すでにSnowflakeを用意しているので、I already have a warehouseを選びましょう。

Snowflakeを選びます。

Snowflakeの情報を入力します。注意点は下記のとおり。

Host
- Snowflakeの管理画面を開いた時のURLを入力する（～snowflakecomputing.comまで）
User、Password、Database
- 上記のクエリで作成したもの

SAVE＆TESTを押すと、テスト接続が始まり、問題なければ下記の表示になります。

FivetranとSnowflakeの連携設定が終わりました。

データ連携元を設定する

今度は連携したいデータ元のサービスの設定を行います。すなわちZendeskを連携させるための設定です。まず、画面左部メニューのConnectorsを選びます。

そのままメニューを進めましょう。

コネクタを選ぶ画面になるので、Zendeskで検索します。

すると、あれよあれよと言う間に、Zendeskの設定画面になりました。設定項目はたったの2つです。データ送り先のスキーマ名はzendeskで大丈夫です。ドメインは説明文そのままですね。

こちらもSAVE＆TESTでテスト接続が行われます。

次の画面に進むと自動的に初回連携が実行されます。

初期の連携テーブル設定は下記の通り。organization_memberは連携に時間がかかる可能性があるため、デフォルトでは連携されないようになっています。

ちゃんと連携されているかどうか確認してみる

初回連携が終わるとメールが届きます。

正しくデータが入っているかどうか、Snowflakeを確認してみます。

テーブルができています。

データも入ってます。

Fivetranは連携先にどういう形でテーブルが格納されているか…について、ER図を公開してくれています。分析する上で非常に助かります。

Zendesk ERD v3 - Google スライド

ちなみに、連携時にちゃんとWarehouseが稼働していることも確認できました。

データを可視化する環境としてLookerを設定する

ZendeskデータをSnowflakeに流し込めたので、最後はそのデータをLookerで定義&可視化したいと思います。

先に、SnowflakeでLooker用の設定を行う

Fivetran同様、これまた先にSnowflake上で設定をする必要があります。といっても、これまた専用クエリがLooker側で用意されているので、それを使います。

Fivetranの時と同じ要領で下記クエリを実行します。

-- change role to ACCOUNTADMIN
use role ACCOUNTADMIN;

-- create role for looker
create role if not exists looker_role;
grant role looker_role to role SYSADMIN;
    -- Note that we are not making the looker_role a SYSADMIN,
    -- but rather granting users with the SYSADMIN role to modify the looker_role

-- create a user for looker
create user if not exists looker_user
password = '<enter password here>';
grant role looker_role to user looker_user;
alter user looker_user
set default_role = looker_role
default_warehouse = 'looker_wh';

-- change role
use role SYSADMIN;

-- create a warehouse for looker (optional)
create warehouse if not exists looker_wh

-- set the size based on your dataset
warehouse_size = medium
warehouse_type = standard
auto_suspend = 1800
auto_resume = true
initially_suspended = true;
grant all privileges
on warehouse looker_wh
to role looker_role;

-- grant read only database access (repeat for all database/schemas)
grant usage on database <database> to role looker_role;
grant usage on schema <database>.<schema> to role looker_role;

-- rerun the following any time a table is added to the schema
grant select on all tables in schema <database>.<schema> to role looker_role;

-- create schema for looker to write back to
use database <database>;
create schema if not exists looker_scratch;
use role ACCOUNTADMIN;
grant ownership on schema looker_scratch to role SYSADMIN revoke current grants;
grant all on schema looker_scratch to role looker_role;

注意点は下記の通り。

database.schema
- Lookerが読み込みたいDBを指定するので、Fivetranで作成したDBとスキーマの名前を入力します（今回はFIVETRAN.ZENDESK）
enter password here
- 任意のパスワードを設定

今回もWarehouseが作成されるわけですが、これはLooker側がクエリを発行する（ダッシュボードを見た時も発行される）ため、それを動作させるためのWarehouseになります。Fivetranと違い、デフォルトではMediumサイズが指定されています（Fivetranのような定期的な連携と違い、Lookの構築やExploreの利用でクエリがドシドシ発行されるためだと思います）。

connectionにSnowflakeを設定する

Snowflake側の設定を終えたところで、LookerにSnowflakeを接続させましょう。Adminメニューからconnectionを選んで、Snowflakeを設定します。

注意点としては下記の通り。

Host
- Fivetranと同じですが、https://は不要
DatabaseやUsernameなど
- 上記のクエリで作成したもの
Persistent Derived Tables（永続派生テーブル）
- ONにする
- Temp DatabaseはLOOKER_SCRATCH
Additional Params
- account=[account_name]&warehouse=[warehouse_name]
- account_nameはSnowflakeのHostの.snowflake.comの前の文字列を指定。
- AWSバージョンのSnowflakeの場合、リージョン名が入ってるが、リージョン名は消しておく
- warehouse_nameは上記のクエリで作成したもの
SSL
- ONにする
Database Time Zone
- UTCを選ぶ
- Zendeskの時間データはUTC
Query Time Zone
- JSTを選ぶ
- こうすることで、クエリ実行時に時間を自動でJSTに変換してくれるようになる

テスト接続に成功したらconnectionを保存しましょう。

LookMLプロジェクトを作成する

上記のconnectionを使用して、新規のLookMLプロジェクトを作成します。今回はGenerate Model from Database Schemaで、予めModelとViewを自動定義します。手順自体は下記を参考。

グラフを作成してみる

自動で定義されたLookMLを使って、Look（グラフ）を作ってみます。とりあえず年月別の問い合わせ数グラフを作成してみました。

Ticketビュー

上記で使っているのはTicketビューですが、今回の環境では下記の通り定義されました。

explore: ticket {
  join: organization {
    type: left_outer
    sql_on: ${ticket.organization_id} = ${organization.id} ;;
    relationship: many_to_one
  }

  join: group {
    type: left_outer
    sql_on: ${ticket.group_id} = ${group.id} ;;
    relationship: many_to_one
  }

  join: brand {
    type: left_outer
    sql_on: ${ticket.brand_id} = ${brand.id} ;;
    relationship: many_to_one
  }
}

組織、グループ、ブランドが自動で結合されているので、特に手動でクエリを書いたりしなくても、すぐにブランド別で上記グラフをフィルターする等の操作ができます。