Bigquery create table from Sheet files using Terraform

Question

I'm trying to create a BQ table using Terraform ingesting data from Google Sheets here is my external_data_configuration block

resource "google_bigquery_table" "sheet" {
  dataset_id = google_bigquery_dataset.bq-dataset.dataset_id
  table_id   = "sheet"

  external_data_configuration {
    autodetect    = true
    source_format = "GOOGLE_SHEETS"

    google_sheets_options {
      skip_leading_rows = 1
    }

    source_uris = [
      "https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxx",
    ]
  }

I made the file public but when I try to create the table I get the error:

Error: googleapi: Error 400: Error while reading table: sheet, error message: Failed to read the spreadsheet. Errors: No OAuth token with Google Drive scope was found., invalid

I read Terraform documentation and it seems that I need to specify access_token and scopes in my provider.tf file I just don't know how to do that as I think it will conflict with my current authentication method (service account)

Solution

Add the scopes argument to your provider.tf

provider "google" {
    credentials = "${file("${var.path}/secret.json")}"
    scopes = ["https://www.googleapis.com/auth/drive","https://www.googleapis.com/auth/bigquery"]
    project     = "${var.project_id}"
    region      = "${var.gcp_region}"
}

You need to add the scope for Google Driver and Bigquery

Answer 1

I suspect you only need to supply the scopes, while retaining the existing service account credentials. Service account credential files don't specify scope. Per the terraform documentation, the following scopes are used by default:

> https://www.googleapis.com/auth/compute
> https://www.googleapis.com/auth/cloud-platform
> https://www.googleapis.com/auth/ndev.clouddns.readwrite
> https://www.googleapis.com/auth/devstorage.full_control
> https://www.googleapis.com/auth/userinfo.email

By default, most GCP services accept and use the cloud-platform scope. However, Google Drive does not accept/use the cloud-platform scope, and so this particular feature in BigQuery requires additional scopes to be specified. In order to make this work you should augment the default terraform list of scopes that with the Google Drive scope https://www.googleapis.com/auth/drive (relevant BQ documentation). For a more exhaustive list of documented scopes, see https://developers.google.com/identity/protocols/oauth2/scopes

Access token implies that you've already gone through an authentication flow and supplied the necessary scope(s), so it doesn't make sense that you'd supply both scopes and token. You'd either generate the token with the scopes, or you'd use service account with additional scopes.

Hope this helps.

Answer 2

Example:

resource "google_service_account" "gdrive-connector" {
  project    = "test-project"
  account_id   = "gdrive-connector"
  display_name = "Service account Google Drive transfers"
}

data "google_service_account_access_token" "gdrive-connector" {
  target_service_account = google_service_account.gdrive-connector.email
  scopes                 = ["https://www.googleapis.com/auth/drive", "https://www.googleapis.com/auth/bigquery"]
  lifetime               = "300s"
}

provider "google" {
  alias        = "gdrive-connector"
  access_token = data.google_service_account_access_token.gdrive-connector.access_token
}

resource "google_bigquery_dataset_iam_member" "gdrive-connector" {
  project    = "test-project"
  dataset_id = "test-dataset"
  role       = "roles/bigquery.dataOwner"
  member     = "serviceAccount:${google_service_account.gdrive-connector.email}"
}

resource "google_project_iam_member" "gdrive-connector" {
  project  = "test-project"
  role     = "roles/bigquery.jobUser"
  member   = "serviceAccount:${google_service_account.gdrive-connector.email}"
}

resource "google_bigquery_table" "sheets_table" {
  provider   = google.gdrive-connector
  project    = "test-project"
  dataset_id = "test-dataset"
  table_id   = "sheets_table"


  external_data_configuration {
    autodetect    = true
    source_format = "GOOGLE_SHEETS"

    google_sheets_options {
      skip_leading_rows = 1
    }

    source_uris = [
      "https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxx/edit?usp=sharing",
    ]
  }
}