Hive SQL开发规范
Hive数据排序语法建议
- partition by和order by使用建议
Hive SQL命令中包含over(partition by order by)语法，且partition by和order by后的字段一致，导致MapReduce任务运行缓慢。
partition by、order by的语法含义为分组后排序组内数据，使用的是快速排序算法，partition by和order by字段一致会导致分区内全为相同数据，排序性能严重下降，且字段相同排序无意义。因此，建议如下：
- 修改partition by与order by的字段为不同的字段值。
- 删除order by参数。
- 禁止使用order by null进行排序
order by null为MySQL用于取消group by中隐式排序的功能，Hive不支持该操作，会导致group by后两层group by分组按不排序的结果进行分组，结果不符合预期。
- row_number over ()使用建议
建议使用唯一能标识一行数据的字段做排序字段。
row_number对排序后的数据打标签，取标签为1的数据，但排序时未考虑排序字段完全一致的场景，排序字段一致的两条数据多次排序顺序可能不同，导致SQL多次结果不同。
join on语法使用建议
- 使用标准join on语法
Hive SQL命令中未使用标准a join b on a.xxx=b.xxx形式进行两表关联，而是写成a,b where a.xxx=b.xxx形式，会笛卡尔积导致任务运行缓慢。 该问题可通过执行计划进行确认，该类SQL join的task中无key值，例如：
图1 查看任务执行计划
因此，建议如下：
- 修改Hive SQL命令，使用标准的join on语法。
- 部分场景在设置“hive.cbo.enable”为“true”开启CBO功能时，a,b where a.xxx=b.xxx形式的执行计划可转化为非笛卡尔积，可再次通过执行计划确认keys是否有值，如果有值则不存在该问题，例如：
图2 任务运行正常
- join on语法中不建议包含or
Hive SQL命令中join on条件包含or，执行join时会没有key导致产生笛卡尔积。建议整改SQL语句，可以使用union替换。
- 不建议join存在大量重复数据的两个表
join的两表关联的key值字段存在大量重复数据，会产生类似笛卡尔积，导致写出数据膨胀，任务运行慢。
建议查询两表join on条件字段值的分布情况，可以将大key提取出来进行单独处理，结果集使用union all进行拼接执行。
- 不建议join on中加判断语句
join on条件中存在if或case when判断，在进行mapjoin时，会拿大表数据一条一条地与小表进行比对，同时做判断，导致任务运行慢。例如：
select *
from tba t1
join tbb t2
on t1.id=
case where t1.type=’qwr’ THEN t2.type ELSE null END;
建议将SQL进行拆分。
- 未开启Hive CBO功能时，不建议join on中带不等于的条件和使用in进行过滤查询
- join on中不等于条件使用建议
开启CBO功能时，Hive支持join的on条件中存在不等于条件；如果未开启CBO功能时在join的on条件配置不等于条件，会导致所有执行结果都为Null且产生笛卡尔积。
建议开启CBO功能时在join的on条件中配置不等于条件，或整改SQL语句。
- join on中in条件使用建议
当“hive.cbo.enable”参数值为“false”（即未开启CBO功能）时，Hive SQL不支持多表关联过滤条件中按in的子查询进行过滤，left join对应右表的where过滤条件会丢失，例如：
创建表：
create table test101 (id string,id2 string);
create table test102 (id string,id2 string);
create table test103 (id string,id2 string);
create table test104 (id string,id2 string);
进行关联查询：
explain select * from test101 t1
left join test102 t2 on t1.id=t2.id
left join test103 t3 on t1.id=t3.id2
where t1.id in (select s.id from test104 s)
and t3.id2='123';
查看执行计划会发现t3的id2=123的过滤条件丢失。
因此，建议修改“hive.cbo.enable”参数值为“true”再重新执行对应的SQL。
- join on中不等于条件使用建议
- 关联查询的字段类型需保持一致
两个表进行关联查询时，建议关联的字段类型保持一致。如果关联字段类型不一致，关联前会做一个类型转换，导致时间变长，任务执行慢。
- 关联查询时副表的分区过滤条件写在join中
关联查询时副表的分区过滤条件写在left join后面，右表会进行全表扫描，查询慢。例如：
select t1.id
from student_p t1
left join test0617 t2
on t1.id=t2.id
where t1.pt_dt<'2022-02-25' and t2.pt_dt<'20220616';
副表（t2）表，where条件写在join后面，会导致先全表关联再过滤分区。虽然主表（t1）表分区条件也写在join后面，但是主表会谓词下推，先执行分区过滤再进行join，通过执行计划可以看到SQL扫描了副表不在过滤条件内的分区。
因此，建议将副表的分区过滤条件写在join中，则以上命令可修改为：
select t1.id
from student_p t1
left join test0617 t2
on t1.id=t2.id and t2.pt_dt<'20220616'
where t1.pt_dt<'2022-02-25';
多表union all的视图使用建议
视图定义为多表union all的情况下，在视图定义中的表分区数据类型不同时，在视图外指定分区条件不会下推条件到视图中的表，导致视图定义中的表在执行SQL时出现全表扫描。例如：
创建视图的命令为：
CREATE VTEW 'view 1' AS
select 'tb_1'.'id' from 'default'.'tb_1'
union all
select 'tb_2'.'id' from 'default'.'tb_2'
union all
select 'tb_3'.'id' from 'default'.'tb_3'
union all
select 'tb_4'.'id' from 'default'.'tb_4`;
查询命令如下（例如，tb_1-tb_4的分区条件都为cp）：
select * from view_1 where cp=xxx;
可以查看执行计划中是否有分区筛选条件，并且统计信息是否很多，有筛选且统计信息打印很多则为未下推。
union all使用建议如下：
- 视图定义中的表设置相同的分区字段类型。
- 视图中指定分区条件。
级联修改表字段建议
级联修改表字段，会将表分区的字段都进行修改，并在元数据库中启动事务新增分区数*字段数条记录，再删除之前的分区数*字段数条记录，在分区和字段多的情况下可能会出现修改超时，导致DBService异常。例如，以下SQL命令：
alter table tb_1 add cloumns id string cascade;
因此，建议如下：
- 修改命令不加cascade，不级联修改时，如果往历史分区插入数据，新增的字段由于无元数据，查询显示为null。
- 重新创建表，将历史数据重新导入新表。
分区操作建议
- 查询Hive表分区建议
Hive查询命令扫描的分区过多，元数据SQL拼接过长，导致任务报错，HiveServer日志或者客户端返回大量“part_name= ?”（每打印一个part_name即表示扫描了一个分区）。
建议整改Hive SQL，减少分区遍历量，当前发现2000分区以下可正常遍历。可通过explain authorization sql;打印执行计划查看遍历的分区是否小于2000分区。
- 不建议删除大量分区
删除分区会关联删除大量元数据表，例如Partitions、partition_params，可能会导致DBService异常。因此，建议分批删除分区，一次删除分区不超过1000个。
- 建议带分区查询大分区表
查询大分区表时，如果不带分区，会进行全表扫描，造成元数据和HDFS压力，查询缓慢，可能会导致Hiveserver FullGC报错。因此，建议带分区进行查询，多分区查询可配置以下HiveServer和MetaStore参数进行优化：
- 配置“hive.metastore.million.partition.optimizer.batch.retrieve.max”参数值为“1000”。
- 配置“hive.metastore.client.socket.timeout”为“86400”秒。
- 配置“hive.metastore.million.partition.optimizer.batch.delete.max”参数值为“1000”。
with as语句使用建议
大量使用with as并在SQL中多处调用会导致任务运行慢。with as语句不会物化，每次调用会执行一遍，多次调用且with as逻辑复杂的情况下会导致任务运行慢。
因此，建议将with as语句中的内容单独创建成临时表。
导入或插入Hive表数据建议
- insert into table values使用建议
不建议使用insert into table values形式插入大量数据，该种方式为把数据导出为insert into values的SQL语句，如果数据条数很多，会导致导出的SQL过长，导致HiveServer FullGC报错。
因此，建议使用常规的数据导入方式，例如先生成文本文件，再load到Hive表中。
- 禁止并发插入同表或同分区数据
Hive不支持并发插入同表或同分区数据，并发插入时多个任务会共同操作同一份临时文件，导致一个任务把另一个任务的文件移走，导致找不到临时文件报错。可修改对应SQL为串行运行。
- 导入文本数据建议
导入至Hive中的text表数据包含\n等特殊字符，会导致数据不一致，出现串列或多行等问题。text表在HDFS中存储为文本，\n等特殊字符会出现换行，因此：
- 建议整改SQL命令。
- 将表格式修改为ORC、Parquet等带Schema的格式，将\n当做数据存在字段中。
- 不建议使用insert overwrite覆写数据
由于insert overwrite操作为先在表目录下生成.hive-staging临时目录，在相关运算结束后会将原表数据清除，再将临时目录中数据移动到正式目录，完成整个SQL执行过程如果表数据清除时服务端出现异常，临时目录数据不会移动到正式目录，数据发生缺失，且数据可能无法补齐；或者存在相同的查询SQL并发执行，此时查询到的数据为空，中间结果为空，最终数据被清除。
因此建议整改SQL，通过临时表进行相关覆写操作，恢复数据还能从临时表中查询后再插入，同时限制并发执行任务。
- 往同一个表或同一个分区频繁插入数据建议
往同一个表或同一个分区频繁插入数据时，Movetask阶段loading data变慢。
MapReduce插入的文件名都为000000_0状文件，由于多次大量插入，为保障插入的文件名不重复，每次插入会检查文件名是否重复，如果重复则生成000000_0_copy_1状文件，由于多次插入存在大量copy文件，每次插入需要遍历所有的copy文件，文件过多导致loading data阶段变慢。
因此，建议对多次频繁插入的表或分区定期合并文件，减少copy后缀的文件数量。
Hive表数据查询建议
- 查询string类型字段建议
查询string类型字段的数据时应该带单引号，即格式为“字段名='字段取值'”。当string类型字段的条件不带引号时，会把字段转为int类型和条件做比较，而不是直接使用string类型和条件进行比较。
因此，业务须按照标准进行查询，即string类型的字段用string类型的条件进行查询。
- 不建议使用浮点数进行运算
- 不建议使用in子查询作为分区过滤条件
使用in子查询方式作为条件过滤分区时，分区需要从另一个查询中才能得到具体值，在生成执行计划时是不知道具体分区值，无法下推，就会执行全表扫描。例如，以下命令中的cp为分区字段：
select * from table_1 where cp in (select id from table_2);
因此，建议整改SQL命令。
- 分区的过滤条件中带<> ''方式查询建议
Hive SQL命令中分区字段为string类型且过滤条件中带<> ''编译时，在查询DBService获取需要扫描的元数据分区时，由于GaussDB查询!= ''空字符串时返回结果错误，导致该SQL不会查任何一个分区，最终所有分区都没有扫描，查询无数据。
因此，建议按SQL含义整改，分区不为空则扫描所有分区，分区多文件多的情况下可能导致服务异常，建议指定分区扫描，如果必须全表扫描，可以选用不指定分区的方式进行查询规避。
percentil_approx函数使用建议
Hive SQL命令中，使用percentile_approx udf函数输出字段，Map阶段进行了部分聚合以及过滤条件下推，Reduce阶段则是将Map聚合的结果合并，如果percentil_approx处理的字段过多，可能会导致Reduce阶段失败。因此，建议如下：
