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

Radiosurgery

thanks for taking the time to share a solution! super helpful – Sometime

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.

Tatiana

Answer 2

Here is the provider config that solved it for me: it keeps the default one and adds the minimum readonly scope required by bigquery to read sheets.

provider "google" {
    project = "project-id"
    region  = "region"
    zone    = "zone"
    scopes = [
        "https://www.googleapis.com/auth/cloud-platform",
        "https://www.googleapis.com/auth/userinfo.email",
        "https://www.googleapis.com/auth/drive.readonly",
    ]
}

This worked remotely via a Service Account (on Tfcloud to bbe precise). However, to run it locally I needed to run the following command in order to authenticate myself with the right scopes:

gcloud auth application-default login --scopes=openid,https://www.googleapis.com/auth/userinfo.email,https://www.googleapis.com/auth/cloud-platform,https://www.googleapis.com/auth/sqlservice.login,https://www.googleapis.com/auth/drive

or more simply: gcloud auth login --enable-gdrive-access

Ergot

Answer 3

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",
    ]
  }
}

Lukelukens

I can't make this work. Does something need to be updated for 2023? – Hangchow

Answer 4

The solution for me was to authenticate with a special flag on the gcloud login command which adds the extra scope that @shollyman mentioned in their answer.

gcloud auth login --update-adc --enable-gdrive-access

via https://cloud.google.com/bigquery/docs/external-data-drive#enable-google-drive

Hangchow