兄弟们写ODPS SQL的有没有被正则表达式折磨过我印象最深的一次是在MySQL里跑得好好的匹配规则原封不动挪到ODPS SQL里结果一行都匹配不上。排查了半天发现是正则表达式里的反斜杠被ODPS的字符串解析吃掉了一层。从那以后我就明白ODPS SQL的正则跟你想的不太一样。这篇文章就是把我在ODPS SQL里用正则的经验整理一遍适合正在用MaxCompute做数据开发、数据分析、ETL清洗的同学参考。会讲清楚ODPS正则和MySQL/Hive的差别、六个常用函数的正确姿势、反斜杠双重转义这个最大的坑以及几个能直接抄作业的实战模板。最后聊一下性能问题——正则这东西用好了是利器用不好能让几十亿行的大查询直接跑崩。1. 同源不同命ODPS SQL正则和别的SQL到底差在哪很多人在MySQL、Hive里用过正则就以为到了ODPS SQL可以直接照搬。这个想法我一开始也有后来被现实教育了。ODPSMaxCompute的底层是Java体系它的正则语法基本遵循Java的正则规范和MySQL那种基于POSIX/ICU的实现、Hive自封装的那套正则API看起来很像实际用起来细节差异很大。最大的差异点有两个。第一个是函数行为同样叫regexp_extractODPS的groupid默认值和边界处理跟Hive不完全一样同样一个正则在MySQL里能匹配的文本在ODPS里可能因为转义层数不同就匹配不上。第二个是转义规则ODPS SQL的字符串字面量本身对反斜杠有解析正则表达式作为字符串传进去时等于套了两层转义。这一块最容易翻车后面我会单独用一整章来讲。1.1 一张表看全六个函数ODPS SQL提供的正则函数一共有六个我把它们的返回值、一句话用途整理成了下面这个表函数名返回值一句话说明regexp_like(source, pattern)Boolean判断source是否匹配patternregexp_extract(source, pattern[, groupid])String提取匹配到的字符串片段regexp_replace(source, pattern, replace_string[, occurrence])String替换匹配到的内容regexp_count(source, pattern[, start_position])Bigint统计匹配次数regexp_instr(source, pattern[, start_position[, nth_occurrence[, return_option]]])Bigint返回匹配位置regexp_split(source, pattern[, limit])ArrayString按匹配位置拆分成数组这六个函数覆盖了日常数据开发99%的场景判断LIKE、提取EXTRACT、替换REPLACE、计数COUNT、定位INSTR、拆分SPLIT。把它们用明白了文本处理基本就够使了。1.2 这些场景才值得上正则正则表达式不是万能锤子用之前先想清楚场景是否合适。我自己一般只在这几类需求里用它字段格式校验手机号、身份证号、邮箱、IP地址是不是符合规范。从复杂文本里抽字段日志里抽IP、URL里取域名和参数、一段大文本里抠数字和日期。数据清洗与脱敏电话号中间四位打码、身份证出生日期提取、去掉字符串里的特殊符号。字符串结构判断判断某个字段是不是JSON、是不是纯数字、是不是包含了某种模式的子串。反过来如果是简单的数值大小判断、前后缀判断、固定分隔符拆分就老实走比较运算符、LIKE、SUBSTR、SPLIT_PART这些内置函数。正则表达式有它的开销能用简单函数解决的事别硬上正则这个道理后面聊性能的时候会细说。2. 六个函数各自的分工从最常用的到最容易被忽略的这章我把六个函数一个一个拆开讲每个都给出语法、边界条件和典型写法。我尽量用最贴近实际开发的例子而不是文档里那种意义不明的demo。2.1 REGEXP_LIKEWHERE里的守门员regexp_like(source, pattern)返回的是布尔值最常用的地方就是WHERE条件。比如筛选出所有合法的手机号SELECT phone FROM user_profile WHERE regexp_like(phone, ^1[3-9]\\d{9}$);这里正则表达式^1[3-9]\\d{9}$的意思是从头到尾是1开头第二位是3到9后面跟9位数字。注意我在SQL里写的是\\d而不是\d这就是前面说的双重转义下面第三章细讲。用regexp_like有两点要提醒。第一它和你直觉里的LIKE不一样regexp_like是包含匹配而不是全匹配。如果pattern写成1[3-9]那么13112345678abc也会被匹配上。所以需要全匹配时一定记得在正则首尾加上^和$。第二source为NULL时返回NULL在WHERE里会被当成分数过滤掉一般不用特殊处理但如果你用CASE WHEN包它记得先处理NULL。还有大小写问题。ODPS的正则默认区分大小写如果要做不区分大小写的匹配可以在正则最前面加内联标志(?i)SELECT name FROM user_table WHERE regexp_like(name, (?i)^zhang);2.2 REGEXP_EXTRACT从文本里捞数据的探针regexp_extract(source, pattern[, groupid])是六个函数里使用频率最高的一个它负责从字符串里提取匹配片段。语法第三参groupid控制你提取第几个捕获组注意这里的编号从1开始不是Java正则里习惯的group(0)。如果pattern里没写括号groupid固定传1就可以拿到整个匹配结果。但实际写代码时我基本都会在正则里打括号因为这样提取更精确。举一个从URL里提取域名的例子SELECT url, regexp_extract(url, https?://([^/]), 1) AS domain FROM click_log;https?://匹配http://或https://([^/])匹配后面到第一个斜杠之前的所有字符也就是域名。用[^/]而不是.*是为了避免贪婪匹配吃掉后面不该吃的内容这个细节很关键后面性能章会展开。regexp_extract匹配不到时返回NULL不会报错。但如果groupid超出实际捕获组数量行为就不确定了不同版本的ODPS表现有差异有的返回NULL有的直接报错。所以写正则时大脑里要清楚一共打了几个括号groupid对应第几个。2.3 REGEXP_REPLACE清洗和脱敏的一把刀regexp_replace(source, pattern, replace_string[, occurrence])用它做脱敏和清洗非常顺手。它有第四参occurrence指定替换第几个匹配项0表示全部替换省略时默认0。这个参数在很多SQL引擎里没有是ODPS比较友好的一个扩展。最经典的场景就是手机号脱敏SELECT phone, regexp_replace(phone, (\\d{3})\\d{4}(\\d{4}), \\1****\\2) AS masked_phone FROM user_profile;正则(\\d{3})\\d{4}(\\d{4})把11位手机号分成了三段前3位和后4位分别用括号括起来。替换串\\1****\\2里的\\1和\\2分别代表第一、第二个捕获组注意在SQL字符串里引用捕获组也要写双反斜杠。执行结果是138****1234这种效果。再举一个清洗特殊字符的例子。有些上游数据会把不可见控制字符、制表符混进文本里导致下游报表对不齐可以这样清掉所有空白和特殊符号SELECT regexp_replace(remark, [\\s\\p{Cntrl}], ) AS clean_remark FROM raw_table;正则[\\s\\p{Cntrl}]匹配所有空白字符和控制字符Java正则支持\p{Cntrl}这种Unicode属性写法这个也是ODPS和MySQL的差异点之一。注意替换串为空字符串是合法的效果就等于删除匹配内容。2.4 容易被忽视的三兄弟REGEXP_COUNT、REGEXP_INSTR、REGEXP_SPLIT这三个函数用的频率不如前三个高但特定场景下非常好用我这里一起说了。regexp_count(source, pattern[, start_position])用来统计匹配次数。比如统计一个字段里逗号的数量用于判断数组字段的维度SELECT tags, regexp_count(tags, ,) 1 AS tag_count FROM item_table;注意如果source为NULL返回值是NULL不是0做加法时要留意。regexp_instr(source, pattern[, start_position[, nth_occurrence[, return_option]]])返回匹配的开始位置从1开始计数。比如找字符串里第一个数字的位置SELECT regexp_instr(abc123def, \\d) AS first_digit_pos;返回4。这个函数适合做动态截取先定位位置再用substr截取。再加上return_option参数设为1时返回的是匹配结束位置的下一个位置具体用到时再查文档。regexp_split(source, pattern[, limit])返回的是ARRAYString。它和split函数最大的区别是支持正则分割。比如把一段以空格或逗号或分号分隔的标签字段拆成数组SELECT id, regexp_split(tag_str, [,;\\s]) AS tag_arr FROM raw_table;注意连续的分隔符会被当成一个处理不会产生空字符串元素这一点和Java的split行为一致。如果要把数组展开成多行就用LATERAL VIEW配合EXPLODESELECT id, single_tag FROM raw_table LATERAL VIEW EXPLODE(regexp_split(tag_str, [,;\\s])) t AS single_tag;3. 双重转义这个坑一次讲明白反斜杠的来龙去脉很多人在ODPS SQL里写正则第一次都跪在转义上。我自己也不例外。这一章把转义的底层逻辑彻底讲透以后你就不会再被它坑了。3.1 为什么会需要两层转义这个问题的根源在于你写的正则本质上是放在SQL字符串里的文本这个文本要先经过SQL字符串解析器再传给正则引擎去解析。反斜杠在这两层里都有特殊含义。先看正则引擎这一层。在正则表达式里\d表示数字\.表示英文句点\\表示一个真正的反斜杠字符。也就是说正则引擎遇到反斜杠会自动把后面一个字符组合起来解释成特殊含义。再看SQL字符串这一层。ODPS SQL的字符串字面量用单引号包围在这个字符串里反斜杠也是转义字符。比如你要在SQL里表示一个单引号字符得写成\。同样的正则里的\d如果直接写进SQL字符串变成\dSQL解析器会先处理掉这个反斜杠把它变成d等传到正则引擎那里就只剩下一个光秃秃的字母d了。所以结论很简单正则里本来只需要一个反斜杠的地方放进ODPS SQL字符串里必须写成两个反斜杠。要让正则引擎看到\dSQL里就要写\\d。3.2 常用字符转义对照表我把日常最常用的几种转义情况整理成了一个表大家可以存着备用。左侧是正则引擎实际看到的表达式右侧是ODPS SQL里的写法想匹配的内容正则原始写法ODPS SQL里的写法数字0-9\d\d字母、数字、下划线\w\w空白字符空格、制表符等\s\s英文句点..\.一个真正的反斜杠字符\\\中括号[[\[匹配中文汉字\p{Han}\p{Han}注意最后一行\p{Han}是Java正则提供的Unicode属性写法专门匹配中文汉字这个在ODPS里是支持的非常实用。我经常用它来做中文相关的清洗。3.3 一个真实的排查过程有次我写了一个线上任务要从备注字段里提取金额正则初始写的是\d\.\d{2}拿到ODPS SQL里直接改成regexp_extract(remark, \\d\\.\\d{2}, 1)结果跑出来全是NULL。我在本地用Python正则测试同样表达式能正常提取到。问题显然出在SQL和本地环境的差异上。当时我做了几步排查。第一步是怀疑数据本身取了几十条样本看确认备注里确实有金额。第二步是怀疑转义我把\\d改成\\d的十六进制形式肉眼对比意识到SQL字符串里的\\在传给正则引擎时缩写成了一个\实际表达式变成了\d\.\d{2}这本身没问题。那问题出在哪第三步我打印SQL最终执行时的表达式发现问题出在客户端工具和ODPS服务端中间又多了一层转义导致服务端收到的字符串已经面目全非。最后我在表达式里把每个反斜杠再加一倍用\\\\d\\\\.\\\\d{2}才真正跑通。这个故事说明一个经验在正式任务里写正则前务必先用一个小的SELECT语句加少量数据做好验证。比如SELECT regexp_extract(商品价格是12.30元, \\d\\.\\d{2}, 1);这条查询如果能返回12.30说明你的转义层数对了再把这个表达式复制到正式任务里。如果返回NULL优先怀疑转义层数而不是数据问题。4. 拿得出手的实战模板从手机号清洗到URL拆解这一章给几个可以直接抄的实战模板。每个模板我都会写下完整SQL和正则含义解释你在自己的业务里改一改字段名就能用。4.1 手机号校验与脱敏这个在用户运营和营销场景里非常常见。上游导入的号码可能有各种脏格式先用regexp_like筛选合法手机号再做脱敏输出SELECT phone, regexp_like(phone, ^1[3-9]\\d{9}$) AS is_valid_phone, CASE WHEN regexp_like(phone, ^1[3-9]\\d{9}$) THEN regexp_replace(phone, (\\d{3})\\d{4}(\\d{4}), \\1****\\2) ELSE phone END AS masked_phone FROM user_profile;这里正则^1[3-9]\\d{9}$已经用^和$锁死了首尾所以不会出现包含匹配的误判。脱敏时\\d{3}和\\d{4}分别锁定前三位和后四位。如果号码不是纯11位数字比如带了分隔符或者空格建议先做一层清洗把非数字字符去掉再校验。4.2 身份证号提取出生日期和性别身份证号18位里藏着出生日期和性别从业务库里导出的用户表经常需要从证件号里拆出这些信息。正则写法如下SELECT id_no, regexp_extract(id_no, ^(\\d{6})(\\d{4})(\\d{2})(\\d{2})(\\d{3})(\\d|X)$, 2) AS birth_year, regexp_extract(id_no, ^(\\d{6})(\\d{4})(\\d{2})(\\d{2})(\\d{3})(\\d|X)$, 3) AS birth_month, regexp_extract(id_no, ^(\\d{6})(\\d{4})(\\d{2})(\\d{2})(\\d{3})(\\d|X)$, 4) AS birth_day, CASE WHEN CAST(regexp_extract(id_no, ^(\\d{6})(\\d{4})(\\d{2})(\\d{2})(\\d{3})(\\d|X)$, 5) AS BIGINT) % 2 1 THEN 男 ELSE 女 END AS gender FROM user_table;正则分了6个捕获组前6位地区码、7-10位出生年、11-12位出生月、13-14位出生日、15-17位顺序码、最后1位校验码。性别由第17位也就是顺序码的最后一位的奇偶决定。注意这里复用同一个正则提取了多次虽然可读性变差了但避免引入UDF在SQL里属于常规操作。如果你们有自定义函数能力封装成一个解析函数会更优雅。4.3 URL解析域名、路径和query参数日志分析里经常会跟URL打交道。我需要从访客点击的URL里拆出域名、路径和指定的query参数比如搜索引擎关键词SELECT url, regexp_extract(url, https?://([^/]), 1) AS domain, regexp_extract(url, https?://[^/](/[^?]*), 1) AS path, regexp_extract(url, [?]keyword([^]), 1) AS search_keyword FROM click_log;第二行(/[^?]*)匹配域名后面的路径部分以[^?]结尾表示遇到问号就停不会把query参数吞进去。第三行是query参数解析的关键写法[?]keyword可以同时兼容?keyword和keyword两种情况捕获组([^])匹配参数值遇到就停止。这个写法是我日常用得非常多的通用模板。注意URL里的中文参数一般是URL编码后的形式比如%E6%B5%8B%E8%AF%95直接提取出来是编码字符串。如果业务需要中文得再包一层URL解码函数ODPS里通常用url_decode。4.4 日志结构化一行日志拆出IP、状态码和耗时服务端日志经常是把一堆字段拼在一行里分析之前要先结构化。拿最常见的nginx日志举例SELECT log_line, regexp_extract(log_line, ^([\\d\\.]) , 1) AS client_ip, regexp_extract(log_line, (\\d{3}) , 1) AS status_code, regexp_extract(log_line, (\\d)ms, 1) AS cost_ms FROM access_log;第一行^([\\d\\.])匹配行首的IP用字符类而不是.*避免匹配过宽。第二行 (\\d{3})匹配状态码前面有一个双引号和空格这个上下文锚点能帮定位。第三行(\\d)ms匹配耗时ms结尾让正则精确圈定数字范围。日志结构一变正则也得跟着变。所以写日志解析脚本时最好先取几条真实日志样本确认字段之间的分隔符是空格还是制表符再确定正则的锚点。4.5 高危字符清洗把非内容性字符统一处理这个模板适合做文本类数据的标准化。比如文章标题、商品名里混了各种符号想要只保留中文、字母、数字其他全部替换成空格SELECT title, regexp_replace(title, [^\\p{Han}a-zA-Z0-9], ) AS clean_title FROM article_table;这里\\p{Han}匹配所有CJK统一表意文字a-zA-Z0-9匹配英文和数字取反字符集[^...]匹配所有不属于这些范围的字符。注意如果上游标题里有URL链接或者emoji都会被替换成空格这正好是想要的效果。如果想替换成空字符串而不是空格直接把第三个参数改成就行。5. 性能忠告正则用不对会让大查询越来越慢ODPS的数据量动辄几十亿行正则表达式如果写得不好性能影响会被放大到非常明显的程度。这一章讲几个我踩过坑后的经验。5.1 贪婪匹配和灾难性回溯正则里.*是贪婪匹配它会尽可能多地吞字符然后一个一个吐出来尝试匹配后面的部分。在长文本上用复杂贪婪模式可能产生指数级的回溯直接拖垮查询。最典型的反面教材是SELECT regexp_extract(log_line, .*status(\\d), 1) AS status_code FROM access_log;这个表达式的.*会先把整行吞掉然后一个个回退去找status如果行很长性能极差。改成这样就好了SELECT regexp_extract(log_line, status(\\d), 1) AS status_code FROM access_log;没有贪婪前缀正则从字符串开头顺序扫描找到status匹配得更快。另一个常用技巧是用[^x]*代替.*比如提取两个引号之间的内容用([^]*)而不是(.*)。前者遇到下一个引号就停不会回溯后者要一直贪到最后一个引号再回溯。在ODPS这种分布式计算环境里一个小优化放到每行数据上节省的时间非常可观。5.2 能用内置函数解决就别用正则正则虽强但代价比LIKE、SUBSTR这些简单函数高。能用简单函数的地方直接用简单函数就好。比如判断某字段是否以abc开头用LIKE abc%就够了没必要用regexp_like(col, ^abc)。按固定字符分割用内置的split函数也比regexp_split快。提取固定位置的子串直接用substr比写捕获组简洁且不会翻车。我之前接手过一个慢查询同事用正则从头到尾解析一个格式化日期字符串跑了半小时。我改成用substr按位置截取秒级出结果。所以写正向前先问自己一句这个需求是不是非得靠正则才能完成如果答案是否放下正则用简单的。5.3 上线前自检清单最后分享一个我每次写正则前都会过的自检清单算是这些年踩坑踩出来的经验先用在线正则工具验证表达式本身确认能在样本上出正确结果。再把表达式放进SQL字符串逐个数反斜杠的层数确保双重转义没有漏。用一条小数据SELECT语句验证函数返回值而不是直接丢进大任务里跑。检查有没有.*这类贪婪模式能换成[^x]*就换。查询前面尽量用分区过滤或LIKE条件粗筛缩小正则处理的数据量。如果正则特别长、特别复杂在SQL注释里把表达式的原义写清楚方便后面接手的人维护。最后聊一点我自己的习惯。用ODPS SQL写正则做了这么久的数仓开发我最大的改变就是不再追求一个正则搞定所有问题。能用LIKE、SUBSTR这些简单函数解决的坚决不用正则必须用正则的写完先抄到在线工具里验证一遍再经过转义、抽样跑数最后才上生产。正则表达式是ODPS SQL处理文本时最锋利的刀但越是锋利的刀越要小心拿稳。希望这篇整理能帮你少踩几个坑尤其是那个反斜杠的坑。