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

2
votes

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:

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:

enter image description here

Next, I pushed 2 records into the partition2 table and to the same partition ($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:

enter image description here

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

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.

What am I doing wrong?

2
google-bigquery
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
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
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
I used the CLI tool - bq cp --append_table <removed>.partition2 <removed>.partition1 - it definitely does not work. I triple checked. – Graham Polley
yes, i got it. I mentioned in my last comment that it is potentially the issue with bq cli – Mikhail Berlyant

2 Answers

2
votes

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

Run:

gcloud components update

0
votes

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.