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 classe FileOperation (que você normalmente acessa através do método Session.file), incluindo put e get.

  • 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 ...") e Session.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 objeto Session).

  • 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 Snowpark

  • O 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"
  }
}
Copy

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"
  }
}
Copy

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');
Copy

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
    }
}
$$;
Copy

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)
...
Copy

Se você fizer isso, chamar o procedimento armazenado resultará no erro:

CALL test_return_geography_table_1();
Copy
Stored procedure execution error: data type of returned table does not match expected returned table type
Copy

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()
...
Copy
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
    }
}
$$;
Copy

Como chamar o procedimento

O exemplo a seguir chama o procedimento armazenado:

CALL filter_by_role('employees', 'dev');
Copy

A chamada do procedimento produz os seguintes resultados:

+----+-------+------+
| ID | NAME  | ROLE |
+----+-------+------+
| 2  | Bob   | dev  |
| 3  | Cindy | dev  |
+----+-------+------+
Copy

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.