HIVE中join、semijoin、outerjoin举例详解
举例⼦：
hive> select * from zz0;
111111
222222
888888
hive> select * from zz1;
111111
333333
444444
888888
hive> select * from zz0 join zz1 on zz0.uid = zz1.uid;
111111  111111
888888  888888
hive> select * from zz0 left outer join zz1 on zz0.uid = zz1.uid;
111111  111111
222222  NULL
888888  888888
hive> select * from zz0 right outer join zz1 on zz0.uid = zz1.uid;
NULL
111111  111111
NULL    333333
NULL    444444
888888  888888
hive> select * from zz0 full outer join zz1 on zz0.uid = zz1.uid;
NULL
111111  111111
222222  NULL
NULL    333333
NULL    444444
888888  888888
hive> select * from zz0 left semi join zz1 on zz0.uid = zz1.uid;
111111  111111
888888  888888
写好Hive 程序的五个提⽰
使⽤Hive可以⾼效⽽⼜快速地编写复杂的MapReduce查询逻辑。但是某些情况下，因为不熟悉数据特性，或没有遵循Hive的优化约
定，Hive计算任务会变得⾮常低效，甚⾄⽆法得到结果。⼀个”好”的Hive程序仍然需要对Hive运⾏机制有深⼊的了解。
有⼀些⼤家⽐较熟悉的优化约定包括：Join中需要将⼤表写在靠右的位置；尽量使⽤UDF⽽不是transfrom……诸如此类。下⾯讨论5个性能和逻辑相关的问题，帮助你写出更好的Hive程序。
全排序
Hive的排序关键字是SORT BY，它有意区别于传统数据库的ORDER BY也是为了强调两者的区别–SORT BY只能在单机范围内排序。考虑以下表定义：
CREATE TABLE if not exists t_order(
id int, -- 订单编号
sale_id int, -- 销售ID
customer_id int, -- 客户ID
product _id int, -- 产品ID
amount int -- 数量
) PARTITIONED BY (ds STRING);
在表中查询所有销售记录，并按照销售ID和数量排序：
duce.tasks=2;
Select sale_id, amount from t_order
Sort by sale_id, amount;
这⼀查询可能得到⾮期望的排序。指定的2个reducer分发到的数据可能是（各⾃排序）：
Reducer1：
Sale_id | amount
0 | 100
1 | 30
1 | 50
2 | 20
Reducer2：
Sale_id | amount
0 | 110
0 | 120
3 | 50
4 | 20
因为上述查询没有reduce key，hive会⽣成随机数作为reduce key。这样的话输⼊记录也随机地被分发到不同reducer机器上去了。为了保
证reducer之间没有重复的sale_id记录，可以使⽤DISTRIBUTE BY关键字指定分发key为sale_id。改造后的HQL如下：
duce.tasks=2;
Select sale_id, amount from t_order
Distribute by sale_id
Sort by sale_id, amount;
这样能够保证查询的销售记录集合中，销售ID对应的数量是正确排序的，但是销售ID不能正确排序，原因是hive使⽤hadoop默认
的HashPartitioner分发数据。
这就涉及到⼀个全排序的问题。解决的办法⽆外乎两种：
1.) 不分发数据，使⽤单个reducer：
duce.tasks=1;
这⼀⽅法的缺陷在于reduce端成为了性能瓶颈，⽽且在数据量⼤的情况下⼀般都⽆法得到结果。但是实践中这仍然是最常⽤的⽅法，原因是通常排序的查询是为了得到排名靠前的若⼲结果，因此可以⽤limit⼦句⼤⼤减少数据量。使⽤limit n后，传输到reduce端（单机）的数据记录数就减少到n* （map个数）。
2.) 修改Partitioner，这种⽅法可以做到全排序。这⾥可以使⽤Hadoop⾃带的TotalOrderPartitioner（来⾃于Yahoo!的TeraSort项⽬），这是⼀个为了⽀持跨reducer分发有序数据开发的Partitioner，它需要⼀个SequenceFile格式的⽂件指定分发的数据区间。如果我们已经⽣成了这⼀⽂件（存储在/tmp/range_key_list，分成100个reducer），可以将上述查询改写为
duce.tasks=100;
set hive.mapred.partitioner=org.apache.hadoop.mapred.lib.TotalOrderPartitioner;
der.partitioner.path=/tmp/ range_key_list;
Select sale_id, amount from t_order
Cluster by sale_id
Sort by amount;
有很多种⽅法⽣成这⼀区间⽂件（例如hadoop⾃带的o.a.h.mapreduce.lib.partition.InputSampler⼯具）。这⾥介绍⽤Hive⽣成的⽅法，例如有⼀个按id有序的t_sale表：
CREATE TABLE if not exists t_sale (
id int,
name string,
loc string
);
则⽣成按sale_id分发的区间⽂件的⽅法是：
create external table range_keys(sale_id int)
row format serde
'org.apache.hadoop.hive.serde2.binarysortable.BinarySortableSerDe'
stored as
inputformat
'org.apache.hadoop.mapred.TextInputFormat'
outputformat
'org.apache.hadoop.hive.ql.io.HiveNullValueSequenceFileOutputFormat'
location '/tmp/range_key_list';
insert overwrite table range_keys
select distinct sale_id
from source t_sale sampletable(BUCKET 100 OUT OF 100 ON rand()) s
sort by sale_id;
⽣成的⽂件（/tmp/range_key_list⽬录下）可以让TotalOrderPartitioner按sale_id有序地分发reduce处理的数据。区间⽂件需要考虑的主要问题是数据分发的均衡性，这有赖于对数据深⼊的理解。
怎样做笛卡尔积？group by的用法及原理详解
当Hive设定为严格模式（de=strict）时，不允许在HQL语句中出现笛卡尔积，这实际说明了Hive对笛卡尔积⽀持较弱。因为不到Join key，Hive只能使⽤1个reducer来完成笛卡尔积。
当然也可以⽤上⾯说的limit的办法来减少某个表参与join的数据量，但对于需要笛卡尔积语义的需求来说，经常是⼀个⼤表和⼀个⼩表
的Join操作，结果仍然很⼤（以⾄于⽆法⽤单机处理），这时MapJoin才是最好的解决办法。
MapJoin，顾名思义，会在Map端完成Join操作。这需要将Join操作的⼀个或多个表完全读⼊内存。
MapJoin的⽤法是在查询/⼦查询的SELECT关键字后⾯添加/*+ MAPJOIN(tablelist) */提⽰优化器转化为MapJoin（⽬前Hive的优化器不能⾃动优化MapJoin）。其中tablelist可以是⼀个表，或以逗号连接的表的列表。tablelist中的表将会读⼊内存，应该将⼩表写在这⾥。
PS：有⽤户说MapJoin在⼦查询中可能出现未知BUG。在⼤表和⼩表做笛卡尔积时，规避笛卡尔积的
⽅法是，给Join添加⼀个Join key，原理很简单：将⼩表扩充⼀列join key，并将⼩表的条⽬复制数倍，join key各不相同；将⼤表扩充⼀列join key为随机数。
怎样写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);
left semi join是0.5.0以上版本的特性。
Hive怎样决定reducer个数？
Hadoop MapReduce程序中，reducer个数的设定极⼤影响执⾏效率，这使得Hive怎样决定reducer个数成为⼀个关键问题。遗憾的是Hive的估计机制很弱，不指定reducer个数的情况下，Hive会猜测确定⼀个reducer个数，基于以下两个设定：
1. ducers.ducer（默认为1000^3）
2. ducers.max（默认为999）
计算reducer数的公式很简单：
N=min(参数2，总输⼊数据量/参数1)
通常情况下，有必要⼿动指定reducer个数。考虑到map阶段的输出数据量通常会⽐输⼊有⼤幅减少，因此即使不设定reducer个数，重设参数2还是必要的。依据Hadoop的经验，可以将参数2设定为0.95*(集中TaskTracker个数)。
合并MapReduce操作
Multi-group by
Multi-group by是Hive的⼀个⾮常好的特性，它使得Hive中利⽤中间结果变得⾮常⽅便。例如，
FROM (SELECT a.status, b.school, b.gender
FROM status_updates a JOIN profiles b
ON (a.userid = b.userid and
a.ds='2009-03-20' )
) subq1
INSERT OVERWRITE TABLE gender_summary
PARTITION(ds='2009-03-20')
der, COUNT(1) GROUP der
INSERT OVERWRITE TABLE school_summary
PARTITION(ds='2009-03-20')
SELECT subq1.school, COUNT(1) GROUP BY subq1.school
上述查询语句使⽤了Multi-group by特性连续group by了2次数据，使⽤不同的group by key。这⼀特性可以减少⼀次MapReduce操作。
Multi-distinct
Multi-distinct是淘宝开发的另⼀个multi-xxx特性，使⽤Multi-distinct可以在同⼀查询/⼦查询中使⽤多个distinct，这同样减少了多
次MapReduce操作。
摘⾃：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。