hive sql / mysql 相关面试知识点

一、代码相关

--mysql累计分布窗口函数
cume_dist()  --当前行数/总行数
precent_rank() --(rank - 1) / (total_rows - 1)
--取某列第几个值
first_value()
last_value()
nth_value(cal,num) 从某一列第n行获取值
--分组窗口函数
ntile(num) 将数据分为特定数量的组，如果不平均，余n个数据，那排在前n的组就会各多一个

--hive列转行
concat_ws(',',collect_list(col)
concat_ws(',',collect_set(col)) 

--取交集 intersect
select customer_id from test_join_order 
Intersect 
select customer_id from user_info;

--取差集 minus
select customer_id from test_join_order 
Minus 
select customer_id from user_info;

--lateral view / explode用法

select(array('1','2','3')) --返回3行数据

select explode(map('A','1','B','2','C','3')) --返回两列三行，有对应关系

select explode(split('1,2,3',',')) --针对未拆分字段为string的情况

--使用explode函数有限制，不允许select中出现其他表达式，一般搭配lateral view使用
--tmp1为虚拟表的表明 order_id为拆分出来字段列名
select user_id -- 原字段
      ,user_layer -- 原字段
      ,ordr_id -- 拆分字段
from A
lateral view explode(ordr_set)tmp1 as ordr_id;

--poseexplode 对拆分出来的字段按照顺序打编号,如果有两列需要拆分切有对应关系，如下
select user_id -- 原字段
      ,user_layer -- 原字段
      ,ordr_id -- 拆分字段
from A
lateral view poseexplode(ordr_set)tmp1 as cn,ordr_id
lateral view poseexplode(ordr_set2)tmp2 as cn2,ordr_id2
where cn = cn2

--lateral view outer 
--对于某一行的未拆分字段为null，拆分后改行仍显示，拆分字段显示null

二、内部表 / 外部表

我们平时创建的表默认是内部表，内部表的数据是由hive管理，外部表的数据由hdfs管理
hive会管理内部表元数据和源数据的生命周期 ，管理外部表元数据的生命周期
元数据是指表的一些属性、信息、结构；源数据是指具体的数据内容（存储数据）

三、分区表 / 分桶表

分区表和分桶表是两种不同的数据组织方式，都用于优化查询性能和数据管理
分区表是按照表中的某一列进行划分，常见的有日期
分桶表是根据表中的某一列进行哈希，将数据分为指定的桶

从建表语句上：
分区表是partition by ，需要指定字段类型 
分桶表是clustered by , 需要制定桶的个数

从数量上看：
分区表的分区可以变化
分桶表的桶数一旦指定不会变化

从作用上看：
分区避免全表扫描，根据分区列查询指定目录提高查询速度
分桶表数据进行抽样和JOIN时可以提高MR程序效率

四、数据倾斜问题

数据倾斜主要在三中情况中会遇到： join \ group by \ count(distinct)

1. join 

1.1 大表 join 小表 
倾斜原因：
主要是热点key导致，相同的key会基于hash分发到同一个reduce算子上，导致join时倾斜。
解决方法：
开启map端join   set hive.auto.convert.join = true
– 开启该设置后，join时会在map端将小表缓存到内存中（缓存为hash table），在map端执行join。
– 小表默认是 1000行或者25Mb大小。可以通过设置参数提高小表的阈值 ：
set hive.mapjoin.smalltable.filesize=25000000;

1.2 大表 join 大表
倾斜原因：
存在热点key，或者大量的空key，导致倾斜
解决办法：
-开启对倾斜数据join的优化 set hive.optimize.skewjoin=true;
设置倾斜阈值 set hive.skewjoin.key=100000; 
（当join的key对应的记录条数超过100000，就认为这个key发生了数据倾斜，会对其分拆优化。）
开启编译时优化 set hive.optimize.skewjoin.compiletime=true； （Hive的查询优化器会在编译时尝试检测出可能的倾斜连接，并自动采取优化措施。将倾斜的键分布到多个reduce端进行处理，或者将倾斜数据移到map端进行连接）
开启union优化 set hive.optimize.union.remove=true;   （在没有开启优化的情况下，Hive会为两个SELECT语句分别生成执行计划，并可能分别进行数据的读取和处理。然后，结果会被合并。当开启优化后，Hive的查询优化器可能会识别出两个查询可以合并执行，从而减少数据的移动和处理步骤。例如，它可以将两个查询合并为一个查询，然后一次性读取和处理数据，最后返回结果。）
-join时剔除空置 或者 将空值转为随机的字符串

2.group by 倾斜
倾斜原因：
map阶段相同的key会分发到同一个reduce端聚合，热点key导致group by 聚合倾斜。
解决方法：
– 开启 map 端聚合： set hive.map.aggr = true; ，默认开启。这个设置会提前在map端聚合，减少数据传输及在reduce阶段的处理时间。
通过 set hive.groupby.mapaggr.checkinterval = 100000; 设置在map端聚合操作的条数。当大于该值时，才会进行map端聚合。
– 开启负载均衡： set hive.groupby.skewindata = true; ，该设置会在数据出现倾斜时，将一个group by 的执行计划拆分为两个阶段MR，第一阶段将key随机分发到不同reduce进行聚合，然后第二阶段MR会基于前一阶段预处理的结果，将相同的key分发到同一个reduce算子，执行聚合操作。

3.count(distinct )倾斜
倾斜原因：
所有key会分发到同一个reduce进行去重统计个数，数据量较大时导致运行时间过长。
解决方法：
将 count distinct 改写为 group by 的写法

五、hive优化参数

--开启动态分区，根据表字段自动将数据加入到指定分区
set hive.exec.dynamic.partition.mode=nonstrict;

--开启并行执行
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=16;