Écriture de procédures stockées en Scala

Vous pouvez écrire une procédure stockée en Scala. Vous pouvez utiliser la bibliothèque Snowpark dans votre procédure stockée pour effectuer des requêtes, des mises à jour et d’autres travaux sur les tables dans Snowflake.

Cette rubrique explique comment écrire une logique dans une procédure stockée. Une fois la logique établie, vous pouvez créer et appeler la procédure à l’aide de SQL. Pour plus d’informations, voir Création d’une procédure stockée et Appel d’une procédure stockée.

Dans ce chapitre :

Introduction

Vous pouvez construire et exécuter votre pipeline de données dans Snowflake, en utilisant un entrepôt Snowflake comme cadre de calcul. Pour le code de votre pipeline de données, vous utilisez l’API Snowpark pour Scala pour écrire des procédures stockées. Pour planifier l’exécution de ces procédures stockées, vous utilisez des tâches.

Vous pouvez capturer des données d’enregistrement et de trace pendant l’exécution du code de votre gestionnaire. Pour plus d’informations, reportez-vous à Vue d’ensemble de la journalisation et du traçage.

Note

Pour créer et appeler une procédure anonyme, utilisez CALL (avec procédure anonyme). La création et l’appel d’une procédure anonyme ne nécessitent pas un rôle avec des privilèges de schéma CREATE PROCEDURE.

Conditions préalables

Vous devez utiliser la version 1.1.0 ou une version plus récente de la bibliothèque Snowpark.

Si vous écrivez une procédure stockée dont le code du gestionnaire sera copié dans une zone de préparation, vous devez compiler vos classes pour qu’elles s’exécutent dans la version 11.x de Java.

Configuration de votre environnement de développement pour Snowpark

Configurez votre environnement de développement pour utiliser la bibliothèque Snowpark. Voir Configuration de votre environnement de développement pour Snowpark Scala.

Structuration et conception du code du gestionnaire

Vous pouvez conserver le code source du gestionnaire en ligne avec le code SQL qui crée la procédure ou conserver le résultat compilé du gestionnaire dans un emplacement distinct et y faire référence à partir du code SQL. Pour plus d’informations, voir Conserver le code du gestionnaire en ligne ou dans une zone de préparation.

Pour en savoir plus sur la conception du code source du gestionnaire à utiliser avec une procédure, voir Empaquetage du code de gestionnaire.

Création et appel d’une procédure

Une fois que vous avez écrit le gestionnaire d’une procédure, vous pouvez le créer et l’appeler à l’aide de SQL.

Limitations

Les procédures stockées de Snowpark ont les limitations suivantes :

  • La simultanéité n’est pas prise en charge. Par exemple, à partir de votre code, vous ne pouvez pas soumettre de requêtes à partir de plusieurs threads. Le code qui émet simultanément plusieurs requêtes produira une erreur.

  • Si vous exécutez votre procédure stockée à partir d’une tâche, vous devez spécifier un entrepôt lors de la création de la tâche. (Vous ne pouvez pas utiliser les ressources de calcul sans serveur pour exécuter la tâche.)

  • Gardez à l’esprit les limitations suivantes pour l’utilisation de certaines APIs de Snowpark dans votre procédure stockée.

    • Lorsque vous utilisez des APIs qui exécutent des commandes PUT et GET (y compris Session.sql("PUT ...") et Session.sql("GET ...")), vous ne pouvez écrire que dans le répertoire /tmp du système de fichiers à mémoire vive prévu pour la requête appelant la procédure.

    • Ne pas utiliser d”APIs pour les actions asynchrones.

    • N’utilisez pas d”APIs qui créent de nouvelles sessions (par exemple, Session.builder().configs(...).create()).

    • L’utilisation de session.jdbcConnection (et de la connexion qu’elle renvoie) n’est pas prise en charge, car elle peut entraîner un comportement dangereux.

  • La création d’objets temp nommés n’est pas prise en charge dans une procédure stockée relative aux droits du propriétaire. Une procédure stockée avec les droits du propriétaire est une procédure stockée qui s’exécute avec les privilèges du propriétaire de la procédure stockée. Pour plus d’informations, voir les droits de l’appelant ou les droits du propriétaire.

Écriture du code de gestionnaire pour la procédure stockée

Pour la logique de la procédure, écrivez un code de gestionnaire qui s’exécute lorsque la procédure est appelée. Cette section décrit la conception d’un gestionnaire.

Vous pouvez inclure ce code en ligne avec l’instruction SQL qui crée la procédure, ou copier le code dans une zone de préparation et y faire référence lorsque vous créez la procédure. Pour plus d’informations, voir Conserver le code du gestionnaire en ligne ou dans une zone de préparation.

Planification de l’écriture de votre procédure stockée

  • Limitez la quantité de mémoire consommée.

    Snowflake impose des limites à une méthode ou une fonction en matière de quantité de mémoire nécessaire. Pour plus d’informations sur la manière d’éviter une consommation excessive, voir Concevoir des gestionnaires qui respectent les contraintes imposées par Snowflake.

  • Écrivez du code en respectant le niveau de « thread safety »

    Veillez à ce que la méthode ou la fonction de votre gestionnaire respecte le niveau de « thread safety ».

  • Comprenez les restrictions de sécurité.

    Le code de votre gestionnaire s’exécute dans un moteur restreint. Veillez donc à respecter les règles décrites dans Pratiques de sécurité pour UDFs et procédures.

  • Décidez d’utiliser les droits du propriétaire ou les droits de l’appelant.

    Lorsque vous planifiez l’écriture de votre procédure stockée, déterminez si vous souhaitez que la procédure stockée s’exécute avec les droits de l’appelant ou les droits du propriétaire.

  • Gardez à l’esprit le comportement du délai d’attente pour les procédures stockées.

    L’exécution d’une procédure stockée se termine au bout d’un certain temps, sauf si le temporisateur est réinitialisé par l’activité du code. Plus précisément, le délai d’attente est réinitialisé par les interactions du code avec les données, notamment les opérations sur les fichiers, les requêtes et l’itération dans un jeu de résultats.

Écriture de la classe ou de l’objet

La méthode ou la fonction que vous définissez doit faire partie d’une classe ou d’un objet.

Lorsque vous écrivez la classe ou l’objet, notez ce qui suit :

  • La classe (ou l’objet) et la méthode ne doivent pas être protégés ou privés.

  • Si la méthode n’est pas statique et que vous souhaitez définir un constructeur, définissez un constructeur à zéro argument pour la classe. Snowflake appelle ce constructeur à zéro argument au moment de l’initialisation pour créer une instance de votre classe.

  • Vous pouvez définir différentes méthodes pour différentes procédures stockées dans la même classe ou le même objet.

Écriture de la méthode ou de la fonction

Lorsque vous écrivez la méthode ou la fonction pour la procédure stockée, notez ce qui suit :

  • Spécifiez l’objet Snowpark Session comme premier argument de votre méthode ou fonction.

    Lorsque vous appelez votre procédure stockée, Snowflake crée automatiquement un objet Session et le transmet à votre procédure stockée. (Vous ne pouvez pas créer l’objet Session vous-même).

  • Pour le reste des arguments et pour la valeur de retour, utilisez les types Scala qui correspondent aux types de données Snowflake.

  • Votre méthode ou fonction doit retourner une valeur. Pour les procédures stockées en Scala, une valeur de retour est requise.

  • L’exécution d’une procédure stockée se termine au bout d’un certain temps, sauf si le temporisateur est réinitialisé par l’activité du code. Plus précisément, le délai d’attente est réinitialisé par les interactions du code avec les données, notamment les opérations sur les fichiers, les requêtes et l’itération dans un jeu de résultats.

Mettre les dépendances à la disposition de votre code

Si le code de votre gestionnaire dépend d’un code défini en dehors du gestionnaire lui-même (comme les classes d’un fichier JAR) ou de fichiers de ressources, vous pouvez mettre ces dépendances à la disposition de votre code en les chargeant dans une zone de préparation. Lors de la création de la procédure, vous pouvez faire référence à ces dépendances en utilisant la clause IMPORTS.

Pour plus d’informations, voir Mettre les dépendances à la disposition de votre code.

Accès aux données dans une procédure Snowflake

Pour accéder aux données dans Snowflake, utilisez les APIs de bibliothèque Snowpark.

Lors du traitement d’un appel à votre procédure stockée Scala, Snowflake crée un objet Snowpark Session et transmet cet objet à la méthode ou à la fonction de votre procédure stockée.

Comme c’est le cas avec les procédures stockées dans d’autres langages, le contexte de la session (par exemple, les privilèges, la base de données et le schéma actuels, etc.) est déterminé par le fait que la procédure stockée s’exécute avec les droits de l’appelant ou les droits du propriétaire. Pour plus de détails, voir Accessing and Setting the Session State.

Vous pouvez utiliser cet objet Session pour appeler des APIs dans la bibliothèque Snowpark. Par exemple, vous pouvez créer un DataFrame pour une table ou exécuter une instruction SQL.

Voir le Guide du développeur Snowpark pour Scala pour plus d’informations.

Note

Pour plus d’informations sur les limitations, y compris celles concernant l’accès aux données, voir Limitations.

Exemple d’accès aux données

Voici un exemple de méthode Scala qui copie un nombre donné de lignes d’une table vers une autre table. La méthode accepte les arguments suivants :

  • Un objet Session Snowpark

  • Le nom de la table à partir de laquelle les lignes doivent être copiées

  • Le nom de la table dans laquelle les lignes doivent être enregistrées

  • Le nombre de lignes à copier

La méthode dans cet exemple renvoie une chaîne.

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

L’exemple suivant définit une fonction, plutôt qu’une méthode :

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

Lecture d’un fichier avec une procédure Scala

Vous pouvez lire le contenu d’un fichier avec le code du gestionnaire. Le fichier doit se trouver sur une zone de préparation Snowflake qui est disponible pour votre gestionnaire. Par exemple, vous pourriez vouloir lire un fichier pour traiter des données non structurées dans le gestionnaire.

Pour lire le contenu des fichiers en zone de préparation, votre gestionnaire peut appeler des méthodes de la classe SnowflakeFile ou de la classe InputStream. Vous pouvez le faire si vous avez besoin d’accéder au fichier de manière dynamique pendant le calcul. Pour plus d’informations, voir Lecture d’un fichier spécifié de façon dynamique avec SnowflakeFile ou Lecture d’un fichier spécifié de façon dynamique avec InputStream dans cette rubrique.

SnowflakeFile offre des fonctionnalités non disponibles avec InputStream, comme décrit dans le tableau suivant.

Classe

Entrée

Remarques

SnowflakeFile

Formats d’URL :

  • URL scopée qui réduit le risque d’attaques par injection de fichiers lorsque l’appelant de la fonction n’est pas également son propriétaire.

  • URL de fichier ou chemin de chaîne pour les fichiers auxquels le propriétaire de l’UDF a accès.

Le fichier doit être situé dans une zone de préparation interne ou externe nommée.

Accédez facilement à des attributs de fichier supplémentaires, tels que la taille du fichier.

InputStream

Formats d’URL :

  • URL scopée qui réduit le risque d’attaques par injection de fichiers lorsque l’appelant de la fonction n’est pas également son propriétaire.

Le fichier doit être situé dans une zone de préparation interne ou externe nommée.

Note

Pour une procédure stockée avec droits du propriétaire, le propriétaire de la procédure doit avoir accès à tous les fichiers qui ne sont pas des URLs scopées. Pour les procédures de droits de l’appelant, l’appelant doit avoir accès à tous les fichiers qui ne sont pas des URLs scopées. Dans les deux cas, vous pouvez lire le fichier en zone de préparation en demandant au code du gestionnaire d’appeler la méthode SnowflakeFile.newInstance avec une valeur boolean pour un nouveau paramètre requireScopedUrl.

L’exemple suivant utilise SnowflakeFile.newInstance tout en spécifiant qu’une URL scopée n’est pas nécessaire.

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

Lecture d’un fichier spécifié de façon dynamique avec SnowflakeFile

Le code de l’exemple suivant comporte une fonction de gestionnaire execute qui prend un String et renvoie un String avec le contenu du fichier. Au moment de l’exécution, Snowflake initialise la variable fileName du gestionnaire à partir du chemin du fichier entrant dans la variable input de la procédure. Le code du gestionnaire utilise une instance SnowflakeFile pour lire le fichier.

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

Le code de l’exemple CALL suivant crée une URL de fichier scopée qui pointe vers le fichier. Il s’agit d’une URL encodée qui permet un accès temporaire à un fichier en zone de préparation sans accorder de privilèges à la zone de préparation en soi.

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

Lecture d’un fichier spécifié de façon dynamique avec InputStream

Le code de l’exemple suivant comporte une fonction de gestionnaire execute qui prend un InputStream et renvoie un String avec le contenu du fichier. Au moment de l’exécution, Snowflake initialise la variable stream du gestionnaire à partir du chemin du fichier entrant dans la variable input de la procédure. Le code du gestionnaire utilise le InputStream pour lire le fichier.

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

Le code de l’exemple CALL suivant crée une URL de fichier scopée qui pointe vers le fichier. Il s’agit d’une URL encodée qui permet un accès temporaire à un fichier en zone de préparation sans accorder de privilèges à la zone de préparation en soi.

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

Renvoi de données tabulaires

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 renvoyé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 TABLE. Lorsque vous le faites, les colonnes de valeur de retour de la procédure seront converties à partir des colonnes du dataframe renvoyées par son gestionnaire. Les types de données de colonne seront 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érera une erreur lors de l’exécution si l’une des conditions suivantes est vraie :

  • 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 colonnes 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  |
+----+-------+------+

Préparation d’une procédure stockée avec un gestionnaire mis en zone de préparation

Si vous envisagez de créer une procédure stockée dont le gestionnaire sera compilé et copié dans une zone de préparation (plutôt que conservé en ligne en tant que source), vous devez compiler et empaqueter vos classes dans un fichier JAR, et vous devez télécharger le fichier JAR dans une zone de préparation.

  1. Compiler et empaqueter votre code de gestionnaire

    Pour faciliter la configuration de votre procédure stockée, créez un fichier JAR qui contient toutes les dépendances nécessaires à votre procédure stockée. Plus tard, vous devrez charger le fichier JAR vers une zone de préparation et faire pointer vers le fichier JAR à partir de votre instruction CREATE PROCEDURE. Ce processus est plus simple si vous avez moins de fichiers JAR à charger et vers lesquels pointer.

    • Utilisez sbt pour concevoir un fichier JAR avec des dépendances.

      Si vous utilisez SBT pour construire et empaqueter votre code, vous pouvez utiliser le plugin sbt-assembly pour créer un fichier JAR contenant toutes les dépendances. Pour plus d’informations, voir Empaquetage du code du gestionnaire Scala avec sbt.

    • Utilisation de Maven pour concevoir un fichier JAR avec des dépendances

      Si vous utilisez Maven pour construire et empaqueter votre code, vous pouvez utiliser le plugin Maven Assembly pour créer un fichier JAR contenant toutes les dépendances. Pour plus d’informations, voir Empaquetage du code du gestionnaire Java ou Scala avec Maven.

    • Utilisez d’autres outils pour concevoir un fichier JAR avec des dépendances.

      Si vous n’utilisez pas SBT ou Maven, consultez la documentation de votre outil de construction pour obtenir des instructions sur la construction d’un fichier JAR avec toutes les dépendances.

      Par exemple, si vous utilisez un projet IntelliJ IDEA (et non un projet SBT dans IntelliJ), consultez les instructions sur la mise en place d’une configuration d’artefact.

  2. Charger des fichiers vers une zone de préparation

    Pour que la logique de votre procédure (et les autres dépendances, le cas échéant) soit accessible à la procédure, vous devez charger les fichiers nécessaires dans une zone de préparation. Pour plus d’informations, voir Mettre les dépendances à la disposition de votre code.