4

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

klee
  • 1,554
  • 2
  • 19
  • 31
  • 3
    If you make the change what does the plan show? Have you tried running it in a test environment? What happens? If possible you should include the answers to these questions (with code and output) in your question. – ydaetskcoR Jun 13 '20 at 10:43
  • Do you perform a terraform plan before the apply? Is the update only a column addition? no other change? – guillaume blaquiere Jun 13 '20 at 18:40
  • Notice that you'd be able to make this column addition to the schema only if the column is NULLABLE or REPEATED, [since REQUIRED columns can only be added when you create a table while loading data, or when you create an empty table with a schema definition](https://cloud.google.com/bigquery/docs/managing-table-schemas#manually_adding_an_empty_column). The type of the column will determine the correct procedure to follow. – Daniel Ocando Jun 14 '20 at 13:14
  • Like @ydaetskcoR said, please provide those information if possible. What Daniel Ocando said is also important. BigQuery will not allow you to insert certain kinds of fields – rmesteves Jun 15 '20 at 15:03
  • 1
    @klee did you ever solve this? – dendog Oct 21 '20 at 18:00
  • I have tried this with a nullable column in a dev environment and it ran, deleting all previous data in the table. – JBCP May 21 '21 at 20:50
  • I know this is old, and I admit I haven't tried this, but it should be possible to build a module that will handle creating a temp table with the new column, copying the data to the new table (adding a value for the new column if it will be required), modifying the existing table (deleting the data in the process), reloading the existing table from the temp table, and dropping the temp table. – Devon_C_Miller Sep 22 '21 at 03:14

2 Answers2

2

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.

Pawan Sharma
  • 41
  • 4
2

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

workingclassheroine
  • 21
  • 2
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 15 '21 at 06:00