Run federated queries on Salesforce Data Cloud

Article
09/17/2024

This article describes how to set up Lakehouse Federation to run federated queries on Salesforce Data Cloud data that is not managed by Azure Databricks. To learn more about Lakehouse Federation, see What is Lakehouse Federation?.

To connect to your Salesforce Data Cloud database using Lakehouse Federation, you must create the following in your Azure Databricks Unity Catalog metastore:

A connection to your Salesforce Data Cloud database.
A foreign catalog that mirrors your Salesforce Data Cloud database in Unity Catalog so that you can use Unity Catalog query syntax and data governance tools to manage Azure Databricks user access to the database.

Before you begin

Workspace requirements:

Workspace enabled for Unity Catalog.

Compute requirements:

Network connectivity from your compute resource to the target database systems. See Networking recommendations for Lakehouse Federation.
Azure Databricks compute must use Databricks Runtime 15.2 or above and Shared or Single user access mode.
SQL warehouses must be pro or serverless and must use 2024.30 or above.

Permissions required:

To create a connection, you must be a metastore admin or a user with the CREATE CONNECTION privilege on the Unity Catalog metastore attached to the workspace.
To create a foreign catalog, you must have the CREATE CATALOG permission on the metastore and be either the owner of the connection or have the CREATE FOREIGN CATALOG privilege on the connection.

Additional permission requirements are specified in each task-based section that follows.

Create a Salesforce connected app

Salesforce connected apps allow an external application to integrate with Salesforce using APIs and standard protocols. This section describes how to create a connected app using SSO to allow Databricks to authenticate with Salesforce.

Note

For more detailed instructions, see Create a Connected App in the Salesforce Data Cloud documentation.

To create a Salesforce connected app, do the following:

In the upper-right of Data Cloud, click Setup.
Under Platform Tools, click Apps > App Manager.
Click New Connected App.
Enter a Name and a Contact email address.
Enable OAuth settings:
1. Enter the Callback URL, in the following format: https://<databricks_instance_url>/login/oauth/salesforce.html. For example: https://cust-success.cloud.databricks.com/login/oauth/salesforce.html.
2. (Optional) If you plan to use SQL to create the Azure Databricks connection and the foreign catalog in the next step, your Salesforce Connected App also needs to support the redirect URI https://login.salesforce.com/services/oauth2/success. This isn’t needed if you plan to use Catalog Explorer to create the Azure Databricks connection and the foreign catalog. Databricks recommends using Catalog Explorer because it requires fewer manual steps than other methods.
3. Add the following Scopes:
  - Access all Data Cloud API resources (cdp_api)
  - Manage user data via APIs (api)
  - Perform ANSI SQL queries on Data Cloud data (cdp_query_api)
  - Perform requests at any time (refresh_token, offline_access)
4. Click Save.
5. Click Continue.
On the Connected App overview page, click Manage Consumer Details. You will prompted to authenticate.
Upon successful authentication, Consumer key and Consumer secret are revealed. Save these values. You’ll need them when you create an Azure Databricks connection.

Create an Azure Databricks connection

A connection specifies a path and credentials for accessing an external database system. To create a connection, you can use Catalog Explorer or the CREATE CONNECTION SQL command in an Azure Databricks notebook or the Databricks SQL query editor.

Note

You can also use the Databricks REST API or the Databricks CLI to create a connection. See POST /api/2.1/unity-catalog/connections and Unity Catalog commands.

Permissions required: Metastore admin or user with the CREATE CONNECTION privilege.

Catalog Explorer

In your Azure Databricks workspace, click Catalog.
In the left pane, expand the External Data menu and select Connections.
Click Create connection.
Enter a user-friendly Connection name.
Select a Connection type of Salesforce Data Cloud.
Enter the following connection properties for your Salesforce Data Cloud.
- Auth type: OAuth
- Is sandbox false
- (OAuth) Client secret: Salesforce connected app consumer secret
- (OAuth) Client ID: Salesforce connected app consumer key
- (OAuth) Client scope: cdp_api api cdp_query_api refresh_token offline_access
Click Log in with Salesforce.
(OAuth) You are prompted to sign in to Salesforce Data Cloud using your SSO credentials.
Upon successful login you are directed back to the Databricks Create Connection page. The Log in with Salesforce button has been replaced with a Successfully authorized message.
(Optional) Add a comment.
Click Create.

SQL

Databricks recommends using Catalog Explorer to create the connection and the foreign catalog because it requires fewer manual steps than other methods.

If you plan to use SQL to create the Azure Databricks connection and the foreign catalog, your Salesforce Connected App needs to support the redirect URI https://login.salesforce.com/services/oauth2/success. This isn’t needed if you use Catalog Explorer.

Generate PKCE code verifier and code challenge codes. You can do this using an online tool such as https://tonyxu-io.github.io/pkce-generator/ or by running the following Python script:

%python

import base64
import re
import os
import hashlib

code_verifier = base64.urlsafe_b64encode(os.urandom(40)).decode('utf-8')
code_verifier = re.sub('[^a-zA-Z0-9]+', '', code_verifier)

code_challenge = hashlib.sha256(code_verifier.encode('utf-8')).digest()
code_challenge = base64.urlsafe_b64encode(code_challenge).decode('utf-8')
code_challenge = code_challenge.replace('=', '')
print(f"pkce_verifier  = \"{code_verifier}\"")
print(f"code_challenge = \"{code_challenge}\"")

Visit the following URL and authenticate with your Salesforce credentials to get the authorization_code (replace <client_id> and <code_challenge> with your parameters).
```
https://login.salesforce.com/services/oauth2/authorize
?client_id=<client_id>
&redirect_uri=https://login.salesforce.com/services/oauth2/success
&response_type=code
&code_challenge=<code_challenge>
```
A URL-encoded authorization code is visible in the redirected URL.

Run the following in a notebook or the Databricks SQL query editor:

CREATE CONNECTION '<Connection name>' TYPE salesforce_data_cloud
OPTIONS (
  client_id '<Consumer key from Salesforce Connected App>',
  client_secret '<Consumer secret from Salesforce Connected App>',
  pkce_verifier '<pkce_verifier from the last step>',
  authorization_code '<URL decoded `authorization_code`, should end with == instead of %3D%3D>',
  oauth_redirect_uri "https://login.salesforce.com/services/oauth2/success",
  oauth_scope "cdp_api api cdp_query_api refresh_token offline access",
  is_sandbox "false"
  );

Databricks recommends that you use Azure Databricks secrets instead of plaintext strings for sensitive values like credentials. For example:

CREATE CONNECTION '<Connection name>' TYPE salesforce_data_cloud
OPTIONS (
  client_id secret ('<Secret scope>','<Secret key client id>'),
  client_secret secret ('<Secret scope>','<Secret key client secret>'),
  pkce_verifier '<pkce_verifier from the last step>',
  authorization_code '<URL decoded `authorization_code`, should end with == instead of %3D%3D>',
  oauth_redirect_uri "https://login.salesforce.com/services/oauth2/success",
  oauth_scope "cdp_api api cdp_query_api refresh_token offline access",
  is_sandbox "false"
  );

For information about setting up secrets, see Secret management.

Create a foreign catalog

A foreign catalog mirrors a database in an external data system so that you can query and manage access to data in that database using Azure Databricks and Unity Catalog. To create a foreign catalog, you use a connection to the data source that has already been defined.

To create a foreign catalog, you can use Catalog Explorer or the CREATE FOREIGN CATALOG SQL command in an Azure Databricks notebook or the SQL query editor.

Note

You can also use the Databricks REST API or the Databricks CLI to create a catalog. See POST /api/2.1/unity-catalog/catalogs and Unity Catalog commands.

Permissions required: CREATE CATALOG permission on the metastore and either ownership of the connection or the CREATE FOREIGN CATALOG privilege on the connection.

Catalog Explorer

In your Azure Databricks workspace, click Catalog to open Catalog Explorer.
In the upper right, click Create Catalog.
Enter the following properties for your Salesforce Data Cloud catalog.
- Catalog name: A user-friendly name for the catalog.
- Type: Foreign.
- Connection name: The name of the connection the catalog will be created on.
- Dataspace: A Salesforce data space.
Click Create.

SQL

Run the following SQL command in a notebook or the SQL query editor. Items in brackets are optional.

CREATE FOREIGN CATALOG [IF NOT EXISTS] '<catalog-name>' USING CONNECTION '<connection-name>'
OPTIONS (dataspace '<dataspace>');

Replace the following values:

<catalog-name>:
<connection-name>:
<dataspace>: Salesforce data space. For example, default.

Supported pushdowns

The following pushdowns are supported:

Filters
Projections
Limit
Aggregates
Offset
Cast
Contains, Startswith, Endswith

Data type mappings

When you read from Salesforce Data Cloud to Spark, data types map as follows:

Salesforce Data Cloud type	Spark type
Boolean	BooleanType
Date	DateType
Datetime	TimestampType
Email, Phone, Text, Url	StringType
Number, Percent	DecimalType(38, 18)

Limitations

Only one Salesforce Data Space per Databricks catalog is supported.

Share via

Run federated queries on Salesforce Data Cloud

Before you begin

Create a Salesforce connected app

Create an Azure Databricks connection

Catalog Explorer

SQL

Create a foreign catalog

Catalog Explorer

SQL

Supported pushdowns

Data type mappings

Limitations

Feedback

Additional resources