How to connect your favorite Data App to Data Cloud

Data Cloud, more than any other Salesforce Cloud, is positioned to be used with external applications. It is engineered to hold both structured and unstructured data, including non-original Salesforce data at scale; so that it provides a unified and complete view of the customer with added enrichment data that supports a full set of use cases employing business rules automation, predictions, and generative AI.
In this blog, we provide an example of connecting to the versatile enterprise capable Database tool, DBeaver, to your Data Cloud org. We make use of Salesforce’s Data Cloud JDBC driver to do so. The aim is to show the reader how relatively easy it is to get going with external tools to gain insights from your data.

This blog is aimed at Trailblazers who would be happy to attempt setup configuration tasks and have an interest in using their go-to data tools to read Data Cloud data.

Below is a diagram showing how your client app is going to communicate with your Data Cloud org.
The client app:

• uses a connected app for authorization
• retrieves a list of Data Cloud objects
• sends SQL queries to those objects
• interprets the results returned by Data Cloud

Download and install the DBeaver Application

DBeaver is a Java-based application which comes bundled with its own OpenJDK version. In this example, we are using DBeaver lite but please use your favorite DBeaver product for your enterprise. Follow the installation instructions for your target platform to install it. For Windows and Mac it’s a fairly simple installation, though it is more complicated for some flavors of Linux.

Set up your Salesforce Connected Application

To connect from DBeaver, you need a Salesforce user that has:

• permissions to create a connected app in your Data Cloud org. Your admin can give you these permissions or have your admin do this for you.
• access to Data Cloud objects and permissions to query them.

Ensure that your org has support for the OAuth 2.0 username password flow turned on. In the OAuth and OpenID connect settings select Allow OAuth Username-Password Flows.

Connected Applications are quite a complex topic, they manage access to Salesforce Cloud for external applications and determine what type of permissions and policies are applied for that external application. The information provided here is for a simple starter example for DBeaver connected app configuration that nonetheless provides a solid entry into the topic.

1. In your Data Cloud Org, go to Setup, enter App Manager, click New Connected App
2. Enter the values in the following screen and select Enable OAuth Settings.
3. Add Callback URL(s) for DBeaver (see help here for URLs) and select the OAuth scopes as shown below.:

4. Click on Save, and you will get a message informing you that changes can take up to 10 minutes, then click on Continue.
5. Click on Manage Consumer Details.

6. Copy the Consumer Key and Secret and save it. You’ll need them later.

Using the DC JDBC driver to create a Connector in DBeaver

JDBC is a common standard interface that is used by client applications to connect to databases, execute SQL queries, and explore results.

1. Download the latest release of Data Cloud JDBC driver from here. At the time of this blog, the latest release was the jar: Salesforce-CDP-jdbc-1.19.2.jar
2. Save it somewhere it is easily accessible to the DBeaver application.
3. Open the DBeaver application. Although the preferred and easiest way to connect to Data Cloud is through DBeaver’s new Salesforce Data Cloud connector (as shown below) where the download of the JDBC jar is automated and the correct properties are present, we are NOT going to use it, instead, we are going to use the Generic JDBC Driver Connector to provide a better illustration of the process across various applications.

For the Generic JDBC Driver :

1. Go to the top menu item Database > Driver Manager. Click on New.
2. For Driver Type, select Generic.
3. For Driver Name, enter a driver name. DC_JDBC_Driver for example.
4. For the Class name, add the class name of the driver: com.salesforce.cdp.queryservice.QueryServiceDriver.

5. Click the Libraries tab. Navigate to the jdbc jar file and add it.
6. Click Classpath. Navigate to the jdbc jar file and add it. It may well be in the classpath already or can be added to the system classpath outside of the DBeaver but to be sure add it here.

7. Add the clientId and clientSecret properties along with the corresponding consumerKey and consumerSecret values from the connected app.

Once the driver has been created, you can create a database connection based on that driver.

1. Click the Projects tab and expand the General folder.
2. Select Connections > Create and select the driver name, for example DC_JDBC_Driver.
3. click Next >.

4. In the main tab:
   • Add the JDBC URL jdbc:queryService-jdbc:https://login.salesforce.com.
   • Add user login credentials Username and Password.

Connection error resolution

There may be some policies enforced on your Org that are incompatible with your connected app config. A good example is IP Restrictions and whether they need to be relaxed for that App overriding your user IP restrictions, or that you have not enabled OAuth username password flows as described previously

IP Restrictions can be found by selecting Manage on the connected app. You can then select Edit Policies to change it.

Explore your data

If the credentials have been added correctly then the Data Lake Objects (_dll) and Data Lake Models (_dlm) are shown as available for querying.

Try some SQL to gain insights from your data. For example, try returning data from a join between two DLOs:

SELECT a.Name__c, 
c.AccountId__c, 
c.Phone__c , 
c.AssistantName__c, 
c.Email__c FROM Contact_00DHn000002UpPv__dll c Join Account_00DHn000002UpPv__dll a on c.AccountId__c = a.Id__c

Written by

Terence Wilson

See author's posts