13.6. The JDBC Connector

Documentation

Home » Documentation » Using VoltDB

13.6. The JDBC Connector

The JDBC connector fetches the serialized data from the export tables and writes it, in batches, to another database through the standard JDBC (Java Database Connectivity) protocol.

When the JDBC connector opens the connection to the remote database, it first attempts to create tables in the remote database to match the VoltDB export-only tables by executing CREATE TABLE statements through JDBC. This is important to note because, it ensures there are suitable tables to receive the exported data. The tables are created using either the table names from the VoltDB schema or (if you do not enable the ignoregenerations property) the table name prefixed by the database generation ID.

If the target database has existing tables that match the VoltDB export-only tables in both name and structure (that is, the number, order, and datatype of the columns), be sure to enable the ignoregenerations property in the export configuration to ensure that VoltDB uses those tables as the export target.

It is also important to note that the JDBC connector exports data through JDBC in batches. That is, multiple INSERT instructions are passed to the target database at a time, in approximately two megabyte batches. There are two consequences of the batching of export data:

  • For many databases, such as Netezza, where there is a cost for individual invocations, batching reduces the performance impact on the receiving database and avoids unnecessary latency in the export processing.

  • On the other hand, no matter what the target database, if a query fails for any reason the entire batch fails.

To avoid errors causing batch inserts to fail, it is strongly recommended that the target database not use unique indexes on the receiving tables that might cause constraint violations.

If any errors do occur when the JDBC connector attempts to submit data to the remote database, the VoltDB disconnects and then retries the connection. This process is repeated until the connection succeeds. If the connection does not succeed, VoltDB eventually reduces the retry rate to approximately every eight seconds.

Table 13.2, “JDBC Export Properties” describes the supported properties for the JDBC connector.

Table 13.2. JDBC Export Properties

PropertyAllowable ValuesDescription
jdbcurl*connection stringThe JDBC connection string, also known as the URL.
jdbcuser*stringThe username for accessing the target database.
jdbcpasswordstringThe password for accessing the target database.
jdbcdriverstring

The class name of the JDBC driver. The JDBC driver class must be accessible to the VoltDB process for the JDBC export process to work. Place the driver JAR files in the lib/extension/ directory where VoltDB is installed to ensure they are accessible at runtime.

You do not need to specify the driver as a property value for several popular databases, including MySQL, Netezza, Oracle, PostgreSQL, and Vertica. However, you still must provide the driver JAR file.

schemastringThe schema name for the target database. The use of the schema name is database specific. In some cases you must specify the database name as the schema. In other cases, the schema name is not needed and the connection string contains all the information necessary. See the documentation for the JDBC driver you are using for more information.
minpoolsizeintegerThe minimum number of connections in the pool of connections to the target database. The default value is 10.
maxpoolsizeintegerThe maximum number of connections in the pool. The default value is 100.
maxidletimeintegerThe number of milliseconds a connection can be idle before it is removed from the pool. The default value is 60000 (one minute).
maxstatementcachedintegerThe maximum number of statements cached by the connection pool. The default value is 50.
ignoregenerationstrue, falseSpecifies whether a unique ID for the generation of the database is included as part of the output table name(s). The generation ID changes each time a database restarts or the catalog is updated. The default is false.
skipinternalstrue, falseSpecifies whether to include six columns of VoltDB metadata (such as transaction ID and timestamp) in the output. If you specify skipinternals as true, the output contains only the exported table data. The default is false.

*Required