[SOLVED] When you create an integer-range partitioned table and set the range_partitioning (start, end, interval) value, can you update it later?

When you create an integer-range partitioned table and set the range_partitioning (start, end, interval) value, can you update it later?

Currently I'm working on a task to partition a table that I loaded from CSV. I want to partition on the unix time (INT). If I set the range partitioning (start, end, interval) value now, can I update it later?

For example, if I initially set the range partitioning to (unix time now, unix time + 2 years, 1 day interval) and want to update the end value, would that be possible with

bq update --range_partitioning=COLUMN_NAME, START, END, INTERVAL

Solution

It is not possible to change the partition specification of an existing table. A workaround could be copying the data of your current partitioned table to a new table with the new partition you want.

In SQL you can run this query:

CREATE TABLE `project-id.dataset_id.new_partitioned_table`
PARTITION BY RANGE_BUCKET(UTCTimestamp, GENERATE_ARRAY(5, 200, 20))
AS
SELECT * FROM `project-id.dataset_id.old_partitioned_table`