Using the JDBC Driver¶

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

Copy

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.

Upload data files directly from a stream to an internal stage¶

You can upload data files using the PUT command. However, sometimes it makes sense to transfer data directly from a stream to an internal (i.e. Snowflake) stage as a file. (The stage can be any internal stage type: table stage, user stage, or named stage. The JDBC driver does not support uploading to an external stage.) 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

Copy

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

Copy

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

Copy

Download data files directly from an internal stage to a stream¶

You can download data files using the GET command. However, sometimes it makes sense to transfer data directly from a file in an internal (i.e. Snowflake) stage to a stream. (The stage can be any internal stage type: table stage, user stage, or named stage. The JDBC driver does not support downloading to an external stage.) 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;

Copy

Sample usage:

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

Copy

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

Copy

Sending multiple statements and 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;
  

  Copy

  Or:

  alter account set MULTI_STATEMENT_COUNT = 0;
  

  Copy

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

Copy

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 more statements are available for iteration, and false otherwise.

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();                              // 0 (DDL)

// Second statement (insert)
stmt.getMoreResults();                              // true
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();                              // 0 (no more results)

Copy

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

Copy

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.

Simple binding¶

Without binding, a SQL statement specifies values by specifying literals inside the statement. For example, the following statement uses the literal value 42 in an UPDATE statement:

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

Copy

With binding, you can execute a SQL statement that uses a value that is inside a variable. For example:

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

Copy

The ? inside the VALUES clause specifies that the SQL statement uses the value from a variable. The setInt() method specifies that the first question mark in the SQL statement should be replaced with the value in the variable named my_integer_variable. Note that setInt() uses 1-based, rather than 0-based values (i.e. the first question mark is referenced by 1, not 0).

Binding variables to timestamp columns¶

Snowflake supports three different variations for timestamps: TIMESTAMP_LTZ , TIMESTAMP_NTZ , TIMESTAMP_TZ. When you call PreparedStatement.setTimestamp to bind a variable to a timestamp column, the JDBC Driver interprets the timestamp value in terms of the local time zone (TIMESTAMP_LTZ) or the time zone of the Calendar object passed in as an 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")));

If you want the driver to interpret the timestamp using a different variation (e.g. TIMESTAMP_NTZ), use one of the following approaches:

• Set the session parameter CLIENT_TIMESTAMP_TYPE_MAPPING to the variation.

  Note that the parameter affects all binding operations for the current session. If you need to change the variation (e.g. back to TIMESTAMP_LTZ), you must set this session parameter again.

• (In the JDBC Driver 3.13.3 and later versions) Call the PreparedStatement.setObject method, and use the targetSqlType parameter to specify one of the following Snowflake timestamp variations:

  • SnowflakeUtil.EXTRA_TYPES_TIMESTAMP_LTZ

  • SnowflakeUtil.EXTRA_TYPES_TIMESTAMP_TZ

  • SnowflakeUtil.EXTRA_TYPES_TIMESTAMP_NTZ

  For example:

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

  Copy

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

Copy

When you use this technique to insert a large number of values, the driver can improve performance by streaming the data (without creating files on the local machine) to a temporary stage for ingestion. The driver automatically does this when the number of values exceeds a threshold.

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.

I/O error: Connection reset¶

In some cases, the JDBC Driver might fail with the following error message after a period of inactivity:

I/O error: Connection reset

You can work around the problem by setting a specific “time to live” for the connections. If a connection is idle for longer than the “time to live”, the JDBC Driver removes the connection from the connection pool and creates a new connection.

To set the time to live, set the Java system property named net.snowflake.jdbc.ttl to the number of seconds that the connection should live:

• To set this property programmatically, call System.setProperty:

  // Set the "time to live" to 60 seconds.
  System.setProperty("net.snowflake.jdbc.ttl", "60")
  

  Copy

• To set this property when running the java command, use the -D flag:

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

  Copy

The default value of the net.snowflake.jdbc.ttl property is -1, which means that idle connections are not removed from the connection pool.

Handling errors¶

When handling errors and exceptions for a JDBC application, you can use the ErrorCode.java file that Snowflake provides to determine the cause of the problems. Error codes specific to the JDBC driver start with 2, in the form: 2NNNNN.