Copy partitioned bigquery table that only overwrites the partitions from the source table

Question

So something like

bq cp -f src_table dst_table

but I want partitions in dst_table that are not present in src_table to stay unoverwritten. Is something like this possible?

Answer 1

You can do something like this:

Use this query to build script for bq command

#legacySql        
select concat ('bq cp -f ', s.project_id, ':', s.dataset_id, '.', 
s.table_id, '\$', s.partition_id, ' ', 
t.project_id, ':', t.dataset_id, '.', t.table_id, '\$', t.partition_id, ';') 
from [source_table$__PARTITIONS_SUMMARY__] s
inner join (select * from [target_table$__PARTITIONS_SUMMARY__]) t
on t.partition_id = s.partition_id

And just execute result in terminal Make sure you escaped $ - this code will work for Mac/Unix - not sure about Windows

Answer 2

If I understood well your question, you intend to copy from one partitioned table (table 1) to another one (table 2) just the partitions filled in table 1.

You have to configure the required permissions to perform the operation, as described here. In your case, use bq cp -a to

append the data from the source partition to an existing table or partition in the destination dataset

instead of bq cp -f, which forces overwriting. For example:

bq --location=[LOCATION] cp -a -f -n [PROJECT_ID]:[DATASET].[SOURCE_TABLE]$[SOURCE_PARTITION] [PROJECT_ID]:[DATASET].[DESTINATION_TABLE]$[DESTINATION_PARTITION]