Using the JDBC Driver

This topic provides information about how to use the JDBC driver.

In this Topic:

Snowflake JDBC API Extensions

The Snowflake JDBC driver supports additional methods beyond the standard JDBC specification. This section documents how to use unwrapping to access the Snowflake-specific methods, then describes three of the situations in which you might need to unwrap:

Unwrapping Snowflake-specific Classes

The Snowflake JDBC driver supports Snowflake-specific methods. These methods are defined in Snowflake-specific Java-language interfaces, such as SnowflakeConnection, SnowflakeStatement, and SnowflakeResultSet. For example, the SnowflakeStatement interface contains a getQueryID() method that is not in the JDBC Statement interface.

When the Snowflake JDBC driver is asked to create a JDBC object (e.g. create a JDBC Statement object by calling a Connection object’s createStatement() method), the Snowflake JDBC driver actually creates Snowflake-specific objects that implement not only the methods of the JDBC standard, but also the additional methods from the Snowflake interfaces.

To access these Snowflake methods, you “unwrap” an object (such as a Statement object) to expose the Snowflake object and its methods. You can then call the additional methods.

The following code shows how to unwrap a JDBC Statement object to expose the methods of the SnowflakeStatement interface, and then call one of those methods, in this case 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(...);

Performing an Asynchronous Query

The Snowflake JDBC Driver supports asynchronous queries (i.e. queries that return control to the user before the query completes). Users can start a query, then use polling to determine when the query has completed. After the query completes, the user can read the result set.

This feature allows a client program to run multiple queries in parallel without the client program itself using multi-threading.

Asynchronous queries use methods added to the SnowflakeConnection, SnowflakeStatement, SnowflakePreparedStatement, and SnowflakeResultSet classes.

You can run a mix of synchronous and asynchronous queries in the same session.

Best Practices For Asynchronous Queries

  • Ensure that you know which queries are dependent upon other queries before you run any queries in parallel. Some queries are interdependent and order sensitive, and therefore not suitable for parallelizing. For example, obviously an INSERT statement should not start until after the corresponding CREATE TABLE statement has finished.

  • Ensure that you do not run too many queries for the memory that you have available. Running multiple queries in parallel typically consumes more memory, especially if more than one ResultSet is stored in memory at the same time.

  • When polling, handle the rare cases where a query does not succeed. For example, avoid the following potential infinite loop:

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

    Instead, use code similar to the following:

    // 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) {
        ...
        }
    
  • Ensure that transaction control statements (BEGIN, COMMIT, and ROLLBACK) do not execute in parallel with other statements.

Examples of Asynchronous Queries

Most of these examples require that the program import classes as shown below:

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;
import veritas_classes.JDBC_veritas_base; // Used for testing. Customers should not and cannot use

// this.

This is a very simple example:

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

This example stores the query ID, closes the connection, re-opens the connection, and uses the query ID to retrieve the data:

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

Upload Data Files Directly from a Stream to an Internal Stage

You can upload data files using the PUT command. However, sometimes it make sense to transfer data directly from a stream to an internal (i.e. Snowflake) stage as a file. Here is the method exposed in the SnowflakeConnection class:

/**
 * 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

Sample usage:

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

Code written for JDBC Driver versions prior to 3.9.2 might cast SnowflakeConnectionV1 rather than unwrap SnowflakeConnection.class. For example:

...

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

Note

Customers using newer versions of the driver should update their code to use unwrap.

Download Data Files Directly from an Internal Stage to a Stream

You can download data files using the GET command. However, sometimes it make sense to transfer data directly from a file in an internal (i.e. Snowflake) stage to a stream. Here is the method exposed in the SnowflakeConnection class:

/**
 * 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;

Sample usage:

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

Code written for JDBC Driver versions prior to 3.9.2 might cast SnowflakeConnectionV1 rather than unwrap SnowflakeConnection.class. For example:

...

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

Multi-Statement Support

This section describes how to execute multiple statements in a single request using the JDBC Driver.

Note

By default, Snowflake returns an error for queries issued with multiple statements. This behavior is done in part to protect against SQL injection . Using multiple statements feature opens up the possibility for SQL injection, and so it should be used carefully. The risk can be reduced by using the SnowflakeStatement class’s setParameter() method to specify the number of statements to be executed, which makes it more difficult to inject a statement by appending it. For details, see Interface: SnowflakeStatement.

Sending Multiple Statements & Handling Results

Queries containing multiple statements can be executed the same way as queries with a single statement, except that the query string contains multiple statements separated by semicolons.

There are two ways to allow multiple statements:

  • Call Statement.setParameter(“MULTI_STATEMENT_COUNT”, n) to specify how many statements at a time this Statement should be allowed to execute. See below for more details.

  • Set the MULTI_STATEMENT_COUNT parameter at the session level or the account level by executing one of the following commands:

    alter session set MULTI_STATEMENT_COUNT = 0;
    

    Or:

    alter account <account> set MULTI_STATEMENT_COUNT = 0;
    

    Setting the parameter to 0 allows an unlimited number of statements. Setting the parameter to 1 allows only one statement at a time.

In order to make SQL Injection attacks more difficult, users can call the setParameter method to specify the number of statements to be executed in a single call, as shown below. In this example, the number of statements to execute in a single call is 3:

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

The default number of statements is 1; in other words, multi-statement mode is off.

To execute multiple statements without specifying the exact number, pass a value of 0.

The MULTI_STATEMENT_COUNT parameter is not part of the JDBC standard; it is a Snowflake extension. This parameter affects more than one Snowflake driver/connector.

When multiple statements are executed in a single execute() call, the result of the first statement is available through the standard getResultSet() and getUpdateCount() methods. To access the results of the statements that follow, use the getMoreResults() method. This method returns true when the next result is a result set, and false when the next result is an update count (or there are no more results).

The example below sets the MULTI_STATEMENT_COUNT parameter, executes 3 statements, and retrieves update counts and result sets:

// 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 recommends using execute() for multi-statement queries. The methods executeQuery() and executeUpdate() also support multiple statements, but will throw an exception if the first result is not the expected result type (result set and update count, respectively).

Failed Statements

If any of the SQL statements fails to compile or execute, execution is aborted. Any previous statements that ran before are unaffected.

For example, if the statements below are run as a single multi-statement query, the query will fail on the third statement, and an exception will be thrown.

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

If you were to then query the contents of table test, values 1 and 2 would be present.

Unsupported Features

PUT and GET statements are not supported for multi-statement queries.

Preparing statements and using bind variables are also not supported for multi-statement queries.

Batch Inserts

In your Java application code, you can insert multiple rows in a single batch by binding parameters in an INSERT statement and calling addBatch() and executeBatch().

As an example, the following code inserts two rows into a table that contains an INTEGER column and a VARCHAR column. The example binds values to the parameters in the INSERT statement and calls addBatch() and executeBatch() to perform a batch insert.

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

When you use this technique to insert a large number of values, the driver can improve performance by sending the data to a temporary stage for ingestion. The driver automatically does this when the number of values exceeds a threshold.

In order for the driver to send the data to a temporary stage, the user must have the following privilege on the schema:

  • CREATE STAGE.

If the user does not have this privilege, the driver falls back to sending the data with the query to the Snowflake database.

In addition, the current database and schema for the session must be set. If these are not set, the CREATE TEMPORARY STAGE command executed by the driver can fail with the following error:

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

For alternative ways to load data into the Snowflake database (including bulk loading using the COPY command), see Loading Data into Snowflake.

Java Sample Program

For a working sample written in Java, right-click the name of the file, SnowflakeJDBCExample.java, and save the link/file to your local file system.