この記事は公開されてから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まで）
- Snowflakeの管理画面を開いた時のURLを入力する（
- 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）
- Lookerが読み込みたいDBを指定するので、Fivetranで作成したDBとスキーマの名前を入力します（今回は
- enter password here
- 任意のパスワードを設定
今回もWarehouseが作成されるわけですが、これはLooker側がクエリを発行する（ダッシュボードを見た時も発行される）ため、それを動作させるためのWarehouseになります。Fivetranと違い、デフォルトではMediumサイズが指定されています（Fivetranのような定期的な連携と違い、Lookの構築やExploreの利用でクエリがドシドシ発行されるためだと思います）。
connectionにSnowflakeを設定する
Snowflake側の設定を終えたところで、LookerにSnowflakeを接続させましょう。Adminメニューからconnectionを選んで、Snowflakeを設定します。
注意点としては下記の通り。
- Host
- Fivetranと同じですが、
https://は不要
- Fivetranと同じですが、
- 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から作るシステムの設計をしている間に、サービス申し込み～利用開始が完了する時代になってきました。
24
