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.

É possível capturar dados de registro e rastreamento à medida que o código do manipulador é executado. Para obter mais informações, consulte Visão geral do registro e do rastreamento.

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.

Como criar e chamar um procedimento

Depois de escrever o manipulador de um procedimento, você pode criá-lo e chamá-lo usando SQL.

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 de computação sem servidor para executar a tarefa.)

  • 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 do manipulador 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

  • Limite 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.

  • Escreva o código thread-safe.

    Certifique-se de que o método ou função do seu manipulador seja thread-safe.

  • Entenda 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.

  • Decida sobre o uso dos direitos do proprietário ou dos direitos do chamador.

    Ao planejar escrever seu procedimento armazenado, considere se deseja que o procedimento armazenado seja executado com direitos do chamador ou direitos do proprietário.

  • Lembre-se do comportamento de 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.

Como acessar dados em um procedimento Snowflake

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 Accessing and Setting the Session State.

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

Como ler um arquivo com um procedimento Scala

Você pode ler o conteúdo de um arquivo com o código do manipulador. O arquivo deve estar em um estágio do Snowflake que esteja disponível para seu manipulador. Por exemplo, você pode querer ler um arquivo para processar dados não estruturados no manipulador.

Para ler o conteúdo dos arquivos preparados, seu manipulador pode chamar métodos na classe SnowflakeFile ou na classe InputStream. Você pode fazer isso se precisar acessar o arquivo de forma dinâmica durante a computação Para obter mais informações, consulte Como ler um arquivo especificado dinamicamente com SnowflakeFile ou Como ler um arquivo especificado dinamicamente com InputStream neste tópico.

SnowflakeFile fornece recursos não disponíveis com InputStream, conforme descrito na tabela a seguir.

Classe

Entrada

Notas

SnowflakeFile

Formatos de URL:

  • URL com escopo para reduzir o risco de ataques de injeção de arquivo quando o chamador da função não for também seu proprietário.

  • URL do arquivo ou caminho da cadeia de caracteres para arquivos que o proprietário da UDF tenha acesso.

O arquivo deve estar localizado em um estágio interno ou em um estágio externo nomeado.

Acesso fácil a atributos adicionais do arquivo, como o tamanho do arquivo.

InputStream

Formatos de URL:

  • URL com escopo para reduzir o risco de ataques de injeção de arquivo quando o chamador da função não for também seu proprietário.

O arquivo deve estar localizado em um estágio interno ou em um estágio externo nomeado.

Nota

Para um procedimento armazenado de direitos do proprietário, o proprietário do procedimento deve ter acesso a todos os arquivos que não são URLs com escopo. Para os procedimentos de direitos do chamador, o chamador deve ter acesso a todos os arquivos que não são URLs com escopo. Em ambos os casos, você pode ler o arquivo preparado fazendo com que o código do manipulador chame o método SnowflakeFile.newInstance com um valor boolean para um novo parâmetro requireScopedUrl.

O exemplo a seguir usa SnowflakeFile.newInstance ao especificar que um URL com escopo não é necessário.

var filename = "@my_stage/filename.txt"
var sfFile = SnowflakeFile.newInstance(filename, false)
Copy

Como ler um arquivo especificado dinamicamente com SnowflakeFile

O código no exemplo a seguir tem uma função de manipulador execute que obtém um String e retorna um String com o conteúdo do arquivo. No tempo de execução, Snowflake inicializa a variável do manipulador fileName do caminho do arquivo de entrada na variável do procedimento input. O código do manipulador usa uma instância SnowflakeFile para ler o arquivo.

CREATE OR REPLACE PROCEDURE file_reader_scala_proc_snowflakefile(input VARCHAR)
RETURNS VARCHAR
LANGUAGE SCALA
RUNTIME_VERSION = 2.12
HANDLER = 'FileReader.execute'
PACKAGES=('com.snowflake:snowpark:latest')
AS $$
import java.io.InputStream
import java.nio.charset.StandardCharsets
import com.snowflake.snowpark_java.types.SnowflakeFile
import com.snowflake.snowpark_java.Session

object FileReader {
  def execute(session: Session, fileName: String): String = {
    var input: InputStream = SnowflakeFile.newInstance(fileName).getInputStream()
    return new String(input.readAllBytes(), StandardCharsets.UTF_8)
  }
}
$$;
Copy

O código no exemplo de CALL a seguir cria um URL de arquivo com escopo que aponta para o arquivo. Este é um URL codificado que permite o acesso temporário a um arquivo preparado sem conceder privilégios ao próprio estágio.

CALL file_reader_scala_proc_snowflakefile(BUILD_SCOPED_FILE_URL('@sales_data_stage', '/car_sales.json'));
Copy

Como ler um arquivo especificado dinamicamente com InputStream

O código no exemplo a seguir tem uma função de manipulador execute que obtém um InputStream e retorna um String com o conteúdo do arquivo. No tempo de execução, Snowflake inicializa a variável do manipulador stream do caminho do arquivo de entrada na variável do procedimento input. O código do manipulador usa o InputStream para ler o arquivo.

CREATE OR REPLACE PROCEDURE file_reader_scala_proc_input(input VARCHAR)
RETURNS VARCHAR
LANGUAGE SCALA
RUNTIME_VERSION = 2.12
HANDLER = 'FileReader.execute'
PACKAGES=('com.snowflake:snowpark:latest')
AS $$
import java.io.InputStream
import java.nio.charset.StandardCharsets
import com.snowflake.snowpark_java.Session

object FileReader {
  def execute(session: Session, stream: InputStream): String = {
    val contents = new String(stream.readAllBytes(), StandardCharsets.UTF_8)
    return contents
  }
}
$$;
Copy

O código no exemplo de CALL a seguir cria um URL de arquivo com escopo que aponta para o arquivo. Este é um URL codificado que permite o acesso temporário a um arquivo preparado sem conceder privilégios ao próprio estágio.

CALL file_reader_scala_proc_input(BUILD_SCOPED_FILE_URL('@sales_data_stage', '/car_sales.json'));
Copy

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(...), você não pode especificar GEOGRAPHY como um tipo de coluna. Isso se aplica se você estiver criando um procedimento armazenado ou anônimo.

CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
  RETURNS TABLE(g GEOGRAPHY)
  ...
Copy
WITH test_return_geography_table_1() AS PROCEDURE
  RETURNS TABLE(g GEOGRAPHY)
  ...
CALL test_return_geography_table_1();
Copy

Se você tentar especificar GEOGRAPHY como um tipo de coluna, chamar o procedimento armazenado resultará no erro:

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
WITH test_return_geography_table_1() AS PROCEDURE
  RETURNS TABLE()
  ...
CALL test_return_geography_table_1();
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  |
+----+-------+------+

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.

  1. Como compilar e empacotar seu código de manipulador

    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.

  2. Como carregar 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.