bq: quotes issues with view queries

[sylvainar]

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 the modular-magician user, it is either in the process of being autogenerated, or is planned to be autogenerated soon. If an issue is assigned to a user, that user is claiming responsibility for the issue. If an issue is assigned to hashibot, a community member has claimed the issue already.

Terraform Version

Terraform v0.13.3
+ provider registry.terraform.io/hashicorp/google v3.41.0

Affected Resource(s)

• google_bigquery_table

Terraform Configuration Files

resource "google_bigquery_table" "default" {
  dataset_id = google_bigquery_dataset.dataset.dataset_id
  table_id = "testviewusers"
  view {
    query = "SELECT * FROM EXTERNAL_QUERY('projects/myproject/locations/eu/connections/myconnection,'SELECT CAST(\"companyId\" AS varchar),CAST(id AS varchar),\"firstName\",\"lastName\",email,\"createdAt\",\"joinedAt\" FROM \"user\"')"
  }
}

Debug Output

google_bigquery_table.default: Creating...

Error: googleapi: Error 400: Encountered "" at line 1, column 30.
[Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)], invalidQuery

Expected Behavior

This is a valid query that works in the query designer, I just copied & pasted it and escaped double quotes. I also tried with a template file. Nothing seems to work, I tried with double quotes, single quotes, even by replacing quotes with their html code, i'm still getting 400 issues.

Actual Behavior

View should register properly.

References

Didn't find anything on the web for this issue.

• #0000

[sylvainar]

Weird because it works perfectly when using the scheduled job from the beta provider (using the same query)

[edwardmedia]

@sylvainar The error massage Try using standard SQL seems clear what was wrong. It appears not from The Terraform plugin. I would suggest you review the doc to see if that helps

[sylvainar]

Putting the flag use_legacy_sql to false made it work. I tried true but not false lol.

This is the config I ended up using:

resource "google_bigquery_table" "default" {
  dataset_id = google_bigquery_dataset.dataset.dataset_id
  table_id = "viewCompanies"
  view {
    query = "SELECT * FROM EXTERNAL_QUERY('projects/${var.project}/locations/${var.dwh_location}/connections/${google_bigquery_connection.connection.connection_id}', 'SELECT CAST(\"id\" AS varchar),name FROM company')"
    use_legacy_sql = false
  }
}

Hope it'll help others.

[edwardmedia]

@sylvainar I am glad you have figured this out. Thank you for posting your resolution.

[ghost]

I'm going to lock this issue because it has been closed for 30 days ⏳. This helps our maintainers find and focus on the active issues.

If you feel this issue should be reopened, we encourage creating a new issue linking back to this one for added context. If you feel I made an error 🤖 🙉 , please reach out to my human friends 👉 hashibot-feedback@hashicorp.com. Thanks!