JDBC importer for Elasticsearch *** THIS REPOSITORY WILL BE DELETED WITHOUT ANY FURTHER NOTICE AFTER AUG 1 2026 ***
JDBC importer for Elasticsearch *** THIS REPOSITORY WILL BE DELETED WITHOUT ANY FURTHER NOTICE AFTER AUG 1 2026 ***
After 13 years and a long silence here, I say goodbye to Github. It was a pleasure to have served your needs and being overwhelmed by hundreds of issues and questions. I'm still well at my private forgejo at https://xbib.org being busy with other Java open source projects. So Long, and Thanks for All the Fish! Jörg
Image by icons8 Creative Commons Attribution-NoDerivs 3.0 Unported.
The Java Database Connection (JDBC) importer allows to fetch data from JDBC sources for indexing into Elasticsearch.
The JDBC importer was designed for tabular data. If you have tables with many joins, the JDBC importer is limited in the way to reconstruct deeply nested objects to JSON and process object semantics like object identity. Though it would be possible to extend the JDBC importer with a mapping feature where all the object properties could be specified, the current solution is focused on rather simple tabular data streams.
Assuming you have a table of name orders with a primary key in column id,
you can issue this from the command line
bin=$JDBC_IMPORTER_HOME/bin
lib=$JDBC_IMPORTER_HOME/lib
echo '{
"type" : "jdbc",
"jdbc" : {
"url" : "jdbc:mysql://localhost:3306/test",
"user" : "",
"password" : "",
"sql" : "select *, id as _id from orders"
}
}' | java \
-cp "${lib}/*" \
-Dlog4j.configurationFile=${bin}/log4j2.xml \
org.xbib.tools.Runner \
org.xbib.tools.JDBCImporter
And that's it. Now you can check your Elasticsearch cluster for the index jdbc or your Elasticsearch logs
about what happened.
JDBC importer 2.3.4.0
http://xbib.org/repository/org/xbib/elasticsearch/importer/elasticsearch-jdbc/2.3.4.0/elasticsearch-jdbc-2.3.4.0-dist.zip
in the following steps replace <version> by one of the versions above, e.g. 1.7.0.0
download the JDBC importer distribution
wget http://xbib.org/repository/org/xbib/elasticsearch/importer/elasticsearch-jdbc/<version>/elasticsearch-jdbc-<version>-dist.zip
unpack
unzip elasticsearch-jdbc-<version>-dist.zip
go to the unpacked directory (we call it $JDBC_IMPORTER_HOME)
cd elasticsearch-jdbc-<version>
if you do not find the JDBC driver jar in the lib directory, download it from your vendor's site
and put the driver jar into the lib folder
modify script in the bin directory to your needs (Elasticsearch cluster address)
run script with a command that starts org.xbib.tools.JDBCImporter with the lib directory on the classpath
The JDBC importer comes with open source JDBC drivers bundled for your convenience. They are not part of the JDBC importer, hence, there is no support and no guarantee the bundled drivers will work. Please read the JDBC driver license files attached in the distribution. JDBC importer does not link against the code of the drivers. If you do not want the drivers jars, they can be safely removed or replaced by other JDBC drivers at your choice.
The Maven project site is available at Github
All feedback is welcome! If you find issues, please post them at Github
You find this software useful and want to honor me for my work? Please donate. Donations will also help to keep up the development of open source Elasticsearch add-ons.
The relational data is internally transformed into structured JSON objects for the schema-less indexing model of Elasticsearch documents.
The importer can fetch data from RDBMS while multithreaded bulk mode ensures high throughput when indexing to Elasticsearch.
The general form of a JDBC import specification is a JSON object.
{
"type" : "jdbc",
"jdbc" : {
<definition>
}
}
Example:
{
"type" : "jdbc",
"jdbc" : {
"url" : "jdbc:mysql://localhost:3306/test",
"user" : "",
"password" : "",
"sql" : "select * from orders",
"index" : "myindex",
"type" : "mytype",
...
}
}
The importer can either be executed via stdin (for example with echo)
bin=$JDBC_IMPORTER_HOME/bin
lib=$JDBC_IMPORTER_HOME/lib
echo '{
...
}' | java \
-cp "${lib}/*" \
-Dlog4j.configurationFile=${bin}/log4j2.xml \
org.xbib.tools.Runner \
org.xbib.tools.JDBCImporter
or with explicit file name parameter from command line. Here is an example
where statefile.json is a file which is loaded before execution.
java \
-cp "${lib}/*" \
-Dlog4j.configurationFile=${bin}/log4j2.xml \
org.xbib.tools.Runner \
org.xbib.tools.JDBCImporter \
statefile.json
This style is convenient for subsequent execution controlled by the statefile parameter
if statefile is set to statefile.json.
Here is the list of parameters for the jdbc block in the definition.
strategy - the strategy of the JDBC importer, currently implemented: "standard", "column"
url - the JDBC driver URL
user - the JDBC database user
password - the JDBC database password
sql - SQL statement(s), either a string or a list. If a statement ends with .sql, the statement is looked up in the file system. Example for a list of SQL statements:
"sql" : [
{
"statement" : "select ... from ... where a = ?, b = ?, c = ?",
"parameter" : [ "value for a", "value for b", "value for c" ]
},
{
"statement" : "insert into ... where a = ?, b = ?, c = ?",
"parameter" : [ "value for a", "value for b", "value for c" ],
"write" : "true"
},
{
"statement" : ...
}
]
sql.statement - the SQL statement
sql.write - boolean flag, if true, the SQL statement is interpreted as an insert/update statement that needs write access (default: false).
sql.callable - boolean flag, if true, the SQL statement is interpreted as a JDBC CallableStatement for stored procedures (default: false).
sql.parameter - bind parameters for the SQL statement (in order). Some special values can be used with the following meanings:
$now - the current timestamp$state - the state, one of: BEFORE_FETCH, FETCH, AFTER_FETCH, IDLE, EXCEPTION$metrics.counter - a counter$lastrowcount - number of rows from last statement$lastexceptiondate - SQL timestamp of last exception$lastexception - full stack trace of last exception$metrics.lastexecutionstart - SQL timestamp of the time when last execution started$metrics.lastexecutionend - SQL timestamp of the time when last execution ended$metrics.totalrows - total number of rows fetched$metrics.totalbytes - total number of bytes fetched$metrics.failed - total number of failed SQL executions$metrics.succeeded - total number of succeeded SQL executionslocale - the default locale (used for parsing numerical values, floating point character. Recommended values is "en_US")
timezone - the timezone for JDBC setTimestamp() calls when binding parameters with timestamp values
rounding - rounding mode for parsing numeric values. Possible values "ceiling", "down", "floor", "halfdown", "halfeven", "halfup", "unnecessary", "up"
scale - the precision of parsing numeric values
autocommit - true if each statement should be automatically executed. Default is false
fetchsize - the fetchsize for large result sets, most drivers use this to control the amount of rows in the buffer while iterating through the result set
max_rows - limit the number of rows fetches by a statement, the rest of the rows is ignored
max_retries - the number of retries to (re)connect to a database
max_retries_wait - a time value for the time that should be waited between retries. Default is "30s"
resultset_type - the JDBC result set type, can be TYPE_FORWARD_ONLY, TYPE_SCROLL_SENSITIVE, TYPE_SCROLL_INSENSITIVE. Default is TYPE_FORWARD_ONLY
resultset_concurrency - the JDBC result set concurrency, can be CONCUR_READ_ONLY, CONCUR_UPDATABLE. Default is CONCUR_UPDATABLE
ignore_null_values - if NULL values should be ignored when constructing JSON documents. Default is false
detect_geo - if geo polygons / points in SQL columns should be parsed when constructing JSON documents. Default is true
detect_json - if json structures in SQL columns should be parsed when constructing JSON documents. Default is true
prepare_database_metadata - if the driver metadata should be prepared as parameters. Default is false
prepare_resultset_metadata - if the result set metadata should be prepared as parameters. Default is false
column_name_map - a map of aliases that should be used as a replacement for column names of the database. Useful for Oracle 30 char column name limit. Default is null
query_timeout - a second value for how long an SQL statement is allowed to be executed before it is considered as lost. Default is 1800
connection_properties - a map for the connection properties for driver connection creation. Default is null
schedule - a single or a list of cron expressions for scheduled execution. Syntax is equivalent to the
Quartz cron expression format (see below for syntax)
threadpoolsize - a thread pool size for the scheduled executions for schedule parameter. If set to 1, all jobs will be executed serially. Default is 4.
interval - a time value for the delay between two runs (default: not set)
elasticsearch.cluster - Elasticsearch cluster name
elasticsearch.host - array of Elasticsearch host specifications (host name or host:port)
elasticsearch.port - port of Elasticsearch host
elasticsearch.autodiscover - if true, JDBC importer will try to connect to all cluster nodes. Default is false
max_bulk_actions - the length of each bulk index request submitted (default: 10000)
max_concurrent_bulk_requests - the maximum number of concurrent bulk requests (default: 2 * number of CPU cores)
max_bulk_volume - a byte size parameter for the maximum volume allowed for a bulk request (default: "10m")
max_request_wait - a time value for the maximum wait time for a response of a bulk request (default: "60s")
flush_interval - a time value for the interval period of flushing index docs to a bulk action (defaul
No open issues yet, or sync has not completed.