Database Plugins

Apache Hop has optimized support for many database types. If you are using an unsupported database, you can always create a generic connection.

To create a database connection go to the metadata perspective, right-click on Relational Database Connections and select New.

The connection is saved in a central location and can then be used by all pipelines and workflows. If you have set your project to work with Hop, the database information will be in the ${PROJECT_HOME}/metadata/rdbms folder. For each connection, a separate .json file will be generated in this folder. This json file will have the name of the connection and will contain all of the connection information.

Adding JDBC drivers

Apache Hop ships the JDBC drivers it is allowed to redistribute - those under a license compatible with the Apache License - in the lib/jdbc folder of your installation. For databases whose driver has a restricted license, or that simply isn’t bundled, Hop can download the driver for you on demand, or you can add it manually.

The connection editor shows the installed driver and its version for the selected database type. When no driver is installed but one is available to download, a Download driver button appears next to it.

Downloading a driver on demand

Apache Hop knows the Maven coordinates of the JDBC driver for most database types and can download it - together with the dependencies it needs - from a public repository (Maven Central by default) at your request. There are three ways to do this:

  • In the GUI: open the database connection editor and select your database type. If the driver isn’t installed yet and a download is available, click Download driver. For restricted drivers (proprietary, or copyleft licenses such as LGPL/GPL) you first have to read and accept the vendor’s license in the dialog. The driver is downloaded into your JDBC folder and hot-loaded, so you can usually test the connection without restarting Hop.

  • From the command line: hop driver list lists every database type with a downloadable driver, its license category and whether it is already installed. hop driver install <id> downloads and installs it, for example:

    hop driver list
hop driver install oracle --accept-license

    --accept-license is required for restricted (LIC=X) drivers. Use --driver-version to pick a specific version and --repo to download from an internal mirror.

  • In a container: set the HOP_DRIVERS_DOWNLOAD environment variable (and HOP_DRIVERS_ACCEPT_LICENSE=true for restricted drivers) on the Hop Docker image to install drivers automatically at start-up. See the Docker container documentation for details.

Apache Hop never bundles, hosts or mirrors restricted drivers. They are downloaded from Maven Central (or the vendor) onto your own machine, at your explicit request, under the vendor’s license.

Adding a driver manually

Some databases (typically older or proprietary ones) do not publish their JDBC driver to a public repository. For those, download the driver from the vendor - the documentation for each database type below points you to the download location - and copy the jar into the lib/jdbc folder or into a folder listed in HOP_SHARED_JDBC_FOLDERS.

Set the HOP_SHARED_JDBC_FOLDERS environment variable to a folder that contains your additional JDBC drivers. Having them in a central folder helps you to easily upgrade or change your Apache Hop installation, without the need to add your JDBC drivers every time. This variable accepts a comma separated list to point to multiple directories; the default value when not set is lib/jdbc, e.g. <PATH_TO_YOUR_HOP_INSTALLATION>/lib/jdbc,<PATH_TO_YOUR_JDBC_FOLDER>. When you download a driver, Hop installs it into the first folder in this list that isn’t lib/jdbc, so your downloaded drivers stay separate from the bundled ones and survive upgrades.

To avoid conflicts, make sure you only have one driver for each database, and make sure you don’t have multiple copies of your drivers, e.g. both in HOP_SHARED_JDBC_FOLDERS and the hop/lib/jdbc folder.

Generic connection

When a specific database type is not yet available for the database you want to use, you can use the generic connection. To use a generic connection you have to copy your jdbc driver to the <PATH_TO_YOUR_HOP_INSTALLATION>/lib/jdbc folder or in your HOP_SHARED_JDBC_FOLDERS folder.

Generic Connection Dialog

Check the documentation for your databases' driver class and URL syntax to create your connection.

In the Driver Class field you specify your driver class, for example if you use PostgreSQL the class is org.postgresql.Driver.

Advanced properties

The advanced tab lets you specify a number of additional properties for your database connection.

Property Description

Supports the Boolean data type

is Boolean supported?

Supports the Timestamp data type

is Timestamp supported?

Quote all identifiers in database

Add quotes around all identifiers for generated SQL statements.

Force all identifiers to lower case

Change all identifiers to lower case for generated SQL statements.

Force all identifiers to upper case

Change all identifiers to upper case for generated SQL statements.

Preserve case of reserved words

Don’t change the casing for all reserved words for generated SQL statements.

The preferred schema name

The schema name to use by default (can be overruled).

The SQL statements to run after connection (; separated

a semicolon (';') separated list of SQL statements that need to be executed after the connection is created.

Options

The options table contains a list of key/value pairs that can be added to your JDBC driver. Check your database JDBC driver documentation for the right syntax.

For example, to add additional options to a MS SQL database, you can add options to achieve a JDBC URL like the one below. Apache Hop will take care of these properties in the background, there usually is no need to manually modify the JDBC URL (but you can, and in a Generic connection, you have to).

jdbc:sqlserver://localhost:1433;" +
     "databaseName=AdventureWorks;integratedSecurity=true;" +
     "encrypt=true;trustServerCertificate=true

integratedSecurity

true

encrypt

true

trustServerCertificate

true

Connection and socket timeouts

If a database host becomes unreachable at the wrong moment (for example a half-open TCP connection that is never answered), a transform can block indefinitely while it waits for the driver. This makes a pipeline appear to "hang" during initialization, even though the databases themselves are healthy.

Two variables set a default timeout for every JDBC connection Hop opens:

Variable Default Description

HOP_DATABASE_CONNECTION_TIMEOUT

30

Login/connection timeout in seconds applied while establishing a connection (maps to DriverManager.setLoginTimeout). Set to 0 to leave the connect unbounded. The setting is advisory - some drivers ignore it in favor of their own connection options.

HOP_DATABASE_SOCKET_TIMEOUT

0

Network/socket timeout in seconds applied to a connection once it is open (maps to Connection.setNetworkTimeout). 0 (default) leaves reads unbounded. Unsupported drivers are ignored.

HOP_DATABASE_SOCKET_TIMEOUT is disabled by default (0) on purpose: when set, it applies to every request on the connection, not just the initial read. Any single query, read or bulk operation that runs longer than the timeout is aborted and its connection is closed. If your pipelines contain long-running SQL (heavy aggregations, large bulk loads, slow reports), only enable it with a value large enough to cover them.

These are global defaults applied to all connections. For finer control - or to bound the initial handshake read, which happens inside the driver before Hop can apply the socket timeout - set the driver’s own timeout options in the connection’s Options tab (see the Options table above). For example:

Hard-coded JDBC to Hop data type mappings

Below are the mappings applied by Hop when converting from database column types to Hop data types when HOP_DB_DDL_COMPATIBLE is false (the default).

JDBC type Hop type

CHAR, VARCHAR, NVARCHAR, LONGVARCHAR, NCHAR, LONGNVARCHAR

String

CLOB, NCLOB

String (large text)

INTEGER, SMALLINT, TINYINT

Integer

BIGINT (signed)

Integer

BIGINT (unsigned)

BigNumber

FLOAT, DOUBLE, REAL, DECIMAL, NUMERIC

Number (with length/precision rules; BigNumber when needed)

DATE, TIME

Date

TIMESTAMP

Timestamp (or Date if the database does not support Timestamp)

BOOLEAN, BIT

Boolean

BINARY, BLOB, VARBINARY, LONGVARBINARY

Binary (with database-specific overrides)

Unmapped types (for example OTHER for UUID or INET) return no match from the hard-coded path and fall through to legacy method of figuring out the data type, which was asking all the different value metadata plugin types for a guess.