核心内容摘要
《召唤魅魔竟是妈妈来了第二季》：当“母爱”化身“魅魔”，这次，爸爸的钱包还好吗？
在数据处理场景中经常会遇到中英文混合的字符串比如品牌名Céline思琳、Sergio Rossi塞乔罗西、商品名iPhone苹果手机等。
需要将英文部分和中文部分拆分成独立字段用于后续的品牌分析、分类统计等需求。
Hive作为大数据生态中常用的数据仓库工具拆分中英文的核心是利用正则表达式匹配中英文字符特征。
但实际场景中英文可能含空格、重音符号é/è/ç等中文可能与英文无空格拼接直接用基础正则容易出现拆分不完整、兼容问题。
本文将覆盖全场景解决方案从基础到进阶附完整SQL和测试验证。
核心原理中英文字符正则特征拆分的本质是通过正则匹配中英文的专属字符范围精准提取对应部分核心正则如下中文正则[\\u4e00-\\u9fa5]匹配Unicode编码中的所有简体中文字符覆盖日常中文场景若需兼容繁体字可补充[\\u8000-\\u9fff]。
英文正则基础版[a-zA-Z\\s]匹配大小写字母和空格适配普通英文单词及多单词组合如Sergio Rossi。
英文正则进阶版[^\\u4e00-\\u9fa5]反向匹配排除中文字符适配含重音、特殊符号的英文如Céline、Chloé兼容性最强。
注Hive中正则需使用双反斜杠\\转义单斜杠无法识别。
全场景解决方案附SQL根据英文是否含特殊字符、中英文是否有空格分为3类场景方案从简单到通用按需选择。
场景1基础场景英文无特殊字符中英文无空格示例数据AncientStone古石服饰、Nike耐克、Adidas阿迪达斯。
英文为纯字母组合与中文直接拼接无空格、重音。
解决方案基础正则提取核心用英文基础正则匹配字母中文正则匹配中文适合简单场景。
-- 新建表存储拆分结果推荐数仓分层使用 CREATE TABLE IF NOT EXISTS brand_info_split ( brand_full_name STRING COMMENT 原品牌全称, brand_english STRING COMMENT 品牌英文名称, brand_chinese STRING COMMENT 品牌中文名称 ) STORED AS ORC -- 数仓优选ORC格式压缩比高、查询快 AS SELECT brand_full_name, -- 提取英文匹配开头所有字母trim去除冗余空格 NVL(trim(regexp_extract(brand_full_name, ^([a-zA-Z]),
), 无) AS brand_english, -- 提取中文匹配所有中文字符空值赋值为无 NVL(regexp_extract(brand_full_name, ([\\u4e00-\\u9fa5]),
, 无) AS brand_chinese FROM brand_info; -- 若需更新原表新增字段 ALTER TABLE brand_info ADD COLUMNS ( brand_english STRING COMMENT 品牌英文名称, brand_chinese STRING COMMENT 品牌中文名称 ); UPDATE brand_info SET brand_english NVL(trim(regexp_extract(brand_full_name, ^([a-zA-Z]),
), 无), brand_chinese NVL(regexp_extract(brand_full_name, ([\\u4e00-\\u9fa5]),
, 无) WHERE brand_full_name RLIKE ^[a-zA-Z][\\u4e00-\\u9fa5]$; -- 筛选目标数据提升效率场景2进阶场景英文含空格中英文无空格示例数据Sergio Rossi塞乔罗西、Saint Laurent圣罗兰、Tom Ford汤姆福特。
英文为多单词组合含空格与中文直接拼接。
解决方案正则补充空格匹配在基础英文正则中加入\\s匹配空格确保多单词英文完整提取。
-- 核心SQL仅修改英文提取正则 SELECT brand_full_name, -- 英文正则加入\\s匹配字母空格 NVL(trim(regexp_extract(brand_full_name, ^([a-zA-Z\\s]),
), 无) AS brand_english, NVL(regexp_extract(brand_full_name, ([\\u4e00-\\u9fa5]),
, 无) AS brand_chinese FROM brand_info;场景3复杂场景英文含重音/特殊字符示例数据Céline思琳、Chloé蔻依、Müller穆勒。
英文含重音符号é/è/ü基础正则无法识别易出现“只提取部分英文”如Céline只取到C的问题。
解决方案反向匹配兼容所有特殊字符放弃枚举重音字符改用反向匹配“排除中文字符”所有前置非中文字符均视为英文彻底解决兼容问题推荐作为生产环境首选方案。
-- 最终生产版SQL兼容重音、空格、特殊字符 CREATE TABLE IF NOT EXISTS brand_info_split ( brand_full_name STRING COMMENT 原品牌全称, brand_english STRING COMMENT 品牌英文名称含重音/空格, brand_chinese STRING COMMENT 品牌中文名称 ) STORED AS ORC AS SELECT brand_full_name, -- 反向匹配提取开头所有非中文字符trim去空格空值赋值为无 NVL(trim(regexp_extract(brand_full_name, ^([^\\u4e00-\\u9fa5]),
), 无) AS brand_english, -- 提取中文空值赋值为无 NVL(regexp_extract(brand_full_name, ([\\u4e00-\\u9fa5]),
, 无) AS brand_chinese FROM brand_info; -- 原表更新SQL UPDATE brand_info SET brand_english NVL(trim(regexp_extract(brand_full_name, ^([^\\u4e00-\\u9fa5]),
), 无), brand_chinese NVL(regexp_extract(brand_full_name, ([\\u4e00-\\u9fa5]),
, 无) WHERE brand_full_name RLIKE ^[^\\u4e00-\\u9fa5][\\u4e00-\\u9fa5]$; -- 筛选中英文混合数据
测试验证全场景覆盖用模拟数据测试反向匹配方案验证不同场景下的拆分效果确保精准性。
SELECT brand_full_name, trim(regexp_extract(brand_full_name, ^([^\\u4e00-\\u9fa5]),
) AS brand_english, regexp_extract(brand_full_name, ([\\u4e00-\\u9fa5]),
AS brand_chinese FROM ( -- 模拟全场景测试数据 SELECT Céline思琳 AS brand_full_name UNION ALL SELECT Sergio Rossi塞乔罗西 AS brand_full_name UNION ALL SELECT AncientStone古石服饰 AS brand_full_name UNION ALL SELECT Chloé蔻依 AS brand_full_name UNION ALL SELECT Nike耐克 AS brand_full_name ) t;测试结果100%精准拆分brand_full_namebrand_englishbrand_chineseCéline思琳Céline思琳Sergio Rossi塞乔罗西Sergio Rossi塞乔罗西AncientStone古石服饰AncientStone古石服饰Chloé蔻依Chloé蔻依Nike耐克Nike耐克
避坑指南生产环境必看
正则转义问题Hive中必须使用双反斜杠\\转义如\\u4e00单斜杠\会被识别为无效字符导致正则失效。
空值处理纯英文、纯中文数据拆分后对应字段会为空需用NVL()函数赋值为“无”或空字符串避免后续分析报错。
冗余空格处理英文部分可能因匹配规则出现首尾空格需用trim()函数去除保证字段整洁。
Hive版本兼容部分低版本Hive
x以下不支持\\p{M}重音匹配元字符避免使用该元字符优先选择反向匹配方案。
筛选条件优化更新原表时用RLIKE筛选中英文混合数据避免对纯英文、纯中文数据做无效更新提升SQL执行效率。
五、
总结Hive拆分中英文混合字符串的核心是“正则匹配场景适配”简单场景用基础正则复杂场景含重音、空格用反向匹配后者兼容性最强推荐作为生产环境首选。
实际应用中需结合数据特点选择方案同时做好空值、空格、版本兼容处理确保拆分结果精准、可用。
本文方案覆盖品牌名、商品名等常见业务场景可直接复制修改表名、字段名落地使用。