Chapter 6. 타임트래블을 이용한 복구(Lab)

Snowflake SE·2024년 12월 27일

Zero to Snowflake

목록 보기

12/21

0. 실습 시나리오

관리자는 푸드트럭의 연식을 추가하고자 작업용 테이블을 만들어 컬럼을 추가하고 잘못 입력된 필드값도 정리하려고 합니다. 모든 작업이 끝나면 작업용 테이블을 운영 테이블로 전환하고 이전에 사용한 테이블은 삭제하려고 합니다.

상황 1. 푸드트럭의 제조사명이 Ford_인 필드를 Ford로 고치려고 합니다. 그런데 WHERE 절을 쓰지 않아 모든 푸드트럭의 제조사명이 Ford로 업데이트되어 버렸습니다. 이제 어떻게 해야할까요?
상황 2. 앞의 상황을 해결하고 작업용 테이블과 운영용 테이블 전환도 모두 잘 마쳤습니다. 마지막으로 작업용 테이블을 드롭하고 일을 마치려고 했는데 실수로 운영용 테이블을 드롭했네요.

시나리오

1. 워크시트와 데이터 준비

Projects > Workspace 에서 +Add new를 클릭하여 새 SQL File을 만듭니다
- 이름 : Lab6. Time Travel

Snowsight UI또는 다음과 같은 SQL로 컨텍스트를 설정합니다

// 컨텍스트 설정
USE ROLE sysadmin ;
USE WAREHOUSE tasty_de_wh ;
USE DATABASE frostbyte_tasty_bytes ;
USE SCHEMA raw_pos ;

클론을 이용하여 작업용 테이블 준비
```
// 작업용 테이블 준비
CREATE OR REPLACE TABLE truck_dev 
 CLONE truck ;
```
- 지금까지는 지정한 웨어하우스가 활성화되지 않았다는 점도 다시 한번 확인해 보세요

2. 데이터 컬럼 추가 및 수정

푸드 트럭의 연식을 저장하는 컬럼을 추가하려고 합니다. 먼저 데이터를 조회해서 연식을 제대로 표현하는지 확인해봅니다
```
// 푸드 트럭의 연식 조회
SELECT truck_id, year, make, model, (year(current_date()) - year) as truck_age
  FROM truck_dev ;
```
- (year(current_date()) - year)를 사용한 것이 적절해 보입니다

그러면 트럭의 연식을 나타낼 수 있는 컬럼을 하나 추가해 보겠습니다

// 컬럼 추가
ALTER TABLE truck_dev 
  ADD COLUMN truck_age number(4) ;
 
// 추가된 컬럼에 데이터 업데이트
UPDATE truck_dev 
   SET truck_age = (year(current_date()) - year) ; 

// 데이터 확인   
SELECT truck_id, year, make, model, truck_age
  FROM truck_dev ;

이제는 푸드 트럭의 제조사가 어떤 회사들이 있는지 확인해 봅니다
```
// 푸드 트럭 제조사
SELECT distinct make 
  FROM truck_dev ;
```

Ford사에 오타가 있군요. 이를 수정하겠습니다

// 데이터 수정
UPDATE truck_dev
   SET make = 'Ford'
-- WHERE make = 'Ford_' --oops
;

// 데이터 확인   
SELECT truck_id, year, make, model, truck_age
  FROM truck_dev ;

데이터를 수정할 때 WHERE절의 코맨트가 있던 것을 지웠어야 했는데 깜빡 실수를 해버렸네요. 너무 놀라지 말고 차분하게 해결해 봅시다

3. Time Travel 데이터 복구

Snowflake의 모든 SQL 작업 내역은 information_schema.query_history에 기록되어 있습니다. 여기서 조금전에 잘못 수행한 업데이트 문장을 찾아보겠습니다.

// 쿼리 히스토리 검사
SELECT query_id,
       query_text,
       user_name,
       query_type,
       start_time
  FROM TABLE(information_schema.query_history())
 WHERE 1=1
   AND query_type = 'UPDATE'
   AND query_text LIKE '%truck_dev%'
 ORDER BY start_time DESC;

WHERE 절을 뺀 잘못 수행한 업데이트 문장을 찾았습니다. 여기서 query_id 값을 기록해도 괜찮지만 다음과 같이 변수에 담아 놓을 수도 있습니다

// query_id 변수
SET query_id = 
(
    SELECT TOP 1 query_id
      FROM TABLE(information_schema.query_history())
     WHERE 1=1
       AND query_type = 'UPDATE'
       AND query_text LIKE '%truck_dev%'
     ORDER BY start_time DESC
);   

SELECT $query_id ;

잘못 수행한 업데이트 문장이 실행되기 전의 테이블로 되돌리도록 하겠습니다

// Time Travel 쿼리를 이용하여 테이블 복구
CREATE OR REPLACE TABLE truck_dev AS 
SELECT * FROM truck_dev
BEFORE (STATEMENT => $query_id);

// 데이터 확인   
SELECT truck_id, year, make, model, truck_age
  FROM truck_dev ;

이제는 실수없이 제대로 데이터를 수정합니다

// 데이터 수정
UPDATE truck_dev
   SET make = 'Ford'
 WHERE make = 'Ford_' 
;

// 데이터 확인   
SELECT truck_id, year, make, model, truck_age
  FROM truck_dev ;

Time Travel 쿼리를 사용하기 위한 키워드
- AT: AT 키워드는 지정된 매개변수와 동일한 타임스탬프를 갖는 문 또는 트랜잭션에 의해 수행된 모든 변경 사항을 요청이 포함하도록 지정합니다.
- BEFORE: BEFORE 키워드는 요청이 지정된 매개변수 바로 앞의 지점을 참조하도록 지정합니다.
- TIMESTAMP: Time Travel에 사용할 정확한 날짜와 시간을 지정합니다.
- OFFSET: Time Travel에 사용하기 위한 현재 시간의 초 차이를 지정합니다.
- STATEMENT: Time Travel의 기준점으로 사용할 문의 쿼리 ID를 지정합니다.

4. 작업 테이블을 운영용으로 변경

이제 작업 테이블에 대해 모든 수정사항이 반영되었습니다. 작업 테이블을 운영용으로 변경하고 테이블 목록도 정리하고자 합니다

작업용 테이블을 운영용 테이블로 교체합니다

// 테이블 교체
ALTER TABLE truck_dev SWAP WITH truck;

// 푸드 트럭 제조사 확인
SELECT distinct make 
  FROM truck ;

// 푸드 트럭 제조사 확인
SELECT distinct make 
  FROM truck_dev ;

테이블을 정리합니다

// 테이블 드롭
DROP TABLE truck_dev ; 
DROP TABLE truck ; --oops

잠깐..지금 방금 뭘 지운건가요? 테이블을 정리하고 일을 마치려다가 너무 성급하게 모두 드롭했군요.
다시 조금 전과 같이 작업을 해야할까요?

5. 드롭된 테이블 복구

다행히도 snowflake에는 drop된 오브젝트를 되살릴 수 있는 undrop 명령이 있어 더 간편하게 복구할 수 있습니다
```
// 테이블 언드롭
UNDROP TABLE truck ; 
```

이제는 정말로 일을 마치고 퇴근할 수 있겠네요

// 데이터 확인   
SELECT truck_id, year, make, model, truck_age
  FROM truck ;

Snowflake SE

Snowflake Korea SE

이전 포스트

Chapter 6. Time Travel

다음 포스트