JDBC Lookup

Query pipeline stage configuration specifications

The JDBC Lookup query pipeline stage is used to call out to a database as part of a pipeline stage, to inject results into either the context/request or the pipeline document. For example, if you needed to look up a user from a DB and add their profile information onto a request for downstream use in a pipeline, the JDBC Lookup Stage would facilitate this.

You must first Upload a JDBC Driver to Lucidworks Search.

Upload a JDBC Driver to Lucidworks Search

The JDBC connector fetches documents from a relational database via SQL queries. Under the hood, this connector implements the Solr DataImportHandler (DIH) plugin.

Lucidworks Search stores JDBC drivers in the blob store. You can upload a driver using the Lucidworks Search UI or the Blob Store API. Learn more about the JDBC V2 connector.

How to upload a JDBC driver using the Fusion UI

1. In the Lucidworks Search UI, navigate to System > Blobs.

2. Click Add.

3. Select JDBC Driver.
   The “New ‘JDBC Driver’ Upload” panel appears.

   

4. Click Choose File and select the .jar file from your file system.

   

5. 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

1. Upload the JAR file to Lucidworks Search’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 https://EXAMPLE_COMPANY.b.lucidworks.cloud/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"
     }
   }
   

   Lucidworks Search 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

2. To verify the uploaded driver, run:

   curl -u USERNAME:PASSWORD https://EXAMPLE_COMPANY.b.lucidworks.cloud/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"
     }
   }
   

This stage supports asynchronous processing.

Example

An example of setup for a JDBC Lookup query pipeline stage.

Upload stage config via POST to Lucidworks Search REST API endpoint api/query-stages/instances

curl -u USERNAME:PASSWORD -X POST -H "Content-Type: application/json" -d '{"id": "jdbc-quer","type":"jdbc-query-lookup","driver":"postgresql-9.3-1101-jdbc4.jar","connection":"jdbc:postgresql:database","username":"user","password":"password1","preparedStatement":"select ID as id from DATABASE;"}' http://EXAMPLE_COMPANY.lucidworks.cloud/api/query-stages/instances

Replace EXAMPLE_COMPANY with the name provided by your Lucidworks representative.

Response

{
  "type" : "jdbc-query-lookup",
  "id" : "jdbc-quer",
  "driver" : "postgresql-9.3-1101-jdbc4.jar",
  "connection" : "jdbc:postgresql:database",
  "username" : "user",
  "password" : "password1",
  "preparedStatement" : "select ID as id from DATABASE;",
  "fetchSize" : -1,
  "join" : true,
  "rows" : 10,
  "skip" : false,
  "label" : "jdbc-query-lookup",
  "type" : "jdbc-query-lookup"
}

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.