BigQuery - Delete rows from table partitioned by date

Question

I have a dataset.table partioned by date (100 partition) like this :

table_name_(100) which means : table_name_20200101, table_name_20200102, table_name_20200103, ...

Exemple of table_name_20200101 :

|       id      |       col_1       |       col_2       |       col_3       |
-----------------------------------------------------------------------------
|       xxx     |       2           |       6           |       10          |
|       yyy     |       1           |       60          |       29          |
|       zzz     |       12          |       61          |       78          |
|       aaa     |       18          |       56          |       80          |

I would like to delete the row ID = yyy in all the table (partioned) :

DELETE FROM `project_id.dataset_id.table_name_*`
WHERE id = 'yyy'

I got this error :

Illegal operation (write) on meta-table project_id:dataset_id.table_name_*

Is there a way to delete rows 'yyy' in all table (partioned) ?

Thank you

user_212435 · Answer

Okay, some various things to call out here to ensure we're using consistent terminology.

You're talking about sharded tables, not partitioned. In a partitioned table, the data within the table is organized based on the partitioning specification. Here, you just have a series of tables named using a common prefix and a suffix based on date.
The use of the table_prefix* syntax is called a wildcard table, and DML is explicitly not allowed via wildcard tables: https://cloud.google.com/bigquery/docs/querying-wildcard-tables
The table_name_(100) is an aspect of how the BigQuery UI collapses series of like-named tables to save space in the navigation panes. It's not how the service itself references tables at all.

The way you can accomplish this is to leverage other aspects of BigQuery: The INFORMATION_SCHEMA tables and scripting functionality.

Information about what tables are in a dataset is available via the TABLES view: https://cloud.google.com/bigquery/docs/information-schema-tables

Information about scripting can be found here: https://cloud.google.com/bigquery/docs/reference/standard-sql/scripting

Now, here's an example that combines these concepts:

DECLARE myTables ARRAY;
DECLARE X INT64 DEFAULT 0;
DECLARE queryStr STRING;

# First, we query INFORMATION_SCHEMA to generate an array of the tables we want to process.
# This INFORMATION_SCHEMA query currently has a LIMIT clause so that if you get it wrong,
# you won't bork all the tables in the dataset in one go.
SET myTables = (
  SELECT
    ARRAY_AGG(t)
  FROM (
    SELECT
      TABLE_NAME as t
    FROM `my-project-id`.my_dataset.INFORMATION_SCHEMA.TABLES 
    WHERE
      TABLE_TYPE = 'BASE TABLE' AND
      STARTS_WITH(TABLE_NAME, 'table_name_')
    ORDER BY TABLE_NAME
    LIMIT 2
  )
);

# Now, we process that array of tables using scripting's loop construct,
# one at a time.
LOOP
  IF X >= ARRAY_LENGTH(myTables)
    THEN LEAVE;
  END IF;
  # DANGER WILL ROBINSON: This mutates tables!!!
  # 
  # The next line constructs the SQL statement we want to run for each table.
  #
  # In this example, we're constructing the same DML DELETE
  # statement to run on each table.  For safety sake, you may want to start with 
  # something like a SELECT query to validate your assumptions and project the 
  # myTables values to see what you're getting.
  SET queryStr = "DELETE FROM `my-project-id`.my_dataset." || myTables[SAFE_OFFSET(X)] || " WHERE id = 'yyy'";
  # Now, run the generated SQL via EXECUTE IMMEDIATE.
  EXECUTE IMMEDIATE queryStr;
  SET X = X + 1;
END LOOP;

BigQuery - Delete rows from table partitioned by date

1 Answers