JavaScript UDTFs (ユーザー定義のテーブル関数)

SQL UDTFs と同様に、 JavaScript UDTF は行のセットを返し、クエリの FROM 句でアクセスできます。

このトピックの内容:

構文

JavaScript UDTF は、標準の SQL UDTF と同じ構文を使用して定義されていますが、 LANGUAGE キーワードが追加されています。さらに、関数定義の SQL ブロックの代わりに、 JavaScript コードが渡されます。

CREATE OR REPLACE FUNCTION <name> ( [ <arguments> ] )
  RETURNS TABLE ( <output_columns> )
  LANGUAGE JAVASCRIPT
  AS '<javascript_code>';

次のセクションでは、 UDTF の本文(別名「定義」)を構成する JavaScriptコード を記述するための詳細を説明します。

JavaScript UDTFsを含むすべての UDFsの一般的な構文の詳細については、 CREATE FUNCTION をご参照ください。

使用上の注意

  • PARTITION BY 句で JavaScript UDTF を使用する場合、 PARTITION BY 句には列参照のみを含めることができ、一般式は含めることができません。たとえば、次のコマンドは 許可されていません

    SELECT * FROM udtf_table, TABLE(my_func(col1) OVER (PARTITION BY col2 * 2));

書き込み JavaScript UDTFs

コード要件

JavaScript コードは、 UDTF を有効にするために次の要件を満たしている必要があります。

  • コードは、単一のリテラル JavaScript オブジェクトを定義する必要があります。

  • 定義されたオブジェクトには、 processRow というコールバック関数が含まれている必要があります。詳細については、次のセクションをご参照ください。

重要

JavaScript コードがこれらの要件を満たさない場合でも、 UDTF は作成されます。ただし、クエリで呼び出されると失敗します。

オブジェクトコールバック関数

Snowflakeは、 JavaScript コードを通じて、クエリの実行中にさまざまなコールバック関数を呼び出すことで UDTF と対話します。次のスケルトンは、使用可能なすべてのコールバックとそれらの予想される署名の概要を示しています。

{
   processRow: function (row, rowWriter, context) {/*...*/},
   finalize: function (rowWriter, context) {/*...*/},
   initialize: function (argumentInfo, context) {/*...*/},
}

processRow コールバックのみが必要であり、残りはオプションです。

processRow

このコールバック関数は、入力関係の行ごとに1回呼び出されます。 UDTF への引数は row オブジェクトで渡されます。 UDTF の作成に使用される CREATE FUNCTION DDL で定義された各引数には、すべて大文字の同じ名前の row オブジェクトのプロパティがあります。このプロパティの値は、 JavaScript 値に変換された現在の行の引数の値です。

rowWriter 引数は、ユーザー提供のコードが出力行を生成するために使用されます。単一の関数 writeRow が、 rowWriter オブジェクトで定義されています。 writeRow 関数は、1つの引数 Rowオブジェクト を取ります。これは、 JavaScript オブジェクトとして表される出力テーブル内の単一行です。 CREATE FUNCTION DDL の RETURNS 句で定義された各列に対して、対応するプロパティを行オブジェクトに定義できます。行オブジェクトのそのプロパティの値は、出力関係の対応する列の値になります。行オブジェクトに対応するプロパティのない出力列は、結果テーブルで値 NULL になります。

finalize

このコールバック関数は、すべての行が processRow に渡された後、パーティションごとに1回呼び出されます。

processRow に渡されるのと同じ行ライターを使用して、 processRow に集約された可能性のある状態を出力するために使用できます。

initialize

このコールバック関数は、 processRow の呼び出しの前にパーティションごとに1回呼び出されます。このメソッドでは行を生成できません。

initialize は、結果の計算中に必要な状態を設定するために使用できます。さらに、 initialize には、引数に関するメタデータが argumentInfo オブジェクトのユーザー定義関数に渡されます。 argumentInfo には、 DDL で定義された各入力列のプロパティとその列に関するメタデータがあります。各エントリは、次の値を持つオブジェクトです。

  • type :文字列この列の型。

  • isConst :ブール値trueの場合、この列の値は一定(つまり、すべての行で同じ)です。

  • constValueisConst (上記で定義の通り)がtrueの場合、このエントリには列の定数値が含まれます。それ以外の場合、このフィールドは undefined です。

コールバック関数の一般的な使用上の注意

  • 3つのコールバック関数はすべて context オブジェクトを取ります。これは将来の使用のために予約されており、現在は空です。

    ご用心

    context オブジェクトを変更すると、未定義の動作が生じる可能性があります。

  • 必要に応じて、 UDTF で使用するオブジェクトに追加の関数とプロパティを定義できます。

  • コールバック関数の引数は定位置であり、任意の名前を付けることができます。ただし、このトピックの目的のために、残りの説明と例では上記の名前を使用しています。

パーティション

JavaScript UDTFs は、パーティションを使用して呼び出すことができます。例:

SELECT * FROM tab1, TABLE(js_udtf(tab1.c1, tab1.c2) OVER (PARTITION BY <expr> ORDER BY <expr>));

パーティションは、 PARTITION BY 式が同じ値に評価されるすべての行で構成されます。行は、 ORDER BY 式で定義された順序で processRow に渡されます。

この説明では、 JavaScript UDTF の インスタンス は、Snowflakeで関数を表すために使用される JavaScript オブジェクトの1つのインスタンスとして定義されます。

JavaScript UDTF を呼び出すときのパフォーマンスを向上させるために、複数の UDTF インスタンスを並行して実行できます。これを機能するためには、入力の行を関数の異なるインスタンス間で分割する必要があります。 JavaScript UDTF を呼び出すときにパーティションが指定されます。

  • パーティション内のすべての行は、 processRow 関数を介して同じ UDTF インスタンスに渡されます。

  • initialize および finalize はそれぞれパーティションごとに1回呼び出されます。

パーティションと UDTF インスタンスの間には1対1の関係はありません。各パーティションは1つの UDTF インスタンスのみで処理されますが、逆は当てはまりません。1つの UDTF インスタンスで複数のパーティションを処理できます。そのため、例えばあるパーティションの処理から別のパーティションの処理への累積値の「持ち越し」を避けるために、パーティションを具体的に設定および破棄するために initialize および finalize を使用することが重要です。

結果列

このセクションでは、 JavaScript UDTFによって返される列について説明します。

  • CREATE FUNCTION DDL の RETURNS 句で定義されたすべての列は、出力リレーションで返されます。

  • UDTF に渡されたすべての列も返されます。

processRow コールバックで生成される行と finalize で生成される行には区別があります。

  • 行が processRow で生成されると、Snowflakeはそれを入力、つまり row 引数として関数に渡された入力に関連付けることができます。指定された processRow が複数の行を生成する場合、入力属性は各出力行に複製されます。

    processRow で生成された行の場合、すべての入力列が出力関係で複製されます。

  • finalize コールバックでは、相関する「現在の行」がないため、Snowflakeはそれを単一の行に関連付けることができません。

    finalize コールバックで生成された行の場合、 PARTITION BY 句で使用される列のみが複製されます(これらは現在のパーティションの行で同じであるため)。他のすべての属性は NULL です。 PARTITION BY 句が指定されていない場合、すべての入力属性は NULL です。

クエリで JavaScript UDTFs を呼び出す

パーティション分割なし

これは、 UDTFを呼び出す方法のシンプルな例です。この例では、リテラル値を渡します。UDTF は、渡された順序と逆の順序でパラメーターを返すだけです。この例では、パーティション分割を使用しません。

SELECT * FROM TABLE(js_udtf(10.0::FLOAT, 20.0::FLOAT));
+----+----+
|  Y |  X |
|----+----|
| 20 | 10 |
+----+----+

この例では、 UDTF を呼び出し、別のテーブルから値を渡します。この例では、 js_udtf という名前の UDTF が、 tab1 という名前のテーブル内の行ごとに1回呼び出されます。関数が呼び出されるたびに、現在の行の列 c1 および c2 から値が渡されます。上記のように、 UDTF は PARTITION BY 句なしで呼び出されます。

SELECT * FROM tab1, TABLE(js_udtf(tab1.c1, tab1.c2)) ;

パーティション分割が使用されない場合、Snowflake実行エンジンは、関数を処理するウェアハウスのサイズや入力関係のカーディナリティなどの複数の要因に従って入力自体をパーティション化します。このモードで実行する場合、ユーザーコードはパーティションについて何も仮定しません。これは、関数が出力を生成するために行を分離して見るだけで、行間で状態が集約されない場合に最も役立ちます。

明示的なパーティション分割

JavaScript UDTFs は、パーティションを使用して呼び出すこともできます。例:

SELECT * FROM tab1, TABLE(js_udtf(tab1.c1, tab1.c2) OVER (PARTITION BY tab1.c3 ORDER BY tab1.c1));

空の OVER 句を使用した明示的なパーティション分割

SELECT * FROM tab1, TABLE(js_udtf(tab1.c1, tab1.c2) OVER ());

空の OVER 句は、すべての行が同じパーティションに属していることを意味します(つまり、入力関係全体が1つのパーティション)。

注釈

空の OVER 句でJavaScript JavaScriptUDTF を呼び出すときは注意が必要です。これにより、Snowflakeは関数の1つのインスタンスの作成に制限され、Snowflakeは計算を並列化できません。

サンプル JavaScript UDTFs

このセクションには、いくつかのサンプル JavaScript UDTFsが含まれています。

基本的な Hello World の例

次の JavaScript UDTF はパラメーターを取らず、常に同じ値を返します。主に説明のために提供されています。

CREATE OR REPLACE FUNCTION HelloWorld0()
    RETURNS TABLE (OUTPUT_COL VARCHAR)
    LANGUAGE JAVASCRIPT
    AS '{
        processRow: function f(row, rowWriter, context){
           rowWriter.writeRow({OUTPUT_COL: "Hello"});
           rowWriter.writeRow({OUTPUT_COL: "World"});
           }
        }';

SELECT output_col FROM TABLE(HelloWorld0());

出力:

+------------+
| OUTPUT_COL |
+============+
| Hello      |
+------------+
| World      |
+------------+

次の JavaScript UDTF も説明用ですが、入力パラメーターを使用しています。 JavaScript では大文字と小文字が区別されますが、 SQL では識別子が大文字になるため、 JavaScript コードが SQL パラメーター名を参照する場合、 JavaScript コードでは大文字を使用する必要があります。

また、関数パラメーターは、 get_params() 関数の row という名前のパラメーターを通じてアクセスされます。

CREATE OR REPLACE FUNCTION HelloHuman(First_Name VARCHAR, Last_Name VARCHAR)
    RETURNS TABLE (V VARCHAR)
    LANGUAGE JAVASCRIPT
    AS '{
        processRow: function get_params(row, rowWriter, context){
           rowWriter.writeRow({V: "Hello"});
           rowWriter.writeRow({V: row.FIRST_NAME});  // Note the capitalization and the use of "row."!
           rowWriter.writeRow({V: row.LAST_NAME});   // Note the capitalization and the use of "row."!
           }
        }';

SELECT V AS Greeting FROM TABLE(HelloHuman('James', 'Kirk'));

出力:

+------------+
|  GREETING  |
+============+
| Hello      |
+------------+
| James      |
+------------+
| Kirk       |
+------------+

コールバック関数を示す基本的な例

次の JavaScript UDTF は、すべての API コールバック関数とさまざまな出力列を示しています。単にすべての行をそのまま返し、各パーティションで見られる文字数のカウントを提供します。また、 THIS 参照を使用してパーティション全体で状態を共有する方法も示します。この例では、 initialize コールバックを使用してカウンターをゼロに初期化しています。これは、特定の関数インスタンスを使用して複数のパーティションを処理するために必要です。

-- set up for the sample
CREATE TABLE parts (p FLOAT, s STRING);

INSERT INTO parts VALUES (1, 'michael'), (1, 'kelly'), (1, 'brian');
INSERT INTO parts VALUES (2, 'clara'), (2, 'maggie'), (2, 'reagan');

-- creation of the UDTF
CREATE OR REPLACE FUNCTION "CHAR_SUM"(INS STRING)
    RETURNS TABLE (NUM FLOAT)
    LANGUAGE JAVASCRIPT
    AS '{
    processRow: function (row, rowWriter, context) {
      this.ccount = this.ccount + 1;
      this.csum = this.csum + row.INS.length;
      rowWriter.writeRow({NUM: row.INS.length});
    },
    finalize: function (rowWriter, context) {
     rowWriter.writeRow({NUM: this.csum});
    },
    initialize: function(argumentInfo, context) {
     this.ccount = 0;
     this.csum = 0;
    }}';

次のクエリは、パーティション分割なしで parts テーブルの CHAR_SUM UDTF を呼び出すことを示しています。

SELECT * FROM parts, TABLE(char_sum(s));

出力:

+--------+---------+-----+
| P      | S       | NUM |
+--------+---------+-----+
| 1      | michael | 7   |
| 1      | kelly   | 5   |
| 1      | brian   | 5   |
| 2      | clara   | 5   |
| 2      | maggie  | 6   |
| 2      | reagan  | 6   |
| [NULL] | [NULL]  | 34  |
+--------+---------+-----+

パーティションが指定されていない場合、Snowflakeは自動的にパーティションを定義します。この例では、行数が少ないため、作成されるパーティションは1つだけです( finalize の呼び出しが1つだけ実行される)。最終行の入力列には NULL 値が含まれています。

同じクエリですが、明示的なパーティション分割があります。

SELECT * FROM parts, TABLE(char_sum(s) OVER (PARTITION BY p));

出力:

+--------+---------+-----+
| P      | S       | NUM |
+--------+---------+-----+
| 1      | michael | 7   |
| 1      | kelly   | 5   |
| 1      | brian   | 5   |
| 1      | [NULL]  | 17  |
| 2      | clara   | 5   |
| 2      | maggie  | 6   |
| 2      | reagan  | 6   |
| 2      | [NULL]  | 17  |
+--------+---------+-----+

この例では、 p 列でパーティションを作成し、2つのパーティションを作成します。パーティションごとに、単一の行が finalize コールバックで返され、合計2行が生成され、 s 列の NULL 値で識別されます。 p は PARTITION BY 列であるため、 finalize で作成された行には、現在のパーティションを定義する p の値が含まれます。

入力としてテーブル値とその他の UDTFs を使用した拡張例

この基本的な UDTF は、 IP アドレスの「範囲」を IP アドレスの完全なリストに変換します。入力は、 IP アドレスの最初の3セグメント(例: '192.168.1')と、最後のセグメントの生成に使用される範囲の開始および終了(例: 42 および 45)で構成されます。

CREATE OR REPLACE FUNCTION range_to_values(PREFIX VARCHAR, RANGE_START FLOAT, RANGE_END FLOAT)
    RETURNS TABLE (IP_ADDRESS VARCHAR)
    LANGUAGE JAVASCRIPT
    AS $$
      {
        processRow: function f(row, rowWriter, context)  {
          var suffix = row.RANGE_START;
          while (suffix <= row.RANGE_END)  {
            rowWriter.writeRow( {IP_ADDRESS: row.PREFIX + "." + suffix} );
            suffix = suffix + 1;
            }
          }
      }
      $$;

SELECT * FROM TABLE(range_to_values('192.168.1', 42::FLOAT, 45::FLOAT));

出力:

+--------------+
| IP_ADDRESS   |
+==============+
| 192.168.1.42 |
+--------------+
| 192.168.1.43 |
+--------------+
| 192.168.1.44 |
+--------------+
| 192.168.1.45 |
+--------------+

前の例に基づいて、複数の範囲の個々の IP アドレスを計算できます。次のステートメントは、個々の IP アドレスに展開するために使用できる範囲のテーブルを作成します。次に、クエリはテーブルの行を range_to_values() UDTF に入力して、個々の IP アドレスを返します。

CREATE TABLE ip_address_ranges(prefix VARCHAR, range_start INTEGER, range_end INTEGER);
INSERT INTO ip_address_ranges (prefix, range_start, range_end) VALUES
    ('192.168.1', 42, 44),
    ('192.168.2', 10, 12),
    ('192.168.2', 40, 40)
    ;

SELECT rtv.ip_address
  FROM ip_address_ranges AS r, TABLE(range_to_values(r.prefix, r.range_start::FLOAT, r.range_end::FLOAT)) AS rtv;

出力:

+--------------+
| IP_ADDRESS   |
+==============+
| 192.168.1.42 |
+--------------+
| 192.168.1.43 |
+--------------+
| 192.168.1.44 |
+--------------+
| 192.168.2.10 |
+--------------+
| 192.168.2.11 |
+--------------+
| 192.168.2.12 |
+--------------+
| 192.168.2.40 |
+--------------+

注意

この例では、 FROM 句で使用される構文は、内部結合( FROM t1, t2)の構文と同じです。ただし、実行される操作は真の内部結合ではありません。実際の動作では、 ip_address changes テーブルの各行の値で range_to_values() 関数が呼び出されます。つまり、それは次のように書くことと同等です。

for input_row in ip_address_ranges:
  output_row = range_to_values(input_row.prefix, input_row.range_start, input_row.range_end)

UDTF に値を渡すという概念は、複数の UDTFs に拡張できます。次の例では、 IPV4 アドレスを IPV6 アドレスに「変換」する fake_ipv4_to_ipv6() という名前の UDTF を作成します。次に、クエリは、別の UDTF を含むより複雑なステートメントの一部として関数を呼び出します。

-- Example UDTF that "converts" an IPV4 address to a range of IPV6 addresses.
-- (for illustration purposes only and is not intended for actual use)
CREATE OR REPLACE FUNCTION fake_ipv4_to_ipv6(ipv4 VARCHAR)
    RETURNS TABLE (IPV6 VARCHAR)
    LANGUAGE JAVASCRIPT
    AS $$
      {
        processRow: function f(row, rowWriter, context)  {
          rowWriter.writeRow( {IPV6: row.IPV4 + "." + "000.000.000.000"} );
          rowWriter.writeRow( {IPV6: row.IPV4 + "." + "..."} );
          rowWriter.writeRow( {IPV6: row.IPV4 + "." + "FFF.FFF.FFF.FFF"} );
          }
      }
      $$;

SELECT ipv6 FROM TABLE(fake_ipv4_to_ipv6('192.168.3.100'));

出力:

+-------------------------------+
| IPV6                          |
+===============================+
| 192.168.3.100.000.000.000.000 |
+-------------------------------+
| 192.168.3.100....             |
+-------------------------------+
| 192.168.3.100.FFF.FFF.FFF.FFF |
+-------------------------------+

次のクエリは、 ip_address changes テーブルからの入力で、以前に作成された fake_ipv4_to_ipv6range_to_values() UDTFs を使用します。つまり、一連の IP アドレス範囲で始まり、それらを個々の IPV4 アドレスに変換してから、各 IPV4 アドレスを取得して、それを IPV6 アドレスの範囲に「変換」します。

SELECT rtv6.ipv6
  FROM ip_address_ranges AS r,
       TABLE(range_to_values(r.prefix, r.range_start::FLOAT, r.range_end::FLOAT)) AS rtv,
       TABLE(fake_ipv4_to_ipv6(rtv.ip_address)) AS rtv6
  WHERE r.prefix = '192.168.2'  -- limits the output for this example
  ;

出力:

+------------------------------+
| IPV6                         |
+==============================+
| 192.168.2.10.000.000.000.000 |
+------------------------------+
| 192.168.2.10....             |
+------------------------------+
| 192.168.2.10.FFF.FFF.FFF.FFF |
+------------------------------+
| 192.168.2.11.000.000.000.000 |
+------------------------------+
| 192.168.2.11....             |
+------------------------------+
| 192.168.2.11.FFF.FFF.FFF.FFF |
+------------------------------+
| 192.168.2.12.000.000.000.000 |
+------------------------------+
| 192.168.2.12....             |
+------------------------------+
| 192.168.2.12.FFF.FFF.FFF.FFF |
+------------------------------+
| 192.168.2.40.000.000.000.000 |
+------------------------------+
| 192.168.2.40....             |
+------------------------------+
| 192.168.2.40.FFF.FFF.FFF.FFF |
+------------------------------+

この例では、結合構文を2回使用しましたが、どちらの操作も真の結合ではありません。どちらも、テーブルの出力または別の UDTF を入力として使用する UDTF の呼び出しでした。

真の内部結合は順序に依存しません。たとえば、次のステートメントは同一です。

table1 INNER JOIN table2 ON ...
table2 INNER JOIN table1 ON ...

UDTF に値を入力することは、真の結合では なく 、操作は順序に依存 しません 。たとえば、次のクエリは、 FROM 句の UDTFs の順序が逆であることを除いて、前の例と同じです。

SELECT rtv6.ipv6
  FROM ip_address_ranges AS r,
       TABLE(fake_ipv4_to_ipv6(rtv.ip_address)) AS rtv6,
       TABLE(range_to_values(r.prefix, r.range_start::FLOAT, r.range_end::FLOAT)) AS rtv
 WHERE r.prefix = '192.168.2'  -- limits the output for this example
  ;

クエリは次のエラーメッセージで失敗します。

SQL compilation error: error line 3 at position 35 invalid identifier 'RTV.IP_ADDRESS'

rtv.ip_address 識別子は、使用前に定義されていなかったため無効です。真の結合ではこれは起こりませんが、結合構文を使用して UDTFs を処理すると、このエラーが発生する場合があります。

次に、 UDTF への入力と真の結合を組み合わせたステートメントを試してください。ただし、 UDTF への入力と内部結合の実行はどちらも同じ構文を使用するため、混乱する可能性があります。

-- First, create a small table of IP address owners.
-- This table uses only IPv4 addresses for simplicity.
DROP TABLE ip_address_owners;
CREATE TABLE ip_address_owners (ip_address VARCHAR, owner_name VARCHAR);
INSERT INTO ip_address_owners (ip_address, owner_name) VALUES
  ('192.168.2.10', 'Barbara Hart'),
  ('192.168.2.11', 'David Saugus'),
  ('192.168.2.12', 'Diego King'),
  ('192.168.2.40', 'Victoria Valencia')
  ;

-- Now join the IP address owner table to the IPv4 addresses.
SELECT rtv.ip_address, ipo.owner_name
  FROM ip_address_ranges AS r,
       TABLE(range_to_values(r.prefix, r.range_start::FLOAT, r.range_end::FLOAT)) AS rtv,
       ip_address_owners AS ipo
 WHERE ipo.ip_address = rtv.ip_address AND
      r.prefix = '192.168.2'   -- limits the output for this example
  ;

出力:

+--------------+-------------------+
| IP_ADDRESS   | OWNER_NAME        |
+==============+===================+
| 192.168.2.10 | Barbara Hart      |
+--------------+-------------------+
| 192.168.2.11 | David Saugus      |
+--------------+-------------------+
| 192.168.2.12 | Diego King        |
+--------------+-------------------+
| 192.168.2.40 | Victoria Valencia |
+--------------+-------------------+

注意

前述の例は説明どおりに機能します。ただし、 UDTFs を真の結合と組み合わせる場合は、非決定的または予期しない動作が発生する可能性があるため、注意が必要です。

この動作は将来変更される可能性があることに注意してください。