hive 表连接
目录
Hive Join
hive只支持等值连接，外连接。
hive不支持非相等的join条件（通过其他方式实现，如left outer join），因为它很难在map/reduce job实现这样的条件。
hive可以join两个以上的表。
两表join
建表导入数据
-- 创建表test_a
CREATE TABLE test_a(
id int,
name string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
-- 创建表test_b
CREATE TABLE test_b(
id int,
name string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
--创建表test_c
CREATE TABLE test_c(
id int,
name string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
--分别导入数据到三个表中
--test_a
1 a1
2 a2
4 a4
--test_b
1 b1
3 b3
4 b4
--test_c
1 c1
4 c4
5 c5
-- load数据
load data LOCAL INPATH '/home/user/d1/joindata/file1' overwrite into table test_a;
load data LOCAL INPATH '/home/user/d1/joindata/file2' overwrite into table test_b;
load data LOCAL INPATH '/home/user/d1/joindata/file3' overwrite into table test_c;
等值连接：inner join
select * from test_a a inner join test_b b on a.id=b.id;
Total MapReduce CPU Time Spent: 4 seconds 210 msec
OK
1 a1 1 b1
4 a4 4 b4
Time taken: 20.556 seconds, Fetched: 2 row(s)
外连接：left join 、right join
--left join
select a.*,b.* from test_a a
left join test_b b
on a.id=b.id;
Total MapReduce CPU Time Spent: 3 seconds 700 msec
OK
1 a1 1 b1
2 a2 NULL NULL
4 a4 4 b4
--right join
select a.*,b.* from test_a a
right join test_b b
on a.id=b.id;
Total MapReduce CPU Time Spent: 3 seconds 710 msec
OK
1 a1 1 b1
NULL NULL 3 b3
4 a4 4 b4
实现非等值连接
-- 查询test_a有，test_b没有，用 left join + is null
select * from test_a a left join test_b b on a.id = b.id where b.id is null;
Total MapReduce CPU Time Spent: 4 seconds 330 msec
OK
2 a2 NULL NULL
-- 查询test_a没有，test_b有，用 right join + is null
select * from test_a a right join test_b b on a.id = b.id where a.id is null;
Total MapReduce CPU Time Spent: 4 seconds 30 msec
OK
NULL NULL 3 b3
多表join
三表inner join
--三表inner join
select a.*,b.name,c.name from test_a a
inner join test_b b on a.id=b.id
inner join test_c c on b.id=c.id;
计算新增用户
查询1228 有而1117 没有的数据
select * from
(select * from uis_partition where dt='20141228') a
left join
(select * from uis_partition where dt='20141117') b
on a.aid = b.aid
where b.aid is null;
计算每个国家记录数的百分比
无关联字段时创造字段 自定义连接条件
round，默认取整，如果想精确，那就在后面加精确几位小数
select b.country,round((b.ct*100)/a.t,2) bfb from
(
-- 计算所有国家的用户数
select count(DISTINCT aid) t,'my' jc from user_install_status_orc
) a inner join
(
-- 计算每个国家的用户数
select country,count(DISTINCT aid) ct,'my' jc from user_install_status_orc GROUP BY country
-- 以自创的字段连接
) b on a.jc = b.jc;
新建字典表用于join
制作字典文件country_dict.dat
create table country_dict(
code string,
name string,
region string
);
加载数据
……
要避免的查询操作
--笛卡尔积的SQL
select * from test_a inner join test_b;
-- 设置hive的严格校验(建议 很麻烦 确实可以帮助我们进行数据上的查询失误)
set hive.mapred.mode=strict;
设置这个参数，可以限制以下情况：
1）限制执行可能形成笛卡尔积的SQL；
2）partition表使用时不加分区；
3）order by全局排序的时候不加limit的情况；
-- 取消严格校验模式
set hive.mapred.mode=nonstrict;
full outer join
包括两个表的join结果，（左边有，右边NULL union 右边有，左边NULL）
其结果等于left join union right join
做test_a 与 test_b 的full outer join
select a.*,b.*
from test_a a full outer join test_b b
on a.id=b.id;
Total MapReduce CPU Time Spent: 8 seconds 890 msec
OK
1 a1 1 b1
2 a2 NULL NULL
NULL NULL 3 b3
4 a4 4 b4
用hive shell 执行HQL
用于执行HQL语句，将语句的查询结果放到文件里。
#数据输出，及错误输出均输出到u1
nohup hive -e "use hainiu;select * from user_install_status_limit;" > ~/u1 2>&1 &
#数据输出到u3,错误日志输出到无底洞
nohup hive -e "use hainiu;set mapred.reduce.tasks=1;select * from user_install_status_limit;" 1> ~/u3 2> /dev/null &
#数据输出到u3,错误日志输出到err.log
nohup hive -e "use hainiu;set mapred.reduce.tasks=1;select * from user_install_status_limit;" 1> ~/u3 2> ~/err.log &
union的使用
用 left join union right join 实现 full outer join
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
select a.id aid,a.name aname, b.id bid,b.name bname from test_a a left join test_b b on a.id=b.id
union
select a.id aid,a.name aname, b.id bid,b.name bname from test_a a right join test_b b on a.id=b.id;
Total MapReduce CPU Time Spent: 9 seconds 430 msec
OK
NULL NULL 3 b3
1 a1 1 b1
2 a2 NULL NULL
4 a4 4 b4
优化方法：
增加 reduce的数据处理能力 reduce数据处理的越快 HIVESQL的运行的就越快
增加reducer任务数量
set mapred.reduce.tasks=4;
设置reducer内存大小
set mapreduce.reduce.memory.mb=512;
set mapreduce.reduce.java.opts=-Xmx2048m;
在同一个sql中的不同的job是否可以同时运行
set hive.exec.parallel=true;
增加同一个sql允许并行任务的最大线程数
set hive.exec.parallel.thread.number=8;
map端的join
什么是MapJoin? = semijoin
MapJoin顾名思义，就是在Map阶段进行表之间的连接。而不需要进入到Reduce阶段才进行连接。这样就节省了在Shuffle阶段时要进行的大量数据传输。从而起到了优化作业的作用。
MapJoin原理
即在map 端进行join，其原理是broadcast join，即把小表作为一个完整的驱动表来进行join操作。
通常情况下，要连接的各个表里面的数据会分布在不同的Map中进行处理。即同一个Key对应的Value可能存在不同的Map中。
这样就必须等到 Reduce中去连接。要使MapJoin能够顺利进行，那就必须满足这样的条件：
除了一份表的数据分布在不同的Map中外，其他连接的表的数据必须在每 个Map中有完整的拷贝。
MAPJION会把小表全部读入内存中，在map阶段直接拿另外一个表的数据和内存中表数据做匹配，由于在map是进行了join操作，省去了reduce运行的效率也会高很多。
MapJoin适用的场景：
mapjoin的适用场景如关联操作中有一张表非常小，.不等值的链接操作。通过上面分析你会发现，并不是所有的场景都适合用MapJoin. 它通常会用在如下的一些情景：在二个要连接的表中，有一个很大，有一个很小，这个小表可以存放在内存中而不影响性能。这样我们就把小表文件复制到每一个Map任务的本地，再让Map把文件读到内存中待用。
Hive版本区别：
Hive内置提供的优化机制之一就包括MapJoin。
在Hive v0.7之前，需要给出MapJoin的指示，Hive才会提供MapJoin的优化。
Hive v0.7之后的版本已经不需要给出MapJoin的指示就进行优化。
它是通过如下配置参数来控制的：
-- 开启自动mapjoin
set hive.auto.convert.join=true;
hive 0.11之后，在表的大小符合设置时
-- 是否自动转换为mapjoin
hive.auto.convert.join.noconditionaltask=true
--是否将多个mapjoin合并为一个这个参数控制多大的表可以放进内存，默认值为10000000L（10M），该值表示可以被转换为哈希映射的表大小的总和。
hive.auto.convert.join.noconditionaltask.size=10000
--小表的最大文件大小，默认为25000000，即25M
hive.mapjoin.smalltable.filesize=25000000
默认会把join转换为map join
-- 忽略MAPJOIN标记 /*+ MAPJOIN(smalltable)*/
hive.ignore.mapjoin.hint=true
-- 开启自动mapjoin
hive.auto.convert.join=true
MAPJOIN配置参数 :
set hive.auto.convert.join=true; // 将小表刷入内存中，默认true
set hive.ignore.mapjoin.hint=true; // 是否忽略mapjoin hint 即mapjoin标记 默认true
set hive.mapjoin.smalltable.filesize=2500000; // 刷入内存表的大小(字节),根据自己的数据集加大
可以设置不开启
set hive.auto.convert.join=false;
set hive.ignore.mapjoin.hint=false;
在设置成false 或 true时，可以手动的 /*+ MAPJOIN(c) */ 。
select /*+ MAPJOIN(c) */ * from user_install_status_other u inner join country_dict c on u.country=c.code where u.dt='20141228' limit 10;
set hive.auto.convert.join=true;
set hive.ignore.mapjoin.hint=true;
如果是自动mapjoin,在使用MAPJOIN时，需要注意：
1、LEFT OUTER JOIN的左表必须是大表;
2、RIGHT OUTER JOIN的右表必须是大表；
3、INNER JOIN左表或右表均可以作为大表；
4、FULL OUTER JOIN不能使用MAPJOIN；
5、MAPJOIN支持小表为子查询；
6、使用MAPJOIN时需要引用小表或是子查询时，需要引用别名；
7、在MAPJOIN中，可以使用不等值连接或者使用OR连接多个条件；
mapjoin里写的是小表，且left outer join时小表写在join的后面；
hive中使用mapjoin有时可以大大提高sql语句的执行效率。
其原理是：它会把小表全部读入内存中，在map的时候直接拿另外一张表的数据和内存中表的数据做匹配，进行join操作，这样省去了reduce。
在“关联操作中有一个表非常小，另一个表很大”的场景下，mapjoin就不会由于数据倾斜而导致某个reduce上落数据太多而失败；
