JDBC V2

Latest version: v2.8.0
Compatible with Fusion version: 5.9.0 and later

Add a JDBC driver to Fusion

Perform these prerequisites to ensure the connector can reliably access, crawl, and index your data. Proper setup helps avoid configuration or permission errors, so use the following guidelines to keep your content available for discovery and search in Fusion. Before creating your datasource, ensure Fusion can load your JDBC driver. These steps let the connector pull in the driver automatically at runtime. Fusion UI:

Navigate to System > Blobs.
Click Add > JDBC Driver.
Choose your JAR file and click Upload.

API: In the following steps, replace ID with the name of your JAR file.

PUT your driver JAR file to /api/blobs/ID?resourceType=driver:jdbc.
Verify with GET /api/blobs/ID/manifest.

See the Blob Store API for additional guidance on sending these requests.

Upload a JDBC Driver to Fusion

The JDBC V2 connector is supported, and fetches documents from a relational database via SQL queries. Under the hood, this connector implements the Solr DataImportHandler (DIH) plugin.Fusion stores JDBC drivers in the blob store. You can upload a driver using the Fusion UI or the Blob Store API.

How to upload a JDBC driver using the Fusion UI

In the Fusion UI, navigate to System > Blobs.
Click Add.
Select JDBC Driver. The “New ‘JDBC Driver’ Upload” panel appears.
Click Choose File and select the .jar file from your file system.
Click Upload. The new driver’s blob manifest appears.

From this screen you can also delete or replace the driver.

How to install a JDBC driver using the API

Upload the JAR file to Fusion’s blob store using the /blobs/{id} endpoint. Specify an arbitrary blob ID, and a resourceType value of plugin:connector, as in this example:

curl -u USERNAME:PASSWORD -H "content-type:application/java-archive" -H "content-length:707261" -X PUT --data-binary @postgresql-42.0.0.jar http://localhost:8764/api/blobs/mydriver?resourceType=driver:jdbc

Success response:

{
  "name" : "mydriver",
  "contentType" : "application/java-archive",
  "size" : 707261,
  "modifiedTime" : "2017-06-09T19:00:48.919Z",
  "version" : 0,
  "md5" : "c67163ca764bfe632f28229c142131b5",
  "metadata" : {
    "subtype" : "driver:jdbc",
    "drivers" : "org.postgresql.Driver",
    "resourceType" : "driver:jdbc"
  }
}

Fusion automatically publishes the event to the cluster, and the listeners perform the driver installation process on each node.

If the blob ID is identical to an existing one, the old driver will be uninstalled and the new driver will installed in its place. To get the list of existing blob IDs, run: curl -u USERNAME:PASSWORD https://FUSION_HOST:FUSION_PORT/api/blobs

To verify the uploaded driver, run:

curl -u USERNAME:PASSWORD https://FUSION_HOST:FUSION_PORT/api/blobs/BLOB_ID/manifest

Where the BLOB_ID is the name specified during upload, such as “mydriver” above. A success response looks like this:

{
  "name" : "mydriver",
  "contentType" : "application/java-archive",
  "size" : 707261,
  "modifiedTime" : "2017-06-09T19:05:17.897Z",
  "version" : 1569755095787110400,
  "md5" : "c67163ca764bfe632f28229c142131b5",
  "metadata" : {
    "subtype" : "driver:jdbc",
    "drivers" : "org.postgresql.Driver",
    "resourceType" : "driver:jdbc"
  }
}

Supported JDBC drivers

This section contains a list of supported JDBC drivers that are compatible with any driver/database that implements a SQL standard.

Authentication parameters may be provided as part of the connection string. It is not necessary to include a username and password in the datasource configuration.

MySQL

Download driver
Default driver class name: com.mysql.cj.jdbc.Driver
Connection URL specification
- Example: jdbc:mysql://mysql:3306/testdb

Postgresql

Download driver
Default driver class name: org.postgresql.Driver
Connection URL specification
- Example: jdbc:postgresql://postgres:5432/testdb

Microsoft SQL Server and Azure SQL Service

Download driver
Default driver class name: com.microsoft.sqlserver.jdbc.SQLServerDriver
Connection URL specification
- Example (SQL Server): jdbc:sqlserver://mssql:1433
- Example (cloud-based Azure SQL Service): jdbc:sqlserver://azure-test.database.windows.net:1433;database=testdbencrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;

Oracle database

Download driver
Default driver class name: oracle.jdbc.OracleDriver
Connection URL specification
- Example: jdbc:oracle:thin:@oracledb:1521:orc1

IBM DB2

Download driver
Default driver class name: com.ibm.db2.jcc.DB2Driver
Connection URL specification
- Example: jdbc:db2://db2:50000/testdb

Authentication

Setting up the correct authentication according to your organization’s data governance policies helps keep sensitive data secure while allowing authorized indexing. If authentication is required to access your data, the JDBC V2 connector uses standard database credentials to log in. In your datasource configuration, supply:

username using the database account’s username.
password using the corresponding password.

Remote connectors

V2 connectors support running remotely in Fusion versions 5.7.1 and later. For more information, see Configure remote V2 connectors. Below is an example configuration showing how to specify the file system to index under the connector-plugins entry in your values.yaml file:

additionalVolumes:
- name: fusion-data1-pvc
    persistentVolumeClaim:
    claimName: fusion-data1-pvc
- name: fusion-data2-pvc
    persistentVolumeClaim:
    claimName: fusion-data2-pvc
additionalVolumeMounts:
- name: fusion-data1-pvc
    mountPath: "/connector/data1"
- name: fusion-data2-pvc
    mountPath: "/connector/data2"

You may also need to specify the user that is authorized to access the file system, as in this example:

securityContext:
    fsGroup: 1002100000
    runAsUser: 1002100000

Crawl behavior

The JDBC V2 connector retrieves data based on the user-supplied SQL query. The SQL implementation allows full use of all features, but for best results, structure the SQL query to utilize pagination.

Pagination

Automatic pagination

Automatic pagination is enabled by default. You can disable it by setting enableAutomaticPagination to false. The batchSize field sets the number of documents displayed per page of query results. When a SQL query includes pagination terms such as OFFSET, LIMIT, or ROWS ONLY, the SQL query terms override automatic pagination.

DB2 notes

When crawling an IBM DB2 database, the pagination method depends on your database version:

IBM DB2 version < 11.5 - Pagination is performed using subqueries.
IBM DB2 version >= 11.5 - Pagination is performed using rows and offsets.
Appending --subquery to the SQL query statement forces the connector to paginate using subqueries regardless of the IBM DB2 database version.

Special variables for pagination

The connector uses placeholders for pagination that are updated based on batchSize:

${limit}: used as the LIMIT parameter in native SQL
${offset}: used as the OFFSET parameter in native SQL

Here are examples of SQL statements using these parameters:

MySQL, postgresql, and DB2:

SELECT * FROM example_table LIMIT ${limit} OFFSET ${offset}

Microsoft, Azure, and Oracle:

SELECT * FROM example_table ORDER BY id OFFSET ${offset} ROWS FETCH NEXT ${limit} ROWS ONLY

Nested queries

The connector supports nested queries. Nested queries are a set of SQL queries performed on each row returned by the main query. Because they are executed on every row returned, nested queries can significantly degrade performance. Use the ${id} variable to retrieve items associated with a specific primary key:

SELECT * FROM example_table ORDER BY id OFFSET ${offset} ROWS FETCH NEXT ${limit} ROWS ONLY

Incremental crawl

In addition to the main SQL query, users can also specify an optional delta SQL query that returns only new and modified items to increase performance during recrawls. The special ${limit} and ${offset} pagination variables are the same as in normal crawls.

Special incremental crawl variable

Use the ${last_index_time} for an incremental crawl as a placeholder variable that contains the time the last crawl completed. This variable is used to filter results for items added or modified since the last time the datasource job was run and is stored as a timestamp in the format of yyyy-MM-dd HH:mm:ss.SSS

The format may not be compatible with all driver date math implementations without additional conversion steps.

Example:

SELECT * FROM example_table WHERE (timestamp_column >= ${last_index_time})

Depending on the database used, it may be necessary to surround ${last_index_time} in single quotes. For example, '${last_index_time}'.

Stray content deletion

Stray content deletion is a plugin feature that deletes documents from a content collection that are not rediscovered on subsequent crawls after the first crawl. This process removes stale documents from the content collection and is enabled by default. Stray content deletion must be configured by the user because native SQL does not provide the ability to retrieve rows that have been deleted from a table. If you are using Fusion 5.18.0 or later, stray content deletion behavior is determined by the Enable Stray Content Deletion settings built into Fusion’s core datasource settings, available on every V2 and Pro connector. If system-level stray content deletion is enabled and you have a delta query configured, saving the datasource fails with a validation error. Resolve the conflict by adjusting the system setting or removing the delta query. If you are using the JDBC V2 2.8.0 connector or later with a Fusion version earlier than 5.18.0, then the stray content deletion behavior is automatically determined by your delta query configuration.

When no delta query is configured, stray content deletion is enabled by default.
When a delta query is configured, stray content deletion is disabled because delta queries only track changes, not deletions.

Using a delta query to perform incremental crawling only returns new and modified items. If stray content deletion is enabled when the delta query is run, unmodified items that are still valid are deleted.

Binary content indexing

Binary content indexing is available in versions 2.7.0 and later of the JDBC V2 connector when using MSSQL or Oracle databases.

The JDBC V2 connector supports binary content indexing, which enables full-text search of documents, images, and other binary data stored in database BINARY, VARBINARY, IMAGE, or BLOB columns. To use binary content indexing:

Navigate to the JDBC connector.
In the Query field, ensure that your SQL query specifies the columns explicitly with primary keys. A query such as SELECT * is not supported in binary content indexing. The queries are case-sensitive.
Select Binary content settings. Two more text boxes display.
Binary content indexing settings
In the Binary content column name field, enter the database column that contains your binary content. This field is required if you are using binary content indexing. The value is case sensitive.
If your database contains a separate column with file names, enter that column name in the Binary file column name field.
If you do not enter a column name, the JDBC connector automatically attempts to detect a column name based on common naming practices. To prevent an incorrect column name in this value, enter a value manually.
Click Save.
Start another crawl to begin indexing the binary content.

After the crawl is completed, verify that your binary content has been indexed properly. To verify your indexing status:

Navigate to the Query Workbench
A list of documents displays. Click Show fields next to a document to view the indexing results.
The binary content is stored in a text field. The format varies depending on the type of binary content that was indexed. Verify that the contents display.

The default field for binary content is body_t. You can edit your index pipeline if you want this data to be indexed into a different field.

Processor flow diagram

The following diagram represents the flow for crawls. Incremental crawls follow the same basic flow as full crawls, except that they emit a page candidate from a delta SQL query if provided. If no delta query is provided, then incremental crawls use the original query.

Configuration

When entering configuration values in the UI, use unescaped characters, such as \t for the tab character. When entering configuration values in the API, use escaped characters, such as \\t for the tab character.

Concepts

Connectors

Developers

Downloads

Add a JDBC driver to Fusion

How to upload a JDBC driver using the Fusion UI

How to install a JDBC driver using the API

Supported JDBC drivers

MySQL

Postgresql

Microsoft SQL Server and Azure SQL Service

Oracle database

IBM DB2

Authentication

Remote connectors

Crawl behavior

DB2 notes

Nested queries

Incremental crawl

Special incremental crawl variable

Stray content deletion

Binary content indexing

Processor flow diagram

Configuration

​Add a JDBC driver to Fusion

​How to upload a JDBC driver using the Fusion UI

​How to install a JDBC driver using the API

​Supported JDBC drivers

​MySQL

​Postgresql

​Microsoft SQL Server and Azure SQL Service

​Oracle database

​IBM DB2

​Authentication

​Remote connectors

​Crawl behavior

​Pagination

​Automatic pagination

​DB2 notes

​Special variables for pagination

​Nested queries

​Incremental crawl

​Special incremental crawl variable

​Stray content deletion

​Binary content indexing

​Processor flow diagram

​Configuration

Add a JDBC driver to Fusion

How to upload a JDBC driver using the Fusion UI

How to install a JDBC driver using the API

Supported JDBC drivers

MySQL

Postgresql

Microsoft SQL Server and Azure SQL Service

Oracle database

IBM DB2

Authentication

Remote connectors

Crawl behavior

Pagination

Automatic pagination

DB2 notes

Special variables for pagination

Nested queries

Incremental crawl

Special incremental crawl variable

Stray content deletion

Binary content indexing

Processor flow diagram

Configuration