Écriture de procédures stockées en Scala¶

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.

• Création de la procédure stockée

  Pour plus d’informations sur la création d’une procédure stockée avec SQL, voir Création d’une procédure stockée.

  Pour obtenir des informations sur la création d’une procédure anonyme à l’aide de SQL, consultez CALL (avec procédure anonyme).

• Utilisation de votre procédure stockée

  Pour plus d’informations sur l’appel d’une procédure stockée depuis du code SQL, voir Appel d’une procédure stockée.

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.

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.

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

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

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

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

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

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

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.

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

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

CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
  RETURNS TABLE(g GEOGRAPHY)
  ...

WITH test_return_geography_table_1() AS PROCEDURE
  RETURNS TABLE(g GEOGRAPHY)
  ...
CALL test_return_geography_table_1();

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

CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
  RETURNS TABLE()
  ...

WITH test_return_geography_table_1() AS PROCEDURE
  RETURNS TABLE()
  ...
CALL test_return_geography_table_1();

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

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

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