Почему наличие подзапроса внутри предложения IN запускает полное сканирование, а использование массива — нет?
У меня есть две таблицы BigQuery A (~ 100 МБ) и B (~ 69 ГБ). Таблица A разделена по полю даты и имеет несколько полей. Таблица B не разделена и имеет только одно поле даты. Я хочу удалить все записи в таблице A, которые находятся в пределах дат раздела в таблице B.
Использование подзапроса внутри моего предложения delete where запускает полное сканирование таблицы B:
DELETE
FROM `table_B`
WHERE cdate IN (SELECT DISTINCT (cdate) FROM `table_A`)
В моем случае с BigQuery это обрабатывает ~69 ГБ: https://i.stack.imgur.com/nvLQD.png
Однако, если я пишу все даты в виде массива вместо использования подзапроса, он читает только указанные разделы:
DELETE
FROM `table_B`
WHERE cdate IN ("2022-04-01","2022-04-02", "2022-04-03", ...)
В моем случае с BigQuery это обрабатывает ~12,5 ГБ: https://i.stack.imgur.com/xCSzu.png
Я понимаю, что подзапрос имеет накладные расходы на выборку записей из таблицы A, но 69 ГБ обработки наводят меня на мысль, что в таблице B происходит полное сканирование.
Я пробовал несколько разных вещей, таких как использование временной таблицы и оператора WITH, но результат тот же. Я не хочу прибегать к написанию процедурного оператора и ручному построению массива, это было бы довольно громоздко, но у меня заканчиваются варианты.
Как оценивается подзапрос? Есть ли другой способ выполнить то, что происходит во втором запросе, который мне не хватает?
Я попробовал это, к сожалению, он обработал все 69 ГБ: i.imgur.com/VeJcieX.png.
В этом случае вам могут помочь динамические запросы, но это скрипт, и пробный прогон в любом случае может помочь вам в этой задаче.
@guillaumeblaquiere, вы имеете в виду динамическую генерацию запроса со значениями массива? Я избегаю этого решения из-за возможности иметь тысячи разных значений (в моем примере это уже ~ 250, что много). Элегантное решение было бы лучше, но если я не найду ничего другого, мне придется бежать с этим.
Да, динамический запрос — это создание строки с последующим немедленным выполнением. И действительно, ваш IN может быть огромным, но не больше, чем во время выполнения! При этом я не знаю, как часто вы запускаете свой запрос, но 70Go не очень большой...
Это довольно часто, каждые 15 минут на самом деле! Я подожду, чтобы увидеть, появятся ли другие решения. Спасибо!
Ответы 3
В первом запросе необходимо оценить подзапрос для каждой строки в таблице B, чтобы проверить, соответствует ли cdate совпадение или нет. Это должно быть очень дорогой операцией в большой таблице.
Вы можете добиться лучшей производительности с помощью not exists:
DELETE
FROM `table_B` b
WHERE EXISTS (
SELECT 1
FROM `table_A` a
WHERE a.cdate= b.cdate
)
Мне кажется, при таком методе обработка увеличивается: i.imgur.com/8RfrRSO.png
Используя вид РАЗДЕЛЫ :
DELETE FROM `table_B` WHERE cdate IN (
SELECT partition_id
FROM `your-project-name.your_dataset.INFORMATION_SCHEMA.PARTITIONS`
WHERE table_schema = 'your_dataset' AND table_name = 'table_A'
);
Или вы можете попробовать динамический SQL
EXECUTE IMMEDIATE FORMAT("""
DELETE FROM `table_B` WHERE cdate IN ('%s')
""", (
SELECT STRING_AGG(partition_id, "','")
FROM `your-project-name.your_dataset.INFORMATION_SCHEMA.PARTITIONS`
WHERE table_schema = 'your_dataset' AND table_name = 'table_A'
));
-- or, without using PARTITIONS view.
EXECUTE IMMEDIATE FORMAT("""
DELETE FROM `table_B` WHERE cdate IN ('%s')
""", (SELECT STRING_AGG(DISTINCT cdate, "','") FROM `table_A`))
Я вижу, часть представления PARTITIONS не будет работать для меня, поскольку я не собираюсь удалять из B разделы в A, я хочу удалить разделы в B, которые соответствуют значениям столбца cdate в A. часть динамического SQL можно было бы обновить, чтобы сделать это с помощью моего исходного запроса, но я избегал этого из-за того, что он выглядел немного хакерским. Если не будет альтернативы, я, вероятно, воспользуюсь этим. Спасибо за ответ!
Итак, как и ожидалось, написание динамического запроса на процедурном языке BQ работает:
EXECUTE IMMEDIATE FORMAT("""
SELECT * FROM `table_B` WHERE cdate IN ( '%s' )
""", (SELECT STRING_AGG(DISTINCT CAST(cdate AS STRING), "','") FROM `table_A`))
Это в моем случае BigQuery обрабатывает 2 КБ для построения оператора и 12,5 ГБ для фактического запроса, то есть такая же обработка, как ввод дат в массиве. Это решает мою проблему, но я все же хотел бы понять, как BigQuery оценивает подзапрос, и действительно ли это единственный способ сделать это, поскольку я нахожу его далеко не элегантным.
Вы выполнили запрос и увидели результат в логах (TotalByteBilled)? DryRun не может предположить, что вы получите от подзапроса (без его запуска!), поэтому предложит худший вариант. Но во время выполнения возможно, что оптимизировано, поэтому, если вы посмотрите в журналах, чтобы увидеть истинный результат.