MaxCompute:UDFs (funções definidas pelo usuário em SQL)

Conceitos principais

Pré-requisitos

Antes de começar, verifique se você:

• Dispõe de um ambiente em execução no modo de script SQL. Para mais detalhes, consulte SQL no modo de script.

• Confirmou que o MaxCompute oferece suporte aos tipos de dados dos parâmetros de entrada planejados. Para a lista completa, consulte Edição de tipos de dados do MaxCompute V2.0.

• Possui as permissões necessárias no nível de função na sua conta Alibaba Cloud. Para mais detalhes, consulte Permissões do MaxCompute.

Crie uma SQL UDF permanente

O sistema de metadados do MaxCompute armazena as SQL UDFs permanentes. Após a criação, elas aparecem na lista de funções e podem ser chamadas em qualquer fase.

Sintaxe

CREATE SQL FUNCTION <function_name>(@<parameter_in1> <datatype>[, @<parameter_in2> <datatype>...])
[RETURNS @<parameter_out> <datatype>]
AS [BEGIN]
<function_expression>
[END];

Parâmetros

Exemplos

Função simples: soma 1 a uma entrada BIGINT.

CREATE SQL FUNCTION my_add(@a BIGINT) AS @a + 1;

Função com múltiplas instruções usando BEGIN / END:

CREATE SQL FUNCTION my_sum(@a BIGINT, @b BIGINT, @c BIGINT) RETURNS @my_sum BIGINT
AS BEGIN
    @temp := @a + @b;
    @my_sum := @temp + @c;
END;

Crie uma SQL UDF temporária

O MaxCompute não armazena SQL UDFs temporárias. Elas existem apenas no script SQL onde foram definidas e não podem ser chamadas em outras sessões ou scripts.

Sintaxe

FUNCTION <function_name>(@<parameter_in1> <datatype>[, @<parameter_in2> <datatype>...])
[RETURNS @<parameter_out> <datatype>]
AS [BEGIN]
<function_expression>
[END];

Os parâmetros são idênticos aos de CREATE SQL FUNCTION. Omita CREATE SQL para criar uma UDF temporária.

Exemplo

FUNCTION my_add(@a BIGINT) AS @a + 1;

Consultar uma SQL UDF

Apenas SQL UDFs permanentes podem ser consultadas, pois UDFs temporárias não ficam armazenadas no MaxCompute.

Para executar DESC FUNCTION no cliente MaxCompute (odpscmd), atualize o cliente para a versão 0.34.0 ou posterior. Para instruções de instalação e atualização, consulte Cliente MaxCompute (odpscmd) .

Sintaxe

DESC FUNCTION <function_name>;

Exemplo

DESC FUNCTION my_add;

Saída:

Name                  my_add
Owner                 ALIYUN$s***_****@**.aliyunid.com
Created Time          2021-05-08 11:26:02
SQL Definition Text   CREATE SQL FUNCTION MY_ADD(@a BIGINT) AS @a + 1

Chamar uma SQL UDF

Chame uma SQL UDF da mesma forma que chama uma função integrada.

• É possível chamar SQL UDFs permanentes em qualquer fase.

• SQL UDFs temporárias só podem ser chamadas no script onde foram definidas.

Os tipos de dados dos argumentos passados devem corresponder aos tipos definidos na UDF.

Sintaxe

SELECT <function_name>(<column_name>[, ...]) FROM <table_name>;

Exemplo

-- Create a table and insert sample data.
CREATE TABLE src (c BIGINT, d STRING);
INSERT INTO TABLE src VALUES (1, '100.1'), (2, '100.2'), (3, '100.3');

-- Call my_add on column c.
SELECT my_add(c) FROM src;

Saída:

+------------+
| _c0        |
+------------+
| 2          |
| 3          |
| 4          |
+------------+

Excluir uma SQL UDF

Sintaxe

DROP FUNCTION <function_name>;

Exemplo

DROP FUNCTION my_add;

Passar uma função como parâmetro

As SQL UDFs aceitam parâmetros do tipo função. Ao declarar um parâmetro como FUNCTION (<input_type>) RETURNS <output_type>, o chamador pode passar qualquer função compatível: integrada, outra UDF (Java, Python ou SQL) ou anônima.

Exemplo

-- Define a SQL UDF.
FUNCTION add(@a BIGINT) AS @a + 1;

-- Define a higher-order UDF that accepts a function as an argument.
FUNCTION op(@a BIGINT, @fun FUNCTION (BIGINT) RETURNS BIGINT) AS @fun(@a);

-- Call op, passing different functions as the second argument.
-- add is a SQL UDF; abs is a MaxCompute built-in function.
SELECT op(key, add), op(key, abs) FROM VALUES (1), (2) AS t(key);

Saída:

+------------+------------+
| _c0        | _c1        |
+------------+------------+
| 2          | 1          |
| 3          | 2          |
+------------+------------+

_c0 é o resultado de add(key) (soma 1). _c1 é o resultado de abs(key) (valor absoluto). Para detalhes sobre a função ABS, consulte Funções matemáticas.

Para precauções sobre o uso de expressões Lambda no MaxCompute, consulte Funções Lambda .

Usar funções anônimas

Ao chamar uma UDF com um parâmetro do tipo função, passe uma função anônima inline em vez de uma função nomeada. O compilador infere os tipos dos parâmetros da função anônima com base na assinatura da UDF.

Exemplo

FUNCTION op(@a BIGINT, @fun FUNCTION (BIGINT) RETURNS BIGINT) AS @fun(@a);

-- Pass an anonymous function as the second argument.
SELECT op(key, FUNCTION (@a) AS @a + 1) FROM VALUES (1), (2) AS t(key);

FUNCTION (@a) AS @a + 1 é a função anônima. Seu tipo de parâmetro é inferido a partir da assinatura de op, portanto nenhuma declaração explícita de tipo é necessária.

Exemplo: simplificar lógica SQL repetitiva

Cenário: Converter strings de data de yyyy-mm-dd para yyyymmdd. As datas de entrada são 2020-11-21, 2020-1-01, 2019-5-1 e 19-12-1.

Com uma SQL UDF (recomendado)

Defina a lógica de conversão uma única vez e chame a função sempre que necessário:

CREATE SQL FUNCTION y_m_d2yyyymmdd(@y_m_d STRING) RETURNS @yyyymmdd STRING
AS BEGIN
    @yyyymmdd := CONCAT(
        LPAD(SPLIT_PART(@y_m_d, '-', 1), 4, '0'),
        LPAD(SPLIT_PART(@y_m_d, '-', 2), 2, '0'),
        LPAD(SPLIT_PART(@y_m_d, '-', 3), 2, '0')
    );
END;

SELECT y_m_d2yyyymmdd(d) FROM VALUES ('2020-11-21'), ('2020-1-01'), ('2019-5-1'), ('19-12-1') AS t(d);

Saída:

+------------+
| _c0        |
+------------+
| 20201121   |
| 20200101   |
| 20190501   |
| 00191201   |
+------------+

Sem uma SQL UDF

Insira a expressão completa inline em cada local de chamada. Essa abordagem dificulta a manutenção e aumenta a propensão a erros quando a lógica precisa ser alterada:

SELECT CONCAT(
    LPAD(SPLIT_PART(d, '-', 1), 4, '0'),
    LPAD(SPLIT_PART(d, '-', 2), 2, '0'),
    LPAD(SPLIT_PART(d, '-', 3), 2, '0')
) FROM VALUES ('2020-11-21'), ('2020-1-01'), ('2019-5-1'), ('19-12-1') AS t(d);

Limites

Próximos passos

• SQL no modo de script

• Edição de tipos de dados do MaxCompute V2.0

• Funções Lambda

• Funções matemáticas

• LIST FUNCTIONS