How to convert microsecond integer into Timestamp with bq load?

user_3605780 0
Viewed 92

How can I load a csv file into Bigquery using bq load command line tool while converting a microsecond timestamp into bq timestamp?

I'm using the following command:

 bq load --skip_leading_rows=1 project1.partition_table "gs://data.csv.gz" ./schema.json

When loading this data into a new table with autodetect schema it works but bigquery makes an integer from the microsecond integer. When trying to add this file to this table it gives the error:

Waiting on bqjob_r65c10_1 ... (0s) Current status: DONE   
BigQuery error in load operation: Error processing job 'skjfafq': Error while reading
data, error message: Could not parse '1611705599779000' as TIMESTAMP for field timestamp (position 2) starting at location 232  with message
'Epoch second value "1611705599779000" out of range'
Failure details:
- Error while reading data, error message: CSV processing encountered
too many errors, giving up. Rows: 1; errors: 1; max bad: 0; error
percent: 0
- query: Could not parse '1611705599779000' as TIMESTAMP for field
timestamp (position 2) starting at location 232  with message
'Epoch second value "1611705599779000" out of range'
- You are loading data without specifying data format, data will be
treated as CSV format by default. If this is not what you mean,
please specify data format by --source_format.

Is there a way to directly load files with microsecond timestamp into partitioned (by timestamp) bigquery tables? Or do I first need to load it into a temporary table and convert the value and copy into the partitioned table?

google-cloud-storage google-bigquery
1 Answers

I suggest the second method you mentioned.

First, load the data into a staging table just with strings then transform it into the timestamp. You can use TIMESTAMP_MICROS(int64_expression) that which is a function in bq itself. Also, something important if you are planning to partition by this field, is that each bq table has a limit of 4, 000 partitions.

https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions?hl=es_419

SELECT TIMESTAMP_MICROS(1230219000000000) AS timestamp_value;

-- Display of results may differ, depending upon the environment and time zone where this query was executed.
+-------------------------+
| timestamp_value         |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+

Using staging table as I mentioned below, should be something like this:

# the expiration flag should delete the table after 1 hour (3600 is the minimum)
# Deleting tables directly from bq command line seems to not be an option
# https://cloud.google.com/bigquery/docs/samples/bigquery-delete-table 
bq mk --table --expiration 3600 project_id:dataset.temporary_table ./schema.json

# load data from storage into temp table
bq load --skip_leading_rows=1 project_id:dataset.temporary_table 
    "gs://bucket_name/datafile.csv.gz" ./schema.json

# insert data in partitioned table.
bq query 'INSERT `project_id:dataset.complete_table` 
    SELECT timestamp_micros(safe_cast(t1.timestamp as int64)) as timestamp,  
    * except(t1.timestamp) FROM `project_id:dataset.temporary_table` as t1'
user_114821760
Related