第一部分:产生背景
import org.apache.hadoop.hive.ql.exec.UDF;
public class HiveUtfTest extends UDF {
public String evaluate(String str) {
try {
return "HelloWorld " + str;
} catch (Exception e) {
return null;
}
}
}
产生背景
•为了满足客户个性化的需求,Hive被设计成一个很开放的系统,很多内容都支持用户定制,包括:
•文件格式:Text File,Sequence File
•内存中的数据格式: Java Integer/String, Hadoop IntWritable/Text
•用户提供的 map/reduce 脚本:不管什么语言,利用 stdin/stdout 传输数据
•用户自定义函数
自定义函数
•虽然Hive提供了很多函数,但是有些还是难以满足我们的需求。因此Hive提供了自定义函数开发
•自定义函数包括三种UDF、UADF、UDTF
•UDF(User-Defined-Function)
•UDAF(User- Defined Aggregation Funcation)
•UDTF(User-Defined Table-Generating Functions) 用来解决 输入一行输出多行(On-to-many maping) 的需求。
HIVE中使用定义的函数的三种方式
•在HIVE会话中add 自定义函数的jar文件,然后创建function,继而使用函数
•在进入HIVE会话之前先自动执行创建function,不用用户手工创建
•把自定义的函数写到系统函数中,使之成为HIVE的一个默认函数,这样就不需要create temporary function
第二部分:UDF
UDF用法
•UDF(User-Defined-Function)
•UDF函数可以直接应用于select语句,对查询结构做格式化处理后,再输出内容
•编写UDF函数的时候需要注意一下几点
•自定义UDF需要继承org.apache.hadoop.hive.ql.UDF
•需要实现evaluate函数
•evaluate函数支持重载
•UDF只能实现一进一出的操作,如果需要实现多进一出,则需要实现UDAF
UDF用法代码示例
package com.hive;
import org.apache.hadoop.hive.ql.exec.UDF;
public class HiveUtfTest extends UDF {
public String evaluate(String str) {
try {
return "HelloWorld " + str;
} catch (Exception e) {
return null;
}
}
}
开发步骤
•开发代码
•把程序打包放到目标机器上去(HDFS上),可以使用eclipse,在JAVA文件上右键,EXPORT——》JAR FILE来生成JAR文件
•进入hive客户端
•添加jar包:hive>add jar hdfs://namenode:8020/user/hive/warehouse/test/add_function.jar;(
需要加上前面的全路径,否则找不到该JAR文件)
•创建临时函数:hive>CREATE TEMPORARY FUNCTION my_add AS 'com.hive.HiveUtfTest‘
•查询HQL语句:
•SELECT my_add (8, 9) FROM scores;
•SELECT my_add (scores.math, scores.art) FROM scores;
•销毁临时函数:hive> DROP TEMPORARY FUNCTION my_add ;
•细节
•在使用UDF的时候,会自动进行类型转换,例如:
SELECT my_add (8,9.1) FROM scores;
SELECT my_add (8,9.1) FROM scores;
•结果是17.1,UDF将类型为Int的参数转化成double。类型的饮食转换是通过UDFResolver来进行控制的
---------------------------------------------------------------------------------------------
下面使用一个真实的例子来学习UDF
有一张很大的表:TRLOG
该表大概有2T左右
TRLOG:
CREATE TABLE TRLOG
(PLATFORM string,
USER_ID int,
CLICK_TIME string,
CLICK_URL string)
row format delimited
fields terminated by '\t';
数据:
PLATFORM USER_ID CLICK_TIME CLICK_URL
WEB 12332321 2013-03-21 13:48:31.324 /home/
WEB 12332321 2013-03-21 13:48:32.954 /selectcat/er/
WEB 12332321 2013-03-21 13:48:46.365 /er/viewad/12.html
WEB 12332321 2013-03-21 13:48:53.651 /er/viewad/13.html
WEB 12332321 2013-03-21 13:49:13.435 /er/viewad/24.html
WEB 12332321 2013-03-21 13:49:35.876 /selectcat/che/
WEB 12332321 2013-03-21 13:49:56.398 /che/viewad/93.html
WEB 12332321 2013-03-21 13:50:03.143 /che/viewad/10.html
WEB 12332321 2013-03-21 13:50:34.265 /home/
WAP 32483923 2013-03-21 23:58:41.123 /m/home/
WAP 32483923 2013-03-21 23:59:16.123 /m/selectcat/fang/
WAP 32483923 2013-03-21 23:59:45.123 /m/fang/33.html
WAP 32483923 2013-03-22 00:00:23.984 /m/fang/54.html
WAP 32483923 2013-03-22 00:00:54.043 /m/selectcat/er/
WAP 32483923 2013-03-22 00:01:16.576 /m/er/49.html
…… …… …… ……
需要把上述数据处理为如下结构的表ALLOG:
CREATE TABLE ALLOG
(PLATFORM string,
USER_ID int,
SEQ int,
FROM_URL string,
TO_URL string)
row format delimited
fields terminated by '\t';
整理后的数据结构:
PLATFORM USER_ID SEQ FROM_URL TO_URL
WEB 12332321 1 NULL /home/
WEB 12332321 2 /home/ /selectcat/er/
WEB 12332321 3 /selectcat/er/ /er/viewad/12.html
WEB 12332321 4 /er/viewad/12.html /er/viewad/13.html
WEB 12332321 5 /er/viewad/13.html /er/viewad/24.html
WEB 12332321 6 /er/viewad/24.html /selectcat/che/
WEB 12332321 7 /selectcat/che/ /che/viewad/93.html
WEB 12332321 8 /che/viewad/93.html /che/viewad/10.html
WEB 12332321 9 /che/viewad/10.html /home/
WAP 32483923 1 NULL /m/home/
WAP 32483923 2 /m/home/ /m/selectcat/fang/
WAP 32483923 3 /m/selectcat/fang/ /m/fang/33.html
WAP 32483923 4 /m/fang/33.html /m/fang/54.html
WAP 32483923 5 /m/fang/54.html /m/selectcat/er/
WAP 32483923 6 /m/selectcat/er/ /m/er/49.html
…… …… …… ……
PLATFORM和USER_ID还是代表平台和用户ID;SEQ字段代表用户按时间排序后的访问顺序,FROM_URL和TO_URL分别代表用户从哪一页跳转到哪一页。对于某个平台上某个用户的第一条访问记录,其FROM_URL是NULL(空值)。
面试官说需要用两种办法做出来:
1、实现一个能加速上述处理过程的Hive Generic UDF,并给出使用此UDF实现ETL过程的Hive SQL
该函数的主要功能是计算该用户的出现顺序数,因为前提是文件已经按照用户归类到一起并且是按照时间排序的了。。。
SELECT t1.platform,t1.user_id,t1.seq,t2.click_url FROM_URL,t1.click_url TO_URL FROM
(SELECT platform,user_id,click_time,click_url,count(1) seq FROM (SELECT a.*,b.click_time click_time1,b.click_url click_url2 FROM trlog a left outer join trlog b on a.user_id = b.user_id)t WHERE click_time>=click_time1 GROUP BY platform,user_id,click_time,click_url)t1
LEFT OUTER JOIN
(SELECT platform,user_id,click_time,click_url,count(1) seq FROM (SELECT a.*,b.click_time click_time1,b.click_url click_url2 FROM trlog a left outer join trlog b on a.user_id = b.user_id)t WHERE click_time>=click_time1 GROUP BY platform,user_id,click_time,click_url )t2
on t1.user_id = t2.user_id and t1.seq = t2.seq + 1;
---------------------------------------------------------------------------------------------
下面使用一个真实的例子来学习UDF
有一张很大的表:TRLOG
该表大概有2T左右
TRLOG:
CREATE TABLE TRLOG
(PLATFORM string,
USER_ID int,
CLICK_TIME string,
CLICK_URL string)
row format delimited
fields terminated by '\t';
数据:
PLATFORM USER_ID CLICK_TIME CLICK_URL
WEB 12332321 2013-03-21 13:48:31.324 /home/
WEB 12332321 2013-03-21 13:48:32.954 /selectcat/er/
WEB 12332321 2013-03-21 13:48:46.365 /er/viewad/12.html
WEB 12332321 2013-03-21 13:48:53.651 /er/viewad/13.html
WEB 12332321 2013-03-21 13:49:13.435 /er/viewad/24.html
WEB 12332321 2013-03-21 13:49:35.876 /selectcat/che/
WEB 12332321 2013-03-21 13:49:56.398 /che/viewad/93.html
WEB 12332321 2013-03-21 13:50:03.143 /che/viewad/10.html
WEB 12332321 2013-03-21 13:50:34.265 /home/
WAP 32483923 2013-03-21 23:58:41.123 /m/home/
WAP 32483923 2013-03-21 23:59:16.123 /m/selectcat/fang/
WAP 32483923 2013-03-21 23:59:45.123 /m/fang/33.html
WAP 32483923 2013-03-22 00:00:23.984 /m/fang/54.html
WAP 32483923 2013-03-22 00:00:54.043 /m/selectcat/er/
WAP 32483923 2013-03-22 00:01:16.576 /m/er/49.html
…… …… …… ……
需要把上述数据处理为如下结构的表ALLOG:
CREATE TABLE ALLOG
(PLATFORM string,
USER_ID int,
SEQ int,
FROM_URL string,
TO_URL string)
row format delimited
fields terminated by '\t';
整理后的数据结构:
PLATFORM USER_ID SEQ FROM_URL TO_URL
WEB 12332321 1 NULL /home/
WEB 12332321 2 /home/ /selectcat/er/
WEB 12332321 3 /selectcat/er/ /er/viewad/12.html
WEB 12332321 4 /er/viewad/12.html /er/viewad/13.html
WEB 12332321 5 /er/viewad/13.html /er/viewad/24.html
WEB 12332321 6 /er/viewad/24.html /selectcat/che/
WEB 12332321 7 /selectcat/che/ /che/viewad/93.html
WEB 12332321 8 /che/viewad/93.html /che/viewad/10.html
WEB 12332321 9 /che/viewad/10.html /home/
WAP 32483923 1 NULL /m/home/
WAP 32483923 2 /m/home/ /m/selectcat/fang/
WAP 32483923 3 /m/selectcat/fang/ /m/fang/33.html
WAP 32483923 4 /m/fang/33.html /m/fang/54.html
WAP 32483923 5 /m/fang/54.html /m/selectcat/er/
WAP 32483923 6 /m/selectcat/er/ /m/er/49.html
…… …… …… ……
PLATFORM和USER_ID还是代表平台和用户ID;SEQ字段代表用户按时间排序后的访问顺序,FROM_URL和TO_URL分别代表用户从哪一页跳转到哪一页。对于某个平台上某个用户的第一条访问记录,其FROM_URL是NULL(空值)。
面试官说需要用两种办法做出来:
1、实现一个能加速上述处理过程的Hive Generic UDF,并给出使用此UDF实现ETL过程的Hive SQL
2、实现基于纯Hive SQL的ETL过程,从TRLOG表生成ALLOG表;(结果是一套SQL)
答案:
1.
UDF
- package org.apache.hadoop.hive.udf;
- public class RowNumber extends org.apache.hadoop.hive.ql.exec.UDF {
- private static int MAX_VALUE = 50;
- private static String comparedColumn[] = new String[MAX_VALUE];
- private static int rowNum = 1;
- public int evaluate(Object... args) {
- String columnValue[] = new String[args.length];
- for (int i = 0; i < args.length; i++)
- columnValue[i] = args[i].toString();
- if (rowNum == 1)
- {
- for (int i = 0; i < columnValue.length; i++)
- comparedColumn[i] = columnValue[i];
- }
- for (int i = 0; i < columnValue.length; i++)
- {
- if (!comparedColumn[i].equals(columnValue[i]))
- {
- for (int j = 0; j < columnValue.length; j++)
- {
- comparedColumn[j] = columnValue[j];
- }
- rowNum = 1;
- return rowNum++;
- }
- }
- return rowNum++;
- }
- public static void main(String[] args) {
- RowNumber aRowNumber = new RowNumber();
- System.out.println(aRowNumber.evaluate("12332321"));
- System.out.println(aRowNumber.evaluate("12332321"));
- System.out.println(aRowNumber.evaluate("12332321"));
- System.out.println(aRowNumber.evaluate("12332321"));
- System.out.println(aRowNumber.evaluate("12332321"));
- }
- }
该函数的主要功能是计算该用户的出现顺序数,因为前提是文件已经按照用户归类到一起并且是按照时间排序的了。。。
INSERT OVERWRITE TABLE ALLOG
SELECT t1.platform,t1.user_id,RowNumber(t1.user_id) as seq,t2.click_url as FROM_URL,t1.click_url as TO_URL FROM
(select *,RowNumber(user_id) as seq from trlog) t1
LEFT OUTER JOIN
(select *,RowNumber(user_id) as seq from trlog) t2
on t1.user_id = t2.user_id and t1.seq=t2.seq+1;
2.
INSERT OVERWRITE TABLE ALLOGSELECT t1.platform,t1.user_id,t1.seq,t2.click_url FROM_URL,t1.click_url TO_URL FROM
(SELECT platform,user_id,click_time,click_url,count(1) seq FROM (SELECT a.*,b.click_time click_time1,b.click_url click_url2 FROM trlog a left outer join trlog b on a.user_id = b.user_id)t WHERE click_time>=click_time1 GROUP BY platform,user_id,click_time,click_url)t1
LEFT OUTER JOIN
(SELECT platform,user_id,click_time,click_url,count(1) seq FROM (SELECT a.*,b.click_time click_time1,b.click_url click_url2 FROM trlog a left outer join trlog b on a.user_id = b.user_id)t WHERE click_time>=click_time1 GROUP BY platform,user_id,click_time,click_url )t2
on t1.user_id = t2.user_id and t1.seq = t2.seq + 1;