JDBC Producer destination setting in the Streamsets Data Collector.

One of the most used managed destinations in the SDC is the JDBC Producer. It allows data writing to a relational database table using a JDBC connection. The SDC release I am referring to in this post is the 1.5.1.2 running in the JVM 8.

Installing a specific JDBC driver.

In order to insert data in a database table SDC requires the specific JDBC driver for the database you need to use. This applies to the JDBC consumer origin as well.
The first time you plan to add  a JDBC Producer destination to a pipeline you need to create a local directory in the SDC host machine external to the SDC installation directory. Example: /home/sdc-user/sdc-extras
Then create the following sub-directory structure for all of the JDBC drivers:
/home/sdc-user/sdc-extras/streamsets-datacollector-jdbc-lib/lib/
Finally copy the JDBC driver in that folder.
Now it is time to make SDC aware of this directory. First you have to add the STREAMSETS_LIBRARIES_EXTRA_DIR environment variable and make it pointing to the JDBC drivers directory. You need to add the following line to the $SDC_DIST/libexec/sdc-env.sh or the $SDC_DIST/libexec/sdcc-env.sh file:

export STREAMSETS_LIBRARIES_EXTRA_DIR="/home/sdc-user/sdc-extras/"

By default SDC comes with the Java Security Manager enabled. In that case you have to update the Data Collector security policy to include the driver directory. You need to edit the $SDC_CONF/sdc-security.policy file adding the following lines:

// user-defined additional driver directory
grant codebase "file:///home/sdc-user/sdc-extras/-" {
  permission java.security.AllPermission;
};

Restart SDC in order to apply the configuration changes.

JDBC Producer configuration.

Here is the standard configuration for a JDBC Producer destination:
Tab General:

• Name: the name of the stage.
• On Record Error: to set what to do in case of errors for one or more records.

Tab JDBC:

• JDBC Connection String: the connection string to the database. It depends by the database you are using. Example: in case of PostgreSQL it has the following syntax:

              jdbc:postgresql://<db_host>:<port>/<db_name>
       
          Please check the specific JDBC official documentation you are going to use for the correct string syntax.

• Table Name: the name of the table into which the records would be inserted.
• Use Multi-Row Insert: uncheck this box if you want the fields written to columns of the same name. 
• Use Credentials: check this box if authentication is enabled for the database. 

Tab Credentials:

• Username: the username to connect to the database.
• Password: the password to connect to the database.

Tab Advanced:
      Set here all of the properties for the connection pool.