bigqueryでgoogle sheetsのデータを参照する方法

概要

• google sheetsでまとめたデータをbigqueryで参照・使用できるように設定する方法
• SQLでデータを参照する設定と、コマンドでデータを参照する設定がある　
  • コマンドで参照は使用するコマンドが多く手順が複雑

SQLでgoogle sheetsのデータを参照する方法

Google Drive統合の有効化

$ bq --project_id=<PROJECT_ID> update --location=US --connection GoogleDriveConnectionName

テーブル作成

CREATE EXTERNAL TABLE `xxx.yyy.zzz` -- テーブル名
( col1 STRING, col2 STRING, col3 STRING, col4 STRING ) -- カラム情報を明示的に定義
OPTIONS (
  format = 'GOOGLE_SHEETS',
  uris = ['https://docs.google.com/spreadsheets/d/*****************************/edit?gid=0#gid=0'],
  skip_leading_rows = 1
);

コマンドでgoogle sheetsのデータを参照する方法

手順

• gcloud コマンドでgoogle driveへのアクセスを許可する
• bq mkdef コマンドで定義テンプレートを作成する
• 定義テンプレートを編集し/google sheets/の参照先シート・参照範囲と型を指定する
• bq mkコマンドで定義テンプレートを参照しテーブルを作成

google driveへのアクセスを許可

# gcloud auth loginを使用する場合
$ gcloud auth login --enable-gdrive-access
# gcloud auth application-default loginを使用する場合
$ gcloud auth application-default login --scopes=https://www.googleapis.com/auth/cloud-platform,https://www.googleapis.com/auth/bigquery,https://www.googleapis.com/auth/drive

bq mkdefコマンドで定義テンプレートを作成する

$ bq mkdef <url> > bq_def.json

定義テンプレートを編集しgoogle sheetsの参照先シート・参照範囲と型を指定する

• sourceUris: google sheetsのURL
• googleSheetsOptions : range: 参照するシート名・カラム
• schema: 型

{
  "sourceFormat": "GOOGLE_SHEETS",
  "sourceUris": [
    "https://docs.google.com/spreadsheets/d/1wTr9fmtF3YG_lQMehYjAnDzgKmJ92g-8yvFtYctrbB4"
  ],
  "googleSheetsOptions":{
    "range":"Sheet1!A:C",
    "skipLeadingRows":1
  },
  "schema":{
    "fields":[
      {"name":"name",    "type":"STRING", "mode":"NULLABLE"},
      {"name":"sex",     "type":"STRING", "mode":"NULLABLE"},
      {"name":"age",     "type":"STRING", "mode":"NULLABLE"},
    ]
  }
}

bq mkコマンドで定義テンプレートを参照しテーブルを作成

$ bq mk --external_table_definition="./bq_def.json" \
    <project-name>:<dataset-name>.<table-name>
# 成功時のメッセージ
# Table 'xxxx:yyyy.zzzz' successfully created.

確認

SELECT
  *
FROM
  `xxxx.yyyy.zzz`

name,      sex,    age
suzuki,    male,   20
yamada,    female, 21
kobayashi, female, 18

参考

• GoogleスプレッドシートをデータソースとするBigQuery外部テーブルをCLIで作成する