UDFs de SQL escalares

Este tópico cobre conceitos e detalhes de uso específicos para UDFs (funções definidas pelo usuário) de SQL.

Neste tópico:

Uso geral

Uma UDF de SQL avalia uma expressão arbitrária de SQL e retorna o(s) resultado(s) da expressão.

A definição da função pode ser uma expressão SQL que retorna um valor escalar (isso é, único) ou, se definida como uma função de tabela, um conjunto de linhas. Por exemplo, aqui está um exemplo básico de uma UDF escalar que calcula a área de um círculo:

CREATE FUNCTION area_of_circle(radius FLOAT)
  RETURNS FLOAT
  AS
  $$
    pi() * radius * radius
  $$
  ;
Copy
SELECT area_of_circle(1.0);
Copy

Saída:

SELECT area_of_circle(1.0);
+---------------------+
| AREA_OF_CIRCLE(1.0) |
|---------------------|
|         3.141592654 |
+---------------------+
Copy

A expressão pode ser uma expressão de consulta (uma expressão SELECT). Por exemplo:

CREATE FUNCTION profit()
  RETURNS NUMERIC(11, 2)
  AS
  $$
    SELECT SUM((retail_price - wholesale_price) * number_sold)
        FROM purchases
  $$
  ;
Copy

Ao utilizar uma expressão de consulta em uma UDF de SQL, não inclua um ponto-e-vírgula dentro do corpo da UDF para encerrar a expressão de consulta.

Você pode incluir apenas uma expressão de consulta. A expressão pode incluir UNION [ALL].

Nota

Embora o corpo de uma UDF possa conter uma instrução SELECT completa, ele não pode conter instruções DDL ou qualquer outra instrução DML além de SELECT.

Nota

Funções escalares (UDFs) têm um limite de 500 argumentos de entrada.

UDFs memoizáveis

Um UDF SQL escalar pode ser memoizável. Uma função memoizável armazena o resultado da chamada de um UDF SQL escalar e então retorna o resultado em cache quando a saída é necessária em um momento posterior. O benefício de utilizar uma função memoizável é melhorar o desempenho em consultas complexas, tais como consultas múltiplas de colunas em tabelas de mapeamento referenciadas dentro de uma política de acesso a linhas ou política de mascaramento.

Os proprietários de política (por exemplo, a função com o privilégio OWNERSHIP na política de acesso a linhas) podem atualizar suas condições de política para substituir as subconsultas que têm tabelas de mapeamento por uma função memoizável. Quando os usuários referenciam a coluna protegida pela política em uma consulta posterior, os resultados em cache da função memoizável ficarão disponíveis para uso conforme necessário.

Criação de uma função memoizável

Você pode definir um UDF SQL escalar a ser memoizável na instrução CREATE FUNCTION, especificando a palavra-chave MEMOIZABLE. As funções memoizáveis não incluem argumentos. Quando você escreve uma função memoizável:

  • Especifique BOOLEAN ou outros tipos de dados escalares como result_data_type.

    Tenha cuidado ao especificar ARRAY como result_data_type porque há limites para o tamanho do cache.

  • Não especifique outros tipos de dados, como OBJECT e VARIANT.

  • Não faça referência a outra função memoizável de forma alguma.

Chamada de uma função memoizável

Uma função memoizável pode ser chamada em uma instrução SELECT ou ser incluída em uma definição de política, que então chama a função memoizável com base nas condições da política.

Ao chamar uma função memoizável, observe:

  • Para UDFs SQL que retornam o tipo de dados ARRAY ou especificam um valor não escalar, use a função memoizável como um argumento na função ARRAY_CONTAINS.

  • Limite de tamanho de cache:

    Cada função memoizável tem um limite de 10 KB para a atual sessão de Snowflake.

    Se a função memoizável exceder este limite para o cache do conjunto de resultados, o Snowflake não armazena em cache o resultado da chamada da função memoizável. Em vez disso, o UDF age como um UDF normal escalar baseado na forma como a função é escrita.

  • Uso de cache:

    Funções memoizáveis têm um cache de resultados reutilizável para diferentes instruções SQL quando o ambiente e o contexto da consulta não muda. Geralmente, isto significa que o cache de resultados se aplica a diferentes instruções SQL, desde que:

    • A autorização de controle de acesso em objetos e colunas referenciadas em uma consulta permanece a mesma.

    • Os objetos referenciados na consulta não são modificados (por exemplo, por instruções DML).

    A coluna CHILD_QUERIES_WAIT_TIME na exibição do Account Usage QUERY_HISTORY registra o tempo (em milissegundos) para completar a pesquisa em cache ao chamar uma função memoizável.

  • Funções memoizáveis não reutilizam os resultados armazenados em cache quando:

    • A função faz referência a uma tabela ou outro objeto e há uma atualização da tabela referenciada.

    • Há uma mudança no controle de acesso à tabela.

    • A função chama função não determinística.

    • A função chama uma função externa ou um UDF que não é um UDF SQL.

Exemplos

Exemplo(s) básico(s) de UDFs de SQL escalar(es)

Este exemplo retorna uma aproximação codificada da constante matemática pi.

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

Saída:

SELECT pi_udf();
+-------------+
|    PI_UDF() |
|-------------|
| 3.141592654 |
+-------------+
Copy

Exemplos comuns de SQL

Expressão de consulta com instrução SELECT

Crie a tabela e os dados a serem usados:

CREATE TABLE purchases (number_sold INTEGER, wholesale_price NUMBER(7,2), retail_price NUMBER(7,2));
INSERT INTO purchases (number_sold, wholesale_price, retail_price) VALUES 
   (3,  10.00,  20.00),
   (5, 100.00, 200.00)
   ;
Copy

Crie a UDF:

CREATE FUNCTION profit()
  RETURNS NUMERIC(11, 2)
  AS
  $$
    SELECT SUM((retail_price - wholesale_price) * number_sold)
        FROM purchases
  $$
  ;
Copy

Chame a UDF em uma consulta:

SELECT profit();
Copy

Saída:

SELECT profit();
+----------+
| PROFIT() |
|----------|
|   530.00 |
+----------+
Copy

UDF em uma cláusula WITH

CREATE TABLE circles (diameter FLOAT);

INSERT INTO circles (diameter) VALUES
    (2.0),
    (4.0);

CREATE FUNCTION diameter_to_radius(f FLOAT) 
  RETURNS FLOAT
  AS 
  $$ f / 2 $$
  ;
Copy
WITH
    radii AS (SELECT diameter_to_radius(diameter) AS radius FROM circles)
  SELECT radius FROM radii
    ORDER BY radius
  ;
Copy

Saída:

+--------+
| RADIUS |
|--------|
|      1 |
|      2 |
+--------+
Copy

Operação JOIN

Este exemplo usa uma consulta mais complexa que inclui uma operação JOIN:

Crie a tabela e os dados a serem usados:

CREATE TABLE orders (product_ID varchar, quantity integer, price numeric(11, 2), buyer_info varchar);
CREATE TABLE inventory (product_ID varchar, quantity integer, price numeric(11, 2), vendor_info varchar);
INSERT INTO inventory (product_ID, quantity, price, vendor_info) VALUES 
  ('X24 Bicycle', 4, 1000.00, 'HelloVelo'),
  ('GreenStar Helmet', 8, 50.00, 'MellowVelo'),
  ('SoundFX', 5, 20.00, 'Annoying FX Corporation');
INSERT INTO orders (product_id, quantity, price, buyer_info) VALUES 
  ('X24 Bicycle', 1, 1500.00, 'Jennifer Juniper'),
  ('GreenStar Helmet', 1, 75.00, 'Donovan Liege'),
  ('GreenStar Helmet', 1, 75.00, 'Montgomery Python');
Copy

Crie a UDF:

CREATE FUNCTION store_profit()
  RETURNS NUMERIC(11, 2)
  AS
  $$
  SELECT SUM( (o.price - i.price) * o.quantity) 
    FROM orders AS o, inventory AS i 
    WHERE o.product_id = i.product_id
  $$
  ;
Copy

Chame a UDF em uma consulta:

SELECT store_profit();
Copy

Saída:

SELECT store_profit();
+----------------+
| STORE_PROFIT() |
|----------------|
|         550.00 |
+----------------+
Copy

O tópico CREATE FUNCTION contém exemplos adicionais.

Como usar UDFs em diferentes cláusulas

Uma UDF escalar pode ser usada em qualquer lugar onde uma expressão escalar possa ser usada. Por exemplo:

-- ----- These examples show a UDF called from different clauses ----- --

select MyFunc(column1) from table1;

select * from table1 where column2 > MyFunc(column1);
Copy

Usando variáveis de SQL em uma UDF

Este exemplo mostra como definir uma variável de SQL e usar essa variável dentro de uma UDF:

SET id_threshold = (SELECT COUNT(*)/2 FROM table1);
Copy
CREATE OR REPLACE FUNCTION my_filter_function()
RETURNS TABLE (id int)
AS
$$
SELECT id FROM table1 WHERE id > $id_threshold
$$
;
Copy
Linguagem: Português