[Bigquery] Merge into 사용 가이드

1.Merge란? 

Bigquery에는 Upsert가 없고, Merge라는 것이 존재한다.이름하고 사용법만 조금 다르지, 개념은 완전 똑같다.

*Upsert : Update + Insert의 개념으로 , 대상 테이블에 기존 데이터가 있는 경우 해당 데이터를 업데이트 하고, 없는 경우에는 새 데이터를 삽입 

*Merge : 조건에 따라 대상 테이블의 레코드를 업데이트 하거나 삽입 

 

2.Bigquery Merge 기본 구문 및 실제 예시 

MERGE INTO target_table AS target -- Merge를 수행할 대상 테이블
USING source_table AS source -- Merge에 사용할 소스 데이터
ON target.key_column = source.key_column -- 대상 테이블과 소스 테이블을 연결하는 고유 키
WHEN MATCHED THEN -- 매칭된다면
  UPDATE SET -- 업데이트
    target.column1 = source.column1, -- 소스의 값을 타겟의 값으로 
    target.column2 = source.column2 
WHEN NOT MATCHED THEN -- 매칭 되지 않는다면
  INSERT (key_column, column1, column2) -- INSERT 
  VALUES (source.key_column, source.column1, source.column2); -- 소스의 값들을 대상 테이블에 INSERT

위가 Merge 구문의 기본틀이며, CHAT GPT 에서 제안해준 예시대로 한번 적용해보자. 

 

아래와 같은 테이블 두개가 있다.

-- orders 테이블
CREATE TABLE orders (
  order_id INT64 PRIMARY KEY,
  product_name STRING,
  quantity INT64
);

-- new_orders 테이블
CREATE TABLE new_orders (
  order_id INT64 PRIMARY KEY,
  product_name STRING,
  quantity INT64
);

 

해당 테이블은 order_id 1에서 quantity의 차이가 있으며, new_orders에는 order_id 3이 없고 대신에 5인 새로운 행이 존재하는 차이점이 있다. 그럼 이 테이블을 예시로 merge를 쳐보겠다. 

MERGE INTO orders AS target
USING new_orders AS source
ON target.order_id = source.order_id
WHEN MATCHED THEN
  UPDATE SET
    target.product_name = source.product_name,
    target.quantity = source.quantity
    -- 전체다 업데이트 치고 싶으면 target=source 단, 테이블의 컬럼 순서와 데이터 유형이 일치해야함
WHEN NOT MATCHED THEN
  INSERT (order_id, product_name, quantity)
  VALUES (source.order_id, source.product_name, source.quantity);

위 SQL을 그대로 해석해보자면, new_orders 테이블을 사용해서 orders 테이블을 Merge 하는 것이다. 키값은 order_id로 선정하며 양쪽 테이블에 같은 key값이 있으면 new_orders의 행을 orders의 행으로 UPDATE 친다. 반대로 두 테이블의 키값이 맞는게 없다면 new_orders에서 orders로 insert한다.  따라서 해당 구문을 실행하면 다음과 같게 orders 테이블이 변경된다. 

 

 

 

*  MATCHED 상태에서 양쪽이 같은 경우에도 UPDATE 쳐서 뭐함? 

=> 위 예시의 order_id 1일 때 처럼 양쪽행이 다를 수도 있지만, 2,3 처럼 양쪽 행이 같은 경우도 있다. 이 경우까지 업데이트 쳐버리면 너무 불필요하지 않을까 ? 

 

맞다. 업데이트할 내용이 동일하거나 불필요한 작업을 수행하게 되는 것이다. 그러나 MERGE문 자체가 조건에 맞는 레코드를 업데이트 할 것으로 기술된 상태에서 해당 조건에 맞는 레코드를 모두 업데이트하도록 동작되기 때문에, 업데이트가 불필요한 경우에도 문법적으로는 수행된다. 

 

값이 동일한 경우의 불필요한 업데이트를 피하고 싶다면, 조건절에 MATCHED AND 하고 원하는 조건을 써주면 된다. 

WHEN MATCHED AND target.quantity <> source.quantity OR target.product_name <> source.product_name THEN

 

3.왜 쓰나요? 

이번 회사에서 Upsert와 Merge의 개념을 처음 알았는데, 우리 회사의 경우 아래의 상황에 대비하기 위해 사용한다. 

예시를 보면 바로 이해될 듯 해서 이 내용도 같이 서술한다! 

우리의 경우, UTC 기준 새벽 1시에 로컬에서 빅쿼리로 ETL을 진행한다. 스트레스 테스트 진행결과 MAX 시간보다 여유있는 UTC 기준 새벽 1시 30분에 ETL이 완료된 Log 데이터들을 활용하여 통계테이블을 구성하는 Bigquery Daily Job이 돌아간다. 

 

그런데 만약, 혹시라도 데이터가 스트레스 테스트했던 것보다 훨씬 많이 들어와서 ETL 시간이 30분을 넘어가게 된다면, Daily 데이터가 모두 반영되지 않은 채로 Daily 통계 테이블이 생성되는 것이다. 

 

데이터가 쌓이지 않은 채로 통계 테이블이 완성되고, 경영진과 기획팀 사업팀은 데이터가 덜 들어온 BI를 보고 의사결정을 하게되는 불상사가 발생할 수 있는 것이다! 매우매우 중요한 것이다. 

 

2번 과정 이후 Local의 데이터와 Bigquery의 데이터를 비교하여 제대로 적재가 되면 Success, 적재가 덜 되면 Fail을 알려준다. 이 때 Fail이 뜨면 데이터 엔지니어분께서 미처 넘어오지 못한 local의 로그 데이터를 다시 ETL 해주신다. 그럼 이후 MERGE를 통해 데이터가 덜들어왔을 떄 Log 테이블로 만들었던 통계 테이블과 데이터가 다 들어왔을 때 Log 테이블로 만들었던 통계 테이블을 알아서 비교해서 없는 부분만 채워주면 문제가 해결되기 때문에 쿼리 한번 실행해주면 가볍게 해결된다. 

 

MERGE를 안쓴다면 오늘 추가된 통계에 대해 Delete 하고 다시 Insert하는 과정이 반복될 것이다.