Retourner le tabulaire avec Scala dans les procédures stockées créées avec SQL

Vous pouvez écrire une procédure qui renvoie des données sous forme de tableau. Pour écrire une procédure qui renvoie des données tabulaires, procédez comme suit :

  • Spécifiez TABLE(...) comme type de retour de la procédure dans votre instruction CREATE PROCEDURE.

    En tant que paramètres TABLE, vous pouvez spécifier les noms de colonne des données renvoyées et les types si vous les connaissez. Si vous ne connaissez pas les colonnes retournées lors de la définition de la procédure, par exemple lorsqu’elles sont spécifiées au moment de l’exécution, vous pouvez omettre les paramètres de TABLE. Lorsque vous le faites, les colonnes de la valeur de retour de la procédure sont converties à partir des colonnes de l’image de données renvoyée par son gestionnaire. Les types de données des colonnes sont convertis en SQL selon le mappage spécifié dans Mappages de type de données SQL-Scala.

  • Écrivez le gestionnaire afin qu’il renvoie le résultat tabulaire dans un dataframe Snowpark.

    Pour plus d’informations sur les dataframes, voir Utilisation de DataFrames dans Snowpark Scala.

Note

Une procédure génère une erreur dans l’environnement d’exécution si l’une des conditions suivantes est remplie :

  • Elle déclare TABLE comme type de retour, mais son gestionnaire ne renvoie pas de dataframe.

  • Son gestionnaire renvoie un dataframe, mais la procédure ne déclare pas TABLE comme type de retour.

Exemple

Les exemples de cette section illustrent le retour de valeurs tabulaires à partir d’une procédure qui filtre les lignes où une colonne correspond à une chaîne.

Définition des données

Le code de l’exemple suivant crée une table d’employés.

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

Déclaration d’une procédure pour filtrer les lignes

Le code des deux exemples suivants crée une procédure stockée qui prend le nom et le rôle de la table comme arguments, renvoyant les lignes de la table dont la valeur de colonne de rôle correspond au rôle spécifié comme argument.

Spécification des noms et des types de colonne de retour

Cet exemple spécifie les noms et les types de colonne dans l’instruction 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

Note

Actuellement, dans la clause RETURNS TABLE(...), vous ne pouvez pas spécifier GEOGRAPHY comme type de colonne. Ceci s’applique, que vous créiez une procédure stockée ou anonyme.

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

Si vous tentez de spécifier GEOGRAPHY comme type de colonne, l’appel de la procédure stockée entraîne une erreur :

Stored procedure execution error: data type of returned table does not match expected returned table type
Copy

Pour contourner ce problème, vous pouvez omettre les arguments et les types de colonne dans 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

Omission des noms et des types de colonne de retour

Le code dans l’exemple suivant déclare une procédure qui permet d’extrapoler les noms et les types de colonne de valeur de retour à partir des colonnes de la valeur de retour du gestionnaire. Il omet les noms et les types de colonne de l’instruction 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

Appel de la procédure

L’exemple suivant appelle la procédure stockée :

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

L’appel de procédure produit la sortie suivante :

+----+-------+------+
| ID | NAME  | ROLE |
+----+-------+------+
| 2  | Bob   | dev  |
| 3  | Cindy | dev  |
+----+-------+------+
Langage: Français