UDFs tabulares de JavaScript (UDTFs)¶

processRow()¶

Essa função de chamada de retorno é invocada uma vez para cada linha na relação de entrada. Os argumentos para processRow() são passados no objeto row. Para cada um dos argumentos definidos na instrução CREATE FUNCTION usada para criar a UDTF, há uma propriedade no objeto row com o mesmo nome com todas as letras maiúsculas. O valor dessa propriedade é o valor do argumento para a linha atual. (O valor é convertido para um valor de JavaScript).

O argumento rowWriter é usado pelo código fornecido pelo usuário para produzir linhas de saída. O objeto rowWriter define uma única função, writeRow(). A função writeRow() leva um argumento, o objeto linha, que é uma única linha na tabela de saída representada como um objeto de JavaScript. Para cada coluna definida na cláusula RETURNS do comando CREATE FUNCTION, uma propriedade correspondente pode ser definida no objeto da linha. O valor dessa propriedade no objeto da linha torna-se o valor para a coluna correspondente na relação de saída. Qualquer coluna de saída sem uma propriedade correspondente no objeto da linha terá o valor NULL na tabela de resultados.

finalize()¶

A função de retorno de chamada finalize() é invocada uma vez, após todas as linhas terem sido passadas para processRow(). (Se os dados forem agrupados em partições, finalize() é invocado uma vez para cada partição, depois que todas as linhas nessa partição tiverem sido passadas para processRow()).

Essa função de retorno pode ser usada para produzir qualquer estado que possa ter sido agregado em processRow() usando a mesma linha rowWriter que é passada para processRow().

initialize()¶

Essa função de retorno é invocada uma vez para cada partição antes de qualquer invocação de processRow().

Use initialize() para configurar qualquer estado necessário durante a computação do resultado.

O parâmetro initialize() da função argumentInfo contém metadados sobre os argumentos para a função definida pelo usuário. Por exemplo, se a UDF for definida como:

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

argumentInfo conterá informações sobre argument_1 e argument_2.

argumentInfo tem uma propriedade para cada um desses argumentos. Cada propriedade é um objeto com os seguintes valores:

• type: String. O tipo desse argumento.

• isConst: Boolean. Se for verdadeiro, o valor desse argumento é constante (ou seja, é o mesmo para cada linha).

• constValue: Se isConst (como definido acima) for verdadeiro, essa entrada conterá o valor constante do argumento; caso contrário, esse campo será undefined.

A função initialize() não pode produzir linhas de saída.

Notas de uso geral para funções de retorno de chamada¶

• Todas as três funções de retorno de chamada recebem um objeto context; esse objeto está reservado para uso futuro e atualmente está vazio.

  Cuidado

  A modificação do objeto context pode produzir um comportamento indefinido.

• Funções e propriedades adicionais podem ser definidas, conforme necessário, para o objeto para uso na UDTF.

• Os argumentos para as funções de retorno de chamada são posicionais e podem ter qualquer nome; entretanto, para os fins deste tópico, os nomes acima são usados para a discussão e exemplos restantes.

Colunas de resultados¶

Quando uma tabela é unida a uma função de tabela, como nos exemplos de particionamento acima, o conjunto de resultados pode conter o seguinte, dependendo do que for selecionado:

• As colunas definidas na cláusula RETURNS do comando CREATE FUNCTION.

• As colunas de tabela, incluindo as colunas usadas para partição dos dados e outras colunas, sejam elas usadas ou não como parâmetros de entrada para a UDTF.

Observe que as linhas produzidas no retorno de chamada processRow e as linhas produzidas por finalize diferem das seguintes maneiras:

• Quando uma linha é produzida em processRow, o Snowflake pode correlacioná-la a uma linha de entrada, ou seja, aquela passada para a função como o argumento row. Observe que se uma determinada invocação processRow produz mais de uma linha, os atributos de entrada são correlacionados com cada linha de saída.

  Para linhas produzidas em processRow, todas as colunas de entrada podem ser unidas à relação de saída.

Na função de retorno de chamada finalize, o Snowflake é incapaz de correlacioná-la a qualquer linha porque não há uma linha atual a ser correlacionada.

• Para linhas produzidas no retorno de chamada finalize, apenas as colunas usadas na cláusula PARTITION BY estão disponíveis (pois são as mesmas para qualquer linha na partição atual); todos os outros atributos são NULL. Se nenhuma cláusula PARTITION BY for especificada, todos esses atributos serão NULL.

Sem particionamento¶

Este exemplo simples mostra como chamar uma UDTF. Este exemplo passa valores literais. A UDTF apenas retorna os parâmetros na ordem inversa da ordem em que foram passados. Este exemplo não usa particionamento.

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

Este exemplo chama uma UDTF e passa-lhe valores de outra tabela. Neste exemplo, a UDTF com o nome js_udtf é chamada uma vez para cada linha da tabela chamada tab1. Cada vez que a função é chamada, ela recebe valores das colunas c1 e c2 da linha atual. Como acima, a UDTF é chamada sem uma cláusula PARTITION BY.

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

Quando nenhum particionamento é usado, o mecanismo de execução do Snowflake particiona a entrada em si conforme vários fatores, tais como o tamanho do warehouse que processa a função e a cardinalidade da relação de entrada. Quando executado nesse modo, o código do usuário não pode fazer suposições sobre as partições. Isso é útil principalmente quando a função só precisa olhar as linhas isoladamente para produzir sua saída e nenhum estado é agregado entre linhas diferentes.

Particionamento explícito¶

UDTFs de JavaScript também podem ser chamado usando uma partição. Por exemplo:

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

Particionamento explícito com uma cláusula OVER vazia¶

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

Uma cláusula OVER vazia significa que cada linha pertence à mesma partição (ou seja, toda a relação de entrada é uma partição).

Exemplos básicos de Hello World¶

A UDTF de JavaScript a seguir não recebe nenhum parâmetro e sempre retorna os mesmos valores. Ela é fornecida principalmente para fins ilustrativos:

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());

Saída:

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

A UDTF de JavaScript a seguir também é para fins de ilustração, mas utiliza um parâmetro de entrada. Observe que o JavaScript faz distinção entre maiúsculas e minúsculas, mas o SQL força os identificadores a usar maiúsculas; portanto, quando o código de JavaScript faz referência a um nome de parâmetro de SQL, o código de JavaScript deve usar maiúsculas.

Observe também que os parâmetros da função são acessados através do parâmetro chamado row na função get_params():

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'));

Saída:

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

Exemplos básicos ilustrando as funções de retorno de chamada¶

A UDTF de JavaScript a seguir ilustra todas as funções de retorno de chamada de API e várias colunas de saída. Ela simplesmente retorna todas as linhas como estão e fornece uma contagem do número de caracteres vistos em cada partição. Ela também ilustra como compartilhar o estado em uma partição usando uma referência THIS. Note que o exemplo usa uma função de retorno de chamada initialize() para inicializar o contador a zero; isso é necessário porque uma determinada instância de função pode ser usada para processar várias partições:

-- 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;
    }}';

A consulta a seguir ilustra a chamada da UDTF CHAR_SUM na tabela parts sem particionamento:

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

Saída:

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

Quando nenhuma partição é especificada, o Snowflake define partições automaticamente. Neste exemplo, devido ao pequeno número de linhas, apenas uma partição é criada (ou seja, apenas uma invocação de finalize() é executada). Observe que a última linha tem valores NULL nas colunas de entrada.

A mesma consulta, mas com particionamento explícito:

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

Saída:

+--------+---------+-----+
| 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  |
+--------+---------+-----+

Este exemplo particiona sobre a coluna p, produzindo duas partições. Para cada partição, uma única linha é retornada na função retorno de chamada finalize(), produzindo um total de duas linhas, distinguidas pelo valor NULL na coluna s. Como p é a coluna PARTITION BY, as linhas criadas em finalize() têm o valor p que define a partição atual.

Exemplos estendidos usando valores de tabela e outras UDTFs como entrada¶

Esta UDTF básica converte um “intervalo” de endereços IP para uma lista completa de endereços IP. A entrada consiste nos primeiros 3 segmentos do endereço IP (por exemplo, '192.168.1') e, em seguida, no início e fim do intervalo usado para gerar o último segmento (por exemplo, 42 e 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));

Saída:

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

Com base no exemplo anterior, talvez você queira calcular endereços IP individuais para mais de um intervalo. Esta próxima instrução cria uma tabela de intervalos que pode ser usada para expandir para endereços IP individuais. A consulta então insere as linhas da tabela na UDTF range_to_values() para retornar os endereços IP individuais:

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;

Saída:

+--------------+
| 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 |
+--------------+

O conceito de passar valores a uma UDTF pode ser estendido a várias UDTFs. O próximo exemplo cria uma UDTF chamada fake_ipv4_to_ipv6() que “converte” endereços IPV4 para endereços IPV6. A consulta então chama a função como parte de uma instrução mais complexa envolvendo outra 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'));

Saída:

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

A consulta a seguir usa as UDTFs fake_ipv4_to_ipv6 e range_to_values() criadas anteriormente com a entrada da tabela ip_address changes. Em outras palavras, ela começa com um conjunto de intervalos de endereços IP, converte-os em endereços IPV4 individuais e então pega cada endereço IPV4 e o “converte” para um intervalo de endereços 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
  ;

Saída:

+------------------------------+
| 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 |
+------------------------------+

Observe que este exemplo usou uma sintaxe de junção duas vezes, mas nenhuma das operações foi uma junção verdadeira; ambas foram chamadas a uma UDTF usando a saída de uma tabela ou outra UDTF como entrada.

Uma junção interna verdadeira não leva em conta a ordem. Por exemplo, as instruções a seguir são idênticas:

A entrada de valores em uma UDTF não é uma verdadeira junção, e as operações levam em conta a ordem. Por exemplo, a consulta a seguir é idêntica ao exemplo anterior, com a diferença que ela inverte a ordem das UDTFs na cláusula FROM:

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
  ;

A consulta falha com a seguinte mensagem de erro:

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

O identificador rtv.ip_address é inválido porque não foi definido antes de ser usado. Isso não aconteceria em uma junção verdadeira, mas esse erro pode ocorrer ao processar UDTFs usando uma sintaxe de junção.

Em seguida, tente uma instrução que mistura a entrada a uma UDTF com uma junção verdadeira; no entanto, lembre a inserção de uma entrada em uma UDTF e a realização de uma junção interna usam a mesma sintaxe, o que pode ser confuso:

-- 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
  ;

Saída:

+--------------+-------------------+
| 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 |
+--------------+-------------------+