JDBC Logger

The JDBC logger is a special type of TableLogger that will log snapshots and updates to Deephaven tables to a JDBC data source. The target JDBC table must have a schema compatible with the source table (column names and types).

By default, Deephaven table events are replicated in the target table:

new rows cause an SQL INSERT.
modifications result in an UPDATE.
removals result in a DELETE.

Alternatively, the logger can be configured in "log mode", which will log every Deephaven event as a new row in the target table. The row index and operation (added/modified/removed) can also be logged.

The JDBC logger is configured to use batching to ensure inserts are as efficient as possible (multi-row inserts). The default batch size is 500 rows. Irrespective of the batch size, each commit to the target requires flushing any outstanding rows. For this reason, batching is only effective in transaction modes other than RowByRow.

Quickstart

For a (fake) trade table defined in Deephaven as:

trades=db.timeTable("00:00:05").updateView("id=i+1",
    "trade_date='2018-10-01T00:00:00.000000000 NY'",
    "sym=`MSFT`",
    "qty=100",
    "px=31.14159f",
    "comment=`A sample trade`")

Create a table in your JDBC data source defined by:

CREATE TABLE trade(
    id INT PRIMARY KEY,
    trade_date TIMESTAMP NOT NULL,
    sym VARCHAR(12) NOT NULL,
    px DOUBLE PRECISION NOT NULL,
    comment TEXT NOT NULL);

Create a JDBC logger using the builder (note: make sure the JDBC driver is available in the classpath):

jdbcLogger = JdbcLogger.builder( "jdbc:sqlserver://<host>;database=<database>;user=<user>;password=<pwd>",
"trade").build()

Start logging from Deephaven to the JDBC table:

jdbcLogger.logTable(trades, trades.getIndex(), TableLoggerBase.Flags.Atomic, true, true);

JDBC Logger API

JDBC loggers must be created the the JDBCLogger.Builder class.

Note

See: This API is described in the Javadoc.Builder options.

Option	Argument(s)	Required Yes/No	Default	Description
`jdbcUser`	String	N	N/A	The JDBC user, if not specified in the URL.
`jdbcPassword`	String	N	N/A	The JDBC password, if not specified in the URL.
`calendar`	java.util.Calendar	N	Local calendar.	The calendar to use when logging JDBC date/datetime/timestamp values. This is important for columns where the time zone is not stored.
`batchSize`	int	N	500	The batch size when writing to the JDBC data source. This is only effective when not in RowByRow mode.
`logMode`	boolean	N	false	Whether to treat target table as append-only; logging each change to the source table as a new row.
`rowIndexColumn`	String	N	N/A	The JDBC table column that will receive the index of the source table row that caused each row (in log mode).
`operationColumn`	String	N	N/A	The JDBC column that will receive the operation that caused each row (in log mode).
`dataColumn`	String, `[String]`	N	N/A	Add a data column for logging. Optionally, specify the source column name if not the same as the target.
`keyColumns`	String	N*	N/A	A set of columns that represent the primary key when logging to JDBC. This is required if not in log mode, and may be either the row index column or a set of data columns that represent a unique key for each row.

* Required if not in log mode, since the logger needs a way to uniquely identify target rows.

Examples

Builder Examples

The following three examples show different ways to configure a JDBC logger.

Create a logger that will log all data columns with matching names, using the "id" column as the primary key for UPDATE and DELETE operations.

jdbcLogger = JdbcLogger.builder(jdbcUrl, "trade")
        .keyColumns("id")
        .build()

from deephaven import *
importjava('com.illumon.iris.export.jdbc.JdbcLogger')

jdbcLogger = JdbcLogger.builder(jdbcUrl, "trade") \
        .keyColumns("id") \
        .build()

Create a logger that will log all data columns with matching names, as well as the source row index and operation, in "log mode", putting the source row index and operation in the rowIndex and operation JDBC columns respectively.

jdbcLogger = JdbcLogger.builder(jdbcUrl, "trade")
        .logMode(true)
        .rowIndexColumn("rowIndex")
        .operationColumn("operation")
        .build()

from deephaven import *
importjava('com.illumon.iris.export.jdbc.JdbcLogger')

jdbcLogger = JdbcLogger.builder(jdbcUrl, "trade") \
        .logMode(True) \
        .rowIndexColumn("rowIndex") \
        .operationColumn("operation") \
        .build()

Create a logger that maps data columns with different names (the Deephaven table has the columns Id, Ticker, TradeDate, Size, Price, and Comment which correspond to the SQL columns id, sym, trade_date, qty, px, and comment respectively). The id column is used as the primary key for UPDATE and DELETE operations.

jdbcLogger = JdbcLogger.builder(jdbcUrl, "trade")
        .dataColumn("id", "Id")
        .dataColumn("sym", "Ticker")
        .dataColumn("trade_date", "TradeDate")
        .dataColumn("qty", "Size")
        .dataColumn("px", "Price")
        .dataColumn("comment", "Comment")
        .keyColumns("id")
        .build()

from deephaven import *
importjava('com.illumon.iris.export.jdbc.JdbcLogger')

jdbcLogger = JdbcLogger.builder(jdbcUrl, "trade") \
        .dataColumn("id", "Id") \
        .dataColumn("sym", "Ticker") \
        .dataColumn("trade_date", "TradeDate") \
        .dataColumn("qty", "Size") \
        .dataColumn("px", "Price") \
        .dataColumn("comment", "Comment") \
        .keyColumns("id") \
        .build()

Logging Examples

The following three examples show variants of the logTable method.

Log a snapshot of the trades table (no updates). This uses the Atomic option, which is highly recommended for insert speed, especially for large tables.

jdbcLogger.logTable(trades, TableLoggerBase.Flags.Atomic)

from deephaven import *
importjava('com.illumon.iris.export.jdbc.JdbcLogger')
importjava('com.illumon.iris.db.tables.dataimport.TableLoggerBase')

jdbcLogger.logTable(trades, TableLoggerBase.Flags.Atomic)

Log a snapshot, followed by updates. Each update to the source table will be logged as a single JDBC transaction.

jdbcLogger.logTable(trades, TableLoggerBase.Flags.Atomic, true)

from deephaven import *
importjava('com.illumon.iris.export.jdbc.JdbcLogger')
importjava('com.illumon.iris.db.tables.dataimport.TableLoggerBase')

jdbcLogger.logTable(trades, TableLoggerBase.Flags.Atomic, True)

Log a snapshot, followed by updates. Each update to the source table will be logged as a single JDBC transaction. Do not log previous values as a separate row when modifications occur (only relevant in log mode).

jdbcLogger.logTable(trades, TableLoggerBase.Flags.Atomic, true, false)

from deephaven import *
importjava('com.illumon.iris.export.jdbc.JdbcLogger')
importjava('com.illumon.iris.db.tables.dataimport.TableLoggerBase')

jdbcLogger.logTable(trades, TableLoggerBase.Flags.Atomic, True, False)