hive中使用正则及炸裂函数解析jsonarray的实现方法附正则表达式的使用举例

大地你王哥

已于 2024-01-17 17:40:49 修改

阅读量514

点赞数

分类专栏： hive 文章标签： hive 数据库

于 2020-05-28 17:37:08 首次发布

本文链接：https://blog.csdn.net/DaDiNiWangGe/article/details/106409345

版权

hive 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

202401更新jasonarray的解析
记一次解析jsonarray的问题，需求是从jsonarray中解析出每一个的fineScore。
原始数据：[{“infoId”:null,“uid”:69815086,“extendMap”:{“resumeId”:“13343643600”,“fineScore”:55.0,“activetime”:1705283338000}},{“infoId”:null,“uid”:69815086,“extendMap”:{“resumeId”:“1334364360323”,“fineScore”:55.0,“activetime”:1705283338000}}]
原始数据在这里插入图片描述

最终实现代码（实际开发中使用lateral view explode替换即可）：
select
regexp_extract(a1,'\"fineScore\"\:([^(\,)]+)',1)
from(
  select
  explode(split(regexp_replace(regexp_replace('[{"infoId":null,"uid":69815086,"extendMap":{"resumeId":"13343643600","fineScore":55.0,"activetime":1705283338000}},{"infoId":null,"uid":69815086,"extendMap":{"resumeId":"13343643600","fineScore":55.0,"activetime":1705283338000}}]', '\\[|\\]' , ''), '\\}\\,\\{' , '\\}\\&\\{'), '&')) a1
)t1
解析：先使用正则替换掉“[]”为空格，替换两个json对象间的逗号为‘&’,然后做切割
切割后一列变成了多行，对每一行数据使用正则匹配的方式解析出对应的k-v，([^(\,)]+)部分代表了解析截止到数据后的逗号位置，可以测试下不加这个条件时候的结果是解析出来以fineScore":开头及之后的全部数据。
;

在这里插入图片描述


错误示范，直接使用分号分隔
SELECT
  regexp_replace(
  regexp_replace('[{"infoId":null,"uid":69815086,"extendMap":{"resumeId":"13343643600","fineScore":55.0,"activetime":1705283338000}},{"infoId":null,"uid":69815086,"extendMap":{"resumeId":"1334364360323","fineScore":55.0,"activetime":1705283338000}}]', '\\[|\\]' , ''),
   '\\}\\,\\{' ,'\\}\\;\\{')
  ;
  
  遇到分号问题的正确写法
SELECT
  regexp_replace(
  regexp_replace('[{"infoId":null,"uid":69815086,"extendMap":{"resumeId":"13343643600","fineScore":55.0,"activetime":1705283338000}},{"infoId":null,"uid":69815086,"extendMap":{"resumeId":"1334364360","fineScore":55.0,"activetime":1705283338000}}]', '\\[|\\]' , ''),
   '\\}\\,\\{' ,'\\}\073\\{')
  ;
  差异点：分号是hive中的默认结束符，需要使用ascii码表的\073替换，一般不建议使用，可以用其他代码中不会出现的符号，如“&”

以下为历史的更新hive中正则匹配的使用

select userid as cuserid,
                 regexp_extract(trackurl,'\"slot=([^\"]+)\"',1) as tjfrom1,
                 regexp_extract(trackurl,'\"tjfrom=([^\"]+)\"',1) as tjfrom2,
                 regexp_extract(trackurl,'\"infoid=([^\"]+)\"',1) as infoid,
          from xxx.app_action
          where dt = '20200528'
         limit 100;

解析：
语法: regexp_extract(string subject, string pattern, int index)
首先匹配""slot=“匹配其后跟着的以"开头最后再匹配一个”,index=1去第一个括号内的内容即slot=后边的value值返回string,命名为tjfrom1
返回值: string
说明：将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。
第一参数：要处理的字段
第二参数: 需要匹配的正则表达式
第三个参数:
0是显示与之匹配的整个字符串
1 是显示第一个括号里面的（默认为1）
2 是显示第二个括号里面的字段…（注意index数量不能大于括号数量）
注意：在有些情况下要使用转义字符双斜杠‘\’

示例：
select regexp_extract(‘x=a3&x=18abc&x=2&y=3&x=4’,‘x=([0-9]+)([a-z]+)’,2) from default.dual;
结果：abc，此处index如果写3则会报错，因为多于括号的数量了。
执行解析：首先匹配"x=“,然后比配"0-9”,“+“代表匹配前面的子表达式一次或多次,最后匹配"a-z” ,”+"代表匹配前面的子表达式一次或多次,输出第二个括号的内容即数字后面跟随的字母.

更新
1.从一种url中取出ttfrom的信息，
1.1第1种写法如下（不够精准）：
select regexp_extract(‘https://xxx/info/4215?slot_id=1002103&adtype=28&ttfrom=abc_detail_xx__1650786453357685455601__6__jz__adtypes__3__null__{CID}__eyJyIjp7ImluZm9pZCI6IjQyNTY5MjA4ODEzMzE1Iiwic2xvdCI6ImxtX2RldGFpbF9qeiIsInR5cGUiOiIxNTEiLCJzaWQiOiIxNjUwNzg2NjEyMTA0NTMzNTc2ODU0NTU2MDEifSwidCI6MSwidiI6MSwidyI6eyJsbV9zbG90IjoibG1fZGV0YWlsIn19&spm=u-2e71fxbnc988m2&utm_source=lk&entyType=0&psid=165070453357685455601&pos=3&ClickID=8&slot=ll_detail&lego_tid=1e2a1297-f89f-4c0b-9ce7-28411ead7882&entinfo=42569208813315_0&cid=0b7bb0b0-8590-4881-94b2-3c33787e5bef&extParam=%7B%22adxcid%22%3A%220b7bb0b0-8590-4881-94b2-3c33787e5bef%22%7D&PGTID=0d4035ea-0011-41c2-7a2a-80304e0016d1&-15=20&ad_id=1285496918671118337’,‘&ttfrom=([^&]*)+’,1)

注：[]表示排除xxx信息，其中的^&在本案例中也可以替换成任意ttfrom中不会出现的字符，‘+’表示匹配前面的表达式一次或多次 ‘*’表示匹配前面的表达式0次或多次，最后一个参数1是表示取第一个小括号内的内容作为返回值。这个写法正则表达式中’[]'部分没有起到作用。

1.2第2种写法如下（符合正则规则定义的表达式）：
select regexp_extract(url,‘&ttfrom=(.*)+’,1) ==>表示匹配以‘&ttfrom=’开头，返回其后的任意字符。

2.另一种url中解析ttfrom或者slot的信息
select regexp_extract(‘https://abc.xxx.com/api/detail/siji/3745?abtest=B&localname=dg&commondata={“slot”:“app_abc_gxx__114429210464431842192548__227946100252540928__pt__5__13__null__null__eyJyIjp7InNwbSI6Ii0iLCJpbmZvaWQiOiIzNzQzMjMzODQ1NTMwNSIsInNsb3QiOiIyNSIsInR5cGUiOiIxNDkiLCJzaWQiOiIxMQ0MzE4NDIxOTI1NDgiLCJ1dG0iOiItIiwiY2lkIjoiMTA3MSJ9LCJ0IjoxLCJ2IjoxfQ==”,“title”:“EED9CD53936F57D83A589021796D624D”,“finalCp”:“finalCp=0000017500000000000000000000_114429330210464431842192548”,“ttfrom”:“app_abc_gz__114429330210461842192548__227946102540928__pt__5__13__null__null__eyJyIjp7InNwbSI6Ii0iLCJpbmZvaWQiOiIzDQ1NTMwNSIsInNsb3QiOiIyNSIsInR5cGUiOiIxNDkiLCJzaWQiOiIxMTQ0MjkzMz0MzE4NDIxOTI1NDgiLCJ1dG0iOiItIiwiY2lkIjoiMTA3MSJ9LCJ0IjoxLCJ2IjoxfQ==”,“position”:14,“posType”:3,“isNew”:“B”}&v=1&ttfrom=app_xx_gz__114429330210464431842192548__227946100252540928__pt__5__13__null__null__eyJyCJpbmZvaWQiOiIzNzQzMjMzODQ1NTMwNSIsIR5cGUiOiIxNDkiLCJzaWQiOiIxMTQ0MjkzMzAyMTA0NjQ0MzE4NDIxOTI1NDgiLCJ1dG0iOiItIiwiY2lkIjoiMTA3MSJ9LCJ0IjoxLCJ2IjoxfQ==&format=xml&version=10.7.2&sidDict={“PGTID”:“”,“GTID”:“114429330210464431842192548”}&psid=114429330210464431842192548’,‘“(slot|ttfrom)”:“([^”]+)"’,2)