hive 正则表达式

KANGBboy

1761人浏览 · 2024-08-08 21:45:08

KANGBboy · 2024-08-08 21:45:08 发布

regexp_extract

字符串正则表达式解析函数，返回值为字符串string类型。
regexp_extract可用于一般截取函数substr无法使用的一些复杂场景。
用法：regexp_extract(str, regexp,[idx])
str：被解析的字符串或字段名
regexp：正则表达式
idx：是返回结果索引，取表达式的哪一()部分<子表达式>，默认值为1
0表示把整个正则表达式对应的结果全部返回；
1表示返回正则表达式中第一个() 对应的结果，以此类推
注意：idx的数字不能大于表达式中()的个数，否则报错
应用场景：

解析单个字符串；
字段值粒度太细，需要粗粒度分组聚合/比较时；
示例5：version版本字段值：[1级].[2级].[3级]（1,2,3级取值范围：0~99）
示例6：字段值不规范，提取有效部分

regexp_replace

regexp_replace(原始字符串, 正则模式, 替换值)
在一个字符串中，用指定的替换值替换所有匹配正则表达式的子字符串。

regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)
INITIAL_STRING：要进行替换操作的原始字符串
PATTERN：正则表达式匹配模式（Hive基于Java正则语法）
REPLACEMENT：替换后的字符串

应用场景：
简单文本替换(非正则特殊字符)替换固定文本，和普通replace函数效果一致
正则匹配替换（核心场景）利用正则的通配符、字符类等特性实现灵活替换
处理NULL值,如果原始字符串为NULL，函数返回NULL，需要结合nvl处理
注意事项:
转义字符：Hive中正则的转义需要双重反斜杠（\\），比如匹配小数点要用\\.（直接用.会匹配任意字符）。
贪婪匹配：正则默认贪婪匹配（匹配最长符合条件的字符串），如需非贪婪需加?，比如.*?。

贪婪匹配（默认）：正则表达式会尽可能匹配最长的符合条件的字符串。
非贪婪匹配（加?）：正则表达式会尽可能匹配最短的符合条件的字符串。
提取/替换字符串中第一个目标内容时，必须用非贪婪匹配（.*?），否则会匹配到最后一个目标内容。
SELECT regexp_replace('name:张三,age:25,gender:男', '.*,', 'user_info:'),
regexp_replace('name:张三,age:25,gender:男', '.*?,', 'user_info:'),
regexp_replace('name:张三,age:25,gender:男', ',', 'user_info:');
结果
_c0	                 _c1                           _c2
user_info:gender:男	 user_info:user_info:gender:男 name:张三user_info:age:25user_info:gender:男

大小写敏感：默认大小写敏感，如需忽略大小写，可在正则开头加(?i):

SELECT regexp_replace('Hello HIVE', '(?i)hive', 'hadoop'); --输出：Hello hadoop

code	结果
1.select regexp_extract('hitdecisiondlist','(i)(.*?)(e)',0)	itde
2.select regexp_extract('hitdecisiondlist','(i)(.*?)(e)',1)	i
3.select regexp_extract('hitdecisiondlist','(i)(.*?)(e)',2) 	td
4.select regexp_extract('x=a3&x=18abc&x=2&y=3&x=4','x=([0-9]+)([a-z]+)',0)	 x=18abc
5.select cast(regexp_extract(version,'^([0-9]+)',0) as BIGINT) <6	取1级版本小于6的
6.select regexp_extract(app_version,'^([0-9]+).([0-9]+).([0-9]+)',0)	清洗数据

select regexp_extract('hitdecisiondlist','(i)(.*?)(e)',0)	
,regexp_extract('hitdecisiondlist','(i)(.*?)(e)',1)	
,regexp_extract('hitdecisiondlist','(i)(.*?)(e)',2) 	
,regexp_extract('x=a3&x=18abc&x=2&y=3&x=4','x=([0-9]+)([a-z]+)',0);
结果
_c0	  _c1 _c2 _c3
itde  i	 td	  x=18abc

截取ip

with tablea as (select 'OH;IIP=2408:8474:7200:3a2a:7556:9b74:69c1:637e;HN=' as station
union all select 'OH;IIP=39.144.45.148;HN=' as station) 
select station
,regexp_extract(station, 'IIP=([^;]+);',1) as station1 --^;不是分号的所有字符
,(regexp_extract(station, 'IIP=([0-9.]+);', 1)) as station2
,split(station, 'IIP=([^;]+);') as station3
,(case when regexp_extract(station, 'IIP=([^;]+)',1) rlike '[0-9]+\\.[0-9]+\\.[0-9]+\\.[0-9]+$' then 'ipv4'  --^([0-9]{1,3}\\.){3}[0-9]{1,3}$ 对应解释(0-9 一到三位 点) 三次 点 0-9 结尾 
       when regexp_extract(station, 'IIP=([^;]+)',1) rlike '^[0-9a-fA-F:]+$' then 'IPv6'
       else '未知' end) as station4
from tablea


station	                                            station1	                                station2	    station3	    station4
OH;IIP=2408:8474:7200:3a2a:7556:9b74:69c1:637e;HN=  2408:8474:7200:3a2a:7556:9b74:69c1:637e                     ["OH;","HN="]	IPv6
OH;IIP=39.144.45.148;HN=                            39.144.45.148	                            39.144.45.148   ["OH;","HN="]	ipv4

–去除小数点后面的0

select regexp_replace(regexp_replace(cast(106.0000 as string),"0*$",''),"\\.$",'')
,regexp_replace(regexp_replace(cast(100.3100 as string),"0*$",''),"\\.$",'')

–106 100.31

将xxxx年xx月xx日转为 xxxxxxxx

select '2024年5月6日公告: 根据公司《可转债募集说明书》的规定:在本次发行的可转换公司债券期满后5个交易日内,公司将以本次可转债票面面值上浮6%(含最后一期利息)的价格向投资者赎回全部未转股的可转债。即“三力转债”到期合计兑付价格为106元/张(含最后一期利息,含税)。 2024年6月1日公告: 1、“三力转债”到期日和兑付登记日:2024年6月7日(星期五) 2、“三力转债”到期兑付价格:106元/张(含最后一期利息,含税) 3、“三力转债”到期兑付资金发放日:2024年6月11日(星期二) 4、“三力转债”摘牌日:2024年6月11日(星期二) 5、“三力转债”最后交易日:2024年6月4日(星期二) 6、“三力转债”最后转股日:2024年6月7日(星期五)	
'
,regexp_extract('2024年5月6日公告: 根据公司《可转债募集说明书》的规定:在本次发行的可转换公司债券期满后5个交易日内,公司将以本次可转债票面面值上浮6%(含最后一期利息)的价格向投资者赎回全部未转股的可转债。即“三力转债”到期合计兑付价格为106元/张(含最后一期利息,含税)。 2024年6月1日公告: 1、“三力转债”到期日和兑付登记日:2024年6月7日(星期五) 2、“三力转债”到期兑付价格:106元/张(含最后一期利息,含税) 3、“三力转债”到期兑付资金发放日:2024年6月11日(星期二) 4、“三力转债”摘牌日:2024年6月11日(星期二) 5、“三力转债”最后交易日:2024年6月4日(星期二) 6、“三力转债”最后转股日:2024年6月7日(星期五)	
', '交易日:+(\\d+年\\d+月\\d+日)')
,unix_timestamp(regexp_extract('2024年5月6日公告: 根据公司《可转债募集说明书》的规定:在本次发行的可转换公司债券期满后5个交易日内,公司将以本次可转债票面面值上浮6%(含最后一期利息)的价格向投资者赎回全部未转股的可转债。即“三力转债”到期合计兑付价格为106元/张(含最后一期利息,含税)。 2024年6月1日公告: 1、“三力转债”到期日和兑付登记日:2024年6月7日(星期五) 2、“三力转债”到期兑付价格:106元/张(含最后一期利息,含税) 3、“三力转债”到期兑付资金发放日:2024年6月11日(星期二) 4、“三力转债”摘牌日:2024年6月11日(星期二) 5、“三力转债”最后交易日:2024年6月4日(星期二) 6、“三力转债”最后转股日:2024年6月7日(星期五)	
', '交易日:+(\\d+年\\d+月\\d+日)'), 'yyyy年MM月dd日')
,from_unixtime(unix_timestamp(regexp_extract('2024年5月6日公告: 根据公司《可转债募集说明书》的规定:在本次发行的可转换公司债券期满后5个交易日内,公司将以本次可转债票面面值上浮6%(含最后一期利息)的价格向投资者赎回全部未转股的可转债。即“三力转债”到期合计兑付价格为106元/张(含最后一期利息,含税)。 2024年6月1日公告: 1、“三力转债”到期日和兑付登记日:2024年6月7日(星期五) 2、“三力转债”到期兑付价格:106元/张(含最后一期利息,含税) 3、“三力转债”到期兑付资金发放日:2024年6月11日(星期二) 4、“三力转债”摘牌日:2024年6月11日(星期二) 5、“三力转债”最后交易日:2024年6月4日(星期二) 6、“三力转债”最后转股日:2024年6月7日(星期五)	
', '交易日:+(\\d+年\\d+月\\d+日)'), 'yyyy年MM月dd日'), 'yyyy年MM月dd日') 
,from_unixtime(unix_timestamp(regexp_extract('2024年5月6日公告: 根据公司《可转债募集说明书》的规定:在本次发行的可转换公司债券期满后5个交易日内,公司将以本次可转债票面面值上浮6%(含最后一期利息)的价格向投资者赎回全部未转股的可转债。即“三力转债”到期合计兑付价格为106元/张(含最后一期利息,含税)。 2024年6月1日公告: 1、“三力转债”到期日和兑付登记日:2024年6月7日(星期五) 2、“三力转债”到期兑付价格:106元/张(含最后一期利息,含税) 3、“三力转债”到期兑付资金发放日:2024年6月11日(星期二) 4、“三力转债”摘牌日:2024年6月11日(星期二) 5、“三力转债”最后交易日:2024年6月4日(星期二) 6、“三力转债”最后转股日:2024年6月7日(星期五)	
', '交易日:+(\\d+年\\d+月\\d+日)'), 'yyyy年MM月dd日'), 'yyyyMMdd') 
,regexp_extract(regexp_extract(enforcereason, '交易日:+(\\d+年\\d+月\\d+日)'),'(\\d+)(年)',1)
        ,regexp_extract(regexp_extract(enforcereason, '交易日:+(\\d+年\\d+月\\d+日)'),'(年)(\\d+)(月)',2)
        ,lpad(regexp_extract(regexp_extract(enforcereason, '交易日:+(\\d+年\\d+月\\d+日)'),'(年)(\\d+)(月)',2),2,'0')
        ,lpad(regexp_extract(regexp_extract(enforcereason, '交易日:+(\\d+年\\d+月\\d+日)'),'(月)(\\d+)(日)',2),2,'0')
		,from_unixtime(unix_timestamp(regexp_extract(enforcereason, '交易日:+(\\d+年\\d+月\\d+日)'), 'yyyy年MM月dd日'), 'yyyyMMdd') as enforcereason1		--最后交易日
        ,concat(regexp_extract(regexp_extract(enforcereason, '交易日:+(\\d+年\\d+月\\d+日)'),'(\\d+)(年)',1),lpad(regexp_extract(regexp_extract(enforcereason, '交易日:+(\\d+年\\d+月\\d+日)'),'(年)(\\d+)(月)',2),2,'0'),lpad(regexp_extract(regexp_extract(enforcereason, '交易日:+(\\d+年\\d+月\\d+日)'),'(月)(\\d+)(日)',2),2,'0'));

with a as (select '2024年5月6日公告: 根据公司《可转债募集说明书》的规定:在本次发行的可转换公司债券期满后5个交易日内,公司将以本次可转债票面面值上浮6%(含最后一期利息)的价格向投资者赎回全部未转股的可转债。即“三力转债”到期合计兑付价格为106元/张(含最后一期利息,含税)。 2024年6月1日公告: 1、“三力转债”到期日和兑付登记日:2024年6月7日(星期五) 2、“三力转债”到期兑付价格:106元/张(含最后一期利息,含税) 3、“三力转债”到期兑付资金发放日:2024年6月11日(星期二) 4、“三力转债”摘牌日:2024年6月11日(星期二) 5、“三力转债”最后交易日:2024年6月4日(星期二) 6、“三力转债”最后转股日:2024年6月7日(星期五)' AS original_text)
select -- 提取最后交易日的日期字符串
regexp_extract(original_text, '最后交易日:(\\d+年\\d+月\\d+日)', 1) AS trade_date_str
-- 将日期字符串转为yyyymmdd时间戳
,concat(regexp_extract(regexp_extract(original_text, '最后交易[日期为:]+(\\d+年\\d+月\\d+日)'),'(\\d+)(年)',1) 
,lpad(regexp_extract(regexp_extract(original_text, '最后交易[日期为:]+(\\d+年\\d+月\\d+日)'),'(年)(\\d+)(月)',2),2,'0')
,lpad(regexp_extract(regexp_extract(original_text, '最后交易[日期为:]+(\\d+年\\d+月\\d+日)'),'(月)(\\d+)(日)',2),2,'0')) AS trade_date_format3
from a;
结果
trade_date_str	trade_date_format3
2024年6月4日	 20240604

在这里插入图片描述
hive 表中有个字段保存了资金账号、手机号和身份证号及其他信息，分别为8-9位数字，11位数字，和18位数字。替换为9

select '资金账号: 12345678, 手机号: 13800000000, 身份证号: 110101199001010000'
,regexp_replace(
    regexp_replace(
    regexp_replace('资金账号: 12345678, 手机号: 13800000000, 身份证号: 110101199001010000', '\\b\\d{18}\\b', '999999999999999999'), 
    '\\b\\d{11}\\b', '88888888888'
    ), 
    '\\b\\d{8,9}\\b', '77777777'
  ) AS new_info

\b：表示单词边界，确保匹配的是独立的数字字符串，而不是包含在其他数字或字符中的部分。
\d{8,9}：匹配8到9位的数字。
\d{11}：匹配11位的数字。
\d{18}：匹配18位的数字。
regexp_replace：Hive中的正则表达式替换函数，格式为regexp_replace(string, pattern, replacement)

hive 表中有个字段保存了资金账号、手机号和身份证号及其他信息，分别为8-9位数字，11位数字，和18位数字。查出相关记录

SELECT *
FROM user_info
WHERE info RLIKE '\\b\\d{8,9}\\b' -- 匹配8到9位数字的资金账号
   OR info RLIKE '\\b\\d{11}\\b' -- 匹配11位数字的手机号
   OR info RLIKE '\\b\\d{18}\\b'; -- 匹配18位数字的身份证号

RLIKE 是Hive中的正则表达式匹配函数，\b 表示单词边界，确保匹配的是独立的数字序列，而不是更大数字的一部分。\d{8,9} 表示匹配8到9位的数字，\d{11} 表示匹配11位的数字，\d{18} 表示匹配18位的数字。

提取固定前缀后缀字符串的分组内容

--从字符串honeymoon中，基于hon和moon这两个固定前缀/后缀作为“锚点”，提取第2个正则分组（也就是moon这个片段）的内容。
select regexp_extract('honeymoon', 'hon(.*?)(moon)',0),regexp_extract('honeymoon', 'hon(.*?)(moon)',1),regexp_extract('honeymoon', 'hon(.*?)(moon)',2);

结果 
_c0        _c1  _c2
honeymoon  ey   moon

取11位手机号码

select regexp_extract('MA;IIP=58.211.125.238;IPORT=39236;LIP=172.18.249.61;MAC=NA;IMEI=NA;RMPN=13861311293;UMPN=NA@DSPT-XYD-UA=www.tzt.cn Android Mobile Brow;HN=EMall-Wrs249-61','(\\d{11})',0) as tel_number;

1）国内电话号码已1开头，且第二位在3-9之间，所以有^1[3-9]
2）除上述两位数字外，其余还有9位，所以有\d{9}，且以加上结尾符号$
3）可以使用或条件进行更加严格的手机号匹配
select regexp_extract(lower(tel),'(^1[3-9]\\d{9}$)',0) as tel_number;
select 'selfstock.tzt.stock.15180445203.-1'
,regexp_extract('selfstock.tzt.stock.15180445203.-1','(\\d{11})',0) as tel_num
,regexp_extract('selfstock.tzt.stock.15180445203.-1','(1[0-9]{10})',0)

提取数字

with a as (select '158元2分' as je)
select regexp_extract(je,'([0-9]+)',1),split(regexp_replace(je,'([^0-9]+)',' '),' ')[1],je from a;

取id后面的数字

with a as (select 'http://127.0.0.1:61062/redmall/html/empd_prodtBuy.html?id=14634061&TZTREQUESTCRC=ACAE284CE7FAD3F42050027FA1CC395F' as info )
select info,regexp_extract(info,'id=([0-9]+)',1) from a
结果
info	_c1
http://127.0.0.1:61062/redmall/html/empd_prodtBuy.html?id=14634061&TZTREQUESTCRC=ACAE284CE7FAD3F42050027FA1CC395F	 14634061

取字母或者数字

select regexp_extract('MA;IIP=175.153.162.124;IPORT=26377;LIP=192.168.0.7;MAC=FED7A908ECBD;IMEI=NA;RMPN=18699171002;UMPN=18699171002;ICCID=NA;OSV=android10;IMSI=NA@XYD-VER=6.01.030;JYSERVER:172.18.1.133	
','MAC=([A-Za-z0-9]+)',1)
,regexp_extract('http://127.0.0.1:61062/redmall/html/empd_prodtBuy.html?id=14634061&TZTREQUESTCRC=ACAE284CE7FAD3F42050027FA1CC395F','id=([0-9]+)',1) ;
-- FED7A908ECBD  14634061

员工表中入职时间入职日期

（hire_date）包含有多个形式的数据，如2021/12/13 18：00：00、2021-12-13 18：00：00、20211213 18：00：00，请将数据统一成一个格式，（如20211213180000）
SELECT REGEXP_REPLACE('2021/12/13 18：00：00', '[^0-9]', '') as times,
    concat(
        -- 提取年份（4位，无需补零）
        split(split('2021/1/13 8：00：00', ' ')[0], '/')[0],
        -- 提取月份，lpad补零至2位（1→01）
        lpad(split(split('2021/1/13 8：00：00', ' ')[0], '/')[1], 2, '0'),
        -- 提取日期，lpad补零至2位（13→13，无需补零但兼容单位数）
        lpad(split(split('2021/1/13 8：00：00', ' ')[0], '/')[2], 2, '0'),
        -- 提取小时，先替换全角冒号，再拆分，lpad补零至2位（8→08）
        lpad(split(regexp_replace(split('2021/1/13 8：00：00', ' ')[1], '：', ':'), ':')[0], 2, '0'),
        -- 提取分钟，补零至2位（00→00）
        lpad(split(regexp_replace(split('2021/1/13 8：00：00', ' ')[1], '：', ':'), ':')[1], 2, '0'),
        -- 提取秒，补零至2位（00→00）
        lpad(split(regexp_replace(split('2021/1/13 8：00：00', ' ')[1], '：', ':'), ':')[2], 2, '0')
    ) AS target_datetime;
结果为
times	        target_datetime
20211213180000	20210113080000

提取opstaion的手机号码信息

（符合11位长度的数字），若不符合手机号规则，则置空值。
（E@183.135.107.156@15867854587@07396666555，E@223.104.161.221@13884412998，E@39.188.105.52@13906842974@0201111555，3@54E1ADE44071@125116208030@6666666）
Select cust_no,col1 from (
select cust_no,col1 from table lateral view explode(split(opstation,'@')) ad as col1 
) where length(col1) = 11 and length(regexp_replace(col1,'[a-z]+',',')) = 11；

判断字段串中连续出现两个以上的字母或者数字

WITH a AS (
    -- 1. 替换非数字为逗号，避免空值；2. 拆分后过滤空字符串；3. 筛选长度≥2的数字串
    SELECT explode(split(regexp_replace('sdfsd1232sdf324','[^0-9]',','),',')) AS col
) 
SELECT col AS continuous_numbers FROM a WHERE col <> ''  -- 过滤拆分后产生的空字符串
AND LENGTH(col) >= 2;  -- 连续2位及以上数字
结果 1232 324

select explode(split(regexp_replace('sdfsd1232sdf324','[0-9]+',','),','))
union all
select explode(split(regexp_replace('sdfsd1232sdf324','[a-z]+',','),','));
结果 sdfsd sdf 1232 324

按规则截取值

WITH test_data AS (
  SELECT '[bfare_kind=9943->9978];[ofare_kind=50->2657];[stbfare_kind=9999->9901];' AS fare_str
)
SELECT 
  split(fare_item, '=')[0] AS fare_type,
  split(fare_item, '=')[1] AS fare_value,
  split(split(fare_item, '=')[1],'->')[0] AS old_value,
  split(split(fare_item, '=')[1],'->')[1] AS new_value
FROM (
  SELECT 
    regexp_replace(trim(fare_segment), '[\\[\\]]', '') AS fare_item
  FROM (
    SELECT explode(split(fare_str, ';')) AS fare_segment
    FROM test_data
  ) t1
  WHERE trim(fare_segment) != '' AND fare_segment LIKE '[%]'
) t2;

在这里插入图片描述

WITH test_data AS (
  SELECT '[bfare_kind=9943->9978];[ofare_kind=50->2657];[stbfare_kind=9999->9901];' AS fare_str
) select * from (
SELECT 
  split(fare_item, '=')[0] AS fare_type
  ,(case when split(fare_item, '=')[0]='ffare_kind' then '前台费用'
when split(fare_item, '=')[0]='bfare_kind' then '后台费用'
when split(fare_item, '=')[0]='hfare_kind' then '回购费用'
when split(fare_item, '=')[0]='ofare_kind' then '场内基金'
when split(fare_item, '=')[0]='afofdiscount_kind' then '基金盘后折扣'
when split(fare_item, '=')[0]='zfare_kind' then '债券费用'
when split(fare_item, '=')[0]='qfare_kind' then '权证费用'
when split(fare_item, '=')[0]='dfare_kind' then '大宗交易费用'
when split(fare_item, '=')[0]='lowfare_flag' then '最低费用'
when split(fare_item, '=')[0]='cbfare_kind' then '融资融券后台费用（融资融券交易费用）'
when split(fare_item, '=')[0]='chfare_kind' then '融资融券回购费用'
when split(fare_item, '=')[0]='cofare_kind' then '融资融券基金费用'
when split(fare_item, '=')[0]='czfare_kind' then '融资融券债券费用'
when split(fare_item, '=')[0]='cqfare_kind' then '融资融券权证费用'
when split(fare_item, '=')[0]='prodfare_kind' then '多金融产品费用'
when split(fare_item, '=')[0]='optfare_kind' then '期权费用属性（个股期权费用）'
when split(fare_item, '=')[0]='hkfare_kind' then '港股通费用属性'
when split(fare_item, '=')[0]='stbfare_kind' then '全国股转费用'
when split(fare_item, '=')[0]='prodswapfare_kind' then '收益互换费用属性（多金融费用）'
when split(fare_item, '=')[0]='zyfare_kind' then '银行间债券交易费用属性'
when split(fare_item, '=')[0]='cstbfare_kind' then '融资融券北证费用属性'
when split(fare_item, '=')[0]='bgfare_kind' then 'B股费用属性（配置参数3695）'
when split(fare_item, '=')[0]='stbzfare_kind' then '北证及股转债券费用属性（配置参数3704）'
when split(fare_item, '=')[0]='cbzfare_kind' then '融资融券北交所债券费用属性'
else '' end) as fare_name
,cast((case when split(fare_item, '=')[0]='ffare_kind' then 1
when split(fare_item, '=')[0]='bfare_kind' then 2
when split(fare_item, '=')[0]='hfare_kind' then 3
when split(fare_item, '=')[0]='ofare_kind' then 4
when split(fare_item, '=')[0]='afofdiscount_kind' then 5
when split(fare_item, '=')[0]='zfare_kind' then 6
when split(fare_item, '=')[0]='qfare_kind' then 7
when split(fare_item, '=')[0]='dfare_kind' then 8
when split(fare_item, '=')[0]='lowfare_flag' then 9
when split(fare_item, '=')[0]='cbfare_kind' then 10
when split(fare_item, '=')[0]='chfare_kind' then 11
when split(fare_item, '=')[0]='cofare_kind' then 12
when split(fare_item, '=')[0]='czfare_kind' then 13
when split(fare_item, '=')[0]='cqfare_kind' then 14
when split(fare_item, '=')[0]='prodfare_kind' then 15
when split(fare_item, '=')[0]='optfare_kind' then 16
when split(fare_item, '=')[0]='hkfare_kind' then 17
when split(fare_item, '=')[0]='stbfare_kind' then 18
when split(fare_item, '=')[0]='prodswapfare_kind' then 19
when split(fare_item, '=')[0]='zyfare_kind' then 20
when split(fare_item, '=')[0]='cstbfare_kind' then 21
when split(fare_item, '=')[0]='bgfare_kind' then 22
when split(fare_item, '=')[0]='stbzfare_kind' then 23
when split(fare_item, '=')[0]='cbzfare_kind' then 24
else 25 end) as int) AS sort_weight
,split(fare_item, '=')[1] AS fare_value
,split(split(fare_item, '=')[1],'->')[0] AS old_value
,split(split(fare_item, '=')[1],'->')[1] AS new_value
FROM (
  SELECT 
    regexp_replace(trim(fare_segment), '[\\[\\]]', '') AS fare_item
  FROM (
    SELECT explode(split(fare_str, ';')) AS fare_segment
    FROM test_data
  ) t1
  WHERE trim(fare_segment) != '' AND fare_segment LIKE '[%]'
) t2 
) t3 SORT BY t3.sort_weight ASC;

在这里插入图片描述

排序方式	         生效范围	            适用场景	             问题
SORT BY	         每个Reduce分片内排序	大数据量（多Reduce）	 3行数据被分到2个Reduce：Reduce1：bfare_kind(2)、stbfare_kind(18)→分片内排序2→18
                                                             Reduce2：ofare_kind(4)→无排序,最终结果合并为 2→18→4
ORDER BY         全局排序（单Reduce）	小数据量	             强制所有数据进入1个Reduce，排序后为2→4→18
SORT BY+单Reduce 全局排序（单Reduce）	中大数据量(需全局排序) 强制单Reduce(SET mapred.reduce.tasks=1),SORT BY等同于ORDER BY

截取数字、去除固定文字、取文字中数字

select regexp_extract('501001.OF','([0-9]+)')
,regexp_extract('F050004.OF','([0-9]+)')
,regexp_replace('华夏纯债债券型证券投资基金A类', '(AB)$|(A$)|(B$)|(C$)|(A类)$|(B类)$|(C类)$|(Y类)$|(A/B类)$|(A/E类)$|(A/C类)$|(A/B)$|(E类)$|(A级)$|(A1)$|(I类)$', '') --$表示匹配字符串末尾
,regexp_replace('华夏纯债债券型证券投资基金I类', '(AB)$|(A$)|(B$)|(C$)|(A类)$|(B类)$|(C类)$|(Y类)$|(A/B类)$|(A/E类)$|(A/C类)$|(A/B)$|(E类)$|(A级)$|(A1)$|(I类)$', '')
,regexp_replace('华夏纯债债券型证券投资基金AB类', '(AB)', '')
,regexp_extract(split('客户姓名李明(客户号139009)为【20230924 14:09:03】策略商城产品购买断点客户，客户点击【产品名称:黄金眼(商品编号:19)】签约按钮但未成功下单（签约），请尽快跟进服务！','商品编号:')[1],'([0-9]+)',1);
-- ^在正则开头时表示匹配字符串开头，若在[]中（字符集），^表示取反,比如[^0-9]匹配非数字）：
select regexp_extract('sz501001OF', '[^0-9]',0),--匹配非数字字符 s
regexp_extract('501001.OF', '^[0-9]+',0),--匹配数字字符 501001
regexp_extract('sz501001.OF', '^[^0-9]+',0);--返回空（开头是数字，非数字不匹配） sz

在这里插入图片描述

获取网址数据，获取嵌套json数据及ip

select '尊敬的客户，1月新年有礼的活动已经开始报名啦，报名即可领豆，任意福利达标可领更多奖励，具体活动详情您可登录APP查看，快捷报名入口：{{https://Sidhiejr.com:8088/cms-h5/index.html?works_link_id=AErjx3MVyB}}。投资有风险，入市需谨慎。' as asd
,regexp_extract('尊敬的客户，1月新年有礼的活动已经开始报名啦，报名即可领豆，任意福利达标可领更多奖励，具体活动详情您可登录APP查看，快捷报名入口：{{https://Sidhiejr.com:8088/cms-h5/index.html?works_link_id=AErjx3MVyB}},{{https://Sidhiejr.com:8088/cms-h5/index.html?works_link_id=AErjx3MVyB}}。投资有风险，入市需谨慎。','(https)(.*?)(}})',0)
,replace(regexp_extract('尊敬的客户，1月新年有礼的活动已经开始报名啦，报名即可领豆，任意福利达标可领更多奖励，具体活动详情您可登录APP查看，快捷报名入口：{{https://Sidhiejr.com:8088/cms-h5/index.html?works_link_id=AErjx3MVyB}}','(https)(.*?)(}})',0),'}','');

在这里插入图片描述

获取json数据

{"distinct_id":"RNiHlL","time":1718651827371,"type":"track","event":"dl_pageview","properties":{"shortUrl":"https://t.bocichina.com/1ymiIn-RNiHlL","host":"116.210.236.211","longUrl":"https://vip.bocichina.com:8088/h5-outer/#/?target=points","ua":"{\"x-real-ip\":\"116.210.236.211\",\"host\":\"t.bocichina.com\",\"connection\":\"close\",\"x-forwarded-for\":\"116.210.236.211\",\"accept-encoding\":\"gzip\",\"user-agent\":\"antispam/1.0.0\",\"via\":\"1.1 ID-0016035524053044 uproxy-5\"}","dl_eventid":"1ymiIn","dl_num":"97871700","dl_time":"2024-06-18 03:17:07","$ip":"172.19.244.241","$is_login_id":false,"$city":"保留IP","$province":"保留IP","$country":"保留IP"},"lib":{"$lib":"NoLib","$lib_version":"unknown","$lib_method":"unknown","$lib_detail":"unknown","$app_version":"unknown"},"possible_remapping_data":true,"dtk":["dl_time"],"map_id":"RNiHlL","user_id":3186726444591,"recv_time":1718651802953,"extractor":{"f":"sdf_input_topic","o":4656918483,"n":"sdf_input_topic","s":4679099653,"c":4679099653,"p":0,"e":"data01.dcanalytics.sa"},"project_id":3,"project":"production","ver":2}

插入数据注意\转义

create table tmp.tablessasa (
v_date string
);

insert overwrite table tmp.tablessasa values('{"distinct_id":"RNiHlL","time":1718651827371,"type":"track","event":"dl_pageview","properties":{"shortUrl":"https://t.bocichina.com/1ymiIn-RNiHlL","host":"116.210.236.211","longUrl":"https://vip.bocichina.com:8088/h5-outer/#/?target=points","ua":"{\\"x-real-ip\\":\\"116.210.236.211\\",\\"host\\":\\"t.bocichina.com\\",\\"connection\\":\\"close\\",\\"x-forwarded-for\\":\\"116.210.236.211\\",\\"accept-encoding\\":\\"gzip\\",\\"user-agent\\":\\"antispam/1.0.0\\",\\"via\\":\\"1.1 ID-0016035524053044 uproxy-5\\"}","dl_eventid":"1ymiIn","dl_num":"97871700","dl_time":"2024-06-18 03:17:07","$ip":"172.19.244.241","$is_login_id":false,"$city":"保留IP","$province":"保留IP","$country":"保留IP"},"lib":{"$lib":"NoLib","$lib_version":"unknown","$lib_method":"unknown","$lib_detail":"unknown","$app_version":"unknown"},"possible_remapping_data":true,"dtk":["dl_time"],"map_id":"RNiHlL","user_id":3186726444591,"recv_time":1718651802953,"extractor":{"f":"sdf_input_topic","o":4656918483,"n":"sdf_input_topic","s":4679099653,"c":4679099653,"p":0,"e":"data01.dcanalytics.sa"},"project_id":3,"project":"production","ver":2}');

select v_date
,get_json_object(v_date,'$.distinct_id') as distinct_id
,get_json_object(v_date,'$.properties.dl_eventid') as dl_eventid
,get_json_object(v_date,'$.properties.dl_time') as dl_time
,get_json_object(v_date,'$.properties.dl_num') as dl_num
,get_json_object(v_date,'$.properties.ua') as ua
,get_json_object(v_date,'$.properties.host') as host
,get_json_object(v_date,'$.properties.longUrl') as longUrl
,get_json_object(v_date,'$.properties.shortUrl') as shortUrl
,replace(replace(split(substring_index(v_date,'$ip',-1),',')[0],'ip":"',''),'"','') as ip
 from tmp.tablessasa ;



使用get_json_object（单字段解析）
SELECT 
    v_date,
    -- 核心：用 JSON 路径直接提取 $ip 的值
    get_json_object(v_date, '$.properties.$ip') AS ip
FROM tmp.tablessasa;

使用json_tuple（多字段解析，性能更好）
SELECT 
    v_date,
    -- jt.ip 对应 $ip 的值，可同时解析多个字段
    jt.ip,
    jt.distinct_id,
    jt.dl_time
FROM tmp.tablessasa
LATERAL VIEW json_tuple(v_date, 
    'distinct_id',          -- 根节点字段
    'properties.$ip',       -- properties 下的 $ip
    'properties.dl_time'    -- properties 下的 dl_time
) jt AS distinct_id, ip, dl_time;

在这里插入图片描述

多层嵌套json且key里面含有特殊符号，神策数据解析

create table tmp.tablessasa (
v_data string
);

insert overwrite table tmp.tablessasa values('[{"login_id":"9c15488e65bf3ab2345657ba0b61e20a","time":1726736736552,"anonymous_id":"90F15655-B468-40E6-A729-052617BC12B3","event":"$AppPageLeave","_track_id":3470292893,"identities":{"$identity_login_id":"9c15488e65bf3ab2345657ba0b61e20a","$identity_idfv":"6469CB1C-D2AE-4BCE-B18F-0CC843247E79"},"properties":{"$os_version":"17.6.1","$os":"iOS","SourceScreenVersion":"5.0","$app_version":"6.04.050","SourceModuleVersion":"5.0","$screen_width":375,"$is_first_day":false,"$model":"iPhone16,2","$device_id":"6469CB1C-D2AE-4BCE-B18F-0CC843247E79","$network_type":"WIFI","$timezone_offset":-480,"$app_name":"uat-神策","$wifi":true,"SourceElementContent":"业务办理","$url":"tztZZWebInfoContentViewController","appName":"神策","$screen_height":812,"$referrer":"tztZZWebInfoContentViewController","$lib_method":"code","SourceModuleName":"快捷方式","$lib_version":"4.5.9","$lib":"iOS","$manufacturer":"Apple","event_duration":54.835,"$screen_name":"tztZZWebInfoContentViewController","SourceScreenName":"我的（登录，新版）","$app_id":"com.bocichina.newAppEnterprise"},"lib":{"$lib_version":"4.5.9","$lib":"iOS","$app_version":"6.04.050","$lib_method":"code"},"distinct_id":"9c15488e65bf3ab2345657ba0b61e20a","type":"track","_flush_time":1726736738288},{"login_id":"9c15488e65bf3ab2345657ba0b61e20a","time":1726736736554,"anonymous_id":"90F15655-B468-40E6-A729-052617BC12B3","event":"$AppViewScreen","_track_id":3381321132,"identities":{"$identity_login_id":"9c15488e65bf3ab2345657ba0b61e20a","$identity_idfv":"6469CB1C-D2AE-4BCE-B18F-0CC843247E79"},"properties":{"$os":"iOS","SourceScreenVersion":"5.0","$app_version":"6.04.050","SourceModuleVersion":"5.0","$screen_width":375,"$is_first_day":false,"$model":"iPhone16,2","$device_id":"6469CB1C-D2AE-4BCE-B18F-0CC843247E79","$network_type":"WIFI","$timezone_offset":-480,"$app_name":"uat-神策","$wifi":true,"SourceElementContent":"业务办理","$url":"tztZZWebInfoContentViewController","appName":"神策","$screen_height":812,"$referrer":"tztZZWebInfoContentViewController","SourceModuleName":"快捷方式","$screen_name":"tztZZWebInfoContentViewController","SourceScreenName":"我的（登录，新版）","$os_version":"17.6.1","$lib":"iOS","$manufacturer":"Apple","$lib_version":"4.5.9","$lib_method":"autoTrack","$app_id":"com.bocichina.newAppEnterprise"},"lib":{"$lib_detail":"tztZZWebInfoContentViewController######","$lib_version":"4.5.9","$lib":"iOS","$app_version":"6.04.050","$lib_method":"autoTrack"},"distinct_id":"9c15488e65bf3ab2345657ba0b61e20a","type":"track","_flush_time":1726736738288},{"login_id":"9c15488e65bf3ab2345657ba0b61e20a","time":1726736738273,"anonymous_id":"90F15655-B468-40E6-A729-052617BC12B3","event":"$AppPageLeave","_track_id":137504216,"identities":{"$identity_login_id":"9c15488e65bf3ab2345657ba0b61e20a","$identity_idfv":"6469CB1C-D2AE-4BCE-B18F-0CC843247E79"},"properties":{"$os_version":"17.6.1","$os":"iOS","SourceScreenVersion":"5.0","$app_version":"6.04.050","SourceModuleVersion":"5.0","$screen_width":375,"$is_first_day":false,"$model":"iPhone16,2","$device_id":"6469CB1C-D2AE-4BCE-B18F-0CC843247E79","$network_type":"WIFI","$timezone_offset":-480,"$app_name":"uat-神策","$wifi":true,"SourceElementContent":"业务办理","$url":"tztZZWebInfoContentViewController","appName":"神策","$screen_height":812,"$referrer":"tztZZWebInfoContentViewController","$lib_method":"code","SourceModuleName":"快捷方式","$lib_version":"4.5.9","$lib":"iOS","$manufacturer":"Apple","event_duration":1.72,"$screen_name":"tztZZWebInfoContentViewController","SourceScreenName":"我的（登录，新版）","$app_id":"com.bocichina.newAppEnterprise"},"lib":{"$lib_version":"4.5.9","$lib":"iOS","$app_version":"6.04.050","$lib_method":"code"},"distinct_id":"9c15488e65bf3ab2345657ba0b61e20a","type":"track","_flush_time":1726736738289},{"login_id":"9c15488e65bf3ab2345657ba0b61e20a","time":1726736738273,"anonymous_id":"90F15655-B468-40E6-A729-052617BC12B3","event":"$AppEnd","_track_id":3031134351,"identities":{"$identity_login_id":"9c15488e65bf3ab2345657ba0b61e20a","$identity_idfv":"6469CB1C-D2AE-4BCE-B18F-0CC843247E79"},"properties":{"$os":"iOS","SourceScreenVersion":"5.0","$app_version":"6.04.050","SourceModuleVersion":"5.0","$screen_width":375,"$is_first_day":false,"$model":"iPhone16,2","$device_id":"6469CB1C-D2AE-4BCE-B18F-0CC843247E79","$network_type":"WIFI","$timezone_offset":-480,"$app_name":"uat-神策","$wifi":true,"SourceElementContent":"业务办理","appName":"神策","$screen_height":812,"SourceModuleName":"快捷方式","$lib_method":"autoTrack","SourceScreenName":"我的（登录，新版）","$os_version":"17.6.1","$lib":"iOS","$manufacturer":"Apple","$lib_version":"4.5.9","event_duration":123.5,"$app_id":"com.bocichina.newAppEnterprise"},"lib":{"$lib_version":"4.5.9","$lib":"iOS","$app_version":"6.04.050","$lib_method":"autoTrack"},"distinct_id":"9c15488e65bf3ab2345657ba0b61e20a","type":"track","_flush_time":1726736738289}]
');

WITH step1_remove_brackets AS (
    -- 去除外层 [ 和 ]，得到 "obj1,obj2,obj3" 格式（保留中间的},{）
    SELECT
        regexp_replace(replace(v_data,'$',''), '^\\[|\\]$', '') AS json_str
    FROM  tmp.tablessasa
),
step2_split_objects AS (
    -- 按 ",\\s*{" 拆分（注意：实际分隔符是 "},{", 需拆分为 "},\\s*{"）
    SELECT
        explode(
            split(json_str, '\\},s*\\{')  -- 关键：拆分符为 "}, " 或 "},{", 需转义{ s*表示0个或者多个空白符
        ) AS single_json  -- 单个JSON对象的不完整字符串（可能缺少首尾的{或}）
    FROM step1_remove_brackets
),
step3_complete_json AS (
    -- 补全首尾的 { 和 }（处理第一个/最后一个元素可能缺失的情况）
    SELECT
        concat(
            case when substr(single_json,1,1) = '{' then '' else '{' end,  -- 开头补{
            single_json,
            case when substr(single_json,-1,1) = '}' then '' else '}' end   -- 结尾补}
        ) AS valid_json  -- 完整的JSON对象字符串
    FROM step2_split_objects
)
-- 步骤4：解析每个JSON对象的字段（按需提取字段）
SELECT
    get_json_object(valid_json, '$.distinct_id') AS distinct_id,
    get_json_object(valid_json, '$.time') AS event_time,
    FROM_UNIXTIME(cast(substring(get_json_object(valid_json, '$.time'),1,10) as int),'yyyy-MM-dd HH:mm:ss') as event_times,
    get_json_object(valid_json, '$.event') AS event_name,
    get_json_object(valid_json, '$.properties.is_first_day') AS is_first_day,
    get_json_object(valid_json, '$.lib.lib') AS lib_type,
    get_json_object(valid_json, '$._flush_time') AS flush_time,
    FROM_UNIXTIME(cast(substring(get_json_object(valid_json, '$._flush_time'),1,10) as int),'yyyy-MM-dd HH:mm:ss') as flush_times,
    -- 其他字段继续用 get_json_object 提取...
    substr(current_timestamp,1,19) as tech_etl_datetime
FROM step3_complete_json;


WITH step1_remove_brackets AS (
    SELECT
        regexp_replace(replace(datas,'$','lgkkgl'), '^\\[|\\]$', '') AS json_str
    FROM  ods.asd where etl_date=${last_date} and datas<>'' 
),
step2_split_objects AS (
    SELECT
        explode(
            split(json_str, '\\},s*\\{')  -- 关键：拆分符为 "}, " 或 "},{", 需转义{
        ) AS single_json  -- 单个JSON对象的不完整字符串（可能缺少首尾的{或}）
    FROM step1_remove_brackets
),
step3_complete_json AS (
    SELECT
        concat(
            case when substr(single_json,1,1) = '{' then '' else '{' end,  -- 开头补{
            single_json,
            case when substr(single_json,-1,1) = '}' then '' else '}' end   -- 结尾补}
        ) AS valid_json  -- 完整的JSON对象字符串
    FROM step2_split_objects --where single_json like '%app_version%'
)
SELECT
replace(get_json_object(valid_json, '$.distinct_id'),'lgkkgl','$') AS logn_imei_id,
replace(get_json_object(valid_json, '$.properties.oaid'),'lgkkgl','$') AS oaid,
replace(get_json_object(valid_json, '$.properties.imei'),'lgkkgl','$') AS imei,
replace(get_json_object(valid_json, '$.properties.lgkkgldevice_id'),'lgkkgl','$') AS device_id,
replace(get_json_object(valid_json, '$.properties.lgkkglurl'),'lgkkgl','$') AS url,
replace(get_json_object(valid_json, '$.properties.lgkkglurl_path'),'lgkkgl','$') AS url_path,
replace(get_json_object(valid_json, '$.properties.lgkkglis_first_day'),'lgkkgl','$') AS is_fst_visit,
replace(get_json_object(valid_json, '$.properties.lgkkglmanufacturer'),'lgkkgl','$') AS manufacturer,
replace(get_json_object(valid_json, '$.properties.lgkkglbrand'),'lgkkgl','$') AS brand,
replace(get_json_object(valid_json, '$.event'),'lgkkgl','$') AS event_name,
FROM_UNIXTIME(cast(substring(get_json_object(valid_json, '$.time'),1,10) as int),'yyyy-MM-dd HH:mm:ss') as click_time,
replace(get_json_object(valid_json, '$._track_id'),'lgkkgl','$') AS track_id,
FROM_UNIXTIME(cast(substring(get_json_object(valid_json, '$._flush_time'),1,10) as int),'yyyy-MM-dd HH:mm:ss') as flush_time,
substr(current_timestamp,1,19) as tech_etl_datetime,
valid_json,
replace(get_json_object(valid_json, '$.properties.lgkkglapp_version'),'lgkkgl','$') AS app_version
FROM step3_complete_json;

对array数据处理,统计里面配置参数次数

with tablea as
(
select '{"ver":"2.0","con":[{"op":1,"fd":"client_name","cmp":7,"val":"陈","desc":"客户姓名 包含 陈","c_has_operator":"3"},{"op":1,"fd":"client_age","cmp":4,"val":"29","desc":"年龄 = 29岁","c_has_operator":"1"}]}' as group_condition
union all
select '{"ver":"2.0","con":[{"op":1,"sub":[{"op":1,"fd":"client_sex","cmp":4,"val":"0","desc":"客户性别 = 男","c_has_operator":"0"},{"op":"0","fd":"client_sex","cmp":4,"val":"1","desc":"客户性别 = 女","c_has_operator":"0"}]}]}' as group_condition
union all
select '中间状态客群名' as group_condition
)
select group_condition from tablea;

在这里插入图片描述

with tablea as 
(
select '{"ver":"2.0","con":[{"op":1,"fd":"client_name","cmp":7,"val":"陈","desc":"客户姓名 包含 陈","c_has_operator":"3"},{"op":1,"fd":"client_age","cmp":4,"val":"29","desc":"年龄 = 29岁","c_has_operator":"1"}]}' as group_condition
union all 
select '{"ver":"2.0","con":[{"op":1,"sub":[{"op":1,"fd":"client_sex","cmp":4,"val":"0","desc":"客户性别 = 男","c_has_operator":"0"},{"op":"0","fd":"client_sex","cmp":4,"val":"1","desc":"客户性别 = 女","c_has_operator":"0"}]}]}' as group_condition
union all
select '中间状态客群名' as group_condition
) 
select group_condition,get_json_object(group_condition,'$.con.fd') as arr from tablea where  get_json_object(group_condition,'$.con.fd') is not null
union all 
select group_condition, get_json_object(group_condition,'$.con.sub.fd') as arr from tablea where  get_json_object(group_condition,'$.con.sub.fd') is not null;

在这里插入图片描述

with tablea as 
(
select '{"ver":"2.0","con":[{"op":1,"fd":"client_name","cmp":7,"val":"陈","desc":"客户姓名 包含 陈","c_has_operator":"3"},{"op":1,"fd":"client_age","cmp":4,"val":"29","desc":"年龄 = 29岁","c_has_operator":"1"}]}' as group_condition
union all 
select '{"ver":"2.0","con":[{"op":1,"sub":[{"op":1,"fd":"client_sex","cmp":4,"val":"0","desc":"客户性别 = 男","c_has_operator":"0"},{"op":"0","fd":"client_sex","cmp":4,"val":"1","desc":"客户性别 = 女","c_has_operator":"0"}]}]}' as group_condition
union all
select '中间状态客群名' as group_condition
) 
select group_condition,get_json_object(group_condition,'$.con.fd') as arr ,regexp_replace(regexp_replace(get_json_object(group_condition,'$.con.fd'),'"',''),'\\[|\\]','') 
,c1
from tablea 
--where  get_json_object(group_condition,'$.con.fd') is not null 
lateral view explode(split(regexp_replace(regexp_replace(get_json_object(group_condition,'$.con.fd') ,'"',''),'\\[|\\]','') ,',')) b as c1
union all 
select group_condition,get_json_object(group_condition,'$.con.sub.fd') as arr ,regexp_replace(regexp_replace(get_json_object(group_condition,'$.con.sub.fd'),'"',''),'\\[|\\]','') 
,c1
from tablea 
--where  get_json_object(group_condition,'$.con.sub.fd') is not null 
lateral view explode(split(regexp_replace(regexp_replace(get_json_object(group_condition,'$.con.sub.fd') ,'"',''),'\\[|\\]','') ,',')) b as c1;

在这里插入图片描述

with tablea as 
(
select '{"ver":"2.0","con":[{"op":1,"fd":"client_name","cmp":7,"val":"陈","desc":"客户姓名 包含 陈","c_has_operator":"3"},{"op":1,"fd":"client_age","cmp":4,"val":"29","desc":"年龄 = 29岁","c_has_operator":"1"}]}' as group_condition
union all 
select '{"ver":"2.0","con":[{"op":1,"sub":[{"op":1,"fd":"client_sex","cmp":4,"val":"0","desc":"客户性别 = 男","c_has_operator":"0"},{"op":"0","fd":"client_sex","cmp":4,"val":"1","desc":"客户性别 = 女","c_has_operator":"0"}]}]}' as group_condition
union all
select '中间状态客群名' as group_condition
)  ,
 asd as (select group_condition,get_json_object(group_condition,'$.con.fd') as arr ,regexp_replace(regexp_replace(get_json_object(group_condition,'$.con.fd'),'"',''),'\\[|\\]','') 
,c1
from tablea 
--where  get_json_object(group_condition,'$.con.fd') is not null 
lateral view explode(split(regexp_replace(regexp_replace(get_json_object(group_condition,'$.con.fd') ,'"',''),'\\[|\\]','') ,',')) b as c1
union all 
select group_condition,get_json_object(group_condition,'$.con.sub.fd') as arr ,regexp_replace(regexp_replace(get_json_object(group_condition,'$.con.sub.fd'),'"',''),'\\[|\\]','') 
,c1
from tablea 
--where  get_json_object(group_condition,'$.con.sub.fd') is not null 
lateral view explode(split(regexp_replace(regexp_replace(get_json_object(group_condition,'$.con.sub.fd') ,'"',''),'\\[|\\]','') ,',')) b as c1)
select * from asd;

在这里插入图片描述

with tablea as 
(
select '{"ver":"2.0","con":[{"op":1,"fd":"client_name","cmp":7,"val":"陈洋勇","desc":"客户姓名 包含 陈洋勇","c_has_operator":"3"},{"op":1,"fd":"client_age","cmp":4,"val":"29","desc":"年龄 = 29岁","c_has_operator":"1"}]}' as group_condition
union all 
select '{"ver":"2.0","con":[{"op":1,"sub":[{"op":1,"fd":"client_sex","cmp":4,"val":"0","desc":"客户性别 = 男","c_has_operator":"0"},{"op":"0","fd":"client_sex","cmp":4,"val":"1","desc":"客户性别 = 女","c_has_operator":"0"}]}]}' as group_condition
union all
select '中间状态客群名' as group_condition
) ,
 asd as (select group_condition,get_json_object(group_condition,'$.con.fd') as arr ,regexp_replace(regexp_replace(get_json_object(group_condition,'$.con.fd'),'"',''),'\\[|\\]','') 
,c1
from tablea 
--where  get_json_object(group_condition,'$.con.fd') is not null 
lateral view explode(split(regexp_replace(regexp_replace(get_json_object(group_condition,'$.con.fd') ,'"',''),'\\[|\\]','') ,',')) b as c1
union all 
select group_condition,get_json_object(group_condition,'$.con.sub.fd') as arr ,regexp_replace(regexp_replace(get_json_object(group_condition,'$.con.sub.fd'),'"',''),'\\[|\\]','') 
,c1
from tablea 
--where  get_json_object(group_condition,'$.con.sub.fd') is not null 
lateral view explode(split(regexp_replace(regexp_replace(get_json_object(group_condition,'$.con.sub.fd') ,'"',''),'\\[|\\]','') ,',')) b as c1)
select c1,count(1) from asd group by c1;

在这里插入图片描述

with tablea as
(
select '{"ver":"2.0","con":[{"op":1,"fd":"client_name","cmp":7,"val":"陈","desc":"客户姓名 包含 陈","c_has_operator":"3"},{"op":1,"fd":"client_age","cmp":4,"val":"29","desc":"年龄 = 29岁","c_has_operator":"1"}]}' as group_condition
)
select
  -- 原始 JSON 字符串
group_condition,
-- 解析con数组（返回的仍是JSON字符串形式的数组）
get_json_object(group_condition, '$.con') AS con_json_array,
-- 解析数组中第一个元素的某个字段
get_json_object(group_condition, '$.con[0].fd') AS first_fd,
-- 解析数组中第二个元素的val字段
get_json_object(group_condition, '$.con[1].val') AS second_val
from tablea;

在这里插入图片描述

json数组拆分

首先去除’[ ] $'三种符号,然后对 ‘},{’ 替换成 '};{‘方便切分为json格式,最后以’;'切片后爆炸对json提取内容

create table tmp.tmp_test1(
position string
);
insert into table tmp.tmp_test1
select '[{"e":3554,"i":723,"v":13000,"s":"0050666065","stop_date":null,"t":0,"lmtm":1780470807593450444},{"e":3554,"i":887,"v":2300,"s":"0050666065","stop_date":null,"t":0,"lmtm":1780953674163190700},{"e":3554,"i":895,"v":2600,"s":"0050666065","stop_date":null,"t":0,"lmtm":1780470807593450444},{"e":3554,"i":2249,"v":8300,"s":"0050666065","stop_date":null,"t":0,"lmtm":1780539545104463108},{"e":3554,"i":2456,"v":8900,"s":"0050666065","stop_date":null,"t":0,"lmtm":1780539545104462916}]' as position;


SELECT a.position,
get_json_object(json_item, '$.e') AS market_id,
get_json_object(json_item, '$.i') AS stock_code,
lpad(cast(get_json_object(json_item, '$.i') as string),6,'0') asstock_code1,
get_json_object(json_item, '$.v') AS auth_qty,
get_json_object(json_item, '$.s') AS shareholder_no,
get_json_object(json_item, '$.stop_date') AS stop_date,
get_json_object(json_item, '$.t') AS parent_order_type,
get_json_object(json_item, '$.lmtm') AS modify_time,
from_unixtime(cast(get_json_object(json_item, '$.lmtm')/1000000000 as bigint),'yyyy-MM-dd HH:mm:ss') as date_time
FROM tmp.tmp_test1 a
LATERAL VIEW EXPLODE(split(regexp_replace(regexp_replace(a.position, '^\\[|\\]$|\$$', ''), '\\}\\,\\{', '\\}\\;\\{'), ';')) t AS json_item
--首先去除'[ ] $',然后对 '},{' 替换成 '};{',最后以';'切片后爆炸对json提取内容

a.position	market_id	stock_code	asstock_code1	auth_qty	shareholder_no	stop_date	parent_order_type	modify_time	date_time

[{"e":3554,"i":723,"v":13000,"s":"0050666065","stop_date":null,"t":0,"lmtm":1780470807593450444},{"e":3554,"i":887,"v":2300,"s":"0050666065","stop_date":null,"t":0,"lmtm":1780953674163190700},{"e":3554,"i":895,"v":2600,"s":"0050666065","stop_date":null,"t":0,"lmtm":1780470807593450444},{"e":3554,"i":2249,"v":8300,"s":"0050666065","stop_date":null,"t":0,"lmtm":1780539545104463108},{"e":3554,"i":2456,"v":8900,"s":"0050666065","stop_date":null,"t":0,"lmtm":1780539545104462916}]	3554	723	000723	13000	0050666065	null    0	1780470807593450000	2026-06-03 15:13:27

天工造物局

北京人形旗下天工造物具身智能开源社区，聚焦具身天工与慧思开物两大平台

更多推荐

deck.gl TripsLayer 出租车轨迹动画示例实战：从示例运行到着色器原理

TripsLayer 是 deck.gl 中用于渲染**随时间流动的轨迹动画**（如出租车行程、物流路径、迁徙路线）的核心图层。本文以仓库中的最小独立示例 [examples/website/trips](https://link.gitcode.com/i/1442af8f442f4cff39ff546e050fceb5) 为骨架，带你完成从安装依赖、启动应用、理解曼哈顿出租车数据格式，到深入

天工造物局

Foundry Anvil 中 eth_simulateV1 的 Blob 交易与 Blob Gas 字段规范化：原理、实现与测试验证

本篇技术指南以 [anvil-simulate-blob-transactions.md](https://link.gitcode.com/i/3d2ebe227262de169335eb290af80da8) 这条 changelog 条目为起点，深入剖析 Foundry Anvil 对 `eth_simulateV1` RPC 返回结果中 blob（EIP-4844）交易与 blob gas

天工造物局

LMCache CLI 实战：用 `lmcache kvcache clear` 管理运行中服务的 KV Cache 状态

本文是一份针对 LMCache 官方 CLI 命令 `lmcache kvcache` 的完整技术指南。该命令用于在**运行中的 LMCache 服务（MP HTTP server）**上管理 KV cache 状态，当前提供 `clear` 子命令，用于清空 L1（CPU 内存）层中缓存的全部 KV 数据。读完本文，你将掌握该命令的完整参数体系、退出码语义、终端/JSON/静默三种输出模式，并能