hivesql优化

1. 业务处理优化原则

• Hive对复杂SQL没有关系形数据库表现好，业务过程可使用临时表使用过程更清晰简单；
• 关联条件尽可能避免使用函数、正则表达式。
• 使用MAPJOIN()会将较小的表 加载到内存中，使连结过程在Map阶段完成
• 分区避免数据倾斜问题。
• jobs数比较多的作业运行效率相对比较低。
• 可选择tez方式加快小数据量的查询速度。
• 不要求全局有序时使用Distribute By + Sort By来排序
• 如果全局有序是为了得到排名靠前的结果，使用limit子句来减少数据量。
• 使用create EXTERNAL table if not exists …. LOCATION …
  ‘/OSSDATA…’这种方式建表，hiveserver2在执行建表语句时会检查映射的目录对象，当映射的hdfs目录子目录和文件过多时（如超过10万个），检查这些对象就比较耗时，会导致Hive解析卡住。

2. Hive典型分区失效样例

• 对于资源消耗，可以通过以下监控界面评估（map数多、分配内存大）：

  1、yarn监控：http://...:8088
  2、jobhistory监控： http://...:19888

• 典型样例1：

原sql，全表扫描消耗大量资源，执行时长70分钟:

select * from hw.V_ODS_SE_UR_XDR_CLP_GM_MW_H limit 1; 

优sql，只扫描特定分区数据，执行时长2.5分钟：

select * from hw.V_ODS_SE_UR_XDR_CLP_GM_MW_H where data_date='20180614' and data_hour='01' limit 1;

• 典型样例2，分区字段为day:

原sql:

where substr(a.day,1,6)='$MONTH'

优化后sql：

where a.day between '$MONTH'01 and  '$MONTH'31