I have a table in Big Query, which is partitioned, has primary keys and some clustering keys as well. I want to rename this table. Big Query docs mention that tables can be renamed using:
ALTER TABLE mydataset.mytable RENAME TO mynewtable;
I tried it, and it worked when the table was partitioned. However, if the table contained any Primary Keys then BQ said it was not allowed. This limitation is not mentioned in the docs. Given this, what are the options to rename/copy a partitioned table which has PKs and clustering keys. I know that I can first re-create the target table with the appropriate partitioning, PKs and clustering and then use
INSERT INTO <Target_Table> SELECT * FROM <Source_Table>
Is there any alternative where I do not have to re-create the target table first?
Instead of tinkering with table, just drop primary key(s), rename table and recreate primary keys:
Both foreign keys and primary keys in BQ are not enforced - it should be way faster than recreating table all together.