hive知识点整理

1. 你知道哪些窗口函数及应用场景？

1.1 分析函数

• rank()：返回数据项在分组中的排名，排名相等会在名次中留空位
• dense_rank()：返回数据项在分组中的排名，排名相等不会在名次中留空位
• ntile()：返回分片后的值
• row_number()：编号，从1开始，为每条记录返回一个数字

row_number() over (partition by col1 order by col2)
根据col1分组，在分组内部根据col2排序，计算结果表示每组内排序后的顺序编号（编号组内连续且唯一）

# 在学生表中存在学号stu_id，科目subject_id，分数score三个字段，要求根据科目分组，显示各个科目的成绩排名
select stu_id, subject_id, score, row_numer() over(partition by subject_id order by score desc) rank
from student;

注意：order by asc时，null值排在末尾；order by desc时，null排在首位（默认null最大）。可以通过nulls last/nulls first控制。如：

rank() over(partition by col desc nulls last)

ntile(n) over(order by sum(col2) desc nulls last) from table group by col1
按层次查询，统计前1/n的sum(col2)，即把所有的数据按照col1分成n份，为n的那一份就是目标

# 统计分数累积在前1/2的科目信息
select subject_id, sum(score), ntile(2) over(order by sum(score) desc nulls last) til_col
from student
group by subject_id;

1.2 窗口函数

用于计算一定范围内、一定值域内、或者一段时间内的累积和以及移动平均值等。一般结合聚集函数sum()、avg()、first_value()、last_value()等使用。
first_value()：获取top1
last_vaule()：获取topn
lag()：获取相对于当前记录向前指定距离的那条记录的数据
lead()：获取相对于当前记录向后指定距离的那条记录的数据

• 计算累积和

# 统计1-12月的累积销量（1月为1月的值，12月为1-12月的值的和）
select
	month
	,sum(amount) as month_amount
	# sum(sum(amount))内部的sum(amount)为需要累加的值
	# order by month窗口范围内的排序
	# rows between unboundded preceding and current row 定义起点和终点
	# unbounded preceding为起点，表明从第一行开始
	# preceding表示前n行，following表示后n行
	,sum(sum(amount)) over(order by month rows between unbounded preceding and current row) as cumul_amount
from table
group by month
order by month;

• 按id排序，计算当前部门与上一个部门的累积和

# 按照部门ID大小排序，依次统计当前部门和之前一个部门的工资累积
select
	deptid
	,sum(salary) salary_amount
	,sum(sum(salary)) over(order by deptid asc rows between 1 preceding and current row) as cumul_amount
from employee
group by deptid
order by deptid;

• 计算前3个月的和

sum(sum(amount)) over(order by month rows between 3 proceding and current row) as cumul_amount

• 计算前后一个月之间的和

sum(sum(amount)) over(order by month rows between 1 proceding and 1 following) as cumul_amount

• 窗体第一条

first_value(sum(amount)) over(order by month rows between 1 proceding and 1 following) as cumul_amount

• 窗体最后一条

last_value(sum(amount)) over(order by month rows between 1 proceding and 1 following) as cumul_amount

• 获取前一条

lag(col1, 1) over(order by col2)

• 获取后一条

lead(col,1) over(order by col2)

• 获取一年中销量最高和最低的月份

SELECT  
	MIN(month) KEEP(DENSE_RANK FIRST ORDER BY SUM(amount)) AS highest_sales_month,  
	MIN(month) KEEP(DENSE_RANK LAST ORDER BY SUM(amount)) AS lows_sales_month  
FROM table_name  
GROUP BY month  
ORDER BY month; 

2. 内部表和外部表的区别

• 建表不同：内部表未被external修饰；外部表被external修饰
• 数据管理方不同：内部表由hive自身管理；外部表由HDFS管理
• 存储位置不同：内部表存储位置是hive.metastore.warehouse.dir(默认：/user/hive/warehouse)；外部表存储位置由自己制定(若为加location，hive将在hdfs的/user/hive/metastore文件夹下以外部表名创建一个文件夹，并将属于这个表的数据存放在这里)
• 删除不同：内部表会直接删除元数据和数据；外部表只会删除元数据，hdfs的文件并不会被删除
• 修改不同：内部表的修改会将修改直接同步给元数据；对外部表的表结构和分区进行修改，需要修改（MSCK REPAIR TABLE tablename;）

参考博文

3. 分区表

hive的表对应HDFS上指定目录，查询数据时默认扫描全表，这样时间和性能消耗都非常大。分区为HDFS上表目录的子目录，数据按照分区存储在子目录中，当查询条件包含分区时，则直接从该分区子目录去查找。合理的分区设计可以极大提高查询速度和性能。

分区提供了一个隔离数据和优化查询的可行方案，但是并非所有的数据集都可以形成合理的分区，分区的数量也不是越多越好，过多的分区条件可能导致很多分区上没有数据。同时，hive会限制动态分区可以创建的最大分区数，用来避免过多分区文件对文件系统产生负担。

4. 分桶表

桶为表目录下的具体文件。分桶的实质是对指定字段做了hash散列，然后存放到对应文件中，向分桶表中插入数据必然要通过MapReduce，且Reducer的数量必须等于分桶的数量。不能使用load加载数据，应使用Create table as select的方式，因为CTAS操作会触发MapReduce。

参考博文

5. 视图

视图时一组数据的逻辑表示，本质上就是一条select语句的结果集。

• 视图是只读的，不能用作load/insert/alter
• 创建视图时，视图就已经固定，对基表的后续更改（添加列等）不会反映在视图上
• 删除基表并不会删除视图，但是视图不能被使用了，需要手动删除视图
• 创建视图时，未提供列名，则将从select语句中自动派生列名
• 创建视图时，若select语句中包含其他表达式，列名称将以_C1、_C2等形式生成

6. 索引

hive 0.7.0引入索引功能，用于提高表某些列的查询速度。在指定列上建立索引，会产生一张索引表，里边的字段包括：索引列的值、该值对应的HDFS文件路径、该值在文件中的偏移量。在查询涉及到索引字段时，首先到索引表查找索引列值对应的HDFS文件路径及偏移量，避免全表扫描。

• 删除索引会删除对应的索引表；删除存在索引的表，则对应的索引和索引表都会被删除
• 创建索引后，索引表时没有数据的，需要重建索引才会有索引的数据
• 默认情况下，虽然建立了索引，但是hive在查询的时候是不会自动去使用索引的，需要开启相关配置，开启配置后，涉及到索引列查询时就会使用索引功能去优化查询
• 索引表最主要的一个缺陷：索引表无法自动rebuild，即若表中有数据新增或删除，则必须手动手动rebuild，重新执行MapReduce作业，生成索引表数据
• hive 3.0移除索引功能，主要原因：
  • Hive 2.3.0 增加了对物化视图的支持，在 3.0 之后正式引入，可以产生与索引相似的效果
  • 使用列式存储文件格式（parquet、ORC）进行存储时，支持选择性扫描，可以跳过不需要的文件或块

参考博文

7. 数据查询

7.1 group by

• hive.map.aggr控制如何聚合：默认false；设置为true时，会在map阶段执行一次聚合，提高聚合效率的同时会消耗更多内存
• 对分组数据过滤用having，用于group by之后

7.2 order by/sort by/distribute by/cluster by区别

可以对整型和字符串排序：若整型，则按照大小排序；若字符串，则按照字典排序

• order by
  order by 会对数据进行全局排序,和oracle和mysql等数据库中的order by 效果一样，它只在一个reduce中进行所以数据量特别大的时候效率非常低。而且当设置 ：set hive.mapred.mode=strict的时候不指定limit，执行select会报错，如下：
  LIMIT must also be specified。
• sort by
  sort by 是单独在各自的reduce中进行排序，所以并不能保证全局有序，一般和distribute by 一起执行，而且distribute by 要写在sort by前面。
  如果mapred.reduce.tasks=1和order by效果一样，如果大于1会分成几个文件输出每个文件会按照指定的字段排序，而不保证全局有序。
  sort by 不受 hive.mapred.mode 是否为strict ,nostrict 的影响。
• distribute by
  DISTRIBUTE BY 控制map 中的输出在 reducer 中是如何进行划分的。使用DISTRIBUTE BY 可以保证相同KEY的记录被划分到一个Reduce 中。
• cluster by
  distribute by 和 sort by 合用就相当于cluster by，但是cluster by 不能指定排序为asc或 desc 的规则，只能是升序排列。

参考博文

几种join的区别

• inner join内连接
  多张表进行内连接操作时，只有所有表与on条件中相匹配的数据才会显示。