[Hive] Merge문 이해하기

Merge문을 사용하는 이유

• merge문 은 match문과 unmatch문의 분기를 이용하여 내용을 합치는데 사용한다.
• hive에서 merge문의 matched로 update, delete를 사용하려면 transaction을 true로 설정한 테이블이여야한다.
• ANSI SQL문을 사용하여 쿼리를 작성 할 수 있다.

Merge문 사용 메뉴얼

MERGE INTO <target table> AS T USING <source expression/table> AS S
ON <boolean expression1>
WHEN MATCHED [AND <boolean expression2>] THEN UPDATE SET <set clause list>
WHEN MATCHED [AND <boolean expression3>] THEN DELETE
WHEN NOT MATCHED [AND <boolean expression4>] THEN INSERT VALUES<value list>

실습

• source table : 새로 생성된 데이터나 합치고 싶은 데이터
• target_table : merge하고 싶은 원본 테이블
• merged_table : taget table과 동일한 테이블 이나 source 데이터 + target 데이터를 나타냄

Target 테이블 생성

원본 테이블 생성

CREATE TABLE if not exists default.person(
      pid     INT,
      name    STRING,
      age     INT,
      address STRING
)
CLUSTERED BY (pid) into 1 BUCKETS
STORED AS ORC
LOCATION 'hdfs://namenode:8020/person/'
TBLPROPERTIES('transactional'='true');

Dummy 데이터 insert

샘플 데이터 넣기

insert into table default.person values
(1, 'James', 22, 'Seoul'),
(2, 'Tom', 23, 'New York'),
(3, 'Jace', 24, 'San Francisco'),
(4, 'Catalina', 25, 'Virginia'),
(5, 'Nilla', 31, 'Florida'),
(6, 'Hyun', 32, 'Busan'),
(7, 'Jack', 45, 'Tokyo'),
(8, 'Micheal', 82, 'Fukuoka'),
(9, 'Sunny', 31, 'Gwangju'),
(10, 'John', 42, 'Canberra'),
(11, 'James', 94, 'Seoul'),
(12, 'Tom', 47, 'New York'),
(13, 'Jace', 56, 'San Francisco'),
(14, 'Catalina', 22, 'Florida'),
(15, 'Nilla', 36, 'Busan'),
(16, 'Hyun', 22, 'Tokyo'),
(17, 'Jack', 26, 'Fukuoka'),
(18, 'Micheal', 52, 'Fukuoka'),
(19, 'Sunny', 38, 'Gwangju'),
(20, 'John', 34, 'Canberra'),
(21, 'james', 22, 'Seoul'),
(22, 'Tom', 47, 'New York'),
(23, 'Jace', 56, 'San Francisco'),
(24, 'Catalina', 36, 'Florida'),
(25, 'Nilla', 26, 'Seoul'),
(26, 'Hyun', 52, 'Seoul'),
(27, 'Jack', 34, 'Tokyo'),
(28, 'Sunny', 38, 'Fukuoka');

Source 테이블 생성

증가하는 데이터 구현하기 위한 임시 테이블 생성

CREATE TABLE if not exists default.incremental_temp_person(
     pid     INT,
     name    STRING,
     age     INT,
     address STRING
)
CLUSTERED BY (pid) into 1 BUCKETS
STORED AS ORC
LOCATION 'hdfs://namenode:8020/incremental_temp_person/'
TBLPROPERTIES('transactional'='true');

증가 dummy data insert

적용 확인용 데이터 insert

insert into table default.incremental_temp_person values
(1, 'James', 100, 'Seoul'),
(29, 'Joon', 38, 'Fukuoka'),
(30, 'Brown', 38, 'Florida'),
(31, 'Sonny', 38, 'Seoul'),
(32, 'Hammer', 38, 'New York');

데이터 확인

source 테이블에 pid 1 을 넣어 update 되는 지 확인 나머지는 insert되는지 확인

select * from default.person;
28      Sunny   38      Fukuoka
27      Jack    34      Tokyo
26      Hyun    52      Seoul
25      Nilla   26      Seoul
24      Catalina        36      Florida
23      Jace    56      San Francisco
22      Tom     47      New York
21      james   22      Seoul
20      John    34      Canberra
19      Sunny   38      Gwangju
18      Micheal 52      Fukuoka
17      Jack    26      Fukuoka
16      Hyun    22      Tokyo
15      Nilla   36      Busan
14      Catalina        22      Florida
13      Jace    56      San Francisco
12      Tom     47      New York
11      James   94      Seoul
10      John    42      Canberra
9       Sunny   31      Gwangju
8       Micheal 82      Fukuoka
7       Jack    45      Tokyo
6       Hyun    32      Busan
5       Nilla   31      Florida
4       Catalina        25      Virginia
3       Jace    24      San Francisco
2       Tom     23      New York
1       James   22      Seoul

selecT* from default.incremental_temp_person;
32      Hammer  38      New York
31      Sonny   38      Seoul
30      Brown   38      Florida
29      Joon    38      Fukuoka
1       James   100     Seoul

Merge 및 Merged_table 확인

MERGE INTO default.person as t
USING (select  pid, 
               name, 
               age,
               address
       from default.incremental_temp_person
       ) s
  ON s.pid = t.pid
WHEN MATCHED THEN 
  UPDATE set  name    = s.name
            , age     = s.age
            , address = s.address 
WHEN NOT MATCHED THEN 
  INSERT VALUES(s.pid
              , s.name
              , s.age
              , s.address);

Merged_table (= Target_table)데이터 확인

pid 1 이 업데이트 된 것을 확인 가능하고, 나머지는 insert 된 것을 볼 수 있음, merge 문에 matched와 unmatched 되었을 때 분기 처리 되는 것을 기억하자.

selecT * from default.person;

1	James	100	Seoul
2	Tom	23	New York
3	Jace	24	San Francisco
4	Catalina	25	Virginia
5	Nilla	31	Florida
6	Hyun	32	Busan
7	Jack	45	Tokyo
8	Micheal	82	Fukuoka
9	Sunny	31	Gwangju
10	John	42	Canberra
11	James	94	Seoul
12	Tom	47	New York
13	Jace	56	San Francisco
14	Catalina	22	Florida
15	Nilla	36	Busan
16	Hyun	22	Tokyo
17	Jack	26	Fukuoka
18	Micheal	52	Fukuoka
19	Sunny	38	Gwangju
20	John	34	Canberra
21	james	22	Seoul
22	Tom	47	New York
23	Jace	56	San Francisco
24	Catalina	36	Florida
25	Nilla	26	Seoul
26	Hyun	52	Seoul
27	Jack	34	Tokyo
28	Sunny	38	Fukuoka
29	Joon	38	Fukuoka
30	Brown	38	Florida
31	Sonny	38	Seoul
32	Hammer	38	New York

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

참고 문헌

https://wikidocs.net/23562