hive 正则表达替换函数 regexp_replace

1、语法：regexp_replace(subject,pattern,str)

注：subject,pattern,str都为字符串，subject为被替换的字符串，pattern为正则表达式，str需要替换的字符串。

2、表描述：表explode_test，表头（area,good_id,sale_info）

a:shangdong,b:beijing,c:shanghai 1,2,3 [{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},{"source":"jdmart","monthSales":7900,"userCount":2900,"score":"5.9"},{"source":"yam","monthSales":54900,"userCount":12900,"score":"4.9"}]
a:tianjing,b:beijing,c:shanghai 3,4,5 [{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},{"source":"jdmart","monthSales":7900,"userCount":2900,"score":"5.9"},{"source":"yam","monthSales":54900,"userCount":12900,"score":"4.9"}]

3、去掉sale_info的【】

select regexp_replace(sale_info, '\[ | \]' , '') —— “|”是或的意思

from explode_test;

4、切分取出数据

select explode(split(regexp_replace(sale_info, '\[\{|\}\]' ), '\},\{' ))

from explode_test;

5、变成结构化表

select
split(split(a.col,',')[0],':')[1] as source,
split(split(a.col,',')[1],':')[1] as monthSales,
split(split(a.col,',')[2],':')[1] as userCount,
split(split(a.col,',')[3],':')[1] as score
from
(select explode(split(regexp_replace(sale_info,'\\[\\{|\\}\\]',''),'\\},\\{')) as col from explode_test) a;

6、get_json_object实现

将数据变成字典形式，然后用get_json_object提取数据。

select

get_json_object ( a.col, '$.source' ) as source,

get_json_object (a.col, '$.monthSales' ) as monthSales,

get_json_object (a.col, '$.userCount' ) as userCount,

get_json_object (a.col, '$.score' ) as score

from

(select

explot(split(regexp_replace(regexp_replace(sale_info, '\[|\]',''),, '\}\,\{', '\}\;\{'), '\;')) as col

from explode_test) a;