저장 프로시저 개요

저장 프로시저 개요 (Stored Procedures Overview)

저장 프로시저(stored procedure)를 작성하면 절차적 코드로 시스템을 확장할 수 있어요. 프로시저 안에서 분기(branching), 반복(looping) 같은 프로그래밍 구문을 사용할 수 있고, 다른 코드에서 호출해 여러 번 재사용할 수도 있죠.

출처: Snowflake SQL Reference

본문

저장 프로시저를 사용하면 다음을 할 수 있어요.

• 자주 수행해야 하는 여러 데이터베이스 작업이 필요한 작업을 자동화.
• 데이터베이스 작업을 동적으로 생성하고 실행.
• 프로시저를 실행하는 역할의 권한이 아니라, 프로시저를 소유한 역할의 권한으로 코드를 실행. 이를 통해 프로시저 소유자는 지정된 작업을 수행할 권한을 원래는 그렇게 할 수 없던 사용자에게 위임할 수 있어요. 다만 이러한 owner’s rights 저장 프로시저에는 제약이 있습니다.

예를 들어, 지정된 날짜보다 오래된 데이터를 삭제해 데이터베이스를 정리하고 싶다고 해볼게요. 여러 테이블에서 각각 데이터를 삭제하는 delete 작업을 코드에서 반복 수행해야 하는 상황이라면, 그 모든 문장을 하나의 저장 프로시저에 넣고 기준 날짜(컷오프 날짜)를 지정하는 파라미터를 전달할 수 있어요. 프로시저를 배포한 뒤에는 그것을 호출해 데이터베이스를 정리하면 됩니다. 데이터베이스가 변하면 프로시저를 갱신해 추가 테이블도 정리하게 만들 수 있고, 여러 사용자가 새 정리 명령을 쓰는 경우 매번 테이블 이름을 기억해 개별적으로 정리하는 대신 프로시저 하나만 호출하면 돼요.

저장 프로시저는 UDF와 비슷하지만 중요한 차이점이 있습니다. 저장 프로시저를 쓸지 사용자 정의 함수를 쓸지 결정하는 방법 문서를 참고하세요.

프로시저는 Snowflake를 확장하는 여러 방법 중 하나일 뿐이에요. 다른 방법은 사용자 정의 함수 개요, 외부 함수 작성, Snowpark API 문서를 보면 됩니다.

지원되는 언어와 도구

여러 도구 중 어떤 것을 쓰느냐에 따라 저장 프로시저(및 기타 Snowflake 엔티티)를 생성·관리할 수 있어요.

프로시저의 로직(핸들러)은 지원되는 언어 중 하나로 작성해요. 핸들러가 준비되면 CREATE PROCEDURE 명령으로 프로시저를 생성하고, CALL 문으로 호출하면 됩니다.

저장 프로시저에서 단일 값이나(핸들러 언어에서 지원되는 경우) 테이블 형식 데이터를 반환할 수 있어요. 지원되는 반환 유형에 대한 자세한 내용은 CREATE PROCEDURE 문서를 참고하세요.

저장 프로시저는 프로시저에 국한되고 자급자족적인 임시 테이블도 사용할 수 있어요. 이 테이블은 프로시저 실행 동안에만 존재하며, 프로시저의 스코프가 끝나면 자동으로 제거됩니다.

언어를 고를 때는 지원되는 핸들러 위치도 고려하세요. 모든 언어가 스테이지에 있는 핸들러 참조를 지원하지는 않아요(어떤 언어는 핸들러 코드가 인라인이어야 합니다).

임시 프로시저 (Temporary procedures)

사용 후 폐기되는 프로시저를 만들 수 있어요. 여러 세션이나 여러 사용자에게 지속적으로 프로시저를 제공할 필요가 없을 때 유용합니다. 또한 다음 방식으로 프로시저를 만들면 CREATE PROCEDURE 권한이 필요하지 않아 더 널리 사용할 수 있어요.

• 현재 세션 동안만 유지되다가 드롭되는 임시 저장 프로시저 생성. CREATE PROCEDURE의 TEMP 또는 TEMPORARY 파라미터, 또는 Java·Python·Scala용 Snowpark API로 지원.
• 즉시 호출하고 바로 드롭되는 익명 프로시저 생성. CALL (익명 프로시저 포함) 구문으로.

저장 프로시저 예제

다음 예제는 run이라는 Python 핸들러를 가진 저장 프로시저 myproc을 만듭니다.

CREATE OR REPLACE PROCEDURE myproc(from_table STRING, to_table STRING, count INT)
  RETURNS STRING
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.12'
  PACKAGES = ('snowflake-snowpark-python')
  HANDLER = 'run'
as
$$
def run(session, from_table, to_table, count):
  session.table(from_table).limit(count).write.save_as_table(to_table)
  return "SUCCESS"
$$;

다음 예제는 저장 프로시저 myproc을 호출해요.

CALL myproc('table_a', 'table_b', 5);

지침과 제약 사항

• 팁: 저장 프로시저 작성 요령은 저장 프로시저 다루기 문서를 참고.
• Snowflake 제약: Snowflake 환경 내의 제약 안에서 개발해 안정성을 확보할 수 있어요.
• 이름 지정: 다른 프로시저와 충돌을 피하도록 이름을 지으세요.
• 인수: 저장 프로시저의 인수를 지정하고 어떤 인수가 선택적인지 표시하세요.
• 데이터 타입 매핑: 각 핸들러 언어마다 해당 언어의 데이터 타입과 인수·반환값에 쓰이는 SQL 타입 사이에 별도의 매핑 집합이 있어요.

핸들러 작성

• 핸들러 언어: 핸들러 작성에 대한 언어별 내용은 지원되는 언어와 도구 문서를 참고.
• 외부 네트워크 접근: external network access로 Snowflake 외부의 특정 네트워크 위치에 안전하게 접근할 수 있어요.
• 로깅과 트레이싱: 로그 메시지와 트레이스 이벤트를 캡처해 나중에 조회 가능한 데이터베이스에 저장하며 코드 활동을 기록할 수 있어요.

보안

저장 프로시저를 caller’s rights로 실행할지 owner’s rights로 실행할지에 따라 접근할 수 있는 정보와 수행할 수 있는 작업이 달라져요. 저장 프로시저는 사용자 정의 함수(UDF)와 일부 보안 고려 사항을 공유합니다. 민감한 정보를 접근해서는 안 되는 사용자로부터 감추려면 보안 UDF 및 저장 프로시저로 민감 정보 보호 문서를 참고하세요.

핸들러 코드 배포

프로시저를 만들 때 핸들러(프로시저의 로직을 구현하는 것)를 CREATE PROCEDURE 문에 인라인 코드로 지정하거나, 스테이지에 복사된 컴파일된 코드처럼 문 외부의 코드로 지정할 수 있어요.

더 알아보기 (Learn more)

• 사용자 정의 함수 개요
• Snowflake Scripting
• CREATE PROCEDURE