google_bigquery_job - required paramter missing (project_reference)

[ralbritton-utah]

Community Note

• Please vote on this issue by adding a 👍 reaction to the original issue to help the community and maintainers prioritize this request.
• Please do not leave +1 or me too comments, they generate extra noise for issue followers and do not help prioritize the request.
• If you are interested in working on this issue or have submitted a pull request, please leave a comment.
• If an issue is assigned to a user, that user is claiming responsibility for the issue.
• Customers working with a Google Technical Account Manager or Customer Engineer can ask them to reach out internally to expedite investigation and resolution of this issue.

Terraform Version & Provider Version(s)

Terraform vX.X.X on

• provider registry.terraform.io/hashicorp/google vX.X.X
• provider registry.terraform.io/hashicorp/google-beta vX.X.X

Affected Resource(s)

google_bigquery_job

Terraform Configuration

Terraform v1.9.6
on windows_amd64

provider registry.terraform.io/hasicorp/google v 6.6.0

Debug Output

google_bigquery_job.job: Creating... ╷ │ Error: Error creating Job: googleapi: Error 400: Required parameter is missing: project_reference, required │ │ with google_bigquery_job.job, │ on main.tf line 1, in resource "google_bigquery_job" "job": │ 1: resource "google_bigquery_job" "job" { │

Expected Behavior

job should be created

Actual Behavior

Error received

Steps to reproduce

create main.tf:

resource "google_bigquery_job" "job" {
  project = var.project
  job_id  = var.job_id
  labels  = var.job_labels
  query {
    query = file("sql/${var.query["file_path"]}")
    destination_table {
      dataset_id = var.destination_table["dataset_id"]
      table_id   = var.destination_table["table_id"]
    }
    write_disposition = var.query["write_disposition"]
  }
}

Define Variables:

variable "project" {
  description = "Project where the dataset and table are created"
}

variable "job_id" {
  description = "Required.The ID of the job."
  type        = string
}

variable "query" {
  description = "Required. SQL query text to execute along with additional parameters."
  type        = map(string)
}

variable "job_labels" {
  description = "A mapping of labels to assign to the job"
  type        = map(string)
}

variable "destination_table" {
  description = "destination of the query results"
  type        = map(string)
}

Created a tfvars file:

Important Factoids

project = "my-gcp-project" job_id="my_job_id"

query = { file_path ="query.txt" write_disposition="WRITE_TRUNCATE" }

destination_table = { dataset_id = "my_dataset" table_id = "my_table" }

job_labels = { env = "dev" }

run `terraform plan -var-file="my_var_file.tfvars"

The plan runs successfully. Then run `terraform apply -auto-approve -var-file="my_var_file.tfvars" It begins creating the job and fails with the error

Error: Error creating Job: googleapi: Error 400: Required parameter is missing: project_reference, required
│
│   with google_bigquery_job.job,
│   on main.tf line 1, in resource "google_bigquery_job" "job":
│    1: resource "google_bigquery_job" "job" {

There is no project_reference parameter in the terraform documentation. A project is provided.

References

No response

b/373453207

[bindermuehle]

I have successfully reproduced the issue. To resolve it, you need to make two modifications to the google_bigquery_job resource definition:

1. Add the location=<LOCATION> parameter.
2. Include the project_id = google_bigquery_table.foo.project parameter.

I'm currently investigating why these parameters are required by the API but optional in the Terraform syntax. Here's an example of the working configuration:

"google_bigquery_dataset" "dataset" {
  dataset_id    = "example_dataset"
  friendly_name = "test"
  description   = "This is a test description"
  location      = "US"
  labels = {
    env = "default"
  }
}

resource "google_bigquery_table" "foo" {
  deletion_protection = false
  dataset_id          = google_bigquery_dataset.dataset.dataset_id
  table_id            = "job_query_table"
}

resource "google_bigquery_job" "job" {
  job_id = "job_query"
  labels = {
    "example-label" = "example-value"
  }
  query {
    query = "SELECT state FROM [lookerdata:cdc.project_tycho_reports]"
    destination_table {
      project_id = google_bigquery_table.foo.project
      dataset_id = google_bigquery_table.foo.dataset_id
      table_id   = google_bigquery_table.foo.table_id
    }
    write_disposition = "WRITE_TRUNCATE"
  }
  location = "US"
}

This configuration should resolve the "Required parameter is missing: project_reference" error you encountered. I'll update the ticket with any additional findings regarding the discrepancy between API requirements and Terraform syntax.

[ggtisc]

Confirmed issue!

According to terraform registry the query.destination_table.project_id is not required, but it is not possible to create the resource without it.

[ralbritton-utah]

I was able to work around the issue using the steps that @bindermuehle suggested. Thank you.

[wj-chen]

I haven't tried to repro the error myself, but according to the API reference doc for JobReference and JobConfigurationQuery -

• location is optional
• destination_table.project_id and destination_table.dataset_id should both be required, which we'll look into updating on the Terraform side

[bindermuehle]

After further investigation, I'd like to provide some clarification based on the documentation and my findings:

The documentation indicates that project_id is indeed an optional field when the table is specified using a fully qualified ID. Specifically, it states: "table_id - (Required) The table. Can be specified {{table_id}} if project_id and dataset_id are also set, or of the form projects/{{project}}/datasets/{{dataset_id}}/tables/{{table_id}} if not." Based on this, both of the following configurations are valid ( I've validated this):

destination_table {
  table_id = google_bigquery_table.foo.id
}

destination_table {
  project_id = google_bigquery_table.foo.project
  dataset_id = google_bigquery_table.foo.dataset_id
  table_id   = google_bigquery_table.foo.table_id
}

However, it appears that this validation cannot be performed during the planning phase. because the resolved string is not available. Regarding the possibility of making this error more user-friendly, I'm not immediately aware of an approach to achieve this. It seems to be a limitation in how Terraform interacts with the Google Cloud API during the planning stage. If you know of a way to improve this let me know and I'm happy to investigate