CODEWITHSUNDEEP
google-bigqueryterraform
Em Ae
Em Ae

Reputation: 8704

Schema modification giving partitioning error

I have previously created a table in bigquery using following script in terraform

resource "google_bigquery_dataset" "my-dataset" {
  dataset_id  = "datasetname"
  description = "description"
}

resource "google_bigquery_table" "mytable" {
  dataset_id = google_bigquery_dataset.my-dataset.dataset_id
  table_id   = "mytable"

  time_partitioning {
    type = "DAY"
  }

  schema = <<EOF
[
  {
      "name": "field_one",
      "type": "STRING",
      "mode": "NULLABLE"
  },
  {
      "name": "field_two",
      "type": "RECORD",
      "mode": "NULLABLE",
      "fields": [
          {
              "name": "sub_field_one",
              "type": "FLOAT",
              "mode": "NULLABLE"
          },
          {
              "name": "sub_field_two",
              "type": "FLOAT",
              "mode": "NULLABLE"
          }
      ]
  },
  {
      "name": "field_three",
      "type": "STRING",
      "mode": "NULLABLE"
  }
]
EOF

}

This worked fine and i was able to create mytable in bigquery. Now i have to modify this table and have to add a new value to it (field_four. So made this script

resource "google_bigquery_dataset" "my-dataset" {
  dataset_id  = "datasetname"
  description = "description"
}

resource "google_bigquery_table" "mytable" {
  dataset_id = google_bigquery_dataset.my-dataset.dataset_id
  table_id   = "mytable"

  time_partitioning {
    type = "DAY"
  }

  schema = <<EOF
[
  {
      "name": "field_one",
      "type": "STRING",
      "mode": "NULLABLE"
  },
  {
      "name": "field_two",
      "type": "RECORD",
      "mode": "NULLABLE",
      "fields": [
          {
              "name": "sub_field_one",
              "type": "FLOAT",
              "mode": "NULLABLE"
          },
          {
              "name": "sub_field_two",
              "type": "FLOAT",
              "mode": "NULLABLE"
          }
      ]
  },
  {
      "name": "field_three",
      "type": "STRING",
      "mode": "NULLABLE"
  },
  {
      "name": "field_four",
      "type": "RECORD",
      "mode": "NULLABLE",
      "fields": [
          {
              "name": "sub_field_three",
              "type": "STRING",
              "mode": "NULLABLE"
          }
      ]
  }
]
EOF

}

When i run it using terraform plan, it shows that the new field will be added. But when i do terraform apply, I get following error

Error: googleapi: Error 400: Cannot convert non partitioned/clustered table to partitioned/clustered table., invalid

  on filename.tf line 10, in resource "google_bigquery_table" "mytable":
  10: resource "google_bigquery_table" "mytable" {

I am not changing the partition of the table. Whats going on here. How to make schema change to

  1. Add a new field?
  2. Change the field type (which has only null values as of now) to something new?

Terraform details

Terraform v0.12.24
+ provider.google v3.1.0
+ provider.google-beta v3.1.0
+ provider.random v2.2.1

Upvotes: 0

Views: 1651

Answers (1)

rmesteves
rmesteves

Reputation: 4085

After reproducing the same codes I could add a new field without problem. As discussed in the comments, the error below is raised when you try to transform a non partitioned table into a partitioned table. In this case, the problem was caused by a version mismatch of Terraform: the version used to create the table didn't support partitioned table so a normal table was created. When running both codes in Terraform 0.12.24 it worked.

Error: googleapi: Error 400: Cannot convert non partitioned/clustered table to partitioned/clustered table., invalid

  on filename.tf line 10, in resource "google_bigquery_table" "mytable":
  10: resource "google_bigquery_table" "mytable" {

About the second question, you can't change your field's type using Terraform. As you can see here, for changing a field's type you need a query job, which is not possible in Terraform as you can see in this open issue.

I hope it helps

Upvotes: 1

Related Questions