ASPIRE

Hive SQL中的regexp_extract_all函数的学习

=Start=

缘由：

之前没怎么用过 regexp_extract_all 这个函数，用的比较多的是 regexp_extract 这个，但是近期在分析处理一些日志的时候发现 regexp_extract_all 这个函数还是挺有用的，所以整理总结一下，方便后面有需要的时候参考。

正文：

参考解答：
语法&参数&返回

regexp_extract_all(str, regexp [, idx] )

  • str：匹配的 STRING 表达式。
  • regexp：具有匹配模式的 STRING 表达式。
  • idx：大于或等于 0 的可选整数表达式，默认值为 1。

返回 ARRAY 。

  • 字符串 regexp 必须是 Java 正则表达式。
  • 使用文本时，请使用raw-literal（r 前缀）来避免转义字符预处理。
  • regexp 可以包含多个组。 idx 指示要提取的正则表达式组。 如果 idx 为 0，则表示匹配整个正则表达式。
一些SQL样例
SELECT
regexp_extract_all('100-200, 300-400', '(\\d+)-(\\d+)', 1) as v1
,regexp_extract_all('100-200, 300-400', '(\\d+)-(\\d+)', 0) as vx
,regexp_extract_all('100-200, 300-400', '(\\d+)', 1) as v2
,regexp_extract_all('100-200, 300-400', '(\\d+)', 0) as v3
,regexp_extract_all('100-200, 300-400', '\\d+', 0) as v4 --如果是要匹配所有数字的话，regexp_extract_all函数的第3个参数一般写0，表示匹配整个正则表达式


["100","300"]
["100-200","300-400"]
["100","200","300","400"]
["100","200","300","400"]
["100","200","300","400"]

对于SQL语句的解析和内容提取，有一些注意事项

  1. 先格式化再提取（格式化指的是把一些注释和多余的空白符号进行替换，避免后面处理脏数据）
  2. 简单粗暴的 regexp_extract_all 好处在于一次提取，反复使用；坏处在于可能（肯定）会遇到脏数据【推荐这种方式，但需要做一些力所能及的过滤，减少后面脏数据出现的概率】
  3. 定向 regexp_extract 提取的好处在于精准，坏处在于总有自己考虑不周的地方，导致需要反复修改和重跑数据，影响效率和浪费资源
  4. 不断学习、不断优化更新

提取SQL语句中的所有长度大于等于5的数字字符串（需要注意排除一些不需要的数字，比如日期格式的数字字符串、limit后面的数字字符串、set语句后面可能包含的数字字符串等等）

  • regexp_extract_all
  • array_except – 用于计算两个数组的差集
-- 示例代码
with t1 as (
-- 用双引号括起来的内容可以直接换行写，只是其中不能包含英文分号，这点要注意，具体原因之前的文章记录过了，在此就不赘述
select "select
1234567 as id_int ,'2345678' as id_str
from db1.tbl1
where
date between '20240101' and '20241201'
or dt between '2024-01-01' and '2024-12-01'
and condition1 > 3456789
and condition2 < 9876543
limit 999999
limit 100001" as multi_line_var
)

SELECT
size(num_array) as num_cnt
,num_array
-- ,num_array2
,num_array3
,array_except(num_array,yyyymmdd_array) as filted_num --排除掉匹配出来的yyyymmdd日期格式的数字字符串
,num1
,yyyy_mm_dd_array
,yyyymmdd_array
,limit_num
FROM
(select multi_line_var
,regexp_extract_all(multi_line_var,'[1-9]\\d{4,}',0) as num_array --可能会把limit后面的数字给匹配出来，一种办法是在此之前就把limit那一小段给去掉
-- ,regexp_extract_all(multi_line_var,'(?<!limit\\s{1,})[1-9]\\d{4,}',0) as num_array2 --另一种办法是借助正则表达式的反向否定预查来排除，但不一定能排除干净，比如这里的100001可以被排除，但是999999不行，而且查询速度慢了很多
,regexp_extract_all(regexp_replace(multi_line_var,'\\s+limit\\s+(\\d+)\\s*','') ,'[1-9]\\d{4,}',0) as num_array3
,regexp_extract_all(multi_line_var,'20[1-2][0-9]\\-(0[1-9]|1[0-2])\\-(0[1-9]|1[0-9]|2[0-9]|3[0-1])',0) as yyyy_mm_dd_array
,regexp_extract_all(multi_line_var,'20[1-2][0-9](0[1-9]|1[0-2])(0[1-9]|1[0-9]|2[0-9]|3[0-1])',0) as yyyymmdd_array
,regexp_extract(multi_line_var, '\\s+limit\\s+(\\d+)\\s*', 1) as limit_num --regexp_extract函数只会提取第一个匹配的内容，这里只是为了演示所以在一个SQL语句里面连着写了2个limit
from t1
)x1
lateral view explode(num_array) num as num1

# num_array
["1234567","2345678","20240101","20241201","3456789","9876543","999999","100001"]

# num_array3
["1234567","2345678","20240101","20241201","3456789","9876543","100001"]

# filted_num
["1234567","2345678","3456789","9876543","999999","100001"]

# yyyy_mm_dd_array
["2024-01-01","2024-12-01"]

# yyyymmdd_array
["20240101","20241201"]

# limit_num
999999

提取SQL中所有的中文字符串

-- 和前面的数字字符串提取一个套路，不过这里用的是unicode中常见中文字符的编码范围
,regexp_extract_all(query_sql,'[\\u4e00-\\u9fa5]+',0) as chinese_array
参考链接：

regexp_extract_all 函数
https://learn.microsoft.com/zh-cn/azure/databricks/sql/language-manual/functions/regexp_extract_all

正则式函数
https://cloud.tencent.com/document/product/614/63505

HIVE使用正则表达式截取金额信息
https://blog.csdn.net/weixin_43929753/article/details/135401116

HIVE用户自义定函数:正则匹配所有子串
https://juejin.cn/post/6898522310425362445

hive自定义函数, 目前包含:regexp_extract_all
https://github.com/leeshuaichao/hive_functions

正则表达式的基本使用
https://docs.guandata.com/product/bi/428146874701053952

hive 正则表达式匹配多个结果的方法
https://blog.51cto.com/u_16213440/7602587

hive regexp_extract weirdness
https://stackoverflow.com/questions/8497090/hive-regexp-extract-weirdness/8497735#8497735

hive udf函数 array_except 实现
https://blog.csdn.net/qq_35515661/article/details/130161544

Hive array_except GenericUDF编写
https://ixyzero.com/blog/archives/5594.html

提取SQL中的limit限制数值
https://ixyzero.com/blog/archives/5428.html

数组函数和运算符
https://www.alibabacloud.com/help/zh/sls/user-guide/array-functions-and-operators#section-h1n-rrz-7xn

正则表达式在前面或者后面不想匹配某字符串的特殊用法
https://blog.csdn.net/zh515858237/article/details/113877648

近期的一些零碎知识点整理
https://ixyzero.com/blog/archives/5508.html

Unicode汉字范围[bak]
https://ixyzero.com/blog/archives/1524.html

=END=

Docker

, ,
, , , , , , ,

《 “Hive SQL中的regexp_extract_all函数的学习” 》 有 3 条评论

  1. regexp_replace 可能会对字符串进行多次连续替换，直到结果字符串中匹配不到指定正则表达式的内容
    `
    with t1 as (
    select “foobar” as str1
    ,”fooobar” as str2
    )

    select
    str1
    ,regexp_replace(str1, ‘oo|ar’, ”) as str1_1 –‘fb’
    ,str2
    ,regexp_replace(str2, ‘oo|ar’, ”) as str2_1 –‘fob’
    ,regexp_replace(str2, ‘oo|ar|ob’, ”) as str2_2 –‘f’ 从这里可以看出 regexp_replace 会对字符串进行多次连续替换，直到结果字符串中匹配不到指定正则表达式的内容
    from
    t1

    `

    回复

  3. 数据集（本质上是ClickHouse数据库表）这类数据的查询，ta的SQL会自动给添加上【以下划线开始+数字】的别名，这种情况下如果直接用regexp_extract_all函数提取数字，会把后面的这部分无意义的数字提取出来，因此需要排除掉这部分内容的提取，还有limit语句后面的数字也需要排除。

    `
    # 数据集的SQL的情况
    SELECT (advertiser_name) AS _1700007340280,
    (advertiser_id) AS _1700007340302,
    (sum(coalesce(round(cash_cost/100000, 2), 0))) AS _sum_1700007340301,
    …
    where
    …
    (advertiser_id) IN ((123935752),(1234956722639367),…)
    …
    limit 123456789
    `

    有些数字仅仅只是数值的含义，而非特定类型的ID的意思，因此需要进行排除，总结整理的一些排除条件有：
    * 数字前面就直接是英文下划线【_】
    * 数字前面有limit后面接着空白符
    * 数字前面有 【加减乘除、大于等于小于等比较符号】

    and lower(sql) not rlike ‘(\\+|-|\\*|/|>|>=|<|<=|limit|_)\\s{0,}\\d{5,}' –排除掉加减乘除、大于等于小于等比较符号
    and sql not rlike '20[1-2][0-9](0[1-9]|1[0-2])(0[1-9]|1[0-9]|2[0-9]|3[0-1])' –排除掉日期字符串
    and sql rlike '\\b\\d{5,}\\b' –用\b做一下单词边界锚定的限制，避免匹配到某个单词内满足条件的数字部分

    回复

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注