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