Retorno tabular com Scala em procedimentos armazenados criados com SQL¶

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 souber quais são as colunas retornadas ao definir o procedimento - por exemplo, quando elas são especificadas em tempo de execução, poderá deixar de fora os parâmetros TABLE. Quando você faz isso, as colunas do valor de retorno do procedimento são convertidas a partir das colunas do dataframe retornado por seu manipulador. Os tipos de dados de coluna são convertidos para 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 gera um erro no tempo de execução se 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 este problema, 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  |
+----+-------+------+