Connect to Salesforce from KNIME Analytics Using JDBC Driver

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.

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.

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.

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.

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.

    

   

    

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.

    

   

    

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.

    

   

    

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.

     

    

     

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.

 

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.