Which Protobuf type is compatible for TIMESTAMP field in bigquery?

[tangcent]

I want to export data from Pub/Sub to BigQuery.

First. I create a pubsub schema with protobuf:

message ArchivedCtx {
  ...
  int64 created_time = 6;
}

Then. I create a table in BigQuery:

[
    ...
    {
        "name": "created_time",
        "type": "TIMESTAMP",
        "mode": "NULLABLE",
        "description": "data create time"
    }
]

In the end. I try to create a subscription to export data from Pub/Sub to BigQuery.

And it popup a error message:

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

But I found a page: https://cloud.google.com/bigquery/docs/write-api#data_type_conversions

It says that:

It made me feel quite confused. So which type should created_time be defined in protobuf?

Or there is another way to import data from pubsub into a TIMESTAMP field in bigquery?

[RC1 · Accepted solution]

@tangcent I guess here they are talking about unix timestamp as integer type in timestamp field. Try putting a unix timestamp value like `1569888224` instead of 6 

EDIT :

Check here : https://cloud.google.com/pubsub/docs/bigquery#protobuf-to-zetasql

• 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.

[tangcent]

Thank you for your reply.

Follow your suggestion. I use string created_time instead of int64 created_time.

And format the timestamp from long to yyyy-MM-dd HH:mm:ss.S . It works!