0

I run the following query in Google Big Query:

DELETE FROM mydataset.mytable_wrong

WHERE time = "2019-09-01 13:00:00 UTC"

and then I realised it was the wrong table. Can I recover those rows somehow or undo the query?

The table still exists.

Thanks

Edit to add more info: Table is partitioned.

Lev
  • 673
  • 1
  • 12
  • 29

1 Answers1

0

You can use an snapshot decorator to query a point-in-time snapshot of your data. You can revert changes with it. Check how to restore a deleted table, I think you can extrapolate to restore your data.

Also, here's a similar Stackoverflow answer with more info.

Guillermo Cacheda
  • 2,162
  • 14
  • 23
  • Hi @Guillermo, I saw that answer and the decoractor docs. Pretty useful, but I keep having this error: `Table "project_id.mydataset.mytable_wrong@1567500883493" cannot include decorator`. Any ideas? Btw, I used standardSQL and when I try the same with legacySQL it errors again and asks me to use standard. And also the table is partitioned. – Lev Sep 03 '19 at 10:04
  • @Lev chan you check [this answer](https://stackoverflow.com/a/48789171/9908251)? Apparently for standard SQL you have to use `FOR SYSTEM_TIME AS OF`. At least it works for deleted tables – Guillermo Cacheda Sep 03 '19 at 10:28
  • @Lev you get a decorator related error when using `FOR SYSTEM_TIME AS OF`? That's odd. I'm out of ideas, but if you're using Standard SQL, check the [Query Syntax page](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for-system-time-as-of), maybe you see something there that can help get the query working – Guillermo Cacheda Sep 03 '19 at 11:34
  • Having the same problem, the snapshot decorator worked but the data was not there, I don't really understand what happened...maybe SQL deletes don't work? – Albert Casademont Jun 04 '20 at 17:37