Hive自定义函数与transform的使用

hive是给了我们很多内置函数的，比如转大小写，截取字符串等，具体的都在官方文档里面。但是并不是所有的函数都能满足我们的需求，所以hive提供了给我们自定义函数的功能。
1、至于怎么测试hive为我们提供的函数
因为mysql或者oracle中都可以使用伪表，但是hive不行，所以可以使用以下方法
1）、创建表dual,create table dual(id string)
2）、在本地创建文件dual.data,内容为空格或者空一行
3）、将dual.data文件load到表dual
进行测试，比如：字符串截取

0: jdbc:hive2://localhost:10000> select substr('sichuan',1,3) from dual;+------+--+| _c0  |+------+--+| sic  |+------+--+

当然也可以直接使用 select substr(‘sichuan’,1,3)，但是还是习惯用from dual;

2、自定义内置函数
1）、大写转小写
可以先创建java类继承UDF,重载evaluate方法。

/** * 大写转小写 * @author 12706 */public class UpperToLowerCase extends UDF {    /*     * 重载evaluate     * 访问限制必须是public     */    public String evaluate(String word) {        String lowerWord = word.toLowerCase();        return lowerWord;    }}

打包上传到hadoop集群（打的jar包名字为hive.jar）。

0: jdbc:hive2://localhost:10000> select * from t5;+--------+-----------+--+| t5.id  |  t5.name  |+--------+-----------+--+| 13     | BABY      || 1      | zhangsan  || 2      | lisi      || 3      | wangwu    || 4      | furong    || 5      | fengjie   || 6      | aaa       || 7      | bbb       || 8      | ccc       || 9      | ddd       || 10     | eee       || 11     | fff       || 12     | ggg       |+--------+-----------+--+13 rows selected (0.221 seconds)

将jar包放到hive的classpath下

0: jdbc:hive2://localhost:10000> add jar /root/hive.jar;

创建临时函数，指定完整类名

0: jdbc:hive2://localhost:10000> create temporary function tolower as 'com.scu.hive.UpperToLowerCase';

到这就可以使用自定义临时函数tolower()了，测试t5表中的name输出小写

0: jdbc:hive2://localhost:10000> select id,tolower(name) from t5;+-----+-----------+--+| id  |    _c1    |+-----+-----------+--+| 13  | baby      || 1   | zhangsan  || 2   | lisi      || 3   | wangwu    || 4   | furong    || 5   | fengjie   || 6   | aaa       || 7   | bbb       || 8   | ccc       || 9   | ddd       || 10  | eee       || 11  | fff       || 12  | ggg       |+-----+-----------+--+

2）、根据电话号码显示归属地信息
jave类

/** * 根据电话号码前三位获取归属地 * @author 12706 * */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);    }}

将工程打包上传到linux，注意：如果名字还是跟上面一样，那么需要重新连接hive服务端了，否则jar包是不会覆盖的，建议打的jar包名字别一样

编辑文件vi prov.data
创建表flow(phonenum int,flow int)
将文件load到flow表

[root@mini1 ~]# vi prov.data;1367788,11367788,101377788,801377788,971387788,981387788,991387788,1001555118,99

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/prov.data' into table flow;INFO  : Loading data to table myhive3.flow from file:/root/prov.dataINFO  : Table myhive3.flow stats: [numFiles=1, totalSize=88]No rows affected (0.316 seconds)0: jdbc:hive2://localhost:10000> select * from flow;+----------------+------------+--+| flow.phonenum  | flow.flow  |+----------------+------------+--+| 1367788        | 1          || 1367788        | 10         || 1377788        | 80         || 1377788        | 97         || 1387788        | 98         || 1387788        | 99         || 1387788        | 100        || 1555118        | 99         |+----------------+------------+--+

classpath下加入jar包，创建临时函数，测试

0: jdbc:hive2://localhost:10000> add jar /root/hive.jar;INFO  : Added [/root/hive.jar] to class pathINFO  : Added resources: [/root/hive.jar]No rows affected (0.236 seconds)0: jdbc:hive2://localhost:10000> create temporary function getprovince as 'com.scu.hive.PhoneNumParse';No rows affected (0.038 seconds)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开发
有文件，内容一部分如下，里面都是json串，现在需要将它展示输出到表中，并解析对应为4个字段。

{"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"}

java类

public class JsonParse extends UDF{    //{"movie":"1193","rate":"5","timeStamp":"978300760","uid":"1"}    //输出字符串 1193 5 978300760 1    public static String evaluate(String line){        ObjectMapper objectMapper = new ObjectMapper();        //json串转java对象        String json = "";        try {            MovieRateBean bean = objectMapper.readValue(line,MovieRateBean.class);            json = bean.toString();        } catch (Exception e) {            e.printStackTrace();        }        return json;    }}

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方法}

工程打包上传到linux下。
创建表json
create table json(line string);
将文件导入到json表
load data local inpath ‘/root/json.data’ into table json;

0: jdbc:hive2://localhost:10000> select * from json limit 10;+----------------------------------------------------------------+--+|                           json.line                            |+----------------------------------------------------------------+--+| {"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"}  || {"movie":"1287","rate":"5","timeStamp":"978302039","uid":"1"}  || {"movie":"2804","rate":"5","timeStamp":"978300719","uid":"1"}  || {"movie":"594","rate":"4","timeStamp":"978302268","uid":"1"}   || {"movie":"919","rate":"4","timeStamp":"978301368","uid":"1"}   |+----------------------------------------------------------------+--+

0: jdbc:hive2://localhost:10000> add jar /root/hive3.jar;INFO  : Added [/root/hive3.jar] to class pathINFO  : Added resources: [/root/hive3.jar]No rows affected (0.023 seconds)0: jdbc:hive2://localhost:10000> create temporary function parsejson as 'com.scu.hive.JsonParse';No rows affected (0.07 seconds)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   |+---------------------+--+

到这里发现还有不足的地方，就是没显示字段。可以使用函数来实现重写建表来命名。

0: jdbc:hive2://localhost:10000> create table t_rating as0: 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 limit 10;...0: jdbc:hive2://localhost:10000> select * from t_rating;+-------------------+----------------+----------------------+---------------+--+| 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关键字使用
需求，创建新表，内容与t_rating表一致，但是第三个字段时间戳要改为输出周几。
Hive的 TRANSFORM 关键字提供了在SQL中调用自写脚本的功能
适合实现Hive中没有的功能又不想写UDF的情况。

1、编写python脚本(先看看机器有没有python)，用来将表时间戳转为周几
2、加入编写的py文件
3、创建新表，字段值为t_rating表传入py函数后输出的字段值

[root@mini1 ~]# pythonPython 2.6.6 (r266:84292, Feb 21 2013, 23:54:59) [GCC 4.4.7 20120313 (Red Hat 4.4.7-3)] on linux2Type "help", "copyright", "credits" or "license" for more information.>>>  print 'hello';hello>>> quit()[root@mini1 ~]# vi weekday_mapper.py;#import sysimport datetimefor 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])

切换到hive客户端

0: jdbc:hive2://localhost:10000> add FILE /root/weekday_mapper.py;

0: jdbc:hive2://localhost:10000> create TABLE u_data_new as0: jdbc:hive2://localhost:10000> SELECT0: jdbc:hive2://localhost:10000>   TRANSFORM (movieid, rate, timestring,uid)0: jdbc:hive2://localhost:10000>   USING 'python weekday_mapper.py'0: jdbc:hive2://localhost:10000>   AS (movieid, rate, weekday,uid)0: jdbc:hive2://localhost:10000> FROM t_rating;...0: jdbc:hive2://localhost:10000> select * from u_data_new;+---------------------+------------------+---------------------+-----------------+--+| u_data_new.movieid  | u_data_new.rate  | u_data_new.weekday  | u_data_new.uid  |+---------------------+------------------+---------------------+-----------------+--+| 919                 | 4                | 1                   | 1               || 594                 | 4                | 1                   | 1               || 2804                | 5                | 1                   | 1               || 1287                | 5                | 1                   | 1               || 1197                | 3                | 1                   | 1               || 2355                | 5                | 7                   | 1               || 3408                | 4                | 1                   | 1               || 914                 | 3                | 1                   | 1               || 661                 | 3                | 1                   | 1               || 1193                | 5                | 1                   | 1               |+---------------------+------------------+---------------------+-----------------+--+