BigQuery not dealing with timestamp in millisecond with partition column

3
votes

I have a unix timestamp column which is represented in millisecond in my csv file. Now when I insert this data in my bigQuery table and query it I get this error

bigQuery not supporting milliseconds timestamps

Now I would like to make this column as a partition column. I have a few questions 1) Even if I save it as int64, how can I make a partition column on this field? 2) I would like to avoid duplicate tables.

1
google-bigquery
I'm not a BigQuery expert, but according to the docs you need a date or timestamp column for the partition. So just populate a timestamp column with your data. – Tim Biegeleisen

1 Answers

1
votes

If your timestamp data is represented in milliseconds, you won't be able to properly create the Partitioned table. Instead you should use a "TIMESTAMP or DATE column" as stated by @TimBiegeleisen. Timestamp will use microsecond precision. Once your column is in microsecond you can use something like the following to create the partitioned table:

bq load --schema <your-timestamp-column>:TIMESTAMP,<some-other-column>:FLOAT --skip_leading_rows=1 --source_format=CSV --time_partitioning_field=<your-timestamp-column> <your-dataset>.<your-table> <your-csv-file>

(use --skip_leading_rows if you have the column names in the csv file.)

Query your table using Standard SQL not Legacy, as you can see in official docs here:

You cannot use legacy SQL to query partitioned tables or to write query results to partitioned tables.