如何使用Athena SQL提取配置变更记录的字段及变更前后值

需求背景 #

我们有一张main_table，其中string字段存储了配置修改的记录文本，需要从中提取出修改的配置项、修改前内容和修改后内容。

表结构与示例数据 #

格式规则 #

• 配置项名称以冒号(:)结尾
• 变更内容位于两个分号(;)之间，每个配置项对应修改前和修改后两组内容，格式为配置项:- 修改前;- 修改后;

预期结果 #

实现方案 #

你之前用regexp_extract_all配合unnest的思路方向是对的，但没按完整配置项单元拆分，导致结果不符合预期。以下是适配Athena（基于Presto SQL）的完整实现步骤：

1. 按完整配置项拆分字符串 #

先通过正则匹配每个独立的配置项块（格式为配置项:- 修改前;- 修改后;），再用unnest展开每个配置项：

SELECT 
    id,
    date,
    regexp_replace(item, ';$', '') AS item -- 去掉末尾多余的分号
FROM main_table
CROSS JOIN UNNEST(REGEXP_EXTRACT_ALL(string, '([a-zA-Z_]+:\;- .*?;- .*?;)')) AS t(item)

这里的正则逻辑：

• [a-zA-Z_]+: 匹配以冒号结尾的配置项名称
• \;- .*? 匹配修改前内容（以;- 开头，非贪婪匹配到下一个;- ）
• ;- .*?; 匹配修改后内容（以;- 开头，非贪婪匹配到结尾分号）

2. 提取配置项及变更内容 #

对拆分后的每个配置项，进一步提取具体字段，同时处理空值场景：

SELECT
    id,
    date,
    -- 提取配置项名称：截取冒号前的内容
    REGEXP_EXTRACT(item, '^([a-zA-Z_]+):') AS changed_items,
    -- 提取修改前内容，空内容替换为null
    CASE 
        WHEN TRIM(REGEXP_EXTRACT(item, '\;- (.*?)\;- ')) = '' THEN 'null' 
        ELSE TRIM(REGEXP_EXTRACT(item, '\;- (.*?)\;- ')) 
    END AS before,
    -- 提取修改后内容，去除前后空格
    TRIM(REGEXP_EXTRACT(item, '\;- (.*?)$')) AS after
FROM (
    SELECT 
        id,
        date,
        regexp_replace(item, ';$', '') AS item
    FROM main_table
    CROSS JOIN UNNEST(REGEXP_EXTRACT_ALL(string, '([a-zA-Z_]+:\;- .*?;- .*?;)')) AS t(item)
)

最终完整SQL #

合并上述步骤，直接执行即可得到预期结果：

SELECT
    id,
    date,
    REGEXP_EXTRACT(item, '^([a-zA-Z_]+):') AS changed_items,
    CASE 
        WHEN TRIM(REGEXP_EXTRACT(item, '\;- (.*?)\;- ')) = '' THEN 'null' 
        ELSE TRIM(REGEXP_EXTRACT(item, '\;- (.*?)\;- ')) 
    END AS before,
    TRIM(REGEXP_EXTRACT(item, '\;- (.*?)$')) AS after
FROM (
    SELECT 
        id,
        date,
        regexp_replace(item, ';$', '') AS item
    FROM main_table
    CROSS JOIN UNNEST(REGEXP_EXTRACT_ALL(string, '([a-zA-Z_]+:\;- .*?;- .*?;)')) AS t(item)
)

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