BigQuery Limit Rows Scanned by Merge DML

Question

Given DML statement below, is there a way to limit numbers of rows scanned by target table? For example, let's say we have a field shard_id which the table is partitioned with. I know beforehand that all update should happen in some range of shard_id. Is there a way to specify where clause for target to limit numbers of rows that need to be scanned so update does not have to do a full table scan to look for an id?

MERGE dataset.table_target target
USING dataset.table_source source
ON target.id = "123"
WHEN MATCHED THEN
UPDATE SET some_value = source.some_value
WHEN NOT MATCHED BY SOURCE AND id = "123" THEN
DELETE

Linked: https://stackoverflow.com/questions/51194105/cannot-query-over-table-without-a-filter-that-can-be-used-for-partition-eliminat — Pentium10, Jul 10 '18 at 15:07

score 2 · Accepted Answer · answered Jul 10 '18 at 15:05

2

The ON condition is the Where statement where you need to write your clause.

ON target.id = "123" AND DATE(t.shard_id) BETWEEN date1 and date2

answered Jul 10 '18 at 15:05

Pentium10

204,586
122
423
502

score 2 · Answer 2 · answered Jul 12 '18 at 06:11

For your case, it's incorrect to do the partition pruning by ON condition. Instead, you should do that in WHEN clause.

There is an example exactly for such scenario at https://cloud.google.com/bigquery/docs/using-dml-with-partitioned-tables#pruning_partitions_when_using_a_merge_statement.

Basically, the ON condition is used as the matching condition to join target & source tables in MERGE. Following two queries shows the difference between join condition and where clause,

Query 1:

with
t1 as (
  select '2018-01-01' pt, 10 v1 union all
  select '2018-01-01', 20 union all
  select '2000-01-01', 10),
t2 as (select 10 v2)
select * from t1 left outer join t2 on v1=v2 and pt = '2018-01-01'

Result:

pt          v1  v2
2018-01-01  10  10
2018-01-01  20  NULL
2000-01-01  10  NULL

Query 2:

with
t1 as (
  select '2018-01-01' pt, 10 v1 union all
  select '2018-01-01', 20 union all
  select '2000-01-01', 10),
t2 as (select 10 v2)
select * from t1 left outer join t2 on v1=v2 where pt = '2018-01-01'

Result:

pt          v1  v2
2018-01-01  10  10
2018-01-01  20  NULL

BigQuery Limit Rows Scanned by Merge DML

2 Answers2