Блог

У меня есть большая таблица запросов, в которой есть только один столбец с именем «точка». Он содержит координаты местоположения, которые я хочу сгруппировать с помощью функции ST_CLUSTERDBSCAN в BigQuery.

Я использую следующий запрос:

 SELECT ST_CLUSTERDBSCAN(point, 2000, 200) OVER () AS cluster_num 
FROM mytable
 

Я получаю эту ошибку:

Превышение ресурсов во время выполнения запроса: запрос не удалось выполнить в выделенной памяти. Пиковое использование: 128% от лимита. Лучший потребитель(ы) памяти: аналитические предложения OVER (): 97% другое/не приписано: 3%

Насколько я понимаю, это связано с тем, что запрос требует много памяти. Есть ли какой-либо способ использовать кластеризацию моих данных, учитывая, что моя таблица содержит миллионы строк?

Большинство аналитических функций в BigQuery в настоящее время запускают один раздел на одном сегменте (машине), и, таким образом, размер раздела ограничен в памяти размером данных около 1 ГБ. В вашем запросе OVER () это означает, что нет разделения — все данные выполняются в одном разделе.

Решение обычно заключается в разделении данных на некоторую большую степень детализации. Например, если данные имеют некоторую пространственную иерархию, вы можете разделить их по этому столбцу — например, сделать OVER(PARTITION BY state) . Конечно, это означает, что не будет кластеров между государствами, поэтому результат не совсем тот же, но если есть естественная кластеризация, это обычно разумно.

Если такая внутренняя иерархия недоступна, другой вариант — разделить, скажем, на короткий геохеш (с очень небольшим количеством букв-ровно столько, сколько необходимо, чтобы избежать ошибок превышения ресурса), что-то вроде OVER(PARTITION BY st_geohash(point, 2)) . Хороший вариант S2_CellIDFromPoint(ST_Centroid(geo, level)) -посмотреть Размеры ячеек S2 для выбора уровня ячеек.

Чтобы добавить к ответу Майкла, одна из проблем, которую я обнаружил, заключалась в том, что сетка S2, будучи регулярной, не соответствовала различной плотности на земле. Таким образом, вы, как правило, получаете гораздо меньшие разделы, чем хотите, просто потому, что вам нужно установить уровень S2, соответствующий наиболее плотной области. В случае Великобритании, где существуют готовые сетки разного масштаба, я создал смешанную сетку длиной 100 км и 20 км, см. Ниже. Меньшие площади охватывают большие города.

Другим вариантом было бы использовать шестнадцатеричную сетку, которую Carto сделал доступной для BiqQuery через jslibs.h3.

Существует множество других вариантов, таких как рекурсивное разделение пространства, аналогичное построению дерева KD, до тех пор, пока не будет гарантировано, что на одном сегменте поместится самый большой оставшийся входной раздел.

В зависимости от того, что вы пытаетесь сделать, возникает дополнительная проблема объединения кластеров, которые пересекают любое подразделение, используемое в предложении OVER. Для этого есть решения, такие как использование ST_Union и ST_Intersects и объединение соседних кластеров, но это выходит за рамки первоначального вопроса. В конечном счете, именно поэтому вы хотите сохранить разделы как можно большего размера, но это уменьшит объем работы, необходимой для рекомбинации кластеров, если вы хотите это сделать.