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

问题描述

我不小心删除了一个BigQuery表。是否有可能恢复？ API似乎不支持取消删除。

解决方案

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

有几个注意事项：

1. 您必须分两步执行此操作;首先，将表格的快照复制到第二个表格。然后，您可以将第二张表复制回原始位置。


2. 您只能取消删除在上一天 2天内删除的表格。


3. 如果您已经重新创建了一个具有相同名称的表格，则可能无法取消删除旧表格。

以下是一个使用 bq 的示例，但您可以使用BigQuery Web UI执行相同的操作。

首先让我们创建一个假的bigquery数据集和我们要删除的表：

  $ bq mk -d dataset1 
数据集'helixdata2：dataset1'已成功创建。 
 $ bq query --destination_table = dataset1.table1SELECT 17 as a
等待bqjob_ra0dedbee5cb4228_0000014a5af133d6_1 ...（0s）
当前状态：完成
 + ---- + 
 | a | 
 + ---- + 
 | 17 | 
 + ---- + 
  

现在，抓住当前unix时间戳，该表还活着。

  $ date +％s 
 1418864998 
  
 
 
 
请注意，这一次是以秒为单位，我们需要以毫秒为单位。
 
 
 
删除表格'意外' 
 
 
  $ bq rm dataset1.table1 
 rm：删除表'helixdata2：dataset1.table1'？ （y / N）y 
  
现在我们可以通过复制快照来取消删除表格：
  $ bq cp dataset1.table1@1418864998000 dataset1.temp 
等待bqjob_r4d8174e2e41ae73_0000014a5af2a028_1 ...（0s）
当前状态：DONE 
 Tables'helixdata2：dataset1.table1@1418864998000'已成功复制到
'helixdata2：dataset1.temp'
  
（注意，我们将时间乘以1000，因为我们需要毫秒）
将表的旧快照复制到 dataset1.temp 。让我们将其复制回旧位置，然后删除临时表。
  $ bq cp dataset1.temp dataset1.table1 
正在等待bqjob_r3c0bb9302fb81d59_0000014a5af2dc7b_1 ...（0s）
当前状态：DONE 
表'helixdata2：dataset1.temp'成功复制到
'helixdata2：dataset1.table1'
 $ bq rm dataset1.temp 
 rm：删除表'helixdata2：dataset1.temp'？ （y / N）y 
  
现在我们来验证表格是否已经恢复：
  $ bq查询select * from dataset1.table1
等待bqjob_r5967bea49ed9e97f_0000014a5af34dec_1 ...（0s）
当前状态：完成
 + ---- + 
 | a | 
 + ---- + 
 | 17 | 
 + ---- + 
  
 
I've accidentally deleted one of my BigQuery tables. Is it possible to get it back? The API doesn't seem to support undelete.
解决方案
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.

There are a couple of caveats: 

You must do this in two steps; first, copy a snapshot of the table to a second table. Then you can copy that second table back to the original location.
You can only undelete tables that were deleted within the last 2 days.
If you have since recreated a table with the same name, you may not be able to undelete the old one.
Here is an example using bq, but you can do the same thing with the BigQuery Web UI.

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

Remove the table 'accidentally'
$ 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'
(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屋！