Can not delete a partitioned bigquery table

Question

I have a partitioned table in bigquery which needs to be unpartitioned. It is empty at the moment so I do not need to worry about losing information. I deleted the table by clicking on it > choosing delete > typing delete but the table is still there even I refreshed the page or wait for 30 mins.

Then I tried the cloud shell terminal:

bq rm --table project:dataset.table_name

then it asked me to confirm and deleted the table but the table is still there!!!

Once I want to create an unpartitioned table with the same name, it gives an error that a table with this name already exists!

I have done this many times before, not sure why the table does not get removed?

Answer 1

Deleting partitions from Google Cloud Console is not supported. You need to execute the command bq rm with the -t shortcut in the Cloud Shell.

Before deleting the partitioned table, I suggest verifying you are going to delete the correct tables (partitions).

You can execute these commands:

for table in `bq ls --max_results=10000000 $MY_DATASET_ID | grep TABLE | grep $MY_PART_TABLE_NAME | awk '{print $1}'`; do echo $MY_DATASET_ID.$table; done

For the variable $MY_PART_TABLE_NAME, you need to write the table name and delete the partition date/value, like in this example "table_name_20200818".

After verifying that these are the correct partitions, you need to execute this command:

for table in `bq ls --max_results=10000000 $MY_DATASET_ID | grep TABLE | grep $MY_PART_TABLE_NAME | awk '{print $1}'`; do echo $MY_DATASET_ID.$table; bq rm -f -t $MY_DATASET_ID.$table; done