Como escrever procedimentos armazenados em Scala¶
Você pode escrever um procedimento armazenado em Scala. Você pode usar a biblioteca do Snowpark dentro do seu procedimento armazenado para realizar consultas, atualizações e outros trabalhos em tabelas no Snowflake.
Este tópico explica como escrever uma lógica de procedimento armazenado. Uma vez que você tenha a lógica, você pode criar e chamar o procedimento usando SQL. Para obter mais informações, consulte Criação de um procedimento armazenado e Chamada de um procedimento armazenado.
Neste tópico:
Introdução¶
Você pode construir e executar seu pipeline de dados dentro do Snowflake usando um warehouse do Snowflake como estrutura computacional. Para o código do seu pipeline de dados, você usa a API do Snowpark para Scala para escrever procedimentos armazenados. Para programar a execução desses procedimentos armazenados, você usa tarefas.
Nota
Para criar e chamar um procedimento anônimo, use CALL (com procedimento anônimo). Criar e chamar um procedimento anônimo não exige uma função com privilégios de esquema CREATE PROCEDURE.
Pré-requisitos¶
Você deve usar a versão 1.1.0 ou uma versão mais recente da biblioteca do Snowpark.
Se você estiver escrevendo um procedimento armazenado cujo código do manipulador será copiado em um estágio você deverá compilar suas classes para executar na versão Java 11.x.
Configuração do ambiente de desenvolvimento para o Snowpark¶
Configure seu ambiente de desenvolvimento para usar a biblioteca do Snowpark. Consulte Configuração do seu ambiente de desenvolvimento para o Snowpark Scala.
Como estruturar e criar um código do manipulador¶
Você pode manter o código fonte do manipulador em linha com o SQL que cria o procedimento ou manter o resultado do manipulador compilado em um local separado e referenciá-lo a partir do SQL. Para obter mais informações, consulte Como manter o código do manipulador inline ou em um estágio.
Para saber mais sobre como criar o código-fonte do manipulador para uso com um procedimento, consulte Empacotamento do código do manipulador.
Limitações¶
Os Procedimentos Armazenados Snowpark têm as seguintes limitações:
Não há suporte para simultaneidade. Por exemplo, de dentro de seu código, você não pode enviar consultas a partir de múltiplos threads. O código que emite simultaneamente múltiplas consultas produzirá um erro.
Se você estiver executando seu procedimento armazenado a partir de uma tarefa, você deve especificar um warehouse ao criar a tarefa. (Você não pode usar recursos computacionais gerenciados pelo Snowflake para executar a tarefa).
A leitura e escrita de arquivos de código do manipulador de procedimentos armazenados em Scala ainda não é completamente suportada. A leitura e a escrita de arquivos pode resultar em comportamento instável. Isso inclui receber um
InputStream
como argumento e usar métodos disponíveis da classeFileOperation
(que você normalmente acessa através do métodoSession.file
), incluindoput
eget
.Considere as seguintes limitações para usar algumas APIs do Snowpark em seu procedimento armazenado.
Quando você usa APIs que executam os comandos PUT e GET (incluindo
Session.sql("PUT ...")
eSession.sql("GET ...")
), você pode escrever somente no diretório /tmp no sistema de arquivos com suporte de memória fornecido para a consulta que chama o procedimento.Não use APIs para ações assíncronas.
Não use APIs que criem novas sessões (por exemplo,
Session.builder().configs(...).create()
).O uso de
session.jdbcConnection
(e a conexão retornada a partir dele) não é aceito porque pode resultar em um comportamento que não é seguro.
A criação de objetos temporários nomeados não é suportada em um procedimento armazenado de direitos do proprietário. Um procedimento de armazenamento de direitos do proprietário é um procedimento armazenado que funciona com os privilégios do proprietário do procedimento armazenado. Para obter mais informações, consulte direitos do chamador ou direitos do proprietário.
Como escrever o código Scala para o procedimento armazenado¶
Para sua lógica de procedimento, você escreve o código do manipulador que executa quando o procedimento é chamado. Esta seção descreve o projeto de um manipulador.
Você pode incluir este código em linha com a instrução SQL que cria o procedimento, ou copiar o código em um estágio e referenciá-lo lá quando você criar o procedimento. Para obter mais informações, consulte Como manter o código do manipulador inline ou em um estágio.
Planejamento para escrever seu procedimento armazenado¶
Limitar a quantidade de memória consumida¶
O Snowflake impõe limites em um método em termos da quantidade de memória necessária. Para obter mais informações sobre como evitar o consumo excessivo, consulte Criação de manipuladores que ficam dentro das restrições impostas pelo Snowflake.
Escrever o código thread-safe¶
Certifique-se de que o método ou função do seu manipulador seja thread-safe.
Entender as restrições de segurança¶
O código de seu manipulador funciona dentro de um mecanismo restrito, portanto, certifique-se de seguir as regras descritas em Práticas de segurança para UDFs e procedimentos.
Decidir sobre o uso dos direitos do proprietário ou dos direitos do chamador¶
Além disso, ao planejar escrever seu procedimento armazenado, considere se você quer que o procedimento armazenado seja executado com direitos do chamador ou direitos do proprietário.
Considerar o comportamento do tempo limite para procedimentos armazenados¶
A execução do procedimento armazenado será interrompida, a menos que o temporizador seja reiniciado pela atividade do código. Em particular, o temporizador de tempo limite é redefinido pelas interações do código com os dados, incluindo operações de arquivo, consultas e iteração por meio de um conjunto de resultados.
Escrita da classe ou objeto¶
O método ou função que você define deve ser parte de uma classe ou objeto.
Ao escrever a classe ou objeto, observe o seguinte:
A classe (ou objeto) e o método não devem ser protegidos ou privados.
Se o método não for estático e você quiser definir um construtor, defina um construtor sem argumentos para a classe. O Snowflake ativa esse construtor sem argumentos no momento da inicialização para criar uma instância da sua classe.
Você pode definir diferentes métodos para diferentes procedimentos armazenados na mesma classe ou objeto.
Escrita do método ou função¶
Ao escrever o método ou função para o procedimento armazenado, observe o seguinte:
Especifique o objeto
Session
Snowpark como o primeiro argumento de seu método ou função.Quando você chama seu procedimento armazenado, o Snowflake cria automaticamente um objeto
Session
e o passa para seu procedimento armazenado. (Você, por si só, não pode criar o objetoSession
).Para o restante dos argumentos e para o valor de retorno, use os tipos Scala que correspondem aos tipos de dados Snowflake.
Seu método ou função deve retornar um valor. Para procedimentos armazenados em Scala, é necessário um valor de retorno.
A execução do procedimento armazenado será interrompida, a menos que o temporizador seja reiniciado pela atividade do código. Em particular, o temporizador de tempo limite é redefinido pelas interações do código com os dados, incluindo operações de arquivo, consultas e iteração por meio de um conjunto de resultados.
Disponibilização das dependências para seus códigos¶
Se seu código de manipulador depender do código definido fora do próprio manipulador (como classes em um arquivo JAR) ou de arquivos de recursos, você pode disponibilizar essas dependências para seu código, carregando-os em um estágio. Ao criar o procedimento, você pode referenciar estas dependências usando a cláusula IMPORTS.
Para obter mais informações, consulte Disponibilização das dependências para seus códigos.
Acesso aos dados no Snowflake a partir do seu procedimento armazenado¶
Para acessar dados no Snowflake, use as APIs da biblioteca do Snowpark.
Ao tratar uma chamada para seu procedimento armazenado em Scala, o Snowflake cria um objeto Session
Snowpark e passa o objeto para o método ou função do seu procedimento armazenado.
Como no caso de procedimentos armazenados em outras linguagens, o contexto da sessão (por exemplo, os privilégios, a base de dados e o esquema atual etc.) é determinado pela condição de o procedimento armazenado ser executado com os direitos do chamador ou com os direitos do proprietário. Para obter mais detalhes, consulte Acesso e definição do estado da sessão.
Você pode usar este objeto Session
para chamar APIs na Biblioteca do Snowpark. Por exemplo, você pode criar um DataFrame para uma tabela ou executar uma instrução SQL.
Consulte o Guia do Desenvolvedor do Snowpark para Scala para obter mais informações.
Nota
Para obter mais informações sobre limitações, incluindo limitações no acesso aos dados, consulte Limitações.
Exemplo de acesso aos dados¶
A seguir, um exemplo de um método Scala que copia um número especificado de linhas de uma tabela para outra tabela. O método utiliza os seguintes argumentos:
Um objeto
Session
do SnowparkO nome da tabela para copiar as linhas de
O nome da tabela para salvar as linhas para
O número de linhas a copiar
O método neste exemplo retorna uma cadeia de caracteres.
object MyObject
{
def myProcedure(session: com.snowflake.snowpark.Session, fromTable: String, toTable: String, count: Int): String =
{
session.table(fromTable).limit(count).write.saveAsTable(toTable)
return "Success"
}
}
O exemplo seguinte define uma função, em vez de um método:
object MyObject
{
val myProcedure = (session: com.snowflake.snowpark.Session, fromTable: String, toTable: String, count: Int): String =>
{
session.table(fromTable).limit(count).write.saveAsTable(toTable)
"Success"
}
}
Acesso a outras classes e arquivos de recursos¶
Se seu código depender de classes definidas fora do procedimento armazenado (por exemplo, classes em um arquivo JAR separado) ou arquivos de recursos, você pode disponibilizar essas dependências para o manipulador, carregando-as em um estágio. Para obter mais informações, consulte Disponibilização das dependências para seus códigos.
Depois, quando executar a instrução CREATE PROCEDURE, use a cláusula IMPORTS para apontar para esses arquivos.
Como preparar um procedimento armazenado com um manipulador em estágio¶
Se você planeja criar um procedimento armazenado cujo manipulador será compilado e copiado em um estágio (em vez de mantido em linha como a origem), você deve compilar e empacotar suas classes em um arquivo JAR, e você deve carregar o arquivo JAR em um estágio.
Compilação e empacotamento do seu código Scala¶
Para facilitar a configuração do seu procedimento armazenado, construa um arquivo JAR que contenha todas as dependências necessárias para seu procedimento armazenado. Depois, você precisará carregar o arquivo JAR para um estágio e apontar para o arquivo JAR de sua instrução CREATE PROCEDURE. Esse processo é mais simples se você tiver menos arquivos JAR para carregar e para os quais apontar.
Uso do SBT para construir um arquivo JAR com dependências¶
Se você estiver usando SBT para construir e empacotar seu código, você pode usar o plugin sbt-assembly para criar um arquivo JAR contendo todas as dependências. Para obter mais informações, consulte Empacotamento do código do manipulador Scala com SBT.
Uso do Maven para construir um arquivo JAR com dependências¶
Se você estiver usando Maven para construir e empacotar seu código, você pode usar o plugin Maven Assembly para criar um arquivo JAR que contenha todas as dependências. Para obter mais informações, consulte Empacotamento do código do manipulador Java ou Scala com Maven.
Uso de outras ferramentas para construir um arquivo JAR com dependências¶
Se você não estiver usando SBT ou Maven, consulte a documentação de sua ferramenta de construção para obter instruções sobre como construir um arquivo JAR com todas as dependências.
Por exemplo, se você estiver usando um projeto IntelliJ IDEA (não um projeto SBT em IntelliJ), consulte as instruções sobre como definir uma configuração de artefato.
Carregamento de arquivos para um estágio¶
Para disponibilizar a lógica de seu procedimento (e outras dependências, se houver) para o procedimento, você precisará carregar os arquivos necessários em um estágio. Para obter mais informações, consulte Disponibilização das dependências para seus códigos.
Criação do procedimento armazenado¶
Para obter mais informações sobre a criação de um procedimento armazenado com SQL, consulte Criação de um procedimento armazenado.
Retorno de dados tabulares¶
Você pode escrever um procedimento que retorne os dados em forma de tabela. Para escrever um procedimento que retorne dados tabulares, faça o seguinte:
Especifique
TABLE(...)
como tipo de retorno do procedimento em sua instrução CREATE PROCEDURE.Como parâmetros TABLE, você pode especificar os nomes das colunas e tipos de dados retornados se você os conhecer. Se você não conhecer as colunas retornadas ao definir o procedimento – como quando elas são especificadas em tempo de execução – você pode deixar de fora os parâmetros TABLE. Quando você fizer isso, as colunas de valor de retorno do procedimento serão convertidas a partir das colunas no dataframe retornado por seu manipulador. Os tipos de dados das colunas serão convertidos em SQL de acordo com o mapeamento especificado em Mapeamentos de tipos de dados SQL-Scala.
Escreva o manipulador para que ele retorne o resultado tabular em um dataframe do Snowpark.
Para obter mais informações sobre dataframes, consulte Como trabalhar com DataFrames no Snowpark Scala.
Nota
Um procedimento gerará um erro no tempo de execução se qualquer uma das seguintes opções for verdadeira:
Ele declara TABLE como seu tipo de retorno, mas seu manipulador não retorna um dataframe.
Seu manipulador retorna um dataframe, mas o procedimento não declara TABLE como seu tipo de retorno.
Exemplo¶
Os exemplos nesta seção ilustram o retorno de valores tabulares de um procedimento que filtra por linhas onde uma coluna corresponde a uma cadeia de caracteres.
Definição dos dados¶
O código no exemplo a seguir cria uma tabela de funcionários.
CREATE OR REPLACE TABLE employees(id NUMBER, name VARCHAR, role VARCHAR);
INSERT INTO employees (id, name, role) VALUES (1, 'Alice', 'op'), (2, 'Bob', 'dev'), (3, 'Cindy', 'dev');
Declaração de um procedimento para filtrar linhas¶
O código nos dois exemplos a seguir cria um procedimento armazenado que assume o nome da tabela e a função como argumentos, retornando as linhas da tabela cujo valor da coluna da função corresponde à função especificada como argumento.
Especificação de nomes e tipos de colunas de retorno¶
Este exemplo especifica os nomes e tipos de colunas na instrução RETURNS TABLE()
.
CREATE OR REPLACE PROCEDURE filter_by_role(table_name VARCHAR, role VARCHAR)
RETURNS TABLE(id NUMBER, name VARCHAR, role VARCHAR)
LANGUAGE SCALA
RUNTIME_VERSION = '2.12'
PACKAGES = ('com.snowflake:snowpark:latest')
HANDLER = 'Filter.filterByRole'
AS
$$
import com.snowflake.snowpark.functions._
import com.snowflake.snowpark._
object Filter {
def filterByRole(session: Session, tableName: String, role: String): DataFrame = {
val table = session.table(tableName)
val filteredRows = table.filter(col("role") === role)
return filteredRows
}
}
$$;
Nota
Atualmente, na cláusula RETURNS TABLE(...)
em CREATE PROCEDURE
, você não pode especificar GEOGRAPHY como um tipo de coluna.
CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
RETURNS TABLE(g GEOGRAPHY)
...
Se você fizer isso, chamar o procedimento armazenado resultará no erro:
CALL test_return_geography_table_1();
Stored procedure execution error: data type of returned table does not match expected returned table type
Para contornar isso, você pode omitir os argumentos da coluna e digitar RETURNS TABLE()
.
CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
RETURNS TABLE()
...
Omissão de nomes e tipos de colunas de retorno¶
O código no exemplo a seguir declara um procedimento que permite extrapolar nomes e tipos de colunas de valor de retorno a partir de colunas no valor de retorno do manipulador. Ele omite os nomes e tipos de coluna da instrução RETURNS TABLE()
.
CREATE OR REPLACE PROCEDURE filter_by_role(table_name VARCHAR, role VARCHAR)
RETURNS TABLE()
LANGUAGE SCALA
RUNTIME_VERSION = '2.12'
PACKAGES = ('com.snowflake:snowpark:latest')
HANDLER = 'Filter.filterByRole'
AS
$$
import com.snowflake.snowpark.functions._
import com.snowflake.snowpark._
object Filter {
def filterByRole(session: Session, tableName: String, role: String): DataFrame = {
val table = session.table(tableName)
val filteredRows = table.filter(col("role") === role)
return filteredRows
}
}
$$;
Como chamar o procedimento¶
O exemplo a seguir chama o procedimento armazenado:
CALL filter_by_role('employees', 'dev');
A chamada do procedimento produz os seguintes resultados:
+----+-------+------+
| ID | NAME | ROLE |
+----+-------+------+
| 2 | Bob | dev |
| 3 | Cindy | dev |
+----+-------+------+
Chamada do seu procedimento armazenado¶
Para obter mais informações sobre como chamar um procedimento armazenado a partir de SQL, consulte Chamada de um procedimento armazenado.