Inserting data

Inserting data into ingestion-time partitioned tables

When you use a DML statement to add rows to an ingestion-time partitioned table,
you can specify the partition to which the rows should be added. You reference
the partition using the _PARTITIONTIME pseudo column.

For example, the following INSERT statement adds a row to the May 1, 2017
partition of mytable — “2017-05-01”.

Deleting data

Deleting data in ingestion-time partitioned tables

The following DELETE statement deletes all rows from the June 1, 2017
partition ("2017-06-01") of mytable where field1 is equal to 21. You
reference the partition using the _PARTITIONTIME pseudo column.

Updating data

Updating data in ingestion-time partitioned tables

The following UPDATE statement moves rows from one partition to another.
Rows in the May 1, 2017 partition (“2017-05-01”) of mytable where field1
is equal to 21 are moved to the June 1, 2017 partition (“2017-06-01”).

Updating data in partitioned tables

Updating data in a partitioned table using DML is the same as updating data
from a non-partitioned table. For example, the following UPDATE
statement moves rows from one partition to another. Rows in the May 1, 2017
partition (“2017-05-01”) of mytable where field1 is equal to 21 are
moved to the June 1, 2017 partition (“2017-06-01”).

Using a MERGE statement

You use a DML MERGE statement
to combine INSERT, UPDATE, and DELETE operations for a partitioned table
into one statement and perform them atomically.

Pruning partitions when using a MERGE statement

When you run a MERGE statement against a partitioned table, you can limit the
partitions involved in the statement by using the _PARTITIONTIME pseudo column
(for ingestion-time partitioned tables) or by using the date or timestamp column
(for partitioned tables). Pruning partitions reduces cost and improves query
performance.

You can use partition pruning conditions in the following places: in a subquery
filter, a search_condition filter, or a merge_condition
filter.

Each of the examples below queries an ingestion-time partitioned table using
the _PARTITIONTIME pseudo column.

Using a subquery to filter source data

You can use a filter in a subquery to prune partitions. For example, in the
following MERGE statement, only the rows in the '2018-01-01' partition in
the source table are scanned.

Using a filter in the search_condition of a when_clause

The query optimizer attempts to use a filter in a search_condition to prune
partitions. For example, in the following MERGE statement, only the rows in
the following partitions are scanned in the target table: '2018-01-01',
'2018-01-02', and '2018-01-03'.

MERGE dataset.target T
USING dataset.source S
ON T.c1 = S.c1
WHEN MATCHED AND T._PARTITIONTIME = '2018-01-01' THEN
UPDATE SET c1 = S.c1
WHEN MATCHED AND T._PARTITIONTIME = '2018-01-02' THEN
UPDATE SET c1 = c1 + 10
WHEN NOT MATCHED BY SOURCE AND T._PARTITIONTIME = '2018-01-03' THEN
DELETE

In the following example, the WHEN NOT MATCHED BY SOURCE clause needs all data
from the target table. As a result, all partitions are scanned, and you are
charged for the bytes read in all partitions.

MERGE dataset.target T
USING dataset.source S
ON T.c1 = S.c1
WHEN MATCHED AND T._PARTITIONTIME = '2018-01-01' THEN
UPDATE SET c1 = S.c1
WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET c1 = c1 + 1

In general, when you use the WHEN NOT MATCHED and WHEN NOT MATCHED BY SOURCE
clauses together, BigQuery assumes a FULL OUTER JOIN between the
source and target tables. Normally, partitions cannot be pruned in a FULL OUTER
JOIN. However, if a constant false predicate
is used, the filter condition can be used for partition pruning. The following
query uses partition pruning to scan only the '2018-01-01' partition in both
the target and source tables.

MERGE dataset.target T
USING dataset.source S
ON FALSE
WHEN NOT MATCHED AND _PARTITIONTIME = '2018-01-01' THEN
INSERT(c1) VALUES(c1)
WHEN NOT MATCHED BY SOURCE AND _PARTITIONTIME = '2018-01-01' THEN
DELETE

Using a filter in a merge_condition

The query optimizer attempts to use a filter in a merge_condition to prune
partitions. For example, following query will only scan the '2018-01-01'
partition in both the target and source tables.