Ritolabo
  1. Home
  2. GoogleCloud
  3. BigQuery
  4. Google Spreadsheet のデータを BigQuery に取り込む｜外部テーブル + スケジュールドクエリを Terraform で構築

Google Spreadsheet のデータを BigQuery に取り込む｜外部テーブル + スケジュールドクエリを Terraform で構築

  • 公開日
  • カテゴリ：BigQuery
  • タグ：BigQuery,ExternalTable,ScheduledQuery,Terraform
Google Spreadsheet のデータを BigQuery に取り込む｜外部テーブル + スケジュールドクエリを Terraform で構築

BigQuery Data Transfer Service には Google Spreadsheet コネクタが存在しない。Spreadsheet のデータを BigQuery に取り込むには、外部テーブルとスケジュールドクエリを組み合わせる方法が Google Cloud のサービスだけで完結し、コードも不要で手軽に実現できる。

本記事では、この構成を Terraform で構築する。

contents

  1. 構成
    1. 外部テーブル
    2. スケジュールドクエリ
    3. なぜネイティブテーブルに書き込むか
  2. 前提
  3. Spreadsheet の準備
    1. サンプルデータ
    2. サービスアカウントへの共有
  4. ディレクトリ構成
  5. Step 1: API の有効化
    1. api.tf に追記
  6. Step 2: spreadsheet module の作成
    1. module の呼び出し
    2. spreadsheet/variables.tf
  7. Step 3: データセットと IAM
    1. spreadsheet/dataset.tf
  8. Step 4: 外部テーブル
    1. spreadsheet/external_table.tf
    2. 動作確認
  9. Step 5: スケジュールドクエリ
    1. spreadsheet/scheduled_query.tf
    2. 手動実行
    3. 動作確認
  10. 適用

構成

Google Spreadsheet
  │
  │ 外部テーブルとして参照（Google Drive 連携）
  ↓
BigQuery 外部テーブル（dl_spreadsheet.ext_product_categories）
  │
  │ スケジュールドクエリで定期的に WRITE_TRUNCATE
  ↓
BigQuery ネイティブテーブル（dl_spreadsheet.product_categories）

外部テーブル

BigQuery から Google Drive 上の Spreadsheet を直接参照する仕組み。テーブル作成時に Spreadsheet の URL を指定すると、クエリ実行のたびにシートから最新データを読み取る。

スケジュールドクエリ

BigQuery の機能で、指定した SQL を定期的に自動実行する。外部テーブルからデータを読み取り、ネイティブテーブルに WRITE_TRUNCATE（全件洗い替え）で書き込む。

なぜネイティブテーブルに書き込むか

外部テーブルだけでも参照は可能だが、datalake 層として安定したスナップショットを持つためにネイティブテーブルに書き込む。

外部テーブル（参照のみ）ネイティブテーブル
データの所在Spreadsheet 上BigQuery ストレージ上
速度遅い（毎回シートを読む）速い
dwh/dm 層からの参照不安定（シート側の変更に即影響）安定（スナップショット）

前提

  • GCP プロジェクトが作成済み
  • Terraform がインストール済み
  • gcloud で認証済み
  • Google Spreadsheet にデータが入力済み
  • Dataform 環境を Terraform で構築する の構成が適用済み（サービスアカウント dataform-executor が存在する状態）

Spreadsheet の準備

サンプルデータ

本記事では、以下の商品カテゴリマスタを使用する。

idcategory_namedisplay_order
1家電1
2書籍2
3食品3

Google Spreadsheet に上記データを入力しておく。

サービスアカウントへの共有

BigQuery が Spreadsheet にアクセスするために、サービスアカウント（SA）に閲覧権限を付与する。

  1. Spreadsheet の「共有」を開く
  2. SA のメールアドレスを 閲覧者 として追加
<YOUR-SA-NAME>@<YOUR-PROJECT-ID>.iam.gserviceaccount.com

Spreadsheet を外部公開する必要はない。SA に共有するだけでよい。

この共有設定は Google Drive の権限であり、Terraform では管理できない。手動で行う。

ディレクトリ構成

前回の記事で構築した Terraform に spreadsheet/ module を追加する。

terraform/
├── versions.tf
├── main.tf              # module "spreadsheet" 追加
├── variables.tf
├── terraform.tfvars
├── api.tf               # BigQuery Data Transfer API 追加
├── outputs.tf
│
├── dataform/            # 既存
│   ├── variables.tf
│   ├── service_account.tf
│   ├── secret_manager.tf
│   ├── dataform.tf
│   └── outputs.tf
│
└── spreadsheet/         # 新規
    ├── variables.tf
    ├── dataset.tf
    ├── external_table.tf
    └── scheduled_query.tf

Step 1: API の有効化

スケジュールドクエリの実行には BigQuery Data Transfer API が必要。

api.tf に追記

# スケジュールドクエリ
resource "google_project_service" "bigquery_data_transfer" {
  service            = "bigquerydatatransfer.googleapis.com"
  disable_on_destroy = false
}

Step 2: spreadsheet module の作成

module の呼び出し

ルートの main.tf に module 呼び出しを追加する。

# main.tf に追記
module "spreadsheet" {
  source                  = "./spreadsheet"
  project_id              = var.project_id
  region                  = var.region
  dataform_executor_email = module.dataform.dataform_executor_email
}

dataform_executor_email は dataform module の output から受け取る。既存の SA を共用する構成。

spreadsheet/variables.tf

variable "project_id" {
  description = "GCP プロジェクト ID"
  type        = string
}

variable "region" {
  description = "デフォルトリージョン"
  type        = string
}

variable "dataform_executor_email" {
  description = "Dataform 実行用サービスアカウントのメールアドレス"
  type        = string
}

Step 3: データセットと IAM

Spreadsheet 取り込み用のデータセット dl_spreadsheet を作成し、SA に編集権限を付与する。

spreadsheet/dataset.tf

# Spreadsheet 取り込み用データセット
resource "google_bigquery_dataset" "dl_spreadsheet" {
  dataset_id = "dl_spreadsheet"
  location   = var.region
}

# SA に dl_spreadsheet への編集権限を付与
resource "google_bigquery_dataset_iam_member" "dl_spreadsheet_editor" {
  dataset_id = google_bigquery_dataset.dl_spreadsheet.dataset_id
  role       = "roles/bigquery.dataEditor"
  member     = "serviceAccount:${var.dataform_executor_email}"
}

SA にはスケジュールドクエリの宛先テーブルへの書き込み権限（dataEditor）が必要。

Step 4: 外部テーブル

Google Spreadsheet を参照する外部テーブルを作成する。

spreadsheet/external_table.tf

# 外部テーブル: Google Spreadsheet を参照
resource "google_bigquery_table" "ext_product_categories" {
  dataset_id  = google_bigquery_dataset.dl_spreadsheet.dataset_id
  table_id    = "ext_product_categories"
  description = "商品カテゴリマスタ（Google Spreadsheet 外部テーブル）"

  schema = jsonencode([
    { name = "id", type = "INTEGER", mode = "NULLABLE", description = "カテゴリID" },
    { name = "category_name", type = "STRING", mode = "NULLABLE", description = "カテゴリ名" },
    { name = "display_order", type = "INTEGER", mode = "NULLABLE", description = "表示順" },
  ])

  external_data_configuration {
    source_format = "GOOGLE_SHEETS"
    autodetect    = false
    source_uris   = ["https://docs.google.com/spreadsheets/d/xxxxx"]

    google_sheets_options {
      skip_leading_rows = 1
    }
  }
}
項目説明
source_formatGOOGLE_SHEETS を指定
source_urisSpreadsheet の URL
autodetectfalse にしてスキーマを明示定義
skip_leading_rowsヘッダー行をスキップ
schemaカラム名・型・説明を定義

Spreadsheet の URL はインフラ定義の一部であるため、terraform.tfvars ではなく Terraform コード内に直接記載する。

動作確認

適用後、BigQuery コンソールで外部テーブルを確認できる。

SELECT * FROM `<YOUR-PROJECT-ID>.dl_spreadsheet.ext_product_categories`;

Spreadsheet のデータが返ってくれば成功。Spreadsheet 側のデータを変更して再度クエリを実行すると、変更が即座に反映される。

Step 5: スケジュールドクエリ

外部テーブルのデータをネイティブテーブルに定期書き込みするスケジュールドクエリを作成する。

spreadsheet/scheduled_query.tf

# スケジュールドクエリ: 外部テーブル → ネイティブテーブルへ定期書き込み
resource "google_bigquery_data_transfer_config" "sync_product_categories" {
  display_name           = "sync_product_categories"
  location               = var.region
  data_source_id         = "scheduled_query"
  destination_dataset_id = google_bigquery_dataset.dl_spreadsheet.dataset_id

  schedule = "every day 00:00" # UTC 00:00 = JST 09:00

  params = {
    query = "SELECT * FROM `${var.project_id}.${google_bigquery_dataset.dl_spreadsheet.dataset_id}.${google_bigquery_table.ext_product_categories.table_id}`"
    destination_table_name_template = "product_categories"
    write_disposition               = "WRITE_TRUNCATE"
  }

  service_account_name = var.dataform_executor_email
}
項目説明
data_source_idscheduled_query を指定（スケジュールドクエリ）
destination_dataset_id書き込み先データセット
schedule実行スケジュール。every day 00:00 は UTC 00:00 = JST 09:00
destination_table_name_template書き込み先テーブル名
write_dispositionWRITE_TRUNCATE で全件洗い替え
service_account_nameクエリ実行に使用する SA

手動実行

スケジュールを待たずに手動で実行するには、BigQuery コンソールの「スケジュールされたクエリ」から対象を選び、「スケジュール バックフィルを実行」で日付範囲を指定して実行する。

動作確認

実行後、ネイティブテーブルにデータが書き込まれていることを確認する。

SELECT * FROM `<YOUR-PROJECT-ID>.dl_spreadsheet.product_categories`;

適用

cd terraform

terraform init    # spreadsheet module の初期化
terraform plan
terraform apply

Plan: 5 to add で以下のリソースが作成される。

リソース説明
BigQuery Data Transfer APIスケジュールドクエリに必要
dl_spreadsheet データセットSpreadsheet 取り込み用
SA の IAM（dataEditor）dl_spreadsheet への書き込み権限
外部テーブル（ext_product_categories）Spreadsheet を参照
スケジュールドクエリ（sync_product_categories）定期書き込み

まとめ

Terraform で作成したリソースの全体像。

terraform/
├── api.tf
│   └── BigQuery Data Transfer API（追加）
│
└── spreadsheet/（module）
    ├── dataset.tf
    │   ├── dl_spreadsheet データセット
    │   └── SA の IAM（dataEditor）
    │
    ├── external_table.tf
    │   └── 外部テーブル（Spreadsheet → BigQuery）
    │
    └── scheduled_query.tf
        └── スケジュールドクエリ（外部テーブル → ネイティブテーブル）

BigQuery Data Transfer Service に Spreadsheet コネクタは存在しないが、外部テーブル + スケジュールドクエリの組み合わせで Google Cloud のサービスだけで定期取り込みが実現できる。Terraform で管理することで、取り込み対象の Spreadsheet が増えた場合もコードベースで管理・レビューが可能になる。

Author

rito

rito

  • Backend Engineer
  • Tokyo, Japan
  • PHP 5 技術者認定上級試験　認定者
  • 統計検定 3 級