테이블 형식 JavaScript UDF(UDTF)¶

processRow()¶

이 콜백 함수는 입력 관계의 각 행에 대해 한 번씩 호출됩니다. processRow() 에 대한 인자는 row 오브젝트에 전달됩니다. UDTF를 만드는 데 사용된 CREATE FUNCTION 문에 정의된 각 인자의 경우, 모두 대문자로 된 동일한 이름을 가진 row 오브젝트에 속성이 있습니다. 이 속성의 값은 현재 행에 대한 인자 값입니다. (값은 JavaScript 값으로 변환됩니다.)

rowWriter 인자는 사용자 제공 코드에서 출력 행을 생성하는 데 사용됩니다. rowWriter 오브젝트는 단일 함수인 writeRow() 를 정의합니다. writeRow() 함수는 하나의 인자인 행 오브젝트 를 사용하며 이는 JavaScript 오브젝트로 표시되는 출력 테이블의 단일 행입니다. CREATE FUNCTION 명령의 RETURNS 절에 정의된 각 열의 경우, 행 오브젝트에 해당 속성을 정의할 수 있습니다. 행 오브젝트의 해당 속성 값은 출력 관계에서 해당 열의 값이 됩니다. 행 오브젝트에 해당 속성이 없는 출력 열은 결과 테이블에서 NULL 값을 갖습니다.

finalize()¶

finalize() 콜백 함수는 모든 행이 processRow() 에 전달된 후 한 번 호출됩니다. (데이터가 파티션 으로 그룹화되면 해당 파티션의 모든 행이 processRow() 에 전달된 후 각 파티션에 대해 finalize() 가 한 번 호출됩니다.)

이 콜백 함수는 processRow() 에 전달된 것과 동일한 행 rowWriter 를 사용하여 processRow() 에서 집계되었을 수 있는 모든 상태를 출력하는 데 사용할 수 있습니다.

initialize()¶

이 콜백 함수는 processRow() 를 호출하기 전에 각 파티션에 대해 한 번 호출됩니다.

결과 계산 중에 필요한 상태를 설정하려면 initialize() 를 사용하십시오.

initialize() 함수의 argumentInfo 매개 변수에는 사용자 정의 함수에 대한 인자에 관한 메타데이터가 포함되어 있습니다. 예를 들어, UDF가 다음과 같이 정의된 경우:

CREATE FUNCTION f(argument_1 INTEGER, argument_2 VARCHAR) ...

그러면 argumentInfo 는 argument_1 및 argument_2 에 대한 정보를 포함합니다.

argumentInfo 에는 이러한 각 인자에 대한 속성이 있습니다. 각 속성은 다음 값을 가진 오브젝트입니다.

• type: 문자열입니다. 이 인자의 형식입니다.

• isConst: 부울입니다. true이면 이 인자의 값은 상수입니다(즉, 모든 행에 대해 동일함).

• constValue: isConst (위에 정의됨)가 true이면 이 항목에는 인자의 상수 값이 포함됩니다. 그렇지 않으면 이 필드는 undefined 입니다.

initialize() 함수는 출력 행을 생성할 수 없습니다.

콜백 함수에 대한 일반 사용법 노트¶

• 세 가지 콜백 함수는 모두 context 오브젝트를 사용합니다. 이는 향후 사용을 위해 예약되며 현재 비어 있습니다.

  조심

  context 오브젝트를 수정하면 정의되지 않은 동작이 발생할 수 있습니다.

• 필요에 따라 UDTF에서 사용하기 위해 오브젝트에 추가 함수 및 속성을 정의할 수 있습니다.

• 콜백 함수에 대한 인자는 위치 인자이며, 어떤 이름으로든 지정할 수 있습니다. 그러나 이 항목의 목적을 위해 위의 이름이 나머지 설명 및 예에 사용됩니다.

결과 열¶

위의 분할 예제처럼 테이블이 테이블 함수에 조인되면 결과 집합에는 선택한 내용에 따라 다음이 포함될 수 있습니다.

• CREATE FUNCTION 명령의 RETURNS 절에 정의된 열입니다.

• 테이블의 열은 데이터를 분할하는 데 사용되는 열과 UDTF의 입력 매개 변수로 사용되는지 여부에 관계없이 다른 열을 모두 포함합니다.

processRow 콜백에서 생성되는 행과 finalize 에서 생성되는 행은 다음과 같은 방식으로 다릅니다.

• processRow 에서 행이 생성되면 Snowflake는 이를 입력 행, 즉 row 인자로서 함수에 전달된 항목에 대해 상관 관계를 지정할 수 있습니다. 주어진 processRow 호출이 두 개 이상의 행을 생성하는 경우 입력 특성은 각 출력 행과 상관관계가 있다는 점에 유의하십시오.

  processRow 에서 생성된 행의 경우 모든 입력 열을 출력 관계에 조인할 수 있습니다.

finalize 콜백에서 Snowflake는 상관 관계를 지정할 “현재 행”이 없기 때문에 단일 행에 대해 상관 관계를 지정할 수 없습니다.

• finalize 콜백에서 생성된 행의 경우, PARTITION BY 절에 사용된 열만 사용할 수 있습니다(이러한 항목은 현재 파티션의 모든 행에 대해 동일하므로). 기타 모든 속성은 NULL입니다. PARTITION BY 절이 지정되지 않은 경우 모든 해당 특성은 NULL입니다.

분할 없음¶

이 간단한 예는 UDTF를 호출하는 방법을 보여줍니다. 이 예는 리터럴 값을 전달합니다. UDTF는 전달된 순서의 역순으로 매개 변수를 반환할 뿐입니다. 이 예에서는 분할을 사용하지 않습니다.

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

이 예는 UDTF를 호출하고 다른 테이블의 값을 전달합니다. 이 예에서 js_udtf 라는 UDTF는 tab1 이라는 테이블의 각 행에 대해 한 번씩 호출됩니다. 함수가 호출될 때마다 현재 행의 c1 및 c2 열에서 값이 전달됩니다. 위와 같이 UDTF는 PARTITION BY 절 없이 호출됩니다.

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

분할이 사용되지 않는 경우, Snowflake 실행 엔진은 함수를 처리하는 웨어하우스의 크기 및 입력 관계의 카디널리티와 같은 여러 요인에 따라 입력 자체를 분할합니다. 이 모드에서 실행할 때 사용자 코드는 파티션에 대해 가정할 수 없습니다. 이는 함수가 출력을 생성하기 위해 개별적으로 행만 볼 필요가 있고 행 간에 집계된 상태가 없는 경우에 가장 유용합니다.

명시적 분할¶

JavaScript UDTF는 파티션을 사용하여 호출할 수도 있습니다. 예:

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 절은 모든 행이 동일 파티션에 속함을 의미합니다(즉, 전체 입력 관계가 하나의 파티션임).

기본적인 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() 콜백을 사용하여 카운터를 0으로 초기화합니다. 이는 주어진 함수 인스턴스가 여러 파티션을 처리하는 데 사용될 수 있기 때문에 필요합니다.

-- 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가 자동으로 파티션을 정의합니다. 이 예에서는 행 수가 적기 때문에 파티션이 하나만 생성됩니다(즉, finalize() 호출이 하나만 실행됨). 마지막 행의 입력 열에는 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 열에 대해 분할하여 두 개의 파티션을 생성합니다. 각 파티션에 대해 finalize() 콜백에서 단일 행이 반환되어 총 두 개의 행을 생성하며 이는 s 열의 NULL 값으로 구분됩니다. p 는 PARTITION BY 열이므로 finalize() 에서 생성된 행은 현재 파티션을 정의하는 p 값을 갖습니다.

테이블 값 및 기타 UDTF를 입력으로 사용하는 확장된 예¶

이 기본 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 |
+--------------+

UDTF에 값을 전달한다는 개념은 여러 UDTF로 확장될 수 있습니다. 다음 예는 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_ipv6 및 range_to_values() UDTF를 사용합니다. 즉, 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 |
+------------------------------+

이 예에서는 조인 구문을 두 번 사용했지만, 두 작업 모두 실제 조인이 아니었습니다. 둘 다 테이블의 출력이나 다른 UDTF를 입력으로 사용하는 UDTF에 대한 호출이었습니다.

실제 내부 조인은 순서를 구분하지 않습니다. 예를 들어 다음 문은 동일합니다.

UDTF에 값을 입력하는 것은 실제 조인이 아니며 작업은 순서를 구분하지 않습니다. 예를 들어, 다음 쿼리는 FROM 절에서 UDTF의 순서를 반대로 한다는 점을 제외하고는 이전 예와 동일합니다.

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 식별자는 유효하지 않습니다. 사용되기 전에 정의되지 않았기 때문입니다. 실제 조인에서는 이런 일이 일어나지 않지만, 조인 구문을 사용하여 UDTF를 처리할 때 이 오류가 발생할 수 있습니다.

다음으로, 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 |
+--------------+-------------------+