[Hive] 조인 문 : INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL JOIN, CARTESIAN PRODUCT JOIN, MAP-SIDE JOIN, UNION ALL
Hive 2016. 6. 26. 22:40빅데이터 환경에서 사이즈가 어마무시하게 큰 테이블 간 JOIN은 부하가 매우 크므로 되도록이면 데이터 스키마 설계를 할 때 JOIN을 하지 않고도 분석을 할 수 있도록 요건을 반영하곤 합니다. ARRAY, MAP, STRUCT 등의 컬렉션 데이터형이 정규화를 깨더라도 빅데이터 환경에서 사용되는 이유입니다.
하지만, 아무리 스키마 설계를 잘 한다고 해도 분석이 복잡해지면 JOIN을 안하고 모든 것을 처리하기가 힘든 것도 사실입니다.
이번 포스팅에서는 두 개 이상의 테이블을 좌, 우로 서로 연결해서 분석할 때 사용하는 Hive 조인 문으로
- INNER JOIN
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUER JOIN
- CROSS JOIN(CARTESIAN PRODUCT JOIN)
- MAP-SIDE JOIN 과
두 개 이상의 테이블을 상, 하로 서로 붙여서 분석할 때 사용하는
- UNION ALL
문에 대해서 알아보겠습니다.
기존에 관계형 DB를 사용했던 분이라면 아마 쉽게 이해할 수 있을 내용들입니다.
- 내부 조인 (INNER JOIN)
[쿼리 예시]
SELECT a.var1, a.var2, b.var2
FROM mytable_1 a JOIN mytable_2 b
ON a.var1 = b.var1 -- EQUI-JOIN 만 가능하며, NON-EQUI JOIN은 불가
WHERE a.prd_cd = 'ABC' AND b.prd_cd = 'DEF';
SELECT a.var1, a.var2, a.var3, b.var4
FROM mytable_1 a JOIN mytable_2 b
ON a.var1 = b.var1 AND a.var2 = b.var2 -- 2 JOIN keys
WHERE a.prd_cd = 'ABC';
SELECT a.var1, a.var2, b.var3, c.var4
FROM mytable_1 a JOIN mytable_1 b ON a.var1 = b.var1
JOIN mytable_1 c ON a.var1 = c.var1
WHERE (a.prd_cd = 'ABC' AND b.prd_cd = 'DEF')
AND (c.prd_cd = 'GHI');
- 왼쪽 세미 조인 (LEFT SEMI-JOIN)
-- : 오른쪽 테이블에서 ON의 술어를 만족하는 레코드를 찾으면 왼쪽 테이블의 레코드를 반환
-- : 내부 조인(INNER JOIN)을 최적화한 특수한 형태로, 내부 조인보다 빠름
-- : 왼쪽 세미 조인만 지원하며, 오른쪽 세미 조인은 미지원
SELECT a.var1, a.var2, a.var3
FROM mytable_1 a LEFT SEMI JOIN mytable_2 b
ON a.var1 = b.var1 AND a.var2 = b.var2;
- 왼쪽 외부 조인 (LEFT OUTER JOIN)
[ 쿼리 예시 ]
SELECT a.var1, a.var2, a.var3, b.var4
FROM mytable_1 a LEFT OUTER JOIN mytable_2 b
ON a.var1 = b.var1 AND a.var2 = b.var2
WHERE a.prd_cd = 'ABC';
- 오른쪽 외부 조인 (RIGHT OUTER JOIN)
[ 쿼리 예시 ]
SELECT a.var1, a.var2, a.var3, b.var4
FROM mytable_1 a RIGHT OUTER JOIN mytable_2 b
ON a.var1 = b.var1 AND a.var2 = b.var2
WHERE a.prd_cd = 'ABC';
- 완전 외부 조인 (FULL OUTER JOIN)
[ 쿼리 예시 ]
SELECT a.var1, a.var2, a.var3, b.var4
FROM mytable_1 a FULL OUTER JOIN mytable_2 b
ON a.var1 = b.var1 AND a.var2 = b.var2
WHERE a.prd_cd = 'ABC';
- 교차 조인 (CROSS JOIN (CARTESIAN PRODUCT JOIN))
[ 쿼리 예시]
SELECT * FROM mytable_1 JOIN mytable_2;
쿼리 문이 JOIN 문 중에서 제일 간단합니다. 그런데 속도는 제일 느리고 또 위험하기도 합니다. ^^;
A*B 개의 ROW를 반환(가령 A 테이블이 100개 row, B 테이블이 1000개 row를 가지고 있다면 A JOIN B 는 100 * 1,000 = 100,000 개 row 출력)하므로 출력량도 제일 많습니다. 뿐만 아니라 다른 JOIN문과 달리 CORSS JOIN (CARTESIAN PRODUCT JOIN)은 병렬처리도 안되므로 느리기까지 합니다.
SELECT 문을 가지고 시스템을 다운시키기가 쉽지 않은데요, SELECT 문으로 혹시 HIVE가 다운되거나 성능이 현격히 저하되었다면 JOIN 걸 때 ON, WHERE 절을 빼먹고 CROSS JOIN (Cartesian Product JOIN)을 실수로 쿼리를 날린게 아닌가 하고 범인 후보로 의심을 해볼 수 있습니다.
CROSS JOIN 걸 때는 '내가 원하는 output/분석 목적이 CROSS JOIN 맞나?'를 재차 확인하고 쿼리를 날리기 바랍니다. 특히 'BIG~ 데이터' 환경에서는 말이지요.
- Map-Side JOIN
[ 쿼리 예시 ]
hive> set hive.auto.convert.join=true; -- default is false
SELECT a.var1, a.var2, a.var3, b.var4
FROM mytable_1 a JOIN mytable_2 b -- 큰 테이블은 왼쪽, 크기가 작은 테이블은 오른쪽!
ON a.var1 = b.var1 AND a.var2 = b.var2
WHERE a.prd_cd = 'ABC';
맵 사이드 조인을 하려면 먼저 set hive.auto.convert.join=true; 로 설정을 바꿔줘야 합니다.
맵 사이드 조인은 이름에서 알 수 있는 것처럼, 크기가 작은 테이블을 메모리에 캐시하고, 큰 테이블은 맵퍼로 보내 후에, 캐시한 작은 테이블로부터 일치하는 모든 것을 찾아낼 수 있기 때문에 맵에서 모든 조인을 할 수 있습니다.
=> 일반 JOIN 과 달리 리듀스 단계를 건너뛸 수 있어서 속도가 빠른 장점이 있습니다. 성능 최적화를 위해 알아두면 좋은 JOIN 방법입니다.
이상으로 JOIN 문을 마칩니다.
두 개 이상의 테이블을 합칠 때 사용하는 UNION ALL 입니다.
- UNION ALL
-- 두 개 이상의 테이블을 위, 아래로 합치기
-- subquery 는 같은 수의 칼럼, 각 칼럼의 Data Type 동일해야 함
[ 쿼리 예시 ]
SELECT a.var1, a.var2, a.var3
FROM (
SELECT a1.var1, a1.var2, a1.var3, 'A1' as source
FROM mytable a1
UNION ALL
SELECT a2.var1, a2.var2, a2.var3, 'A2' as source
FROM mytable a2
) a
SORT BY a.var1 ASC, a.var2 DESC;
이상으로 HIVE에서 테이블 연결하거나 합쳐서 분석할 때 사용하는 JOIN문, UNION ALL에 대해서 알아보았습니다.
다음번 포스팅에서는 하이브 뷰(VIEW)에 대해서 알아보겠습니다.
이번 포스팅이 도움이 되었다면 아래의 '공감 ~♡'를 꾸욱 눌러주세요.
