大数据开发第15课 hive表关联

1. hive的jion 限制

• 只支持等值连接
• on后面不支持or

1. inner join同时满足条件的记录进入结果
2. left join 保留左侧
3. right join 保留右侧
4. full join 全部保留

实操

-- 默认false 不用设置，设置了远程插数据不行
set hive.exec.mode.local.auto=ture;
-- reduce 不限制个数
set mapred.reduce.tasks=-1;
create table t_a(name string,value string);
insert into t_a values('a','1'),('b',2),('c',3),('d',4);
insert into t_a values ('e',5);
create table t_b(name string,value string);

select * from t_a;
insert into t_b values('a','11'),('b',22),('c',33),('d',44);
insert into  t_b values ('f','ff');
--inner join
select
    a.name as aname,
    a.value as avalue,
    b.name as bname,
    b.value as bvaue
from t_a a
         join t_b b
              on a.name=b.name;
--out join
select a.name  as aname,
       a.value as avalue,
       b.name  as bname,
       b.value as bvaue
from t_a a
         left outer join t_b b
                         on a.name = b.name;

--left outer join
select
    a.name as aname,
    a.value as avalue,
    b.name  as bname,
    b.value as bvaue
from t_a a
         left outer join t_b b
                         on a.name=b.name;
-- right outer join
select
    a.name as aname,
    a.value as avalue,
    b.name  as bname,
    b.value as bvaue
from t_a a
         right outer join t_b b
                          on a.name=b.name;
----out outer join
-- 两个表中的记录全部保留
select
    a.name as aname,
    a.value as avalue,
    b.name  as bname,
    b.value as bvaue
from t_a a
         full join   t_b b
                     on a.name=b.name;
--left semi join
select
    a.name as aname,
    a.value as avalue
from t_a a
         left semi join t_b b
                        on a.name=b.name;

-- union all ,insect,
-- union 数据集合并 去重；union all 不去重
select name,value from t_a
union
select name,value from t_b;
-- intersect 交集
select name,value from t_a
intersect
select name,value from t_b;
-- minus 差集
select name,value from t_a
minus
select name,value from t_b;