优化Hive中性能低下的REGEXP_EXTRACT实现方案问询

问题背景 #

现有Hive表结构如下：

id     |my_list                               |
-------+--------------------------------------+
52     |D.07.01.01=1;D.07.01.02=2;D.07.01.03=1|

需要将my_list中的键值对转为独立列，目标表结构：

id     |D.07.01.01|D.07.01.02|D.07.01.03|
-------+--------------------------------+
52     |LOW       |MODERATE  |LOW       |

原有实现通过多次REGEXP_EXTRACT和rlike判断完成转换，但在30个字段的场景下，50k行数据耗时2小时，性能极差，现需优化方案并验证正则格式的合理性。

原有方案性能差的原因 #

1. 重复字符串扫描：每个字段都执行一次rlike匹配和一次REGEXP_EXTRACT提取，30个字段等于对每行数据做60次全字符串扫描，开销呈倍数放大。
2. 冗余正则匹配：正则中的.*?是非贪婪匹配，会让正则引擎逐个字符遍历直到找到目标键，额外增加匹配耗时。

正则格式的合理性 #

原有正则能实现功能，但冗余且低效：.*?完全没必要，我们只需要精准匹配目标键D.07.01.xx后面的数字，不需要匹配前面的任意字符。

优化方案 #

最优方案：行转列拆分法 #

先将my_list拆分为键值对行，再聚合为列，仅需一次字符串拆分操作，性能提升显著：

WITH split_data AS (
    SELECT 
        id,
        split(kv_pair, '=')[0] AS key_col,
        -- 直接转换数字为对应文本
        CASE split(kv_pair, '=')[1]
            WHEN '1' THEN 'LOW'
            WHEN '2' THEN 'MODERATE'
        END AS value_col
    FROM your_table
    -- 将分号分隔的键值对拆分为多行
    LATERAL VIEW explode(split(my_list, ';')) kv AS kv_pair
)
SELECT 
    id,
    -- 聚合每个键对应的值为列
    MAX(CASE WHEN key_col = 'D.07.01.01' THEN value_col END) AS `D.07.01.01`,
    MAX(CASE WHEN key_col = 'D.07.01.02' THEN value_col END) AS `D.07.01.02`,
    MAX(CASE WHEN key_col = 'D.07.01.03' THEN value_col END) AS `D.07.01.03`,
    -- 依次添加剩余27个字段
FROM split_data
GROUP BY id;

次优方案：简化正则与移除冗余判断 #

如果不想改变原有列转行的结构，可以通过以下方式优化：

1. 移除多余的rlike判断：REGEXP_EXTRACT匹配不到时返回空，直接用CASE处理即可。
2. 简化正则，去掉.*?，直接匹配目标键：

SELECT 
    id,
    CASE REGEXP_EXTRACT(my_list, 'D\\.07\\.01\\.01=(\\d)')
        WHEN '1' THEN 'LOW'
        WHEN '2' THEN 'MODERATE'
    END AS `D.07.01.01`,
    CASE REGEXP_EXTRACT(my_list, 'D\\.07\\.01\\.02=(\\d)')
        WHEN '1' THEN 'LOW'
        WHEN '2' THEN 'MODERATE'
    END AS `D.07.01.02`,
    -- 其他字段同理
FROM your_table;

替代方案：用split代替正则 #

字符串拆分函数split比正则表达式开销更低，可进一步提升性能：

SELECT 
    id,
    CASE split(split(my_list, 'D\\.07\\.01\\.01=')[1], ';')[0]
        WHEN '1' THEN 'LOW'
        WHEN '2' THEN 'MODERATE'
    END AS `D.07.01.01`,
    CASE split(split(my_list, 'D\\.07\\.01\\.02=')[1], ';')[0]
        WHEN '1' THEN 'LOW'
        WHEN '2' THEN 'MODERATE'
    END AS `D.07.01.02`,
    -- 其他字段同理
FROM your_table;

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