CODEWITHSUNDEEP
jsongoogle-bigquerygoogle-cloud-storage
Harel Yacovian
Harel Yacovian

Reputation: 137

load json files in google cloud storage into big query table

I am trying to do it with client lib using python.

the problem I am facing is that the TIMESTAMP on the JSON files are on Unix epoch TIMESTAMP format and big query can't detect that:

according to documentation:

enter image description here

so I wonder what to do?

I thought about changing the JSON format manually before I load it into BigQuery table?

Or maybe looking for an auto conversion from the BigQuery side?

I wondered across the internet and could not find anything useful yet.

Thanks in advance for any support.

Upvotes: 0

Views: 988

Answers (1)

guillaume blaquiere
guillaume blaquiere

Reputation: 76018

You have 2 solutions

  • Either you update the format before the BigQuery integration
  • Or you update the format after the BigQuery integration

Before

Before means updating your JSON (manually or by script) or to update it by the process that load the JSON into BigQuery (like Dataflow).

I personally don't like this, file handling are never funny and efficient.

After

In this case, you let BigQuery loading your JSON file into a temporary table and convert your UNIX timestamp into a Number or a String. Then, perform a request into this temporary table, convert the field in the correct timestamp format, and insert the data in the final table.

This way is smoother and easier (a simple SQL query to write). However, it implies cost to read all the loaded data (to write them then)

Upvotes: 2

Related Questions