Exporting Data via JDBC with CSV Format

Exporting data with the JDBC delivery method requires the use of the CSV format. Setup for this involves the following steps in the Export Manager or in an OIEP:

  • Add required drivers for JDBC
  • Configure properties in the sharedconfig.properties file
  • Select format of CSV, using the required settings
  • Map Data, making sure to include the required action field
  • Select Delivery Method of JDBC, using the necessary settings

Each of these steps is described below.

For example configurations when exporting classifications or assets, refer to the Classification and Asset Configuration Examples section below.

For details on a use case in which JDBC is used to update analytics data, refer to the Analytics using JDBC Example topic in the Analytics guide here.

Install Required Drivers

JDBC specification 4.1-compliant drivers should be placed in a directory accessible from all application servers. These drivers can then be made available for the delivery plugin via the dynamic properties JDBCDeliveryPlugin.DriverPath.[n] and JDBCDeliveryPlugin.DriverClass.[n]. For more information regarding applicable Java drivers, review the RDBMS vendor's homepage on the web.

Configure Properties

The options available in the following dropdown parameters on the JDBC delivery method are supplied by the sharedconfig.properties file on the STEP application server. Multiple entries can be added using the dynamic properties. Each configuration entry must have a unique integer (JDBCDeliveryPlugin.DriverPath.1, JDBCDeliveryPlugin.DriverPath.2, JDBCDeliveryPlugin.DriverPath.3, etc.). When duplicate integers exist, only the last value is displayed in the dialog.

Use the following steps to supply data for the parameters displayed above:

  1. Prior to configuration, clicking the Driver Location dropdown parameter displays the required property name. Provide a selection for the dropdown parameter via the sharedconfig.properties file on the STEP application server using the case-sensitive JDBCDeliveryPlugin.DriverPath.[n] property. As an example:
JDBCDeliveryPlugin.DriverPath.1 = L:/shared/mysql-connector-java-5.1.42-bin.jar

In this example, the drivers are stored on the application server's L:/shared drive.

  1. Prior to configuration, clicking the Driver Class dropdown parameter displays the required property name. Provide a selection for the dropdown parameter via the sharedconfig.properties file on the STEP application server using the case-sensitive JDBCDeliveryPlugin.DriverClass.[n] property. As an example:
JDBCDeliveryPlugin.DriverClass.1 = com.mysql.jdbc.Driver

In this example, the drivers class used is 'com.mysql.jdbc.Driver.'

  1. Prior to configuration, clicking the Database URL dropdown parameter displays the required property name. Provide a selection for the dropdown parameter via the sharedconfig.properties file on the STEP application server using the case-sensitive JDBCDeliveryPlugin.URL.[n] property. 
JDBCDeliveryPlugin.URL.1 = jdbc:mysql://localhost:3306/mydb

In this example, the database URL used is 'jdbc:mysql://localhost:3306/mydb.'

Select Format

When exporting data using the JDBC delivery option it is important that, in the 'Select Format' step, users select 'CSV.' JDBC does not support any other formats in STEP. In order to enable proper functioning of the JDBC delivery option, the configuration of the 'CSV' format option should look like this:

Map Data

The JDBC delivery option uses export mapping to allow STEP data to be inserted or updated ("upsert") into, or deleted from ("delete") a destination database table. When a configured object in the exported CSV file finds a match in the destination database, the interaction can be configured to either / both:

  • 'upsert' the object, which means the object, if found based on the JDBC Key Columns, will be updated with the information in the exported CSV file, or if not found based on the JDBC Key Columns, the information in the exported CSV file will be inserted into the table in the destination database
  • 'delete' the object, which means that if an object indicated in the exported CSV file is found in the destination database based on the JDBC Delete Key Columns, it will be deleted

For example, consider the following external database sample table:

In the 'Map Data' step, map the required objects considering the following points to ensure the CSV file can be successfully processed:

  • The column headers in the exported CSV file must match the table column headers in the destination database. Map a constant value on the Header row for each mapped object to supply the external database table column header. For example, notice that the external table has a row named 'description' but is mapped to the STEP attribute 'Consumer Short Description.' The header parameter is used to make the exported data match the external table. For details about using the constant value data source, refer to the Constant Value - Data Source Outbound topic here.
  • Create an additional mapped column with the header of 'action' and the appropriate value of either delete or upsert. Use the transformation button to change the text displayed for both the Header and the Value.

Below is an example of an 'upsert' action in Map Data for the external database table shown above. This setup will direct the process to look for three configured attributes (ID, EAN, and Consumer Short Description) in the table in the destination database, and either insert the value (if not found), or update the value (if found):

Important: For the external destination database, the JDBC delivery method assumes that the destination data table has a column used as a key, and a number of columns for values (for example, key value1 value2 [value (n)].) This means a destination data table having only a single column (e.g., ID) will cause the JDBC export to fail, however, a table having at least two (2) columns (e.g., ID and Name) will work properly.

To create mapping for the delete action, the action column would have a value of "delete."

If using an OIEP, one output template would be created to handle Create and Modify events, and a second output template would handle Delete events. In this way, separate mapping is available for each, allowing for one to include the upsert action and the other to include the delete action.

Select Delivery Method

When setting the delivery method for the export, use the following parameters to configure it.

  1. In Select Delivery Method, choose JDBC from the dropdown.

  2. In Driver Location, select from the dropdown one of the paths to the relevant .jar file.

  3. In Driver Class, select from the dropdown one of the pre-configured driver class.

  4. In Database URL, select from the dropdown one of the pre-configured URLs to the destination database.

  5. In Username, enter the username required to access the destination database.

  6. In Password, enter the password required to access the destination database.

  7. In Table Name, enter the name of the table in the destination database to which STEP will be publishing data

  8. In Key Columns, list the names of the columns appearing on both the exported CSV file and the table in the destination database, separated by commas (and no spaces), into which STEP will publish data

  9. In Delete Key Columns, list the names of the columns appearing on both the exported CSV file and the table in the destination database, separated by commas (and no spaces), from which STEP will delete data. The headers contained in this field can differ from the headers in the 'Key Columns' field, but they must also be part of the upsert key definition.

  10. In Convert "NULL", choose Yes if the string “NULL” should be converted to the value null. This may, for instance, be used for clearing a value in a column in the target database. This parameter defaults to No.

Classification and Asset Configuration Examples

When using a format that requires mapping, the following applies to exporting classifications or assets in an OIEP using Choose Data Source = Select Objects:

  • It is not possible to export both classifications and assets in the same CSV file. Instead, create two separate OIEPs - one for classifications and one for assets.

  • In the Object Selection Configuration flipper, if a classification object is selected, and in the Output Templates section, the Format field has Classification selected in the Mapping tab (and relevant data sources are mapped):

    • In the Advanced tab, checking the Only Export Selected Objects option means that only one classification object is exported.

    • In the Advanced tab, checking the Only Export Leaf Objects option means only the children classification objects are exported.

    • In the Advanced tab, checking neither option means both the selected classification object and its children classification objects are exported.

  • In the Object Selection Configuration flipper, if a classification object is selected, and in the Output Templates section, the Format field has Asset selected in the Mapping tab (and relevant data sources are mapped), all assets below that classification object are exported.

Important: If a collection contains both classification objects that have assets below them in addition to other asset objects, and the collection is added to the Object Selection Configuration flipper with Asset selected in the Mapping tab for Output Templates section, then all assets within the collection are exported (including the assets below the classification objects).