【Hive SQL】使用正则表达式做数据清洗

正则表达式语法

在操作大数据时，通常需要将某些数据进行清洗后再进行操作，某些不规则数据清洗需要用到正则表达式。正则表达式是一种匹配模式，可以进行字符匹配和位置匹配。

基本语法

语法	解释
英文	A-Za-z
数字	0-9
()	标记一个子表达式的开始和结束的位置，括号内表示一个连续的表达式；如果要匹配小括号，需要用\转义，例如：()
[]	定义匹配的字符范围，例如：[a-zA-Z]表示匹配大小写英文字符
{}	定义匹配范围的长度，例如：{1}表示匹配一次，{1,}表示匹配大于等于1次，{2,3}表示匹配大于等于2小于等于3次
*	匹配子表达式零次或多次
+	匹配子表达式1次或多次
?	匹配子表达式0次或1次
\	转义字符
^	表示非的意思，例如：[^>]* 表示非>的字符可以有0次或多次
\d	匹配一个数字字符，等价于[0-9]
\D	匹配一个非数字字符，等价于[^0-9]
\w	匹配字母、数字、下划线，等价于[A-Za-z0-9_]
\W	匹配非字母、数字、下划线，等价于[^A-Za-z0-9_]
.	匹配除换行符（\n、\r）之外的任何单个字符

字符串函数

MaxCompute SQL中使用字符串函数对指定字符串进行灵活处理，详情可以见：https://help.aliyun.com/document_detail/48973.html?utm_content=g_1000230851&spm=5176.20966629.toubu.3.f2991ddcpxxvD1#title-of8-230-1g4

REGEXP_REPLACE

string regexp_replace(string <source>, string <pattern>, string <replace_string>[, bigint <occurrence>])

• 命令说明

将source字符串中第occurrence次匹配pattern的子串替换成指定字符串replace_string后返回结果字符串。

• 参数说明

  source：必填。STRING类型，待替换的字符串。
  pattern：必填。STRING类型常量或正则表达式。待匹配的模型。更多正则表达式编写规范，请参见正则表达式规范。pattern为空串时返回报错。
  replace_string：必填。STRING类型，将匹配pattern的字符串替换后的字符串。
  occurrence：可选。BIGINT类型常量，必须大于等于0，表示将第occurrence次匹配的字符串替换为replace_string，为0时表示替换所有匹配的子串。为其他类型或小于0时，返回报错。默认值为0。

• 实例说明

  在做埋点访客量统计时，url后面有单号的需要做清洗，将单号去除，只留下url，例如
  www.baidu.com/query/OO99238842934/RR99382333，清洗后的数据为：www.baidu.com/query
  url = ‘www.baidu.com/query/OO99238842934/RR99382333’

regexp_replace(url,'([\/]{1}[A-Z]{2,}[0-9]{5,})*', '')

正则表达式中的()很重要说明括号内的是一个完整需要匹配的子表达式，如果没有小括号会认为需要匹配url中所有的/、大写字母和数字

Hive SQL

SELECT
			dt,
			url,
			(case
				when site_id in ('110077') then regexp_replace(url,'([\/]{1}[A-Z]{2,}[0-9]{5,})*', '')
			    else url
			end) as url_cleaned
FROM url_info;