Connect to Salesforce from KNIME Analytics Using JDBC Driver

Introduction

KNIME Analytics Platform is a data analytics, reporting and integration application which allows you to create visual data flows with help of nodes for ETL, modeling and visualization. KNIME can read from any database, thanks to its JDBC support. In this tutorial we will walk you through on how you can use the Progress DataDirect Salesforce JDBC driver to access your Salesforce data from KNIME analytics platform.

Prerequisites

We assume that you have installed KNIME Analytics Platform on your machine. If you haven’t, please visit KNIME Analytics to download and install the product.

Install Salesforce JDBC Driver

1. Download Salesforce JDBC driver from here.
2. Once the package is downloaded, unzip the package and run the Setup.exe.
3. The installation process will be simple, just follow the instructions. Most of the time the default settings would be sufficient to install the driver successfully.

Configure KNIME Analytics

  1. With the driver installed successfully, let’s configure KNIME Analytics platform to leverage this driver. To do so, Open KNIME Analytics platform, go to File-> Preferences -> KNIME ->Databases.
  2. Click on Add file and browse the path to Progress DataDirect JDBC driver, it’s generally in the path

 

C:\Program Files\Progress\DataDirect\Connect_for_JDBC_51\lib\sforce.jar.

 

Below is a screenshot of the Preferences for your reference.


Click on Add file and browse the path to Progress DataDirect JDBC driver

Create Workflows

  1. Now that we have configured the DataDirect salesforce JDBC driver, let’s go ahead and create a workflow. Go to File->New->New KNIME Workflow and click on Next. Name the workflow as you wish and click on the Finish button.
  2. From the Node Repository, under Database -> Read/Write ->Choose ‘Database Reader’ and drag it to the Workflow.
  3. Right click on the Database Reader, choose configure. Configure your connection to Salesforce as shown below.
    1. Database driver: com.ddtek.jdbc.sforce.SForceDriver
    2. Database URL: jdbc:datadirect:sforce://login.salesforce.com;SecurityToken=<Your Security Token, if needed>
    3. UserName: <Your Salesforce Username>
    4. Password: <Your Salesforce Password>
  4. After you have configured your connection information, click on Fetch Metadata, you should see Schema and Tables as shown below.

     

    Click on Fetch Metadata

     

  5. On the Right side of the Database browser, you will find a text area where you can write your SQL query. Let’s go with the query “SELECT * FROM ACCOUNT” as show in above screenshot and click on Apply.
  6. Now is the time to check if we are getting the data properly. Go to Node Repository and under Views, drag Interactive Table on to workflow. Connect the output of Database reader to the input of Interactive Table.
  7. Select both of the nodes and Right click and choose to Execute(F7) the workflow. Wait until the status on the nodes to turn green as shown below.

     

    Choose to Execute (F7) the workflow

     

  8. Now right click on Interactive Table node and choose View: Table view. This should open the data fetched in a tabular format as shown below.

     

    Choose View- Table view

     

  9. Now that we have checked that we are able to access the data properly, let’s go ahead and create a simple Pie chart. Under Node Repository-> Views -> Drag the Pie Chart(interactive) on to workflow.
  10. Connect the output of Database Reader to input of Pie Chart(interactive) as shown below.

     

    Pie Chart (interactive)

     

  11. Right click on Pie chart(Interactive), and Execute(F7) the node and wait until the node status changes to green.
  12. Right click on Pie chart(Interactive), and choose view: Pie Chart, and you should see the pie chart as shown below.

See the pie chart as shown below

 

Next Steps

We hope this tutorial has helped you get started on using Progress DataDirect Salesforce JDBC driver with KNIME analytics. Feel free to try Progress DataDirect JDBC Drivers for Salesforce, CDH Hive, Eloqua, Marketo and many more as per your use case when you want to access data from KNIME analytics. Keep in mind that the procedure remains almost the same, all you would have to change is the JDBC configuration details when you are configuring it in Database reader nodes as required.

JDBC TUTORIAL

Connect to Salesforce from KNIME Analytics Using JDBC Driver

DataDirect
connectors

Connect any data source to any application

DataDirect
Tutorials

Discussions, tips and tricks for
DataDirect Connect drivers