Bigquery Terraform Schedule queries deployment error

Question

Below is code from my Bigquery terraform main.tf file

    resource "google_bigquery_data_transfer_config" "config_queries" {
  for_each = fileset("${path.module}/scheduled_queries", "*.sql")
  depends_on = [google_bigquery_table.tables]
  data_source_id = "scheduled_queries"
  location=var.location
  destination_dataset_id=google_bigquery_dataset.dataset.dataset_id
  display_name = "config_queries_${substr(each.value, 0, length(each.value) -4)}"
  schedule = "every 4 hours "
 params = {
    destination_table_name_template = substr(each.value,0,length(each.value) -4)
    write_disposition = "WRITE_APPEND"
    query = file("${path.module}/scheduled_queries/${each.value}")
  }
}

I am getting below error:

│ Error: Error creating Config: googleapi: Error 404: Requested entity was not found.

The same code is working fine if running sql files individually. Any suggestions

Answer 1

I will leave this community answer for visibility. Please feel free to update if it not reflect the actual answer. This is the original response found in the link provided with the solution by paslandau .

As I've spent the better part of the day to make this work, here is the quick guide of what worked for me:

1. Regards,As I've spent the better part of the day to make this work, here is the quick guide of what worked for me:

Create a service account in Project-A at

https://console.cloud.google.com/iam-admin/serviceaccounts?orgonly=true&project=Project-A

If you already have a service account, you can find its corresponding project id in its json keyfile

{
  "type": "service_account",
  "project_id": "Project-A",
...

2. Activate the Big Query Transfer API for the project you want to create the transfer job at ( Project-B) via

   https://console.cloud.google.com/marketplace/product/google/bigquerydatatransfer.googleapis.com?project=

3. Add the service account from step 1 to Project-B with Role "Big Query Admin" via https://console.cloud.google.com/iam-admin/iam?project=Project-B

4. Try to create a transfer job via API (i.e. "from your code"). This will trigger the error mentioned in the titel - which will also include the auto-generated transfer service service account of Project-B, e.g.

400 P4 service account needs iam.serviceAccounts.getAccessToken permission. Running the following command may resolve this error: gcloud projects  add-iam-policy-binding <PROJECT_ID> --member='serviceAccount:service-<PROJECT_NUMBER>@gcp-sa-bigquerydatatransfer.iam.gserviceaccount.com'

Copy the name of the tranfer service account, e.g.

5. Add the transfer service account to Project-A with the role "Service Account Token Creator" via

   https://console.cloud.google.com/iam-admin/iam?project=Project-A

6. Re-run the creation of the transfer service job as in step 4. It should now succeed.