아래 코드는 일별로 유저수와 그 유저의 피쳐 a,b,c 에 대해 각각 평균과 중위수를 구하는 코드이다. 이 코드는 상당히 시간이 오래 걸린다.

select 
date_id,
count(distinct user_id) as user_cnt,
avg(a) as avg_a,
avg(b) as avg_b,
avg(c) as avg_c,
percentile_approx(a, 0.5) as med_a,
percentile_approx(b, 0.5) as med_b,
percentile_approx(c, 0.5) as med_b
from table
group by date_id

그런데, user_cnt 와 평균을 구하는 코드를 제외한 아래 쿼리는 빠르게 실행된다. 왜 그럴까?

select 
date_id,
percentile_approx(a, 0.5) as med_a,
percentile_approx(b, 0.5) as med_b,
percentile_approx(c, 0.5) as med_b
from table
group by date_id

챗 GPT 를 활용해 답변을 생성해보니 아래와 같다고 한다.

유저수를 count하는 연산과 평균을 구하는 연산이 맵리듀스 분산 처리시(Big Data 환경을 가정) 각 노드에서 개별적으로 이루어져서 빠른 처리가 가능하지만, 중앙값을 구하는 percentile_approx 연산은 전체 데이터를 한 노드에서 모아서 처리해야 하기 때문에 상대적으로 시간이 많이 소요됩니다.

하지만, 여러 컬럼의 중앙값을 동시에 구하는 쿼리에서는 중앙값을 구하는 작업이 모든 컬럼에 대해 동시에 이루어지기 때문에 시간이 대폭 줄어듭니다.

즉, count와 avg는 분산처리가 가능하여 각각의 연산이 독립적이지만 percentile_approx는 분산처리가 어려워서 전체 데이터에 대한 연산이 필요하여 시간이 오래 걸린 것입니다. 두번째 쿼리는 percentile_approx만 있기 때문에 동시에 처리될 수 있어 속도가 빠른 것입니다.

확인하기 위해, 각각의 연산을 따로 실행하는 쿼리를 만들어 시간을 체크해보면 더 정확한 비교가 가능할 것입니다.

결론

- hive sql 에서 percentile_approx 는 분산처리가 어렵고 전체 데이터에 대한 연산이 필요하기 때문에 시간이 오래 걸린다. 

- hive sql 에서 percentile_approx 를 활용해 중위수를 구할 때에는 다른 집계는 하지 말고 중위수만 구하자. 그럼 조금 더 빠르다. 

여러 테이블을 Full outer join 하기

/* 2022-08-25 by DeepPlay */

결론: full outer join 은 3개 이상의 테이블에 대해서는 웬만해서는 쓰지 않는 것이 좋다.

만약 3개 이상의 테이블에 대해 full outer join 을 하려고 한다면, 다른 join 방법을 고려해보자. 

여러 테이블을 종합해서 하나의 테이블로 만들고 싶은데, 각 테이블들간 포함관계가 없어 full outer join을 해야하는 상황. 

예를 들면, 아래와 같은 4개의 테이블을 full outer join 을 해서 personid 와 row 개수가 1:1인 테이블을 만들고 싶은 상황이다. 

employee1
+---------------------+-----------------+--+
| employee1.personid  | employee1.name  |
+---------------------+-----------------+--+
| 111                 | aaa             |
| 222                 | bbb             |   
| 333                 | ccc             | 
+---------------------+-----------------+--+
employee2
+---------------------+----------------+--+
| employee2.personid  | employee2.sal  |
+---------------------+----------------+--+
| 111                 | 2              |
| 200                 | 3              |
+---------------------+----------------+--+
employee3
+---------------------+------------------+--+
| employee3.personid  | employee3.place  |
+---------------------+------------------+--+
| 111                 | bbsr             |
| 300                 | atl              |
| 200                 | ny               |
+---------------------+------------------+--+
employee4
+---------------------+---------------+--+
| employee4.personid  | employee4.dt  |
+---------------------+---------------+--+
| 111                 | 2019-02-21    |
| 300                 | 2019-03-18    |
| 400                 | 2019-03-18    |
+---------------------+---------------+--+

원하는 병합 테이블

+-----------+---------+--------+----------+-------------+--+
| personid  | f.name  | u.sal  | v.place  |   v_in.dt   |
+-----------+---------+--------+----------+-------------+--+
| 111       | aaa     | 2      | bbsr     | 2019-02-21  |
| 200       | NULL    | 3      | ny       | NULL        |
| 222       | bbb     | NULL   | NULL     | NULL        |
| 300       | NULL    | NULL   | atl      | 2019-03-18  |
| 333       | ccc     | NULL   | NULL     | NULL        |
| 400       | NULL    | NULL   | NULL     | 2019-03-18  |
+-----------+---------+--------+----------+-------------+--+

해결 방법

가장 깔끔한 방법은 유니크한 personid 를 갖는 병합 테이블을 하나 만들고, 이 테이블에 left outer join 을 하는 것이다. 

create table total_user as 
select ditinct account_id from (
    select distinct account_id from employee1
    union all 
    select distinct account_id from employee2
    union all 
    select distinct account_id from employee3
    union all 
    select distinct account_id from employee4
)

select t0.personid,
`name`, sal, place, dt
from total_user t0
left outer join employee1 t1 on t0.personid = t1.personid
left outer join employee1 t2 on t0.personid = t2.personid
left outer join employee1 t3 on t0.personid = t3.personid
left outer join employee1 t4 on t0.personid = t4.personid

참고) 잘못된 SQL

-> 이렇게 하게 되면, 테이블1과 계속해서 full outer join 을 하기 때문에, 중복된 personid 가 생기게 된다.

select coalesce(f.personid, u.personid, v.personid, v_in.personid) as personid,f.name,u.sal,v.place,v_in.dt
from employee1 f FULL OUTER JOIN employee2 u on f.personid=u.personid
FULL OUTER JOIN employee3 v on f.personid=v.personid
FULL OUTER JOIN employee4 v_in on f.personid=v_in.personid;