LanguageManual Joins

LanguageManual Joins

Hive에서 테이블을 조인하는 문법과 조인 최적화, 각 조인 방식의 의미를 설명해요. SQL 조인의 기본 개념 위에 Hive 특유의 실행 특성(맵/리듀스 잡 수, 버퍼링 순서, 힌트)까지 알아두면 큰 테이블을 효율적으로 다룰 수 있습니다.

출처: 문서

본문

조인 문법(Join Syntax)

Hive는 테이블 조인에 다음 문법을 지원해요:

join_table:
    table_reference [INNER] 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] (as of Hive 0.10)

table_reference:
    table_factor
  | join_table

table_factor:
    tbl_name [alias]
  | table_subquery alias
  | ( table_references )

join_condition:
    ON expression

이 조인 문법의 문맥은 Select Syntax를 참고하세요.

버전 0.13.0+: 암시적 조인 표기법(Implicit join notation)

암시적 조인 표기법은 Hive 0.13.0부터 지원돼요(HIVE-5558 참고). 이는 FROM 절이 JOIN 키워드를 생략하고 쉼표로 구분된 테이블 목록을 조인하게 해 줍니다. 예를 들어:

SELECT * FROM table1 t1, table2 t2, table3 t3 WHERE t1.id = t2.id AND t2.id = t3.id AND t1.zipcode = '02535';

버전 0.13.0+: 자격 없는 컬럼 참조(Unqualified column references)

Hive 0.13.0부터 조인 조건에서 자격 없는(unqualified) 컬럼 참조를 지원해요(HIVE-6393 참고). Hive는 이를 조인 입력에 대해 해석하려고 시도합니다. 자격 없는 컬럼 참조가 둘 이상의 테이블로 해석되면 Hive는 이를 모호한(ambiguous) 참조로 표시합니다.

예를 들어:

CREATE TABLE a (k1 string, v1 string);
CREATE TABLE b (k2 string, v2 string);

SELECT k1, v1, k2, v2
FROM a JOIN b ON k1 = k2;

버전 2.2.0+: ON 절의 복잡한 식(Complex expressions in ON clause)

복잡한 식이 있는 ON 절은 Hive 2.2.0부터 지원돼요(HIVE-15211, HIVE-15251 참고). 그 이전에는 Hive가 동등(equality) 조건이 아닌 조인 조건을 지원하지 않았습니다.

특히 조인 조건 문법은 다음과 같이 제한되어 있었어요:

join_condition:  
    ON equality_expression ( AND equality_expression )*

equality_expression:  
    expression = expression

예제(Examples)

조인 쿼리를 작성할 때 고려할 몇 가지 핵심 포인트입니다:

  • 복잡한 조인 식이 허용돼요, 예를 들어:
  SELECT a.* FROM a JOIN b ON (a.id = b.id)

  SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department)

  SELECT a.* FROM a LEFT OUTER JOIN b ON (a.id <> b.id)

는 모두 유효한 조인입니다.

  • 같은 쿼리에서 2개 이상의 테이블을 조인할 수 있어요, 예를 들어:
  SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

는 유효한 조인입니다.

  • 모든 테이블에서 조인 절에 같은 컬럼을 사용하면, Hive는 여러 테이블에 대한 조인을 단일 맵/리듀스 잡으로 변환해요, 예를 들어:
  SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

는 b의 key1 컬럼만 조인에 관여하므로 단일 맵/리듀스 잡으로 변환됩니다. 반면에

  SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

는 두 개의 맵/리듀스 잡으로 변환되는데, b의 key1 컬럼은 첫 번째 조인 조건에, key2 컬럼은 두 번째 조인 조건에 사용되기 때문이에요. 첫 번째 맵/리듀스 잡이 a와 b를 조인하고, 그 결과가 두 번째 맵/리듀스 잡에서 c와 조인됩니다.

  • 조인의 각 맵/리듀스 단계에서, 수열(sequence)에서 마지막 테이블이 리듀서를 통해 스트리밍되고 나머지는 버퍼링돼요. 따라서 조인 키의 특정 값에 대한 행을 버퍼링하는 데 필요한 메모리를 줄이려면, 가장 큰 테이블이 수열에서 마지막에 오도록 테이블을 구성하면 도움이 됩니다. 예를 들어:
  SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

에서 세 테이블 모두 단일 맵/리듀스 잡으로 조인되고, a와 b의 키 특정 값에 대한 값들이 리듀서 메모리에 버퍼링됩니다. 그러면 c에서 가져온 각 행에 대해 버퍼링된 행과 조인이 계산돼요. 마찬가지로

  SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

는 조인 계산에 두 개의 맵/리듀스 잡이 관여합니다. 첫 번째는 a와 b를 조인하면서 리듀서에서 b의 값은 스트리밍하고 a의 값은 버퍼링해요. 두 번째 잡은 첫 번째 조인의 결과를 버퍼링하면서 c의 값을 리듀서를 통해 스트리밍합니다.

  • 조인의 각 맵/리듀스 단계에서 스트리밍할 테이블은 힌트로 지정할 수 있어요, 예를 들어:
  SELECT /*+ STREAMTABLE(a) */ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

에서 세 테이블 모두 단일 맵/리듀스 잡으로 조인되고, b와 c의 키 특정 값들이 리듀서 메모리에 버퍼링됩니다. 그러면 a에서 가져온 각 행에 대해 버퍼링된 행과 조인이 계산돼요. STREAMTABLE 힌트를 생략하면 Hive는 조인에서 가장 오른쪽 테이블을 스트리밍합니다.

  • LEFT, RIGHT, FULL OUTER 조인은 일치하는 것이 없는 ON 절에 대한 더 많은 제어를 제공하기 위해 존재해요. 예를 들어 이 쿼리:
  SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)

는 a의 모든 행에 대해 행을 반환합니다. a.key와 같은 b.key가 있으면 출력 행은 a.val, b.val이 되고, 대응하는 b.key가 없으면 a.val, NULL이 돼요. 대응하는 a.key가 없는 b의 행은 버려집니다. "FROM a LEFT OUTER JOIN b" 문법은 어떻게 동작하는지 이해하기 위해 한 줄에 써야 해요. 이 쿼리에서 a는 b의 왼쪽(LEFT)에 있으므로 a의 모든 행이 유지됩니다. RIGHT OUTER JOIN은 b의 모든 행을 유지하고, FULL OUTER JOIN은 a와 b의 모든 행을 유지합니다. OUTER JOIN 의미는 표준 SQL 명세를 따라야 합니다.

  • 조인은 WHERE 절 전에 발생해요. 따라서 조인 출력을 제한하려면 그 요구사항이 WHERE 절에 있어야 하고, 그렇지 않으면 JOIN 절에 있어야 해요. 이 문제의 큰 혼동 포인트는 파티셔닝된 테이블입니다:
  SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)
  WHERE a.ds='2009-07-07' AND b.ds='2009-07-07'

는 a를 b와 조인해 a.val과 b.val 목록을 만듭니다. 하지만 WHERE 절은 조인 출력에 있는 a와 b의 다른 컬럼도 참조해 걸러낼 수 있어요. 그런데 JOIN의 행이 a에 대한 키를 찾고 b에 대한 키를 찾지 못하면 b의 모든 컬럼이 NULL이 되며, ds 컬럼까지도 NULL이 됩니다. 즉 유효한 b.key가 없던 조인 출력의 모든 행을 걸러내게 되므로 LEFT OUTER 요구사항을 스스로 무시하게 됩니다. 다시 말해, WHERE 절에서 b의 어떤 컬럼을 참조하면 LEFT OUTER 조인의 의미가 무의미해져요. 대신 OUTER JOIN을 할 때는 이 문법을 사용하세요:

  SELECT a.val, b.val FROM a LEFT OUTER JOIN b
  ON (a.key=b.key AND b.ds='2009-07-07' AND a.ds='2009-07-07')

그 결과 조인의 출력이 사전 필터링되어, 유효한 a.key가 있지만 일치하는 b.key가 없는 행에 대해 사후 필터링 문제가 발생하지 않습니다. 같은 논리가 RIGHT와 FULL 조인에도 적용됩니다.

  • 조인은 교환(commutative)되지 않아요! 조인은 LEFT든 RIGHT든 상관없이 왼쪽 결합(left-associative)입니다.
  SELECT a.val1, a.val2, b.val, c.val
  FROM a
  JOIN b ON (a.key = b.key)
  LEFT OUTER JOIN c ON (a.key = c.key)

…는 먼저 a를 b에 조인하고, 다른 테이블에 대응하는 키가 없는 a나 b의 모든 것을 버립니다. 그 줄어든 테이블을 c에 조인해요. a와 c에는 존재하지만 b에는 없는 키가 있으면 직관에 어긋나는 결과가 생깁니다: 그 행(a.val1, a.val2, a.key 포함)은 b에 없으므로 "a JOIN b" 단계에서 통째로 버려집니다. 결과에 a.key가 없으므로 c와 LEFT OUTER JOIN할 때 a.key와 일치하는 c.key가 없어 c.val이 들어오지 못합니다(그 a의 행이 제거됐기 때문). 마찬가지로, 이것이 LEFT가 아니라 RIGHT OUTER JOIN이라면 NULL, NULL, NULL, c.val 같은 더 이상한 결과가 됩니다. 조인 키로 a.key=c.key를 지정했지만 첫 JOIN과 일치하지 않는 a의 모든 행을 버렸기 때문입니다. 더 직관적인 결과를 얻으려면 FROM c LEFT OUTER JOIN a ON (c.key = a.key) LEFT OUTER JOIN b ON (c.key = b.key)로 해야 합니다.

  • LEFT SEMI JOIN은 상관없는(noncorrelated) IN/EXISTS 서브쿼리 의미를 효율적으로 구현해요. Hive 0.13부터 IN/NOT IN/EXISTS/NOT EXISTS 연산자를 서브쿼리로 지원하므로, 대부분의 이런 조인을 더 이상 수동으로 수행할 필요가 없어요. LEFT SEMI JOIN의 제약은 오른쪽 테이블이 조인 조건(ON 절)에서만 참조되어야 하고, WHERE나 SELECT 절 등에서는 참조할 수 없다는 점입니다.
  SELECT a.key, a.value
  FROM a
  WHERE a.key in
   (SELECT b.key
    FROM B);

는 다음으로 다시 쓸 수 있어요:

   SELECT a.key, a.val
   FROM a LEFT SEMI JOIN b ON (a.key = b.key)
  • 조인되는 테이블 중 하나를 제외한 모든 테이블이 작으면, 조인을 맵 전용(map only) 잡으로 수행할 수 있어요. 쿼리
  SELECT /*+ MAPJOIN(b) */ a.key, a.value
  FROM a JOIN b ON a.key = b.key

는 리듀서가 필요 없습니다. A의 모든 매퍼에 대해 B가 완전히 읽혀요. 제약은 FULL/RIGHT OUTER JOIN b를 수행할 수 없다는 것입니다.

  • 조인되는 테이블이 조인 컬럼으로 버킷화(bucketized)되어 있고, 한 테이블의 버킷 수가 다른 테이블의 버킷 수의 배수이면, 버킷을 서로 조인할 수 있어요. 테이블 A가 4개 버킷, 테이블 B가 4개 버킷이면 다음 조인
  SELECT /*+ MAPJOIN(b) */ a.key, a.value
  FROM a JOIN b ON a.key = b.key

을 매퍼에서만 수행할 수 있습니다. A의 각 매퍼에 대해 B를 완전히 가져오는 대신 필요한 버킷만 가져옵니다. 위 쿼리에서 A의 버킷 1을 처리하는 매퍼는 B의 버킷 1만 가져와요. 이는 기본 동작이 아니며 다음 파라미터로 제어됩니다:

  set hive.optimize.bucketmapjoin = true
  • 조인되는 테이블이 조인 컬럼으로 정렬·버킷화되어 있고 버킷 수가 같으면, 정렬-병합 조인(sort-merge join)을 수행할 수 있어요. 대응하는 버킷들이 매퍼에서 서로 조인됩니다. A와 B가 모두 4개 버킷이면,
  SELECT /*+ MAPJOIN(b) */ a.key, a.value
  FROM A a JOIN B b ON a.key = b.key

를 매퍼에서만 수행할 수 있습니다. A의 버킷을 위한 매퍼가 B의 대응 버킷을 탐색해요. 이는 기본 동작이 아니며 다음 파라미터를 설정해야 합니다:

  set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
  set hive.optimize.bucketmapjoin = true;
  set hive.optimize.bucketmapjoin.sortedmerge = true;

MapJoin 제약 사항(MapJoin Restrictions)

  • 조인되는 테이블 중 하나를 제외한 모든 테이블이 작으면, 조인을 맵 전용 잡으로 수행할 수 있어요. 쿼리
  SELECT /*+ MAPJOIN(b) */ a.key, a.value
  FROM a JOIN b ON a.key = b.key

는 리듀서가 필요 없습니다. A의 모든 매퍼에 대해 B가 완전히 읽혀요.

  • 다음은 지원되지 않습니다.
    • Union 다음에 MapJoin
    • Lateral View 다음에 MapJoin
    • Reduce Sink (Group By/Join/Sort By/Cluster By/Distribute By) 다음에 MapJoin
    • MapJoin 다음에 Union
    • MapJoin 다음에 Join
    • MapJoin 다음에 MapJoin
  • 구성 변수 hive.auto.convert.join(true로 설정 시)은 가능하면 런타임에 조인을 자동으로 mapjoin으로 변환하며, mapjoin 힌트 대신 이걸 사용해야 합니다. mapjoin 힌트는 다음 쿼리에만 사용해야 해요.
  • 모든 입력이 버킷화되거나 정렬되었고, 조인을 버킷화된 맵사이드 조인이나 버킷화된 정렬-병합 조인으로 변환해야 할 때.
  • 서로 다른 키에 여러 mapjoin을 두는 가능성을 고려해 보세요:
select /*+MAPJOIN(smallTableTwo)*/ idOne, idTwo, value FROM
  ( select /*+MAPJOIN(smallTableOne)*/ idOne, idTwo, value FROM
    bigTable JOIN smallTableOne on (bigTable.idOne = smallTableOne.idOne)                                                   
  ) firstjoin                                                             
  JOIN                                                                  
  smallTableTwo ON (firstjoin.idTwo = smallTableTwo.idTwo)

위 쿼리는 지원되지 않습니다. mapjoin 힌트가 없으면 위 쿼리는 2개의 맵 전용 잡으로 실행됩니다. 사용자가 입력이 메모리에 들어갈 만큼 충분히 작다는 걸 미리 안다면, 다음 구성 파라미터로 쿼리가 단일 맵-리듀스 잡으로 실행되게 할 수 있습니다.

+ hive.auto.convert.join.noconditionaltask - Hive가 입력 파일 크기에 기반해 일반 조인을 mapjoin으로 변환하는 최적화를 활성화할지 여부. 이 파라미터가 켜져 있고, n-way 조인의 n-1개 테이블/파티션 크기 합이 지정된 크기보다 작으면 조인이 직접 mapjoin으로 변환된다 (조건부 태스크 없음).
+ hive.auto.convert.join.noconditionaltask.size - hive.auto.convert.join.noconditionaltask가 꺼져 있으면 이 파라미터는 효과가 없다. 그러나 켜져 있고, n-way 조인의 n-1개 테이블/파티션 크기 합이 이 크기보다 작으면 조인이 직접 mapjoin으로 변환된다(조건부 태스크 없음). 기본값은 10MB.

조인 최적화(Join Optimization)

외부 조인의 프레디킷 푸시다운(Predicate Pushdown in Outer Joins)

외부 조인의 프레디킷 푸시다운에 대한 정보는 Hive Outer Join Behavior를 참고하세요.

Hive 0.11 버전의 개선 사항

Hive 0.11.0에 도입된 조인 최적화 개선 사항은 Join Optimization을 참고하세요. 향상된 최적화에서 힌트 사용은 덜 강조됩니다 (HIVE-3784 및 관련 JIRA).

더 알아보기 (Learn more)

Hive 조인의 핵심은 "매핑/버퍼링 순서"와 "큰 테이블을 마지막에" 두는 것, 그리고 WHERE vs JOIN 조건 위치예요. LEFT SEMI JOIN과 MAPJOIN 힌트, 버킷 조인을 함께 공부하면 대용량 조인 성능을 크게 올릴 수 있습니다.