How to recover deleted View in BigQuery?

Question

I have deleted a view in bigquery. I came to know there is an option to restore deleted tables based on table snapshot decorators. Is there any way to retrieve deleted bigquery views?

Answer 1

There is a way to get it back. You need to search for insertion logs rolled at a time of creating your view. This logs can be seen from stackdriver logging. Here are the steps :

1. Go to GCP console

2. Click on stackdriver logging

3. Click on arrow present in search text box and select "Convert to advance filter"
4. Now remove everything and paste below query in it. Don't forget to put your view name in a query.

resource.type="bigquery_resource" protoPayload.methodName="tableservice.insert" protoPayload.serviceData.tableInsertRequest.resource.tableName.tableId="Your_view_name"

5. You will get record of your view creation, Now click on Expand all and go to view section. There you can find a query used at a time of view creation.
6. Just paste that query in bigquery and click on save view.

Alternatively, if you want to search for updates to the view to recover a specific version you can use the following filter:

resource.type="bigquery_resource"
protoPayload.serviceData.tableUpdateRequest.resource.tableName.datasetId="<dataset_id>"
protoPayload.resourceName="projects/<project_id>/datasets/<dataset_id>/tables/<table_or_view_id>"

I hope this helps you get back your BQ view.