如何通过Pub/Sub向BigQuery推送GEOGRAPHY类型字段？

针对你遇到的Pub/Sub AVRO schema STRING类型与BigQuery GEOGRAPHY字段不兼容的问题，提供以下几种可行方案：

方案一：订阅时配置数据转换逻辑 #

创建Pub/Sub到BigQuery的订阅时，通过查询转换直接将String类型的GeoJSON转成GEOGRAPHY类型：

1. 在创建订阅的BigQuery配置环节，选择「使用查询转换数据」选项；
2. 编写转换查询，例如：

   SELECT 
     other_field1,
     other_field2,
     ST_GEOGFROMGEOJSON(gps) AS gps
   FROM pubsub.topic.`你的项目ID`.`你的主题名`
   

3. 将查询结果映射到目标BigQuery表的对应字段，这样订阅推送时会自动完成类型转换，无需修改Pub/Sub的AVRO schema。

方案二：给Pub/Sub的AVRO schema添加BigQuery类型注解 #

AVRO本身没有GEOGRAPHY原生类型，但可以通过BigQuery的扩展注解指定字段实际类型：
在Pub/Sub主题的AVRO schema中，给对应STRING字段添加google.cloud.bigquery.v1.Schema注解，示例如下：

{
  "type": "record",
  "name": "MessageSchema",
  "fields": [
    {"name": "other_field", "type": "string"},
    {
      "name": "gps",
      "type": "string",
      "annotations": {
        "google.cloud.bigquery.v1.Schema": {
          "type": "GEOGRAPHY"
        }
      }
    }
  ]
}

这样Pub/Sub会告知BigQuery该STRING字段需要解析为GEOGRAPHY类型，即可匹配目标表的字段类型。

方案三：通过临时表中转转换 #

如果前两种方案无法快速落地，可以采用中转方式：

1. 创建一个临时BigQuery表，将gps字段设为STRING类型，让Pub/Sub订阅直接推送到该临时表；
2. 通过BigQuery定时查询、Cloud Function或Dataflow任务，定期将临时表中的数据转换后写入目标表，转换逻辑示例：

   INSERT INTO `目标表ID`
   SELECT 
     other_field1,
     other_field2,
     ST_GEOGFROMGEOJSON(gps) AS gps
   FROM `临时表ID`
   WHERE gps IS NOT NULL
   

注意事项 #

• 确保发送的GeoJSON格式符合BigQuery要求，例如必须是有效的GeoJSON对象（如{"type": "Point", "coordinates": [116.40, 39.91]}），否则转换函数会报错；
• 使用方案一时，需确保Pub/Sub服务账号拥有执行查询和写入目标表的权限。

内容的提问来源于stack exchange，提问作者Rui Bras Fernandes