오늘은 Redshift에서 프로시저를 만드는 쿼리문에 대해 소개한다.
프로시저(procedure)란, DB에서 일어나는 일련의 작업을 정리한 절차를 쿼리로 작성하여 관계형 데이터베이스(RDBS) 시스템에 정리한 것을 이야기한다.
그래서 '프로시저를 돌린다' 라는 것은 프로시저를 call 하여 어떤 동작을 일괄적으로 처리하는 것을 의미한다. 굉장히 편리한 기능!
나는 AWS Redshift를 사용하고 있어 이에 따른 프로시저문을 쓰는데, postgresql에도 동일하게 적용되니 참고하자.
또한 DB를 다루는 툴로는 DBeaver를 사용하고 있다.
DBeaver에서 프로시저를 만들 때의 절차는 다음과 같다.
1. script에 프로시저문을 입력한 후, 전체선택 (ctrl+A) → alt+X 눌러 프로시저문 저장
2. 저장된 프로시저를 실행하려면 새로운 script를 열어 다음을 입력하여 실행한다.
call 프로시저명(input);
Procedure Script (데이터 insert 예제)
예를 들어 가장 많이 쓰는 프로시저 중 하나인, 하나의 테이블에 데이터를 insert하는 프로시저 예제이다.
이 경우 프로시저문의 규칙은 다음과 같다.
- Procedure 생성하기: CREATE procedure()
- begin
- Raise문 : 프로시저가 실행되면 Raise문 내의 문장을 출력한다.
- insert into schema.table명 (컬럼)
- 실행할 쿼리문
- end
- Raise문 : 마찬가지로 프로시저가 끝나면 Raise문 내의 문장을 출력한다.
예를 들어 스키마 everly 내의 가상의 테이블인 naver_db 라는 테이블에 데이터를 insert한다고 하자. 넣을 데이터가 저장된 원본 경로는 spectrum 스키마 내의 naver_crawler 테이블이다.
여기서 넣고자 하는 컬럼은 brand, product_name, product_image, seller_name, sell_price, seller_url, create_date가 있다고 하자.
이를 코드화하면 다음과 같다.
CREATE OR REPLACE PROCEDURE everly.insert_naver_db()
LANGUAGE plpgsql
AS $$
begin
RAISE INFO 'everly.insert_naver_db() start';
INSERT INTO everly.naver_db (
brand
, product_name
, product_image
, seller_name
, sell_price
, seller_url
, create_date
)
select
brand
, product_name
, product_image
, seller_name
, sell_price
, seller_url
, create_date
, to_date(create_date, 'YYYY-MM-DD') as create_date
from spectrum.naver_crawler
where to_date(create_date, 'YYYY-MM-DD') = current_date
;
RAISE INFO 'everly.insert_naver_db() end';
end;
$$
;'Skillset > SQL' 카테고리의 다른 글
| 파이썬에서 SQL parameter 설정하기 (Python SQL formatter) (2) | 2022.09.20 |
|---|---|
| [Python, Redshift] 파이썬에 AWS Redshift 연결하여 사용하는 방법 (1) | 2022.09.19 |
| [MySQL] 문자열에서 0 제거하기 (REPLACE) (0) | 2022.08.12 |
| SQL로 깔끔한 문장 출력하기 (CONCAT 활용) (0) | 2022.08.10 |
| SQL 중첩된 CASE WHEN 구문 활용: 삼각형의 타입 구분하기 (0) | 2022.08.09 |
