具有文件数据集唯一标识符的sql数组聚类

gg0vcinb  于 2021-05-18  发布在  Spark
关注(0)|答案(1)|浏览(421)

我在s3中有一个带有大int数组列的数据集,我想根据数组值高效地过滤行。我知道我们可以在sql表中使用gin索引,但需要解决方案来处理s3数据集。我计划对数组中的每个元素组合使用集群id(因为它们的基数不是很大)。最大2500),然后将其存储为新列,以后可以对其应用筛选器。例子,

Table A
+------+------+-----------+
| Col1 | Col2 |   Col3    |
+------+------+-----------+
|    1 |  101 | [123,234] |
|    2 |  102 | [123]     |
|    3 |  103 | [234,345] |
+------+------+-----------+

我正在尝试添加新的列,比如,

Table B (column Col3 will be removed from actual schema)
+------+------+-----------+-----------+
| Col1 | Col2 |   Col3    | Cid       |
+------+------+-----------+-----------+
|    1 |  101 | [123,234] |    1      |
|    2 |  102 | [123]     |    2      |
|    3 |  103 | [234,345] |    3      |
+------+------+-----------+-----------+

还有另外一张表,是col3和cid的Map,

Table C
+-----------+-----+
|   Col3    | Cid |
+-----------+-----+
| [123,234] |   1 |
| [123]     |   2 |
| [234,345] |   3 |
+-----------+-----+

如果创建了新的组合,表c将被添加一个新条目;如果添加或删除了任何数组元素,表b将被更新。目标是能够根据数组列中的值有效地从表a中筛选出记录。像这样的查询 123 = Any(Col3) 可以作为 Cid = 2 或者像[123345]=any(col3)这样的查询可以作为 Cid in (2,3) .
有没有更好的办法来解决这个问题?我也在考虑在运行时创建所需的组合,以限制组合的数量。创建最小组合是个好主意吗?

ve7v8dk2

ve7v8dk21#

在postgres中,您可以创建表并使用 join 要计算值:

create table array_dim as 
    select col3 as arr, row_number() over (order by min(col1)) as array_id
    from t
    group by col3;

然后可以添加新列:

select a.*, ad.array_id
from a join 
     array_dim ad
     on a.col3 = ad.arr

相关问题