Connect to Amazon Athena Data through External Services in Salesforce

CData Connect Cloud, enables you to access Amazon Athena data from cloud-based applications like the Salesforce and mobile applications like the Salesforce Mobile App. In this article, you will use CData Connect Cloud to access Amazon Athena data through Salesforce External Services.

Getting Started

---

Connect to Amazon Athena from Salesforce

To work with live Amazon Athena data in Salesforce Connect, we need to connect to Amazon Athena from Connect Cloud, provide user access to the connection, and create OData endpoints for the Amazon Athena data.

(Optional) Add a New Connect Cloud User

As needed, create Users to connect to Amazon Athena through Connect Cloud.

1. Navigate to the Users page and click Invite Users
2. Enter the new user's email address and click Send to invite the user
3. You can review and edit users from the Users page

Add a Personal Access Token

If you are connecting from a service, application, platform, or framework that does not support OAuth authentication, you can create a Personal Access Token (PAT) to use for authentication. Best practices would dictate that you create a separate PAT for each service, to maintain granularity of access.

1. Click on your username at the top right of the Connect Cloud app and click User Profile.
2. On the User Profile page, scroll down to the Personal Access Tokens section and click Create PAT.
3. Give your PAT a name and click Create.
4. The personal access token is only visible at creation, so be sure to copy it and store it securely for future use.

Connect to Amazon Athena from Connect Cloud

CData Connect Cloud uses a straightforward, point-and-click interface to connect to data sources.

1. Log into Connect Cloud, click Connections and click Add Connection
2. Select "Amazon Athena" from the Add Connection panel
3. Enter the necessary authentication properties to connect to Amazon Athena.

   Authenticating to Amazon Athena

   To authorize Amazon Athena requests, provide the credentials for an administrator account or for an IAM user with custom permissions: Set AccessKey to the access key Id. Set SecretKey to the secret access key.

   Note: Though you can connect as the AWS account administrator, it is recommended to use IAM user credentials to access AWS services.

   Obtaining the Access Key

   To obtain the credentials for an IAM user, follow the steps below:

   1. Sign into the IAM console.
   2. In the navigation pane, select Users.
   3. To create or manage the access keys for a user, select the user and then select the Security Credentials tab.

   To obtain the credentials for your AWS root account, follow the steps below:

   1. Sign into the AWS Management console with the credentials for your root account.
   2. Select your account name or number and select My Security Credentials in the menu that is displayed.
   3. Click Continue to Security Credentials and expand the Access Keys section to manage or create root account access keys.

   Authenticating from an EC2 Instance

   If you are using the CData Data Provider for Amazon Athena 2018 from an EC2 Instance and have an IAM Role assigned to the instance, you can use the IAM Role to authenticate. To do so, set UseEC2Roles to true and leave AccessKey and SecretKey empty. The CData Data Provider for Amazon Athena 2018 will automatically obtain your IAM Role credentials and authenticate with them.

   Authenticating as an AWS Role

   In many situations it may be preferable to use an IAM role for authentication instead of the direct security credentials of an AWS root user. An AWS role may be used instead by specifying the RoleARN. This will cause the CData Data Provider for Amazon Athena 2018 to attempt to retrieve credentials for the specified role. If you are connecting to AWS (instead of already being connected such as on an EC2 instance), you must additionally specify the AccessKey and SecretKey of an IAM user to assume the role for. Roles may not be used when specifying the AccessKey and SecretKey of an AWS root user.

   Authenticating with MFA

   For users and roles that require Multi-factor Authentication, specify the MFASerialNumber and MFAToken connection properties. This will cause the CData Data Provider for Amazon Athena 2018 to submit the MFA credentials in a request to retrieve temporary authentication credentials. Note that the duration of the temporary credentials may be controlled via the TemporaryTokenDuration (default 3600 seconds).

   Connecting to Amazon Athena

   In addition to the AccessKey and SecretKey properties, specify Database, S3StagingDirectory and Region. Set Region to the region where your Amazon Athena data is hosted. Set S3StagingDirectory to a folder in S3 where you would like to store the results of queries.

   If Database is not set in the connection, the data provider connects to the default database set in Amazon Athena.

   
4. Click Create & Test

Add a workspace and an asset to Virtual Datasets

Virtual Databases provide a new way to access, model, govern, and deliver data. A virtual database mimics a real database. Applications can connect like they normally would to a database, but the connection is a live data connection to the data source. Follow the given process to add a workspace to the Virtual dataset:

1. Select Virtual Datasets in Connect Cloud and add a new workspace. Check the documentation link for further reference.
2. Add a new asset in the workspace and select the required table from the Amazon Athena to update using Salesforce external services (to be demonstrated in the next section).
3. Select View Endpoints -> OData. Copy the Username, OpenAPI URL, and PAT (generated in the previous section) and save them for future reference. Click Close.

Check the column data types of the selected table:

1. Once the Amazon Athena data table is added as an asset, select the table, and note the column names and data types from the Column tab.
2. In the Preview tab, preview the current data in the table to be referenced later.
3. Click on Users from the left panel and search for your username, e.g. [email protected]. Click on the same.
4. On the Edit Users screen, grant the respective user all Permissions (Select, Insert, Update, Delete, and Execute) for the entity (workspace).

Connect to Amazon Athena Data as an External Service

Create a Named Credential

Follow the steps below to connect to the OpenAPI produced by CData Connect Cloud with Salesforce:

1. Log into Salesforce and navigate to Setup.
2. Search for Named Credentials in the search bar and click on it.
3. Select New -> New Legacy.
4. Add the given details in the New Named Credential screen:
   • Label: enter a label of your choice
   • Name: enter a name for the named credential
   • URL: https://cloud.cdata.com
   • Identity Type: Named Principal
   • Authentication Protocol: Password Authentication
   • Username: enter your CData Connect Cloud username. This is displayed in the top-right corner of the CData Connect Cloud interface. For example, [email protected]
   • Password: enter the PAT you generated on the Settings page
   • Generate Authorization Header: enable the checkbox

Click Save to save the details.

Create an External Service

1. Now, search for External Services in the search bar and click on it.
2. In the Add an External Service page, click From API Specification under Select an API Source.
3. In the Enter an Existing Service page, enter the given details under Modify Exiting Registration Details:
   • External Service Name: enter an identifying name for your external service
   • Description: enter a description of your external service (not a mandatory field)
   • Service Schema: Relative URL
   • Select a Named Credential: OpenAPI
   • URL: copy enter the OpenAPI URL from Connect Cloud and remove https://www.cdata.com and keep the rest of the URL. Make sure the URL starts with a forward slash "/api...".
   

   If the connection is successful, the message "Schema validation completed successfully!" will be displayed on the screen. Click Save & Next

4. Select all the entities in the Select Operations screen. Click Next.
5. All the schema-generated actions are displayed in the Review External Services Actions screen. Click Finish.
6. The External Services page will display all the external services created.

Create a new Flow

1. Search for Flows in the search bar and click on it.
2. Click New Flow.
3. Select Start From Scratch option and click Screen Flow.
4. In the Flow Builder screen, click on the "" between Screen Flow and End.
5. Select Screen from Add Element.
6. In the New Screen window, enter "Input Screen" in Label and the API Name will be auto-filled accordingly.
7. Next, based on the table you want to update, note the column data types to add the components to the flow. In the table we discussed in the article, we have column types such as int (orderid), varchar (ordername), and int (personid).
8. Now, for the orderid column, search for the "Number" component in the Components search bar and click on it. Enter Label as "Enter Order ID" and again API Name will be auto-filled accordingly. Enable the Require checkbox and apply this for steps 9 and 10.
9. For the ordername column, search for the "Text" component in the Components search bar and click on it. Enter Label as "Enter Order Name".
10. For the personid column, search for the "Number" component in the Components search bar and click on it. Enter Label as "Enter Person ID".
11. Click Done and Input Screen will be added in the Flow Builder.
12. Now, add an element Action below the input screen in the flow.
13. Under Search Actions, search and click the newly created external service, in this case, OpenAPI1.
14. Click Create Orders.
15. Enter a name in Label, the API Name gets auto filled.
16. Click on the search bar under Set Input Values for the Selected Action and select New Resource -> Variable.
17. In the New Resource window, enter the following details:
    • Resource Type: Variable
    • API Name: Body
    • Data Type: Apex-Defined
    • Apex Class: ExternalService__CDataWorkspace2e
    • Availability Outside the Flow:
      • Available for input: enable checkbox
      • Available for output: enable checkbox
      
18. Add a new element Assignment and add the Label name as "SendBody". Again, the API Name gets auto-filled.
19. Set the given values under Set Variable Values:
    • Variable: Body>orderid; Operator: Equals; Value: Input Screen>Enter Order ID
    • Variable: Body>ordername; Operator: Equals; Value: Input Screen>Enter Order Name
    • Variable: Body>personid; Operator: Equals; Value: Input Screen>Enter Person ID
    
20. Add another Screen element below the Action element.
21. Name the Label as "Display".
22. Under Components, search for the "Display Text" component and click on it.
23. Set the API Name as "Display_Result" and in the Inside a resource search bar, select Create a new order -> response code. Click Done.
24. The flow is now ready. Click Save to save the flow and add a name to the Label as the flow name, in this case, "CreateOrdersPage". Click Save.
25. The flow is displayed on the Flow Builder screen. Click on Activate to activate the flow.

Check the flow and verify the updated details in Connect Cloud

1. Click Debug. The Debug flow window opens. Click Run.
2. In the details input page, in this case, "CreateOrdersPage", enter the details (Orders ID, Orders Name, and Person ID) and click Next.
3. The next page displays the message "200" (HTTP 200 - OK) and the right pane displays the debug details. Click Next.
4. You can change the inputs or run the process again to enter new details.
5. Check in Connect Cloud if the new details are updated in the "Orders" table, as this article outlines. This completes the process.

Simplified access to Amazon Athena data from cloud applications

At this point, you have a direct, cloud-to-cloud connection to live Amazon Athena data from Salesforce. For more details on accessing live data from over 100 SaaS, Big Data, and NoSQL sources through cloud applications, visit our CData Connect Cloud page.

Reach out to our Support Team if you have any questions.