Hive中如何按指定模式将单列带可变标签的数据拆分到多列

实现思路 #

• 第一步：提取全表所有出现过的唯一标签，适配标签不固定的场景
• 第二步：基于提取到的标签，逐列写正则提取逻辑，支持多行取值匹配，缺失标签的列返回null

具体实现 #

假设存储待拆分数据的表名为t1，待拆分的字符串列名为content，数据格式为':9a:abcd efgh ijkl :12a: mnop qr :52b: stuv w :63a: xyz......'

1. 提取全量唯一标签 #

-- 提取所有出现过的标签，结果可存为临时表t_tags后续使用
select distinct tag
from t1
lateral view explode(regexp_extract_all(content, ':[a-zA-Z0-9]+:', 0)) tmp as tag

2. 编写拆分查询SQL #

基于第一步拿到的标签列表，每个标签对应生成一个提取列即可，正则中加入(?s)参数开启单行匹配模式，保证.可以匹配换行符，支持多行格式的标签值：

select
  -- 没有对应标签时返回null
  if(
    length(trim(regexp_extract(content, '(?s):9a:(.*?)(?=:[a-zA-Z0-9]+:|$)', 1))) = 0,
    null,
    trim(regexp_extract(content, '(?s):9a:(.*?)(?=:[a-zA-Z0-9]+:|$)', 1))
  ) as `column :9a:`,
  if(
    length(trim(regexp_extract(content, '(?s):12a:(.*?)(?=:[a-zA-Z0-9]+:|$)', 1))) = 0,
    null,
    trim(regexp_extract(content, '(?s):12a:(.*?)(?=:[a-zA-Z0-9]+:|$)', 1))
  ) as `column :12a:`,
  if(
    length(trim(regexp_extract(content, '(?s):52b:(.*?)(?=:[a-zA-Z0-9]+:|$)', 1))) = 0,
    null,
    trim(regexp_extract(content, '(?s):52b:(.*?)(?=:[a-zA-Z0-9]+:|$)', 1))
  ) as `column :52b:`,
  if(
    length(trim(regexp_extract(content, '(?s):63a:(.*?)(?=:[a-zA-Z0-9]+:|$)', 1))) = 0,
    null,
    trim(regexp_extract(content, '(?s):63a:(.*?)(?=:[a-zA-Z0-9]+:|$)', 1))
  ) as `column :63a:`
from t1

输出效果 #

和需求预期一致，无对应标签的列返回null，多行标签值可以完整提取。

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