Transfering A CSV File From Cloud Storage Into A BigQuery Partitioned Table Using BigQuery Transfer Service

Question

I have created a BigQuery table, partitioned by month.

I now want to transfer a CSV stored in Google Cloud Storage to the table using BigQuery transfer.

However, my last attempt failed. For the destination table I put: table_a${run_time|"%m%d"} but returned the following error:

Invalid monthly partitioned partition key: 0117 (it ran on the 17th of January. Hence 0117.)

What should I put for destination table, given that the table has been partitioned by month?

Answer 1

If partitioned by month, the partition key is <year><month>, while you put <month><day>, for instance

create or replace table yourtable (ts timestamp)
PARTITION BY TIMESTAMP_TRUNC(ts, month)
AS select timestamp '2020-1-1 1:00:00' ts;

The created table will have one partition: yourtable$202001