hive 데이터 최신화

hadoop에 데이터 수집/적재를 하다보면 hive에는 update가 없기 때문에, ST영역에 들어온 신규/변경 건의 데이터들 중 각 기본키들 기준으로 최신 날짜 데이터들만 L0영역에 엎어쳐야 하는 작업이 필요하다. 쉽게 말하면 L0 영역의 데이터 최신화 작업이 필요하다.

데이터 스키마

기본키는 pkcol1, pkcol2로 복합키이다.

reg_dt는 데이터 등록일자, mod_dt는 데이터 수정일자이다.

year과 month는 파티션이다.

ST영역의 데이터는 아래와 같다. (테이블명 : st_table)

pkcol1	pkcol2	col3	reg_dt	mod_dt	year	month
d1	1	kitae	2020-01-01	2020-01-01	2020	01
d1	2	sunho	2020-01-01	2020-01-01	2020	01
d2	1	heedonk	2020-01-01	2020-01-01	2020	01
d2	1	heedong	2020-01-01	2021-08-05	2020	01
d3	1	kitaek	2020-01-01	2020-01-01	2020	01
d4	1	youngjune	2020-01-01	2020-01-01	2020	01
d4	1	youngjun	2020-01-01	2020-08-05	2020	01
d5	1	gildong	2020-01-01	2021-08-05	2020	01

L0영역의 데이터는 아래와 같다. (테이블명 : l0_table)

pkcol1	pkcol2	col3	reg_dt	mod_dt	year	month
d1	1	kitae	2020-01-01	2020-01-01	2020	01
d1	2	sunho	2020-01-01	2020-01-01	2020	01
d2	1	heedonk	2020-01-01	2020-01-01	2020	01
d3	1	kitaek	2020-01-01	2020-01-01	2020	01
d4	1	youngjune	2020-01-01	2020-01-01	2020	01

그렇다면, L0영역의 데이터 최신화는 어떻게 하면 좋을까? ST영역의 데이터 중 수정일자가 최신인 데이터들만 L0영역에 엎어치면 데이터 최신화가 이루어질 것이다.

데이터 확인

먼저, ST영역에 적재된 데이터들을 확인하자.

hive에는 기본키, 외래키 기능이 없지만, 그래도 데이터에 자신이 지정한 기본키가 있을 것이다. 수집하는대로 막 저장한 ST영역에는 아래와 같은 3가지 케이스의 데이터들이 들어올 수 있다.

• 변경의 건 : 같은 기본키, 다른 데이터

  ST영역에서 변경된 데이터는 아래와 같다.

  pkcol1	pkcol2	col3	reg_dt	mod_dt	year	month
  d2	1	heedonk	2020-01-01	2020-01-01	2020	01
  d2	1	heedong	2020-01-01	2021-08-05	2020	01
  d4	1	youngjune	2020-01-01	2020-01-01	2020	01
  d4	1	youngjun	2020-01-01	2020-08-05	2020	01

• 신규의 건 : 새로운 기본키의 데이터

  ST영역에서 신규로 추가된 데이터는 아래와 같다.

  pkcol1	pkcol2	col3	reg_dt	mod_dt	year	month
  d5	1	gildong	2020-01-01	2021-08-05	2020	01

• 삭제의 건 : 삭제된 데이터

  삭제된 데이터가 있는 경우는 실 업무에서는 거의 없기 때문에 예로 넣지 않았다.

지금은 데이터 개수가 몇개 안되서 이렇게 한눈에 파악할 수 있지만, 만약 데이터가 몇천개만 넘어가도 데이터의 변경 건이 있는지 파악하기가 힘들다.

아래 쿼리를 이용하면 쉽게 파악할 수 있다.

select pkcol1, pkcol2, count(*)
from st_table
group by pkcol1, pkcol2
having count(*) > 1;

기본키로 group by를 하여 카운트를 한다. 기본키라면 이 카운트가 1씩만 나올 것이다. 1을 초과한다면 변경 건이 있는 것이다.

신규/삭제 건을 알 수 있는 방법은 st_table의 기본키로 group by 해서 나온 row 수와 l0_table의 row수를 비교해서 다르면 신규/삭제 건이 있는 것이다.

데이터 검증을 위한 count

데이터를 엎어치기 전에, 엎어치고 난후에 제대로 데이터가 들어갔는지 검증하기 위해 미리 몇개의 데이터가 L0에 적재되어야 하는지 알 필요가 있다.

ST 테이블과 L0 테이블을 합쳐 기본키로 group by 한 데이터의 개수를 세면 된다. 쿼리는 아래와 같다.

select count(*)
from (
   	select * from st_table
    union all
	select * from l0_table
) v1
group by pkcol1, pkcol2;

데이터 최신화

이제 ST 테이블에서 최신 데이터만 조회하여 L0 테이블에 적재하자.

같은 기본키로 파티션을 나눠 수정일자 역순으로 랭크를 매긴 후, 첫번째 랭크만 가져오는 방식으로 최신 데이터만 골라내면 된다. 쿼리문은 아래와 같다.

insert overwrite table l0_table partition (year='2020', month='01')
select * from (
	select *, row_number() over(partition by pkcol1, pkcol2 order by cast(mod_dt as date) desc) rn
	from (
        select * from st_table
        union all
        select * from l0_table
    ) t1
) t2
where t2.rn = 1;