CODEWITHSUNDEEP
google-bigquerygoogle-cloud-spanner
Mahesh Uligade
Mahesh Uligade

Reputation: 627

How to convert the spanner TIMESTAMP to BigQuery TIMESTAMP?

I am trying to copy some tables from Spanner to BigQuery.

I dumped Spanner database in csv file and when I try to upload that csv to BigQuery it is throwing error of the timestamp format.

Here they mentioned limitation of BigQuery TIMESTAMP.

How do I convert spanner TIMESTAMP to BigQuery TIMESTAMP?

Upvotes: 0

Views: 1792

Answers (3)

Mahesh Uligade
Mahesh Uligade

Reputation: 627

I converted timestamp to epoch time like this

SELECT myTime , FORMAT_TIMESTAMP("%s",  myTime, "America/Los_Angeles") FROM MyTable

and it worked.

Upvotes: 0

shlomiw
shlomiw

Reputation: 365

In addition to what @Biswa-nag wrote -

We export our Spanner tables to avro files then import to BigQuery. Unfortunately, the timestamps turned out to be Strings in BigQuery.

Our workaround for ad-hoc queries is to use user defined function to convert the timestamp in the queries (it took some time to find the correct format...)

An example:

CREATE TEMP FUNCTION ConvertTimestamp(dt STRING) AS (PARSE_DATETIME("%Y-%m-%dT%H:%M:%E*SZ", dt));
select count(*) from `[db].Games` where ConvertTimestamp(StartTime) >= DateTime(2019,8,1,0,0,0)

Upvotes: 0

Biswa Nag
Biswa Nag

Reputation: 141

There may be two ways to go about this.

  1. Keep the timestamp field as a string as exported by Cloud Spanner and load it into BigQuery as a string. It should still be sortable and used in predicates.
  2. Use a user-defined function to do the string conversion required to load the timestamp natively in BigQuery, via the TextToBigQuery Dataflow template.

You may also write a script to convert the Timestamp to the BigQuery format.

Upvotes: 0

Related Questions