轻松玩转hive中各种join之间的关系以及使用

我会带着你远行 2022-05-30 06:42 123阅读 0赞

**hive编程是整个数据仓库操作的核心，而各种业务之间的join是hive的核心，所以熟练明白滴掌握hive中的各种join是数据仓库开发工程师必备的技能。   **

**hive中的join只支持等值join,也就是说join on中的on里面表之间连接条件只能是=，不能是<,>等符号。此外，on中的等值连接之间只能是and,不能是or. （如果在on 里添加非表之间的条件可以是非等号，下面有演示）**

**     为了演示各种join之间的关系以及使用注意事项，准备如下三个表，以及表中数据。**

表1：CREATE  TABLE `FDM_SOR.mytest_department`(
          `dept_no` int, 
         `dept_name` string) row format delimited fields terminated by ','
    
     表2：CREATE  TABLE `FDM_SOR.mytest_staffinfo`(
             `id` int, 
             `name` string,
              sex   string,
              dept_no int 
          )row format delimited fields terminated by ','
    
      表3：CREATE  TABLE `FDM_SOR.mytest_deptaddr`(
          `dept_no` int, 
          `addr` string, 
          `tel` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
    如下对应的各表中load的数据
    `FDM_SOR.mytest_staffinfo
    001,'tom','man',101
    002,'jerry','man',103
    003,'marry','woman',101
    004,'tom','man',104
    005,'jebby','man',102
    006,'smiiss','man',101
    007,'dosos','man',102
    008,'tiny','woman',102
    009,'feyy','woman',104
    010,'feyy','woman',103
    011,'cake','man',101
    012,'dogy','man',102
    013,'gaayy','man',105
    014,'kety','man',
    015,'caggyy','man',104
    016,'addy','man',106
    017,'nancy','man',104
    018,'tom','man',101
    
    FDM_SOR.mytest_department
    101,'it'
    102,'finance'
    103,'mannager'
    104,'dining'
    105,'boss-office'
    107,'enjoy'
    108,'fun'
    
    FDM_SOR.mytest_deptadd
    109,'new york109','931232323'
    108,'new york108','431232323'
    107,'new york107','531232323'
    106,'new york106','531232323'
    101,'new york101','431232323'
    104,'new york104','131232323'
    103,'new york103','131232323'
    102,'new york102','131232323'
    105,'new york105','131232323'

**1.inner join(内连接，只有进行连接的两个表中都存在与连接条件相匹配的数据才会被留下来) **

** 内连接是最常见的一种连接，它页被称为普通连接，而E.FCodd最早称之为自然连接。其中inner可以省略。inner join == join 等价于早期的连接语法**

select a.id ,a.name,b.dept_no,b.dept_name
      from FDM_SOR.mytest_staffinfo a  inner join  FDM_SOR.mytest_department  b
      on a.dept_no = b.dept_no
           等价于 inner join内连接的另外一种写法，效果一样。
      select a.id ,a.name,b.dept_no,b.dept_name
      from FDM_SOR.mytest_staffinfo a  ,FDM_SOR.mytest_department  b
      where a.dept_no = b.dept_no
            1	'tom'	101	'it'
            2	'jerry'	103	'mannager'
            3	'marry'	101	'it'
            4	'tom'	104	'dining'
            5	'jebby'	102	'finance'
            6	'smiiss'101	'it'
            7	'dosos'	102	'finance'
            8	'tiny'	102	'finance'
            9	'feyy'	104	'dining'
            10	'feyy'	103	'mannager'
            11	'cake'	101	'it'
            12	'dogy'	102	'finance'
            13	'gaayy'	105	'boss-office'
            15	'caggyy'104	'dining'
            17	'nancy'	104	'dining'
            18	'tom'	101	'it'

如上，表1中工号16的员工，所在106部门，因为department中没有，所以其没有留下，同理14号员工因为没有部门，也没有存在。 同理department中部分编号107,108也没有留下，因为其在表staffinfo中没有，故没有留下。  
\------------------------------------------------------------------------------------------------------------------------------  
**2.left join 是左外连接(Left Outer Jion)，其中outer可以省略，left outer join是早期的写法。**  
     A left join  B 是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.  
     意思说,左表(A)的记录将会全部表示出来(不管右边的表中是否存在与它们匹配的行),而右表(B)只会显示符合搜索条件的记录，比如符合on,where中的条件。B表记录不足的地方均为NULL.    **A  left   join   B   等价B   right   join   A  **

select a.id ,a.name,b.dept_no,b.dept_name
    from FDM_SOR.mytest_staffinfo a  left join  FDM_SOR.mytest_department  b
    on a.dept_no = b.dept_no
    1	'tom'	101	'it'
    2	'jerry'	103	'mannager'
    3	'marry'	101	'it'
    4	'tom'	104	'dining'
    5	'jebby'	102	'finance'
    6	'smiiss'101	'it'
    7	'dosos'	102	'finance'
    8	'tiny'	102	'finance'
    9	'feyy'	104	'dining'
    10	'feyy'	103	'mannager'
    11	'cake'	101	'it'
    12	'dogy'	102	'finance'
    13	'gaayy'	105	'boss-office'
    14	'kety'	NULL	NULL
    15	'caggyy'104	'dining'
    16	'addy'	NULL	NULL
    17	'nancy'	104	'dining'
    18	'tom'	101	'it'

3.right join ,同理和left join相反，A right join B ,则显示B表中所有的记录，A表不足的用null填充  
  同样 right outer join = right join ,outer可以省略。

select a.id ,a.name,b.dept_no,b.dept_name
    from FDM_SOR.mytest_staffinfo a  right outer  join  FDM_SOR.mytest_department  b
    on a.dept_no = b.dept_no
    1	'tom'	101	'it'
    3	'marry'	101	'it'
    6	'smiiss'101	'it'
    11	'cake'	101	'it'
    18	'tom'	101	'it'
    5	'jebby'	102	'finance'
    7	'dosos'	102	'finance'
    8	'tiny'	102	'finance'
    12	'dogy'	102	'finance'
    2	'jerry'	103	'mannager'
    10	'feyy'	103	'mannager'
    4	'tom'	104	'dining'
    9	'feyy'	104	'dining'
    15	'caggyy'104	'dining'
    17	'nancy'	104	'dining'
    13	'gaayy'	105	'boss-office'
    NULL	NULL	107	'enjoy'
    NULL	NULL	108	'fun'

**4. full outer join 等价 full join  ,全外连接**

**   包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行**  在功能上，它等价于对这两个数据集合分别进行左外连接和右外连接，然后再使用消去重复行的并操作将上述两个结果集合并为一个结果集。

select a.id ,a.name,b.dept_no,b.dept_name
    from FDM_SOR.mytest_staffinfo a  full  join  FDM_SOR.mytest_department  b
    on a.dept_no = b.dept_no
    order by a.id 
    NULL	NULL	107	'enjoy'
    NULL	NULL	108	'fun'
    1	'tom'	101	'it'
    2	'jerry'	103	'mannager'
    3	'marry'	101	'it'
    4	'tom'	104	'dining'
    5	'jebby'	102	'finance'
    6	'smiiss'	101	'it'
    7	'dosos'	102	'finance'
    8	'tiny'	102	'finance'
    9	'feyy'	104	'dining'
    10	'feyy'	103	'mannager'
    11	'cake'	101	'it'
    12	'dogy'	102	'finance'
    13	'gaayy'	105	'boss-office'
    14	'kety'	NULL	NULL
    15	'caggyy'	104	'dining'
    16	'addy'	NULL	NULL
    17	'nancy'	104	'dining'
    18	'tom'	101	'it'

**5.自连接是自身连接，指同一个表自己与自己进行连接。这种一元连接通常用于从自反关系（也称作递归关系）中抽取数据  
6.外连接与条件配合使用**  
当在内连接（join或者inner join）查询中加入条件时，无论是将它加入到join子句，还是加入到where子句，其效果是完全一样的，比如：

select a.id ,a.name,b.dept_no,b.dept_name
    from FDM_SOR.mytest_staffinfo a  inner join  FDM_SOR.mytest_department  b
    on a.dept_no = b.dept_no and a.id > 5
    等价于
    select a.id ,a.name,b.dept_no,b.dept_name
    from FDM_SOR.mytest_staffinfo a  inner join  FDM_SOR.mytest_department  b
    on a.dept_no = b.dept_no  
    where  a.id > 5
    6	'smiiss'	101	'it'
    7	'dosos'	102	'finance'
    8	'tiny'	102	'finance'
    9	'feyy'	104	'dining'
    10	'feyy'	103	'mannager'
    11	'cake'	101	'it'
    12	'dogy'	102	'finance'
    13	'gaayy'	105	'boss-office'
    15	'caggyy'	104	'dining'
    17	'nancy'	104	'dining'
    18	'tom'	101	'it'

**但对于外连接情况就不同了。加入的条件在join子句中和在where子句中效果完全不一样。**  
**当把条件加入到 join子句时，SQL Server、Informix会返回外连接表的全部行，然后使用指定的条件返回第二个表的行。**

**如果将条件放到where子句中，SQL Server将会首先进行连接操作，然后使用where子句对连接后的行进行筛选**。下面的两个查询展示了条件放置位子对执行结果的影响：

select a.id ,a.name,b.dept_no,b.dept_name
    from FDM_SOR.mytest_staffinfo a  left join  FDM_SOR.mytest_department  b
    on a.dept_no = b.dept_no and  a.id > 5 
    1	'tom'	NULL	NULL
    2	'jerry'	NULL	NULL
    3	'marry'	NULL	NULL
    4	'tom'	NULL	NULL
    5	'jebby'	NULL	NULL
    6	'smiiss'	101	'it'
    7	'dosos'	102	'finance'
    8	'tiny'	102	'finance'
    9	'feyy'	104	'dining'
    10	'feyy'	103	'mannager'
    11	'cake'	101	'it'
    12	'dogy'	102	'finance'
    13	'gaayy'	105	'boss-office'
    14	'kety'	NULL	NULL
    15	'caggyy'	104	'dining'
    16	'addy'	NULL	NULL
    17	'nancy'	104	'dining'
    18	'tom'	101	'it'

**如上，当将条件加入到where 子句后**

select a.id ,a.name,b.dept_no,b.dept_name
    from FDM_SOR.mytest_staffinfo a  left join  FDM_SOR.mytest_department  b
    on a.dept_no = b.dept_no 
    where  a.id > 5
    6	'smiiss'	101	'it'
    7	'dosos'	102	'finance'
    8	'tiny'	102	'finance'
    9	'feyy'	104	'dining'
    10	'feyy'	103	'mannager'
    11	'cake'	101	'it'
    12	'dogy'	102	'finance'
    13	'gaayy'	105	'boss-office'
    14	'kety'	NULL	NULL
    15	'caggyy'	104	'dining'
    16	'addy'	NULL	NULL
    17	'nancy'	104	'dining'
    18	'tom'	101	'it'

**7.多表之间进行join时注意事项**

select a.id ,a.name,b.dept_no,b.dept_name,c.addr,c.tel
              from FDM_SOR.mytest_staffinfo a  
              inner join  FDM_SOR.mytest_department  b  on a.dept_no = b.dept_no
              inner join  FDM_SOR.mytest_deptaddr    c  on a.dept_no = c.dept_no ;
    1	'tom'	101	'it'	    ‘new york101'	'431232323'
    2	'jerry'	103	'mannager'	'new york106'	'131232323'
    3	'marry'	101	'it'	    'new york101'	'431232323'
    4	'tom'	104	'dining'	'new york101'	'131232323'
    5	'jebby'	102	'finance'	'new york102'	'131232323'
    6	'smiiss'101	'it'	    'new york101'	'431232323'
    7	'dosos'	102	'finance'	'new york102'	'131232323'
    8	'tiny'	102	'finance'	'new york102'	'131232323'
    9	'feyy'	104	'dining'	'new york101'	'131232323'
    10	'feyy'	103	'mannager'	'new york106'	'131232323'
    11	'cake'	101	'it'	    'new york101'	'431232323'
    12	'dogy'	102	'finance'	'new york102'	'131232323'
    13	'gaayy'	105	'boss-office''new york106'	'131232323'
    15	'caggyy'104	'dining'	'new york101'	'131232323'
    17	'nancy'	104	'dining'	'new york101'	'131232323'
    18	'tom'	101	'it'	   'new york101'	'431232323

**注意：一般情况下，hive会给每个join对象启动一个mapreduce job进行执行，如上，一般会先启动一个mr job 进行a,b表的join，然后再启动一个mr job进行上面job产生的临时表与c表的join,但是对于3个及以上的多表join,join有所优化，如果每个join的子句on里的连接键一样，如上都是dept\_no,那么这样的话，mr会启动一个job完成所有表的join.此外，多表进行join时，一般要把大表放到最后面，这样可以提高效率。**  
**8.left semi join  左半开连接 ，会显示左半边表中记录，前提是其记录对于右半边表满足于on语句中判定条件。**  
  ** left semi join 效果和inner join 效果差不多，比inner join 效率更高。**

select a.id ,a.name,a.dept_no
     from FDM_SOR.mytest_staffinfo a  
     where a.dept_no in (
     select dept_no  from FDM_SOR.mytest_department
     where dept_no > 102
     )
    2	'jerry'	103
    4	'tom'	104
    9	'feyy'	104
    10	'feyy'	103
    13	'gaayy'	105
    15	'caggyy'104
    17	'nancy'	104
    等价于下面的查询
    select a.id ,a.name,a.dept_no
    from FDM_SOR.mytest_staffinfo a  left semi join FDM_SOR.mytest_department  b  
    on a.dept_no = b.dept_no   and b.dept_no >102
     
    2	'jerry'	103
    4	'tom'	104
    9	'feyy'	104
    10	'feyy'	103
    13	'gaayy'	105
    15	'caggyy'104
    17	'nancy'	104
    
    select a.id ,a.name,a.dept_no
    from FDM_SOR.mytest_staffinfo a  left semi join FDM_SOR.mytest_department  b  
    on a.dept_no = b.dept_no   
    where a.dept_no > 102
     
     2	'jerry'	103
    4	'tom'	104
    9	'feyy'	104
    10	'feyy'	103
    13	'gaayy'	105
    15	'caggyy'104
    17	'nancy'	104
    
    select a.id ,a.name,a.dept_no
    from FDM_SOR.mytest_staffinfo a  left semi join FDM_SOR.mytest_department  b  
    on a.dept_no = b.dept_no   
     where b.dept_no > 102   -----报错。select 和where子句中不能引用右边表中的字段。

最后附上hive中各种join之间的关系一览图。 ![20180330020552876][]

[20180330020552876]: /images/20220530/dda9ab565327438ab67eab3e91fc0ea6.png