hive（三）
hive语句的执行顺序
1.from
2.join on 或 lateral view explode(需炸裂的列) tbl as 炸裂后的列名
3.where
4.group by
5.聚合函数 如Sum() avg() count(1)等
6.having 在此开始可以使用select中的别名
7.select 若包含over（）开窗函数，此时select中的内容作为窗口函数的输入，窗口中所选的数据范围也是在group by，having之后，并不是针对where后的数据进行开窗，这点要注意。需要注意开窗函数的执行顺序及时间点。
8.distinct
9.order by
10.limit
3、where 条件里不支持不等式子查询，实际上是支持 in、not in、exists、not exists
4、hive中大小写不敏感
5、在hive中，数据中如果有null字符串，加载到表中的时候会变成 null （不是字符串）
如果需要判断 null，使用 某个字段名 is null 这样的方式来判断
或者使用 nvl() 函数，不能 直接 某个字段名 == null
6、使用explain查看SQL执行计划
# 查看更加详细的执行计划，加上extended
Hive 常用函数
关系运算
// 等值比较 = == <=>
// 不等值比较 != <>
// 区间比较： select * from default.students where id between 1500100001 and 1500100010;
// 空值/非空值判断：is null、is not null、nvl()、isnull()
// like、rlike、regexp用法
数值计算
取整函数(四舍五入)：round
向上取整：ceil
向下取整：floor
条件函数
-
if： if(表达式,如果表达式成立的返回值,如果表达式不成立的返回值)
-
COALESCE
-
case when
注意条件的顺序
日期函数重点！！！
select from_unixtime(1610611142,'YYYY/MM/dd HH:mm:ss');
select from_unixtime(unix_timestamp(),'YYYY/MM/dd HH:mm:ss');
字符串函数
concat('123','456'); // 123456
concat('123','456',null); // NULL
select concat_ws('#','a','b','c'); // a#b#c
select concat_ws('#','a','b','c',NULL); // a#b#c 可以指定分隔符，并且会自动忽略NULL
select concat_ws("|",cast(id as string),name,cast(age as string),gender,clazz) from students limit 10;
select substring("abcdefg",1); // abcdefg HQL中涉及到位置的时候 是从1开始计数
// '2021/01/14' -> '2021-01-14'
select concat_ws("-",substring('2021/01/14',1,4),substring('2021/01/14',6,2),substring('2021/01/14',9,2));
// 建议使用日期函数去做日期
select from_unixtime(unix_timestamp('2021/01/14','yyyy/MM/dd'),'yyyy-MM-dd');
select split("abcde,fgh",","); // ["abcde","fgh"]
select split("a,b,c,d,e,f",",")[2]; // c 数组的下标依旧是从0开始
select explode(split("abcde,fgh",",")); // abcde
// fgh
// 解析json格式的数据
select get_json_object('{"name":"zhangsan","age":18,"score":[{"course_name":"math","score":100},{"course_name":"english","score":60}]}',"$.score[1].score"); // 100
Hive 中的wordCount
1.1 Hive窗口函数
普通的聚合函数每组(Group by)只返回一个值，而开窗函数则可为窗口中的每行都返回一个值。 简单理解，就是对查询的结果多出一列，这一列可以是聚合值，也可以是排序值。 开窗函数一般就是说的是over（）函数，其窗口是由一个 OVER 子句 定义的多行记录 开窗函数一般分为两类,聚合开窗函数和排序开窗函数。
-- 聚合格式
select sum(字段名) over([partition by 字段名] [ order by 字段名]) as 别名,
max(字段名) over() as 别名
from 表名;
-- 排序窗口格式
select rank() over([partition by 字段名] [ order by 字段名]) as 别名 from 表名;
注意点：
-
over()函数中的分区、排序、指定窗口范围可组合使用也可以不指定，根据不同的业务需求结合使用
-
over()函数中如果不指定分区，窗口大小是针对查询产生的所有数据，如果指定了分区，窗口大小是针对每个分区的数据
1.1.1 聚合开窗函数
sum(求和)
min(最小)
max(最大)
avg(平均值)
count(计数)
lag(获取当前行上一行的数据）
select name,subject,score,
sum(score) over() as sum1,
sum(score) over(partition by subject) as sum2,
sum(score) over(partition by subject order by score) as sum3,
-- 由起点到当前行的窗口聚合，和sum3一样
sum(score) over(partition by subject order by score rows between unbounded preceding and current row) as sum4,
-- 当前行和前面一行的窗口聚合
sum(score) over(partition by subject order by score rows between 1 preceding and current row) as sum5,
-- 当前行的前面一行到后面一行的窗口聚合 前一行+当前行+后一行
sum(score) over(partition by subject order by score rows between 1 preceding and 1 following) as sum6,
-- 当前和后面所有的行
sum(score) over(partition by subject order by score rows between current row and unbounded following) as sum7
from t_fraction;
+-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+
| name | subject | score | sum1 | sum2 | sum3 | sum4 | sum5 | sum6 | sum7 |
+-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+
| 孙悟空 | 数学 | 12 | 359 | 185 | 12 | 12 | 12 | 31 | 185 |
| 沙悟净 | 数学 | 19 | 359 | 185 | 31 | 31 | 31 | 104 | 173 |
| 猪八戒 | 数学 | 73 | 359 | 185 | 104 | 104 | 92 | 173 | 154 |
| 唐玄奘 | 数学 | 81 | 359 | 185 | 185 | 185 | 154 | 154 | 81 |
| 猪八戒 | 英语 | 11 | 359 | 80 | 11 | 11 | 11 | 26 | 80 |
| 孙悟空 | 英语 | 15 | 359 | 80 | 26 | 26 | 26 | 49 | 69 |
| 唐玄奘 | 英语 | 23 | 359 | 80 | 49 | 49 | 38 | 69 | 54 |
| 沙悟净 | 英语 | 31 | 359 | 80 | 80 | 80 | 54 | 54 | 31 |
| 孙悟空 | 语文 | 10 | 359 | 94 | 10 | 10 | 10 | 31 | 94 |
| 唐玄奘 | 语文 | 21 | 359 | 94 | 31 | 31 | 31 | 53 | 84 |
| 沙悟净 | 语文 | 22 | 359 | 94 | 53 | 53 | 43 | 84 | 63 |
| 猪八戒 | 语文 | 41 | 359 | 94 | 94 | 94 | 63 | 63 | 41 |
+-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+
rows必须跟在Order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量。
OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。
CURRENT ROW：当前行
n PRECEDING：往前n行数据
n FOLLOWING：往后n行数据
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点
LAG(col,n,default_val)：往前第n行数据，col是列名，n是往上的行数，当第n行为null的时候取default_val
LEAD(col,n, default_val)：往后第n行数据，col是列名，n是往下的行数，当第n行为null的时候取default_val
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。
cume_dist（），计算某个窗口或分区中某个值的累积分布。假定升序排序，则使用以下公式确定累积分布：
小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中，x 等于 order by 子句中指定的列的当前行中的值。
1.1.2 排序开窗函数(重点)
-
RANK() 排序相同时会重复，总数不会变
-
DENSE_RANK() 排序相同时会重复，总数会减少
-
ROW_NUMBER() 会根据顺序计算
-
PERCENT_RANK()计算给定行的百分比排名。可以用来计算超过了百分之多少的人(当前行的rank值-1)/(分组内的总行数-1)
Hive 行转列
lateral view explode
create table testArray2(
name string,
weight array<string>
)row format delimited
fields terminated by '\t'
COLLECTION ITEMS terminated by ',';
Hive 列转行
create table testLieToLine(
name string,
col1 int
)row format delimited
fields terminated by '\t';
select name,collect_list(col1) from testLieToLine group by name;
select t1.name
,collect_list(t1.col1)
from (
select name
,col1
from testarray2
lateral view explode(weight) t1 as col1
) t1 group by t1.name;
Hive自定义函数UserDefineFunction
UDF：一进一出
定义UDF函数要注意下面几点:
继承
org.apache.hadoop.hive.ql.exec.UDF重写
evaluate()，这个方法不是由接口定义的,因为它可接受的参数的个数,数据类型都是不确定的。Hive会检查UDF,看能否找到和函数调用相匹配的evaluate()方法
函数加载方式
命令加载
这种加载只对本session有效
# 1、将项目打包上传服务器：将打好的jar包传到linux系统中。(不要打依赖)
# 进入到hive客户端,执行下面命令
hive> add jar /usr/local/soft/bigdata17/data/xiaohu/hadoop-mapreduce-1.0-SNAPSHOT.jar
# 2、创建一个临时函数名,要跟上面hive在同一个session里面：
hive> create temporary function toUP as 'com.shujia.testHiveFun.udf.FirstUDF';
3、检查函数是否创建成功
show functions;
4. 测试功能
select toUp('abcdef');
5. 删除函数
drop temporary function if exists toUp;
创建永久函数
将jar上传HDFS：
hadoop fs -put hadoop-mapreduce-1.0-SNAPSHOT.jar /jar/
在hive命令行中创建永久函数：
create function myUp as 'com.shujia.testHiveFun.udf.FirstUDF' using jar 'hdfs:/jar/hadoop-mapreduce-1.0-SNAPSHOT.jar';
create function hxudf as 'com.shujia.hivefun.MyUDF' using jar 'hdfs:/shujia/bigdata17/jar/hive-udf1.jar';
UDTF：一进多出
UDTF是一对多的输入输出,实现UDTF需要完成下面步骤
继承org.apache.hadoop.hive.ql.udf.generic.GenericUDTF， 重写initlizer（）、process（）、close()。 执行流程如下:
UDTF首先会调用initialize方法，此方法返回UDTF的返回行的信息（返回个数，类型）。
初始化完成后，会调用process方法,真正的处理过程在process函数中，在process中，每一次forward()调用产生一行；如果产生多列可以将多个列的值放在一个数组中，然后将该数组传入到forward()函数。
最后close()方法调用，对需要清理的方法进行清理。
"key1:value1,key2:value2,key3:value3"
key1 value1
key2 value2
key3 value3
方法一：使用 explode+split
select split(t.col1,":")[0],split(t.col1,":")[1]
from (select explode(split("key1:value1,key2:value2,key3:value3",",")) as col1) t;
方法二：自定UDTF
UDAF：多进一出
Hive Shell
第一种：
hive -e "select * from test1.students limit 10"
第二种：
hive -f hql文件路径
将HQL写在一个文件里，再使用 -f 参数指定该文件
连续登陆问题
在电商、物流和银行可能经常会遇到这样的需求：统计用户连续交易的总额、连续登陆天数、连续登陆开始和结束时间、间隔天数等
数据：
注意：每个用户每天可能会有多条记录
建表语句
create table deal_tb(
id string
,datestr string
,amount string
)row format delimited fields terminated by ',';
计算逻辑
-
先按用户和日期分组求和，使每个用户每天只有一条数据
select id,datestr,sum(amount) as sum_amount from deal_tb group by id,datestr;
-
根据用户ID分组按日期排序，将日期和分组序号相减得到连续登陆的开始日期，如果开始日期相同说明连续登陆
select tt1.id,tt1.datestr,tt1.sum_amount,date_sub(tt1.datestr,tt1.rn) as grp from (select t1.id as id,t1.datestr as datestr,t1.sum_amount as sum_amount,row_number() over(partition by t1.id order by t1.datestr) as rn from (select id,datestr,sum(amount) as sum_amount from deal_tb group by id,datestr) t1) tt1;
-
datediff(string end_date,string start_date); 等于0说明连续登录
-
统计用户连续交易的总额、连续登陆天数、连续登陆开始和结束时间、间隔天数
Hive优化
1.1 hive的随机抓取策略
理论上来说，Hive中的所有sql都需要进行mapreduce，但是hive的抓取策略帮我们 省略掉了这个过程，把切片split的过程提前帮我们做了。 set hive.fetch.task.conversion=none; (一旦进行这么设置，select字段名也是需要进行mapreduce的过程，默认是more)
Fetch抓取的模式
可以通过 set hive.fetch.task.conversion查看，有以下3种模式：
none：所有涉及hdfs的读取查询都走mapreduce任务；
mininal：在进行简单的select *，简单的过滤或涉及分区字段的过滤时走mr；
more:在mininal模式的基础上，增加了针对查询语句字段进行一些别名的计算操作。
以下HQL，mininal模式与more模式下都不会走mr任务:
SELECT
sale_ord_id,
store_id
FROM
test_table
where
dt = '2021-01-01'
limit 10;
以下HQL,mininal模式会走mr任务，more模式不会：
SELECT
sale_ord_id,
store_id,
if(store_id > 20,1,0) as store_id_new
FROM
test_table
where
dt = '2021-01-01'
limit 10;
查看怎么将一个sql转化成一个MR任务的 explain sql语句 例如： explain select count(*) from stu_dy1_1; 更加详细的查看，例如： explain extended select count(*) from stu_dy1_1; 当你输入一个sql语句的时候，hive会将对其关键字进行截串，截完串之后，变成 都是一些TOK开头的一些东西，然后经过这样的抽象语法树，再转成具体的查询块， 最后变成逻辑查询计划
1.2 本地运行模式
大多数的 Hadoop Job 是需要 Hadoop 提供的完整的可扩展性来处理大数据集的。不过，
有时 Hive 的输入数据量是非常小的。在这种情况下，为查询触发执行任务消耗的时间可能
会比实际 job 的执行时间要多的多。对于大多数这种情况， Hive 可以通过本地模式在单台机
器上处理所有的任务。对于小数据集，执行时间可以明显被缩短。
用户可以通过设置 hive.exec.mode.local.auto 的值为 true ，来让 Hive 在适当的时候自动
启动这个优化。
本地模式运行比集群模式块很多，33秒的任务降到2秒
更改为本地模式：
hive> set hive.exec.mode.local.auto=true
注意：
hive> set hive.exec.mode.local.auto.inputbytes.max=134217728 ---> 128M
（默认值就是128）
表示加载文件的最大值，若大于该配置仍然会以集群的方式去运行。
97万行数据，50MB
当我们开发或者测试阶段，可以去使用本地模式进行运行，默认是集群模式
但是，这里有个问题，当我们去更改为本地模式的时候，在8088的页面上就看不到
任务的执行情况了。
测试：select count(*) from emp group by deptno;
1.3 并行计算
通过设置以下参数开启并行模式（默认是false）
set hive.exec.parallel=true;
注意：hive.exec.parallel.thread.number
(一次SQl计算中允许并行执行的job个数最大值，默认是8个)
举例：
select t1.n1,t2.n2 from (select count(ename) as n1 from emp) t1,(select count(dname) as n2 from dept) t2;
注意，有时候开启并行计算运行时间并没有不开启的快，那是因为，资源的问题。
需要两套资源，资源申请会浪费点时间，最多可以并行8个，默认是8个。
所以，并行的越多，不一定是越快，因为它涉及到一个资源申请的策略。
1.4 严格模式(理解为增加一些限制)
1.什么是Hive的严格模式 hive中的一种模式,在该模式下禁止一些不好SQL的执行。
2.Hive的严格模式不允许哪些SQL执行 2.1 禁止分区表全表扫描 分区表往往数据量大,如果不加分区查询会带来巨大的资源消耗 。例如以下分区表 SELECT DISTINCT(planner_id) FROM fracture_ins WHERE planner_id=5;
报错如下: FAILED: Error in semantic analysis: No Partition Predicate Found for Alias “fracture_ins” Table "fracture_ins
解决如下: SELECT DISTINCT(planner_id) FROM fracture_ins WHERE planner_id=5 AND hit_date=20120101;
2.2 禁止排序不加limit 排序最终是要都进到一个Reduce中操作,防止reducer额外执行很长一段时间 SELECT * FROM fracture_ins WHERE hit_date>2012 ORDER BY planner_id; 出现如下错误 FAILED: Error in semantic analysis: line 1:56 In strict mode,limit must be specified if ORDER BY is present planner_id 解决方案就是增加一个limit关键字： hive> SELECT * FROM fracture_ins WHERE hit_date>2012 ORDER BY planner_id LIMIT 100000;
2.3 禁止笛卡尔积 笛卡尔积是什么: A={a,b}, B={0,1,2}，则 A×B={(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}
SELECT * FROM fracture_act JOIN fracture_ads; 解决方法 SELECT * FROM fracture_act JOIN fracture_ads WHERE fracture_act.planner_id = fracture_ads.planner_id;
3.Hive的严格模式怎样开启
// 查看当前严格模式的状态
set hive.mapred.mode;
// 设置为严格模式
set hive.mapred.mode=strict;
// 设置为非严格模式
set hive.mapred.mode=nostrict;
注意，这里的严格模式和动态分区的那个严格模式半毛钱关系没有）
通过设置以下参数开启严格模式：
set hive.mapred.mode=strict;
(默认为：nonstrict非严格模式)
查询限制：
1、对于分区表，必须添加where对于分区字段的条件过滤
2、order by 语句必须包含limit输出限制
3、限制执行笛卡尔积的查询
这些限制是帮助我们提高查询效率的。
1.5 Hive排序(掌握distribute by和sort by) 回顾
order by 对于查询结果做全排序，只允许有一个reduce处理
（注意：它会把我们所有的字段或者查询结果全部放在一个reduce里进行处理
当数据量较大时候，有可能reduce执行不完，所以，我们以后把这个给弃用掉）
** sort by 对于单个reduce进行排序 但是我们将每个reduce里面进行排序，没有考虑到
每个reduce之间的排序。所以我们引出下一个
** distribute by 分区排序，通常结合sort by一起使用
（distribute by column sort by column asc|desc）
cluster by 相当于distribute by + sort by (注意，虽然是两个结合，但是我们也不去用它
原因很简单，cluster by不能通过asc desc的方式指定排序方式规则)
1.6 Hive join数据倾斜(相当重要，记住这块，面试到hive数据倾斜稳过)
1、小表join小表 不管他
2、小表join大表 map-join
3、大表join大表 map-side
考虑会不会发生reduce,并且考虑reduce压力是否大（是否会出现某个reduce数据量庞大的情况）
join计算的时候，将小表（驱动表）放在join的左边
Map join：在Map端完成join
两种实现方式：
1、sql方式，在sql语句中添加Mapjoin标记（mapjoin hint）
>>语法：
select /*+MAPJOIN(smallTable)*/ smallTable.key bigTable.value from smallTable join bigTable on smallTable.key=bigTable.key;
2、自动开启mapjoin
通过修改以下配置启用自动的mapjoin：
set hive.auto.convert.join=true;
(注意：该参数为true的时候，Hive自动对左边的表统计量，如果
是小表，就加入到内存，即对小表使用Mapjoin)
相关配置参数
hive.mapjoin.smalltable.filesize;(默认25M,大表小表判断的阈值，如果表的大小小于该值则会被加载到内存中运行。)
hive.ignore,mapjoin.hint;(默认值：true;是否忽略mapjoin hint的标记)
hive.auto.convert.join.noconditionaltask;(默认值：true；将普通的join转换为mapjoin时，是否将多个mapjoin转化为一个mapjoin)
hive.auto.convert.join.noconditionaltask.size;(将多个mapjoin转化为一个mapjoin时，这个表的最大值)
3、尽可能使用相同的连接键，如果不同，多一个join就会多开启一个mapreduce，执行速度变得慢。
4、大表join大表（当两个都是大表的时候，只能发生reduce了，但是这里有两个优化策略）（面试的时候说，加分）
a: 空key过滤:
有时join超时是因为某些key对应的数据太多,而相同key对应的数据都会发送到相同的 reducer上,从而导致内存不够。
此时我们应该仔细分析这些异常的key,很多情况下,这些key对应的数据是异常数据,我们需要在SQL语句中进行过滤。
但是这个的前提条件是异常数据，但是我们一般拿到的数据都是经过ETL数据清洗过后的，一般影响不大，面试的时候可以说。
b: 空key转换:
有时虽然某个key为空对应的数据很多,但是相应的数据不是异常数据,必须要包含在join的结果中,
此时我们可以表a中key为空的字段赋随机的值,使得数据随机均匀地分不到不同的 reducer上。
但是我们一般拿到的数据都是经过ETL数据清洗过后的，规则数据，一般影响不大，面试的时候可以说。
5、Map-Side聚合
通过设置以下参数开启在Map端的聚合
set hive.map.aggr=true;（一定要进行开启，虽然进行了两个mapreduce，但是当数据倾斜发生的时候，很多时候会根本跑不出结果，卡死在99%或者100%，慢总比出不来结果要好）！！！！！！！
相关配置参数
hive. groupby mapaggr. checkinterval;
map端 igroup by执行聚合时处理的多少行数据(默认:10000
hive.map.aggr.hash.min.reduction;比例(若聚合之后的数据100大该0.5,map端聚合使用的内存的最大值
hive.mapaggr.hashforce.flush.memory.threshold;map端做聚合操作是has表的最大可用内容,大于该值则会触发fush
hive.groupby.skewindata-是否对 GroupBy产生的数据倾斜做优化,默认为false(十分重要！！！)
6、数据倾斜，尽可能地让我们的数据散列到不同的reduce里面去,负载均衡
1.7 合并小文件
Hive优化
合并小文件
文件数目小,容易在文件存储端造成压力,给hdfs造成压力,影响效率
设置合并属性
是否合并map输出文件: hive.merge.mapfiles=true
是否合并reduce输出文件: hive.merge.mapredfiles=true
合并文件的大小: hive.merge.size.per.task=256*1000*1000
去重统计
数据量小的时候无所谓,数据量大的情况下,由于 COUNT DISTINCT操作需要用一个 Reduce Task来完成,
这一个 Reduce需要处理的数据量太大,就会导致整个JOb很难完成,一般 COUNT DISTINCT使用先 GROUP BY再COUNT的方式替换
1.8 控制map和reduce的数量(一般情况下我们不去动它)
控制Hive中Map以及 Reduce的数量
Map数量相关的参数
mapred.max.split.size;一个split的最大值,即每个map处理文件的最大值
mapred.min.split.size.per.node个节点上split的最小值
mapred.min.split.size.per.rack一个机架上spit的最小值
Reduce数量相关的参数
mapred.reduce.tasks;强制指定reduce任务的数量
hive.exec.reducers.bytes.per.reducer每个reduce任务处理的数据量
hive.exec.reducers.max每个任务最大的reduce数
1.9 JVM重用
当我们的小文件个数过多，task个数过多，需要申请的资源过多的时候，我们可以先申请一部分资源，全部执行完毕后再释放，
比我们申请一个释放一个要快。
通过 set mapred.job.reuse.jvm.num.tasks=n;来设置
（n为task插槽个数）
缺点：
设置开启后，task插槽会一直占用资源，无论是否有task进行，直到所有的task,
即整个job全部执行完毕后，才会释放所有的task插槽，所以我们要合理地设置这个n
(比如，我们设置申请了10个，但是现在来了6个，剩下4个插槽会在job全部执行完毕之前一直占用资源)
