HIVE函数

show functions;

desc function xxx;

窗口函数

row_number：row_number() over(distribute by dt sort by score desc)        没有并列，相同名次顺序排

rank：有并列，相同名次空位    

dense_rank：有并列，相同名次不空位

lag：向上取第n行的数据

lead：向下取第n行的数据

first_value：first_value(score ignore nulls)

last_value：last_value(score) over(distribute by dt sort by score desc rows between unbounded preceding and unbounded following)

ntile(n)：用于将分组数据按照顺序切分成N片，返回当前切片值。ntile不支持rows between，如果切片不均匀，默认增加第一个切片的分布

cume_dist()：小于等于当前值的行数/分组内总行数。

percent_rank()：分组内当前行的rank值-1/分组内总行数-1

 

 preceding:往前

 following：往后

 current rows：当前行

 unbounded：起点

 unbounded preceding：表示从前面的起点

 unbounded following：表示到后面的终点

时间函数

to_date 下周几的日期

 date_sub(next_day(to_date(CURRENT_TIMESTAMP),'MO'),7) --本周1--

 date_sub(next_day(to_date(CURRENT_TIMESTAMP),'MO'),14) --上周1--

 date_sub(next_day(to_date(CURRENT_TIMESTAMP),'MO'),0) --下周1--

 date_add(next_day(to_date(CURRENT_TIMESTAMP),'MO'),7) --下下周1--

 date_add(next_day('2019-02-10','MO'),-1) --当前这周的周日--

trunc 时间截取

 select trunc(sysdate) from dual --2011-3-18 今天的日期为2011-3-18

 select trunc(sysdate, 'mm') from dual --2011-3-1 返回当月第一天.

 select trunc(sysdate,'yy') from dual --2011-1-1 返回当年第一天

 select trunc(sysdate,'dd') from dual --2011-3-18 返回当前年月日

 select trunc(sysdate,'yyyy') from dual --2011-1-1 返回当年第一天

 select trunc(sysdate,'d') from dual --2011-3-13 (星期天)返回当前星期的第一天

 select trunc(sysdate, 'hh') from dual --2011-3-18 14:00:00 当前时间为14:41

 select trunc(sysdate, 'mi') from dual --2011-3-18 14:41:00 TRUNC()函数没有秒的精确

eg:截取小数

9.select trunc(123.458) from dual --123

10.select trunc(123.458,0) from dual --123

11.select trunc(123.458,1) from dual --123.4

12.select trunc(123.458,-1) from dual --120

13.select trunc(123.458,-4) from dual --0

14.select trunc(123.458,4) from dual --123.458

15.select trunc(123) from dual --123

16.select trunc(123,1) from dual --123

17.select trunc(123,-1) from dual --120

date_format 时间格式化

date_format(dt,'yyyy-MM')

datediff 返回日期差，天

 datediff('2019-03-19','2019-03-10');  -- result: 9

year(time),month(time),day(time),weekofyear(time) 返回年、月、日、第几周

year('2019-03-19'),month('2019-03-19'),day('2019-03-19'),weekofyear('2019-03-

19');

months_between(date1,date2) 返回月份差

select months_between('2019-04-01','2019-02-20'); -- result: 1.38709677

 

字符串函数

concat_ws('|', collect_set(user_id))

concat

Split

split('a,b,c,d' , ',') ==> ["a","b","c","d"]

COALESCE(T v1, T v2, …)

返回参数中的第一个非空值；如果所有值都为 NULL，那么返回NULL。

数值

round() 四舍五入

floor() 向下取整

ceil() 向上取整

组合

行列转换

Lateral view 其实就是用来和像类似explode这种UDTF函数联用的。lateral view 会将UDTF生成的结果放

到一个虚拟表中，然后这个虚拟表会和输入行即每个uid进行join 来达到连接UDTF外的select字段的目的

。

列转行

select uid,tag from t12 lateral view explode(split(tags,",")) a as tag;

行转列

concat_ws group by

易混淆概念

SORT BY/ORDER BY

SORT BY ：局部排序，只保证单个的reduce内有序

ORDER BY ：全部排序，保证所有的reduce中的数据有序

如果reduce个数只有一个，则两者的结果一样

两者通常都和ASC DESC搭配，默认使用升序ASC

distribute by：按照指定的字段对数据进行划分输出到不同的reduce中。

cluster by：除了具有 distribute by 的功能外还兼具 sort by 的功能。

排名函数

row_number()：没有并列，相同名次顺序排

rank()：有并列，相同名次空位

dense_rank()：有并列，相同名次不空位

集合

collect_list

collect_set

自定义函数

编写UDF的方式：

1、继承UDF，重写evaluate()，允许重载。

2、继承genericUDF,重写initlizer()\getdisplay()\evaluate()。
hive的自定义模块：serde、自定义函数、输入输出格式等。

UDF：一对一的输入输出。（常用）

UDAF：多对一的输入输出。

UDTF：用户自定义的表生成函数。

 

 

 

 

添加自定义函数方法

当前session有效

第一种

1、将编写好的 UDF的jar包上传到服务器，并添加到hive的class path中

add jar /root/gp1813Demo-1.0-SNAPSHOT.jar;

2、创建一个自定义的临时函数名：

create temporary function myUpperCase as 'com.qfedu.bigdata.hiveUDF.firstUDF';

3、测试：

dual

create table dual (id string);

insert into dual values(' ');

 

select myUpperCase('gaoyuanyuan') from dual;

 

4、确定没有调用该函数时可以注销函数（小心）

drop temporary function myuppercase;

 

第二种：(相当于临时函数)

1、将编写好的UDF的jar包上传到服务器

2、编写配置脚本

vi ./hive-init

add jar /root/gp1813Demo-1.0-SNAPSHOT.jar;

create temporary function myUpperCase as 'com.qfedu.bigdata.hiveUDF.firstUDF';

 

3、启动hive的时候带上初始化脚本文件

hive -i ./hive-init

 

第三种：（临时函数）

1、将编写好的UDF的jar包上传到服务器

2、在hive的安装目录的bin目录下，创建一个文件，文件名.hiverc：

vi bin/.hiverc

add jar /root/gp1813Demo-1.0-SNAPSHOT.jar;

create temporary function myUpperCase as 'com.qfedu.bigdata.hiveUDF.firstUDF';

 

3、启动：

hive

 

创建永久函数

一

1、将编写好的 UDF的jar包上传到服务器，并将jar包上传到hdfs上，并添加到hive的class path中

hdfs dfs -put /root/gp1813Demo-1.0-SNAPSHOT.jar /hiveUDF/

add jar hdfs://qianfeng/hiveUDF/gp1813Demo-1.0-SNAPSHOT.jar;

2、创建一个自定义的函数名：

create function myUpperCase as 'com.qfedu.bigdata.hiveUDF.firstUDF';

二

编译源码（费劲）

1)将写好的Java文件拷贝到~/install/hive-0.8.1/src/ql/src/java/org/apache/hadoop/hive/ql/udf/

cd ~/install/hive-0.8.1/src/ql/src/java/org/apache/hadoop/hive/ql/udf/

ls -lhgt |head

2)修改~/install/hive-0.8.1/src/ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java，增加import和RegisterUDF

 

import com.meilishuo.hive.udf.UDFIp2Long; //添加import

 

registerUDF("ip2long", UDFIp2Long.class, false); //添加register

3)在~/install/hive-0.8.1/src下运行ant -Dhadoop.version=1.0.1 package

cd ~/install/hive-0.8.1/src

ant -Dhadoop.version=1.0.1 package

4)替换exec的jar包，新生成的包在/hive-0.8.1/src/build/ql目录下,替换链接

cp hive-exec-0.8.1.jar /hadoop/hive/lib/hive-exec-0.8.1.jar.0628

rm hive-exec-0.8.1.jar

ln -s hive-exec-0.8.1.jar.0628 hive-exec-0.8.1.jar

5)重启进行测试