CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquerygoogle-cloud-pubsub
Yannis Weishaupt
Yannis Weishaupt

Reputation: 43

PubSub & BigQuery subscription using topic schema for TIMESTAMP

I have a BigQuery table with the following schema:

requested_at    TIMESTAMP   NULLABLE            
req_url         STRING          NULLABLE

And a PubSub topic with the following protobuf schema:

syntax = "proto3";

message Request {
  string req_url = 1;
  int64 requested_at = 2;
}

When I try to create a PubSub subscription using the "Write to BigQuery" delivery type and the "Use topic Schema" option, I have the following error:

Incompatible schema type for field 'requested_at': field is INT64 in the topic schema, but TIMESTAMP in the BigQuery table schema.

I don't understand why because int64 seems to be the preferred format: https://cloud.google.com/bigquery/docs/write-api#data_type_conversions

Upvotes: 4

Views: 2166

Answers (1)

al-dann
al-dann

Reputation: 2725

According to BigQuery Subscription - Schema compatibility -

When the type in the topic schema is a string and the type in the BigQuery table is TIMESTAMP, DATETIME, DATE, or TIME, then any value for this field in a Pub/Sub message must adhere to the format specified for the BigQuery data type.

The link from the quote leads to the timestamp as string description.

Therefore - in your protobuf schema the requested_at field should be of a 'string' type rather than the 'int64' type. And that string should contain a timestamp in a string canonical format - YYYY-[M]M-[D]D[( |T)[H]H:[M]M:[S]S[.F]]

Upvotes: 4

Related Questions