Google BigQuery大表用ROW_NUMBER()内存溢出，求短唯一标识替代方案

你遇到的问题是用ROW_NUMBER() OVER(ORDER BY null)生成数字ID时，全局排序操作导致10亿行数据集内存溢出。以下是两种更高效的替代方案：

方案1：直接用哈希函数生成数字ID（无需排序，性能最优） #

如果不需要连续的整数ID，直接用BigQuery内置的高效哈希函数将字符串哈希转为64位整数，完全避免排序操作，内存占用极低。

示例代码：

SELECT 
    my_hash,
    FARM_FINGERPRINT(my_hash) AS id_numeric
FROM hash_table_raw
GROUP BY my_hash

• 优点：无排序开销，执行速度极快，适合超大规模数据集。
• 注意：理论上存在极小的哈希碰撞概率，但绝大多数业务场景下可以忽略；若需绝对无碰撞，可结合方案2或使用更长的哈希转换。

方案2：分组分段生成连续整数ID（避免全局排序） #

如果必须生成连续的整数ID，可通过哈希分组拆分排序任务，将全局排序转化为多个小分组内的局部排序，降低单节点内存压力。

示例代码：

WITH hashed_groups AS (
    SELECT 
        my_hash,
        -- 用哈希值高位拆分分组
        FARM_FINGERPRINT(my_hash) >> 32 AS group_id,
        -- 分组内生成局部ID
        ROW_NUMBER() OVER(PARTITION BY FARM_FINGERPRINT(my_hash) >> 32 ORDER BY my_hash) AS local_id
    FROM hash_table_raw
    GROUP BY my_hash
)
SELECT 
    my_hash,
    -- 组合成全局唯一连续ID
    (group_id << 32) + local_id AS global_id_numeric
FROM hashed_groups

• 原理：通过将哈希值的高位作为分组键，把10亿行数据集拆分成多个小批次，每个批次内的排序操作内存需求大幅降低，避免全局排序导致的内存溢出。

内容的提问来源于stack exchange，提问作者smaica