Hive之学习之路

1、自定义函数UDF
UDF 一进一出(行) 改变大小写、增加字符串之类的操作
UDAF 聚集函数(行) 多进一出 count max min sum
UDTF 一进多出(行) lateral view explore() 例如：explode
2、大数据解决什么事情
传输 存储 计算
3、hive定义
解决海量结构化日志的数据统计，结构化数据文件映射为一张表，是一个数据仓库工具，分析数据底层默认实现是MapReduce
4、hive的缺点
HQL表达能力有限
（1）迭代式算法无法表达
（2）数据挖掘不擅长（一般是迭代，挖掘两个数据的关系）
5、hive的架构里有哪些组件
元数据（meta store）：hive表与hdfs上文件的映射关系，默认derby数据库，正常时mysql存
解析器-编译器-优化器-执行器
6、hive没有索引，查询数据，需要扫描整个表，延迟较高
7、显示数据库详细信息，extended 例子desc database extended db_hive;
8、如果数据库不为空，可以采用 cascade 命令，强制删除

建表：

create table log_orc_snappy(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string )
row format delimited fields terminated by ‘\t’
stored as orc tblproperties (“orc.compress”=“SNAPPY”);
压缩模式是snappy的建表语句

查询结构：
select
from
join in
where
group by
order by
having
limit
顺序是：先from->join in->where->group by->select->having->order by->limit
select之后的才能用别名
SELECT count(A1) c,B1 from a JOIN b on a.A2=b.B2 GROUP BY B1 HAVING c>1能执行
所以，having排在select的后面，因为hiving能识别select后的别名，说明select运行过了
所以就涉及到一些优化的问题：
select
id,
name
from A
join B on A.id=B.id
where A.id=11;
可以优化成
select
from
(select id ,name from A where A.id=11) r1
join B
on r1.id =B.id
提前缩小数据集，因为join先执行，所以执行的数据集就比较大，那如果先把where执行完，再执行join，数据集就会小很多。不过where条件一定是原表有的才能用这个

对某个查询结果再做加工的要用子查询
例子：
select
count() ct
ct+1
明显是错误的，因为count() ct和ct+1是平级的，count要查询全表，才能出来，所以就找不到ct，更别说+1，所以对于某一个结果再进行加工的时候要用子函数

select
count(*) ct
from
A; t1

select
ct,
ct+1
from
t1

select
ct,
ct+1
from
(select
count(*) ct
from
A)

10、建表时候的语句：STORED AS file_format
COMMENT 为表和列添加注释
CLUSTERED BY 分桶
PARTITIONED BY 分区
LOCATION 改变存储路径

hive中 full join 保留所有数据

11、show create table cc; 查看建表语句

12、LIKE 允许用户复制现有的表结构，但是不复制数据。

13、desc formatted dept;查询表是外部表EXTERNAL_TABLE还是内部表 MANAGED_TABLE

14、修改内部表 student2 为外部表
alter table student2 set tblproperties(‘EXTERNAL’=‘TRUE’); 固定写法，区分大小写
修改外部表 student2 为内部表
alter table student2 set tblproperties(‘EXTERNAL’=‘FALSE’);

15、增加分区
创建单个分区：hive (default)> alter table dept_partition add partition(month=‘201706’) ;

同时创建多个分区：alter table dept_partition add partition(month=‘201705’) partition(month=‘201704’);

查看分区表有多少分区： show partitions dept_partition;

删除单个分区：alter table dept_partition drop partition
(month=‘201704’);

查看分区表结构：desc formatted dept_partition;

执行修复命令 msck repair table a;

16、重命名表：ALTER TABLE table_name RENAME TO new_table_name
增加列：alter table dept_partition add columns(deptdesc string);
更新列：alter table dept_partition change column a(之前列名) b(改后列名) int(对应格式 );
替换列：alter table dept_partition replace columns(a string, bstring, c string);直接把目标表中所有列都替换掉了

DML 数据操作

1、根据查询结果创建表
create table if not exists student3 as select id, name from student;
2、清除表中数据 truncate table a;
3、select官方文档 https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select
4、常用函数 sum count max min avg平均数
5、A<=>B 和A=B区别在于如果 A 和 B 都为 NULL，则返回 TRUE，也就是说<=>可以匹配null，=不可以
6、IN[数值 1, 数值 2] 两边是闭区间
7、like查询 % 代表零个或多个字符(任意个字符)。_ 代表一个字符。relike 后边可以使用java正则
例子：查找以 2 开头薪水的员工信息 select * from emp where sal LIKE ‘2%’;
查找第二个数值为 2 的薪水的员工信息select * from emp where sal LIKE ‘_2%’;
查找薪水中含有 2 的员工信息 select * from emp where sal RLIKE ‘[2]’;
8、where 后面不能写聚合函数，而 having 后面可以使用聚合函数。having 只用于 group by 分组统计语句。where 针对表中的列发挥作用，查询数据；having 针对查询结果中的列发挥作用，
筛选数据
例子： 求每个部门的平均薪水大于 2000 的部门
select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;
9、在hive中，join语法只支持等值连接，不支持非等值连接，和mysql不同
10、left join 指的是：首先取出a表中所有数据,然后再加上与a,b匹配的的数据
11、连接操作，Hive 总是按照从左到右的顺序执行的
12、没有连接条件就会生成笛卡儿积，两个表所有行对乘

排序

1、全局排序（Order By）只有一个reducer
ASC（ascend）: 升序（默认）
DESC（descend）: 降序
2、每个 MapReduce 内部排序（Sort By）
设置 reduce 个数： set mapreduce.job.reduces=3; 再sort by 就生效了
查看设置 reduce 个数：set mapreduce.job.reduces;
————但是如果只用sort by 分的区是随机分区，所以加入Distribute By
注意：！！Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。
————如果当 distribute by 和 sorts by 字段相同时，可以使用 cluster by。cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是升序排序，不能指定排序规则为 ASC 或者 DESC。

分桶

1、分区不是按照建表字段，分桶需要按照建表字段来分桶
创建分桶表时，数据通过子查询的方式导入。直接用load导入，相当于在hdfs上使用put，改变的是元数据信息，一个文件不可能分成多个。
但是同过子查询方式导入也需要先设置属性：
set hive.enforce.bucketing=true; 开启分桶功能
set mapreduce.job.reduces=-1;将reduce个数设置为-1，意思就是自己会根据桶的个数来决定reduce的个数，这也是默认值

2、分桶抽样查询
查询表 stu_buck 中的数据
select * from stu_buck tablesample(bucket 1 out of 4 on id);
tablesample 是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 。
x是从哪个桶开始抽，y 必须是 table 总 bucket 数的倍数或者因子，y是抽取总数 bucket/y 桶的数据，如果抽的超过一个分区，例如一共12个桶，x=1,y=3 ，就是从桶1开始抽，抽4个桶，分别是1，4，7，10这4个桶，每次都+y
x值必须小于y值

常用函数

1、空字段赋值
select nvl(comm,-1) from emp; 如果员工的 comm 为 NULL，则用-1 代替
如果员工的 comm 为 NULL，则用领导 id 代替 select nvl(comm,mgr) from emp;另外一列来代替

2、时间类
date_format:格式化时间 date_format(‘2019-06-29’,‘yyyy-MM-dd’) 后面是你想格式化后的格式

date_add:时间跟天数相加； date_sub:时间跟天数相减，可以传负数，两个记一个就行，select date_add(‘2019-06-29’,5);可以自动跨月，前面得是字符串

datediff:两个时间相减 ：datediff(‘2019-06-29’,‘2019-06-24’);
select datediff(‘2019-06-24 12:12:12’,‘2019-06-29’);
OK
_c0
-5
因为2019/2/12 时间函数无法解析，所以用如下函数
regexp_replace(对哪个字段进行切换,你要替换的什么东西,换成什么)
例子：regexp_replace(‘2019/2/12’,’/’,’-’)

3、case when
列转行就要用到case when
dept_id,
sum(case sex when ‘男’ then 1 else 0 end) male_count,
sum(case sex when ‘女’ then 1 else 0 end) female_count
from
emp_sex
group by
dept_id;
如果只有两个分支也可以用if来代替
dept_id,
if(sex=‘男’,1,0) male_count,
if(sex=‘女’,1,0) male_count if的结构：if（布尔值，true的结果，false的结果）
from
emp_sex
group by
dept_id;

4、CONCAT
CONCAT_WS(separator, str1, str2,…)：如果有多个同样的分隔符，可以用这个！！必须是String
例子： concat_ws(’-’.‘as’,‘df’)
***COLLECT_SET(列)***：因为set函数没有重复的值，所以这个函数有去重的作用，会生成一个array数组 聚合函数，列转行
可以混合使用：concat_ws(’-’.COLLECT_SET(列)) 把一列数据形成一个-分割的字符串

5、EXPLODE(col)：将 hive 一列中复杂的 array 或者 map 结构拆分成多行。explode(炸裂的意思)
如果直接用select explode(a）from b; 可以全部炸开
如果select f,explode(a) from b 报错，语法不能这样使用
这时就需要LATERAL VIEW （侧写）跟原来表关联上
用法：
select
movie,
category_name
from
movie_info lateral view explode(category) table_tmp（表别名） as category_name（列别名）;

substring(orderdate,1,7)截取字符串从1到7个字符

窗口函数

当一个聚合和一个多行的在一起需要用到窗口函数

开窗：
一、对开窗的理解
开窗等于开一部分数据集出来
1、开窗函数必须在聚合函数后边
2、开窗函数开出的数据集，只给聚合函数使用
3、over()里面没有参数代表对整个数据集进行开窗
4、开窗是针对于每一条数据来开窗
我的理解是：over() 是用来选定聚合函数所使用的数据集的
例子：
（1）select name
from business
where substring(orderdate,1,7) = ‘2017-04’

小三
小三
小四
小屋
小气

（2）select name，count() over()
from business
where substring(orderdate,1,7) = ‘2017-04’
小三 5
小三 5
小四 5
小屋 5
小气 5
为什么呢，首先在（2）中先做了一些（1）中的查询名字的操作，形成了一个名字的数据集，也就是这5个人(不一定都是不同的人)。然后再做count()操作，针对于哪一部分进行count呢，over()里面没有参数，就是针对所有数据集做count(*)，那所有数据集是什么，就是这5个人。
如果不加over()，那么有聚合函数必须加group by ，那么就会按照name分组，那么结果就应该是
小三 2
小四 1
小屋 1
小气 1

二、over()的那些参数
1、over(order by a) 对每个数据开窗的时候，加上了比当前数据小的数据，一起作为一个开窗数据集
例子：有一列数据
shuzi
4
5
6
7
对4开窗的时候，数据集就是4
对5 开窗的时候数据集是
4
5
对6开窗是
4
5
6以此类推
所以用sum函数的时候，结果集就是
4
9（4+5）
15（4+5+6）
22（4+5+6+7）

2、over(distrubute by a) 类似于group by 针对a 分组分数据集，也就是说小米的数据集就是3个小米
比如如下数据
a ——b
小米 30
小米 20
小米 10
小顶 40
小皮 50
sum(b)over(distrubute by a)
小米 60
小米 60
小米 60
小顶 40
小皮 50

那么同理
如下数据
a ——b——c
小米 30 3
小米 20 2
小米 10 1
小顶 40 4
小皮 50 6
3、sum(b)over(distrubute by a sort by c) 就是先把数据集限定在小米里，然后对小米对应的c排序累加，原理和order by 那个一样 ，只不过order by 那个是全局累加，这个是在分区的数据集里累加
那么结果是：
小米 10 1
小米 30 2
小米 60 3
小顶 40 4
小皮 50 6

4、over()里面还有其他函数，都是划分数据集的（不太常用）
CURRENT ROW：当前行；
n PRECEDING：往前 n 行数据；
n FOLLOWING：往后 n 行数据；
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点，UNBOUNDED
FOLLOWING 表示到后面的终点；
例子：
select
xx,
sum(ss)over(rows between 2 preceding and current row) 当前行到前面2行的数据加和
from a;

5、over()跟在后面的（必须有over才能用）
（1）LAG(col,n)：往前第 n 行数据； 只指一条数据 col哪一列 还可以在n后边在加个参数
如果第一个数据的前面是没有数据的，就把这个参数就是填在第一行 LAG(col,n,p)
例子：select name,orderdate,cost,
lag(orderdate,1,‘1900-01-01’) over(partition by name order by
orderdate ) as time1, 就是所有orderdate位置的数据写的都是前1天
lag(orderdate,2) over (partition by name
order by orderdate) as time2
from business;
就是所有orderdate位置的数据写的都是前2天
（2）LEAD(col,n)：往后第 n 行数据；——同理

partition by…order by=distrubute by … sort by

（3）NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从 1 开始，
对于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。
例子：
select * from (
select name,orderdate,cost, ntile(5) over(order by orderdate)
sorted
from business
) t
where sorted = 1;
ntile(5) 就是把一个数据集分成5份，order by orderdate是哪一部分数据集呢，按顺序的时间分成5份
也就是前20%的时间

6、rank 函数
RANK() 排序就是 1 1 3 4 排序相同时会重复，总数不会变
DENSE_RANK() 排序就是 1 1 2 3 排序相同时会重复，总数会减少 dense密集的意思
ROW_NUMBER() 排序就是 1 2 3 4 会根据顺序计算

思考：group by 用不了的情况才用over，一般是聚合函数，有相同人，需要按每个人来聚合后面的数，就不能用group by ,group by 就把相同的姓名的都合在一起了，但是要每个人单独显示就得用over了

实际用到over的案例，理解一下
（1）查询在 2017 年 4 月份购买过的顾客及总人数
（2）查询顾客的购买明细及购买总额
（3）上述的场景,要将 cost 按照日期进行累加
（4）查询顾客上次的购买时间
（5）查询前 20%时间的订单信息

就是要展示明细的同时还要聚合
group by 有时候等同于 over(partition by ) over在group by后进行

distinct去重是针对于全表的，用一个reducer处理，数据量大的时候不适合使用