Apresentando Funções Definidas pelo Usuário em SQL

Uma função definida pelo usuário (UDF) é um meio para um usuário estender as capacidades nativas do Apache Spark™ SQL. O SQL no Databricks suporta funções externas definidas pelo usuário escritas nas linguagens de programação Scala, Java, Python e R desde a versão 1.3.0. Embora as UDFs externas sejam muito poderosas, elas também vêm com algumas ressalvas:

• Segurança. Uma UDF escrita em uma linguagem externa pode executar código perigoso ou até malicioso. Isso exige controle rigoroso sobre quem pode criar UDFs.
• Desempenho. UDFs são caixas pretas para o Catalyst Optimizer. Como o Catalyst não está ciente do funcionamento interno de uma UDF, ele não pode fazer nada para melhorar o desempenho da UDF no contexto de uma consulta SQL.
• Usabilidade do SQL. Para um usuário de SQL, pode ser complicado escrever UDFs em uma linguagem hospedeira e depois registrá-las no Spark. Além disso, há um conjunto de extensões que muitos usuários podem querer fazer no SQL que são bastante simples, onde desenvolver uma UDF externa é um exagero.

Para lidar com as limitações acima, estamos entusiasmados em apresentar uma nova forma de UDF: SQL UDFs. Disponível no DBR 9.1 LTS, a SQL UDF é completamente definida com o poder expressivo do SQL e também completamente transparente para o Compilador SQL.

Confira o ebook Por que o Data Lakehouse é o Seu Próximo Data Warehouse para descobrir o funcionamento interno da Databricks Lakehouse Platform.

Benefícios de usar SQL UDFs

SQL UDFs são extensões simples, porém poderosas, para SQL no Databricks. Como funções, elas fornecem uma camada de abstração para simplificar a construção de consultas, tornando as consultas SQL mais legíveis e modulares. Ao contrário das UDFs escritas em uma linguagem não SQL, as SQL UDFs são mais leves para os usuários de SQL criarem. Os corpos das funções SQL são transparentes para o otimizador de consultas, tornando-as mais performáticas do que as UDFs externas. As SQL UDFs podem ser criadas como funções temporárias ou permanentes, reutilizadas em várias consultas, sessões e usuários, e ter o acesso controlado via Linguagem de Controle de Acesso (ACL). Neste blog, vamos apresentar alguns casos de uso chave de SQL UDFs com exemplos.

SQL UDFs como constantes

Vamos começar com a função mais simples imaginável: uma constante. Todos sabemos que não devemos usar literais em nosso código porque isso prejudica a legibilidade e, quem sabe, talvez a constante não permaneça constante afinal. Então, queremos poder alterá-la em apenas um lugar:

Se você está familiarizado com UDFs externas, pode ver que há algumas diferenças que se destacam:

1. Uma SQL UDF deve definir sua lista de parâmetros, mesmo que esteja vazia. Uma constante não recebe parâmetros.
2. A função também declara o tipo de dado que retornará. Neste caso, é uma STRING.
3. A implementação da função faz parte da definição da função.
4. Você especifica LANGUAGE SQL para indicar que é uma SQL UDF. Mas, na verdade, isso não é necessário. A cláusula RETURN é suficiente para indicar isso, então decidimos torná-la opcional.

Além dessas diferenças, há muitas outras coisas que são iguais às UDFs externas:

• Você pode substituir uma função. Mais sobre isso depois.
• Você pode adicionar um comentário que descreve a função – como mostrado acima.
• Você pode até criar uma função temporária que pode ser usada apenas dentro da sessão atual.

Vamos usar a função:

Sem surpresa, isso funciona. Mas o que está acontecendo nos bastidores?

Isso é ótimo! O compilador SQL substituiu a invocação da função pela própria constante.
Isso significa que, pelo menos, esta SQL UDF não tem custo de desempenho.

Agora, vamos dar uma olhada em outro padrão de uso comum.

SQL UDF encapsulando expressões

Imagine que você não gosta da nomenclatura de algumas funções internas. Talvez você esteja migrando muitas consultas de outro produto, que tem nomes e comportamentos de função diferentes. Ou talvez você simplesmente não suporte mais copiar e colar algumas expressões longas repetidamente em suas consultas SQL. Então, você quer corrigir isso.

Com SQL UDF, podemos simplesmente criar uma nova função com o nome que gostamos:

Vamos dar uma olhada na nova sintaxe usada aqui:

• Esta função recebe um argumento, e o parâmetro é definido por um nome, um tipo e um comentário opcional.
• A cláusula CONTAINS SQL é opcional, mas nos diz que a função não lê nem modifica nenhum dado em uma tabela. É a configuração padrão, então normalmente você não a especificaria.
• DETERMINISTIC também é opcional e nos diz que a função sempre retornará o mesmo conjunto de resultados dados os mesmos argumentos. A cláusula é apenas para documentação neste momento. Mas em algum momento no futuro, ela pode ser usada para bloquear funções não determinísticas em certos contextos.
• Na cláusula RETURN, o parâmetro foi referenciado pelo nome. Em cenários mais complexos abaixo, você verá que o parâmetro pode ser desambiguado com o nome da função. Naturalmente, você pode usar expressões arbitrariamente complexas como corpo da função.

Não só funciona…

...mas funciona bem:

Podemos ver que o plano físico mostra uma aplicação direta das funções lpad, hex, least e greatest. Este é o mesmo plano que você obtém ao invocar a série de funções diretamente.

Você também pode compor funções SQL a partir de funções SQL:

SQL UDF lendo de tabelas

Outro uso comum de SQL UDF é codificar pesquisas. Uma pesquisa simples pode ser decodificar códigos de cores RGB em nomes de cores em inglês:

Ok, mas existem muito mais do que duas cores neste mundo. E queremos essa tradução em ambos os sentidos, então elas deveriam estar em uma tabela de pesquisa:

Existem vários conceitos novos aplicados aqui:

• Você pode SUBSTITUIR uma SQL UDF. Para ter permissão para fazer isso, a nova função deve corresponder à assinatura da função antiga. A assinatura de uma função é definida pelo número de seus parâmetros e seus tipos.
• Esta função consulta informações em uma tabela, então você pode opcionalmente documentar isso usando READS SQL DATA. Se você não declarar nada, o Compilador SQL derivará o valor correto, mas você não deve mentir e declarar CONTAINS SQL.
• SQL SECURITY DEFINER é outra cláusula opcional, que afirma que a consulta que acessa a tabela de cores usará a autorização do proprietário da função. Assim, a função poderia ser executada pelo público sem comprometer a segurança da tabela.
• Assim como a função opera sob a autorização de seu proprietário, ela sempre será analisada usando o banco de dados atual no momento da criação.
• `rgb` é o nome da coluna em números. Ao qualificar o parâmetro como `from_rgb`.`rgb` você esclarece que está se referindo ao parâmetro, e não à coluna.

Como fica o plano físico agora? É fácil ver que usar uma UDF externa, que por si só executa uma consulta que resultaria em um join de loop aninhado, é uma maneira terrível de queimar recursos preciosos.

Neste caso, o Catalyst escolheu um broadcast hash join em vez de um nested loop join. Ele pode fazer isso porque entende o conteúdo da SQL UDF.

Até agora, todos os exemplos discutidos usaram funções escalares – aquelas que retornam um único valor. Esse resultado pode ser de qualquer tipo, até mesmo combinações complexas de structs, arrays e maps. Existe também outro tipo de UDF para discutir – a UDF de tabela.

SQL Table UDF

Imagine se as views aceitassem argumentos! Você poderia encapsular predicados complexos, mesmo que dependessem de valores fornecidos pelo usuário. Uma SQL Table UDF é exatamente isso: uma view por qualquer outro nome, exceto com parâmetros.

Vamos supor que o mapeamento de cores acima não seja único. No mínimo, podemos afirmar que os nomes das cores diferem entre os idiomas.

Portanto, a função `from_rgb` precisa ser modificada para retornar um array de nomes ou uma relação.

Como você pode ver, a única diferença em comparação com uma função escalar é uma cláusula RETURNS mais complexa. Diferente das views, as SQL UDFs exigem uma declaração da assinatura da relação retornada:

• TABLE especifica que a função retorna uma relação.
• A cláusula TABLE deve incluir um nome para cada coluna de retorno e o tipo de dado da coluna.
• Você pode opcionalmente especificar um comentário para qualquer coluna de retorno.

Funções de tabela definidas pelo usuário são novas no DBR. Vamos ver como invocá-las.

Em sua forma mais simples, uma função de tabela é invocada da mesma maneira e nos mesmos locais onde uma view é referenciada. A única diferença são os colchetes obrigatórios, que incluem os argumentos da função. Esta função é invocada com argumentos literais, mas os argumentos podem ser qualquer expressão, até mesmo subconsultas escalares.

O mais poderoso, no entanto, é o uso de SQL table UDF em um join, tipicamente um correlated cross join:

Aqui, os argumentos se referem (correlacionam) a uma relação anterior (lateral) na cláusula FROM. A nova palavra-chave LATERAL dá ao Catalyst permissão para resolver essas colunas. Observe também que você pode se referir ao resultado da função de tabela nomeando as colunas conforme definido na assinatura do resultado e opcionalmente qualificado pelo nome da função.

Administration

Naturalmente, as SQL UDFs são totalmente suportadas pelas instruções GRANT, REVOKE, SHOW, DESCRIBE e DROP existentes.

A instrução que vale a pena destacar com mais detalhes é DESCRIBE.

O describe básico retorna o que você poderia esperar, mas o DESCRIBE estendido adiciona detalhes significativamente maiores:

Outlook

O que descrevemos representa a funcionalidade inicial para SQL UDF. Extensões futuras que estamos considerando incluem suporte para:

• SQL PATH, para que você possa criar uma biblioteca de funções em um banco de dados e assiná-las de outro, assim como faria em seu sistema de arquivos.
• Sobrecarga de UDFs.
• UDFs com valores padrão para parâmetros.

SQL UDFs são um grande passo à frente na usabilidade do SQL e podem ser usadas de muitas maneiras diferentes, conforme descrito neste blog. Encorajamos você a pensar em maneiras ainda mais criativas de alavancar SQL UDFs, seja em Databricks SQL ou usando Photon para trabalhos de Data Engineering. Experimente o notebook aqui e veja a documentação para mais informações.

(Esta publicação no blog foi traduzida utilizando ferramentas baseadas em inteligência artificial) Publicação original