CODEWITHSUNDEEP
google-cloud-platformgoogle-bigqueryterraformschema-migration
klee
klee

Reputation: 1624

How to manage schema changes to a BigQuery table via Terraform

We currently use the following mechanism to create a BigQuery table with a pre-defined schema and we created the infrastructure.

https://www.terraform.io/docs/providers/google/r/bigquery_table.html

The dev team decided to modify the schema by adding another column, so we are planning to modify the schema changes in the above terraform script to enable this.

What would be the best way to manage such schema migrations in production environments?

Since in a production environment, we would be expected to retain the table data while the schema migration is performed

Upvotes: 4

Views: 9678

Answers (3)

Rachel Gagnon
Rachel Gagnon

Reputation: 1

Terraform has added functionality to handle certain schema changes like dropping a column, but many are still destructive (eg renaming a column will cause TF to destroy and recreate the table with the new schema, hence destroying the data in the table).

The process to handle this requires some coordination, but any time a destructive schema change needs to happen we will:

  1. back up the BQ table to a temp table
  2. execute schema change via TF
  3. restore temp data to original table

these operations can all be done with BQ SQL statements (CREATE TABLE CLONE temp_table, INSERT * from temp_table INTO original_table)

Upvotes: 0

workingclassheroine
workingclassheroine

Reputation: 21

Looks like there was a fix for it - https://github.com/hashicorp/terraform-provider-google/issues/8503

Upvotes: 2

Pawan Sharma
Pawan Sharma

Reputation: 41

It seems you cannot modify the schema of the table and retain data using Terraform. Instead you can use bq command-line for the same. https://cloud.google.com/bigquery/docs/managing-table-schemas#bq.

Upvotes: 2

Related Questions