How to copy a partitioned table to another partitioned table in BigQuery

I'm trying to copy one partitioned table into another one. According to the docs, this should be possible.

If you want to copy a partitioned table into another partitioned table, the partition specifications for the source and destination tables must match.

To test, I created two partitioned tables (partition1 & partition2) with the same schema. I pushed 3 records into partition $20170101 on the partition1 table:

partition1

partition2

$20170101

partition1

echo '{"a":1, "b":2}' | bq insert '<removed>.partition1$20170101'
echo '{"a":1, "b":2}' | bq insert '<removed>.partition1$20170101'
echo '{"a":1, "b":2}' | bq insert '<removed>.partition1$20170101'


That looks good:

Next, I pushed 2 records into the partition2 table and to the same partition ($20170101):

partition2

$20170101

echo '{"a":1, "b":2}' | bq insert '<removed>.partition2$20170101'
echo '{"a":1, "b":2}' | bq insert '<removed>.partition2$20170101'


Again, this looks good:

Now, I want to copy append partition2 into partition1. I would expect to see 5 records in partition1 under the $20170101 partition:

partition2

partition1

partition1

$20170101

bq cp --append_table <removed>.partition2 <removed>.partition1
Waiting on bqjob_r6d160e17a3b7b733_0000015bb238aa54_1 ... (0s) Current status: DONE
Tables '<removed>.partition2' successfully copied to '<removed>.partition1'


However, partition1 still only has 3 records in it.

partition1

What am I doing wrong?

I cannot confirm this behavior! I just did all exactly the same steps you described (but in UI) and got expected result - 5 records. Can you double check if you do all as you described?
– Mikhail Berlyant
Apr 28 '17 at 4:21

How did you copy in the UI?. It doesn't allow it if the table already exist. It has to be done via the API...
– Graham Polley
Apr 28 '17 at 4:39

good point. I am not using Google Web UI. The UI that we are using implements all via API calls - so technically it was via API. but it shouldn't matter for the issue you raised, I think. Unless it is an issue with bq command line tool
– Mikhail Berlyant
Apr 28 '17 at 4:41

I used the CLI tool - bq cp --append_table <removed>.partition2 <removed>.partition1 - it definitely does not work. I triple checked.
– Graham Polley
Apr 28 '17 at 4:43

bq cp --append_table <removed>.partition2 <removed>.partition1

yes, i got it. I mentioned in my last comment that it is potentially the issue with bq cli
– Mikhail Berlyant
Apr 28 '17 at 4:43

2 Answers
2

This is a bug in the version of the gcloud tool I had installed. I updated it, and then it worked as expected.

gcloud

Run:

gcloud components update

gcloud components update

I have tried the command something like this from gcloud and it works -->

bq cp -a '<source_project>:<source_dataset>.<source_table>$20180605' <destination_project>:<destination_dataset>.<destination_table>


and it works perfiectly fine....
Note:- In the command above , destination partition value/information need not be mentioned. It automatically fetches from source partition data/value.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.