三 Hive 数据处理 自定义函数UDF和Transform

三  Hive 自定义函数UDF和Transform

 

开篇提示:

 快速链接beeline的方式:

./beeline -u jdbc:hive2://hadoop1:10000 -n hadoop

 

1.自定义函数UDF

  当Hive提供的内置函数无法满足你的业务处理需要时,此时就可以考虑使用用户自定义函数(UDF:user-defined function)

  UDF  作用于单个数据行,产生一个数据行作为输出。(数学函数,字符串函数)

2开发实例

  2.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"}
{"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"}
{"movie":"595","rate":"5","timeStamp":"978824268","uid":"1"}
{"movie":"938","rate":"4","timeStamp":"978301752","uid":"1"}

   2.2 创建数据表

create table t_rating (line string)
row format delimited;

  2.3 导入数据

load data local inpath '/home/hadoop/rating.json' into table t_rating;

  2.4 开发UDF程序

package cn.itcast.hive;

import org.apache.hadoop.hive.ql.exec.UDF;
import org.codehaus.jackson.map.ObjectMapper;

/**
 * @author ntjr
 * 解析json数据
 *
 */
public class PaserJson extends UDF {
    private ObjectMapper mapper = new ObjectMapper();

    public String evaluate(String line) {

        try {
            RatingBean ratingBean = mapper.readValue(line, RatingBean.class);
            return ratingBean.toString();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return "";
    }
}

  用于解析t_rating表中每一行的json数据。

package cn.itcast.hive;

public class RatingBean {
    private String movie;
    private String rate;
    private String timeStamp;
    private String uid;

    public String getMovie() {
        return movie;
    }

    public void setMovie(String movie) {
        this.movie = movie;
    }

    public String getRate() {
        return rate;
    }

    public void setRate(String rate) {
        this.rate = rate;
    }

    public String getTimeStamp() {
        return timeStamp;
    }

    public void setTimeStamp(String timeStamp) {
        this.timeStamp = timeStamp;
    }

    public String getUid() {
        return uid;
    }

    public void setUid(String uid) {
        this.uid = uid;
    }

    @Override
    public String toString() {
        return movie + "\t" + rate + "\t" + timeStamp + "\t" + uid;
    }

}

  2.4将udf程序打成jar 导入hive

add JAR /home/hadoop/udf.jar;

  2.5 创建临时函数与开发好的udf进行关联 

create temporary function paseJson as 'cn.itcast.hive.PaserJson';

  2.6 创建完整字段的t_rating02表(用于存放将单列json数据表t_rating转换成多列数据表t_rating02的结果)  

create table t_rating02 as
select split(paseJson(line),'\t')[0] as movieid,
split(paseJson(line),'\t')[1] as rate,
split(paseJson(line),'\t')[2] as timestring,
split(paseJson(line),'\t')[3] as uid
from t_rating;

  至此:完成字段表t_rating02转换完成。

3.利用Transfrom将t_rating02表中的timestring字段转换成周几的形式。

  3.1 t_rating02中的样式:

  

  3.2编写weekday_mapper.py脚本,处理t_rating02表中的timestring字段 

#!/bin/python
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])

  3.3 上传weekday_mapper.py脚本,前提是保证本机装有python 

add FILE weekday_mapper.py;

  3.4 创建新表t_rating_date,保存脚本处理后的数据 

create TABLE t_rating_date as
SELECT
  TRANSFORM (movieid , rate, timestring,uid)
  USING 'python weekday_mapper.py'
  AS (movieid, rating, weekday,userid)
FROM t_rating02;

   3.5查看t_rating_date表

  

  至此将json数据转换成数据表。

 

posted @ 2017-12-11 18:30  IT-執念  阅读(1721)  评论(0编辑  收藏  举报