常用sql函数+sql优化+数据倾斜

sql优化以及shell脚本相关知识点汇总

实时更新！

常用sql函数：
decode（）(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)
rlike  like  REGEXP  正则匹配
列转行：concat_ws(',',collect_set(column))  
说明：collect_list 不去重，collect_set +group
行转列：
select user_id,order_value,order_id
from lie_col
lateral view explode(split(order_value,',')) num as order_id
开窗：
row_number()会生成数据项在分组中的排名，排名即便相等也不会有并列排名，相同排名随机排序。
rank()可以生成数据项在分组中的排名，排名相等时会产生并列排名，然后会在名次中留下空位。应用场景不多，很少需要使用，了解即可。
dense_rank()可以生成数据项在分组中的排名，排名相等时会产生并列排名，但不会在名次中留下空位。应用场景也不多，很少需要使用，了解即可dense_rank()可以生成数据项在分组中的排名，排名相等时会产生并列排名，但不会在名次中留下空位。应用场景也不多，很少需要使用，了解即可
排序
rows
range
between 
---sql优化
1、避免笛卡尔积 不止有null  截取的时候还有空值
举例：做身份证统计的时候，为了更加准确加了trim，然后截取忘记在身份证加trim ，group by 后发现存在大量空值
--一个是去除空值  一个是对数据进行预加工
2、join 的时候 on 加入删选条件 将null去掉  尽量将条件先on掉  不然运算的时候中间表的数据量过大
3、建分桶的时候分桶个数要为质数-保证简单的hashteble的效率不会太低、以分桶字段做查询的时候加快速度
4、存过要取的数据次数过多（且数据量大），建立临时表，完事给drop掉
5、存过里面的temporary表不能直接创建 一开始用with as 但是with as 只能使用一次  excute immediate+create  且注意之后的sql用到此表都不好用 也要加强制执行
6、 decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值) 
7、正则匹配前几位的时候不要截取，直接用正则表达式匹配
8、行列转换使用函数：行转列 (根据主键，对某列进行合并) concat_ws(',',collect_set(column))说明：collect_list 不去重，collect_set 去重
列转行 (对某列拆分，形成新列)lateral view explode(split(column, ',')) num
9、trim 只能用string 类型的数据
10、为了查找null or not null 数据 加入trim 将空格一并删选出
11、hive没有~进行正则而 pg跟mysql有  hive只能用rlike跟regexp
12、数据类型不一致的时候容易造成数据倾斜
13、存在left join 的时候，不能确保join之后的是原有的数据就是null 还是join不到数据产生的null 此时用if来区分
14、大表join小表 可以map join 小表  也可以改为小表join大表
15 group by 的数据倾斜 可以使用set hive.groupby.skewindata=true; 负载均衡；
16、会遇到存过里面多个sql，此时把加入开始结束时间来分析是哪个sql导致存过过慢的
17、https://www.cnblogs.com/qiuhong10/p/8119546.html  null 跟空字符串的不一致 ALTER TABLE b SET SERDEPROPERTIES ('serialization.null.format'='');
18、遇到要查表里有没有的记得用exists，不要关联找null
//数据倾斜
1>参数调节：
开启map端合并（combine操作） hive.map.aggr=true
开启负载均衡 hive.groupby.skewindata=true
2>SQL语句调节：
增加reduce个数
如果数据中出现了多个大key，增加reduce个数，可以让这些大key落到同一个reduce的概率小很多
1)两个表join的时候选用key分布最均匀的表作为驱动表，做好列裁剪和过滤操作。
2)小表Join大表： 使用mapjoin让小表进内存，在map阶段和大表匹配，省去了reduce阶段
如：select /*+ MAPJOIN(a) */ a.c1, b.c1 ,b.c2 from a join b where a.c1 = b.c1;
3)大表Join大表：把空值的Key变成一个字符串加上一个随机数，随机的分发到不同的reduce上。
如：select * from log a left outer join users b on
case when a.user_id is null
then concat(‘hive’,rand() )
else a.user_id end = b.user_id;
4)Count distinct大量数据时，将值为空的情况单独处理，比如可以直接过滤空值的行，在最后结果中加1。Group by 维度过小时，使用Sum和GROUP BY 代替COUNT(DISTINCT) 操作 //COUNT(DISTINCT)只会产生一个Reducetask