CREATE FUNCTION (SQL e Python)

Aplica-se a: 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: 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 pro SQL, ou um cluster Unity Catalog compartilhado ou de usuário único.

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

Além da invocação de parâmetros posicionais, você também pode invocar SQL e Python UDF usando a chamada 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

• OU SUBSTITUIR

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

• function_parameter

  Especifica um parâmetro da função.

  • parameter_name

    O nome do parâmetro deve ser exclusivo dentro da função.

  • data_type

    Qualquer tipo de dados suportado. Para Python, data_type é convertido para um tipo de dados Python de acordo com este mapeamento de linguagem.

  • 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 moldado a 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 .

  • COMENTAR comentário

    Uma descrição opcional do parâmetro. comment deve ser um STRING literal.

• DEVOLUÇÕES 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.

• TABELA DE DEVOLUÇÕES [ (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 .

  • column_name

    O nome da coluna deve ser exclusivo dentro da assinatura.

  • data_type

    Qualquer tipo de dados suportado.

  • COMENTAR column_comment

    Uma descrição opcional da coluna. comment deve ser um STRING literal.

• RETURN { consulta de expressão | }

  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 de produção de linha, como explodir

  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.

• COMO dollar_quoted_definition

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

    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 seu corpo não é e vice-versa. Uma razão 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 é derivada do corpo da função.

  • COMENTAR function_comment

    Um comentário para a função. 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. Se você não especificar nenhuma das cláusulas, a propriedade será derivada do corpo da função.

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)]
$$

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
numpy	1.20.3
embalagem	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
seis	1.16.0
ThreadPoolCtl	3.1.0
WebEncodings	0.5.1
agentes de usuário	2.2.0
criptografia	38.0.4

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

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

Artigos relacionados

• DROP FUNCTION
• SHOW FUNCTIONS
• DESCRIBE FUNCTION
• GRANT
• REVOKE