用过ODPS SQL做数据清洗的人基本都绕不开正则表达式这个坎。特别是从MySQL、SQL Server转过来的同学最容易在ODPS里被正则的写法坑到——不是匹配不到数据就是莫名其妙报错再就是跑出来的结果跟本地测的完全不一样。我自己刚接触ODPS那会儿也在这个上面浪费了大把时间。这篇东西我不打算写成一本正经的官方文档就按实际开发里最常碰到的场景把ODPS SQL里正则这几个函数的用法、坑点、性能问题以及能直接抄的案例都整理出来。不管你是刚接手数仓的新手还是被正则调参搞到头疼的老兵读完应该都能少走点弯路。1. ODPS里正则函数全家桶每次匹配到底该用谁ODPS SQL内置的正则相关函数不算多但每个的定位和适用场景差异挺大的。很多人的误区是一上来就抱着regexp_extract不放其实有些场景用别的函数会更省事。1.1 regexp_extract提取子串的万金油regexp_extract是日常用得最多的一个函数签名长这样string regexp_extract(string source, string pattern[, bigint groupid])它做的事情就是从source里匹配pattern然后取出某个分组的内容返回。第三个参数groupid控制取哪个分组这个参数很多初学者容易搞混。需要注意groupid传0表示返回整个匹配到的内容传1、2、3才分别对应第1、第2、第3个括号里的分组。-- 提取手机号码中间四位 select regexp_extract(13812345678, (\\d{3})(\\d{4})(\\d{4}), 2); -- 返回1234还有一点我一直提醒组里的人groupid超出实际分组数时函数返回的是空字符串不是报错也不是NULL。排查数据的时候要注意这个细节不然你以为是数据问题其实是参数传错了。1.2 regexp_replace替换比你想的更灵活regexp_replace的签名是string regexp_replace(string source, string pattern, string replace_string[, bigint occurrence])前三个参数好理解就是匹配、替换。第四个参数occurrence是个很实用的参数表示只替换第几次匹配到的内容。默认传0表示替换所有匹配项如果传的是正整数n就只替换第n次匹配。-- 把手机号中间四位替换成**** select regexp_replace(13812345678, (\\d{3})\\d{4}(\\d{4}), \\1****\\2); -- 返回138****5678这个\\1的用法是个重点。在替换串里\\1、\\2这类写法可以引用前面正则里捕获的分组内容。很多人不知道替换串里也能用反向引用结果写复杂替换逻辑时只能嵌套好几层函数又慢又难维护。1.3 regexp_instr搞定“找位置”的需求regexp_instr返回匹配内容在字符串中的位置函数签名bigint regexp_instr(string source, string pattern[, bigint start_position[, bigint Nth_match]])start_position表示从第几个字符开始找Nth_match表示找第几次匹配。默认值都是1。这个函数适合做前置判断或截取前的定位。-- 找到第一个数字出现的位置 select regexp_instr(abc123def, \\d); -- 返回4不过坦白讲这个函数在ODPS里的使用频率远不如前两个高大部分“找位置”的场景用instr普通字符串定位函数配合substr也能完成。只有位置本身就不固定、必须靠正则特征来定位的时候才建议上regexp_instr。1.4 配套函数补充regexp_count 和 split_part除了上面两个主力函数还有两个经常一起用的。一个是regexp_count直接统计匹配次数bigint regexp_count(string source, string pattern)另一个是split_part按正则拆分字符串后取某一段string split_part(string source, string separator, bigint start[, bigint end])这两个在特定场景里能省不少事。比如判断某个字段里出现了几个数字、按多个分隔符切分字符串都比自己写一堆instrsubstr组合要干净得多。2. ODPS正则和别的数据库不一样的坑转义和字符匹配这节必须单独拿出来讲。ODPS底层走的是Java的正则引擎跟MySQL、SQL Server这类传统数据库有本质区别。JDK的正则语法更接近Perl风格支持\d、\w、(?i)这些写法但与此同时ODPS SQL在自己的字符串解析层再加了一层转义。两层转义叠加简直是新手重灾区。2.1 反斜杠为什么要写两遍甚至四遍在ODPS SQL的字符串字面量里反斜杠本身是转义符。你想在正则里表达一个\d如果只写\dODPS的SQL解析器会尝试去转义字母d大概率报错或者丢字符。正确姿势是写\\d这样SQL层把\\解析成一个反斜杠剩余的d原样保留最终传给正则引擎的才是\d。来几个对照就清楚了-- 错误写法会报错或匹配不到 select regexp_extract(abc123, \d, 0); -- 正确写法 select regexp_extract(abc123, \\d, 0);更极端的情况是匹配反斜杠本身。正则引擎里要匹配一个反斜杠得写成\\而SQL字符串里每个反斜杠又得写成\\所以最终你在SQL里看到的是四个反斜杠\\\\。这个我当年第一次写的时候也愣了一下后来总结成一个土办法先想清楚正则引擎需要什么再对着把每个反斜杠二倍化。2.2 圆点和方括号在ODPS里的表现.是正则里的万能匹配符匹配除了换行以外的任意字符。很多从SQL Server转过来的同学会下意识以为.就是字面意义上的点号结果匹配出来的结果五花八门。-- 想匹配“19.99”里的点号这样写会匹配任意字符 select regexp_extract(19.99, 19.99, 0); -- 返回19.99 但也会错误匹配 19x99 这类脏数据 -- 正确写法转义点号 select regexp_extract(19.99, 19\\.99, 0); -- 注意 SQL 里写的是 两个反斜杠再加点号方括号[...]用来定义字符集这个跟其他数据库一致。[0-9]等价于\\d[a-zA-Z0-9_]等价于\\w。ODPS里中文匹配要特别注意普通[\\u4e00-\\u9fa5]写起来麻烦后面第五章我会给一个实测好用的中文匹配方案。2.3 常见转义对照速查表把ODPS SQL里正则最常见的转义写法整理成一个表建议直接收藏想匹配的内容正则引擎写法ODPS SQL里的写法数字\d\\d非数字\D\\D字母数字下划线\w\\w空白字符\s\\s点号字面\.\\.反斜杠字面\\\\\\竖线或|\|左括号字面\(\\(每次写正则老出错的可以把表贴在工位上。我后来养成的习惯是写完正则先在测试SQL里跑一个简单的select验证再丢到正式任务里别直接改生产逻辑。3. 正则函数和SQL怎么搭配才是实战的最优解单个函数会用了只是第一步。实际数仓开发里正则很少孤零零出现基本都是嵌在case when、where、lateral view里配合使用。怎么组合才高效、可读这节讲几个高频模式。3.1 用case when做正则多分支判断ETL里最常见的场景是一个字段有多种格式每种格式要提取不同的东西提取不到就返回默认值。这时候case when配合regexp_instr或者regexp_count做分支比一堆嵌套的if清晰得多。select case when regexp_instr(remark, ^订单) 1 then 订单类型 when regexp_instr(remark, ^售后) 1 then 售后类型 when regexp_instr(remark, ^物流) 1 then 物流类型 else 其他 end as remark_type from dwd_order_remark_di;regexp_instr返回1就说明从字符串开头匹配上了这个判断比regexp_like类的函数更直接。ODPS没有专门的regexp_like习惯用instr来判断是否匹配逻辑上完全等价。3.2 正则提取多字段一次扫描拿多个指标从同一个字符串里提取多个部分是正则使用的高频场景。ODPS里regexp_extract每次调用都会对字符串做一次正则扫描所以能一次提取多个字段就别写多个regexp_extract。做法是用一个正则模式把要提取的内容都放到分组里然后用不同的groupid去取select regexp_extract(log_line, user(\\w)age(\\d)city(\\w), 1) as user_name, regexp_extract(log_line, user(\\w)age(\\d)city(\\w), 2) as user_age, regexp_extract(log_line, user(\\w)age(\\d)city(\\w), 3) as user_city from access_log;这三个regexp_extract用的是同一个patternODPS的引擎一般能复用已编译的正则对象实际开销没有想象中翻三倍那么大。但如果pattern本身很复杂、日志行又很长建议还是拆成几步处理别一口气把正则在SQL里写到又臭又长。3.3 lateral view配合explode处理一对多正则匹配有时候正则一次能匹配出多个结果比如字符串里内嵌了多个JSON片段每个片段都要单独解析。这时候regexp_count统计个数、split_part配合lateral view展开是业界比较通用的做法。select t.id, split_part(t.json_part, ,, 1) as first_key, split_part(t.json_part, ,, 2) as second_key from ( select id, regexp_replace(json_str, \\}, }) as processed_json from source_table ) t lateral view explode(split(t.processed_json, )) tmp as json_part;当然这个例子是简化版真实场景里往往还需要JSON解析函数二次处理。但这个思路值得借鉴先用正则把文本归一化再用splitexplode做展开比直接在SQL里写循环逻辑ODPS SQL本身也不支持循环要高效得多。4. 正则性能优化为什么你的任务跑得比别人慢正则表达式是出了名的性能杀手。ODPS是分布式计算引擎一个正则的优劣会放大到每个MapTask上一个任务几亿条数据每条都做一次复杂的正则回溯计算量直接翻好多倍。我排查过不少跑得异常慢的SQL最后定位下来正则写得烂的占比相当高。4.1 贪婪匹配导致的回溯爆炸正则默认是贪婪的.*会尽可能多地匹配字符然后一步一步往回退这个往回退的过程就是“回溯”。遇到复杂嵌套和长文本时回溯次数可能指数级增长。典型的问题正则长这样-- 错误示例用.*去匹配中间内容很容易触发大量回溯 select regexp_extract(log_line, start.*end, 0) from log_table;如果log_line非常长.*会先吞掉整行然后倒退找end找不到再继续倒性能极差。改成非贪婪写法.*?后引擎会尽量少匹配找到第一个end就停整体效率好不少-- 优化示例非贪婪匹配 select regexp_extract(log_line, start(.*?)end, 1) from log_table;4.2 能用普通字符串函数就别用正则正则不是万能的。很多简单的提取用substr、instr、split_part就够了执行效率远高于正则。我的习惯是先尝试用普通字符串函数解决实在拿不下来再上正则。一个典型案例是提取固定分隔符的字段比如a_b_c_d结构用split_part直接按_切分完全不需要正则。还有些人喜欢用regexp_replace做字符替换但如果是把全角逗号替换成半角用translate或者replace就够了。4.3 把长文本先截断再正则匹配如果正则要处理的字段特别长比如一整个HTML页面存进了字段里而你的目标只是提取title标签里的内容。这时候直接在原始长文本上跑正则效率很低。可以先instr定位到title的位置再substr截一小段出来最后在短的子串上跑正则。这个优化思路在很多慢SQL优化案例里都管用。select regexp_extract( substr(content, instr(content, title), 200), title(.*?)/title, 1 ) as page_title from web_content_table;先缩小匹配窗口让正则面对的数据量从几万字符降到几百字符执行效率的提升通常是数量级的。4.4 正则函数的几点性能备忘录regexp_count、regexp_instr、regexp_extract这些函数本质上都会做正则编译和匹配能少用就少用。正则pattern尽量写在SQL里常量位置不要从字段里动态拼接pattern。动态pattern导致每行数据都要重新编译正则性能会断崖式下跌。过滤数据时能用where instr(col, 关键词) 0的地方不要写成where regexp_extract(col, 关键词, 0) is not null。ODPS执行引擎对复杂正则的pattern cache有限制同一个SQL里大量不同的正则模式会导致缓存频繁失效也是一笔隐形的开销。5. 实战正则清洗/提取案例集合讲了这么多理论最后拿几个真实的需求来收尾。这些都是我实际在ODPS开发中处理过的数据场景直接复制改参数基本能跑。5.1 手机号脱敏与身份证信息提取-- 手机号脱敏 select regexp_replace(13812345678, (\\d{3})\\d{4}(\\d{4}), \\1****\\2); -- 身份证提取出生日期 select regexp_extract(110101199003077654, (\\d{6})(\\d{4})(\\d{2})(\\d{2})(\\d{3})(\\d|X), 2 || - || 3 || - || 4);第二条的||拼接写法在有些版本里可能要调整更稳妥的做法是分两次提取再拼接避免把pattern写得太复杂。5.2 URL参数解析日志表里经常存了整串URL要提取其中某个参数值select regexp_extract(url, [?]token([^]), 1) as token, regexp_extract(url, [?]from([^]), 1) as from_source from access_log where url like %token%;[^]表示匹配到下一个为止这个写法在解析URL和query string时非常实用建议记下来。5.3 中文匹配与特殊字符清洗ODPS正则引擎支持Unicode但匹配中文建议直接用[\\u4e00-\\u9fa5]-- 提取所有中文字符 select regexp_replace(hello世界123, [^\\u4e00-\\u9fa5], ); -- 返回世界 -- 判断字符串是否全中文 select if(regexp_count(name, [\\u4e00-\\u9fa5]) length(name), 1, 0) as is_all_chinese from user_table;注意第二个写法在name包含全角字符或生僻字时可能不准生产环境建议先抽样验证。5.4 JSON字段里的多层提取ODPS有内置的get_json_object函数但遇到不规则JSON还是会用到正则兜底。比如从key:value;key:value结构中取某个值select regexp_extract( info_str, city\\s*[:]\\s*([^;]), 1 ) as city_name from user_info_table;5.5 多分隔符拆分某些脏数据用混合分隔符比如空格、逗号、全角逗号都可能出现。用正则统一清洗后再拆分select split_part(regexp_replace(raw_col, [,\\s], ,), ,, 1) as part1, split_part(regexp_replace(raw_col, [,\\s], ,), ,, 2) as part2 from raw_table;6. 个人踩坑经验总结写ODPS正则这几年我踩过最深的一个坑就是“本地测试没问题上了集群就变了”。后来才明白ODPS的正则引擎和本地用Python/Java测的引擎在个别边界行为上是有差异的。比如\d在某些版本的ODPS引擎里不只匹配0-9还可能匹配某些Unicode数字字符。涉及到敏感数据匹配时宁可把字符集写死成[0-9]也不要用\d赌引擎行为。另一个经验是正则分组越少越好。每次看到一串超过20个字符、括号嵌套好几层的正则我都建议拆开来写。可读性差不说后续接手维护的人改一处就可能改崩全局。我现在的习惯是先写注释说明要匹配的原始样本再写正则最后写验证SQL。这样三个月后回来看这段代码还能快速想起来当初为什么这么写。最后一点正则再怎么厉害也只是数据处理的最后一公里。上游如果能在采集阶段就把字段规整好比在下游用正则硬解要省事得多。给上游提需求、推动规范化往往比无限提升自己的正则水平更有效。不过现实嘛总有各种历史原因弄出脏数据那就打开ODPS编辑器老老实实写正则吧。