使用Terraform创建BigQuery表报错：数组无法解析为字符串类型

错误信息 #

Error: json: cannot unmarshal array into Go struct field TableFieldSchema.type of type string
with module.pubsub["b"].google_bigquery_table.bq_table[0],
on ../../modules/main.tf line 82, in resource "google_bigquery_table" "bq_table":
  82: resource "google_bigquery_table" "bq_table" {

Terraform表定义代码 #

resource "google_bigquery_table" "bq_table" {
  deletion_protection = false
  table_id            = var.topic_config.bq_table
  dataset_id          = var.topic_config.bq_dataset
  schema              = var.topic_config.bq_table_schema
}

原Schema内容 #

[ 
   {   "name": "SalesDate",   "type": ["int", "null"] }, 
   {   "name": "SaleFiscalYearWeek",   "type": ["string", "null"] }, 
   {   "name": "CostOfSalesLocalCurrency",   "type": [{
    "type": "bytes",
    "logicalType": "decimal",
    "precision": 38,
    "scale": 6   }, "null"] }]

Terraform变量配置 #

topic_config = {   
  "b" = {
    topic_name                 = "g-b-topic"
    message_retention_duration = "1200s"
    has_schema                 = true
    topic_schema_path          = "schema/twitter.avsc"
    schema_name                = "b-schema"
    message_encoding           = "JSON"
    subscription_name          = "g-b-push-sub"
    subscription_type          = "push_to_bq"
    ack_deadline_seconds       = 600
    bq_dataset                 = "streaming"
    bq_exsiting_ds_flag        = false
    bq_table                   = "push_to_bq_table"
    bq_source_format           = "AVRO"
    bq_table_schema            = <<EOF [ 
   {   "name": "SalesDate",   "type": ["int", "null"] }, 
   {   "name": "SaleFiscalYearWeek",   "type": ["string", "null"] }, 
   {   "name": "CostOfSalesLocalCurrency",   "type": [{
    "type": "bytes",
    "logicalType": "decimal",
    "precision": 38,
    "scale": 6   }, "null"] }] EOF
    use_topic_schema           = true
    write_metadata             = false
    drop_unknown_fields        = false   
  }, 
}

解决方案 #

问题出在你用了Avro风格的Schema格式，但Terraform的google_bigquery_table资源要求的是BigQuery原生的JSON Schema格式，两者语法不兼容：

1. 可空字段处理：BigQuery不用["类型", "null"]这种数组表示可空，而是通过"mode": "NULLABLE"字段声明（默认就是NULLABLE，可省略，但显式写更清晰）
2. Decimal类型处理：BigQuery原生支持NUMERIC/BIGNUMERIC类型，直接指定precision和scale即可，不需要用bytes加logicalType的Avro写法

修正后的Schema #

[
  {
    "name": "SalesDate",
    "type": "INTEGER",
    "mode": "NULLABLE"
  },
  {
    "name": "SaleFiscalYearWeek",
    "type": "STRING",
    "mode": "NULLABLE"
  },
  {
    "name": "CostOfSalesLocalCurrency",
    "type": "BIGNUMERIC",
    "precision": 38,
    "scale": 6,
    "mode": "NULLABLE"
  }
]

修正Terraform变量 #

把bq_table_schema替换为上面的修正内容：

bq_table_schema = <<EOF
[
  {
    "name": "SalesDate",
    "type": "INTEGER",
    "mode": "NULLABLE"
  },
  {
    "name": "SaleFiscalYearWeek",
    "type": "STRING",
    "mode": "NULLABLE"
  },
  {
    "name": "CostOfSalesLocalCurrency",
    "type": "BIGNUMERIC",
    "precision": 38,
    "scale": 6,
    "mode": "NULLABLE"
  }
]
EOF

注意：BigQuery的类型名称是大写的（比如INTEGER、STRING、BIGNUMERIC），必须严格匹配。

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