hive的函数

hive的函数
按照类型
udf 一对一
udaf 多对一
udtf 一对多 explode炸裂

按照来源
内置函数
显示内置函数：show functions
查看函数详细信息：desc function 函数名
查看函数详细的描述信息：desc function extended 函数名

	常用的内置函数：
		集合函数（收集函数）：
		map,array
		select array_contains(array(1,2,3,4),'3');
		数值处理：
		select round(3.1315826,2); 将这个小数保留两位，四舍五入
			向上取余和向下取余
			floor(x) 取比x小的最大整数
			ceil(x) 取比x大的最小整数
		字符串函数：
		substr,substring,substring_index//这个类似split
		split//按哪个字来切分字符串
		concat//字符串合并
		concat_ws//参数是分隔符，后面的参数可以是字符串，可以以是字符串数组，组合到一起
		*处理null值的非常实用*
		nvl 第一个参数是你需要找的值，第二个是找不到给的默认值
		字符串查找：
		instr('Facebook','boo') --->找到就返回位置5，找不到返回0
		upper
		lower
		表函数：一个变多个
		explode
		将数组元素分到不同的行，或者将map元素分到不同的行和列
		所以参数只能是数组或者是map
		一个元素为一行
		所以数组炸出来只有一列，map有两列k-v
		select name,hb.hobby from test_array lateral view
		explode(hobby) hb as hobby;
					炸裂出来的表别名  炸裂出来的列别名
		lateral view : 横向视图
		当普通字段和炸裂函数一起查询的时候，用横向视图保存炸裂结果，来进行查询
		map的同理：name 是明星名字,piaofang 是电影名，票房
		select name,pf.dianying,pf.piaofang from test_map lateral view 
		explode(piaofang) pf as dianying,piaofang;//这个我没验证啊

		select name,pf.* form test_map lateral view 
		explode(paiofang) pf;




		日期函数：
		to_date 返回日期
		date_add 第一个参数是开始日期，第二参数是需要增加的天数
		date_sub 同上，是日期减少
		day 返回当前日期的天
		month 返回当前日期的月份
		year 返回当前日期的年
		weekofyear 返回当前日期是这一年的第几周
		//这个必须掌握啊，就是时间戳转日期
		from_unixtime 第一个参数是系统时间戳，第二参数是时间格式
		select from_unixtime(223344343,'yyyy-MM-dd HH:mm:ss');
		类型转换函数：
		cast(原始数据 as 需要转换的类型)
		cast(age as bigint)



自定义函数
	public class MyUDF extends UDF{
		//重写evaluate的方法，而且必须是public
		//返回值不能使void，否则没意义
		private int evaluate(int a,int b,int c) {
			return a+b+c;

		}
	打成jar包上传
	jar包添加到hive的classpath下
	hive>add jar /home/mading/xxx.jar
	检查是否添加成功
	list jar;
	list jars;
	创建临时函数进行关联jar包
	create temporary function 函数名 as '类的全限定名';

	然后就可以使用自定义的函数了
	临时函数作用域，只对当前客户端起作用，客户端退出，函数就注销
	创建永久函数需要修改源代码

知识点：json
json就是有一定格式的字符串 map+array
解析方式：
1.使用自定义udf
2.使用内置udf get_json_object(‘json_text’,‘path’)
第一个参数是需要解析的json串，第二个参数是路径

	怎么来找json字符串的路径：
	可以想成linux文件系统目录，对于json来说
	最外面的那层就是跟目录
	$ 根目录
	. 子节点
	[] 取数组元素，数组通过下标取
	* 代表所有的

	$[0].moive
	$.xAxis[0].date[2]


建表：
先建一个原始表，然后再做关联
create table json_line(line string);
加载数据：
load data local inpath '/' into table json_line;
解析json另存为一个表：
create table jsonresult as 
select 
get_json_object(t.line,'$.moive') movie, //给个别名，当做新表的字段
get_json_object(t.line,'$.rate') rate,
get_json_object(t.line,'$.timestmp') timestmp,
get_json_object(t.line,'$.uid') uid,
from json_line t;

json解析出来的都是字符串类型的，所以这个表里的数据都是字符串类型的

可以以先建好一个表，再把解析好的数据插入也可以
先建一个表：
create table json_result(movie int,rate int,time bigint,uid int);
单重数据插入：
insert into table json_result 
select 
get_json_object(t.line,'$.moive') movie, //给个别名，当做新表的字段
get_json_object(t.line,'$.rate') rate,
get_json_object(t.line,'$.timestmp') timestmp,
get_json_object(t.line,'$.uid') uid,
from json_line t;

本来是string，自动转为int类型了

多字节分隔符：
多字节就是超过一个字节的分隔符，hive中只支持单字节的分隔符
比如说：
|| :: ..
用自定义字段的分隔符来解决
使用RegexSerDe通过正则表达式抽取字段
create table xxx(name,string) 
row format serde 'org.apache.hadoop.hive.serde3.RegexSerde'
with serdeproperties
('input.regex'='(.*)\\|\\|(.*)','output.format.string'='%1$s%2$s')
stored as textfile;
竖线需要转译，::就不需要转译
正则表达式中()代表分组
同理如果数据是这种格式，有三个部分
(.*)::(.*)::(.*)
则
%1$s%2$s%3$s

用到的时候自己改就行

transform的方式：
需求：
统计周一到周日期间哪一天的观影人数最多

可以使用脚本来把时间戳转化为一周的哪一天，也可以用内置函数dayofweek
transform+python的方式
transform就是解析脚本

#!/bin/python    //脚本的执行方式，linux命令 which python 查看自己的Python路径
import sys
import datetime
for line in sys.stdin:
	line = line.strip()
	movie,rate,timestamp,uid = line.split('\t')
	weekday = datetime.datetime.fromtimestamp(float(timestamp)).isoweekday()
	print '\t'.join([movie,rate,str(timestamp),uid]) //相当于concat的两个参数，前一个分隔符，后面是需要连接的数据

写好脚本之后添加
hive>add file /home///;
检查：
list files;
使用这个脚本：
select transform(movie,rate,timestamp,uid) using 'python my.py' as(movie,rate,weekday,uid) from jason_result;
参数是需要解析的字段,后面是要用哪个脚本解析，as是解析出来的字段给别名，也可以不设置

窗口函数(分析函数)：
在整个查询语句中相当于over字句单独的开一个小窗口，在小窗口中进行分组和排序
over字句
over(distribute by/sort by)
over(partition by / order by)
前面是指定分组的，后面是指定每个组中的排序的
不能单独使用，通常情况会和一些聚合函数使用
比如说：
	row_number() 用于添加行号，规则是over字句里面定的
	
	select a.* form(
	select
	department,age row_number() over(distribute by dept sort by age desc) 
	//对over字句的结果添加行号
	form stu) a
	where a.index=1; //每个部门年龄最大的，前两个用index<3,其实相当于limit了

	还可以
	rank() //添加排名可以并列 1 1 1 4 4 6
	dense_rank() 也进行添加排名，不并列，1 1 1 2 2 3都必须跟over字句连用

---

hive drop 删除的表数据能否被恢复
1.外部表原始数据还在
2.内部表可以在hdfs的回收站里恢复
truncate 不能被恢复，因为不进回收站

select department,name from stu01 group by dept order by age desc limit 1;