CODEWITHSUNDEEP
google-bigquery
Richard
Richard

Reputation: 65530

Custom date format for loading data into BigQuery, using bq?

I'm uploading a CSV file to Google BigQuery using bq load on the command line. It's working great, but I've got a question about converting timestamps on the fly.

In my source data, my timestamps are formatted as YYYYMM, e.g. 201303 meaning March 2013.

However, Google BigQuery's timestamp fields are documented as only supporting Unix timestamps and YYYY-MM-DD HH:MM:SS format strings. So unsurprisingly, when I load the data, these fields don't convert to the correct date.

Is there any way I can convey to BigQuery that these are YYYYMM strings?

If not I can convert them before loading, but I have about 1TB of source data, so I'm keen to avoid that if possible :)

Upvotes: 2

Views: 3802

Answers (2)

Roman
Roman

Reputation: 9441

An alternative to Mosha's answer can be achieved by:

SELECT DATE(CONCAT(your_ts_str, "01")) as ts

Upvotes: 1

Mosha Pasumansky
Mosha Pasumansky

Reputation: 14004

Another alternative is to load this field as STRING, and convert it to TIMESTAMP inside BigQuery itself, copying the data into another table (and deleting the original one afterwards), and doing the following transformation:

SELECT TIMESTAMP(your_ts_str + "01") AS ts

Upvotes: 5

Related Questions