Hive [5] - 조인 (Join)

테이블 간의 Join은 부하가 매우크므로 스키마설계시 Join을 하지 않도록 구성해야 한다.

하이브에서 조인 사용시 왼쪽에서 오른쪽 FROM절 순서로 쿼리가 실행되므로 일반적으로 가장 데이터가 큰 테이블을 맨 오른쪽(마지막)에 실행되도록 쿼리를 짠다. 

* HUE이용시 

• Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.mr.MapredLocalTask

와 같은 오류시 SET hive.auto.convert.join=false 로 설정

[ Join 명령문 ]

   table_reference JOIN table_factor [join_condition]
   | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference
   join_condition
   | table_reference LEFT SEMI JOIN table_reference join_condition
   | table_reference CROSS JOIN table_reference [join_condition]

[ Example 테이블 ]

직원테이블 (CUSTOMER)

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
| 1  | Ramesh   | 32  | Ahmedabad | 2000.00  |  
| 2  | Khilan   | 25  | Delhi     | 1500.00  |  
| 3  | kaushik  | 23  | Kota      | 2000.00  | 
| 4  | Chaitali | 25  | Mumbai    | 6500.00  | 
| 5  | Hardik   | 27  | Bhopal    | 8500.00  | 
| 6  | Komal    | 22  | MP        | 4500.00  | 
| 7  | Muffy    | 24  | Indore    | 10000.00 | 
+----+----------+-----+-----------+----------+

주문테이블 (ORDERS)

+-----+---------------------+-------------+--------+ 
|OID  | DATE                | CUSTOMER_ID | AMOUNT | 
+-----+---------------------+-------------+--------+ 
| 102 | 2009-10-08 00:00:00 |           3 | 3000   | 
| 100 | 2009-10-08 00:00:00 |           3 | 1500   | 
| 101 | 2009-11-20 00:00:00 |           2 | 1560   | 
| 103 | 2008-05-20 00:00:00 |           4 | 2060   | 
+-----+---------------------+-------------+--------+

1. INNER JOIN [ 내부 조인 ]

RDBMS와 개념은 같지만 하이브는 동등조인(EQUI-JOIN)만을 제공한다.

( ON절에 = 만 사용)

[ 쿼리문 ]

SELECT c.id, c.name, c.age, o.amount

FROM customer JOIN orders o

ON c.id = o.customer_id

WHERE o.amount> 1500 AND o.amount< 3000;

SELECT c.id, c.name, c.age

FROM customer JOIN orders o

ON c.id = o.customer_id AND c.salary = o.amount

where c.name='khilan';

SELECT t1.var1, t1.var2, t2.var3

FROM table1 t1 JOIN table2 t2 ON t1.var1 = t2.var1

                     JOIN table3 t3 ON t1.var1 = t3.var1

[ 첫번째 쿼리문 결과 ]

+----+----------+-----+--------+ 
| ID | NAME     | AGE | AMOUNT | 
+----+----------+-----+--------+ 
| 2  | Khilan   | 25  | 1560   | 
| 4  | Chaitali | 25  | 2060   | 
+----+----------+-----+--------+

2. LEFT SEMI-JOIN

• 오른쪽테이블에서 ON에 일치하는 레코드를 찾으면 더 이상 찾는 행동을 멈추고 왼쪽테이블의 데이터를 반환한다. 
• 따라서 내부조인보다 빠르다.
• 하이브는 RIGHT SEMI-JOIN은 지원하지 않는다.

[ 쿼리문 ]

SELECT t1.var1, t1.var2, t1.var3

FROM table1 t1 LEFT SEMI JOIN table2 t2

ON t1.var1 = t2.var1 AND t1.var2 = t2.var2;

3. LEFT(RIGHT) OUTER JOIN

• Left Outer Join : Table1에 있는 모든 행과 Table2에 함께 있는 행을 얻는다.
• Rigth Outer Join : Table2에 있는 모든 행과 Table1에 함께 있는 행을 얻는다.

[ 쿼리문 ]

SELECT c.id, c.name, o.amount, o.date

FROM customers c

LEFT OUTER JOIN orders o

ON c.id = o.customer_id;

[ 결과 ]

+----+----------+--------+---------------------+ 
| ID | NAME     | AMOUNT | DATE                | 
+----+----------+--------+---------------------+ 
| 1  | Ramesh   | NULL   | NULL                | 
| 2  | Khilan   | 1560   | 2009-11-20 00:00:00 | 
| 3  | kaushik  | 3000   | 2009-10-08 00:00:00 | 
| 3  | kaushik  | 1500   | 2009-10-08 00:00:00 | 
| 4  | Chaitali | 2060   | 2008-05-20 00:00:00 | 
| 5  | Hardik   | NULL   | NULL                | 
| 6  | Komal    | NULL   | NULL                | 
| 7  | Muffy    | NULL   | NULL                | 
+----+----------+--------+---------------------+

4. FULL OUTER JOIN 

• left table과 right table의 합집합을 얻는다. left table에는 데이터가 있고 right table에는 없는 경우 null로 표현된다.

[ 쿼리문 ]

SELECT c.id, c.name, o.amount, o.date

FROM customers c

FULL OUTER JOIN ORDERS o

ON c.id = o.customer_id;

[ 결과 ]

+------+----------+--------+---------------------+ 
| ID   | NAME     | AMOUNT | DATE                | 
+------+----------+--------+---------------------+ 
| 1    | Ramesh   | NULL   | NULL                | 
| 2    | Khilan   | 1560   | 2009-11-20 00:00:00 | 
| 3    | kaushik  | 3000   | 2009-10-08 00:00:00 | 
| 3    | kaushik  | 1500   | 2009-10-08 00:00:00 | 
| 4    | Chaitali | 2060   | 2008-05-20 00:00:00 | 
| 5    | Hardik   | NULL   | NULL                | 
| 6    | Komal    | NULL   | NULL                |
| 7    | Muffy    | NULL   | NULL                |  
| 3    | kaushik  | 3000   | 2009-10-08 00:00:00 | 
| 3    | kaushik  | 1500   | 2009-10-08 00:00:00 | 
| 2    | Khilan   | 1560   | 2009-11-20 00:00:00 | 
| 4    | Chaitali | 2060   | 2008-05-20 00:00:00 | 
+------+----------+--------+---------------------+

5. UNION ALL

하이브 Join의 On절에서는 Or 조건의 사용을 할 수 없다. 그럴 때 UNION ALL절 이 쓰는데 예시는 아래와 같다.

• 변경전

SELECT * 

FROM table1 t1 JOIN table2 t2

ON (t1.val1=t2.val1) or (t1.var2=t2.var2);

• 변경후

SELECT * 

FROM table1 t1 JOIN table2 t2

ON t1.val1=t2.val1

UNION ALL

SELECT * 

FROM table1 t1 JOIN table2 t2

ON t1.var2=t2.var2;

참조 :

https://www.tutorialspoint.com/hive/hiveql_joins.htm