Запрос к таблице BigQuery, кластеризованной по географическому признаку, не повышает производительность при пересечении географических регионов из других таблиц
У меня есть большая (250 ГБ) таблица BigQuery, представляющая сетку значений, ячейки которой хранятся в виде многоугольников. Таблица кластеризована по этому географическому столбцу. У меня есть вторая таблица с различными полигонами, и я хочу получить значения ячеек сетки внутри одного из этих полигонов (например, запросить некоторые данные о погоде в пределах определенной страны).
Когда я выполняю пространственное пересечение таблицы сетки с одним полигоном с помощью ST_GEOGFROMGEOJSON, я вижу, что стоимость запроса (обработанные байты) невелика (100 МБ). Но когда я выполняю тот же запрос с многоугольником в составе оператора выбора, я вижу, что таблица полностью сканируется и стоимость высока (250 Гб).
Это воспроизводимый пример. При запросе следующего общедоступного набора данных с использованием определенного многоугольника:
SELECT npp FROM `bigquery-public-data.modis_terra_net_primary_production.MODIS_MOD17A3HGF`
WHERE year=2020
AND ST_INTERSECTS(geography_polygon, ST_GEOGFROMGEOJSON('{ "type": "Polygon", "coordinates": [ [ [ -101.1, 50 ], [ -101, 50 ], [ -101, 56 ], [ -101.1, 56 ], [ -101.1, 50 ] ] ] }'))
-- Обработано байтов -- 785,45 МБ -- Оплачено в байтах -- 786 МБ
С тем же многоугольником как частью оператора выбора в таблице:
CREATE OR REPLACE TABLE `tmp.polygon`
AS SELECT ST_GEOGFROMGEOJSON('{ "type": "Polygon", "coordinates": [ [ [ -101.1, 50 ], [ -101, 50 ], [ -101, 56 ], [ -101.1, 56 ], [ -101.1, 50 ] ] ] }') geog;
SELECT npp FROM `bigquery-public-data.modis_terra_net_primary_production.MODIS_MOD17A3HGF`
WHERE year=2020
AND ST_INTERSECTS(geography_polygon, (SELECT geog FROM `tmp.polygon`))
-- Обработано байтов -- 522,15 ГБ -- Оплачено в байтах -- 522,15 ГБ
Я получил те же результаты, используя перекрестные или внутренние соединения.
Почему кластеризация не работает с операторами выбора? Есть ли способ снизить стоимость запроса без добавления пользовательских столбцов кластеризации/секционирования?
Ответы 1
Эта оптимизация реализована BigQuery только для постоянной географии в фильтре.
Решением является разделение запроса на две части с использованием процедурного языка BigQuery (https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language).
DECLARE poly GEOGRAPHY;
SET poly = (SELECT geog FROM `tmp.polygon`);
SELECT npp FROM `bigquery-public-data.modis_terra_net_primary_production.MODIS_MOD17A3HGF`
WHERE year=2020
AND ST_INTERSECTS(geography_polygon, poly);
Это счета 786 МБ.
Обновление для случая, когда многоугольник слишком сложен и вызывает ошибку evaluation limit: давайте сохраним только ограничивающую рамку и постфильтруем, используя реальный многоугольник:
DECLARE box STRUCT<minx FLOAT64, miny FLOAT64, maxx FLOAT64, maxy FLOAT64>;
SET box = (SELECT ST_BoundingBox(geog) FROM `tmp.polygon`);
SELECT npp FROM `bigquery-public-data.modis_terra_net_primary_production.MODIS_MOD17A3HGF`
WHERE year=2020
AND ST_INTERSECTS(geography_polygon, (SELECT geog FROM `tmp.polygon`))
AND ST_INTERSECTSBOX(geography_polygon, box.minx, box.miny, box.maxx, box.maxy);
За него также взимается плата в размере 786 МБ, хотя в редких случаях за него может взиматься дополнительная плата из-за более слабого фильтра.
Возможно, вычислите выпуклую оболочку многоугольника. Это не должно изменить стоимость запроса, но должно уменьшить размер полигона. Затем вы можете объединить грубый фильтр с контекстной оболочкой и точный фильтр с многоугольником из запроса (выбрать...).
Если даже это не помогло, возможно, переключитесь на ST_BoundingBox и ST_IntersectsBox, а также ST_Intersects с помощью (выберите...).
Спасибо. Это действительно работает, однако не работает для сложных многоугольников с
Script expression exceeded evaluation limit of 1048576 bytes at [2:12]. Даже после значительного упрощения многоугольника.