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

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

最近、データ分析界隈で話題の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図を公開してくれています。分析する上で非常に助かります。

ちなみに、連携時にちゃんと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
  }
}

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

今回の環境ならではのいいところ

インフラ管理一切不要

最初に記載したとおり、今回使用しているサービスは全てフルマネージドサービスなので、インフラ側の運用保守はいりません。

何も意識せずとも自動連携される

Lookerが見に行っているDBはFivetranで定期的に更新されるため、何もしなくても、Lookerのダッシュボードに表示されるデータは常に最新のものとなります(任意のフィルタをかけている場合は別)。

Zendesk以外のサービスのデータも分析可能

今回、Snowflakeにデータを入れているのはFivetranです。つまり、Fivetranが対応しているサービスであれば、Zendesk以外のデータも、今回と同様の方法で分析することができます。Salesforceなんかもできるでしょう。

LookMLでデータをしっかり定義

これだけお手軽にデータをDBに連携できちゃうと、メンバーそれぞれがオレオレダッシュボードを量産しがちになりますが、自動連携されるデータは、そのままLookerのLookMLでしっかり定義できるため、各関係者によるオレオレダッシュボードが量産される心配はありません。

スペックの増強がすぐできる

フルマネージドサービスのため、スペックの増減もお手軽に可能です。

  • データ連携が遅いと感じた時
    • SnowflakeのFivetran用のWarehouseのサイズを大きくする
  • ダッシュボードの描画が遅いと感じたとき
    • SnowflakeのLooker用のWarehouseのサイズを大きくする
    • Looker側のインスタンスの増強を検討する

おわりに

AWSもそうですが、今後はこういった「既にあるものを組み合わせてシステムを構築する」という動きがどんどん盛んになると思います。1から作るシステムの設計をしている間に、サービス申し込み~利用開始が完了する時代になってきました。