CREATE FUNCTION (SQL e Python)

Artigo
04/18/2024

Aplica-se a: marca de seleção positiva Databricks SQL verificação marcada como sim Databricks Runtime

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

Aplica-se a: verificação marcada como sim Databricks SQL Databricks Runtime 13.3 LTS e superior

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

As UDFs do Python exigem o Catálogo do Unity em SQL warehouses sem servidor ou Pro ou um cluster do Catálogo do Unity compartilhado ou de usuário único.

Aplica-se a: verificação marcada como sim Databricks SQL Databricks Runtime 14.1 e superior

Além da invocação de parâmetro posicional, você também pode invocar a UDF do SQL e do Python usando a invocação de parâmetro nomeado.

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] }

Parâmetros

OR REPLACE

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

É 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.
IF NOT EXISTS

Se especificado, cria a função somente quando ela não existir. A criação da função terá êxito (nenhum erro será gerado) se a função especificada já existir no sistema. Não é possível especificar esse parâmetro com OR REPLACE.
function_name

Nome da função. Para uma função permanente, você pode 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.
function_parameter

Especifica um parâmetro da função.
- parameter_name
  
  O nome do parâmetro deve ser exclusivo na função.
- data_type
  
  Qualquer tipo de dados com suporte. Para Python, data_type é convertido em um tipo de dados Python de acordo com esse mapeamento de linguagem.
- DEFAULT default_expression
  
  Aplica-se a: Databricks SQL Databricks Runtime 10.4 LTS e versões superiores
  
  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 ser conversível em data_type. A expressão não deve fazer referência a outro parâmetro nem conter uma subconsulta.
  
  Quando você especifica um padrão para um parâmetro, todos os parâmetros seguintes também devem ter um padrão.
  
  DEFAULT é compatível apenas com LANGUAGE SQL .
- comentário COMMENT
  
  Uma descrição opcional do parâmetro. comment deve ser uma literal de STRING.
RETURNS data_type

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

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

Essa cláusula marca a função como uma função de tabela. Opcionalmente, ele também especifica a assinatura do resultado da função de tabela. Se nenhuma column_spec for especificada, ela será derivada do corpo da UDF do SQL.

RETURNS TABLE é compatível apenas com LANGUAGE SQL .
- column_name
  
  O nome da coluna deve ser exclusivo na assinatura.
- data_type
  
  Qualquer tipo de dados com suporte.
- COMMENT column_comment
  
  Uma descrição opcional da coluna. comment deve ser uma literal de STRING.
RETURN { expression | query }

O corpo da função. Para uma função escalar, pode ser uma consulta ou expressão. Para uma função de tabela, só pode ser uma consulta. A expressão não pode conter:
- Funções de agregação
- Funções da janela
- Funções de classificação
- Funções de produção de linha, como explode.
No corpo da função, você pode consultar o parâmetro pelo seu nome não qualificado ou qualificando-o com o nome da função.
AS dollar_quoted_definition

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

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

$py$
  return "Hello World"
$py$
```
characteristic

Todas as cláusulas de característica são opcionais. Você pode especificar qualquer número delas, em qualquer ordem, mas só pode especificar cada cláusula uma vez.
- LANGUAGE SQL ou LANGUAGE PYTHON
  
  A linguagem da implementação da função.
- [NOT] DETERMINISTIC
  
  Se a função é determinística. Uma função é determinística quando retorna apenas um resultado para determinado conjunto de argumentos. Você pode marcar uma função como DETERMINISTIC quando seu corpo não é e vice-versa. Um motivo para isso pode ser incentivar ou desencorajar otimizações de consulta, como dobramento constante ou cache de consulta. Se você não especificar a opção ths, ela será derivada do corpo da função.
- COMMENT function_comment
  
  Um comentário para a função. function_comment deve ser um literal de cadeia de caracteres.
- CONTAINS SQL ou READS SQL DATA
  
  Se uma função lê dados direta ou indiretamente de uma tabela ou exibição. Quando a função lê SQL dados, você não pode especificar CONTAINS SQL. Se você não especificar nenhuma cláusula, a propriedade será derivada do corpo da função.

Bibliotecas compatíveis em UDFs do Python

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

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

As dependências são limitadas à biblioteca padrão do Python e às seguintes bibliotecas:

Pacote	Versão
bleach	4.0.0
chardet	4.0.0
charset-normalizer	2.0.4
defusedxml	0.7.1
googleapis-common-protos	1.56.4
grpcio	1.47.0
grpcio-status	1.47.0
jmespath	0.10.0
joblib	1.1.0
numpy	1.20.3
empacotando	21.3
pandas	1.3.4
patsy	0.5.2
protobuf	4.21.5
pyarrow	7.0.0
pyparsing	3.0.9
python-dateutil	2.8.2
pytz	2021.3
scikit-learn	0.24.2”
scipy	1.7.1”
setuptools	65.2.0
six	1.16.0
threadpoolctl	3.1.0
webencodings	0.5.1
user-agents	2.2.0
criptografia	38.0.4

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;
 0.0
 2.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 padrões

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

Observação

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 do 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
  $$