Google BigQuery - 如何使用bq命令删除表？

Google BigQuery - bq command enable you to create, load, query and alter table.

Google BigQuery - bq命令可让您创建,加载,查询和更改表。

I did not find any documentation regarding dropping table, will be happy to know how to do it.

我没有找到关于丢桌的任何文件,很高兴知道如何做到这一点。

I found the bq tool much easier to implement instead of writing python interface for each command.

我发现bq工具更容易实现,而不是为每个命令编写python接口。

Thanks.

4 个解决方案

#1

found it :

找到了 :

bq rm -f -t data_set.table_name

-t for table, -f for force, -r remove all tables in the named dataset

-t表,-f表示强制,-r删除指定数据集中的所有表

great tool.

#2

Is there a way to bulk delete multiple tables? – activelearner

有没有办法批量删除多个表? - activelearner

In bash, you can do something like:

在bash中,您可以执行以下操作:

for i in $(bq ls -n 9999 my_dataset | grep keyword | awk '{print $1}'); do bq rm -ft my_dataset.$i; done;

Explanation:

• bq ls -n 9999 my_dataset - list up to 9999 tables in my dataset

bq ls -n 9999 my_dataset - 在我的数据集中列出最多9999个表

• | grep keyword - pipe the results of the previous command into grep, search for a keyword that your tables have in common

| grep关键字 - 将上一个命令的结果传递给grep,搜索表格共有的关键字

• | awk '{print $1}' - pipe the results of the previous command into awk and print only the first column

| awk'{print $ 1}' - 将上一个命令的结果输入awk并仅打印第一列

• Wrap all that into a for loop

将所有内容包装到for循环中

• do bq rm -ft my_dataset.$i; done; - remove each table from your dataset

做bq rm -ft my_dataset。$ i;完成; - 从数据集中删除每个表

I would highly recommend running the commands to list out the tables you want to delete before you add the 'do bq rm'. This way you can ensure you are only deleting the tables you actually want to delete.

我强烈建议您在添加'do bq rm'之前运行命令列出要删除的表。这样,您可以确保只删除实际要删除的表。

#3

You can use Python code (on Jupyter Notebook) for the same purpose:

bigquery_client  = bigquery.Client() #Create a BigQuery service object
dataset_id='Name of your dataset'
table_id='Table to be deleted'
table_ref = bigquery_client.dataset(dataset_id).table(table_id)
bigquery_client.delete_table(table_ref)  # API request
print('Table {}:{} deleted.'.format(dataset_id, table_id))

if you want to delete complete dataset:

If dataset contains tables as well. And we want to delete dataset containing tables in one go the command is:

如果数据集也包含表格。我们想要一次性删除包含表的数据集,命令是:

!bq rm -f -r serene-boulder-203404:Temp1   # It will remove complete data set along with the tables in it

If your dataset is empty then you can use the following command as well: To use the following command make sure that you have deleted all the tables in that dataset otherwise, it will generate an error (dataset is still in use).

如果您的数据集为空,那么您也可以使用以下命令:要使用以下命令,请确保已删除该数据集中的所有表,否则将生成错误(数据集仍在使用中)。

#Now remove an empty dataset using bq command from Python
!bq rm -f dataset_id
print("dataset deleted successfully !!!")

#4

I used the command line for loop to delete a month of table data, but this is reliant on your table naming:

我使用命令行for循环来删除一个月的表数据,但这取决于你的表命名:

for %d in (01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31) DO bq rm -f -t dataset.tablename_201701%d

for%d in(01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31)DO bq rm -f -t dataset.tablename_201701%d