google-bigquery - 在bigquery中使用DBSCAN进行聚类

Question

我有一个 Bigquery 表，其中只有一列名为“point”的列。它包含我想要使用 BigQuery 中的 ST_CLUSTERDBSCAN 函数进行聚类的位置坐标。

我使用以下查询:

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

我收到此错误:

Resources exceeded during query execution: The query could not be executed in the allotted memory. Peak usage: 128% of limit. Top memory consumer(s): analytic OVER() clauses: 97% other/unattributed: 3%

据我了解，这是因为查询是内存密集型的。鉴于我的表包含数百万行，有什么方法可以使用集群数据吗？

Answer 1

BigQuery 中的大多数分析函数目前在单个分片(机器)上运行一个分区，因此分区大小在内存中限制为大约 1GB 数据大小。在您的查询中，OVER ()意味着没有分区 - 所有数据都在单个分区中运行。

解决方案通常是以某种大粒度对数据进行分区。例如。如果数据具有某种空间层次结构，您可以按此列进行分区 - 例如做OVER(PARTITION BY state) 。当然，这意味着不会出现跨州聚类，因此结果并不完全相同，但如果存在自然聚类，这通常是合理的。

如果这种内在层次结构不可用，另一种选择是按短 geohash 进行分区(字母很少 - 只需要避免资源超出错误所需的数量)，例如 OVER(PARTITION BY st_geohash(point, 2)) 。一个不错的选择是 S2_CellIDFromPoint(ST_Centroid(geo, level)) ，参见S2 cell sizes用于选择细胞水平。