こんにちは。
システム開発部ネットワーク課のsupercontinueです。
はじめに
- GoogleSpreadSheetからBigQueryへ接続し、データを参照・更新できるようになりました。
- BigQueryにあるゲームのユーザーログなどをSpreadSheetで分析・共有できます。
- 「SQLは詳しくないけどSpreadSheetは使える」というライトな利用者でもビッグデータが活用できます。
- Googleによるマニュアルがあります。
まずは接続
たったこれだけでBigQueryのデータにアクセスできます！
- SpreadSheetを開く。
- メニューで [データ] 次の操作 [データコネクタ] 次の操作 [BigQuery に接続] をクリック。
- ダイアログに従ってBigQueryのプロジェクトを選ぶ。（対象へのアクセス権が必要です）
- データセット、テーブルを選ぶ。
もしくはSQLクエリーで抽出することもできる。
- コネクテッドシートと呼ぶシートが追加されます。
巨大なテーブルも接続可能！
1億レコード以上あるテーブルを接続してみました。
特に問題ないようです。時間がかかるということもありませんでした。
読めないテーブルがある？
一方でエラーで読めないテーブルがありました。
が、理由がわかりませんでした。
コネクテッドシートの機能
コネクテッドシートには下記のメニューがあります。
グラフ
コネクテッドシートのデータからグラフを作成します。
使用頻度は高いと思います。
BigQueryにあるすべてのデータが対象となるのがメリットです。
フィルタで範囲や条件を指定できるので、グラフを作るときには便利そうです。
あらかじめグラフ化しやすいテーブルを用意しないと使いにくいと思います。
ピボットテーブル
指定した条件で集計して結果を別のシートに出力できます。
使用頻度は高いと思います。
ただし、結果が最大行数は25,000行という制限があります。
以下の例では、毎日のガチャの種類と回数のレコードから、ガチャの種類ごとの累計回数をとります。
関数
コネクテッドシートのデータに計算式を適用して別のシートを作成します。
たとえば、最大とか平均とかを表示したいときに使います。
ただし、単純な合計値や平均値を確認するだけなら、「列の統計情報」を見る方が簡単です。
生成されたシート式のセルは =MAX(sc_daily_scout_dia!count) のようになっており、コネクテッドシートの値を普通のシートから参照できるようです。
抽出
条件を指定しコネクテッドシートからデータを抽出しシートを作成します。
使用頻度は高いと思います。
ただし、結果の最大行数は25,000行という制限があります。
以下の例では、特定の日付のレコードを抽出します。
＋計算された列
コネクテッドシートに列を追加できます。
あまり使い勝手が良い機能とは思えません。
計算で別のシートのデータを参照することはできません。（たぶん）
コネクテッドシートないにある列に対して指定された関数の組み合わせしか使えないようです。（たぶん）
列の統計情報
自動でピボットテーブルやグラフのおすすめみたいなのを出せますが、合計値や平均値以外に期待したものが出ることはほぼないと思います。
積極的に使える場面はないと思いますが、特定の様式のテーブルなら良い感じになるのかもしれません。
利用上のコツ
BigQueryの料金
BigQueryのデータを参照すると、データ量に応じて費用が発生します。
データあたりのコストは低めなので、小さいデータを少ない回数参照するならタダみたいなものですが、大きなデータのすべての行何回も参照するとチリツモで無視できないコストになるでしょう。
何かの操作をするとき大抵事前にデータ量が表示されるので、目安にはなります。
あまりビビる必要はないです。
料金が目に見えるようになるのは、数百億行とかを処理する場合です。数万程度のデータはタダみたいなものです。
費用を抑えるには
- BigQuery側のデータは使いやすい・サイズを小さくするようにあらかじめ集計しておくのがベターです。
- コネクテッドシートから普通のシートに抽出し（上限25,000行）、それから分析やグラフ化をするのがベターです。
扱いやすいデータにする
- あらかじめBigQueryに扱いやすい集計済みデータを生成しておくのがベターです。
- 集計はBigQueryで、スプレッドシートでは分析や表示をしましょう。
自動更新をうまく使う
- BigQueryを使ったシートを日常的に共有して使う場合（KPIを毎日見るとか）の場合は、「更新スケジュール」を積極的に使いましょう。
- 「手動で更新」をしないとデータが古く、意図しない結果をみている場合がありますし、「更新」ボタンを押すのも面倒です。
- デイリーのKPIなら、1日1回更新するようにスケジュールをしておけば便利でしょう。
コネクテッドシートで作成したグラフや抽出したシートにも「更新スケジュール」を設定できます。
データポータルとの使い分け
データポータルは「アドホックでない分析や閲覧」をするのが向いています。
インタラクティブではあるけど、それはビューやフィルタ程度に止めるのが良いと思います。
データポータルなどは「KPIなど毎回同じフォーマットのデータを継続して見るための道具」といえるでしょう。
また、データポータルでビューを整えるのはそれなりに時間がかかりますし、そもそも習熟するための時間が必要です。
SpreadSheetではSQLが苦手な人がアドホックな分析をしたいときに便利です。
スポットでいつもと違った分析をしたい、すばやく・細かい・掘り下げた分析をしたいときに便利です。
SpreadSheetを使い慣れた人なら、集計・分析はしやすいと思います。
データの同期
- コネクテッドシートでBigQueryのデータを編集することができます。ただし、明示的に「更新」する必要があります。
- また、BigQuery側でSpreadSheetを外部データソースとするテーブルが作れます。今回紹介したものの逆パターンです。
- 個人的には、どちらも特殊な場合を除き、アンチパターンな気がします。
できないこと
- 通常のシートにもってくるときに、25,000行以上はエラーになります。
- 読み込めない・指定できない場合など、一般的にSQLだと詳しいエラーやメッセージが出るのに比べて、SpreadSheetでの操作では詳細なエラーログやメッセージが無いので簡単には理由がわかりません。
リベル・エンタテインメントでは、このような最新技術などの取り組みに興味のある方を募集しています。もしご興味を持たれましたら下記サイトにアクセスしてみてください。
https://liberent.co.jp/recruit/
