CODEWITHSUNDEEP
google-bigquery
Patel
Patel

Reputation: 429

Recover overwritten Bigquery table with older table schema

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

Upvotes: 2

Views: 2653

Answers (2)

KCD
KCD

Reputation: 10281

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

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

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 ....

Upvotes: 3

Related Questions