一、hive为我们提供的函数
hive给我们提供了一些内置函数，比如截取字符串，大小写转换
此处距离substr
1、首先模仿sql建立一个伪表dual
create table dual(id string);
2、准备数据
在本地创建一个文档，dual.txt，内容为一个空格或者空行
3、加载数据到表格
load data local inpath '/root/dual.txt' into table dual;
4、进行测试
0: jdbc:hive2://localhost:10000> select substr('sichuan',1,3) from dual;
5、也可以不建表直接使用：
select substr('sichuan',1,3);
二、自定义函数
1、添加maven依赖
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>1.2.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-metastore -->
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-metastore</artifactId>
<version>1.2.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-common -->
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-common</artifactId>
<version>1.2.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-service -->
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-service</artifactId>
<version>1.2.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-jdbc -->
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>1.2.1</version>
</dependency>
2、大写转小写
1）、创建UpperToLowerCase类，继承UDF，重写evaluate方法
public class UpperToLowerCase extends UDF {
/*
* 重载evaluate
* 访问限制必须是public
*/
public String evaluate(String word) {
String lowerWord = word.toLowerCase();
return lowerWord;
}
}
2)、打包上传到hadoop集群上
此处我的项目名称是hive
3)、将jar包放到hive的classpath下
0: jdbc:hive2://localhost:10000> add jar /root/hive.jar;
4)、创建临时函数，指定完整类名
完整类名：包名加类名
0: jdbc:hive2://localhost:10000> create temporary function tolower as 'com.neusoft.hive.UpperToLowerCase';
5)、使用临时函数
select tolower('HELLO');
3、根据电话号码显示归属地信息
1）、创建工具类
public class PhoneNumParse extends UDF{
static HashMap<String, String> phoneMap = new HashMap<String, String>();
static{
phoneMap.put("136", "beijing");
phoneMap.put("137", "shanghai");
phoneMap.put("138", "shenzhen");
}
public static String evaluate(int phoneNum) {
String num = String.valueOf(phoneNum);
String province = phoneMap.get(num.substring(0, 3));
return province==null?"foreign":province;
}
//测试
public static void main(String[] args) {
String string = evaluate(136666);
System.out.println(string);
}
}
2)、重新打jar包，然后上传到hadoop集群上
此处我的项目名称是hive
3)、将jar包放到hive的classpath下
0: jdbc:hive2://localhost:10000> add jar /root/hive.jar;
4)、创建临时函数，指定完整类名
完整类名：包名加类名
create temporary function getprovince as 'com.scu.hive.PhoneNumParse';
5)、创建本地数据
创建flow.txt将以下数据
[root@mini1 ~]# vi flow.txt;
1367788,1
1367788,10
1377788,80
1377788,97
1387788,98
1387788,99
1387788,100
1555118,99
6)、创建表，然后加载数据
0: jdbc:hive2://localhost:10000> create table flow(phonenum int,flow int)
0: jdbc:hive2://localhost:10000> row format delimited fields terminated by ',';
No rows affected (0.143 seconds)
0: jdbc:hive2://localhost:10000> load data local inpath '/root/flow.txt' into table flow;
7)、查询结果
0: jdbc:hive2://localhost:10000> select phonenum,getprovince(phonenum),flow from flow;
+-----------+-----------+-------+--+
| phonenum | _c1 | flow |
+-----------+-----------+-------+--+
| 1367788 | beijing | 1 |
| 1367788 | beijing | 10 |
| 1377788 | shanghai | 80 |
| 1377788 | shanghai | 97 |
| 1387788 | shenzhen | 98 |
| 1387788 | shenzhen | 99 |
| 1387788 | shenzhen | 100 |
| 1555118 | foreign | 99 |
+-----------+-----------+-------+--+
3、Json数据解析UDF开发
1）、创建数据源文本
内容如下，记住不要有空行以及多余的空格，不然会出现解析错误
{"movie":"1193","rate":"5","timeStamp":"978300760","uid":"1"}
{"movie":"661","rate":"3","timeStamp":"978302109","uid":"1"}
{"movie":"914","rate":"3","timeStamp":"978301968","uid":"1"}
{"movie":"3408","rate":"4","timeStamp":"978300275","uid":"1"}
{"movie":"2355","rate":"5","timeStamp":"978824291","uid":"1"}
{"movie":"1197","rate":"3","timeStamp":"978302268","uid":"1"}
2）、创建表，然后上传数据
创建表：
create table json(line string);
上传数据：
load data local inpath ‘/root/json.txt’ into table json;
2）、与json数据对应的javabean
public class MovieRateBean {
private String movie;
private String rate;//评分
private String timeStamp;
private String uid;
@Override
public String toString() {
return this.movie+"\t"+this.rate+"\t"+this.timeStamp+"\t"+this.uid;
}
// get、set方法
}
3）、java工具类
package com.neusoft.hive;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.TypeReference;
import org.apache.hadoop.hive.ql.exec.UDF;
public class JsonParse extends UDF {
public String evaluate(String jsonStr){
MovieRateBean movieRateBean=JSON.parseObject(jsonStr,new TypeReference<MovieRateBean>(){});
returnmovieRateBean.toString();
}
}
4)、打jar包然后上传到hadoop集群
5)、将jar包添加到hive下的classpath
0: jdbc:hive2://localhost:10000> add jar /root/hive.jar;
5)、将fastjson的jar包添加到hive下的classpath
0: jdbc:hive2://localhost:10000> add jar /root/fastjson-1.1.41.jar;
6)、创建临时函数：
指定类名一定要完整的路径，即包名加类名
0: jdbc:hive2://localhost:10000> create temporary function parsejson as 'com.sneusoft.hive.JsonParse';
7）、执行查询
0: jdbc:hive2://localhost:10000> select parsejson(line) from json limit 10;
+---------------------+--+
| _c0 |
+---------------------+--+
| 1193 5 978300760 1 |
| 661 3 978302109 1 |
| 914 3 978301968 1 |
| 3408 4 978300275 1 |
| 2355 5 978824291 1 |
| 1197 3 978302268 1 |
| 1287 5 978302039 1 |
| 2804 5 978300719 1 |
| 594 4 978302268 1 |
| 919 4 978301368 1 |
8）、显示字段名
从上面的结果可以看出来，数据虽然分开了，但是没有字段名，现在我们通过建表来实现显示字段名
0: jdbc:hive2://localhost:10000> create table movie as
0: jdbc:hive2://localhost:10000> select split(parsejson(line),'\t')[0]as movieid,
0: jdbc:hive2://localhost:10000> split(parsejson(line),'\t')[1] as rate,
0: jdbc:hive2://localhost:10000> split(parsejson(line),'\t')[2] as timestring,
0: jdbc:hive2://localhost:10000> split(parsejson(line),'\t')[3] as uid
0: jdbc:hive2://localhost:10000> from json;
再次执行查询，查看结果：
0: jdbc:hive2://localhost:10000> select * from movie;
+-------------------+----------------+----------------------+---------------+--+
| t_rating.movieid | t_rating.rate | t_rating.timestring | t_rating.uid |
+-------------------+----------------+----------------------+---------------+--+
| 919 | 4 | 978301368 | 1 |
| 594 | 4 | 978302268 | 1 |
| 2804 | 5 | 978300719 | 1 |
| 1287 | 5 | 978302039 | 1 |
| 1197 | 3 | 978302268 | 1 |
| 2355 | 5 | 978824291 | 1 |
| 3408 | 4 | 978300275 | 1 |
| 914 | 3 | 978301968 | 1 |
| 661 | 3 | 978302109 | 1 |
| 1193 | 5 | 978300760 | 1 |
+-------------------+----------------+----------------------+---------------+--+
三、transform关键字的使用
将某一个字段时间戳要改为输出周几，可以不用实现UDF
我们直接使用跟上面创建好的表，将第三个字段改为时间
1、编写python脚本
在本地创建一个Python脚本，名字叫做trans.py
vi trans.py
以下是脚本代码
import sys
import datetime
for line in sys.stdin:
line = line.strip()
movieid, rating, unixtime,userid = line.split('\t')
weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()
print '\t'.join([movieid, rating, str(weekday),userid])
2、使用该脚本
1）、添加该文本到hive
0: jdbc:hive2://localhost:10000> add FILE /root/trans.py;
2）、使用该脚本
0: jdbc:hive2://localhost:10000> select TRANSFORM(movieid,rate,time,userid) USING 'python trans.py' as (mov,rat,tim,uid) from movie;
输出结果：
INFO : Number of reduce tasks is set to 0 since there's no reduce operator
INFO : number of splits:1
INFO : Submitting tokens for job: job_1546821616463_0002
INFO : The url to track the job: http://hadoop4:8088/proxy/application_1546821616463_0002/
INFO : Starting Job = job_1546821616463_0002, Tracking URL = http://hadoop4:8088/proxy/application_1546821616463_0002/
INFO : Kill Command = /opt/modules/app/hadoop/bin/hadoop job -kill job_1546821616463_0002
INFO : Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
INFO : 2019-01-05 00:35:37,847 Stage-1 map = 0%, reduce = 0%
INFO : 2019-01-05 00:35:56,316 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.55 sec
INFO : MapReduce Total cumulative CPU time: 1 seconds 550 msec
INFO : Ended Job = job_1546821616463_0002
+-------+------+------+------+--+
| mov | rat | tim | uid |
+-------+------+------+------+--+
| 1197 | 3 | 1 | 1 |
| 2355 | 5 | 7 | 1 |
| 3408 | 4 | 1 | 1 |
| 914 | 3 | 1 | 1 |
| 661 | 3 | 1 | 1 |
| 1193 | 5 | 1 | 1 |
+-------+------+------+------+--+
6 rows selected (36.201 seconds)
