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

Conditions préalables¶

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

Si vous écrivez une procédure stockée, 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 Java.

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.

Limites¶

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 gérées par Snowflake pour exécuter la tâche).

• La lecture et l’écriture de fichiers à partir du code du gestionnaire de procédures stockées en Java ne sont pas encore totalement prises en charge. La lecture et l’écriture de fichiers peuvent entraîner un comportement instable. Cela inclut la réception d’un InputStream en tant qu’argument et l’utilisation des méthodes disponibles dans la classe FileOperation (à laquelle vous accédez généralement par le biais de la méthode Session.file), notamment put et get.

• 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¶

La méthode que vous définissez doit faire partie d’une classe.

Lorsque vous écrivez la classe, notez ce qui suit :

• La classe et la méthode ne doivent pas être protégées ou privées.

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

Écriture de la méthode¶

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

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

  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 Java qui correspondent aux types de données Snowflake.

• Votre méthode doit renvoyer une valeur. Pour les procédures stockées en Java, 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.

Gestion des erreurs¶

Vous pouvez utiliser les techniques normales de gestion des exceptions de Java pour détecter les erreurs dans le code du gestionnaire.

Si une exception non détectée se produit à l’intérieur de la méthode, Snowflake génère une erreur qui inclut la trace de la pile pour l’exception. Lorsque l’enregistrement des exceptions non gérées est activé, Snowflake enregistre les données relatives aux exceptions non gérées dans une table d’événements.

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 Java 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.

import com.snowflake.snowpark_java.*;

public class MyClass
{
  public String myMethod(Session session, String fromTable, String toTable, int count)
  {
    session.table(fromTable).limit(count).write().saveAsTable(toTable);
    return "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_java_proc_snowflakefile(input VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVA
RUNTIME_VERSION = 11
HANDLER = 'FileReader.execute'
PACKAGES=('com.snowflake:snowpark:latest')
AS $$
import java.io.InputStream;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import com.snowflake.snowpark_java.types.SnowflakeFile;
import com.snowflake.snowpark_java.Session;

class FileReader {
  public String execute(Session session, String fileName) throws IOException {
    InputStream input = 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_java_proc_snowflakefile(BUILD_SCOPED_FILE_URL('@sales_data_stage', '/car_sales.json'));

String filename = "@my_stage/filename.txt";
String sfFile = SnowflakeFile.newInstance(filename, false);

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 l’argument input de la procédure. Le code du gestionnaire utilise le InputStream pour lire le fichier.

CREATE OR REPLACE PROCEDURE file_reader_java_proc_input(input VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVA
RUNTIME_VERSION = 11
HANDLER = 'FileReader.execute'
PACKAGES=('com.snowflake:snowpark:latest')
AS $$
import java.io.InputStream;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import com.snowflake.snowpark.Session;

class FileReader {
  public String execute(Session session, InputStream stream) throws IOException {
    String 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_java_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 JAVA
RUNTIME_VERSION = '11'
PACKAGES = ('com.snowflake:snowpark:latest')
HANDLER = 'Filter.filterByRole'
AS
$$
import com.snowflake.snowpark_java.*;

public class Filter {
  public DataFrame filterByRole(Session session, String tableName, String role) {
    DataFrame table = session.table(tableName);
    DataFrame filteredRows = table.filter(Functions.col("role").equal_to(Functions.lit(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 JAVA
RUNTIME_VERSION = '11'
PACKAGES = ('com.snowflake:snowpark:latest')
HANDLER = 'FilterClass.filterByRole'
AS
$$
import com.snowflake.snowpark_java.*;

public class FilterClass {
  public DataFrame filterByRole(Session session, String tableName, String role) {
    DataFrame table = session.table(tableName);
    DataFrame filteredRows = table.filter(Functions.col("role").equal_to(Functions.lit(role)));
    return filteredRows;
  }
}
$$;

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

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