CREATE FUNCTION (SQL e Python)

2025-06-07

Aplica-se a: assinalado com sim Databricks SQL Databricks Runtime

Cria uma função escalar ou de tabela SQL que usa um conjunto de argumentos e retorna um valor escalar ou um conjunto de linhas.

Aplica-se a: Assinalado - Sim Databricks SQL Databricks Runtime 13.3 LTS e superior

Cria uma função escalar Python que usa um conjunto de argumentos e retorna um valor escalar.

As UDFs Python exigem o Unity Catalog em armazéns SQL sem servidor ou armazéns SQL profissionais, ou um recurso computacional habilitado para Unity Catalog.

Aplica-se a: Sim Databricks SQL Sim Databricks Runtime 14.1 e superior

Além da invocação de parâmetros posicionais, também é possível invocar SQL e UDF Python usando a invocação de parâmetros nomeados.

Aplica-se a: sinal de verificação sim Databricks SQL Databricks Runtime 16.2 e superior

Use a ENVIRONMENT cláusula para especificar o ambiente Python para uma função declarada com LANGUAGE PYTHON. Usado para instalar dependências personalizadas e definir a versão do ambiente.

Sintaxe

CREATE [OR REPLACE] [TEMPORARY] FUNCTION [IF NOT EXISTS]
    function_name ( [ function_parameter [, ...] ] )
    { [ RETURNS data_type ] |
      RETURNS TABLE [ ( column_spec [, ...]) ] }
    [ characteristic [...] ]
    { AS dollar_quoted_string | RETURN { expression | query } }

function_parameter
    parameter_name data_type [DEFAULT default_expression] [COMMENT parameter_comment]

column_spec
    column_name data_type [COMMENT column_comment]

characteristic
  { LANGUAGE { SQL | PYTHON } |
    [NOT] DETERMINISTIC |
    COMMENT function_comment |
    [CONTAINS SQL | READS SQL DATA] |
    DEFAULT COLLATION default_collation_name } |
    environment }

environment
  ENVIRONMENT ( { environment_key = environment_value } [, ...] )

Parâmetros

OU SUBSTITUIR

Se for especificado, a função com o mesmo nome e assinatura (número de parâmetros e tipos de parâmetros) é substituída. Não é possível substituir uma função existente por uma assinatura ou um procedimento diferente. Isso é útil principalmente para atualizar o corpo da função e o tipo de retorno da função. Não é possível especificar esse parâmetro com IF NOT EXISTS.
TEMPORÁRIO

O escopo da função que está sendo criada. Quando você especifica TEMPORARY, a função criada é válida e visível na sessão atual. Nenhuma entrada persistente é feita no catálogo.
SE NÃO EXISTIR

Se especificado, cria a função somente quando ela não existe. A criação da função é bem-sucedida (nenhum erro é lançado) se a função especificada já existir no sistema. Não é possível especificar esse parâmetro com OR REPLACE.
function_name

Um nome para a função. Para uma função permanente, você pode, opcionalmente, qualificar o nome da função com um nome de esquema. Se o nome não for qualificado, a função permanente será criada no esquema atual.

O nome da função deve ser exclusivo para todas as rotinas (procedimentos e funções) no esquema.
function_parameter

Especifica um parâmetro da função.
- parameter_name
  
  O nome do parâmetro deve ser exclusivo dentro da função.
- tipo_de_dados
  
  Qualquer tipo de dados suportado. Para Python, data_type é convertido para um tipo de dados Python de acordo com este mapeamento de linguagem.
  
  Para um STRINGdata_type, o agrupamento padrão é a função default_collation_name.
- PADRÃO default_expression
  
  Aplica-se a: Databricks SQL Databricks Runtime 10.4 LTS e superior
  
  Um padrão opcional a ser usado quando uma invocação de função não atribui um argumento ao parâmetro. default_expression deve poder ser convertível para data_type. A expressão não deve fazer referência a outro parâmetro ou conter uma subconsulta.
  
  Quando você especifica um padrão para um parâmetro, todos os parâmetros a seguir também devem ter um padrão.
  
  DEFAULT é suportado apenas para LANGUAGE SQL .
- COMENTÁRIO comentário
  
  Uma descrição opcional do parâmetro. comment deve ser um STRING literal.
RETORNA data_type

O tipo de dados de retorno da função escalar. Para UDFs do Python, os valores de retorno devem corresponder exatamente ao tipo de dados, conforme especificado em data_type. Caso contrário, para evitar conversões de tipo imprevistas, a função falhará.

Para SQL UDF, esta cláusula é opcional. O tipo de dados será derivado do corpo da função se não for fornecido.
RETORNA TABLE [ (column_spec [,...] ) ]

Esta cláusula marca a função como uma função de tabela. Opcionalmente, também especifica a assinatura do resultado da função de tabela. Se nenhum column_spec for especificado, ele será derivado do corpo do SQL UDF.

RETURNS TABLE é suportado apenas para LANGUAGE SQL .
- nome_da_coluna
  
  O nome da coluna deve ser único dentro da assinatura.
- tipo_de_dados
  
  Qualquer tipo de dados suportado.
- COMENTÁRIO column_comment
  
  Uma descrição opcional da coluna. comment deve ser um STRING literal.
RETURN { expressão | consulta }

O corpo da função. Para uma função escalar, pode ser uma consulta ou uma expressão. Para uma função de tabela, só pode ser uma consulta. A expressão não pode conter:
- Funções agregadas
- Funções do Windows
- Funções de classificação
- Funções que geram linhas, como explode
Dentro do corpo da função, você pode se referir ao parâmetro por seu nome não qualificado ou qualificando o parâmetro com o nome da função.
DEFINIÇÃO DO DÓLAR

dollar_quoted_definition é a função body Python delimitada por dois correspondentes $[tag]$body$[tag]$. tag pode ser uma cadeia de caracteres vazia.

Exemplos:
```
$$
  return “Hello world”
$$

$py$
  return "Hello World"
$py$
```
característica

Todas as cláusulas características são opcionais. Você pode especificar qualquer número deles em qualquer ordem, mas pode especificar cada cláusula apenas uma vez.
- LINGUAGEM SQL ou LINGUAGEM PYTHON
  
  A linguagem da implementação da função.
- [NÃO] DETERMINÍSTICO
  
  Se a função é determinística. Uma função é determinística quando retorna apenas um resultado para um determinado conjunto de argumentos. Você pode marcar uma função como DETERMINISTIC quando o corpo da função não o é e vice-versa. Uma razão para isso pode ser incentivar ou desencorajar otimizações de consulta, como simplificação constante ou cache de consulta. Se não especificares esta opção, ela é derivada do corpo da função.
- COMENTÁRIO function_comment
  
  Um comentário para a funcionalidade. function_comment deve ser literal String.
- CONTÉM SQL ou LÊ DADOS SQL
  
  Se uma função lê dados direta ou indiretamente de uma tabela ou exibição. Quando a função lê dados SQL, você não pode especificar CONTAINS SQL. Caso não especifique nenhuma das cláusulas, a propriedade será derivada do corpo da função.
- COTEJAMENTO PADRÃO default_collation_name
  
  Aplica-se a: Databricks Runtime 17.0 e posteriores
  
  Define o agrupamento padrão a ser usado para:
  - STRING parâmetros e RETURNS tipo de dados e RETURNS TABLE campos da função.
  - DEFAULT expressão.
  - O corpo da função SQL.
  Se não for especificado, o agrupamento padrão será UTF8_BINARY.
Meio Ambiente

Especifica o ambiente Python para uma função declarada com LANGUAGE PYTHON. A ENVIRONMENT cláusula não é suportada para funções SQL.
- dependências
  
  Uma matriz JSON de cadeias de caracteres especificando os pacotes Python necessários ou arquivos de roda para a função. A dependencies chave não diferencia maiúsculas de minúsculas. Formatos suportados:
  - Pacote PyPI com versão opcional, por exemplo: "simplejson==3.19.3"
  - Caminho absoluto para um ficheiro wheel guardado num volume do Unity Catalog, por exemplo: "/Volumes/meu_catalogo/minha_esquema/meu_volume/pacotes/pacote_personalizado-1.0.0.whl"
  - URL HTTPS para um arquivo de roda no armazenamento externo, por exemplo: "https://my-bucket.s3.amazonaws.com/packages/special_package-2.0.0.whl?Expires=..."
- environment_version
  
  Um String especificando a versão do ambiente Python. Use None para usar o ambiente Python padrão. Se omitido, o ambiente padrão é usado.
  - Atualmente, apenas o valor None é suportado.

Bibliotecas suportadas em UDFs Python

Para usar quaisquer dependências, use import <package> dentro do corpo da função. Por exemplo, consulte o seguinte:

CREATE FUNCTION […]
AS $$
   import json
   [... (rest of function definition)]
$$

Por padrão, as dependências são limitadas à biblioteca Python padrão e às seguintes bibliotecas:

Pacote	Versão
lixívia	4.0.0
Chardet	4.0.0
Normalizador de Charset	2.0.4
DeUsedXML	0.7.1
googleapis-comuns-protos	1.56.4
Grpcio	1.47.0
grpcio-status	1.47.0
JmesPath	0.10.0
Joblib	1.1.0
dormência	1.20.3
embalagem	21,3
pandas	1.3.4
bode expiatório	0.5.2
Protobuf	4.21.5
Pyarrow	7.0.0
Pyparsing	3.0.9
python-dateutil (uma biblioteca de software para manipulação de datas em Python)	2.8.2
Pytz	2021.3
scikit-learn (biblioteca de aprendizado de máquina em Python)	0.24.2”
SciPy	1.7.1”
Ferramentas de configuração	65.2.0
seis	1.16.0
ThreadPoolCtl	3.1.0
WebEncodings	0.5.1
agentes de usuário	2.2.0
criptografia	38.0.4

Dependências personalizadas em UDFs Python

Para usar dependências adicionais além da biblioteca padrão e dos pacotes internos suportados, especifique-as ENVIRONMENT na cláusula.

Exemplos

Criar e usar uma função escalar SQL
Criar e usar uma função que usa DEFAULTs
Criar uma função de tabela SQL
Substituir uma função SQL
Descrever uma função SQL
Criar funções Python
Definir dependências personalizadas em funções Python

Criar e usar uma função escalar SQL

> CREATE VIEW t(c1, c2) AS VALUES (0, 1), (1, 2);

-- Create a temporary function with no parameter.
> CREATE TEMPORARY FUNCTION hello() RETURNS STRING
    RETURN 'Hello World!';

> SELECT hello();
  Hello World!

-- Create a permanent function with parameters.
> CREATE FUNCTION area(x DOUBLE, y DOUBLE) RETURNS DOUBLE RETURN x * y;

-- Use a SQL function in the SELECT clause of a query.
> SELECT area(c1, c2) AS area FROM t;
 1.0
 1.0

-- Use a SQL function in the WHERE clause of a query.
> SELECT * FROM t WHERE area(c1, c2) > 0;
 1  2

-- Compose SQL functions.
> CREATE FUNCTION square(x DOUBLE) RETURNS DOUBLE RETURN area(x, x);

> SELECT c1, square(c1) AS square FROM t;
  0  0.0
  1  1.0

-- Create a non-deterministic function
> CREATE FUNCTION roll_dice()
    RETURNS INT
    NOT DETERMINISTIC
    CONTAINS SQL
    COMMENT 'Roll a single 6 sided die'
    RETURN (rand() * 6)::INT + 1;
-- Roll a single 6-sided die
> SELECT roll_dice();
 3

Criar e usar uma função que usa DEFAULTs

-- Extend the function to support variable number of sides and dice.
-- Use defaults to support a variable number of arguments
> DROP FUNCTION roll_dice;
> CREATE FUNCTION roll_dice(num_dice  INT DEFAULT 1 COMMENT 'number of dice to roll (Default: 1)',
                            num_sides INT DEFAULT 6 COMMENT 'number of sides per die (Default: 6)')
    RETURNS INT
    NOT DETERMINISTIC
    CONTAINS SQL
    COMMENT 'Roll a number of n-sided dice'
    RETURN aggregate(sequence(1, roll_dice.num_dice, 1),
                     0,
                     (acc, x) -> (rand() * roll_dice.num_sides)::int,
                     acc -> acc + roll_dice.num_dice);

-- Roll a single 6-sided die still works
> SELECT roll_dice();
 3

-- Roll 3 6-sided dice
> SELECT roll_dice(3);
 15

-- Roll 3 10-sided dice
> SELECT roll_dice(3, 10)
 21

-- Roll 3 10-sided dice using named parameter invocation
> SELECT roll_dice(10 => num_sides, num_dice => 3)
 17

-- Create a SQL function with a scalar subquery.
> CREATE VIEW scores(player, score) AS VALUES (0, 1), (0, 2), (1, 2), (1, 5);

> CREATE FUNCTION avg_score(p INT) RETURNS FLOAT
    COMMENT 'get an average score of the player'
    RETURN SELECT AVG(score) FROM scores WHERE player = p;

> SELECT c1, avg_score(c1) FROM t;
 0  1.5
 1  3.5

Criar uma função de tabela SQL

-- Produce all weekdays between two dates
> CREATE FUNCTION weekdays(start DATE, end DATE)
    RETURNS TABLE(day_of_week STRING, day DATE)
    RETURN SELECT extract(DAYOFWEEK_ISO FROM day), day
             FROM (SELECT sequence(weekdays.start, weekdays.end)) AS T(days)
                  LATERAL VIEW explode(days) AS day
             WHERE extract(DAYOFWEEK_ISO FROM day) BETWEEN 1 AND 5;

-- Return all weekdays
> SELECT weekdays.day_of_week, day
    FROM weekdays(DATE'2022-01-01', DATE'2022-01-14');
  1     2022-01-03
  2     2022-01-04
  3     2022-01-05
  4     2022-01-06
  5     2022-01-07
  1     2022-01-10
  2     2022-01-11
  3     2022-01-12
  4     2022-01-13
  5     2022-01-14

-- Return weekdays for date ranges originating from a LATERAL correlation
> SELECT weekdays.*
    FROM VALUES (DATE'2020-01-01'),
                (DATE'2021-01-01'),
                (DATE'2022-01-01') AS starts(start),
         LATERAL weekdays(start, start + INTERVAL '7' DAYS);
  3     2020-01-01
  4     2020-01-02
  5     2020-01-03
  1     2020-01-06
  2     2020-01-07
  3     2020-01-08
  5     2021-01-01
  1     2021-01-04
  2     2021-01-05
  3     2021-01-06
  4     2021-01-07
  5     2021-01-08
  1     2022-01-03
  2     2022-01-04
  3     2022-01-05
  4     2022-01-06
  5     2022-01-07

Substituir uma função SQL

-- Replace a SQL scalar function.
> CREATE OR REPLACE FUNCTION square(x DOUBLE) RETURNS DOUBLE RETURN x * x;

-- Replace a SQL table function.
> CREATE OR REPLACE FUNCTION getemps(deptno INT)
    RETURNS TABLE (name STRING)
    RETURN SELECT name FROM employee e WHERE e.deptno = getemps.deptno;

-- Describe a SQL table function.
> DESCRIBE FUNCTION getemps;
 Function: default.getemps
 Type:     TABLE
 Input:    deptno INT
 Returns:  id   INT
           name STRING

Nota

Não é possível substituir uma função existente por uma assinatura diferente.

Descrever uma função SQL

> DESCRIBE FUNCTION hello;
 Function: hello
 Type:     SCALAR
 Input:    ()
 Returns:  STRING

> DESCRIBE FUNCTION area;
 Function: default.area
 Type:     SCALAR
 Input:    x DOUBLE
           y DOUBLE
 Returns:  DOUBLE

> DESCRIBE FUNCTION roll_dice;
 Function: default.roll_dice
 Type:     SCALAR
 Input:    num_dice  INT
           num_sides INT
 Returns:  INT

> DESCRIBE FUNCTION EXTENDED roll_dice;
 Function:      default.roll_dice
 Type:          SCALAR
 Input:         num_dice  INT DEFAULT 1 'number of dice to roll (Default: 1)'
                num_sides INT DEFAULT 6 'number of sides per dice (Default: 6)'
 Returns:       INT
 Comment:       Roll a number of m-sided dice
 Deterministic: false
 Data Access:   CONTAINS SQL
 Configs:       ...
 Owner:         the.house@always.wins
 Create Time:   Sat Feb 12 09:29:02 PST 2022
 Body:          aggregate(sequence(1, roll_dice.num_dice, 1),
                      0,
                      (acc, x) -> (rand() * roll_dice.num_sides)::int,
                      acc -> acc + roll_dice.num_dice)

Criar funções Python

—- Hello World-like functionality using Python UDFs
> CREATE FUNCTION main.default.greet(s STRING)
  RETURNS STRING
  LANGUAGE PYTHON
  AS $$
    def greet(name):
      return "Hello " + name + "!"

    return greet(s) if s else None
  $$

—- Can import functions from std library and environment
> CREATE FUNCTION main.default.isleapyear(year INT)
  RETURNS BOOLEAN
  LANGUAGE PYTHON
  AS $$
    import calendar
    return calendar.isleap(year) if year else None
  $$

—- Must return the correct type. Otherwise will fail at runtime.
> CREATE FUNCTION main.default.a_number()
  RETURNS INTEGER
  LANGUAGE PYTHON
  AS $$
    # does not work: return "10"
    # does not work: return 3.14
    return 10
  $$

—- Deal with exceptions.
> CREATE FUNCTION main.default.custom_divide(n1 INT, n2 INT)
  RETURNS FLOAT
  LANGUAGE PYTHON
  AS $$
    try:
      return n1/n2
    except ZeroDivisionException:
    # in case of 0, we can return NULL.
    return None
  $$

Definir dependências personalizadas em funções Python

-- Create a Python function with additional dependencies using the ENVIRONMENT clause.
> CREATE FUNCTION main.default.dump_json(data STRING)
    RETURNS STRING
    LANGUAGE PYTHON
    ENVIRONMENT (
      dependencies = '["simplejson==3.19.3", "/Volumes/my_catalog/my_schema/my_volume/packages/custom_package-1.0.0.whl", "https://my-bucket.s3.amazonaws.com/packages/special_package-2.0.0.whl?Expires=2043167927&Signature=abcd"]',
      environment_version = 'None'
    )
    AS $$
      import simplejson as json
      import custom_package
      return json.dumps(custom_package.process(data))
    $$;

-- Use the Python function in a query.
> SELECT dump_json('{"foo": "bar"}');

Partilhar via