API Profile Example - Salesforce Lightning
    • Dark
      Light

    API Profile Example - Salesforce Lightning

    • Dark
      Light

    Article Summary

    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.

    4. 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.
    5. 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 >New File to create a new API profile file.
    5. New File

      New file

    6. 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

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

      Create file

    8. 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

    9. 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

    3. 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

    4. 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

    5. 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.

    6. 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

    7. 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

    8. 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

    9. 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.

    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.

    Two steps remain to produce the query profile:

    1. Remove the hardcoded access token from the "Manage Connection Option".
    2. Obtain the access token at runtime and supply the value to the CustomHeader Parameter in Manage Connection Option as "Authentication : Bearer <Token> using .

    Manage Connection Options

    Manage connection options

    Test the API profile again with the parameter in place to make sure it’s being picked up correctly.

    After the API profile has been parameterized, you can package the components into an Orchestration job.


    API Query component

    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:

    1. Create a new Orchestration Job.
    2. Drag an API Query component onto the job canvas.
    3. Click on the component icon to open the Properties panel.
    4. API Query Component

      API Query Component

    5. In the Basic/Advanced Mode, please keep the Mode 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.
    6. Authentication Mode

      Authentication Mode

    7. Next, select the Authentication Method from the dropdown and click OK.
    8. Authentication Method

      Authentication Method

    9. Next, select the Profile from the dropdown, then click OK. Please note, the Profile is the name of the API Query profile you created. Example: Salesforce-API.
    10. API Query Component-Profile

      API Query Component-Profile

    11. In the Connection Options, you can specify the param attributes value same as in Manage Connection Options 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.
    12. API Query Component-Connection Option

      API Query Component-Connection Option

    13. Select the valid Data Source from the dropdown. The Data Source is the name of the "Endpoint" provided in the Source Details window. Example: Salesforce-API
    14. API Query Component-Data Source

      API Query Component-Data Source

    15. Select the items or variables in Data Selection and click OK. The Data Selection are the items or variables you wanted the data to be collected from endpoint.
    16. API Query Component-Data Selection

      API Query Component-Data Selection

    17. Configure the rest of the API Query component by providing a Target table name and selecting a valid Staging Area. Once all the details are provided and validated as OK, the icon of the API Query component coverts to green.
    18. API Query Component properties

      API Query Component properties

    19. Finally, you can run the component by right clicking on the API Query Component and selecting Run Component. You can view the details in the Tasks Info.
    20. API Query Component-Run Job

      API Query component-run job

    21. Next, you click Sample and then click Data. It should fetch the response as expected.
    22. API Query Component-Sample Data

      API Query Component-Sample Data


    Obtaining an access token at runtime

    There are a number of different ways to achieve this, as outlined in this document.

    For this example, we’ll replace the token access URL (https://um2.salesforce.com/services/oauth2/token) command shown earlier with a Python script in a Matillion "Python Script" component.

    1. Search for the Python Script component in the "Search bar".
    2. Click on Properties of the component.
    3. Open the Script window by clicking ....
    4. Python Script Component

      Python Script Component

    5. On the Script window. Enter the Jython Script for the "Token Access" URL.
    6. Jython Script

      Jython Script

    The Orchestration job

    There are three aspects to this:

    1. Job Variables, accepting all the necessary parameters including client_id, client_secret and refresh_token.
    2. The Python Script component, which gets a new Salesforce access token using the client_id, client_secret and refresh_token.
    3. The API Query component, which uses the access token to extract the Salesforce account data and load it into a database table.
    Orchestration Job

    Orchestration job

    In the Salesforce API Query component, go to Connection Options and add a new record:

    • Parameter: Other
    • Value: access_token=${access_token}
    Variable Parameter

    Variable Parameter

    Once done, check the response data in the Sample tab after adding Python Script to get the access token at runtime.

    Sample Data

    Sample data

    Warning

    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 this article.

    Attachments