Google SpreadsheetsのデータをSnowflakeに自動同期し、Lookerで可視化する

## 概要
データを取り扱う業務の中で、Google Spreadsheets(スプレッドシート)を使っている方も多いと思います。
ただ、Google Spreadsheetsにアップロードされたデータを分析する際、更新されるたびにデータを転送して最新の状態に保つのには手間がかかります。
そこで今回は、[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=spreadsheets_to_snowflake)という分析基盤向けデータ統合サービスを使い、Google Spreadsheetsデータの抽出自動化＋DWH(データウェアハウス)への統合＋可視化までやってみようと思います。

今回、データの転送手段として採用した[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=spreadsheets_to_snowflake)は、Google Spreadsheetsの他にも、様々な広告・CRM・DBなどのデータソースにも対応しています。
[troccoの使い方まとめ（CRM・広告・データベース他）](https://qiita.com/hiro_koba_jp/items/2b2caa040804e402bda7)
![spsnow.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/c26de126-58dc-b733-cbde-1d045134fcb7.png)




## ゴール

下のようなGoogle Spreadsheetsのデータ[^1]を

[^1]: [e-stat](https://www.e-stat.go.jp/stat-search/files?page=1&layout=datalist&toukei=00200573&tst[…]nth=12040605&tclass1=000001085955&result_back=1&tclass2val=0)から消費者物価指数データを引用しました。

![spデータ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/d13bfa40-45f6-f0ad-e942-c77e7a60e179.png)




Snowflakeに結合し、Lookerで可視化します(作成後は自動で最新値に更新することも可能です）
![データ可視化①.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/e75cc3b1-3bc4-2e7e-5270-f2451b4f327c.png)


## こんな人におすすめ
* スプレッドシートに集まるデータを毎回DWHに転送するのを面倒に感じている方
* Googleフォームを利用したアンケートの結果などを自動的に分析できるようにしたい方
* LookerでGoogle Spreadsheetsのデータを分析したい方


## 1. troccoでGoogle Spreadsheets→Snowflakeの転送自動化
### 1-0. 事前準備
データの転送のためには[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=spreadsheets_to_snowflake)のアカウント・Googleのアカウントが必要です。
無料トライアルを実施しているので、事前に申し込み・登録しておいてください！
[https://trocco.io/lp/index.html](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=spreadsheets_to_snowflake)
（申込の際に、この記事を見た旨を記載して頂ければご案内がスムーズに行えます）
### 1-1. 転送元・転送先を決定
[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=spreadsheets_to_snowflake)にアクセスして、ダッシュボードから「転送設定を作成」のボタンを押します。
![ダッシュボード.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/9da76a65-4f0e-1a7b-98f1-c435752ff2b3.png)

転送元に「Google Spreadsheets」を指定し、転送先に「Snowflake」を選択して転送設定作成ボタンを押します。
![転送新規作成.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/ae0e1b22-b4bd-329c-a22f-f5f9c2da7e3c.png)





すると、設定画面になるので、必要な情報を入力していきます。
### 1-2. Goolge Spreadsheetsとの連携設定
あとで見たときに自分で分かるように転送設定の名前とメモを入力します。
![転送設定の新規作成.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/a837c64c-c317-4c9e-669c-33e4b53f6306.png)


次に「転送元の設定」内の「接続情報を追加」ボタンを押します。
![接続情報追加.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/245caecd-79e2-1d52-a89c-256e4bc931af.png)


別タブでGoolge Spreadsheetsの接続情報の新規作成画面が開きます。
接続情報を作成するためにGoolgeのアカウントと連携する必要があります。
![spread接続情報.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/0d996e10-e32b-bff1-8cf6-b5d7680aefca.png)



再度転送設定画面に戻り、接続情報の「再読込」ボタンを押すと、先ほど作成した接続情報が選択できるようになります。
![Qiita追加.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/61d1eb01-1c3e-274c-c6cf-27e2b11a77a7.png)


これでGoolge Spreadsheetsとの連携は完了です。

### 1-3. Goolge Spreadsheetsからのデータ抽出設定
次に、どのようなデータを取得するかを設定していきます。

転送先のGoogle SpreadsheetsのスプレッドシートIDとシート名を入力します。

- スプレッドシートURL
    - 取得したいスプレッドシートのURLを入力します。

- シート名
    - 取得したいシートの名前を入力します。

- 取り込み開始行番号
    - 取り込むデータの最初の行番号を入力します。
    - ヘッダ行がある場合、ヘッダ行を含めずデータの最初の行番号を指定してください。

- デフォルトタイムゾーン
    - デフォルトタイムゾーンを指定します。

- NULL文字列
    - NULLと認識させたい文字列を入力します。

- カラム設定
    - 取得したいカラムのカラム名とデータ型を指定します。



![sp設定.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/b645acc7-56cc-3365-ac6d-9337174b6ed4.png)




### 1-4. 転送先Snowflakeの設定
転送元と同様に設定していきます。Snowflakeの接続情報を作成していきます。
転送先となるSnowflakeの接続先ホスト、ユーザー名、及びパスワードが必要になります。


![snowflake追加.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/1fa11954-2168-7e2d-879c-042a36d080d9.png)

先ほど作成した接続情報を選択し、転送先とするウェアハウス、データベース名、スキーマ名を設定します。
転送モードに関しては`replace`を選択します。各転送モードの詳細は[こちら](https://docs.trocco.io/s/article/転送先-Snowflake)の「転送モードについて」をご確認ください。

![snow設定.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/c210f4aa-534f-26e4-b844-85c2489bc101.png)


これで入力は完了です。「保存して自動データ設定・プレビューへ」をクリックし、確認作業に進みましょう。

### 1-5. データのプレビュー
少し待つと、転送元のデータがプレビューされます。ここではGoogle Spreadsheetsから取り込んだデータが表示されています。
![spsnプレビュー.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/0eb49df3-8ffd-2bbc-2828-c4a7a0ebeba8.png)


転送したいデータが取れているので、このまま「スケジュール・通知設定」に進みます。

### 1-6. スケジュール・通知設定
「スケジュール・トリガー設定」タブを開きます。
![spスケ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/89eac426-a568-8fe7-56a9-158b66eefb7d.png)


「スケジュールを追加」ボタンを押すと、以下の画像のような入力欄が出てきます。ここで実行スケジュールを設定することで、転送を定期的に実行し自動化することが出来ます。



### 1-7. データ転送ジョブの実行
設定は以上です。最後に、手動で転送ジョブを実行し、Snowflakeにデータを送ります。
手動で実行する場合はジョブ詳細画面の「実行」ボタンを押します。
![spスケ追加.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/21c26f4f-23d0-247c-5ae7-ed251b5c1b7a.png)



これで転送は完了です！

## 2. Snowflakeの設定
特に設定することありません。データが転送されているので、今すぐに分析・可視化を行うことが出来ます。
データがきちんと送られているかは「データをプレビュー」押すことで確認してみます。
![snプレ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/6e3a0ed6-66d8-2661-aeaf-fb48c3acec5b.png)

転送されていることが確認できました！
## 3. Lookerで可視化
それでは、これらのデータをLookerで可視化していきます。  
まずはSnowflakeとLookerを接続の設定を行います。
管理タブを開いて「データベース」の「接続」を開きます。
![接続選択.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/7fab6490-acee-6690-a728-d3742491d9f3.png)


接続しているデータベース一覧が表示されています。ここで「Add Connection」→「Database Connection」から接続するデータベース情報を入力します。
![addconnection.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/0fb758d6-073d-48db-47a7-28ba6b6fe234.png)
![loco.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/0838e32f-780c-fc99-8ae5-a37e3a12ef17.png)




Snowflakeのデータベースに接続できたら、次はデータを可視化するために必要なLookMLプロジェクトを作成していきます。 開発タブを開いて「LookMLプロジェクトの管理」に移動します。 

![プロジェクトの管理.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/4a6bf16b-e4e2-5b4a-401f-4b7ba6c7f9ab.png)

「New LookML Project」からLookMLプロジェクトを作成します。 
![newlookerml.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/04bb62b5-3bbc-b6b3-4508-96a35ee93f53.png)

![プロジェクト.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/313391ec-63f3-da97-cc56-6cce78410f85.png)


「Create Project」を押したら、エディタでmodelとviewを定義します。 後々必要になるので、modelの中ではexploreを設定しておきましょう。 (書き方が分からない場合はLookerの[公式ドキュメント](https://docs.looker.com/ja/data-modeling/getting-started/model-development)を参照してください)
![modelsp.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/d36539b5-b1c9-ae6a-5a1d-163fde8f556d.png)
![viewsp.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/03559708-6fc9-d557-51fc-e814a383c50e.png)




これで下準備が整いました。 トップページに戻って「New」からDashboardを作成します。 
![newdash.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/9bf025e6-72f2-b47d-0614-fd1256d87c20.png)

白紙のダッシュボードが作成されるので「spreadsheets_ダッシュボード」と名前をつけます。
そして「Dashborardの編集」→「タイルの追加」を選択し、先ほどのmodel内で定義したExploreを選択します。

![sp選択.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/0c149526-f82e-54b3-66f7-31e93deb80d0.png)




ディメンションとメジャーを設定し、Tileに表示したいデータをプロットします。例えば、年別の加工食品の消費者物価指数を分析したいときはディメンションに「年度」とメジャーに「加工食品」を選択します。

![加工食品.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/c03662d8-c118-120a-a535-a2b7d8e97e94.png)




そのほかに必要なタイルを作成し、ダッシュボードにまとめていきます。
![データ可視化①.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/741ad7ff-fe2e-5bb9-e015-a5b79c92eafb.png)

完成しました！


## まとめ
いかがでしたでしょうか。[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=spreadsheets_to_snowflake)を使うとGoogle Spreadsheetsの管理画面を触ることなく、簡単にデータを取得し、DWH(Snowflake)に貯めることが出来ます。
Snowflakeにデータを貯めると、Lookerと連携することでデータを使ってグラフを作り、可視化できます。
実際に弊社サービスの[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=spreadsheets_to_snowflake)においても、マーケティングKPI等をこのような流れで収集・分析しています。
ぜひ広告データ分析の際にはご活用ください。
[https://trocco.io/lp/index.html](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=spreadsheets_to_snowflake)

実際に試してみたい場合は、[無料トライアル](https://trocco.io/inquiry_trial/new?utm_medium=social&utm_source=qiita&utm_campaign=spreadsheets_to_snowflake)を実施しているので、この機会にぜひ一度お試しください。（申込時に、この記事を見た旨を記載して頂ければスムーズにご案内することができます）

その他にも広告やデータベースなど、様々な分析データをETL・転送した事例をまとめました。
[troccoの使い方まとめ（CRM・広告・データベース他）](https://qiita.com/hiro_koba_jp/items/2b2caa040804e402bda7)