Terraform管理BigQuery表时Schema更新致表替换及数据丢失的解决办法

一、阻止Schema变更触发表重建 #

默认情况下，Terraform会将BigQuery表的Schema变更识别为需要销毁并重建表的操作，直接导致数据丢失。可以通过以下配置组合解决：

1. 启用BigQuery原生Schema更新能力 #

在google_bigquery_table资源中添加schema_update_options参数，允许增量更新Schema而不重建表：

schema_update_options = ["ALLOW_FIELD_ADDITION", "ALLOW_FIELD_RELAXATION"]

• ALLOW_FIELD_ADDITION：支持向表中新增字段
• ALLOW_FIELD_RELAXATION：支持将字段约束从严格改为宽松（例如REQUIRED改为NULLABLE）

2. 忽略Schema变更的生命周期配置 #

如果需要完全屏蔽Terraform对Schema变更的检测，添加lifecycle块：

lifecycle {
  ignore_changes = [
    schema,
  ]
}

修改后的表资源核心代码 #

resource "google_bigquery_table" "bq_tables" {
  for_each            = { for table in var.bigquery_dataset_tables : table.table_id => table }
  project             = var.project_id
  dataset_id          = each.value.dataset_id
  table_id            = each.value.table_id
  schema              = file(format("${path.module}/schema/%v.json", each.value.file_name))
  deletion_protection = false
  # 启用Schema增量更新
  schema_update_options = ["ALLOW_FIELD_ADDITION", "ALLOW_FIELD_RELAXATION"]

  dynamic "time_partitioning" {
    for_each = try(each.value.time_partition, false) == false ? [] : [each.value.time_partition]
    content {
      type  = each.value.partitioning_type
      field = each.value.partitioning_field
    }
  }

  # 忽略Schema变更，防止触发表重建
  lifecycle {
    ignore_changes = [
      schema,
    ]
  }
}

二、添加external_data_configuration配置 #

沿用现有代码的动态块逻辑，添加dynamic "external_data_configuration"来支持外部数据源配置，完整代码如下：

完整资源代码 #

resource "google_bigquery_table" "bq_tables" {
  for_each            = { for table in var.bigquery_dataset_tables : table.table_id => table }
  project             = var.project_id
  dataset_id          = each.value.dataset_id
  table_id            = each.value.table_id
  schema              = file(format("${path.module}/schema/%v.json", each.value.file_name))
  deletion_protection = false
  schema_update_options = ["ALLOW_FIELD_ADDITION", "ALLOW_FIELD_RELAXATION"]

  dynamic "time_partitioning" {
    for_each = try(each.value.time_partition, false) == false ? [] : [each.value.time_partition]
    content {
      type  = each.value.partitioning_type
      field = each.value.partitioning_field
    }
  }

  # 动态生成外部数据配置块
  dynamic "external_data_configuration" {
    for_each = try(each.value.external_config, false) == false ? [] : [each.value.external_config]
    content {
      source_format = external_data_config.value.source_format
      source_uris   = external_data_config.value.source_uris

      # 示例：CSV格式专属配置（可根据数据源类型调整）
      dynamic "csv_options" {
        for_each = try(external_data_config.value.csv_options, false) == false ? [] : [external_data_config.value.csv_options]
        content {
          skip_leading_rows = csv_options.value.skip_leading_rows
          field_delimiter   = csv_options.value.field_delimiter
        }
      }

      autodetect            = external_data_config.value.autodetect
      ignore_unknown_values = external_data_config.value.ignore_unknown_values
    }
  }

  lifecycle {
    ignore_changes = [
      schema,
    ]
  }
}

配套变量定义 #

需要在bigquery_tables模块的variables.tf中补充对应变量：

variable "bigquery_dataset_tables" {
  type = list(object({
    table_id          = string
    dataset_id        = string
    file_name         = string
    time_partition    = optional(object({
      partitioning_type = string
      partitioning_field = string
    }))
    # 新增外部数据配置变量
    external_config = optional(object({
      source_format = string
      source_uris   = list(string)
      autodetect    = optional(bool, false)
      ignore_unknown_values = optional(bool, false)
      csv_options   = optional(object({
        skip_leading_rows = number
        field_delimiter   = string
      }))
    }))
  }))
  description = "List of BigQuery tables to create"
}

内容的提问来源于stack exchange，提问作者John