CREATE FUNCTION¶

Sintaxe¶

A sintaxe para CREATE FUNCTION varia dependendo da linguagem que você está usando como o manipulador da UDF.

Manipulador de Java

Usar a sintaxe abaixo se o código fonte estiver em linha:

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ SECURE ] FUNCTION [ IF NOT EXISTS ] <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
    [ COPY GRANTS ]
    RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
    [ [ NOT ] NULL ]
    LANGUAGE JAVA
    [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
    [ VOLATILE | IMMUTABLE ]
    [ RUNTIME_VERSION = <java_jdk_version> ]
    [ COMMENT = '<string_literal>' ]
    [ IMPORTS = ( '<stage_path_and_file_name_to_read>' [ , ... ] ) ]
    [ PACKAGES = ( '<package_name_and_version>' [ , ... ] ) ]
    HANDLER = '<path_to_method>'
    [ TARGET_PATH = '<stage_path_and_file_name_to_write>' ]
    AS '<function_definition>'

Use a seguinte sintaxe se o código do manipulador for referenciado em um estágio (como em um JAR):

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ SECURE ] FUNCTION [ IF NOT EXISTS ] <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
    [ COPY GRANTS ]
    RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
    [ [ NOT ] NULL ]
    LANGUAGE JAVA
    [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
    [ VOLATILE | IMMUTABLE ]
    [ RUNTIME_VERSION = <java_jdk_version> ]
    [ COMMENT = '<string_literal>' ]
    IMPORTS = ( '<stage_path_and_file_name_to_read>' [ , ... ] )
    HANDLER = '<path_to_method>'

Manipulador de JavaScript

CREATE [ OR REPLACE ] [ SECURE ] FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  [ COPY GRANTS ]
  RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
  [ [ NOT ] NULL ]
  LANGUAGE JAVASCRIPT
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ]
  [ COMMENT = '<string_literal>' ]
  AS '<function_definition>'

Manipulador de Python

Usar a sintaxe abaixo se o código fonte estiver em linha:

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ SECURE ] FUNCTION [ IF NOT EXISTS ] <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
    [ COPY GRANTS ]
    RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
    [ [ NOT ] NULL ]
    LANGUAGE PYTHON
    [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
    [ VOLATILE | IMMUTABLE ]
    RUNTIME_VERSION = <python_version>
    [ COMMENT = '<string_literal>' ]
    [ IMPORTS = ( '<stage_path_and_file_name_to_read>' [ , ... ] ) ]
    [ PACKAGES = ( '<package_name>[==<version>]' [ , ... ] ) ]
    HANDLER = '<function_name>'
    AS '<function_definition>'

Use a seguinte sintaxe se o código do manipulador for referenciado em um estágio (como em um módulo):

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] FUNCTION [ IF NOT EXISTS ] <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
    [ COPY GRANTS ]
    RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
    [ [ NOT ] NULL ]
    LANGUAGE PYTHON
    [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
    [ VOLATILE | IMMUTABLE ]
    RUNTIME_VERSION = <python_version>
    [ COMMENT = '<string_literal>' ]
    IMPORTS = ( '<stage_path_and_file_name_to_read>' [ , ... ] )
    [ PACKAGES = ( '<package_name>[==<version>]' [ , ... ] ) ]
    HANDLER = '<module_file_name>.<function_name>'

Manipulador de SQL

CREATE [ OR REPLACE ] [ SECURE ] FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  [ COPY GRANTS ]
  RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
  [ [ NOT ] NULL ]
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ]
  [ MEMOIZABLE ]
  [ COMMENT = '<string_literal>' ]
  AS '<function_definition>'

Parâmetros obrigatórios¶

Todos os idiomas

name ( [ arg_name arg_data_type ] [ , ... ] )

Especifica o identificador (e opcionalmente um ou mais argumentos/entradas) para a UDF. O identificador não precisa ser único para o esquema no qual a UDF é criada porque as UDFs são identificadas e resolvidas por seu nome e tipos de argumentos.

No entanto, o identificador deve começar com um caractere alfabético e não pode conter espaços ou caracteres especiais a menos que toda a cadeia de caracteres do identificador esteja entre aspas duplas (por exemplo, "My object"). Os identificadores delimitados por aspas duplas também diferenciam letras maiúsculas de minúsculas.

Para obter mais detalhes, consulte Requisitos para identificadores.

RETURNS ...

Especifica os resultados devolvidos pela UDF, o que determina o tipo de UDF:

• result_data_type: cria um UDF escalar que retorna um único valor com o tipo de dados especificado.

• TABLE ( col_name col_data_type , ... ): cria um UDF de tabela que retorna resultados tabulares com a(s) coluna(s) de tabela especificada(s) e tipo(s) de coluna.

Nota

Para UDFs Java, o result_data_type deve estar na coluna SQL Data Type da tabela de mapeamentos de tipos SQL-Java.

Para UDFs Python, o result_data_type deve estar na coluna SQL Data Type da tabela de mapeamentos de tipos SQL-Python.

AS function_definition

Define o código do manipulador executado quando a UDF é chamada. O valor function_definition deve ser o código fonte em uma das linguagens suportadas pelos manipuladores. O código pode ser:

• Java. Para obter mais informações, consulte Introdução a UDFs de Java.

• JavaScript. Para obter mais informações, consulte Introdução a UDFs de JavaScript.

• Python. Para obter mais informações, consulte Introdução a UDFs de Python.

• Uma expressão SQL. Para obter mais informações, consulte Introdução a UDFs de SQL.

Para obter mais detalhes, consulte Notas de uso (neste tópico).

Nota

A cláusula AS não é necessária quando o código do manipulador da UDF é referenciado em um estágio com a cláusula IMPORTS.

Java

LANGUAGE JAVA

Especifica que o código está na linguagem Java.

RUNTIME_VERSION = java_jdk_version

Especifica a versão de tempo de execução Java JDK a ser utilizada. As versões suportadas de Java são:

• 11.x

Se RUNTIME_VERSION não estiver definido, é utilizado Java JDK 11.

IMPORTS = (stage_path_and_file_name_to_read)

O local (estágio), caminho e nome do(s) arquivo(s) a ser(em) importado(s).

Um arquivo pode ser um arquivo JAR ou outro tipo de arquivo.

Se o arquivo for um arquivo JAR, ele pode conter um ou mais arquivos .class e zero ou mais arquivos de recursos.

JNI (Java Native Interface) não é suportado. O Snowflake proíbe o carregamento de bibliotecas que contenham código nativo (ao contrário de código de bytes de Java).

UDFs Java também podem ler arquivos não JAR. Para obter um exemplo, consulte Como ler um arquivo usando IMPORTS.

Se você planeja copiar um arquivo (JAR ou outro arquivo) para um estágio, então o Snowflake recomenda usar um estágio interno nomeado porque o comando PUT oferece suporte à cópia de arquivos para estágios internos nomeados, e o comando PUT é normalmente a maneira mais fácil de mover um arquivo JAR para um estágio.

Os estágios externos são permitidos, mas não são suportados por PUT.

Cada arquivo na cláusula IMPORTS deve ter um nome único, mesmo que os arquivos estejam em subdiretórios diferentes ou em estágios diferentes.

Se ambas as cláusulas IMPORTS e TARGET_PATH estiverem presentes, o nome do arquivo na cláusula TARGET_PATH deve ser diferente de cada nome de arquivo na cláusula IMPORTS, mesmo que os arquivos estejam em subdiretórios diferentes ou em estágios diferentes.

O Snowflake retorna um erro se o TARGET_PATH corresponder a um arquivo existente; você não pode usar TARGET_PATH para substituir um arquivo existente.

Para uma UDF cujo manipulador se encontra em um estágio, a cláusula IMPORTS é necessária porque especifica a localização do arquivo JAR que contém a UDF.

Para UDF cujo código do manipulador seja inline, a cláusula IMPORTS só é necessária se a UDF em linha precisar acessar outros arquivos, tais como bibliotecas ou arquivos de texto.

Para pacotes do sistema Snowflake, como o pacote Snowpark, você pode especificar o pacote com a cláusula PACKAGES em vez de especificar seu arquivo JAR com IMPORTS. Quando você fizer isso, o pacote JAR não precisa ser incluído em um valor IMPORTS.

Java em linha

function_definition

UDFs de Java em linha exigem uma definição de função.

JavaScript

LANGUAGE JAVASCRIPT

Especifica que o código está na linguagem JavaScript.

Python

LANGUAGE PYTHON

Especifica que o código está na linguagem Python.

RUNTIME_VERSION = python_version

Especifica a versão Python a ser utilizada. As versões suportadas do Python são:

• 3.8

IMPORTS = (stage_path_and_file_name_to_read)

O local (estágio), caminho e nome do(s) arquivo(s) a ser(em) importado(s).

Um arquivo pode ser um arquivo .py ou outro tipo de arquivo.

UDFs de Python também pode ler arquivos não Python, tais como arquivos de texto. Para obter um exemplo, consulte Como ler arquivos com um manipulador de UDF.

Se você planeja copiar um arquivo para um estágio, então o Snowflake recomenda usar um estágio interno nomeado porque o comando PUT oferece suporte à cópia de arquivos para estágios internos nomeados, e o comando PUT é normalmente a maneira mais fácil de mover um arquivo para um estágio.

Os estágios externos são permitidos, mas não são suportados por PUT.

Cada arquivo na cláusula IMPORTS deve ter um nome único, mesmo que os arquivos estejam em subdiretórios diferentes ou em estágios diferentes.

Quando o código do manipulador é armazenado em um estágio, você deve usar a cláusula IMPORTS para especificar a localização do código do manipulador.

Para um UDF de Phyton em linha, a cláusula IMPORTS só é necessária se o manipulador do UDF precisar acessar outros arquivos, tais como pacotes ou arquivos de texto.

Para pacotes incluídos no sistema Snowflake, como numpy, você pode especificar o pacote com a cláusula PACKAGES sozinha, omitindo a origem do pacote como um valor IMPORTS.

HANDLER = handler_name

O nome da função ou classe do manipulador.

• Se o manipulador for para um UDF escalar, retornando um valor não tabular, o valor de HANDLER deve ser um nome de função. Se o código do manipulador estiver em linha com a instrução CREATE FUNCTION, você pode usar apenas o nome da função. Quando o código do manipulador é referenciado em um estágio, este valor deve ser qualificado com o nome do módulo, como na forma a seguir: my_module.my_function.

• Se o manipulador for para umUDF de tabela, o valor de HANDLER deve ser o nome de uma classe de manipulador.

Parâmetros opcionais¶

Todos os idiomas

SECURE

Especifica que a função é segura. Para obter mais informações sobre funções seguras, consulte Proteção de informações sensíveis com UDFs e procedimentos armazenados seguros.

[ [ NOT ] NULL ]

Especifica se a função pode retornar valores NULL ou deve retornar somente valores NON NULL. O padrão é NULL (ou seja, a função pode retornar NULL).

Nota

Atualmente, a cláusula NOT NULL não é aplicada para SQL UDFs. SQL UDFs declarados como NOT NULL podem retornar valores NULL. O Snowflake recomenda evitar NOT NULL para SQL UDFs a menos que o código na função seja escrito para garantir que valores NULL nunca sejam retornados.

CALLED ON NULL INPUT ou . RETURNS NULL ON NULL INPUT | STRICT

Especifica o comportamento da UDF quando chamada com entradas nulas. Ao contrário das funções definidas pelo sistema, que sempre retornam nulo quando qualquer entrada é nula, as UDFs podem lidar com entradas nulas, retornando valores não nulos mesmo quando uma entrada é nula:

• CALLED ON NULL INPUT sempre chamará o UDF com entradas nulas. Cabe à UDF lidar apropriadamente com tais valores.

• RETURNS NULL ON NULL INPUT (ou seu sinônimo STRICT) não chamará a UDF se alguma entrada for nula. Em vez disso, um valor nulo será sempre retornado para essa linha. Note que a UDF ainda pode retornar nulo para entradas não nulas.

Padrão: CALLED ON NULL INPUT

VOLATILE | IMMUTABLE

Especifica o comportamento da UDF ao retornar resultados:

• VOLATILE: o UDF pode retornar valores diferentes para linhas diferentes, mesmo para a mesma entrada (por exemplo, devido a não determinação e estado).

• IMMUTABLE: a UDF assume que a função, quando chamada com as mesmas entradas, retornará sempre o mesmo resultado. Esta garantia não é verificada. Especificar IMMUTABLE para uma UDF que retorna valores diferentes para a mesma entrada resultará em comportamento indefinido.

Padrão: VOLATILE

COMMENT = 'string_literal'

Especifica um comentário para a UDF, que é exibido na coluna DESCRIPTION na saída SHOW FUNCTIONS e SHOW USER FUNCTIONS.

Padrão: user-defined function

COPY GRANTS

Especifica para manter os privilégios de acesso da função original quando uma nova função é criada usando CREATE OR REPLACE FUNCTION.

O parâmetro copia todos os privilégios, exceto OWNERSHIPos da função existente para a nova função. A nova função não herdará as concessões futuras definidas para o tipo de objeto no esquema. Por padrão, a função que executa a instrução CREATE FUNCTION é a proprietária da nova função.

Nota:

• Com compartilhamento de dados, se a função existente foi compartilhada com outra conta, a função de substituição também será compartilhada.

• A saída SHOW GRANTS para a função de substituição relaciona o cessionário para os privilégios copiados como a função que executou a instruçãoCREATE FUNCTION, com o carimbo de data/hora atual quando a instrução foi executada.

• A operação de cópia de concessões ocorre atomicamente no comando CREATE FUNCTION (ou seja, dentro da mesma transação).

Java

PACKAGES = (package_name_and_version)

O nome e o número da versão dos pacotes do sistema Snowflake exigidos como dependências. O valor deve ser da forma package_name:version_number, em que package_name é snowflake_domain:package. Observe que você pode especificar latest como o número da versão para que o Snowflake utilize a última versão disponível no sistema.

Por exemplo:

-- Use version 1.2.0 of the Snowpark package.
packages=('com.snowflake:snowpark:1.2.0')

-- Use the latest version of the Snowpark package.
packages=('com.snowflake:snowpark:latest')

Copy

Você pode descobrir a lista de pacotes de sistemas suportados executando o seguinte SQL no Snowflake:

select * from information_schema.packages where language ='java';

Copy

Para uma dependência especificada com PACKAGES, você não precisa especificar também seu arquivo JAR em uma cláusula IMPORTS.

Java em linha

TARGET_PATH = stage_path_and_file_name_to_write

A cláusula TARGET_PATH especifica o local para o qual Snowflake deve escrever o código compilado (arquivo JAR) após compilar o código fonte especificado no arquivo function_definition.

Se esta cláusula for incluída, o usuário deverá remover manualmente o arquivo JAR quando ele não for mais necessário (normalmente quando a UDF Java é descartada).

Se esta cláusula for omitida, o Snowflake recompila o código fonte sempre que o código é necessário. O arquivo JAR não é armazenado permanentemente, e o usuário não precisa limpar o arquivo JAR.

Python

PACKAGES = (package_name[==package_version])

O nome e o número da versão dos pacotes exigidos como dependências. O valor deve ser da forma package_name==version_number. Se você omitir o número da versão, o Snowflake utilizará o último pacote disponível no sistema.

Por exemplo:

-- Use version 1.2.2 of the NumPy package.
packages=('numpy==1.2.2')

-- Use the latest version of the NumPy package.
packages=('numpy')

Copy

Você pode descobrir a lista de pacotes de sistemas suportados executando o seguinte SQL no Snowflake:

select * from information_schema.packages where language ='python';

Copy

Para obter mais informações sobre os pacotes incluídos, consulte Como usar pacotes de terceiros.

SQL

MEMOIZABLE

Especifica que a função é memoizável.

Para obter mais detalhes, consulte UDFs memoizáveis.

Notas de uso¶

Todos os idiomas

• function_definition tem restrições de tamanho. O tamanho máximo permitido está sujeito a alterações.

• Os delimitadores em torno de function_definition podem ser aspas simples ou um par de sinais de cifrão.

  Usar $$ como delimitador facilita a escrita de funções com aspas simples.

  Se o delimitador para o corpo da função for o caractere de aspas simples, então qualquer aspas simples dentro de function_definition (por exemplo, literais de cadeia de caracteres) devem ter um escape com aspas simples.

• Se utilizar uma UDF em uma política de mascaramento, certifique-se de que o tipo de dados da coluna, a UDF e a política de mascaramento sejam correspondentes. Para obter mais informações, consulte Funções definidas pelo usuário em uma política de mascaramento.

• Em relação aos metadados:

  Atenção

  Os clientes devem garantir que nenhum dado pessoal (exceto para um objeto do usuário), dados sensíveis, dados controlados por exportação ou outros dados regulamentados sejam inseridos como metadados ao usar o serviço Snowflake. Para obter mais informações, consulte Campos de metadados no Snowflake.

• Instruções CREATE OR REPLACE <object> são atômicas. Ou seja, quando o objeto é substituído, a exclusão do objeto antigo e a criação do novo objeto são processadas em uma única transação.

Java

• Em Java, os tipos de dados primitivos não permitem valores NULL, portanto, passar um NULL para um argumento de tal tipo resulta em um erro.

• Na cláusula HANDLER, o nome do método diferencia maiúsculas e minúsculas.

• Nas cláusulas IMPORTS e TARGET_PATH:

  • Pacote, classe e nome(s) do(s) arquivo(s) diferenciam maiúsculas e minúsculas.

  • O(s) nome(s) do(s) estágio(s) não diferencia(m) maiúsculas e minúsculas.

• Você pode usar a cláusula PACKAGES para especificar o nome do pacote e o número da versão para as dependências definidas pelo sistema Snowflake, tais como as do Snowpark. Para outras dependências, especificar os arquivos JAR de dependência com a cláusula IMPORTS.

• O Snowflake valida isso:

  • O arquivo JAR especificado no HANDLER da instrução CREATE FUNCTION existe e contém a classe e o método especificados.

  • Os tipos de entrada e saída especificados na instrução da UDF são compatíveis com os tipos de entrada e saída do método Java.

  A validação pode ser feita no momento da criação ou no momento da execução.

  • Se o usuário estiver conectado a um warehouse Snowflake ativo no momento em que a instrução CREATE FUNCTION é executada, então a UDF é validada no momento da criação.

  • Caso contrário, o UDF é criado, mas não é validado imediatamente, e Snowflake retorna a seguinte mensagem: Function <nome> created successfully, but could not be validated since there is no active warehouse.

JavaScript

• O Snowflake não valida o código JavaScript na hora da criação do UDF (isto é, a criação do UDF é bem sucedida independentemente de o código ser válido). Se o código não for válido, os erros serão devolvidos quando a UDF for chamada no momento da consulta.

Python

• Na cláusula HANDLER, o nome da função do manipulador diferencia maiúsculas e minúsculas.

• Na cláusula IMPORTS:

  • O(s) nome(s) dos arquivos diferencia(m) maiúsculas e minúsculas.

  • O(s) nome(s) do(s) estágio(s) não diferencia(m) maiúsculas e minúsculas.

• Você pode usar a cláusula PACKAGES para especificar o nome do pacote e o número da versão para dependências, tais como as do Snowpark. Para outras dependências, especificar os arquivos de dependência com a cláusula IMPORTS.

• O Snowflake valida isso:

  • A função ou classe especificada na instrução CREATE FUNCTION existe HANDLER.

  • Os tipos de entrada e saída especificados na instrução da UDF são compatíveis com os tipos de entrada e saída do manipulador.

SQL

• Atualmente, a cláusula NOT NULL não é aplicada para SQL UDFs.

Exemplos¶

Java

Aqui está um exemplo básico de CREATE FUNCTION com um manipulador em linha:

create or replace function echo_varchar(x varchar)
returns varchar
language java
called on null input
handler='TestFunc.echoVarchar'
target_path='@~/testfunc.jar'
as
'class TestFunc {
  public static String echoVarchar(String x) {
    return x;
  }
}';

Aqui está um exemplo básico de CREATE FUNCTION com uma referência a um manipulador em estágio:

create function my_decrement_udf(i numeric(9, 0))
    returns numeric
    language java
    imports = ('@~/my_decrement_udf_package_dir/my_decrement_udf_jar.jar')
    handler = 'my_decrement_udf_package.my_decrement_udf_class.my_decrement_udf_method'
    ;

Para obter mais exemplos de UDFs Java, consulte exemplos.

JavaScript

Criar um UDF JavaScript chamado js_factorial:

CREATE OR REPLACE FUNCTION js_factorial(d double)
  RETURNS double
  LANGUAGE JAVASCRIPT
  STRICT
  AS '
  if (D <= 0) {
    return 1;
  } else {
    var result = 1;
    for (var i = 2; i <= D; i++) {
      result = result * i;
    }
    return result;
  }
  ';

Python

O código no exemplo a seguir cria uma função py_udf cujo código do manipulador está em linha como udf.

create or replace function py_udf()
  returns variant
  language python
  runtime_version = '3.8'
  packages = ('numpy','pandas','xgboost==1.5.0')
  handler = 'udf'
as $$
import numpy as np
import pandas as pd
import xgboost as xgb
def udf():
    return [np.__version__, pd.__version__, xgb.__version__]
$$;

O código no exemplo a seguir cria uma função dream cujo manipulador está em um arquivo sleepy.py localizado no estágio @my_stage.

create or replace function dream(i int)
  returns variant
  language python
  runtime_version = '3.8'
  handler = 'sleepy.snore'
  imports = ('@my_stage/sleepy.py')

SQL

Criar um UDF escalar de SQL simples que retorna uma aproximação codificada da constante matemática pi:

CREATE FUNCTION pi_udf()
  RETURNS FLOAT
  AS '3.141592654::FLOAT'
  ;

Criar um simples UDF de tabela SQL que retorna valores codificados:

CREATE FUNCTION simple_table_function ()
  RETURNS TABLE (x INTEGER, y INTEGER)
  AS
  $$
    SELECT 1, 2
    UNION ALL
    SELECT 3, 4
  $$
  ;

SELECT * FROM TABLE(simple_table_function());

Saída:

SELECT * FROM TABLE(simple_table_function());
+---+---+
| X | Y |
|---+---|
| 1 | 2 |
| 3 | 4 |
+---+---+

Criar uma UDF que aceite múltiplos parâmetros:

CREATE FUNCTION multiply1 (a number, b number)
  RETURNS number
  COMMENT='multiply two numbers'
  AS 'a * b';

Criar um UDF de tabela SQL chamado get_countries_for_user que retorna os resultados de uma consulta:

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