使用正则表达式截取金额信息
1. 使用MRS Hive
Apahce hive没有regexp_extract_all这个函数,但是mrs hive有,可以测试使用这个函数,网址为:https://support.huaweicloud.com/cmpntguide-lts-mrs/mrs_01_24675.html
官方说明如下
2. Apache版hive自定义函数实现,效果如下
由于家里环境没有MRS Hive,因此使用Apache hive实现这个函数功能,MRS Hive如果可用则不用看此模块
2.1 自定义代码:
Javapackage com.szc;
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* @title: RegexpExtractAll
* @Author Tian
* @Date: 2022/12/18 11:56
* @Version 1.0
*/
public class RegexpExtractAll extends GenericUDTF {
private ArrayList<String> outList = new ArrayList<>();
@Override
public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
//1.定义输出数据的列名和类型
List<String> fieldNames = new ArrayList<>();
List<ObjectInspector> fieldOIs = new ArrayList<>();
//2.添加输出数据的列名和类型
fieldNames.add("lineToWord");
fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs);
}
@Override
public void process(Object[] objects) throws HiveException {
//1.获取字段值
String text = objects[0].toString();
//2.获取正则表达式
String patten = objects[1].toString();
//3.匹配到所有数据
Pattern p = Pattern.compile(patten);
//创建一个匹配器,匹配给定的输入与此模式。
Matcher m = p.matcher(text);
while (m.find()) {
outList.clear();
outList.add(m.group());
forward(outList);
}
}
@Override
public void close() throws HiveException {
}
}
2.2 打包
[hive-udtf-1.0-SNAPSHOT.jar]
2.3 部署
SQL-- 1.上传代码到hdfs(shell执行)
# hdfs dfs -put hive-udtf-1.0-SNAPSHOT.jar /udffunction
-- 2.创建函数关联(sql执行)
create function regexp_extract_all as 'com.szc.RegexpExtractAll' using jar 'hdfs:udffunction/hive-udtf-1.0-SNAPSHOT.jar'
2.4 使用
select t2.id,t2.name ,CONCAT_WS(",",COLLECT_SET(t1.num)) from (
select id,name, num from student2
lateral view regexp_extract_all(name, '(\\d+\\.?\\d+)元|(\\d+\\.?\\d+)万元|(\\d+\\.?\\d+)万') num as num
) t1 right join student2 t2 on t1.id = t2.id group by t2.id,t2.name