I want to import data stored in AVRO format from GCS to Bigquery.
The source timestamp is in Unix format with second precision (e.g : 1572534049), however BQ requires it to be in milli-second or micro-second precision to use the logical type and convert it to the right TIMESTAMP (I currently get values in 1970-XX-XX instead of 2021-XX-XX).
I know my problem is similar to the following thread : Convert unix timestamp to avro and store it in BigQuery
However, I can't multiply the column by 1000 in the AVRO files since they are retrieved from somewhere else then stored on a regular basis. It seems that I can't add a *1000 in the table schema definition either.
If there is no direct method, I will simply import the AVRO with the unix timestamp as an integer in an intermediate table, then output an other table with the expected columns through a query, but this isn't the most convenient.
edit : added example
You can try out following solution to achieve desired requirements.
Step 1: Load data to Bigquery in same format as available over GCS. Step 2: Create a View over recently table and apply Date transformations in that view, going further always refer View for data.
Alternate solution:- Step 1: Load data to Bigquery in same format as available over GCS. Step 2: Create a function for Date transformation and apply function on Unix format date, before you call data.
It will work for your requirement.