Hive进阶3

Hive3

struct：

create table if not exists str1(
name string,
score struct<chinese:int,math:int,english:int>
---注意它的创建和array创建的不同
)
row format delimited fields terminated by '\t'
collection items terminated by ','
;

数据
goudan 60,20,10
mazi 90,30,20
haoge 15,9,20,16
cg 59,151

load data local inpath '/home/hivedata/str' into table str1;
-----查询：
select
*
from str1 s
where s.score.english is not null
;

select
s.name,
size(s.score)
from str1 s
where s.score.english is not null
;

--这是表创建时包含数组属性的创建
create table if not exists arr(
name string,
score array<int>
)
row format delimited fields terminated by ' '
collection items terminated by ','
;

load data local inpath '/home/hivedata/arr' into table arr;

结构体和array的区别：

数组只能是某种类型的元素集合，但是结构体可以任意类型。

arr的函数较为丰富内部函数，strcut较少些

两者取值不一样，arr使用角标取值，而struct使用属性名取值

hive的函数：

内置函数：

rand() 
select split((round(rand()*100,0)),"\\.")[0];
round(double,n) ：四舍误入保留小数点后n位
split(str,spliter)：切割字符串用正则规则
 select split((round(rand()*100,0)),"\\.")[0];
 首先rand出的随机数*100,然后对这个数取小数点后0位即x.0,最后用.切割为["a","0"]取数组中的第0个元素。
substr(str,start,length) 或者 substring()
返回从pos开始且长度为len的str子串或substr(bin，pos[，len]) -返回从pos开始且长度为len的字节数组片段，字符串以1位开始不是0；
 select substr((round(rand()*100,0)),0,2);
--** 45 78 两位整数
 select substr("abcdef",2,3);
--abcdef23
concat(str,str1...)
合并字符串，数字不需要加引号
select concat(1,2,3,"ac");
--123ac
concat_ws(seprater,str,str1..)  : 输入类型一定是字符串或者数组，合并，中间用seprater这个标识符隔开；
select concat_ws("|","123","ac");
--123|ac
cast(col as datatype)
--类型转换，转int时默认向下取整整数
select cast(1.2 as int);
--1 
if(condition,true,false)
 select if(1=2,"男",if(1=1,"妖","女"));
 --妖
 select
 id,
 if(1=id,"男",if(2=id,"女","妖"))
 from u1
 ;
--根据表中的id来匹配得出结果
case when 用法1：
case col
when value then ... 
when value1 then ...
else
...
end

select
id,
(
case id
when 1 then "男"
when 2 then "女"
else
"妖"
end) sl
from u1
;
--匹配id决定结果

用法2：
case
when col=value then ... 
when col=value1 then ...
else
...
end

select
id,
(
case 
when id=1 then "男"
when id=2 then "女"
else
"妖"
end) sl
from u1
;

显示所有函数：
show functions;
查看函数描述：
desc func_name;
模糊查找hive的函数：
show functions like '*concat*'; 
nvl(val,default_value) : val为空则返回default
coalesce(v1,v2,v3...):--返回第一个不为空的值
from_unixtime():
select from_unixtime(1568201236,"yyyy-MM-dd hh:mm:ss")
吧时间戳转化为想要的时间格式
unix_timestamp(): ---得到当前的时间戳
to_date():--提取日期或日期时间表达式表达式表达式的日期部分
date_diff
date_sub--日期子(开始日期，天数)-返回开始日期之前的天数。返回8日的5天前
date_add--日期添加(开始日期，天数)-返回开始日期后的天数。
last_day--（date）返回日期所属月的最后一天
next_day--next_day(start_date，day_of_week) -返回晚于start_date并按指示命名的第一个日期。 next_day(date,"sunday")
current_date:--返回当前日期
current_timestamp:--当前日期+时间
hour()--小时(参数)-返回字符串/时间戳/间隔的小时组成
week()--
mimint()--
extract():--
md5():--
base64():--
加解密场景：
保护数据、将数据变得更小


encode():编解码--编码(str，str) -使用第二个参数字符集编码第一个参数
decode():---使用第二个参数字符集解码第一个参数

format_number(,"0.00"):格式化数字format_number(X，D) -将数字X格式化为“#、###、###”等格式。## '，四舍五入到小数点后第四位，并以字符串形式返回结果。如果D为0，结果没有小数点或小数部分

instr():instr(字符串，子字符串)-返回字符串中第一次出现子字符串的索引
trim():--去除两边空格
ltrim():--去除开头的空格
lpad():左补足lpad(字符串，镜头，衬垫)-返回字符串，用衬垫向左填充到镜头长度，填补到足够的长度用右边字符串，从左边开始填充，选取衬垫的字符也是从左边开始
replace(str,'',''):--没有
substring_index(str,'a',2):指定索引位置
initcap():首字符变大写
find_in_set():查找是否在set中，费性能
select find_in_set("a","b,a");在后面集合中找前面字符出现的位置首次
str_to_map():转map，文本，分开每个map的定界符，单个map中的定界符
get_json_object():获取json对象

regexp_extract(str,reg,2):正则抽取  ()()()
regexp_replace(str,'',''):正则替换

parse_url():解析url

size(a1):map,list
length(str)：字符串
map_keys()--返回map的值集合
map_values()
mv.["k1"]
mv.[0]
mv.k1

collect_set():
collect_list():

cast():类型强转
binary():转换为二进制？？然后并没有
explode():炸裂函数，一般和lateral view搭配使用

窗口函数：

窗口函数：
1、应用场景
求出分组内的统计、排名、自增。
1 23 23
1 35 58
1 12 70

聚合函数和窗口函数区别：
1、聚合函数是一般是多行 返回一行，窗口函数可以达到1行返回一行或者多行返回一行

over(partition by a,b)
count(a) over(partition by a,b order by a desc)
count(a) over(order by a desc)

窗口：物理窗口和逻辑窗口
物理窗口：rows  针对实际排序的列
逻辑窗口：range 针对实际的值

语法：
ROWS/range between [CURRENT ROW | UNBOUNDED PRECEDING | [num] PRECEDING] AND  [UNBOUNDED FOLLOWING | [num] FOLLOWING| CURRENT ROW]

eg:常用
sum() over():
count() over()
avg() over()
...over()
first_value--第一个值
last_value--最后一个值
lag--前面的值
lead--后面的值
ntile

案例：
create table if not exists win(
name string,
class string,
score int
)
row format delimited fields terminated by ' '
;


load data local inpath '/home/hivedata/win' into table win;

逻辑窗口range：
select 
name,
class,
score,
sum(score) over(order by score range between 5 preceding and 5 following) mm --排序后的当前行的值得上下5个单位波动，假如score这是35，前后都是5就是在30-40的分都相加
from win;

物理窗口rows：
select 
name,
class,
score,
sum(score) over(order by score rows between 2 preceding and 3 following) mm --这是直接对列的操作，相当于当前行的上2列，下两列进行相加 
from win;

每个班前一名和其后一名的分差：
select 
name,
class,
score, --lag(score,1)代表前一行的值
(score-lag(score,1) over(partition by class order by score asc)) diff
from win;


第一个和最后一个值：
select 
name,
class,
score,
first_value(score) over(partition by class order by score) first,
last_value(score) over(partition by class order by score) last
from win;

排名函数：
row_number() over()
rank()
dense_rank() 

三者区别：
row_number() over() : 排名函数，名次不会重复，适合于生成主键或者不并列排名如：123
rank() over() :  排名函数，有并列名次，名次不连续。如:1,1,3
dense_rank() over() : 排名函数，有并列名次，名次连续。如：1，1，2


案例：
根据class分组，并根据score排名：
select
class,
name,
score,
row_number() over(distribute by class sort by score desc) rn,
rank() over(distribute by class sort by score desc) rk,
dense_rank() over(distribute by class sort by score desc) drk
from score
;

计算每一个班级的前2名：
select
tmp.*
from (
select
class,
name,
score,
row_number() over(distribute by class sort by score desc) rn,
rank() over(distribute by class sort by score desc) rk,
dense_rank() over(distribute by class sort by score desc) drk
from win) tmp
where tmp.rn < 3
;

聚合函数:

group by :
grouping sets() : 指定分组
with cube:  数据魔方，任意维度的组合查询
with rollup : 

首先，hive一般分为基本聚合和高级聚合，而基本聚合就是常见的group by，而高级聚合就是grouping sets、with cube、with rollup等。一般group by与hive内置的聚合函数max、min、count、sum、avg等搭配使用。而cube和rollup则更多参考oracle的cube和rollup聚合而来，从hive 0.10.0版本后开始支持。

参考官网地址:https://cwiki.apache.org/confluence/display/Hive/Enhanced+Aggregation,+Cube,+Grouping+and+Rollup

group by

分组的意思，使用GROUP BY时，除聚合函数外其他已选择列必须包含在GROUP BY子句中，否则会报错。
求某天、某公司、某部门的员工数量：
select
class,
score,
count(*)
from win
group by class,score
;


结果：
1       80      1
1       95      2
2       74      1
2       92      1
3       45      1
3       55      1
3       78      1
3       99      2



注意:
1、有group by时，查询字段要么在group by子句中，要么在聚合函数中。
2、group by与聚合函数搭配使用，但是聚合函数不能嵌套使用，如sum(count(*))。
3、如果group by的列值中有null，则包含该null的行将在聚合时被忽略。为了避免这种情况，可以使用COALESCE来将null替换为一个默认值。
4、group by与聚合函数count()搭配使用时，同时COUNT又和DISTINCT搭配使用时，Hive将忽略对reducer个数的设置(如：set mapred.reduce.tasks=20;), 仅会有一个reducer！!!此时reduce将成为瓶颈，这时我们可以使用子查询的方式解决该问题，同时子查询需要别名。
5、collect_set() 和collect_list() 不是聚合函数，不需要和group by搭配使用。

grouping sets

grouping sets可以实现对同一个数据集的多重group by操作。事实上grouping sets是多个group by进行union alll操作的结合，它仅使用一个stage完成这些操作。grouping sets的子句中如果包换()数据集，则表示整体聚合。多用于指定的组合查询。
例:
1、grouping sets(a,b) ==> group by a union all group by b
2、grouping sets(a,b,(a,b) ==> group by a union all group by b union all group by a,b
3、grouping sets(a,b,(a,b),()) ==>group by a union all group by b union all group by a,b union all 无group by语句

group by class
group by score
group by class,score



例1、
select
class,
NULL as score,
count(*)
from win
group by class
union all
select
NULL AS class,
score,
count(*)
from win
group by score
union all 
select
class,
score,
count(*)
from win
group by class,score
;

==如上的语句等价于如下的语句

select
class,
score,
count(*)
from win
group by class,score
grouping sets(class,score,(class,score))
;

上面两个语句的结果都如下：

NULL    45      1
NULL    55      1
NULL    74      1
NULL    78      1
NULL    80      1
NULL    92      1
NULL    95      2
NULL    99      2
1       NULL    3
2       NULL    2
3       NULL    5

1       80      1
1       95      2
2       74      1
2       92      1
3       45      1
3       55      1
3       78      1
3       99      2

cube

cube俗称是数据立方，它可以时限hive任意维度的组合查询。即使用with cube语句时，可对group by后的维度做任意组合查询，如：group a,b,c with cube ,则它首先group a,b,c 然后依次group by a,c 、 group by b,c、group by a,b 、group a 、group b、group by c、group by () 等这8种组合查询，所以一般cube个数=2^3个。2是定值，3是维度的个数。多用于无级联关系的任意组合查询。

例1、
select
class,
score,
count(*)
from win
group by class,score
with cube
;

==如上语句等价于如下

select
class,
score,
count(*)
from win
group by class,score
grouping sets(class,score,(class,score),())
;


结果如下：
NULL    NULL    10
NULL    45      1
NULL    55      1
NULL    74      1
NULL    78      1
NULL    80      1
NULL    92      1
NULL    95      2
NULL    99      2
1       NULL    3
1       80      1
1       95      2
2       NULL    2
2       74      1
2       92      1
3       NULL    5
3       45      1
3       55      1
3       78      1
3       99      2


注意:
如上的实现，如果采用传统的group by加union all的方式实现，那就是写得更复杂，大家可以尝试写一写。

rollup

卷起的意思，俗称层级聚合，相对于grouping sets能指定多少种聚合，而with rollup则表示从左往右的逐级递减聚合，如:group by a,b,c with rollup 等价于 group by a, b, c grouping sets( (a, b, c), (a, b), (a), ( )).直到逐级递减为()为止,多适用于有级联关系的组合查询，如国家、省、市级联组合查询。

例1、
select
class,
score,
count(*)
from win
group by class,score
with rollup
;

==上面语句等价于如下语句
select
class,
score,
count(*)
from win
group by class,score
grouping sets((class,score),class,())
;

结果如下：

NULL    NULL    10
1       NULL    3
1       80      1
1       95      2
2       NULL    2
2       74      1
2       92      1
3       NULL    5
3       45      1
3       55      1
3       78      1
3       99      2


注意:
如上的实现，也可以采用传统的group by加union all来实现，大家可以尝试。

Grouping__ID

Grouping__ID在hive2.3.0版本被修复过，修复后的发型版本和之前的不一样。对于每一列，如果这列被聚合过则返回0，否则返回1。应用场景暂时很难想到用于哪儿。

select
deh.bdp_day,
deh.company,
GROUPING__ID,
count(deh.snum) cnt
from dw_employee_hive deh
group by deh.bdp_day,deh.company
with rollup
;

NULL    NULL    0       33102
20190701        NULL    2       33102
20190701        360     3       1839
20190701        Alibaba 3       1781
20190701        Baidu   3       1901
20190701        Ctrip   3       1797
20190701        HuaWei  3       1848
20190701        JD      3       1770
20190701        KingSoft        3       1802
20190701        MeiTuan 3       1826
20190701        NTES    3       1808
20190701        Other   3       1885
20190701        SOHU    3       1862
20190701        SUNING  3       1868
20190701        Sina    3       1823
20190701        Tencent 3       1865
20190701        TouTiao 3       1831
20190701        Unknown 3       1860
20190701        XiaoMi  3       1844
20190701        ZTC     3       1892

注意:
grouping_id是维度的二进制表示，然后会将二进制换算成十进制表示。如:grop by a,b,c 而grouping__id最大值应该是8，因为111的二进制表示为8，转换成10进制也是8。


grouping sets/cube/rollup三者的区别：
注:
grouping sets是指定具体的组合来查询。数据集个数根据指定来
with cube 是group by后列的所有的维度的任意组合查询。数据立方体个数维度的的2次方个
with rollup 是group by后列的从左往右逐级递减的层级组合查询。数据集个数维度+1个
cube/rollup 后不能加()来选择列，hive是要求这样。

聚合优化

hive.map.agg=true;     #最好将其设置为true，因为会用到ma端聚合。
hive.new.job.grouping.set.cardinality=30;   #在grouping sets/cube/rollup中是否启用新的job来执行，主要是因为如果分组多而造成数据立方体炸裂，适当设置该阀值，默认为30.

自定义函数：

hive的内置函数满足不了所有的业务需求。
hive提供很多的模块可以自定义功能，比如：自定义函数、serde、输入输出格式等。

常见的自定义函数：

udf：用户自定义函数，user defined function。一对一的输入输出。（最常用的）。
udaf：用户自定义聚合函数。user defined aggregate function。多对一的输入输出。
udtf：用户自定义表生成函数。user defined table-generate function.一对多的输入输出。

编写：

1、继承UDF，重写evaluate()，允许重载。（常用）
2、继承genericUDF，重写initlizer（）、getdisplay（）、evaluate()。

案例：


> add jar /home/hadoop_learn-1.0.jar;
> create temporary function my_concat as 'com.ali.udf.FirstUdf';
> show functions like *my_c*;
hive> show functions like '*my_c*';
OK
my_concat
> select my_concat('a','b');
> select my_concat(class,name),score from win;


永久：
> create function my_concat as 'com.ali.udf.FirstUdf' using jar hdfs://hadoop01:9000/hadoop_learn-1.0.jar;
将udf的包上传到指定位置。
以后每次将会加载方法。
注意，该方式将会使用库名.函数名。