Unable to map protobuf string field to BigQuery's geography field in a subscription writing to a bigquery table

Question

Problem description We have created a pub/sub topic in Google Cloud Platform where a subscription should write directly to a BigQuery table using the option Delivery type: "Write to BigQuery".

The topic uses a proto3 schema as follows

syntax = "proto3";

message ProtocolBuffer {
  ...
  optional string location = 3;
  ...
}

and the BigQuery table the subscription should write to contains a nullable column location with type GEOGRAPHY.

Now when trying to create the subscription, the following error is thrown: Incompatible schema type for field 'location': field is STRING in the topic schema, but GEOGRAPHY in the BigQuery table schema.

How could we fix this problem without changing the GEOGRAPHY type of the location column in BigQuery? Ideally, you do not want to use Dataflow, the old way of writing from a subscription to BigQuery due to financial reasons.

There are two Google Cloud Platform sources we could find to try and solve this problem.

Schema mapping
https://cloud.google.com/pubsub/docs/bigquery#schema_compatibility

This documentation tells us there is no existing mapping from proto3's string to ZetaSQL's GEOGRAPHY.

Data type conversion
https://cloud.google.com/bigquery/docs/write-api#data_type_conversions

On the other side of the mapping, BigQuery's data conversion documentation notes that GEOGRAPHY is converted to proto's string. However, we get an error after all.

Answer 1

The Cloud Pub/Sub BigQuery subscription does not currently support the GEOGRAPHY type, which is what is indicated by the error. Unfortunately, there is no solution at this time other than using a Dataflow pipeline or changing the type in the BigQuery table. You could also add a feature request in the issue tracker.