表形式 SQL UDFs （UDTFs）¶

Snowflakeは、0、1つ、または複数の行で構成される一連の行を返す SQL UDFs をサポートします。各行には、1つ以上の列があります。このような UDFs は、 表形式 UDFs、 テーブル UDFs、または最も頻繁に UDTFs （ユーザー定義のテーブル関数）と呼ばれます。

UDTF は、クエリの FROM 句でアクセスできます。

このトピックの内容:

構文¶

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 UDTFsを含むすべての UDFsの一般的な構文の詳細については、 CREATE FUNCTION をご参照ください。

引数¶

name:

これは、識別子の要件で説明されているルールに従う有効なデータベースオブジェクト名である必要があります。

arguments:

これは、列名、リテラル、または単一の値に評価できる式などの式でなければなりません。通常、関数は1つの引数を取ります。これは列名です。たとえば、1つ以上の列名、または列名と1つ以上リテラル値など、1つ以上の値を渡すことができます。

定数を渡すことも、値をまったく渡さないことも可能です。ただし、ほとんどの場合、入力が毎回同じであれば、出力は毎回同じです。

RETURNS TABLE(...)

UDF がテーブルを返すように指定します。括弧内で、返されるテーブルに含める列の名前と型のペアを指定します（以下で説明のとおり）。

output_col_name:: 返されたテーブルに含める出力列の名前。少なくとも1つの出力列が必要です。
output_col_type:: 出力列のデータ型です。

sql_expression:

各行に1つ以上の列がある、ゼロ行以上のテーブルを返す有効な SQL 式またはステートメントにする必要があります。出力は、 RETURNS 句で指定された数およびデータ型と一致する必要があります。

使用上の注意¶

SQL UDTF の本体（別名「定義」）は SELECT 式でなければなりません。
sql_expression の前後の区切り文字は通常一重引用符ですが、区切り文字としてドル記号 $$ のペアを使用できます。終了区切り文字は、開始区切り文字と一致する必要があります。 sql_expression に一重引用符が含まれる場合に、ドル記号のペアは便利です。ドル記号のペアを使用した例は、以下の例のセクションに含まれています。

区切り文字が一重引用符であり、本文に一重引用符が含まれている場合は、バックスラッシュ文字 \ をエスケープ文字として使用して、本文内の一重引用符をエスケープできます。例は、下記の例セクションに含まれています。
UDTF で定義された列は、通常のテーブル列を使用できる場所であればどこにでも表示できます。
RETURNS 句で指定された戻り値の型は、表形式の結果にある列の名前と型を決定し、関数本体の SELECT ステートメントの対応する位置の式の型と一致する必要があります。
UDTF を呼び出すときは、 TABLE キーワードに続く括弧内に UDTF の名前と引数を含める必要があります。詳細については、 SQL UDTF の呼び出しをご参照ください。

注釈

表形式の関数（UDTFs）には、入力引数に500個、出力列に500個の制限があります。

SQL UDTF の呼び出し¶

クエリの FROM 句で UDTF を呼び出す場合は、 TABLE キーワードに続く括弧内に UDTF の名前と引数を指定します。

つまり、 UDTF を呼び出すときは、 TABLE キーワードに次のような形式を使用します。

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

Copy

サンプル SQL UDTFs¶

基本的な例¶

これは人為的に簡単にした 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 の別の基本的な例です。テーブルをクエリし、そのテーブルから2つの列を返します。

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