Hive内置运算函数,自定义函数(UDF)和Transform

  • 时间:
  • 浏览:0

tuzuoquan

l  简单UDF示例

有原始json数据如下:

FIELDS TERMINATED BY '\t';

  AS (movieid, rating, weekday,userid)

9783002268

  line = line.strip()

import datetime

3

前期准备,要把hive的lib包导入到工程中,其中UDF依赖的是hive-exec-1.2.1.jar。也可是我说要把apache-hive-1.2.1-bin\lib中内容都引入到工程中。若用到hadoop中的一些api,请把hadoop的api也引入进去。

movie

  rating INT,

  USING 'python weekday_mapper.py'

  movieid, rating, unixtime,userid = line.split('\t')

OK

还要将数据导入到hive数据仓库中

hive> load data local inpath'/home/tuzq/software/hivedata/dual.txt' into table dual;

timestamp

  print '\t'.join([movieid, rating, str(weekday),userid])

import org.apache.hadoop.io.Text;

UDAF(用户定义聚集函数):接收多个输入数据行,并产生一个多输出数据行。(count,max)

  TRANSFORM (movieid , rate, timestring,uid)

OK

1、先开发一个多java类,继承UDF,并重载evaluate依据

Added resources: [/home/tuzq/software/hivedata/udf.jar]

{"movie":"914","rate":"3","timeStamp":"9783001968","uid":"1"}

add FILE weekday_mapper.py;

FROM t_rating;

  userid INT)

作业:

   }

{"movie":"3408","rate":"4","timeStamp":"9783000275","uid":"1"}

1

import sys

使用示例1:下面这句sql可是我借用了weekday_mapper.py对数据进行了补救.

3、select substr('angelababy',2,3) from dual;

   public Text evaluate(final Text s) {

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

{"movie":"1287","rate":"5","timeStamp":"9783002039","uid":"1"}

rate

内容较多,见《Hive官方文档》

hive>

SELECT

  movieid INT,

hive>

内容较多,见《Hive官方文档》

ROW FORMAT DELIMITED

      returnnew Text(s.toString().toLowerCase());

      if(s == null) {returnnull;}

其中dual.txt后面 可是我一个多空格

l  Json数据解析UDF开发

{"movie":"1197","rate":"3","timeStamp":"9783002268","uid":"1"}

Hive的 TRANSFORM 关键字提供了在SQL中调用自写脚本的功能

CREATE TABLE u_data_new (

Added [/home/tuzq/software/hivedata/udf.jar] to class path

{"movie":"1193","rate":"5","timeStamp":"97830007300","uid":"1"}

1197

适合实现Hive中没法的功能又我想要写UDF的清况

3、将jar包加在到hive的classpath

注:全在hive中完成,都还要用自定义函数

import org.apache.hadoop.hive.ql.exec.UDF;

4、hive> addJAR /home/tuzq/software/hivedata/udf.jar> ;

#!/bin/python

package hiveudf;

{"movie":"2355","rate":"5","timeStamp":"978824291","uid":"1"}

hive> select toLowercase("TUZUOQUAN") from dual;

for line in sys.stdin:

uid

测试各种内置函数的快捷依据:

1、创建一个多dual表

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

5、hive>创建临时函数与开发好的java class关联

  weekday INT,

其中weekday_mapper.py内容如下

}

  weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()

{"movie":"661","rate":"3","timeStamp":"9783002109","uid":"1"}

2、load一个多文件(一行,一个多空格)到dual表

我不管你后面 用好多个表,最终我想得到一个多结果表:

Time taken: 0.122 seconds, Fetched: 1 row(s)

create table dual(id string);

当Hive提供的内置函数无法满足你的业务补救还要时,此时就都还要考虑使用用户自定义函数(UDF:user-defined function)。

5、即可在hql中使用自定义的函数tolowercase ip 

2、打成jar包上传到服务器

Time taken: 0.039 seconds

public class ToLowerCase extends UDF {

hive> create temporary function toLowercase as 'hiveudf.ToLowerCase';

INSERT OVERWRITE TABLE u_data_new