HIVE的一点高级用法

cdjxz

944

收藏 2018-12-07

1、只获取文本中的字母，汉字和数字，剔除标点和特殊符号

复制代码

2、获取字符串a首次在字符串b中出现的位置

复制代码

3、比较两个字符串相同的字符数量

复制代码

4、解析json组

复制代码

sid	price	sn	number	outr	deli	outs	act
3458764527033385083	33	1	200	0	0	0	["3458764719724101804"]
3458764702871191593	39	1	2000	0	0	0	["3458764730620903593"]
3458764712379678782	42	1	900	0	0	0	["3458764738128707753"]

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

全部回复

cdjxz

2018-12-7 16:45:55

select
  get_json_object(ss.list, '$.sid') as sid,
  get_json_object(ss.list, '$.price') as price,
  get_json_object(ss.list, '$.sn') as sn,
  get_json_object(ss.list, '$.number') as number,
  get_json_object(ss.list, '$.outr') as outr,
  get_json_object(ss.list, '$.deli') as deli,
  get_json_object(ss.list, '$.outs') as outs,
  get_json_object(ss.list, '$.act') as act
from
  (
select
   split(
      regexp_replace(
      regexp_extract(
         '[{"sid":"3458764527033385083","sn":1,"number":200,"price":33,"outr":0,"outs":0,"deli":0,"act":["3458764719724101804"]},{"sid":"3458764702871191593","sn":1,"number":2000,"price":39,"outr":0,"outs":0,"deli":0,"act":["3458764730620903593"]},{"sid":"3458764712379678782","sn":1,"number":900,"price":42,"outr":0,"outs":0,"deli":0,"act":["3458764738128707753"]}]',
         '^\\[(.+)\\]$',
         1
      ),
      '\\}\\,\\{',
      '\\}\\|\\|\\{'
      ),
      '\\|\\|'
   ) as str
) pp lateral view explode(pp.str) ss as list

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

cdjxz

2018-12-7 16:47:16

select
  get_json_object(ss.list, '$.sid') as sid,
  get_json_object(ss.list, '$.price') as price,
  get_json_object(ss.list, '$.sn') as sn,
  get_json_object(ss.list, '$.number') as number,
  get_json_object(ss.list, '$.outr') as outr,
  get_json_object(ss.list, '$.deli') as deli,
  get_json_object(ss.list, '$.outs') as outs,
  get_json_object(ss.list, '$.act') as act
from (select split(regexp_replace(regexp_extract(
'[{"sid":"3458764527033385083","sn":1,"number":200,"price":33,"outr":0,"outs":0,"deli":0,"act":["3458764719724101804"]},{"sid":"3458764702871191593","sn":1,"number":2000,"price":39,"outr":0,"outs":0,"deli":0,"act":["3458764730620903593"]},{"sid":"3458764712379678782","sn":1,"number":900,"price":42,"outr":0,"outs":0,"deli":0,"act":["3458764738128707753"]}]',
'^\\[(.+)\\]$',1),'\\}\\,\\{','\\}\\|\\|\\{'),'\\|\\|') as str
) pp lateral view explode(pp.str) ss as list

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

栏目导航

扫码加我 拉你入群

扫码加我 拉你入群

扫码加我 拉你入群

分享

扫码加好友，拉您进群

扫码加我拉你入群

扫码加我拉你入群

扫码加我拉你入群