JDBC Driver

The Deephaven JDBC Driver is intended to make access to Deephaven data easier from clients that support the use of JDBC data sources. The current version offers snapshot-query support in Groovy and Python. It allows pass-through of Deephaven query language to the Deephaven database. It does not provide a SQL interpreter or translator, so SQL queries cannot be submitted to/through it. Most of the reading-related portions of the ResultSet and ResultSetMetaData interfaces are implemented. Inserts/updates (DML) and metadata manipulation (DDL) are not supported. Unimplemented methods and features, such as scrollable cursors, will throw an UnsupportedOperationException.

The following is a Java example executing a simple query. Note the use of the conventional "?" specifying a PreparedStatement parameter.

final DeephavenDataSource deephavenDataSource = new DeephavenDataSource("MyDataSource");
deephavenDataSource.setUrl("jdbc:deephaven://<dispatcher host>:<dispatcher port>?propFile=iris-console.prop");
deephavenDataSource.setUser("iris");
deephavenDataSource.setPassword("iris");
deephavenDataSource.setPropFile("iris");

try (final Connection connection = getConnection()) {
   try (final PreparedStatement statement = connection.prepareStatement(
           "db.i(\"DbInternal\", \"QueryPerformanceLog\").where(\"Date=?\").tail(1000)")) {
       statement.setString(1, today);
       try(final ResultSet resultSet = statement.executeQuery()) {
           while(resultSet.next()) {
final long queryId = resultSet.getLong("QueryId");
final String dispatcherName = resultSet.getString("DispatcherName");
...
           }
       }
   }
}

Here is an example in R. Note the Deephaven jars and property file are specified on the classpath before the driver is loaded.

library(RJDBC)

jars <- list.files(deephaven.lib.path, full.names=TRUE)
classpath <- paste(jars, collapse=':')
classpath <- paste(prop.file.path, classpath, sep=":")

drv <- JDBC("io.deephaven.jdbc.Driver", classpath, identifier.quote="`")

url <- paste0("jdbc:deephaven://",dispatcher.host,":",dispatcher.port)

conn <- dbConnect(drv, url, user, password)

result <- dbGetQuery(conn, 'db.i("DbInternal", "QueryPerformanceLog").where("Date=`2019-03-20`").tail(1000)')

dbDisconnect(conn)

Connection Strings

The Deephaven JDBC connection string is formatted like a URL:

jdbc:deephaven://<query host>:<query port>?<properties>

As shown above, <properties> are separated by "&" characters. For example, the following connection string specifies jdbc-config.prop as the Deephaven configuration and a driver log level of DEBUG:

jdbc:deephaven://my-query-server:22013?propFile=jdbc-config.prop&logLevel=DEBUG

See the Configuration Properties below for details on which options are available.

Tested JDBC Clients

The JDBC driver has been tested with the following tools/toolsets, in addition to JDBC connection and resultset processing in Java and Python applications.

• MyBatis no parameter and single parameter selects work.
• SQL Squirrel works for connection and execution of queries.
• LibreOffice Calc with JaguarSQL extension works to pull data into a worksheet.
• LibreOffice Base does not work, because it requires scrollable cursor support.

Query Evaluation

The query passed to the JDBC Statement/PreparedStatement must be a Groovy or Python expression (depending on the session type). The expression must evaluate to a Deephaven Table, Integer, or an array of Table/Integer values. The support for return of Integer values is intended for future compatibility with update statements. If an array is returned, the results can be iterated according to the JDBC standard (getMoreResults()).

After a query is evaluated in the Deephaven worker, a snapshot is taken of the resulting Table and the results are returned to the client incrementally, as next() is called on the ResultSet. The default fetch size is 10,000 rows. This can be changed by calling setFetchSize() on the ResultSet prior to iterating. Note that due to the way the snapshot works, the state of the table is "frozen" for the purposes of the ResultSet but may continue to tick on the server. To retrieve data added to a ticking table after the execution of the query, the current ResultSet must be closed and the query re-executed to get a fresh snapshot.

Under normal conditions, query evaluation takes place in two steps:

• first, question mark characters are converted to bound variable names and escape sequences are converted;
• then, the query is sent to the Deephaven worker for evaluation.

Escape sequences are typically necessary in two cases:

1. an embedded question mark (for example, a Groovy ternary expression).
2. a backslash (since backslashes are used to start an escape).

For example to embed a backslash and question mark in a literal string, you might express a query as db.i("Namespace","Table").updateView("NewColumn=`Something\\\\Weird\?`). These characters must be escaped no matter where they occur in the query. It is possible to turn off escape processing by calling setEscapeProcessing(false) on the Statement before executing it, but this is only useful for Statements without bound parameters, as question marks will not be bound properly unless escape processing is enabled.

PreparedStatement parameters are normally bound in the Deephaven query scope, meaning they are available directly in query expressions, so you can do things like:

db.i("Namespace","Table").where("A=?").updateView("B=?")

If you prefer to have variables bound at the "higher level" of the Groovy/Python session, you can specify this using the property bindParameterMode, but this will make using those variables in query conditions considerably more cumbersome - the latter becomes:

 db.i("Namespace","Table")
.where("A = `" + ? + `"`)``
.updateView("B=`" + ? + "`")``

This example above will depend on string conversion.

Configuration

The Deephaven JDBC Driver requires configuration from a Deephaven property file, like most other Deephaven processes. The property file can be specified in the URL or to the DataSource by calling setPropFile(propFile), and must exist on the classpath. Typically, the proper configuration file to use would match that used by another client process such as the console. In older Deephaven releases, this is generally iris-console.prop; in newer ones, it is usually iris-common.prop. For iris-common.prop, or other properties files that use stanzas, needed properties will need to be available in the root of the properties file, or in stanzas which match the service.name of the application; setting a JVM property of service.name=iris_console will allow the driver to use properties configured for the Deephaven console application. Most JDBC client applications provide an interface for setting additional JVM arguments. For Python, this will typically be set in a call to startJVM. If no property file is specified, the driver will attempt to load iris-defaults.prop. Note that (as is typical) if the specified configuration file includes another file, that file must also exist on the classpath.

Two relatively easy ways to configure a client with the correct jars and support files are using the Deephaven Launcher or the Deephaven Updater.

The Deephaven JDBC Connection URL requires a specific dispatcher host and port in addition to a property file. The specified dispatcher will be used to start a worker session and run all queries created with that Connection.

Classpath

The JDBC jar as well as all its dependencies must be on the classpath for the Driver to function. The property file mentioned above must also be on the classpath.

Authentication

Authentication with the JDBC driver is performed in the same way as other Deephaven processes, with a username and password specified in the URL or to the DataSource. If no user or password is specified, the system will attempt to use any other authentication that might be configured in the property file, however, by default no other authentication is configured.

To authenticate with a private key, the JVM property WAuthenticationClientManager.defaultPrivateKeyFile will need to be set to point to the key file. This can be set in JVM arguments for a Java client application or through setProperty. Most JDBC client applications provide an interface for setting additional JVM arguments. For Python, this will typically be set in a call to startJVM. Note that the list of authentication servers is typically given in the property file, but can be overridden with a connection property as well.

Logging

The driver will log using the standard Deephaven logging infrastructure at the log level specified in the connection string (WARN by default). The log files will be prefixed with "DeephavenJDBC" unless an alternative prefix is specified. For extensive information on which methods are being called in the driver, you may use the log level TRACE, but this may result in very large log files over time.

Column Types

The result of the query is mapped to SQL types according to the following table. For each supported type, there is a recommended "getter" that should be used to extract the data from the ResultSet. Getters that are not "lossy" are also supported - for example you can use getDouble() to get the value from an int column, but not vice-versa. getString() will work on any column, although sometimes the result will be less than ideal (not all objects provide useful toString() overloads). Generally, Deephaven types are nullable (unless a specific non-nullable codec is used), and the wasNull() method can be used to test for null with primitive types, as per the JDBC specification.

Configuration Properties

The following properties are available in the JDBC connection URL or on the DeephavenDataSource (the result is the same).