테이블 형식 SQL UDF(UDTF)¶

Snowflake는 0개, 1개 또는 여러 개의 행(각각 1개 이상의 열 포함)으로 구성된 행 세트를 반환하는 SQL UDF를 지원합니다. 이러한 UDF를 테이블 형식 UDF, 테이블 UDF 또는 가장 흔하게는 UDTF (사용자 정의 테이블 함수)라고 합니다.

쿼리의 FROM 절에서 UDTF에 액세스할 수 있습니다.

이 항목의 내용:

구문¶

CREATE OR REPLACE FUNCTION <name> ( [ <arguments> ] )
  RETURNS TABLE ( <output_col_name> <output_col_type> [, <output_col_name> <output_col_type> ... ] )
  AS '<sql_expression>'

Copy

SQL UDTF를 포함한 모든 UDF의 일반 구문에 대한 자세한 설명은 CREATE FUNCTION 을 참조하십시오.

인자¶

name:

이는 식별자 요구 사항 에 설명된 규칙을 따르는 유효한 데이터베이스 오브젝트 이름이어야 합니다.

arguments:

이는 식이어야 합니다(예: 열 이름이거나, 리터럴이거나, 단일 값으로 평가할 수 있는 식). 일반적으로 함수는 열 이름인 하나의 인자를 사용합니다. 사용자는 둘 이상의 값(예: 둘 이상의 열 이름 또는 열 이름 하나와 리터럴 값 하나 이상)을 전달할 수 있습니다.

상수를 전달하거나 값을 전혀 전달하지 않을 수 있습니다. 그러나 대부분의 경우, 입력이 매번 같으면 출력이 매번 같습니다.

RETURNS TABLE(...)

UDF가 테이블을 반환해야 함을 지정합니다. 괄호 안에 반환된 테이블에 포함할 열의 이름-유형 페어를 지정합니다(아래 설명에 따름).

output_col_name:: 반환된 테이블에 포함할 출력 열의 이름입니다. 하나 이상의 출력 열이 있어야 합니다.
output_col_type:: 출력 열의 데이터 타입입니다.

sql_expression:

각각 하나 이상의 열이 있는 0개 또는 그 이상의 행을 가진 테이블을 반환하는 유효한 SQL 식 또는 문입니다. 출력은 RETURNS 절에 지정된 수 및 데이터 타입과 일치해야 합니다.

사용법 노트¶

SQL UDTF의 본문(일명 “정의”)은 SELECT 식이어야 합니다.
sql_expression 주위의 구분 기호는 일반적으로 작은따옴표이지만, 한 쌍의 달러 기호 $$ 를 구분 기호로 사용할 수 있습니다. 닫는 구분 기호는 여는 구분 기호와 일치해야 합니다. sql_expression 에 작은따옴표가 포함된 경우, 한 쌍의 달러 기호가 편리합니다. 한 쌍의 달러 기호를 사용하는 예는 아래의 예 섹션에 포함되어 있습니다.

구분 기호가 작은따옴표이고 본문에 작은따옴표가 포함된 경우, 백슬래시 문자 \ 를 이스케이프 문자로 사용하여 본문에서 작은따옴표를 이스케이프할 수 있습니다. 아래의 예 섹션에 예가 포함되어 있습니다.
UDTF에 정의된 열은 일반 테이블 열을 사용할 수 있는 모든 위치에 나타날 수 있습니다.
RETURNS 절에 지정된 반환 형식은 테이블 형식 결과에 있는 열의 이름과 형식을 결정하며, 함수 본문에서 SELECT 문의 해당 위치에 있는 식의 형식과 일치해야 합니다.
UDTF를 호출할 때 TABLE 키워드 다음에 오는 괄호 안에 UDTF 이름과 인자를 포함해야 합니다. 자세한 내용은 SQL UDTF 호출하기 섹션을 참조하십시오.

참고

테이블 형식 함수(UDTF)의 입력 인자 500개, 출력 열 500개로 제한됩니다.

SQL UDTF 호출하기¶

쿼리의 FROM 절에서 UDTF를 호출할 때 TABLE 키워드 다음에 오는 괄호 안에 UDTF의 이름과 인수를 지정합니다.

즉, UDTF를 호출할 때 TABLE 키워드에 대해 다음과 같은 양식을 사용합니다.

SELECT ...
  FROM TABLE ( udtf_name (udtf_arguments) )

Copy

샘플 SQL UDTF¶

기본 예¶

이는 출력을 하드 코딩하는 인위적으로 간단한 UDTF의 예입니다. 이는 또한 $$ 를 구분 기호로 사용하는 방법을 보여줍니다.

CREATE FUNCTION t()
    RETURNS TABLE(msg VARCHAR)
    AS
    $$
        SELECT 'Hello'
        UNION
        SELECT 'World'
    $$;

Copy

SELECT msg 
    FROM TABLE(t())
    ORDER BY msg;
+-------+
| MSG   |
|-------|
| Hello |
| World |
+-------+

Copy

이 예는 앞의 예와 유사하지만 작은따옴표를 구분 기호로 사용하며, \ 이스케이프 문자를 사용하여 UDTF 본문에서 작은따옴표를 이스케이프합니다.

CREATE FUNCTION t()
    RETURNS TABLE(msg VARCHAR)
    AS
    '
        SELECT \'Hello\'
        UNION
        SELECT \'World\'
    ';

Copy

SELECT msg
    FROM TABLE(t())
    ORDER BY msg;
+-------+
| MSG   |
|-------|
| Hello |
| World |
+-------+

Copy

이는 UDTF의 또 다른 기본 예입니다. 테이블을 쿼리하고 해당 테이블에서 두 개의 열을 반환합니다.

create or replace table orders (
    product_id varchar, 
    quantity_sold numeric(11, 2)
    );

insert into orders (product_id, quantity_sold) values 
    ('compostable bags', 2000),
    ('re-usable cups',  1000);

Copy

create or replace function orders_for_product(PROD_ID varchar)
    returns table (Product_ID varchar, Quantity_Sold numeric(11, 2))
    as
    $$
        select product_ID, quantity_sold 
            from orders 
            where product_ID = PROD_ID
    $$
    ;

Copy

select product_id, quantity_sold
    from table(orders_for_product('compostable bags'))
    order by product_id;
+------------------+---------------+
| PRODUCT_ID       | QUANTITY_SOLD |
|------------------+---------------|
| compostable bags |       2000.00 |
+------------------+---------------+

Copy

이 동일한 기능은 뷰를 사용하여 구현할 수도 있습니다.

조인이 있는 예¶

지정된 사용자 ID에 대한 국가 정보(COUNTRY_CODE 및 COUNTRY_NAME)를 반환하는 SQL UDTF를 다음과 같이 만들고 사용하십시오.

create or replace table countries (country_code char(2), country_name varchar);
insert into countries (country_code, country_name) values 
    ('FR', 'FRANCE'),
    ('US', 'UNITED STATES'),
    ('SP', 'SPAIN');

create or replace table user_addresses (user_ID integer, country_code char(2));
insert into user_addresses (user_id, country_code) values 
    (100, 'SP'),
    (123, 'FR'),
    (123, 'US');

Copy

CREATE OR REPLACE FUNCTION get_countries_for_user ( id number )
  RETURNS TABLE (country_code char, country_name varchar)
  AS 'select distinct c.country_code, c.country_name
      from user_addresses a, countries c
      where a.user_id = id
      and c.country_code = a.country_code';

Copy

select *
    from table(get_countries_for_user(123)) cc
    where cc.country_code in ('US','FR','CA')
    order by country_code;
+--------------+---------------+
| COUNTRY_CODE | COUNTRY_NAME  |
|--------------+---------------|
| FR           | FRANCE        |
| US           | UNITED STATES |
+--------------+---------------+

Copy

지정된 연도에 대해 선호하는 색상을 반환하는 SQL UDTF를 다음과 같이 만드십시오.

create or replace table favorite_years as
    select 2016 year
    UNION ALL
    select 2017
    UNION ALL
    select 2018
    UNION ALL
    select 2019;

 create or replace table colors as
    select 2017 year, 'red' color, true favorite
    UNION ALL
    select 2017 year, 'orange' color, true favorite
    UNION ALL
    select 2017 year, 'green' color, false favorite
    UNION ALL
    select 2018 year, 'blue' color, true favorite
    UNION ALL
    select 2018 year, 'violet' color, true favorite
    UNION ALL
    select 2018 year, 'brown' color, false favorite;

create or replace table fashion as
    select 2017 year, 'red' fashion_color
    UNION ALL
    select 2018 year, 'black' fashion_color
    UNION ALL
    select 2019 year, 'orange' fashion_color;

Copy

create or replace function favorite_colors(the_year int)
    returns table(color string) as
    'select color from colors where year=the_year and favorite=true';

Copy

다음과 같이 쿼리에서 UDTF를 사용하십시오.

select color
    from table(favorite_colors(2017))
    order by color;
+--------+
| COLOR  |
|--------|
| orange |
| red    |
+--------+

Copy

다른 테이블과의 조인에서 UDTF를 사용하십시오. 테이블의 조인 열이 함수에 인자로 전달됩니다.

select * 
    from favorite_years y join table(favorite_colors(y.year)) c
    order by year, color;
+------+--------+
| YEAR | COLOR  |
|------+--------|
| 2017 | orange |
| 2017 | red    |
| 2018 | blue   |
| 2018 | violet |
+------+--------+

Copy

추가 조건자에 대해 다음과 같이 ON 대신 WHERE 절을 사용하십시오.

select * 
    from fashion f join table(favorite_colors(f.year)) fav
    where fav.color = f.fashion_color ;
+------+---------------+-------+
| YEAR | FASHION_COLOR | COLOR |
|------+---------------+-------|
| 2017 | red           | red   |
+------+---------------+-------+

Copy

조인 식에서 상수와 함께 UDTF를 사용하십시오. 추가 조인 조건에는 ON 대신 WHERE 절을 사용해야 합니다.

select fav.color as favorite_2017, f.*
    from fashion f JOIN table(favorite_colors(2017)) fav
    where fav.color = f.fashion_color
    order by year;
+---------------+------+---------------+
| FAVORITE_2017 | YEAR | FASHION_COLOR |
|---------------+------+---------------|
| red           | 2017 | red           |
| orange        | 2019 | orange        |
+---------------+------+---------------+

Copy