How to take some data from one partition in the BigQuery table and insert to the next partition?

Question

I have a Big Query table with daily partitions

Now the problem is in one of the partitions i.e. the last partition of the month (for example : 2019-12-31) I have some data that should belong to the next partition i.e 2020-01-01.

I want to know if it is possible to take out that data from my partition 2019-12-31 and put it in the next partition 2020-01-01 using Big Query SQL? or do I have to create a Beam job for it?

Answer 1

Yes, using DML. UPDATE statement moves rows from one partition to another. Updating data in a partitioned table using DML is the same as updating data from a non-partitioned table.

For example, the following UPDATE statement moves rows from one partition to another. Rows in the May 1, 2017 partition (“2017-05-01”) of mytable where field1 is equal to 21 are moved to the June 1, 2017 partition (“2017-06-01”).

UPDATE 
project_id.dataset.mycolumntable
SET
ts = "2017-06-01"
WHERE
DATE(ts) = "2017-05-01"
AND field1 = 21