如何取消删除 BigQuery 表? [英] How can I undelete a BigQuery table?

问题描述

我不小心删除了一个 BigQuery 表.有没有可能拿回来?API 似乎不支持取消删除.

I've accidentally deleted one of my BigQuery tables. Is it possible to get it back? The API doesn't seem to support undelete.

推荐答案

可以通过表复制和快照装饰器在 BigQuery 中取消删除.也就是说，您可以从删除表之前复制该表的快照.

Undelete in BigQuery is possible via table copy and snapshot decorators. That is, you can copy a snapshot of the table from before the table was deleted.

BigQuery 过去对取消删除有限制，但随着时间的推移，这些限制已被移除.

BigQuery used to have restrictions on undeletes, but over time, those have been removed.

这是一个使用 bq 的示例，但您可以使用 BigQuery 网页界面执行相同的操作.

Here is an example using bq, but you can do the same thing with the BigQuery Web UI.

首先让我们创建一个我们要删除的虚拟 bigquery 数据集和表:

First let's create a dummy bigquery dataset and table that we're going to delete:

$ bq mk -d dataset1
Dataset 'helixdata2:dataset1' successfully created.
$ bq query --destination_table=dataset1.table1 "SELECT 17 as a"
Waiting on bqjob_ra0dedbee5cb4228_0000014a5af133d6_1 ... (0s) 
Current status: DONE   
+----+
| a  |
+----+
| 17 |
+----+

现在，从表处于活动状态时获取当前的 unix 时间戳.

Now, grab the current unix timestamp from a time when the table was alive.

$ date +%s
1418864998

请注意，这个时间以秒为单位，我们需要毫秒.

Note that this time is in seconds, we'll need miliseconds.

不小心"删除了表格

$ bq rm dataset1.table1
rm: remove table 'helixdata2:dataset1.table1'? (y/N) y

现在我们可以通过复制快照来取消删除表:

Now we can undelete the table by copying a snapshot:

$ bq cp dataset1.table1@1418864998000 dataset1.temp
Waiting on bqjob_r4d8174e2e41ae73_0000014a5af2a028_1 ... (0s) 
    Current status: DONE    
Tables 'helixdata2:dataset1.table1@1418864998000' successfully copied to     
    'helixdata2:dataset1.temp'

(注意我们将时间乘以 1000，因为我们想要毫秒)这将表的旧快照复制到 dataset1.temp.让我们将其复制回旧位置，然后删除临时表.

(note we multiplied the time by 1000 since we want milliseconds) This copied an old snapshot of the table to dataset1.temp. Let's copy it back to the old location and then remove the temp table.

$ bq cp dataset1.temp dataset1.table1
Waiting on bqjob_r3c0bb9302fb81d59_0000014a5af2dc7b_1 ... (0s) 
    Current status: DONE    
Tables 'helixdata2:dataset1.temp' successfully copied to 
    'helixdata2:dataset1.table1'
$ bq rm dataset1.temp
rm: remove table 'helixdata2:dataset1.temp'? (y/N) y

现在让我们验证表是否已经恢复:

Now let's verify that the table has been restored:

$ bq query "select * from dataset1.table1"
Waiting on bqjob_r5967bea49ed9e97f_0000014a5af34dec_1 ... (0s) 
    Current status: DONE   
+----+
| a  |
+----+
| 17 |
+----+

这篇关于如何取消删除 BigQuery 表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！