1

Is it possible to recover data in a table overwritten with WRITE_TRUNCATE in BigQuery?

S.Mohsen sh
  • 2,028
  • 3
  • 21
  • 32

1 Answers1

3

Yes. You can recover data using Table Decorators - in particular Snapshot Decorator

you should figure out <time> right before you did overwrite your table and than run below query with destination table of your choice

SELECT * FROM YourTable@<time>

Please note: this will cost you querying that table
Technically, you can avoid that cost by using COPY instead of Query. So yo can copy YourTable@<time>. Copying is free

bq cp YourTable@<time> <destination_table>

See more about Copy an existing table

To follow up S.Mohsen sh comments:

Above approach will work if yo just simply overwrote table using overwrite table write preference. But if you actually deleted table and than created another table with same name - I don't think above approach will work - you can experiment though!

Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
  • it seems in the doc that this only works with deletion not when data is overwritten and table with the same name and different data exists; Am I right? is there a way to recover overwritten tables? – S.Mohsen sh Dec 10 '16 at 06:12
  • it works with overwritten table too. unless you mean something else by "overwritten data" - just try. It definitelly works for me – Mikhail Berlyant Dec 10 '16 at 06:16
  • 1
    reading again your comment and looks like you have not just simply overwritten data, but most likely deleted original table and than created another table with the same name but different schema - if this is a case - i am not sure that snapshot approach will help. I think if you create table later - you will not be able to recover old table. – Mikhail Berlyant Dec 10 '16 at 06:20
  • no i did overwrite the data and thanks, it worked for me. My first tries had failed because I was using a wrong time format – S.Mohsen sh Dec 10 '16 at 06:28
  • super. glad you get your data back :o) – Mikhail Berlyant Dec 10 '16 at 06:29