Spark引擎下Hive SQL中前缀匹配型array_intersect优化问询

问题背景 #

当前有两种场景需要实现基于字符串前缀（第一个-前的内容）的数组过滤，逻辑类似array_intersect但仅匹配前缀：

1. 场景1：buckets为array<string>类型列，当前实现语句：

transform(
    a.buckets, -- buckets是字符串数组
    x -> if(
        array_contains(c.names, split(x, '-')[0]), -- c是CTE，names是字符串数组/集合
        x,
        null
    )
) AS buckets

2. 场景2：bucket为单个字符串列（元素用逗号分隔），当前实现语句：

transform(
    split(bucket, ','), -- 将单个字符串按逗号拆分为数组
    x -> if(
        array_contains(c.names, split(x, '-')[0]), -- c是CTE，names是字符串数组/集合
        x,
        null
    )
) AS buckets

优化方案 #

1. 替换split为substring_index提升前缀提取效率 #

split(x, '-')[0]会生成临时数组，改用substring_index(x, '-', 1)可直接提取第一个-前的前缀，避免数组创建开销，性能更优：

• 场景1优化后：

transform(
    a.buckets,
    x -> if(
        array_contains(c.names, substring_index(x, '-', 1)),
        x,
        null
    )
) AS buckets

• 场景2优化后：

transform(
    split(bucket, ','),
    x -> if(
        array_contains(c.names, substring_index(x, '-', 1)),
        x,
        null
    )
) AS buckets

2. 将c.names转为集合（Set）减少匹配时间 #

array_contains是线性扫描，数据量大时效率低。先将c.names转为set<string>类型，利用集合O(1)的查找特性：
先修改CTE c：

WITH c AS (
    SELECT collect_set(original_name) AS names_set -- original_name为原名字段
    FROM your_source_table
)

再结合前缀提取做过滤：

-- 场景1结合集合的优化
transform(
    a.buckets,
    x -> if(
        c.names_set contains substring_index(x, '-', 1),
        x,
        null
    )
) AS buckets

注：若Spark版本不支持直接用contains，可改用array_contains(cast(c.names_set as array<string>), substring_index(x, '-', 1))。

3. 用filter替代transform+null简化逻辑 #

当前transform会生成null值，实际需求是过滤不匹配元素，改用filter可直接返回符合条件的元素，逻辑更简洁且减少后续null处理开销：

• 场景1最终优化版：

filter(
    a.buckets,
    x -> array_contains(c.names_set, substring_index(x, '-', 1))
) AS buckets

• 场景2最终优化版：

filter(
    split(bucket, ','),
    x -> array_contains(c.names_set, substring_index(x, '-', 1))
) AS buckets

4. 提前拆分字符串列（针对场景2） #

如果场景2的bucket列频繁被拆分，可在ETL阶段提前将其拆分为array<string>类型存储，避免每次查询重复执行split操作，减少计算开销。

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