2

I accidentally overwrote an existing table by using it as a temporary table to store result of another select. Is there a way to roll it back if both the old table and new table has a different table structure? Is it possible to prevent someone from overwriting a particular table to prevent this in future?

There is a comment in following question which says it is not possible to recover if table schema is different. Not sure if that has changed recently.

Is it possible to recover overwritten data in BigQuery

Patel
  • 399
  • 5
  • 12

2 Answers2

3

first overwrite your table again with something (anything) that has exact same schema as your "lost" table

Then follow same steps as in referenced post - which is : 

SELECT * FROM [yourproject:yourdataset.yourtable@<time>]

You can use @0 if your table was not changed for last week or so or since creation

Or, to avoid cost - do bq cp ....

Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
  • So, If i understand this correctly, bq cp will recover the old table with data as long as the current structure matches the structure i am trying to recover. Problem is that I don't even know the old schema. Table was created from a script that doesn't exist any more. I am doing this only for testing our implementation with BigQuery right now. However, I am more concerned that someone may overwrite data just like I did. Is there a way to prevent schema modification for certain tables in a dataset by anyone but Admin? – Patel Feb 05 '18 at 18:31
  • 1
    yes - for the first part . and - yes - you must know schema. if that table was used - you might "reverse engineer" the schema by checking for old jobs that used that table – Mikhail Berlyant Feb 05 '18 at 18:34
0

You could restore in SQL. But this loses column nullable and description fields and incurs query costs

bq query --use_legacy_sql=false "CREATE OR REPLACE TABLE project:dataset.table AS SELECT * FROM project:dataset.table FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 MINUTE)"

I recently found this to be more effective

Get a unix time stamp in milliseconds and override itself with cp

bq query --use_legacy_sql=false "SELECT DATE_DIFF(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 100 MINUTE), TIMESTAMP '1970-01-01', MILLISECOND)"
bq cp project:dataset.table@1625288152215 project:dataset.table

Before you do it you can check with the following

bq show --schema --format=prettyjson project:dataset.table@1625288152215 > schema-a.json
bq show --schema --format=prettyjson project:dataset.table > schema-b.json
diff schema-a.json schema-b.json
KCD
  • 9,873
  • 5
  • 66
  • 75