如何用Terraform实现BigQuery的CREATE TABLE IF NOT EXISTS并避免表重建？

需要用Terraform创建模块管理BigQuery数据集和表，实现等价于CREATE TABLE IF NOT EXISTS的功能，确保运行Terraform时不会删除或重建已在使用的表。尝试官方terraform-google-modules/bigquery模块及独立google_bigquery_table资源，设置deletion_protection = true后虽能防止删除，但terraform plan仍显示资源需替换（即使schema未修改），terraform apply直接报错。

相关配置及plan输出如下：

配置代码：

resource "google_bigquery_table" "advisor-population" {
  table_id           = "advisor_population"
  dataset_id = "advisors"
  schema             =  file("${path.module}/advisor_population.json")
  expiration_time = null
  #only create the table if it doesn't already exist
  deletion_protection = true
  clustering      = []
  labels          = {
    env     = var.environment
    project = "lava"
  }
}

plan输出片段：

# module.wisc-tl-data-lake-advisors.google_bigquery_table.advisor-population must be replaced
-/+ resource "google_bigquery_table" "advisor-population" {
      ~ creation_time       = 1699629882718 -> (known after apply)
      ~ etag                = "2rxbC0hgF0zvUQoVLgyx8A==" -> (known after apply)
      ~ expiration_time     = 0 -> (known after apply)
      ~ id                  = "projects/doit-at-lace-dev-d96a/datasets/advisors/tables/advisor_population" -> (known after apply)
      ~ last_modified_time  = 1699629882778 -> (known after apply)
      ~ location            = "US" -> (known after apply)
      ~ num_bytes           = 0 -> (known after apply)
      ~ num_long_term_bytes = 0 -> (known after apply)
      ~ num_rows            = 0 -> (known after apply)
      ~ project             = "doit-at-lace-dev-d96a" -> (known after apply)
      ~ schema              = jsonencode(
          ~ [
              ~ {
                  + fields      = []
                    name        = "advisor_pvi"
                    # (3 unchanged attributes hidden)
                },
              ~ {
                  + fields      = []
                    name        = "advisor_netid"
                    # (3 unchanged attributes hidden)
                },
              ~ {
                  + fields      = []
                    name        = "population"
                    # (3 unchanged attributes hidden)
                },
            ] # forces replacement
        )
      ~ self_link           = "https://bigquery.googleapis.com/bigquery/v2/projects/doit-at-lace-dev-d96a/datasets/advisors/tables/advisor_population" -> (known after apply)
      ~ type                = "TABLE" -> (known after apply)
        # (5 unchanged attributes hidden)
    }

1. 修复Schema字段匹配问题 #

从plan输出可见，schema差异是核心原因：本地JSON文件未显式定义fields字段，但BigQuery API返回时会自动给每个顶级字段加上fields = []（表示无嵌套字段）。Terraform对比时认为这是变更，触发资源替换。

解决方法：修改本地advisor_population.json文件，给每个顶级字段添加"fields": []，示例：

[
  {
    "name": "advisor_pvi",
    "type": "STRING",
    "mode": "REQUIRED",
    "description": "Advisor PVI identifier",
    "fields": []
  },
  {
    "name": "advisor_netid",
    "type": "STRING",
    "mode": "REQUIRED",
    "description": "Advisor NetID",
    "fields": []
  },
  {
    "name": "population",
    "type": "STRING",
    "mode": "REQUIRED",
    "description": "Population category",
    "fields": []
  }
]

修改后，Terraform读取的schema与BigQuery实际返回的内容一致，不会再触发替换。

2. 实现CREATE TABLE IF NOT EXISTS行为 #

根据不同需求，有两种实现方式：

方式一：仅创建不存在的表（不管理现有表后续变更） #

通过data资源检查表是否存在，用count控制资源是否创建：

# 检查表是否已存在
data "google_bigquery_table" "advisor-population" {
  table_id    = "advisor_population"
  dataset_id  = "advisors"
  project     = var.project_id
}

# 仅当表不存在时创建
resource "google_bigquery_table" "advisor-population" {
  count = data.google_bigquery_table.advisor-population.id == "" ? 1 : 0
  
  table_id           = "advisor_population"
  dataset_id         = "advisors"
  schema             = file("${path.module}/advisor_population.json")
  expiration_time    = 0 # BigQuery中0表示永不过期，与默认行为一致
  deletion_protection = true
  clustering         = []
  labels             = {
    env     = var.environment
    project = "lava"
  }
}

注意：这种方式下，已存在的表不会进入Terraform状态，后续对表的修改（如schema、标签变更）Terraform不会管理。

方式二：导入现有表并长期管理 #

如果需要后续用Terraform管理已存在的表，先将其导入Terraform状态：

terraform import module.wisc-tl-data-lake-advisors.google_bigquery_table.advisor-population projects/doit-at-lace-dev-d96a/datasets/advisors/tables/advisor_population

导入后，Terraform会记录现有表的状态，后续plan/apply仅会在实际属性变更时执行更新，不会无意义地替换资源。同时保留deletion_protection = true防止意外删除。

3. 额外优化点 #

• 修正expiration_time：BigQuery中未设置过期时间时，API返回值为0，因此将配置中的expiration_time = null改为expiration_time = 0，避免状态差异。
• 验证标签一致性：确保配置中的labels与现有表的标签完全一致，避免触发不必要的更新。

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