Слияние таблиц BigQuery

Оператор MERGE позволяет актуализировать данные одной таблицы данными из другой, совмещая функционал UPDATE, INSERT и DELETE. Полезно применять в случае, когда требуется обновить часть данных агрегированной таблицы без необходимости сводить заново весь массив данных.

Синтаксис:

MERGE `project_name.dataset.target_table` [[AS] %alias%]
USING `project_name.dataset.source_table` [[AS] %alias%]
ON %conditions%
WHEN MATCHED [AND %search_filter%] THEN (UPDATE|DELETE)
WHEN NOT MATCHED [BY TARGET] [AND %search_filter%] THEN INSERT
WHEN NOT MATCHED BY SOURCE [AND %search_filter%] THEN (UPDATE|DELETE)

В первой строке задается путь к целевой таблице.

Во второй — путь к исходной таблице или подзапрос SELECT. Рекомендуется использовать псевдонимы для таблиц %alias%, в случае они имеют совпадающие схемы.

Далее следует логическое выражение сравнения — столбцы таблиц, по которым происходит сопоставление. В зависимости от комбинаций условий WHEN Меняется тип сопоставления.

Следующий блок инструкций может описывать три ситуации: когда выражение сравнение ИСТИНА MATCHED, когда ЛОЖЬ по цели NOT MATCHED BY TARGET и когда выражение ЛОЖЬ по источнику NOT MATCHED BY SOURCE. Для корректного выполнения запроса необходимо и достаточно наличие хотя бы одной инструкций из трех.

WHEN MATCHED [AND %search_filter%] THEN (UPDATE|DELETE) — описание операции обновления UPDATE или удаления DELETE строк целевой таблицы, выполняемых в случае, когда выполняется условие сравнения. Опционально может содержать условие фильтрации [AND %search_filter%]. В случае, если требуется выполнить обновление строки, необходимо обеспечить уникальность сопоставляемых строк в исходной таблице/подзапросе (целевой строке(-ам) должна соответствовать только одна исходная строка).

WHEN NOT MATCHED [BY TARGET] [AND %search_filter%] THEN INSERT — если условие сравнения не выполнено по цели (в целевой таблице не найдета строка из исходной), то выполняется вставка строки INSERT. Инструкцию [BY TARGET] можно опустить.

WHEN NOT MATCHED BY SOURCE [AND %search_filter%] THEN (UPDATE|DELETE) — описание операций обновления UPDATE или удаления DELETE строк целевой таблицы для тех случаев, когда для них не удалось найти соответствующие строки в исходной таблице.

Примеры запросов. Работа с двумя таблицами.

Целевая таблица target_table

Исходная таблица source_table

Задача: проставить корректные значения цены price и общей стоимости cost товаров целевой таблицы

MERGE `project_name.dataset.target_table` AS A
USING `project_name.dataset.source_table` AS B
ON A.ID = B.ID
WHEN MATCHED THEN UPDATE SET price = B.price, cost = quantity * B.price

Результат:

Задача: проставить корректные значения цены price и общей стоимости cost товаров целевой таблицы и удалить строки, для которых цена неизвестна

MERGE `project_name.dataset.target_table` AS A
USING `project_name.dataset.source_table` AS B
ON A.ID = B.ID
WHEN MATCHED THEN 
	UPDATE SET price = B.price, cost = quantity * B.price
WHEN NOT MATCHED BY SOURCE THEN 
	DELETE

Результат:

Задача: проставить корректные значения цены price и общей стоимости cost для Телефона и Телевизора и добавить строку с идентификатором id=6 и NULL-значениями в неизвестных ячейках.

MERGE `project_name.dataset.target_table` AS A
USING `project_name.dataset.source_table` AS B
ON A.ID = B.ID
WHEN MATCHED AND A.name = 'Телефон' OR A.Name = 'Телевизор' THEN 
	UPDATE SET price = B.price, cost = quantity * B.price
WHEN NOT MATCHED AND B.id = '6' THEN
	INSERT (id, name, quantity, price, cost) VALUERS (B.id, null, null, B.price, null)

Результат:

Примеры запросов. Модификация одной таблицы (использование подзапросов).

Целевая таблица target_table

Задача: удалить из таблицы товары количеством меньше 5, для оставшихся определить итоговую стоимость.

MERGE `project_name.dataset.target_table` A
USING (SELECT * FROM `project_name.dataset.target_table` WHERE quantity > 5) B
ON A.ID = B.ID
WHEN MATCHED THEN 
	UPDATE SET cost = quantity * B.price
WHEN NOT MATCHED THEN
	DELETE

Результат:

Целевая таблица target_table

Задача: присвоить верные названия для товаров, уже имеющихся в таблице

MERGE
`project_name.dataset.target_table` A
USING (SELECT id AS id, name AS name FROM `project_name.dataset.target_table` WHERE name IS NOT NULL GROUP BY id, name) B
ON A.id = B.id
WHEN MATCHED AND name IS NULL THEN
	UPDATE SET name = name * B.name

Результат: