Hive练习题之炸裂函数+case when+ 模糊匹配

– 建表

create table if not exists test.tb_str(
id int,
str string
)
row format delimited fields terminated by ",";

– 导入

vi  /doit/tb_str.txt

1,hello
2,world
3,sun
4,door

load data local inpath "/doit/tb_str.txt" into table test.tb_str;

– 解法

with tmp as (
select 
id,
str,
case 
when str like '%e%' and str like '%o%' then "e,o"
when str like '%e%' then "e"
when str like '%o%' then "o"
end as key 
from test.tb_str)

select 
id,
str,
keys
from tmp
lateral view
explode(split(key,",")) k as keys