CODEWITHSUNDEEP
google-bigquery
Manju
Manju

Reputation: 309

Delete tables in BigQuery Cloud Console

I am trying to delete data from Bigquery tables and facing a challenge. At the moment only one date partitioned table drops/deletes at a time. Based on some research and docs on google, I understand I need to use DML operations.

Below are the commands that I used for deletion and it doesn’t work

1.delete from bigquery-project.dataset1.table1_*

2.drop table bigquery-project.dataset1.table1_*;

3.delete from bigquery-project.dataset1.table1_* where _table_suffix='20211117';

The third query works for me and it deletes only for that particular date.

For the 1 and 2 queries, I’ve got a exception saying “Illegal operation (write) on meta-table bigquery-project.dataset1.table1_”

How would I go about deleting over 300 date partitioned tables in one go?

Thanks in advance.

Upvotes: 0

Views: 2267

Answers (1)

Daniel Zagales
Daniel Zagales

Reputation: 3034

You can go the route of creating a stored procedure to generate your statements in this scenario

CREATE OR REPLACE PROCEDURE so_test.so_delete_table()
BEGIN
    DECLARE fqtn STRING;
    FOR record IN
    (
        select concat(table_catalog,".",table_schema,".",table_name) as tn
        from so_test.INFORMATION_SCHEMA.TABLES
        where table_name like 'test_delete_%'
        )
    DO
        SET fqtn=record.tn;
        -- EXECUTE IMMEDIATE format('TRUNCATE TABLE %s',fqtn);
        EXECUTE IMMEDIATE format('DROP TABLE %s',fqtn);
    END FOR;
END

call so_test.so_delete_table();

In the above I query for the tables I would like to remove records from, then pass that to the appropriate statement. In your scenario I could not tell if you were wanting to remove records or the entire table so I included logic for both depending on the scenario.

This could also be modified to take in a table prefix and pass that to the for loop where clause fairly simply.

Alternatively you could just perform the select statement in the for loop, copy the results into a sheet and construct the appropriate DML statements, copy back into the console and execute.

Upvotes: 1

Related Questions