[HIVE] UNION, SubQuery, With 테이블 합치기 팁.

다수의 테이블을 합쳐서 통계를 산출 시에 UNION을 많이 사용하게 된다.

UNION과 UNION ALL의 차이는 다들 알고 있으리라 언급하자면 중복 데이터를 제거하는 과정이 UNION에 포함되어있다.

테이블을 합칠 때 UNION으로 합치게 되면 각 테이블마다 정렬이 들어가는 것을 실행계획을 통계 알 수 있다.

(그래서 느리다.)

UNION ALL 을 사용하게 되면 중복 상관없이 그냥 합쳐주기만 해서 빠르다.

다수의 테이블인 경우엔 각 테이블마다 중복데이터를 제거해가면서 합치는 것보다 일단 다 합쳐놓고 중복데이터를 제거하는게 빠르다.

실제로 UNION을 이용한 쿼리에선 HIVE의 CBO(Cost-based Optimizer)가 동작하지 않는다.

UNION ALL을 이용하여 테이블을 합치려다가 HIVE는 CTE(Common Table Expression) 을 지원한다는 사실을 알았다. 

WITH문으로 더 잘 알려진 이 구문은 오라클 사용자라면 알고 있을 것이다. 

단 MySQL은 지원하지 않으며, MariaDB는 10.2.1 버전부터 지원하고 있다.

UNION ALL을 이용하여 테이블을 합치는 방법은 다음 두가지가 있겠다.

1. 서브쿼리를 이용

2. with

위 두가지 방법으로 쿼리를 짜보고 실행계획을 보았으나 실행계획까지도 똑같다. 

with구문이 더 가독성이 좋다는데... 난 글쎄.. 그냥 쓰고 싶은거 써도 상관없다.

다음 두 쿼리는 동일하다.

SET TDATE='01'

SELECT COUNT(DISTINCT(usr_key)) FROM (

  SELECT mdate, usr_key FROM table1 WHERE mdate>=${hiveconf:TDATE}

  UNION ALL

  SELECT mdate, usr_key FROM table2 WHERE mdate>=${hiveconf:TDATE}

  UNION ALL

  SELECT mdate, usr_key FROM table3 WHERE mdate>=${hiveconf:TDATE}

  UNION ALL

  SELECT mdate, usr_key FROM table4 WHERE mdate>=${hiveconf:TDATE}

) t1

SET TDATE='01'

WITH t1 AS (SELECT usr_key FROM s1

  UNION ALL SELECT usr_key FROM s2

  UNION ALL SELECT usr_key FROM s3

  UNION ALL SELECT usr_key FROM s4

  ),

      s1 as (SELECT mdate, usr_key FROM table1 WHERE mdate>=${hiveconf:TDATE}),

      s2 as (SELECT mdate, usr_key FROM table2 WHERE mdate>=${hiveconf:TDATE}),

      s3 as (SELECT mdate, usr_key FROM table3 WHERE mdate>=${hiveconf:TDATE}),

      s4 as (SELECT mdate, usr_key FROM table4 WHERE mdate>=${hiveconf:TDATE})

SELECT COUNT(DISTINCT(usr_key)) FROM t1;

stackoverflow를 뒤져봐도 subquery와 with은 동일같거나 다름없다고 한다.

차이가 있다면 가독성의 차이와 with문이 recursive한 점인데.. recursive하게 활용하는 with문에 대해서 더 언급하고 싶었으나 길어지니 나중에..