Apache Hive - 조인 (Join)

[ 쿼리시 테이블 배치 ]

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

하이브에서 조인 사용시 왼쪽에서 오른쪽 FROM절 순서로 쿼리가 실행되므로

일반적으로 가장 데이터가 큰 테이블을 맨 오른쪽(마지막)에 실행되도록 쿼리를 짠다.

 

작은 테이블이 먼저 처리되면, 큰 데이터와의 조인이 더 효율적으로 이루어지는 이유는

Mapper에서 작은 테이블을 먼저 읽고 메모리에 올려둔 뒤, 큰 테이블의 데이터와 비교하기 때문이다.

큰 데이터는 메모리에 적재되지 않고 디스크 기반 스트리밍 방식으로 처리된다.

따라서 큰 테이블을 오른쪽에 배치하면 성능 최적화가 가능하다.

 

* 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