Snowflake 760亿行查询优化：OBJECT_CONSTRUCT性能瓶颈解决

问题背景 #

处理760亿行数据时，使用4XL虚拟仓库执行原查询超时，核心瓶颈在于GROUP BY阶段的OBJECT_CONSTRUCT和多次COUNT(DISTINCT)调用——每个字段单独计算唯一值会导致大量重复扫描和计算，尤其是在超大规模数据集上。需求是生成仅保留分组内所有行值唯一的字段的对象。

高效实现方案 #

方案1：先展开JSON字段，再批量统计唯一值

将JSON对象的字段先展开为独立列，一次性统计所有字段的唯一值数量和对应值，最后再构建目标对象。这种方式减少了重复计算，让Snowflake的查询优化器能更好地并行处理：

WITH address_unpacked AS (
    SELECT
        id,
        -- 展开JSON字段为独立列
        org_Adress:City AS city_val,
        org_Adress:Country AS country_val,
        org_Adress:County AS county_val,
        org_Adress:Line1 AS line1_val,
        org_Adress:PostalCode AS postal_code_val,
        org_Adress:Region AS region_val,
        -- 保留原查询需要的其他列
        address,
        columns....,
        OBJECT_CONSTRUCT('OrAdress', org_Adress, 'DstAddress', dst_address) AS detail_obj
    FROM TABLE_A
),
grouped_stats AS (
    SELECT
        id,
        address,
        columns....,
        -- 聚合details_array（提前聚合单个对象，减少GROUP BY后的计算）
        ARRAY_AGG(DISTINCT detail_obj) AS details_array,
        -- 统计每个字段的唯一值数量和对应值
        COUNT(DISTINCT city_val) AS city_distinct_count,
        MAX(city_val) AS city_max,
        COUNT(DISTINCT country_val) AS country_distinct_count,
        MAX(country_val) AS country_max,
        COUNT(DISTINCT county_val) AS county_distinct_count,
        MAX(county_val) AS county_max,
        COUNT(DISTINCT line1_val) AS line1_distinct_count,
        MAX(line1_val) AS line1_max,
        COUNT(DISTINCT postal_code_val) AS postal_code_distinct_count,
        MAX(postal_code_val) AS postal_code_max,
        COUNT(DISTINCT region_val) AS region_distinct_count,
        MAX(region_val) AS region_max
    FROM address_unpacked
    GROUP BY ALL
)
SELECT
    id,
    address,
    columns....,
    details_array,
    -- 仅保留唯一值字段的对象，自动过滤NULL值
    OBJECT_REMOVE_NULL(OBJECT_CONSTRUCT(
        'City', CASE WHEN city_distinct_count = 1 THEN city_max END,
        'Country', CASE WHEN country_distinct_count = 1 THEN country_max END,
        'County', CASE WHEN county_distinct_count = 1 THEN county_max END,
        'Line1', CASE WHEN line1_distinct_count = 1 THEN line1_max END,
        'PostalCode', CASE WHEN postal_code_distinct_count = 1 THEN postal_code_max END,
        'Region', CASE WHEN region_distinct_count = 1 THEN region_max END
    )) AS single_address_object
FROM grouped_stats;

方案2：使用AGGREGATE函数简化唯一值判断

利用Snowflake的AGGREGATE函数，在分组内对每个字段的所有值进行聚合，判断是否唯一，减少多次COUNT(DISTINCT)的开销：

WITH grouped_data AS (
    SELECT
        id,
        address,
        columns....,
        ARRAY_AGG(DISTINCT OBJECT_CONSTRUCT('OrAdress', org_Adress, 'DstAddress', dst_address)) AS details_array,
        -- 对每个字段的数组进行聚合，判断是否所有元素相同
        AGGREGATE(ARRAY_AGG(org_Adress:City), NULL::STRING, 
            (acc, val) => IF(acc IS NULL OR acc = val, val, NULL)) AS city_unique_val,
        AGGREGATE(ARRAY_AGG(org_Adress:Country), NULL::STRING, 
            (acc, val) => IF(acc IS NULL OR acc = val, val, NULL)) AS country_unique_val,
        AGGREGATE(ARRAY_AGG(org_Adress:County), NULL::STRING, 
            (acc, val) => IF(acc IS NULL OR acc = val, val, NULL)) AS county_unique_val,
        AGGREGATE(ARRAY_AGG(org_Adress:Line1), NULL::STRING, 
            (acc, val) => IF(acc IS NULL OR acc = val, val, NULL)) AS line1_unique_val,
        AGGREGATE(ARRAY_AGG(org_Adress:PostalCode), NULL::STRING, 
            (acc, val) => IF(acc IS NULL OR acc = val, val, NULL)) AS postal_code_unique_val,
        AGGREGATE(ARRAY_AGG(org_Adress:Region), NULL::STRING, 
            (acc, val) => IF(acc IS NULL OR acc = val, val, NULL)) AS region_unique_val
    FROM TABLE_A
    GROUP BY ALL
)
SELECT
    id,
    address,
    columns....,
    details_array,
    OBJECT_REMOVE_NULL(OBJECT_CONSTRUCT(
        'City', city_unique_val,
        'Country', country_unique_val,
        'County', county_unique_val,
        'Line1', line1_unique_val,
        'PostalCode', postal_code_unique_val,
        'Region', region_unique_val
    )) AS single_address_object
FROM grouped_data;

关键优化点 #

• 减少重复扫描：方案1提前展开JSON字段，避免在GROUP BY阶段重复解析JSON；方案2用AGGREGATE一次遍历字段值，替代多次COUNT(DISTINCT)的重复计算。
• 自动过滤NULL：使用OBJECT_REMOVE_NULL直接移除值为NULL的字段，无需额外判断，简化对象构建逻辑。
• 提前聚合细节数组：将details_array的聚合提前到分组阶段，减少后续计算的内存开销。

额外性能建议 #

• 确保TABLE_A的id字段有合适的聚类键（Clustering Key），提升GROUP BY的分区扫描效率。
• 考虑启用结果缓存，如果查询重复执行，可直接复用缓存结果。
• 对于超大规模数据集，可尝试将查询拆分为分批处理，利用Snowflake的微分区特性并行处理。

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