# 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.xx* x**=** *b.xx* x形式进行两表关联，而是写成*a,b* **where** *a.xxx* **=** *b.xxx*形式，会笛卡尔积导致任务运行缓慢。 该问题可通过执行计划进行确认，该类SQL join的task中无key值，例如：
  图1查看任务执行计划   
  ![](https://support.huaweicloud.com/devg-rule-mrs/zh-cn_image_0000002186797105.png)
  因此，建议如下：
  - 修改Hive SQL命令，使用标准的**join on**语法。
  
  - 部分场景在设置"hive.cbo.enable"为"true"开启CBO功能时，*a,b* **where** *a.xxx* =*b.xxx* 形式的执行计划可转化为非笛卡尔积，可再次通过执行计划确认keys是否有值，如果有值则不存在该问题，例如：
    图2任务运行正常   
    ![](https://support.huaweicloud.com/devg-rule-mrs/zh-cn_image_0000002151455614.png) 
   
- 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** 中
  关联查询时副表的分区过滤条件写在**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 VIEW '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 columns 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类型的条件进行查询。
  
- 不建议使用浮点数进行运算 Hive SQL命令中的double或float类型数据，需转换为decimal类型再进行操作。
  
- 不建议使用**in** 子查询作为分区过滤条件
  使用**in** 子查询方式作为条件过滤分区时，分区需要从另一个查询中才能得到具体值，在生成执行计划时是不知道具体分区值，无法下推，就会执行全表扫描。例如，以下命令中的**cp**为分区字段：
  ```
  select * from table_1 where cp in (select id from table_2);
  ```
  因此，建议整改SQL命令。
  
- 分区的过滤条件中带**\<\> ''** 方式查询建议
  Hive SQL命令中分区字段为string类型且过滤条件中带**\<\> ''** 编译时，在查询DBService获取需要扫描的元数据分区时，由于GaussDB查询**!= ''**空字符串时返回结果错误，导致该SQL不会查任何一个分区，最终所有分区都没有扫描，查询无数据。
  因此，建议按SQL含义整改，分区不为空则扫描所有分区，分区多文件多的情况下可能导致服务异常，建议指定分区扫描，如果必须全表扫描，可以选用不指定分区的方式进行查询规避。
  
 
#### percentile_approx函数使用建议
Hive SQL命令中，使用**percentile_approx udf**函数输出字段，Map阶段进行了部分聚合以及过滤条件下推，Reduce阶段则是将Map聚合的结果合并。如果percentile_approx处理的字段过多，可能会导致Reduce阶段失败。
因此建议如下：
- 可通过以下命令设置超时时间：
  ```
  set mapreduce.task.timeout=7200000;
  ```
  
- 关闭Map端聚合，所有逻辑放到Reduce进行。Map端聚合只减少了shuffle过程数据量，但Map不聚合，Reduce不用执行耗时的合并逻辑。
  ```
  set hive.map.aggr=false;
  ```
  
- 找出percentile_approx处理字段中倾斜的字段值，单独处理，整改SQL语句。