The JDBC connector receives the serialized data from the export streams and writes it, in batches, to another database through the standard JDBC (Java Database Connectivity) protocol.
By default, 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 stream 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 stream names from the VoltDB schema or (if you do not enable the ignoregenerations property) the stream name prefixed by the database generation ID.
If the target database has existing tables that match the VoltDB export streams 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 15.3, “JDBC Export Properties” describes the supported properties for the JDBC connector.
Table 15.3. JDBC Export Properties
|jdbcurl*||connection string||The JDBC connection string, also known as the URL.|
|jdbcuser*||string||The username for accessing the target database.|
|jdbcpassword||string||The password for accessing the target database.|
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
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.
|schema||string||The 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.|
|minpoolsize||integer||The minimum number of connections in the pool of connections to the target database. The default value is 10.|
|maxpoolsize||integer||The maximum number of connections in the pool. The default value is 100.|
|maxidletime||integer||The number of milliseconds a connection can be idle before it is removed from the pool. The default value is 60000 (one minute).|
|maxstatementcached||integer||The maximum number of statements cached by the connection pool. The default value is 50.|
|createtable||true, false||Specifies whether VoltDB should create the corresponding table in the remote database. By default , VoltDB creates the table(s) to receive the exported data. (That is, the default is true.) If you set this property to false, you must create table(s) with matching names to the VoltDB export streams before starting the export connector.|
|lowercase||true, false||Specifies whether VoltDB uses lowercase table and column names or not. By default, VoltDB issues SQL statements using uppercase names. However, some databases (such as PostgreSQL) are case sensitive. When this property is set to true, VoltDB use all lowercase names rather than uppercase. The default is false.|
|ignoregenerations||true, false||Specifies 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 database schema is updated. The default is false.|
|skipinternals||true, false||Specifies 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 stream data. The default is false.|