Bigquery partition not being used while deleting data

user_4407645 0
Viewed 63

Our project is being charged a high cost. Looks like such commands are not using the partition. Is there a way in bigquery to force the query to become partition aware based on the results for a subquery ?

The following query currently is processing TBs of data.

    delete from tableName
    where QuoteDate >= cast((SELECT batch_date as start  from batch_table where  Batch_Status='Running') as timestamp)

Processes 1TB of data.

Ideally this should only process hundred MB of data.

delete from TableName
    where
        QuoteDate >= '2018-11-01'

Processes ~100MB

google-bigquery
2 Answers

From the documentation:

Complex queries that require the evaluation of multiple stages of a query in order to resolve the predicate (such as inner queries or subqueries) will not prune partitions from the query.

...

The following query does not prune partitions (note the use of a subquery):

#standardSQL
SELECT
  t1.name,
  t2.category
FROM
  table1 t1
INNER JOIN
  table2 t2
ON
  t1.id_field = t2.field2
WHERE
  t1.ts = (SELECT timestamp from table3 where key = 2)

For the time being, you need to evaluate the queries that you have provided separately.

user_62533470

With BigQuery scripting (Beta now), there is a way to reduce the cost.

Basically, a scripting variable is defined to capture the dynamic part of a subquery. Then in subsequent query, scripting variable is used as a filter to prune the partitions to be scanned.

DECLARE date_filter DATETIME 
  DEFAULT (SELECT batch_date from batch_table where  Batch_Status='Running');

delete from tableName
where QuoteDate >= date_filter;
user_112062020
Related