本文导读

Hive数据排序语法建议
join on语法使用建议
多表union all的视图使用建议
级联修改表字段建议
分区操作建议
with as语句使用建议
导入或插入Hive表数据建议
Hive表数据查询建议
percentil_approx函数使用建议

展开导读

文档首页/ MapReduce服务 MRS/ 组件应用开发规范/ Hive应用开发规范/ Hive SQL开发规范

Hive SQL开发规范

更新时间：2025-02-28 GMT+08:00

查看PDF

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中
关联查询时副表的分区过滤条件写在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类型的条件进行查询。
不建议使用浮点数进行运算
 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含义整改，分区不为空则扫描所有分区，分区多文件多的情况下可能导致服务异常，建议指定分区扫描，如果必须全表扫描，可以选用不指定分区的方式进行查询规避。

percentil_approx函数使用建议

Hive SQL命令中，使用percentile_approx udf函数输出字段，Map阶段进行了部分聚合以及过滤条件下推，Reduce阶段则是将Map聚合的结果合并，如果percentil_approx处理的字段过多，可能会导致Reduce阶段失败。因此，建议如下：

可通过以下命令设置超时时间：
set mapreduce.task.timeout=7200000;
关闭Map端聚合，所有逻辑放到Reduce进行。Map端聚合只减少了shuffle过程数据量，但Map不聚合，Reduce不用执行耗时的合并逻辑。
set hive.map.aggr=false;
找出percentil_approx处理字段中倾斜的字段值，单独处理，整改SQL。

父主题： Hive应用开发规范

上一篇：Hive应用开发建议

下一篇：Hudi应用开发规范

意见反馈

文档内容是否对您有帮助？

有帮助没帮助

提供反馈

提交成功！非常感谢您的反馈，我们会继续努力做到更好！您可在我的云声建议查看反馈及问题处理状态。

系统繁忙，请稍后重试

在使用文档中是否遇到以下问题

内容与产品页面不一致

内容不易理解

缺失示例代码

步骤不可操作

搜不到想要的内容

缺少最佳实践

意见反馈（选填）

0/500

请至少选择一项反馈信息并填写问题反馈

字符长度不能超过500

直接提交取消

如您有其它疑问，您也可以通过华为云社区问答频道来与我们联系探讨

智能客服提问云社区提问

Hive SQL开发规范

Hive数据排序语法建议

join on语法使用建议

多表union all的视图使用建议

级联修改表字段建议

分区操作建议

with as语句使用建议

导入或插入Hive表数据建议

Hive表数据查询建议

percentil_approx函数使用建议

相关文档

意见反馈

文档内容是否对您有帮助？

7*24

备案

专业服务

退订

建议反馈

售前咨询热线

文档反馈