Utilisation du pilote JDBC

Ce chapitre fournit des informations sur l’utilisation du pilote JDBC.

Dans ce chapitre :

Extensions des API JDBC Snowflake

Le pilote Snowflake JDBC prend en charge des méthodes supplémentaires allant au-delà de la spécification standard JDBC. Cette section explique comment utiliser le désencapsulage pour accéder aux méthodes spécifiques à Snowflake, puis décrit trois des situations dans lesquelles vous devrez peut-être effectuer un désencapsulage :

Désencapsulage de classes spécifiques à Snowflake

Le pilote Snowflake JDBC prend en charge des méthodes spécifiques à Snowflake. Ces méthodes sont définies dans des interfaces de langage Java spécifiques à Snowflake, telles que SnowflakeConnection, SnowflakeStatement et SnowflakeResultSet. Par exemple, l’interface SnowflakeStatement contient une méthode getQueryID() qui ne figure pas dans l’interface d’instruction JDBC.

Lorsque le pilote Snowflake JDBC est invité à créer un objet JDBC (par exemple, créer un objet JDBC Statement en appelant une méthode createStatement() de l’objet Connection), le pilote JDBC Snowflake génère en réalité des objets spécifiques à Snowflake qui implémentent non seulement les méthodes de la norme JDBC, mais également les méthodes supplémentaires des interfaces Snowflake.

Pour accéder à ces méthodes Snowflake, vous « désencapsulez » un objet (tel qu’un objet Statement) afin d’exposer l’objet Snowflake et ses méthodes. Vous pouvez ensuite appeler les méthodes supplémentaires.

Le code suivant montre comment désencapsuler un objet JDBC Statement pour exposer les méthodes de l’interface SnowflakeStatement, puis appeler l’une de ces méthodes, dans ce cas setParameter :

Statement statement1;
...
// Unwrap the statement1 object to expose the SnowflakeStatement object, and call the
// SnowflakeStatement object's setParameter() method.
statement1.unwrap(SnowflakeStatement.class).setParameter(...);

Exécution d’une requête asynchrone

Le pilote Snowflake JDBC prend en charge les requêtes asynchrones (c’est-à-dire les requêtes qui renvoient le contrôle à l’utilisateur avant la fin de la requête). Les utilisateurs peuvent démarrer une requête, puis utiliser l’interrogation pour déterminer quand la requête est terminée. Une fois la requête terminée, l’utilisateur peut lire l’ensemble de résultats.

Cette fonctionnalité permet à un programme client d’exécuter plusieurs requêtes en parallèle sans que le programme client lui-même utilise le multithread.

Les requêtes asynchrones utilisent des méthodes ajoutées aux classes SnowflakeConnection, SnowflakeStatement, SnowflakePreparedStatement et SnowflakeResultSet.

Vous pouvez exécuter un mélange de requêtes synchrones et asynchrones dans la même session.

Meilleures pratiques pour les requêtes asynchrones

  • Assurez-vous de savoir quelles requêtes dépendent d’autres requêtes avant d’exécuter des requêtes en parallèle. Certaines requêtes sont interdépendantes et sensibles à l’ordre, et ne conviennent donc pas à la parallélisation. Par exemple, une instruction INSERT ne doit évidemment pas démarrer avant la fin de l’instruction CREATE TABLE correspondante.

  • Assurez-vous de ne pas exécuter trop de requêtes par rapport à la mémoire dont vous disposez. L’exécution de plusieurs requêtes en parallèle consomme généralement plus de mémoire, en particulier si plusieurs ResultSet sont stockés en mémoire en même temps.

  • Lors de l’interrogation, gérez les rares cas où une requête échoue. Par exemple, évitez la boucle infinie potentielle suivante :

    QueryStatus queryStatus = QueryStatus.RUNNING;
    while (queryStatus != QueryStatus.SUCCESS)  {     //  NOT RECOMMENDED
        Thread.sleep(2000);   // 2000 milliseconds.
        queryStatus = resultSet.unwrap(SnowflakeResultSet.class).getStatus();
        }
    

    Utilisez plutôt un code similaire au suivant :

    // Assume that the query isn't done yet.
    QueryStatus queryStatus = QueryStatus.RUNNING;
    while (queryStatus == QueryStatus.RUNNING || queryStatus == QueryStatus.RESUMING_WAREHOUSE)  {
        Thread.sleep(2000);   // 2000 milliseconds.
        queryStatus = resultSet.unwrap(SnowflakeResultSet.class).getStatus();
        }
    
    if (queryStatus == QueryStatus.SUCCESS) {
        ...
        }
    
  • Assurez-vous que les instructions de contrôle des transactions (BEGIN, COMMIT et ROLLBACK) ne sont pas exécutées en parallèle avec d’autres instructions.

Exemples de requêtes asynchrones

La plupart de ces exemples nécessitent que le programme importe des classes comme illustré ci-dessous :

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import net.snowflake.client.core.QueryStatus;
import net.snowflake.client.jdbc.SnowflakeConnection;
import net.snowflake.client.jdbc.SnowflakeResultSet;
import net.snowflake.client.jdbc.SnowflakeStatement;

Voici un exemple très simple :

    String sql_command = "";
    ResultSet resultSet;

    System.out.println("Create JDBC statement.");
    Statement statement = connection.createStatement();
    sql_command = "SELECT PI()";
    System.out.println("Simple SELECT query: " + sql_command);
    resultSet = statement.unwrap(SnowflakeStatement.class).executeAsyncQuery(sql_command);

    // Assume that the query isn't done yet.
    QueryStatus queryStatus = QueryStatus.RUNNING;
    while (queryStatus == QueryStatus.RUNNING || queryStatus == QueryStatus.RESUMING_WAREHOUSE) {
      Thread.sleep(2000); // 2000 milliseconds.
      queryStatus = resultSet.unwrap(SnowflakeResultSet.class).getStatus();
    }

    if (queryStatus == QueryStatus.FAILED_WITH_ERROR) {
      // Print the error code to stdout
      System.out.format("Error code: %d%n", queryStatus.getErrorCode());
      System.out.format("Error message: %s%n", queryStatus.getErrorMessage());
    } else if (queryStatus != QueryStatus.SUCCESS) {
      System.out.println("ERROR: unexpected QueryStatus: " + queryStatus);
    } else {
      boolean result_exists = resultSet.next();
      if (!result_exists) {
        System.out.println("ERROR: No rows returned.");
      } else {
        float pi_result = resultSet.getFloat(1);
        System.out.println("pi = " + pi_result);
      }
    }

Cet exemple stocke l’ID de requête, ferme la connexion, rouvre la connexion et utilise l’ID de requête pour récupérer les données :

    String sql_command = "";
    ResultSet resultSet;
    String queryID = "";

    System.out.println("Create JDBC statement.");
    Statement statement = connection.createStatement();
    sql_command = "SELECT PI() * 2";
    System.out.println("Simple SELECT query: " + sql_command);
    resultSet = statement.unwrap(SnowflakeStatement.class).executeAsyncQuery(sql_command);
    queryID = resultSet.unwrap(SnowflakeResultSet.class).getQueryID();
    System.out.println("INFO: Closing statement.");
    statement.close();
    System.out.println("INFO: Closing connection.");
    connection.close();

    System.out.println("INFO: Re-opening connection.");
    connection = create_connection(args);
    use_warehouse_db_and_schema(connection);
    resultSet = connection.unwrap(SnowflakeConnection.class).createResultSet(queryID);

    // Assume that the query isn't done yet.
    QueryStatus queryStatus = QueryStatus.RUNNING;
    while (queryStatus == QueryStatus.RUNNING) {
      Thread.sleep(2000); // 2000 milliseconds.
      queryStatus = resultSet.unwrap(SnowflakeResultSet.class).getStatus();
    }

    if (queryStatus == QueryStatus.FAILED_WITH_ERROR) {
      System.out.format(
          "ERROR %d: %s%n", queryStatus.getErrorMessage(), queryStatus.getErrorCode());
    } else if (queryStatus != QueryStatus.SUCCESS) {
      System.out.println("ERROR: unexpected QueryStatus: " + queryStatus);
    } else {
      boolean result_exists = resultSet.next();
      if (!result_exists) {
        System.out.println("ERROR: No rows returned.");
      } else {
        float pi_result = resultSet.getFloat(1);
        System.out.println("pi = " + pi_result);
      }
    }

Charger des fichiers de données directement depuis un flux vers une zone de préparation interne

Vous pouvez charger des fichiers de données à l’aide de la commande PUT. Cependant, il est parfois utile de transférer des données directement depuis un flux vers une zone de préparation interne (c.-à-d. Snowflake) en fichier. Voici la méthode exposée dans la classe SnowflakeConnection :

/**
 * Method to compress data from a stream and upload it at a stage location.
 * The data will be uploaded as one file. No splitting is done in this method.
 *
 * Caller is responsible for releasing the inputStream after the method is
 * called.
 *
 * @param stageName    stage name: e.g. ~ or table name or stage name
 * @param destPrefix   path / prefix under which the data should be uploaded on the stage
 * @param inputStream  input stream from which the data will be uploaded
 * @param destFileName destination file name to use
 * @param compressData compress data or not before uploading stream
 * @throws java.sql.SQLException failed to compress and put data from a stream at stage
 */
public void uploadStream(String stageName,
                         String destPrefix,
                         InputStream inputStream,
                         String destFileName,
                         boolean compressData)
    throws SQLException

Exemple d’utilisation :

Connection connection = DriverManager.getConnection(url, prop);
File file = new File("/tmp/test.csv");
FileInputStream fileInputStream = new FileInputStream(file);

// upload file stream to user stage
connection.unwrap(SnowflakeConnection.class).uploadStream("MYSTAGE", "testUploadStream",
   fileInputStream, "destFile.csv", true);

Le code écrit pour les versions de pilote JDBC antérieures à 3.9.2 peut convertir SnowflakeConnectionV1 plutôt que désencapsuler SnowflakeConnection.class. Par exemple :

...

// For versions prior to 3.9.2:
// upload file stream to user stage
((SnowflakeConnectionV1) connection.uploadStream("MYSTAGE", "testUploadStream",
   fileInputStream, "destFile.csv", true));

Note

Les clients utilisant des versions plus récentes du pilote doivent mettre à jour leur code pour utiliser unwrap.

Télécharger des fichiers de données directement depuis une zone de préparation interne vers un flux

Vous pouvez télécharger des fichiers de données à l’aide de la commande GET. Cependant, il est parfois utile de transférer des données directement depuis un fichier dans une zone de préparation interne (c.-à-d. Snowflake) vers un flux. Voici la méthode exposée dans la classe SnowflakeConnection :

/**
 * Download file from the given stage and return an input stream
 *
 * @param stageName      stage name
 * @param sourceFileName file path in stage
 * @param decompress     true if file compressed
 * @return an input stream
 * @throws SnowflakeSQLException if any SQL error occurs.
 */
InputStream downloadStream(String stageName,
                           String sourceFileName,
                           boolean decompress) throws SQLException;

Exemple d’utilisation :

Connection connection = DriverManager.getConnection(url, prop);
InputStream out = connection.unwrap(SnowflakeConnection.class).downloadStream(
    "~",
    DEST_PREFIX + "/" + TEST_DATA_FILE + ".gz",
    true);

Le code écrit pour les versions de pilote JDBC antérieures à 3.9.2 peut convertir SnowflakeConnectionV1 plutôt que désencapsuler SnowflakeConnection.class. Par exemple :

...

// For versions prior to 3.9.2:
// download file stream to user stage
((SnowflakeConnectionV1) connection.downloadStream(...));

Prise en charge des multi-instructions

Cette section explique comment exécuter plusieurs instructions en une seule requête à l’aide de Pilote JDBC.

Note

Par défaut, Snowflake renvoie une erreur pour les requêtes émises avec plusieurs instructions. Ce comportement est en partie fait pour offrir une protection contre les injections SQL. L’utilisation de plusieurs instructions ouvre la possibilité d’une injection SQL. Elle doit donc être utilisée avec précaution. Vous pouvez réduire le risque en utilisant la méthode setParameter() de la classe SnowflakeStatement pour spécifier le nombre d’instructions à exécuter, ce qui rend plus difficile l’injection d’une instruction en l’ajoutant. Pour plus de détails, voir Interface : SnowflakeStatement.

Envoi de plusieurs instructions et gestion des résultats

Les requêtes contenant plusieurs instructions peuvent être exécutées de la même manière que les requêtes avec une seule instruction, à la différence que la chaîne de requête contient plusieurs instructions séparées par des points-virgules.

Il existe deux façons d’autoriser plusieurs instructions :

  • Appelez Call Statement.setParameter(« MULTI_STATEMENT_COUNT », n) pour spécifier combien d’instructions à la fois cette instruction doit être autorisée à exécuter. Voir ci-dessous pour plus de détails.

  • Définissez le paramètre MULTI_STATEMENT_COUNT au niveau de la session ou au niveau du compte en exécutant une des commandes suivantes :

    alter session set MULTI_STATEMENT_COUNT = 0;
    

    Ou :

    alter account set MULTI_STATEMENT_COUNT = 0;
    

    La définition du paramètre sur 0 permet un nombre illimité d’instructions. La définition du paramètre sur 1 n’autorise qu’une seule instruction à la fois.

Afin de rendre les attaques par injection SQL plus difficiles, les utilisateurs peuvent appeler la méthode setParameter pour spécifier le nombre d’instructions à exécuter dans un seul appel, comme indiqué ci-dessous. Dans cet exemple, le nombre d’instructions à exécuter dans un seul appel est 3 :

// Specify the number of statements that we expect to execute.
statement.unwrap(SnowflakeStatement.class).setParameter(
        "MULTI_STATEMENT_COUNT", 3);

Le nombre d’instructions par défaut est 1 ; en d’autres termes, le mode multi-instructions est désactivé.

Pour exécuter plusieurs instructions sans spécifier le nombre exact, transmettez une valeur 0.

Le paramètre MULTI_STATEMENT_COUNT ne fait pas partie de la norme JDBC ; il s’agit d’une extension Snowflake. Ce paramètre affecte plusieurs pilotes/connecteurs Snowflake.

Lorsque plusieurs instructions sont exécutées dans un seul appel execute(), le résultat de la première instruction est disponible via les méthodes standard getResultSet() et getUpdateCount(). Pour accéder aux résultats des instructions suivantes, utilisez la méthode getMoreResults(). Cette méthode renvoie true lorsque le résultat suivant est un jeu de résultats et false lorsque le résultat suivant est un nombre de mises à jour (ou s’il n’y a plus de résultats).

L’exemple ci-dessous définit le paramètre MULTI_STATEMENT_COUNT, exécute les trois instructions et récupère le nombre de mises à jour et les jeux de résultats :

// Create a string that contains multiple SQL statements.
String command_string = "create table test(n int); " +
                        "insert into test values (1), (2); " +
                        "select * from test order by n";
Statement stmt = connection.createStatement();
// Specify the number of statements (3) that we expect to execute.
stmt.unwrap(SnowflakeStatement.class).setParameter(
        "MULTI_STATEMENT_COUNT", 3);

// Execute all of the statements.
stmt.execute(command_string);                       // false

// --- Get results. ---
// First statement (create table)
stmt.getUpdateCount();                              // -1 (DDL)

// Second statement (insert)
stmt.getMoreResults();                              // false
stmt.getUpdateCount();                              // 2

// Third statement (select)
stmt.getMoreResults();                              // true
ResultSet rs = stmt.getResultSet();
rs.next();                                          // true
rs.getInt(1);                                       // 1
rs.next();                                          // true
rs.getInt(1);                                       // 2
rs.next();                                          // false

// Past the last statement executed.
stmt.getMoreResults();                              // false
stmt.getUpdateCount();                              // -1 (no more results)

Snowflake recommande d’utiliser execute() pour les requêtes à instructions multiples. Les méthodes executeQuery() et executeUpdate() prennent également en charge plusieurs instructions, mais émettent une exception si le premier résultat n’est pas le type de résultat attendu (jeu de résultats et nombre de mises à jour, respectivement).

Échecs d’instructions

Si l’une des instructions SQL échoue lors de la compilation ou de l’exécution, l’exécution est interrompue. Toutes les instructions précédentes exécutées auparavant ne sont pas affectées.

Par exemple, si les instructions ci-dessous sont exécutées sous la forme d’une requête à plusieurs instructions, la requête échouera sur la troisième instruction et une exception sera levée.

CREATE OR REPLACE TABLE test(n int);
INSERT INTO TEST VALUES (1), (2);
INSERT INTO TEST VALUES ('not_an_int');  -- execution fails here
INSERT INTO TEST VALUES (3);

Si vous interrogiez ensuite le contenu de la table test, les valeurs 1 et 2 seraient présentes.

Fonctionnalités non prises en charge

Les instructions PUT et GET ne sont pas prises en charge pour les requêtes multi-instructions.

La préparation d’instructions et l’utilisation de variables de liaison ne sont également pas prises en charge pour les requêtes multi-instructions.

Lier des variables à des instructions

La liaison permet à une instruction SQL d’utiliser une valeur stockée dans une variable Java.

Liaison simple

Sans liaison, une instruction SQL spécifie des valeurs en insérant des littéraux à l’intérieur de l’instruction. Par exemple, l’instruction suivante utilise la valeur littérale 42 dans une instruction UPDATE :

stmt.execute("UPDATE table1 SET integer_column = 42 WHERE ID = 1000");

Avec la liaison, vous pouvez exécuter une instruction SQL qui utilise une valeur qui se trouve dans une variable. Par exemple :

int my_integer_variable = 42;
PreparedStatement pstmt = connection.prepareStatement("UPDATE table1 SET integer_colum = ? WHERE ID = 1000");
pstmt.setInt(1, my_integer_variable);
pstmt.executeUpdate();

Le ? à l’intérieur de la clause VALUES spécifie que l’instruction SQL utilise la valeur d’une variable. La méthode setInt() spécifie que le premier point d’interrogation de l’instruction SQL doit être remplacé par la valeur de la variable nommée my_integer_variable. Notez que setInt() utilise des valeurs basées sur 1, plutôt que sur 0 ; c’est-à-dire que le premier point d’interrogation est référencé par 1, et non par 0.

Lier des variables à des colonnes d’horodatage

Snowflake prend en charge trois variations différentes pour les horodatages : TIMESTAMP_LTZ , TIMESTAMP_NTZ , TIMESTAMP_TZ. Lorsque vous appelez PreparedStatement.setTimestamp pour lier une variable à une colonne d’horodatage, le pilote JDBC interprète la valeur de l’horodatage en fonction du fuseau horaire local (TIMESTAMP_LTZ) ou du fuseau horaire de l’objet Calendar passé en argument :

// The following call interprets the timestamp in terms of the local time zone.
insertStmt.setTimestamp(1, myTimestamp);
// The following call interprets the timestamp in terms of the time zone of the Calendar object.
insertStmt.setTimestamp(1, myTimestamp, Calendar.getInstance(TimeZone.getTimeZone("America/New_York")));

Si vous voulez que le pilote interprète l’horodatage en utilisant une variation différente (par exemple, TIMESTAMP_NTZ), utilisez l’une des approches suivantes :

  • Définissez le paramètre de session CLIENT_TIMESTAMP_TYPE_MAPPING à la variation.

    Notez que le paramètre affecte toutes les opérations de liaison pour la session en cours. Si vous devez modifier la variation (par exemple, revenir à TIMESTAMP_LTZ), vous devez à nouveau définir ce paramètre de session.

  • (Dans le pilote JDBC 3.13.3 et les versions ultérieures) Appelez la méthode PreparedStatement.setObject et utilisez le paramètre targetSqlType pour spécifier l’une des variations d’horodatage Snowflake suivantes :

    • SnowflakeUtil.EXTRA_TYPES_TIMESTAMP_LTZ

    • SnowflakeUtil.EXTRA_TYPES_TIMESTAMP_TZ

    • SnowflakeUtil.EXTRA_TYPES_TIMESTAMP_NTZ

    Par exemple :

    import net.snowflake.client.jdbc.SnowflakeUtil;
    ...
    insertStmt.setObject(1, myTimestamp, SnowflakeUtil.EXTRA_TYPES_TIMESTAMP_NTZ);
    

Insertions par lots

Dans le code de votre application Java, vous pouvez insérer plusieurs lignes dans un même lot en liant des paramètres dans une instruction INSERT et en appelant addBatch() et executeBatch().

À titre d’exemple, le code suivant insère deux lignes dans une table qui contient une colonne INTEGER et une colonne VARCHAR. L’exemple lie les valeurs aux paramètres dans l’instruction INSERT et appelle addBatch() et executeBatch() pour effectuer une insertion par lots.

Connection connection = DriverManager.getConnection(url, prop);
connection.setAutoCommit(false);

PreparedStatement pstmt = connection.prepareStatement("INSERT INTO t(c1, c2) VALUES(?, ?)");
pstmt.setInt(1, 101);
pstmt.setString(2, "test1");
pstmt.addBatch();

pstmt.setInt(1, 102);
pstmt.setString(2, "test2");
pstmt.addBatch();

int[] count = pstmt.executeBatch(); // After execution, count[0]=1, count[1]=1
connection.commit();

Lorsque vous utilisez cette technique pour insérer un grand nombre de valeurs, le pilote peut améliorer les performances en diffusant les données (sans créer de fichiers sur la machine locale) vers une zone de préparation temporaire pour l’acquisition. Le pilote le fait automatiquement lorsque le nombre de valeurs dépasse un seuil.

Pour que le pilote envoie les données vers une zone de préparation temporaire, l’utilisateur doit disposer du privilège suivant sur le schéma :

  • CREATE STAGE.

Si l’utilisateur ne dispose pas de ce privilège, le pilote revient à l’envoi des données avec la requête à la base de données Snowflake.

En outre, la base de données et le schéma actuels de la session doivent être définis. Si ceux-ci ne sont pas définis, la commande CREATE TEMPORARY STAGE exécutée par le pilote peut échouer avec l’erreur suivante :

CREATE TEMPORARY STAGE SYSTEM$BIND file_format=(type=csv field_optionally_enclosed_by='"')
Cannot perform CREATE STAGE. This session does not have a current schema. Call 'USE SCHEMA', or use a qualified name.

Note

Pour découvrir d’autres moyens de charger des données dans la base de données Snowflake (y compris le chargement en masse à l’aide de la commande COPY), consultez Chargement de données dans Snowflake.

Exemple de programme Java

Pour un exemple concret écrit dans Java, faites un clic droit sur le nom du fichier, SnowflakeJDBCExample.java, et enregistrez le lien/fichier sur votre système de fichiers local.

Dépannage

Erreur E/S : réinitialisation de la connexion

Dans certains cas, le pilote JDBC peut échouer avec le message d’erreur suivant après une période d’inactivité :

I/O error: Connection reset

Vous pouvez contourner le problème en fixant une « durée de vie » spécifique pour les connexions. Si une connexion reste inactive plus longtemps que la « durée de vie », le pilote JDBC retire la connexion du pool de connexion et en crée une nouvelle.

Pour fixer la durée de vie, définissez la propriété système Java nommée net.snowflake.jdbc.ttl sur le nombre de secondes que la connexion doit vivre :

  • Pour définir cette propriété de manière programmatique, appelez System.setProperty:

    // Set the "time to live" to 60 seconds.
    System.setProperty("net.snowflake.jdbc.ttl", "60")
    
  • Pour définir cette propriété lors de l’exécution de la commande java utilisez l’indicateur -D:

    # Set the "time to live" to 60 seconds.
    java -cp .:snowflake-jdbc-<version>.jar -Dnet.snowflake.jdbc.ttl=60 <ClassName>
    

La valeur par défaut de la propriété net.snowflake.jdbc.ttl est -1, ce qui signifie que les connexions inactives ne sont pas supprimées du pool de connexions.