Skip to content

API Profiles Example - Salesforce Lightning

Overview

A REST API provides a powerful, convenient, and simple web services API for interacting with the Salesforce Lightning Platform.

A REST resource is an abstraction of a piece of information or an action, such as a single data record, a collection of records, or a query. REST API is based on the usage of resources, their URIs, and the links between them. You use a resource to interact with your Salesforce org. For example, you can:

  • Retrieve summary information about the API versions available to you.
  • Obtain detailed information about a Salesforce object, such as account, user, or a custom object.
  • Perform a query or search.
  • Update or delete records.

API Endpoint URL Example: https://Instance.salesforce.com/services/data/v42.0/query/?q=SELECT+id,name,email,phone+from+Contact -H 'Authorization: Bearer YOUR_ACCESS_TOKEN'

For more information on Salesforce REST API, click here .

Important information

  • Parameters are supplied as part of the URL.
  • The API is very large. Data is returned as a JSON array, and the returned granularity depends on the SQL query.
  • The API uses paging. Each page of responses includes a URL to the next page. It's blank if there are no more pages to fetch.
  • Enable an SSL endpoint in your application server
  • If you're not already a member of the Lightning platform developer community, go to developer.salesforce.com/signup and follow the instructions for signing up for a Developer edition organization.
  • If you already have a Developer edition organization, verify that you have the API permission enabled. Please note, these permission are enabled by default.

The document is organized into following sections:

Getting set up with Salesforce

We first need to check the Salesforce data center connectivity.

In this guide we'll be using um2.salesforce.com as the data center for Salesforce and the HTTPS port is 443. The connectivity with the data center is successfully verified using the Network Connectivity Test shared job in Matillion. Find the Network Connectivity Test in Shared jobs, provide "Address" and "Port Number" detail in Properties and Run the job.

Network Connectivity Test

Network Connectivity Test

See the Salesforce documentation on how to find your regional data centre here.

Set up connected app

Salesforce provides a connected app to connect with the platform with any other application.

A connected app is an application that allows an external application to integrate with Salesforce using APIs and standard protocols.

For detailed information on Authenticating Salesforce with Matillion, see Salesforce Query Authentication here.

Once you've set up your app, you'll be given a Consumer Key and a Consumer Secret for you app.

The basic Salesforce Oauth data flow

Salesforce Authentication Process

Salesforce Authentication Process

With the connected app set up, it's handy to get an idea of how the data flow works.

To start, you'll be directed to a Saleforce.com authorisation endpoint, there you log in and approve access for your app to access data.

After a successful authorisation Salesforce sends a response with an acccess token and refresh token.

The access token is passed in the header of all API requests for data. This token has an expiry date and will always expire. By default the connected apps have an access token with an expiry of 15 minutes (in line with the sessions settings within your Salesforce settings).

The refresh token is used to get a valid access token (e.g. when the current access token expires). You can change the expiry settings on this but you can also set this never to expire, only when revoked.

Once authorization is successfully established in the Salesforce Lightning platform, you can use OAuth API - Export endpoint to extract the details for ClientId, CustomerSecret, and AccessToken using OAuth 2.0.

Warning

You can use any method to create your Salesforce refresh token, but note that OAuth 2.0 is a web-based protocol, so it's impossible to create a refresh token in a completely non-interactive batch mode script.

Below is the example showing to export the details of the OAuth using the following details:

To export the selected OAuth available within the Matillion instance, provide the "OAuthName" and use the /export endpoint as mentioned in OAuth API - Export endpoint. This example using GET method REST API call to export the OAuth details.

Postman Example-Export Endpoint

Postman Example-Export Endpoint

The values in the above example correspond to the following fields in the sample code and make a note of the below details as they'll be required further to retrieve the access token:

  • ClientID is the ConsumerKey.
  • ClientSecret is the ConsumerSecret.
  • RefreshToken.

Please Note

The OAuth 2.0 specification uses "client" instead of "consumer." Salesforce supports OAuth 2.0.

Generate access token

The Salesforce REST API requires an access token to authenticate. An access token can only be retrieved using the refresh token obtained above.

Please Note

OAuth endpoints in Salesforce:

  • Authorization: https://YourInstance.salesforce.com/services/oauth2/authorize
  • Token request: https://YourInstance.salesforce.com/services/oauth2/token

Various components involved in sending HTTP(s) request to generate the access token. Note we'll be using "Postman" as an API request tool for generating an access token.

  1. Enter the base URL as https://um2.salesforce.com/services/oauth2/token, and select HTTP method POST
  2. Enter the following key/pair values in the "Query Params" section on the UI as shown below:
    3. Query Parameters

    Query Parameters

    Once done, the query parameter and their values will be added to the endpoint URL.

  3. Click Send, and you'll get access_token, instance_url and token_type. This access token is further used as a parameter in the header to send HTTP requests.
    4. Generate Access Token

    Generate an access token

Please note

The consumer key (client id), consumer secret (client secret) and refresh token (refresh token) are used by rest client to get the access token from Salesforce. Once the access token is obtained from Salesforce, the rest client will pass this token to Salesforce in its next webservice calls.

Now you have a way to keep access tokens valid and up to date, you're set up and ready to start working with Salesforce objects.

Salesforce Objects

Salesforce objects (sobjects) are effectively database tables that contain an organization's data. Examples of standard Salesforce objects will be "Accounts", "Contacts", "Leads", and "Tasks."

A Salesforce record describes a specific occurrence of an object (such as a specific contact like "Jonny Appleseed" that is represented by a contact object). A basic comparison would be like a row in a database table.

For the following examples, we're going to focus on contacts.
https://Instance.salesforce.com/services/data/v42.0/sobjects/Contact

Please note

To send the HTTP request and get the response from Salesforce, we have to authenticate the Salesforce user account used by the rest client, by sending this access token as a parameter value in Headers "Authorization: Bearer YOUR_ACCESS_TOKEN" .

Load and save data from Salesforce

In order to retrieve a record from Salesforce, a ‘GET' action is used. Below is the sample URL to fetch the details with a custom query.

Endpoint URI Example:https://Instance.salesforce.com/services/data/v42.0/query/?q=SELECT+id,name,email,phone+from+Contact.

  1. Select the GET HTTP method and enter the endpoint URL.
  2. In Authorization tab, select Bearer Token and provide the access token you received before.
  3. Now, click on Send and itll return all contacts with their associated properties as mentioned in query parameters in the endpoint url.
  4. Finally, save the response to a file in JSON or XML format as "page1.json".
    5. Load and Save data from Salesforce

    Load and Save data from Salesforce

Create API profile file

Once you have JOSN data saved into a local file (page1.json), you can create an API profile file.

First, create and upload JSON or XML file in the query profile interface. Generate the query profile RSD file using the uploaded JSON or XML file. Later, using the generated query profile in API Query component to fetch the data and store it in data warehouse. This process also allows users to upload or write RSD files. For detailed guidelines, please follow the instructions below:

  1. Click Project → Manage API Profiles → Manage Query Profiles to open the Manage Query Profiles interface window. Click to add a new Query profile.

    Manage Query Profiles

    Manage Query Profiles

  2. In the Add Query Profile pop-up window, enter "Salesforce-API" into the Profile Name field and click OK.

    Add Query Profile

    Add Query Profile

  3. On returning to the Manage Query Profiles pop-up window, find "Salesforce-API" in the API Profiles list, and click . This should open the Configure Query Profile pop-up window. API Profile

    API Profile

  4. On the Configure Query Profile window, click on New File to create a new API profile file.
    5. New File

    New file

  5. The New file takes you to select file type window. Choose the type of file you want to create. Select Create Sample File if you want to upload a JSON or XML sample file. Click Next. Select File Type

    Select file type

  6. On the Create File window, upload the JSON file you saved before. Create File

    Create file

  7. The final step is to validate the JSON file using the Validate button at the bottom. Once validated with no errors, click Finish. Validate File

    Validate file

  8. After successful validation, the "page1.json" file will be created into the Configure Query Profile window. JSON File created

    JSON file created

Generating an RSD file using an uploaded JSON file

Once the JSON file is created, generate a new RSD file using the uploaded JSON file, as below:

  1. Click New Endpoint in the Configure Query Profile window.
    2. Validate JSON File

    Validate JSON file

  2. The New Endpoint will take you to Configure Query Connector interface window. Provide the Source details with the name of what you want the table to be called and provide a description.

    Please note, if you want to use a JSON file created before, check the Use Sample File option and select the file to create an RSD file. To use the API endpoint URI to fetch the data and generate an RSD file, deselect Use Sample File, enter the endpoint URI and click Send.

    Source details

    Source details

  3. An API will typically return a JSON or XML document in "response" tab automatically, since you have selected the created JSON file in the previous step. Once, validated successfully, click Next. Endpoint Configuration

    Endpoint Configuration

    4. Please Note

    Endpoint Configuration window, allows you to add "Authentication" and "Parameter" attribute values using Auth and Param tab in this wizard. Please visit their respective documentation to get the information in detail.

    With Salesforce API, you need to provide authentication using access token as discussed before. If you use API endpoint URI to fetch the data, provide access token for "Bearer Authentication" type in Auth tab, detail will be dicussed on next section Switching to Actual API Endpoint URI.

  4. On the Response Configuration window, you may only want specific items such as those nested in an object of the JSON. The repeating group in this example is an array element named "records".

    If you want your xpath to start from the beginning of your object, just "set Repeating Element" using right-click on the tree view (/records) into the "Repeat Element" field. Next, you enable the "Paging" feature and select the required strategy from the dropdown (in this case Paging is "Full Path"), click Next

    Response Configuration

    Response Configuration

  5. You'll see a sample of the data in Data Preview tab. You can also review your configurations through the Config Review tab. When you click Finish. Data Preview

    Data Preview

  6. Returning to the Configure Query Profile window, the file "Salesforce-API.rsd" will now be open in editable mode. RSD file content

    RSD file content

  7. Next, click Test to check the RSD file is working correctly. This will invoke the API and add Salesforce-API to the Tables panel. Clicking on the table name will then display its contents at the bottom of the Configure Query Profile window.

    Test the API again

    Test the API again

Important Information

  • View the sample data at the bottom of the dialog and make sure it's retrieving the data at the expected granularity.
  • The generated RSD file allows you to edit files manually, add or delete any column, or change the value of any field.
  • You may want to adjust the fields returned, which you can do by editing the elements near the top of the RSD (lines 6 to 11 in the example above).

Switching to API endpoint URI

Once you test the response of the query profile, it's time to switch to using the actual API endpoint URI instead of the sample JSON file.

To do that, it's recommended to create another endpoint by clicking the Endpoint button at the Configure Query Profile window and deselect "Use Sample File".

Deselect - Use Sample File

Deselect - Use Sample File

Grant the Salesforce API endpoint URI to fetch the data and enter "access token" in Auth tab under Bearer Token authentication type.

Enter Authentication parameter

Enter authentication parameter

Please note

This time the RSD will attempt to contact the real API endpoint, so it's necessary to also add authentication parameters. For the Salesforce REST API, it's the access token in the form of authentication bearer token. Again, use exactly the same value, to generate an access token.

Follow the steps on the screen and you'll get the new RSD file with new "URI value". Please note, URI parameters will be added automatically as you already provided details in the wizard at Endpoint Configuration. Please test the API again and verify that it still retrieves data at the required granularity.

RSD using Endpoint URI

RSD using Endpoint URI

Please note

  • The RSD script is fully functional as-is, but still contains a hardcoded access token. It's bad practice to leave it this way, for two reasons:
    • It's insecure: owning the access token is equivalent to knowing the Salesforce username and password, so it needs to be properly protected
    • Access tokens expire after two hours by default, so the existing API profile has a very short shelf life

    To remedy these things, the RSD needs to be parameterized as part of production.

    <p>Salesforce access tokens expire after 2 hours by default. After that time the API call may stop working and you'll need to get another access token.</p>
    <p>Two steps remain to produce the query profile:</p>
    <ol>
        <li> Remove the hardcoded access token from the "Manage Connection Option".</li>
        <li> Obtain the access token at runtime and supply the value to the <b>CustomHeader</b> Parameter in Manage Connection Option as "Authentication : Bearer &lt;Token&gt; using <dfn class="icon-settings"></dfn> .</li>
    </ol>
    <br id="connectionwizard">
    <img class="mimg" src="https://matillion-docs.s3-eu-west-1.amazonaws.com/images/2970148/New+Images/New+Images-March2021/Switching-Real-Endpoint-04.png" alt="Manage Connection Options"><p class="caption">Manage connection options</p>
    <p>Test the API profile again with the parameter in place to make sure it's being picked up correctly.</p>
    <p>After the API profile has been parameterized, you can package the components into an Orchestration job.<br> </p>
    <hr id="1.8">
    <h2>API Query component</h2>
    <p>Once an API Query profile (RSD file) is created and has been tested and returns data, it's ready to be used in the API Query component. This can be done as follows:</p>
  <ol>
    <li>Create a new Orchestration Job.</li>
  <li>Drag an API Query component onto the job canvas.</li>
  <li>Click on the component icon to open the Properties panel.</li>
  <img class="mimg" src="https://matillion-docs.s3-eu-west-1.amazonaws.com/images/2970148/New+Images/New+Images-March2021/API+Query+Component-01.png" alt="API Query Component">
      <p class="caption">API Query Component</p>
   <li>In the <b>Basic/Advanced Mode</b>, please keep the <b>Mode</b> of the properties as "Basic" which is a preferred mode. In the "Basic" mode, you need to choose a data source and column. In "Advanced" mode you need to make specific SQL queries in editor.</li>
    <img class="mimg" src="https://matillion-docs.s3-eu-west-1.amazonaws.com/images/2763313/New+Images-2021/API+Query+Componenet-04.png" alt="Authentication Mode">
    <p class="caption">Authentication Mode</p>
    <li>Next, select the <a href="/metl/docs/authentication-with-api-profiles/#1.3">Authentication Method</a> from the dropdown and click <dfn class="green">OK.</dfn></li>
      <img class="mimg" src="https://matillion-docs.s3-eu-west-1.amazonaws.com/images/2970148/New+Images/New+Images-March2021/API+Query+Component-05.png" alt="Authentication Method">
        <p class="caption">Authentication Method</p>
   <li id="profile2">Next, select the <b>Profile</b> from the dropdown, then click <dfn class="green">OK</dfn>. Please note, the <a href="#profile">Profile</a> is the name of the API Query profile you created. Example: <code>Salesforce-API</code>. </li>
      <img class="mimg" src="https://matillion-docs.s3-eu-west-1.amazonaws.com/images/2970148/New+Images/New+Images-March2021/API+Query+Component-06.png" alt="API Query Component-Profile">
      <p class="caption">API Query Component-Profile</p>
      <li id="connectioncomp">In the <b>Connection Options</b>, you can specify the param attributes value same as in <a href="#connectionwizard">Manage Connection Options</a> while generating a query profile and also dynamically adding any params or change the param values . Please note that when you configure Connection Options property in the API Query component, its value will not be persisted.</li>
        <img class="mimg" src="https://matillion-docs.s3-eu-west-1.amazonaws.com/images/2970148/New+Images/New+Images-March2021/API+Query+Component-07.png" alt="API Query Component-Connection Option">
        <p class="caption">API Query Component-Connection Option</p>
       <li id="source2">Select the valid <b>Data Source</b> from the dropdown. The <b>Data Source</b> is the name of the "Endpoint" provided in the <a href="#source">Source Details</a> window. Example: Salesforce-API</li>
      <img class="mimg" src="https://matillion-docs.s3-eu-west-1.amazonaws.com/images/2970148/New+Images/New+Images-March2021/API+Query+Component-08.png" alt="API Query Component-Data Source">
      <p class="caption">API Query Component-Data Source</p>
      <li>Select the items or variables in <b>Data Selection</b> and click <dfn class="green">OK</dfn>. The <a href="#response">Data Selection</a> are the items or variables you wanted the data to be collected from endpoint.</li>
      <img class="mimg" src="https://matillion-docs.s3-eu-west-1.amazonaws.com/images/2970148/New+Images/New+Images-March2021/API+Query+Component-09.png" alt="API Query Component-Data Selection">
      <p class="caption">API Query Component-Data Selection</p>
      <li>Configure the rest of the API Query component by providing a <b>Target table</b> name and selecting a valid <b>Staging Area</b>. Once all the details are provided and validated as OK, the icon of the API Query component coverts to green.</li>
      <img class="mimg" src="https://matillion-docs.s3-eu-west-1.amazonaws.com/images/2970148/New+Images/New+Images-March2021/API+Query+Component-10.png" alt="API Query Component properties">
      <p class="caption">API Query Component properties</p>
      <li>Finally, you can run the component by right clicking on the API Query Component and selecting <dfn>Run Component</dfn>. You can view the details in the <b>Tasks Info</b>.</li>
      <img class="mimg" src="https://matillion-docs.s3-eu-west-1.amazonaws.com/images/2763313/New+Images-2021/API+Query+Componenet--11.png" alt="API Query Component-Run Job">
      <p class="caption">API Query component-run job</p>
      <li>Next, you click <dfn>Sample</dfn> and then click <dfn class="green">Data</dfn>. It should fetch the response as expected.</li>
      <img class="mimg" src="https://matillion-docs.s3-eu-west-1.amazonaws.com/images/2970148/New+Images/New+Images-March2021/API+Query+Component-12.png" alt="API Query Component-Sample Data">
      <p class="caption">API Query Component-Sample Data</p>
  </ol>
    <hr id="1.9">
    <h2>Obtaining an access token at runtime</h2>
    <p>There are a number of different ways to achieve this, as outlined in <a href="/metl/docs/2877639" target="blank">this document</a>.</p>
    <p>For this example, we'll replace the token access URL (https://um2.salesforce.com/services/oauth2/token) command shown earlier with a <b>Python</b> script in a Matillion "Python Script" component.</p>
    <ol>
        <li>Search for the <b>Python Script</b> component in the "Search bar".</li>
        <li>Click on <dfn>Properties</dfn> of the component.</li>
        <li>Open the <b>Script</b> window by clicking <dfn class="green">...</dfn>.</li>
        <img class="mimg" src="https://matillion-docs.s3-eu-west-1.amazonaws.com/images/2970148/New+Images/New+Images-March2021/Obtaining+access-token-01%2C02%2C03.png" alt="Python Script Component"><p class="caption">Python Script Component</p>
        <li>On the <b>Script</b> window. Enter the Jython Script for the "Token Access" URL.</li>
        <img class="mimg" src="https://matillion-docs.s3-eu-west-1.amazonaws.com/images/2970148/New+Images/New+Images-March2021/Python+script+-code+generation-04.png" alt="Jython Script "><p class="caption">Jython Script </p>
    </ol>
    <h3>The Orchestration job</h3>
    <p>There are three aspects to this:</p>
    <ol>
        <li><a href="/metl/docs/2943425" target="blank">Job Variables</a>, accepting all the necessary parameters including client_id, client_secret and refresh_token.</li>
        <li>The <b>Python Script</b> component, which gets a new Salesforce access token using the client_id, client_secret and refresh_token.</li>
        <li>The <b>API Query</b> component, which uses the access token to extract the Salesforce account data and load it into a database table.</li></ol>
    <img class="mimg" src="https://matillion-docs.s3-eu-west-1.amazonaws.com/images/2970148/New+Images/New+Images-March2021/Python+script-Code-Generation-New-05.png" alt="Orchestration Job"><p class="caption">Orchestration job </p>
    <p>In the Salesforce API Query component, go to Connection Options and add a new record:</p>
    <ul><li>Parameter: Other</li><li>Value: access_token=${access_token}</li></ul>
    <img class="mimg" src="https://matillion-docs.s3-eu-west-1.amazonaws.com/images/2970148/New+Images/New+Images-March2021/Python+script+-code+generation-05.png" alt="Variable Parameter"><p class="caption">Variable Parameter </p>
<p>
        Once done, check the response data in the <b>Sample</b> tab after adding Python Script to get the access token at runtime.
</p>
<img class="mimg" src="https://matillion-docs.s3-eu-west-1.amazonaws.com/images/2970148/New+Images/New+Images-March2021/Python+script+-code+generation-06.png" alt="Sample Data"><p class="caption">Sample data</p>
        <h4>Warning</h4>
    <p>For security reasons it's highly recommended that you do not hardcode the "client_id", "client_secret" and "refresh_token" anywhere, or store them as environment variable defaults. Instead, implement password management functionality to protect them - for example as described in <a href="/metl/docs/2937411" target="blank">this article</a>.</p>