Importing from JDBC

Deephaven provides tools for inferring a table schema from JDBC and importing data directly through a JDBC interface. Note that both the schema inference and imports operate on a JDBC ResultSet, not directly on the source table. However, JDBC drivers generally provide rich metadata with the ResultSet, so most standard SQL types can be mapped directly to the appropriate Deephaven column type.

JDBC Data Source Support

When JDBC data sources are used through command line tools or data import classes, any driver class string and connection URL can be specified. The schema discovery and import processes use fairly basic JDBC functionality, so most drivers should work. For schema discovery, though, the driver needs to be able to provide ResultSetMetaData about the query result set; some drivers may not provide this functionality.

For JDBC use in the Schema Editor UI, a limited set of data sources are currently available:

• MySQL/MariaDB
• Apache Redshift
• Microsoft SQL Server
• PostgreSql

These data sources have been tested and their typical drivers are known to support features needed for schema discovery and data import.

In either case, it may be necessary to review product documentation for the data source for details about how to connect to it using a JDBC driver. For example, the data source may need to be reconfigured to allow a username and password connection from the Deephaven merge server on which the import process or Schema Editor is being run. Some drivers also have specific requirements for the connection URL: e.g., Postgres requires the database name in the connection URL, while MySQL does not.

Quickstart

Here is an example of how to generate a schema from an SQL query ResultSet, deploy it, and perform a JDBC import from the command line. These commands assume a typical Deephaven installation and a SQL Server source database.

Given an SQL Server table created as follows:

create table trade (
id int primary key,
trade_date date,
sym varchar(50),
qty int,
px double PRECISION,
comment varchar(150));

Generate a schema from a JDBC query

Note that we limit the size of the result to avoid pulling the entire table into memory.

iris_exec jdbc_schema_creator -- \
-ns ExNamespace -tn Trade \
-cu "jdbc:sqlserver://<host>;database=<db>;user=<user>;password=<pwd>" \
-jd "com.microsoft.sqlserver.jdbc.SQLServerDriver" \
-jq "SELECT TOP 1000 * FROM trade"

The schema file will be generated in the ExNamespace directory.

ls -l ExNamespace/*.schema

Deploy the schema

sudo -u irisadmin /usr/illumon/latest/bin/dhconfig schema import --file <fully qualified schema file name>

Import a single data file into the specified Intraday partition

sudo -u dbmerge /usr/illumon/latest/bin/iris_exec jdbc_import -- \
-ns ExNamespace -tn Trade \
-cu "jdbc:sqlserver://<host>;database=<db>;user=<user>;password=<pwd>" \
-jd "com.microsoft.sqlserver.jdbc.SQLServerDriver" \
-jq "SELECT * FROM trade" \
-dp localhost/2018-09-26

This example will generate a table schema from the Trade table with the namespace ExNamespace and table name Trade. The schema creator creates a directory for the namespace in the current directory and places the schema file inside this directory. Once the schema is deployed, imports from the table may be scheduled.

Schema Inference

JDBC schema inference generates a table schema from a JDBC query ResultSet. A table column is generated for each column in the query result, and the process maps the SQL type to an appropriate Deephaven type. Column names are used as-is unless a systematic conversion is specified (the --sourceCasing and --destCasing arguments). While the presence of metadata makes the type mapping less error-prone for most import types, the generated schema should still be inspected before deployment.

Command Reference

iris_exec jdbc_schema_creator <launch args> -- <schema creator args>

The following arguments are available when running the JDBC schema creator:

Import Query

When Import - JDBC is selected, the Persistent Query Configuration Editor window shows the following options:

• To proceed with creating a query to import a JDBC file, you will need to select a DB Server and enter the desired value for Memory (Heap) Usage (GB).

• Options available in the Show Advanced Options section of the panel are typically not used when importing or merging data. To learn more about this section, please refer to the Persistent Query Configuration Viewer/Editor.

• The Access Control tab presents a panel with the same options as all other configuration types, and gives the query owner the ability to authorize Admin and Viewer Groups for this query. For more information, please refer to Access Control.

• Clicking the Scheduling tab presents a panel with the same scheduling options as all other configuration types. For more information, please refer to Scheduling.

• Clicking the JdbcImport Settings tab presents a panel with relevant options:

  

JdbcImport Settings

• Namespace: This is the namespace into which you want to import the file.
• Table: This is the table into which you want to import the data.
• Output Mode: This determines what happens if data is found in the fully-specified partition for the data. The fully-specified partition includes both the internal partition (unique for the import job) and the column partition (usually the date).
  • Safe - if existing data is found in the fully-specified partition, the import job will fail.
  • Append - if existing data is found in the fully-specified partition, data will be appended to it.
  • Replace - if existing data is found in the fully-specified partition, it will be replaced. This does not replace all data for a column partition value, just the data in the fully-specified partition. Import Source: This is the import source section of the associated schema file that specifies how source data columns will be set as Deephaven columns.
  • Strict Import will fail if a file being imported has missing column values (nulls), unless those columns allow or replace the null values using a default attribute in the ImportColumn definition.
• Single/Multi Partition: This controls the import mode. In single-partition, all of the data is imported into a single Intraday partition. In multi-partition mode, you must specify a column in the source data that will control to which partition each row is imported.
  • Single-partition configuration:
    • Partition Formula: This is the formula needed to partition the JDBC file being imported. If a specific partition value is used it will need to be surrounded by quotes. For example: currentDateNy() "2017-01-01"
    • Partition Substitution: This is a token used to substitute the determined column partition value in the source directory, source file, or source glob, to allow the dynamic determination of these fields. For example, if the partition substitution is "PARTITION_SUB", and the source directory includes "PARTITION_SUB" in its value, that PARTITION_SUB will be replaced with the partition value determined from the partition formula.
    • Substitution Date Format: This is the date format that will be used when a Partition Substitution is used. The standard Deephaven date partition format is yyyy-MM-dd (e.g., 2018-05-30), but this allows substitution in another format. For example, if the filename includes the date in yyyyddMM format instead (e.g., 20183005), that could be used in the Date Substitution Format field. All the patterns from the Java DateTimeFormatter class are allowed.
  • Multi-partition configuration:
    • Import Partition Column: This is the name of the database column used to choose the target partition for each row (typically "Date"). There must be an corresponding Import Column present in the schema, which will indicate how to get this value from the source data.
• Driver: This is the name of the driver needed to access the database. It defaults to Mysql.
• URL: This the URL for the JDBC connection.
• Query: This is the query detailing what data you want to pull from the database.

Importing Using Builder

JDBC imports may be performed from inside a Deephaven Groovy or Python script. Compared to other methods, this permits more elaborate logic with respect to existing data. These scripts may be executed as a persistent query or from the command line using the iris_exec run_local_script tool. All imports should be performed as the dbmerge user (or from a persistent query, running on a merge server).

Examples

The following script imports all the records from a SQL Server table into a single partition:

import com.illumon.iris.importers.util.JdbcImport
import com.illumon.iris.importers.ImportOutputMode

rows=new JdbcImport.Builder("ExNamespace", "Trade")
    .setDriver("com.microsoft.sqlserver.jdbc.SQLServerDriver")
    .setConnectionUrl( "jdbc:sqlserver://<host>;database=<db>;user=<user>;password=<pwd>")
    .setQuery("SELECT * FROM trade")
    .setDestinationPartitions("localhost/2018-05-01")
    .setStrict(false)
    .setOutputMode(ImportOutputMode.SAFE)
    .build()
    .run()

println "Imported " + rows + " rows."

from deephaven import *

rows = JdbcImport.Builder("ExNamespace", "Trade")\
    .setDriver("com.microsoft.sqlserver.jdbc.SQLServerDriver")\
    .setConnectionUrl( "jdbc:sqlserver://<host>;database=<db>;user=<user>;password=<pwd>")\
    .setQuery("SELECT * FROM trade")\
    .setDestinationPartitions("localhost/2018-05-01")\
    .setStrict(False)\
    .setOutputMode("SAFE")\
    .build()\
    .run()

print("Imported {} rows.".format(rows))

The following script imports all records into partitions based on the "Date" column, with the user and password values passed in via the iris_exec command line. This is a useful idiom when importing large datasets. Note that the input query includes a sort clause on the column being used for partitioning; this is important for optimal performance when performing multi-partition imports.

import com.illumon.iris.importers.util.JdbcImport
import com.illumon.iris.importers.ImportOutputMode

rows=new JdbcImport.builder("ExNamespace", "Trade")
    .setDriver("com.microsoft.sqlserver.jdbc.SQLServerDriver")
    .setConnectionUrl( "jdbc:sqlserver://<host>;database=<db>;user=<user>;password=<pwd>")
    .setUser(user)
    .setPassword(password)
    .setSourceTimeZone("UTC")
    .setQuery("SELECT * FROM table1 ORDER BY date")
    .setDestinationPartitions("localhost")
    .setPartitionColumn("Date")
    .build()
    .run()

println "Imported " + rows + " rows."

from deephaven import *

rows = JdbcImport.builder("ExNamespace", "Trade")\
    .setDriver("com.microsoft.sqlserver.jdbc.SQLServerDriver")\
    .setConnectionUrl("jdbc:sqlserver://<host>;database=<db>;user=<user>;password=<pwd>")\
    .setUser(user)\
    .setPassword(password)\
    .setSourceTimeZone("UTC")\
    .setQuery("SELECT * FROM table1 ORDER BY date")\
    .setDestinationPartitions("localhost")\
    .setPartitionColumn("Date")\
    .build()\
    .run()

print("Imported {} rows.".format(rows))

Import API Reference

The JDBC import class provides a static builder method, which produces an object used to set parameters for the import. The builder returns an import object from the build() method. Imports are executed via the run() method and if successful, return the number of rows imported. All other parameters and options for the import are configured via the setter methods described below. The general pattern when scripting an import is:

nRows = JdbcImport.builder(<namespace>,<table>)
    .set<option>(<option value>)
    …
    .build()
    .run()

JDBC Import Options

* Unless the source time zone is specified, the JDBC import task attempts to read the server time zone/UTC offset prior to executing the query. The source time zone is used to interpret SQL "datetime", "date", and "time-of-day" values, unless those types explicitly contain an offset. Since Deephaven presently has no "date" type, date values are interpreted as a datetime at midnight in the server time zone.

Import from Command Line

JDBC imports can be performed directly from the command line, using the iris_exec tool.

Command Reference

iris_exec jdbc_import <launch args> -- <jdbc import args>

JDBC Import Arguments

The JDBC importer takes the following arguments:

The schema of the target table will be used to select default setters for transformation of incoming data if there are no ImportColumns to provide other instructions. For example, if the source column is a string, but the Deephaven table column is designated as integer, the importer will attempt to cast the data as integer.

• The driver will need to be available from the classpath of the importer.
  • Examples of driver classes are:
    • com.microsoft.sqlserver.jdbc.SQLServerDriver for Microsoft SQL Server
    • com.mysql.jdbc.Driver for MySQL
• The connectionURL will include things like server name, port, authentication, etc, that will vary depending on the type of data source being used.
• An example of a SQL Server URL for a local, default instance, using integrated Windows security, might be something like the following:
  • jdbc:sqlserver://localhost:1433;databaseName=Sample;integratedSecurity=true
• Other sources may be a bit different. For example, a sample MySQL connectionURL follows:
  • jdbc:mysql://localhost:3306/Sample?user=root&password=secret

The SQL query format will also vary depending on specific language capabilities and expectations of the data source. However, the resultant columns should match the column names in the target table. Most SQL dialects will allow columns to be calculated or labeled as part of the query, and the importer will match columns based on the resultant labels. Rather than just directly matching result set column names to target table column names, another option is to add transformation metadata to the Deephaven schema. This allows the user to specify options like a different source column name, a different source column data type, whether a column can be null, and even custom transform functions to use during the import. See the general importer topic for a sample of schema for an import target table.

In addition to column translation features of the CSV importer, the JDBC importer will perform special handling when the source column is a Date type (JDBC DateTime, Date, or Timestamp), and the destination is a DBDateTime. This handling will attempt to do direct conversion between date values.