Hive 的正则应用，用会了没？

• 2019 年 12 月 25 日
• 笔记

环境：

Hive: 2.7.7  Oracle SQL Developer  Cloudera JDBC Driver  

案例 – 1 ： regexp_replace(s,regex,replacement)

select regexp_replace(name,'ali','alibaba') as clientName  from default.employee ;      select regexp_replace(name,'[ali]','alibaba') as clientName  from default.employee ;  

结果：

将 name 等于 'ali' 替换成 'alibaba', 但不更改表级别的数据

image

image

注意：正则表达式一定是符合 Java 正则规范的，因为 Hive 是 Java 写的嘛

案例 – 2 ： regexp_extract(subject,regex_pattern,index)

select  regexp_extract(name,'[ali]([a-z])*',0)          ,regexp_extract(name,'[ali]([a-z])*',1)  from default.employee ;  

结果：

image

注意：index 的选择很重要。它是extract group 的位置

案例 – 3 ： where name RLike '.(a|l|i)'

select * from default.employee  where name RLike '.*(a|l|i)+'  

数据部署：

insert into default.employee (name,salary,subordinates,deductions,address)  select 'liton'      ,345      ,array('liton','acai','ayun') as sub      , map('liton',1,'acai',2,'ayun',7) as ded      ,named_struct('street',"zhejiang",  'city',"hangzhou",  'state',"hubin",  'zip',"201210")  from student  limit 1      insert into default.employee (name,salary,subordinates,deductions,address)  select 'tencent'      ,543      ,array('tencent','acai','ayun') as sub      , map('tencent',1,'acai',2,'ayun',7) as ded      ,named_struct('street',"zhejiang",  'city',"hangzhou",  'state',"hubin",  'zip',"201210")  from student  limit 1  

结果：

image