当前位置:Gxlcms > mysql > Hive优化提示-如何写好HQL

Hive优化提示-如何写好HQL

时间:2021-07-01 10:21:17 帮助过:27人阅读

尽量将小表放在join的左边,我们这边使用的hive-0.12.0,所以是自动转化的,既把小表自动装入内存,执行map side join(性能好), 这

一、 Hive join优化

1. 尽量将小表放在join的左边,我们这边使用的hive-0.12.0,所以是自动转化的,既把小表自动装入内存,执行map side join(性能好), 这是由参数hive.auto.convert.join=true 和hive.smalltable.filesize=25000000L)参数控制(默认是25M),如果表文件大小在25M左右,,可以适当调整此参数,进行map side join,避免reduce side join。 也可以显示声明进行map join:特别适用于小表join大表的时候,SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a join b on a.key = b.key

2. 注意带表分区的join, 如:

SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key) WHERE a.dt='2014-08-07' AND b.dt='2014-08-07'

因为hive是先join再where的,所以如果在b中找不到a表的记录,b表中的所以列都会列出null,包括ds列,这样left outer的查询结果与where子句无关了,解决办法:

SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key AND a.dt='2014-08-07' AND b.dt='2014-08-07'')

3. 怎样写exist/in子句?

Hive不支持where子句中的子查询,SQL常用的exist in子句需要改写。这一改写相对简单。考虑以下SQL查询语句:

SELECT a.key, a.value FROM a WHERE a.key in (SELECT b.key FROM B);

可以改写为

SELECT a.key, a.value FROM a LEFT OUTER JOIN b ON (a.key = b.key) WHERE b.key <> NULL;

一个更高效的实现是利用left semi join改写为:

SELECT a.key, a.val FROM a LEFT SEMI JOIN b on (a.key = b.key);

4. Hive join只支持等值连接,不支持非等值连接。

5. 合理的使用map join, 场合:小表A join 大表,

基于Hadoop集群的Hive安装

Hive内表和外表的区别

Hadoop + Hive + Map +reduce 集群安装部署

Hive本地独立模式安装

Hive学习之WordCount单词统计

Hive运行架构及配置部署

二、 合理设置map与reduce的个数。

1、如何合并小文件,减少map数?

如果一个表中的map数特别多,可能是由于文件个数特别多,而且文件特别小照成的,可以进行如下操作,合并文件,:

set mapred.max.split.size=100000000; // 100M

set mapred.min.split.size.per.node=100000000;

set mapred.min.split.size.per.rack=100000000;

set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; // 合并小文件

2、如何适当的增加map数?

如果表A只有一个文件,大小为120M,包含几千万记录,可以考虑用多个map任务完成

set mapred.reduce.tasks=10;

create table a_1 as

select * from a

distribute by rand(123); //将a表的记录,随机的分散到包含10个文件的a_1表中

3、hive如何确定reduce数, reduce的个数基于以下参数设定:

hive.exec.reducers.bytes.per.reducer(每个reduce任务处理的数据量,默认为1000^3=1G)

hive.exec.reducers.max(每个任务最大的reduce数,默认为999)

计算reducer数的公式很简单N=min(参数2,总输入数据量/参数1)

即,如果reduce的输入(map的输出)总大小不超过1G,那么只会有一个reduce任务;所以调整以下参数:

set hive.exec.reducers.bytes.per.reducer=500000000; (500M)

set mapred.reduce.tasks = 15;

三、 如果设计和使用bucket,

Buckets 对指定列计算 hash,根据 hash 值切分数据,目的是为了并行,每一个 Bucket 对应一个文件。 将 user 列分散至 32 个 bucket, 首先对 user 列的值计算 hash,对应 hash 值为 0 的 HDFS 目录为:/wh/pvs/dt=2014-08-01/ctry=US/part-00000; hash 值为 20 的 HDFS 目录为:/wh/pvs/dt=2014-08-01/ctry=US/part-00020

所用场合:对某一列进行分区,比如对用户ID进行分区,例如:

CREATE TABLE weblog (user_id INT, url STRING, source_ip STRING)

> PARTITIONED BY (dt STRING)

> CLUSTERED BY (user_id) INTO 96 BUCKETS; // 按照日期分区后,再按照user_id把日志放在96个篮子里。插入数据的时候:

hive> SET hive.enforce.bucketing = true;

hive> FROM raw_logs

> INSERT OVERWRITE TABLE weblog

> PARTITION (dt='2009-02-25')

> SELECT user_id, url, source_ip WHERE dt='2009-02-25'

四、 Count(distinct)

当count distinct 的记录非常多的时候,设置以下两个参数:

hive> hive.map.aggr = true

hive> set hive.groupby.skewindata=true;

hive> select count (distinct gid) from cookie_label_summary where i_date=20130924;

五、 Group by

Group By的方法是在reduce做一些操作,这样会导致两个问题:

map端聚合,提前一部分计算:hive.map.aggr = true 同时设置间隔:hive.groupby.mapaggr.checkinterval

均衡处理:hive.groupby.skewindata

这是针对数据倾斜的,设为ture的时候,任务的reduce会把原来一个job拆分成两个,第一个的job中reduce处理处理不同的随即分发过来的key的数据,生成中间结果,再由最后一个综合处理。

六、 Order by, Sort by ,Dristribute by,Cluster By

1、 order by VS Sort by: order by是在全局的排序,只用一个reduce去跑,所以在set hive.mapred.mode=strict 模式下,order by 必须limit,否则报错。Sort by只保证同一个reduce下排序正确。

2、 Distribute by with sort by: Distribute by 是按指定的列把map 输出结果分配到reduce里。所以经常和sort by 来实现对某一字段的相同值分配到同一个reduce排序。

3、 Cluster by 实现了Distribute by+ sort by 的功能

Hive 的详细介绍:请点这里
Hive 的下载地址:请点这里

本文永久更新链接地址:

linux

人气教程排行