BigQueryからGoogle スプレッドシートのデータをクエリする方法について解説。BigQueryのデータをスプレッドシートに出力するのではなく、ここではGoogle スプレッドシートのデータをBigQueryと連携させて、スプレッドシートにあるデータをBigQueryから参照する方法となる。そのため、スプレッドシートのデータをBigQueryにインポートさせるのではなく、あくまでデータ自体はスプレッドシートにあり、それをBigQueryからクエリし結果を得る手順となる。

参照：ドライブデータをクエリする – Google Cloud

スプレッドシートとBigQueryを連携

エクスプローラの右側にある[追加]もしくは、データセットの[3点の丸] > [テーブルを作成]から設定が可能。事前に連携先のデータセットは作成しておくこと。

テーブルを作成

• テーブルの作成元
  ドライブ
• ドライブのURIを選択
  スプレッドシートのURIをコピペして貼り付ける
  https://docs.google.com/spreadsheets/d/FILE_ID
• ファイル形式
  Google スプレッドシート
• シート範囲
  スプレッドシートのシート名
• プロジェクト
  プロジェクト名
• データセット
  データセット名
• テーブル
  作成するテーブル名
• スキーマ
  チェックを外し手動で入力（スプレの列名と型を選択）
• 詳細オプション > スキップするヘッダー行
  1

テーブルの詳細を確認

指定したデータセットの中にテーブルが作成されていることを確認する。詳細タブを開くと、[ソースURI]に読み込み先のスプレッドシートのURIが表示されていることを確認する。通常のBigQueryテーブルとは異なりプレビューは表示されない。連携が正常にできているか以下のクエリを確認する。FROM句にはテーブルIDをそのまま貼り付けばよい。

SELECT
  *
FROM
  `<project>.<dataset>.<table>`

うまく連携できない場合

スプレとの連携は慣れていなければスムーズに連携できることの方が珍しい。大抵うまく連携できず何が問題なのかつまずくことが多いが、以下の点をチェックしてみてほしい。

• 日付のフォーマット
  日付を取り組む際、型でDATEを指定するが、BigQueryにおける日付のフォーマットはYYYY-MM-DDであるためハイフンが入っていないと取り込めない。
  参照：Date type – Google Cloud
• ヘッダーのスキップ
  大抵の場合、1行目には列名が記載されているので、この1行目は[スキップするヘッダー行]で1を指定しスキップさせる必要がある。
• スプレッドシートへのアクセス権限
  そもそもスプレにアクセスできる権限がないと連携はできない

スプレッドシート連携の活用事例

データソースはスプレッドシートにあり、それをBigQueryでクエリする用途は多々ある。同じGoogleの製品なので連携がスムーズでスプレッドシートの共有のしやすさから、BigQueryとスプレッドシートを連携したい場面は多い。

連携したデータのレプリカを作成しデータを実体化

スプレッドシートとBigQueryを連携するとデータの存在場所はスプレッドシートにあるが、新規テーブルとして複製するとBigQuery側にデータをコピーして実体化させることが可能になる。元々のスプレッドシートにはRawデータが格納されているものを、整形した上でレプリカを作成するとLooker Studioで可視化する際もレスポンスの改善がみられるなどメリットが得られる。ただ、この場合、データをコピーしているために、スプレッドシートのデータと同期させるためにはCREATE文をその都度クエリのスケジューリング等で定期実行させる必要がある。

CREATE OR REPLACE TABLE `<project>.<dataset>.<Reprica_table>` AS
SELECT
  *
FROM
  `<project>.<dataset>.<table>`
;