[Tibero] Tibero에서 UPSERT 쿼리 구현하기

 지난 글에서 개발하던 앱 서버를 운영하던 중, 이슈가 발생해 해당 이슈를 해결하기 위해 UPSERT 쿼리를 구현할 필요성이 생겼다. UPSERT란, 키 값이 존재하는 row에 대해서는 UPDATE를 수행하고, 그렇지 않은 row에 대해서는 INSERT를 수행하는 쿼리이다. MySQL, PostgreSQL, Oracle 등 많이 사용하는 데이터베이스에서는 UPSERT를 수행하기 위한 쿼리가 제공된다.

 

간략하게 (전적으로 내 기준) 기억하기 쉽도록 각 DBMS에서 제공되는 UPSERT 쿼리를 정리하면 다음과 같다.

• MySQL: ON DUPLICATE KEY UPDATE
• PostgreSQL: ON CONFLICT DO UPDATE SET
• ORACLE: MERGE WHEN MATHCED THEN WHEN NOT MATCHED THEN

 

 해당 글에서는 앱 서버 운영 중 마주친 이슈를 해결하기 위해 이슈를 해결하기 위해 앱 서버의 데이터베이스인 Tibero에서 해당 쿼리를 구현한 방법을 기록한다.

 

 

---

# MySQL UPSERT 테스트

 

 Tibero에서 UPSERT를 구현하기 앞서, MySQL에서 UPSERT를 어떻게 구현할 수 있는지 테스트해 보았다. 센서 id가 존재하면 센서 상태(status)를 OFF로 update하고, 센서 id가 없으면 센서 상태가 ON이 되도록 센서 id와 상태를 insert하는 작업을 수행하고자 한다.

 

 테스트를 위해 아래와 같이 테스트 데이터베이스, 유저를 생성해 주었다.

 

 그리고 sensor_status라는 테스트 테이블을 생성했다. 이 때, 센서 id가 존재하는 값인지 확인해야 하므로, 센서 id에 해당하는 NAME 열을 unique index로 지정해 주었다.

CREATE TABLE UPSERT.status (
name VARCHAR(10) NOT NULL,
status VARCHAR(3) NOT NULL,
PRIMARY KEY (name),
UNIQUE INDEX name_UNIQUE (name ASC) VISIBLE);

 

 UPSERT 쿼리는 다음과 같이 작성한다.

INSERT INTO status (NAME, status)
VALUES ('ST2000', 'ON')
ON DUPLICATE KEY UPDATE status='OFF';

 

 해당 쿼리를 여러 번 수행하며 UPSERT 문이 잘 작동하는지 확인한다.

 중복되는 키 값이 존재하는 경우, UPSERT 쿼리 수행 시 '2 rows affected'라는 메시지가 뜨는 것을 볼 수 있다. Documentation에 의하면 INSERT가 수행된 경우 '1 row affected', UPDATE가 정상적으로 수행된 경우 '2 rows affected', UPDATE가 수행되나 기존 값과 같은 경우 '0 row affected'라는 결과가 나타난다고 한다. 더 자세한 경우를 알아보고 싶으면 이 글을 참고해도 좋을 듯.

 

 MySQL에서 이와 같이 UPSERT 쿼리를 구현할 수 있다. 찾아 보니 UPSERT를 수행해야 하는 상황에 REPLACE를 사용하는 방법도 있다고 하니, 참고.

 

 

---

 

# Tibero 데이터베이스에서 UPSERT 쿼리 구현

 

 Tibero에서 UPSERT 작업을 수행하려면 Oracle 데이터베이스와 비슷하게 MERGE 문을 사용하면 된다. 아래 사진에서와 같이 공식 Tibero SQL 참조서는 여러 삽입, 갱신, 삭제 작업을 수행하기 위해 MERGE 문을 사용하라고 안내한다.

 

 

 공식 참조서를 따라 쉽게 이슈 해결을 위한 UPSERT 쿼리를 구현할 수 있었다. 다만, 단일 테이블에서 row가 존재하는지를 판단해야 했기 때문에 USING 절에 DUAL 테이블을 사용했다.

 

MERGE INTO IOT.TEST_SENSOR_STATUS T
USING DUAL
ON (T.SENSOR_ID = 'ST20000509')
WHEN MATCHED THEN
	UPDATE
		SET T.STATUS = 'OFF'
WHEN NOT MATCHED THEN
	INSERT (T.SENSOR_ID, T.STATUS)
	VALUES ('ST20000509', 'ON');

 

 UPSERT 쿼리가 잘 동작하는지 확인해 보자.

INSERT	UPDATE

 

 

---

 

 위와 같은 과정을 통해 Tibero 데이터베이스에서 쉽게 UPSERT 쿼리를 구현할 수 있다. 더 알아보고 싶은 부분은 다음과 같다.

 

• USING 절에 테이블을 사용할 때와 뷰를 사용할 때, 어떤 차이가 있는가? 성능적인 측면에서?
• UPSERT 문과 연결된 API 설계를 어떻게 해야 할 것인가?