Salesforce¶
Applies from release 2020.4
Alation has certified the Salesforce DB with the following driver to accommodate additional features with the latest version of Salesforce API.
data.jdbc.salesforce.SalesforceDriver.cdata.jdbc.salesforce
Alation will provide the above mentioned CData driver for Salesforce.
Scope of Support¶
Supported as Custom DB with the CData Driver
Metadata Extraction (MDE)
Automated MDE
Compose
Sampling and Profiling
Ports¶
Port 443 must be open.
Required Information¶
JDBC driver used to connect to the database: CData JDBC Driver for Salesforce
JDBC URI for the Salesforce data source
OAuth Client ID
OAuth Client Secret
OAuth Access Token
RTK - Use the RTK parameter only if you have the RTK provided by Alation.
Service Account - Username and Password
Security Token
Construct the URI¶
Pre-Configuration¶
Perform the following steps to generate OAuth Client ID and OAuth Client Secret:
Log in to Salesforce and create a User ID and Password. Refer to Usernames and Passwords.
Generate the OAuth Client ID/Consumer Key and OAuth Client Secret/Consumer Secret:
Create an app, refer to Create a Connected App.
Go to Apps > App Manager. Click the dropdown icon of the created app and select View.
Copy the OAuth Client ID/Consumer Key and OAuth Client Secret/Consumer Secret.
Get the Security Token. Refer to Security Token. Security Token must be provided in the General Settings > Network Connection > Properties field, if the IP is not set as Trusted IP.
JDBC URI Format¶
Use the following format of the JDBC URI if you are not using an OAuth Connection:
salesforce:RTK=<RTK_Code>
Example:
salesforce:RTK=444752465641535552425641454E545042424D333236323900000000000000000000000000000000414C4154494F4E5800005559475655474E4E464242370000
JDBC URI for OAuth Connection¶
Use the following format of the JDBC URI if OAuth connection is used:
salesforce:InitiateOAuth="REFRESH";OAuthClientId=<ID>;OAuthClientSecret=<KEY>;RTK=<RTK_Code>
Example:
salesforce:InitiateOAuth="REFRESH";OAuthClientId=3MVG9d8..z.hDcPKp5929I72FP04v6Gg9UyrPok7Wrtm5ph2Yf9nKl6l_Dr_IvN7l4gg361jh8tzyB15BvMVU;OAuthClientSecret=6C7CE7939B00D9C72811AFCC2F0E49E0CB8F1F829E31AB6B941B7C64181FD6B0;RTK=444752465641535552425641454E545042424D333236323900000000000000000000000000000000414C4154494F4E5800005559475655474E4E464242370000
After you have successfully added the source, you will need to connect to it in Compose, obtain the OAuth Access Token and update the URI on the Data Source Settings > General Settings page. This is described in the sections below.
Preliminaries¶
Add the CData to Alation¶
Set Trusted IP¶
Before setting up the datasource connection in Alation, make sure that the IP of the Alation instance is set as a trusted IP in Salesforce. Refer to Set Trusted IP.
Steps in Alation¶
Step 1: Add a Data Source¶
Add a new Data Source on the Sources page.
Step 2: Set up the Connection¶
On the Add a Data Source screen of the wizard, specify:
Database Type: Custom DB
JDBC URI: Provide any valid URI with port number 443. This is only a temporary URI to move through the Add Data Source wizard.
Example:
https://ap5.salesforce.com:443Note
The correct JDBC URI must be provided on the General Settings tab of the data source Settings page.
Select Driver: select the JDBC driver for Salesforce from the Select Driver drop-down list:
cdata.jdbc.salesforce.SalesforceDriver.cdata.jdbc.salesforce.Do not select the Use Kerberos checkbox.
Click Save and Continue. The next wizard screen Set Up a Service Account will open.
Step 3: Enter Service Account Credentials¶
Select the Yes radio button.
Provide the Username and Password of the service account created for Alation.
Click Save and Continue. The next wizard screen Configure Your Data Source will open.
Step 4: Configure Your Data Source¶
Click Skip this Step. After this step, you are navigated to the Settings page of your data source.
Step 5: General Settings¶
On the General Settings section, change the temporary URI to the correct URI as explained in the Construct the URI section. Provide the Security Token in the Properties fields if the IP is not set as Trusted IP, refer to Set Trusted IP.
Click the Test button to test the connection.
If you are using an OAuth connection, perform the following:
Go to Compose and connect to your Salesforce source using the User ID and Password.
Click the plus icon and provide the URI using the format described in the JDBC URI for OAuth Connection section. Click Add & Use.
Note
InitiateOAuth parameter in the URI must be set to OFF.
Run the following query in Compose. As “<call_back_url>”, provide the callback URL that was provided during the OAuth App creation:
EXEC GetOAuthAuthorizationURL @CallbackUrl='<call_back_url>'Example:
EXEC GetOAuthAuthorizationURL @CallbackUrl='http://localhost:3333'This should return a URL as result.
Copy the URL and run it in a new browser. You will see an error message as shown below and a code in the URL. Copy the code and decode it using an URL decoder.
Copy the decoded code and add it as a value to the Verifier parameter in the below query.
EXEC GetOAuthAccessToken @Verifier=<Code_Copied_from_URL>Example:
EXEC GetOAuthAccessToken @Verifier=32217c96149fda1e96This should return the OAuth Access Token as result.
Copy the OAuth Access Token.
Go to the Settings > General Settings page and edit the connection URI: specify the URI in the correct format and add the OAuth Access Token (refer to the JDBC URI for OAuth Connection section).
Note
InitiateOAuth parameter must be set to Refresh while adding the generated OAuthAccessToken to the URI.
Pattern:
salesforce:InitiateOAuth="REFRESH";OAuthClientId=<ID>;OAuthClientSecret=<KEY>;OAuthAccessToken=<Access_Token>;RTK=<RTK_Code>Example:
salesforce:InitiateOAuth="REFRESH";OAuthClientId=3MVG9d8..z.hDcPKp5929I72FP04v6Gg9UyrPok7Wrtm5ph2Yf9nKl6l_Dr_IvN7l4gg361jh8tzyB15BvMVU;OAuthClientSecret=6C7CE7939B00D9C72811AFCC2F0E49E0CB8F1F829E31AB6B941B7C64181FD6B0;OAuthAccessToken=Ay6CaEUwVim409lF5jp69bi8CK3JHyGBo0T915AwFEIfngegJNFU89FKJDF33lsd_SM9jH9;RTK=444752465641535552425641454E545042424D333236323900000000000000000000000000000000414C4154494F4E5800005559475655474E4E464242370000Save the change. After providing the URI in the required format, you can configure and perform metadata extraction.
Metadata Extraction¶
Configure and perform metadata extraction and verify the results:
In Settings > Custom Settings, you can set the Catalog Object Definition to Schema.Table to remove any driver-imposed prefixes from the metadata object names
Automatic full and selective MDE is supported
Sampling and Profiling¶
Configure and perform Sampling and Profiling :
Users can run a sample for an individual table on the Samples tab of the Table Catalog page or profile an individual column on the Overview tab of the Column page.
Automatic full and selective Profiling is supported.
Use the Per-Object Parameters in Settings tab to specify which objects to profile.
Custom query-based Sampling is supported. Custom Query-Based Sampling allows you to provide a custom query for profiling each specific table.
Deep Column Profiling (Profiling V2 ) is supported.
Query Log Ingestion¶
Not applicable.
Compose¶
You can optionally use Compose with the Salesforce Custom DB data source. Use the Schema.Table format for writing queries.
Troubleshooting¶
Logs to collect/review:
For logs related to MDE: taskserver.log, taskserver_err.log.
For logs related to Compose: connector.log, connector_err.log.
For any other errors: alation-error.log, alation-debug.log
Metadata Extraction¶
During Metadata Extraction from a Salesforce Sandbox data source, if the Error serializing table objects: null or Error serializing table objects: Error iterating schema children is displayed:
Append the parameter UseSandbox=true to the URI. Failed test result for the Network Connection is expected.