스칼라 SQL UDFs¶

이 항목에서는 SQL UDF(사용자 정의 함수)와 관련된 개념 및 사용 세부 정보를 다룹니다.

이 항목의 내용:

일반 사용¶

SQL UDF는 임의의 SQL 식을 평가하고 식의 결과를 반환합니다.

함수 정의는 스칼라(즉, 단일) 값을 반환하거나, 테이블 함수로 정의된 경우 행 세트를 반환하는 SQL 식일 수 있습니다. 예를 들어, 다음은 원의 면적을 계산하는 스칼라 UDF의 기본 예입니다.

CREATE FUNCTION area_of_circle(radius FLOAT)
  RETURNS FLOAT
  AS
  $$
    pi() * radius * radius
  $$
  ;

Copy

SELECT area_of_circle(1.0);

Copy

출력:

SELECT area_of_circle(1.0);
+---------------------+
| AREA_OF_CIRCLE(1.0) |
|---------------------|
|         3.141592654 |
+---------------------+

Copy

식은 쿼리 식(SELECT 식)일 수 있습니다. 예:

CREATE FUNCTION profit()
  RETURNS NUMERIC(11, 2)
  AS
  $$
    SELECT SUM((retail_price - wholesale_price) * number_sold)
        FROM purchases
  $$
  ;

Copy

SQL UDF에서 쿼리 식을 사용할 때는 쿼리 식을 종료하기 위해 UDF 본문에 세미콜론을 포함하지 마십시오.

하나의 쿼리 식만 포함할 수 있습니다. 식에는 UNION [ALL]이 포함될 수 있습니다.

참고

UDF의 본문은 완전한 SELECT 문을 포함할 수 있지만, DDL 문이나 SELECT 이외의 DML 문은 포함할 수 없습니다.

참고

스칼라 함수(UDF)의 입력 인자는 500개로 제한됩니다.

메모이제이션 가능 UDF¶

스칼라 SQL UDF는 메모이제이션 가능할 수 있습니다. 메모이제이션 가능 함수는 스칼라 SQL UDF를 호출한 결과를 캐시했다가 나중에 출력이 필요할 때 캐시된 결과를 반환합니다. 메모이제이션 가능 함수를 사용하면 행 액세스 정책 또는 마스킹 정책 내에서 참조되는 매핑 테이블 의 여러 열 조회와 같은 복잡한 쿼리의 성능이 향상된다는 이점이 있습니다.

정책 소유자(예: 행 액세스 정책에 대한 OWNERSHIP 권한이 있는 역할)는 메모이제이션 가능 함수이 있는 매핑 테이블이 있는 하위 쿼리를 대체하도록 정책 조건을 업데이트할 수 있습니다. 사용자가 나중에 쿼리에서 정책으로 보호된 열을 참조할 때 메모이제이션 가능 함수에서 캐시된 결과를 필요에 따라 사용할 수 있습니다.

메모이제이션 가능 함수 만들기¶

MEMOIZABLE 키워드를 지정하여 CREATE FUNCTION 문에서 메모이제이션 가능하도록 스칼라 SQL UDF를 정의할 수 있습니다. 인자의 유무와 관계없이 메모이제이션 가능 함수를 만들 수 있습니다. 인자를 사용하면 SQL UDF를 더 자유롭게 정의할 수 있습니다. 메모이제이션 가능 함수를 호출하는 정책을 작성하면 정책을 정의하는 방법 측면에서 유연성이 증가합니다.

인자를 지정하는 경우 인자는 다음 데이터 타입 중 하나를 갖는 상수 값이어야 합니다.

VARCHAR 및 기타 문자열 데이터 타입.
NUMBER 및 기타 숫자형 데이터 타입.
TIMESTAMP 및 기타 날짜 데이터 타입.
BOOLEAN.

반정형 데이터 타입 및 테이블 열과 같은 상수가 아닌 값과 해당 데이터 타입은 지원되지 않습니다.

메모이제이션 가능 함수를 작성할 때의 지침은 다음과 같습니다.

BOOLEAN 또는 다른 스칼라 데이터 타입을 result_data_type 으로 지정합니다.

캐시 크기에 제한이 있으므로 ARRAY를 result_data_type 으로 지정할 때는 주의하십시오.
OBJECT 및 VARIANT와 같은 다른 데이터 타입은 지정하지 마십시오.
어떤 방식으로든 다른 메모이제이션 가능 함수를 참조하지 마십시오.

메모이제이션 가능 함수 호출하기¶

메모이제이션 가능 함수는 SELECT 문에서 호출하거나 정책 정의에 포함될 수 있으며, 이처럼 포함한 다음에 정책 조건에 따라 메모이제이션 가능 함수를 호출하게 됩니다.

메모이제이션 가능 함수를 호출할 때는 다음 사항에 유의하십시오.

ARRAY 데이터 타입을 반환하거나 스칼라가 아닌 값을 지정하는 SQL UDF의 경우 메모이제이션 가능 함수를 ARRAY_CONTAINS 함수의 인자로 사용합니다.
캐시 크기 제한:

각 메모이제이션 가능 함수는 현재 Snowflake 세션에 대해 10KB로 제한됩니다.

메모이제이션 가능 함수가 결과 세트 캐시에 대한 이 제한을 초과할 경우 Snowflake는 메모이제이션 가능 함수를 호출한 결과를 캐시하지 않습니다. 대신 UDF은 함수의 작성 방식에 따라 일반 스칼라 UDF로 작동합니다.
캐시 사용량:

메모이제이션 가능 함수에는 쿼리 환경과 컨텍스트가 변경되지 않을 때 다른 SQL 문에 대해 재사용 가능한 결과 캐시가 있습니다. 일반적으로, 이는 결과 캐시가 다음과 같은 경우에 다른 SQL 문에 적용됨을 의미합니다.
- 쿼리에서 참조되는 오브젝트와 열에 대한 액세스 제어 권한은 동일하게 유지됩니다.
- 쿼리에서 참조된 오브젝트는 수정되지 않습니다(예: DML 문을 통해).
Account Usage QUERY_HISTORY 뷰의 CHILD_QUERIES_WAIT_TIME 열은 메모이제이션 가능 함수를 호출할 때 캐시된 조회를 완료하는 데 걸리는 시간(밀리초)을 기록합니다.
다음과 같은 경우에는 메모이제이션 가능 함수가 캐시된 결과를 재사용하지 않습니다.
- 함수가 테이블 또는 기타 오브젝트를 참조하고 참조된 테이블에 대한 업데이트가 있는 경우.
- 테이블에 대한 액세스 제어에 변화가 있는 경우.
- 함수가 비결정적 함수를 호출하는 경우.
- 함수가 외부 함수 또는 SQL UDF가 아닌 UDF를 호출하는 경우.

예¶

기본 SQL 스칼라 UDF 예¶

이 예는 수학 상수 pi의 하드 코딩된 근삿값을 반환합니다.

CREATE FUNCTION pi_udf()
  RETURNS FLOAT
  AS '3.141592654::FLOAT'
  ;

Copy

SELECT pi_udf();   

Copy

출력:

SELECT pi_udf();
+-------------+
|    PI_UDF() |
|-------------|
| 3.141592654 |
+-------------+

Copy

일반 SQL 예¶

SELECT 문을 포함한 쿼리 식¶

다음과 같이 사용할 테이블과 데이터를 만드십시오.

CREATE TABLE purchases (number_sold INTEGER, wholesale_price NUMBER(7,2), retail_price NUMBER(7,2));
INSERT INTO purchases (number_sold, wholesale_price, retail_price) VALUES 
   (3,  10.00,  20.00),
   (5, 100.00, 200.00)
   ;

Copy

UDF를 만듭니다.

CREATE FUNCTION profit()
  RETURNS NUMERIC(11, 2)
  AS
  $$
    SELECT SUM((retail_price - wholesale_price) * number_sold)
        FROM purchases
  $$
  ;

Copy

다음과 같이 쿼리에서 UDF를 호출하십시오.

SELECT profit();

Copy

출력:

SELECT profit();
+----------+
| PROFIT() |
|----------|
|   530.00 |
+----------+

Copy

WITH 절의 UDF¶

CREATE TABLE circles (diameter FLOAT);

INSERT INTO circles (diameter) VALUES
    (2.0),
    (4.0);

CREATE FUNCTION diameter_to_radius(f FLOAT) 
  RETURNS FLOAT
  AS 
  $$ f / 2 $$
  ;

Copy

WITH
    radii AS (SELECT diameter_to_radius(diameter) AS radius FROM circles)
  SELECT radius FROM radii
    ORDER BY radius
  ;

Copy

출력:

+--------+
| RADIUS |
|--------|
|      1 |
|      2 |
+--------+

Copy

JOIN 작업¶

이 예에서는 JOIN 작업을 포함하는 더 복잡한 쿼리를 사용합니다.

다음과 같이 사용할 테이블과 데이터를 만드십시오.

CREATE TABLE orders (product_ID varchar, quantity integer, price numeric(11, 2), buyer_info varchar);
CREATE TABLE inventory (product_ID varchar, quantity integer, price numeric(11, 2), vendor_info varchar);
INSERT INTO inventory (product_ID, quantity, price, vendor_info) VALUES 
  ('X24 Bicycle', 4, 1000.00, 'HelloVelo'),
  ('GreenStar Helmet', 8, 50.00, 'MellowVelo'),
  ('SoundFX', 5, 20.00, 'Annoying FX Corporation');
INSERT INTO orders (product_id, quantity, price, buyer_info) VALUES 
  ('X24 Bicycle', 1, 1500.00, 'Jennifer Juniper'),
  ('GreenStar Helmet', 1, 75.00, 'Donovan Liege'),
  ('GreenStar Helmet', 1, 75.00, 'Montgomery Python');

Copy

UDF를 만듭니다.

CREATE FUNCTION store_profit()
  RETURNS NUMERIC(11, 2)
  AS
  $$
  SELECT SUM( (o.price - i.price) * o.quantity) 
    FROM orders AS o, inventory AS i 
    WHERE o.product_id = i.product_id
  $$
  ;

Copy

다음과 같이 쿼리에서 UDF를 호출하십시오.

SELECT store_profit();

Copy

출력:

SELECT store_profit();
+----------------+
| STORE_PROFIT() |
|----------------|
|         550.00 |
+----------------+

Copy

CREATE FUNCTION 항목에는 추가 예가 포함되어 있습니다.

다양한 절에서 UDFs 사용¶

스칼라 UDF는 스칼라 식을 사용할 수 있는 모든 곳에서 사용할 수 있습니다. 예:

-- ----- These examples show a UDF called from different clauses ----- --

select MyFunc(column1) from table1;

select * from table1 where column2 > MyFunc(column1);

Copy

UDF에서 SQL 변수 사용¶

이 예는 SQL 변수를 설정하고 UDF 내에서 해당 변수를 사용하는 방법을 보여줍니다.

SET id_threshold = (SELECT COUNT(*)/2 FROM table1);

Copy

CREATE OR REPLACE FUNCTION my_filter_function()
RETURNS TABLE (id int)
AS
$$
SELECT id FROM table1 WHERE id > $id_threshold
$$
;

Copy

메모이제이션 가능 함수¶

예는 다음을 참조하십시오.

행 액세스 정책 에서 인자가 없는 메모이제이션 가능 함수.
마스킹 정책 의 인자로 메모이제이션 가능한 함수.