Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
BrandonB
Alteryx
Alteryx

API.gif

 

 

Are you tired of downloading exports from websites and changing your Input Data tool to bring in the latest file? Do you wish that your Alteryx workflows could automatically pull the latest data into your workflow so that you can schedule them on Alteryx Server without manual intervention? You may be in luck! Alteryx has the capability of connecting to something called an API which can do exactly that!

 

What exactly is a Web API?

API stands for Application Programming Interface and is a web development concept that allows you to access content from an external vendor in a nice format. This means that instead of downloading files to use in your analysis, you may be able to directly pull this data into the workflow directly from the source without dealing with files at all. This is different than web scraping, in which an entire webpage of data at formatting is pulled, and then complex parsing techniques are used to filter through extra formatting. An API is desirable because the data will be coming in as a format that does not require advanced regular expressions to get it into a workable state.

 

How do I figure out if the website that I download files from has an API?

The easiest way that I have found is to web search for “CompanyNameHere API Documentation.” This will help you to validate whether the source you would like to pull from has an API available, and the documentation is always the starting point when you are going to set things up within Alteryx Designer. Sometimes these documents can be found in “Developer” or “Resources” sections of company websites. You only need to worry about finding this documentation at this time. Below is an example of Yelp’s documentation that you would be looking for:

 

BrandonB_0-1630009642556.png

 

I found the API documentation, now how does it work?

The Download tool is going to be the primary focus of this article, although you may decide that you like to use the Python tool or R tool if you enjoy using code more. The Download tool uses what is called an Endpoint to pull data from an API. An Endpoint looks like a regular website URL at first glance, but it is a special kind of link that will return data directly into the workflow in a nice format to be used for analytics.

 

Where should I start?

The first step is looking at the API documentation to see what type of API it is (REST vs SOAP) and what kind of authentication it uses. Full transparency, this isn’t always the easiest to figure out but most of the time they will be REST APIs. If they are SOAP APIs, additional steps need to be taken (make note of this - you need to create the xml payload with a Formula tool).

 

For APIs that require authentication, this is like your security badge to get access to the data. You may see that it uses no authentication, Basic authentication, API Key Authentication, or Oauth. Try taking a look at the “Getting Started,” “Introduction,” or “Authentication” sections of your API documentation to check what authentication is needed, if any. Here is Yelp’s example which states that an API key is used. Make note of this method as it will influence the way that your workflow is built.

 

BrandonB_1-1630009642669.png

 

 

After a brief look at how the Download tool works, we are going to look at four examples of API calls. One does not leverage authentication, one uses Basic Authentication, one uses an API Key, and one uses Oauth. If you would like to skip ahead, examples of authentication for API calls can be found here.

 

How does the Download tool work for pulling data from APIs?

The Download tool documentation can be found here but we will cover the basics.

 

In the configuration window, the Basic tab has a variety of options that are used to retrieve data from a URL. The URL field is expecting a website link or API endpoint to be passed in so that it can connect and pull data. The Encode URL Text checkbox can generally remain checked because it converts unsafe ASCII characters into a format that can be transmitted over the internet. The other settings can all remain the same because for most API calls you will want your data downloaded as a string data type. Some scenarios may permit downloading files to temporary locations, but we won’t worry about that for now.

 

The second tab in the configuration window, Headers, is generally used to send authentication information to the API. You will commonly see API Keys sent as part of the headers, but ultimately the documentation will dictate where this information needs to be included. The Header values can either be hard coded as a Name/Value pair by clicking the Add button at the top, or they can be dynamically read in from upstream in the workflow by checking the boxes for the corresponding column names. This can be helpful when an authentication component is dynamically generated like in the case of an Oauth flow.

 

The third tab in the configuration window, Payload, is used to pass parameters that specify what data is to be retrieved. These payload parameters can be thought of similar to filters in that they are narrowing down the specific dataset to be returned by the API. Similar to the Headers, they can either be hard coded as a Name/Value pair, or the box can be checked to read the value from a column that is coming in from upstream in the workflow. Alternatively, you can also create a Query string upstream in the workflow like in a formula tool and then reference that by selecting the “Take Query String/Body from Field” option. You can also “Use Following for Query String/Body” if you would like to hard code the query into the bottom of the payload tab. Up at the very top of the Payload tab, there is a dropdown for whatever HTTP action the API has specified is needed for the endpoint. The GET and POST options will be used most commonly, but you have access to all of the standard HTTP Actions.

 

Finally the fourth tab is “Connection” which won’t be used for most API connections so we can disregard that for now.

 

What do examples of each authentication type look like?

No Authentication:

 

BrandonB_2-1630009642682.png

 

 

API Key Authentication:

 

BrandonB_3-1630009642718.png

 

 

Basic Authentication:

 

BrandonB_4-1630009642763.png

 

 

Oauth 2.0:

 

BrandonB_5-1630009642823.png

 

 

As you begin to build out your workflow, pay close attention to the API documentation. There will be some headers and payload values that are optional, but this will all be outlined in the documentation. For those of you that may already be using something like Postman and have a working API call, I would highly suggest clicking the code button on the right hand side so that you can see the cURL equivalent. Your headers and payload values will be easily identified from the snippet that is shown.

 

BrandonB_6-1630009642916.png

 

Once your API call has been successfully configured, you will likely see the results as either a JSON or comma separated string under the “DownloadData” column (JSON is generally more common). If you connect a JSON Parse tool after your Download tool, it will parse the JSON into a JSON Name and JSON ValueString column as shown below:

 

BrandonB_7-1630009643242.png

 

 

JSON takes the format of a key value hierarchy so to get our data into a nice tabular format we will want to use a Text to Columns tool to split the JSON Name column on the period delimiter and then follow up with a crosstab. We will use the new data as column headers and group by the numeric value which will serve as our record ID. Example shown below:

 

BrandonB_8-1630009643505.png

 

And with that, hopefully you have made your first successful API call! Leave your questions down below but hopefully this helps you on your journey to integrate all of your disparate data systems.

Comments
DanielBr
Alteryx
Alteryx

Nice work Brandon, great article!

BrandonB
Alteryx
Alteryx

@TonyA thank you very much for reviewing this with me!

Emil_Kos
17 - Castor
17 - Castor

Great resource @BrandonB 
Thank you for sharing!

sparksun
11 - Bolide

nice sharing!

AleksM
Alteryx Alumni (Retired)

Thanks for sharing this Brandon -- awesome content, as always!

jasonMMedina
7 - Meteor

Thanks for the super helpful walk thru! We have an API endpoint expecting encryption.  Our workflow gets a 200 response, but no data downloads.  What's the right way to handle encrypted API endpoints with Alteryx?  Appreciate any help thank you!

 

Here is the error we see from Alteryx:

Warning: Download (12): No data received from "Encrypted API ENDPOINT"; Check the headers for details.
  

BrandonB
Alteryx
Alteryx

@jasonMMedina what does the cell in the "DownloadHeaders" after the Download tool say?  Does your API documentation have any guidance on Authorization? 

jasonMMedina
7 - Meteor

DownloadHeaders returns 200 OK with content length 0 and content type and security options.  Have not found any guidance in the API doc other than the encryption type.  Is there anything to search for the document?  Appreciate the help to look at this the right way.  Thanks @BrandonB!   

BrandonB
Alteryx
Alteryx

@jasonMMedina is this public facing API documentation by chance? No data received could be because of the payload that you have specified for the call. Feel free to send me a private message and we can continue chatting there.  If you haven't already, I would also make sure that you are using https, not http in your URL which could also be the issue. 

Bill_Richardson
7 - Meteor

Hi @BrandonB ,

Great article on using APIs with Alteryx.

 

The system I want to access via API requires me to enable the API by setting up an "External Application" (which gives it an API key, etc.).  One of the required items in "External Application" setup is a "Redirect URL" which is described as "URL where the authentication response will be returned".  The example it gives is for access for a Postman call to the API where the URL is set to "https://oauth.pstmn.io/v1/callback".  

 

For the Alteryx Download tool, what should the "Redirect URL" be set to?  How does Alteryx know where to get the response from?

 

--Bill R

BrandonB
Alteryx
Alteryx

@Bill_Richardson do you happen to have the API documentation link handy so I can take a look? 

 

An example would generally be:

 

1. Register an App and get Client ID/Client Secret/Redirect Url

2. Use the Client ID and Redirect Url to retrieve a "code" (required for every request made)

    The code returned on the url: http://[Redirect Url]?code=xxxxxxxxxxxxxxxxxxxxxxxxxxxxx

3. Append the code, Client ID, Client Secret and encoded Redirect Url to generate the "token"

4. Put the "token" into the header and make request to retrieve the data

 

If your example is an Oauth 2.0 flow, you would have one download tool passing the client id and secret to an authentication endpoint which would generally return a bearer token and then you would use that bearer token in the second download tool following a formula tool that specifies the endpoint to access the data. 

 

If you have the documentation link handy it would be most helpful because it isn't an Alteryx specific redirect URL that you are referring to. 

Bill_Richardson
7 - Meteor

@BrandonB ,

Thanks for the quick reply.  The API that I'd like to access is the UiPath DataService.  The general docs for it are at https://docs.uipath.com/data-service/docs/api-access . 

 

I've built a workflow that attempts to follow the pattern of the steps you outline.  But I'm hung up on what the "redirect uri" should be.  UiPath requires me to tell it what I want the redirect uri to be when I setup an External Application.  Am I wrong to interpret that as meaning that the redirect uri varies according to the client with which you want to access the API?  There's a video at https://forum.uipath.com/t/data-service-api-part-1-getting-started/327278 that shows accessing the DataService API with PostMan and for PostMan, they set the redirect uri to "https://oauth.pstmn.io/v1/callback" which seems to make sense because PostMan is the client that's working with the data.  

 

I don't know if the info at https://help.alteryx.com/designer/designer-compatibility-data-connectors applies to what I'm trying to do, but it shows a redirect uri for various connectors as "https://cef.alteryx.com/designer/oauthcallback"

 

Is it possible that my External App config should look like this?

Bill_Richardson_0-1673277975964.png

I've tried this, and I get a "302 Found" with "ocation: https://cloud.uipath.com/identity_/ui/error/error?errorId=..."

 

I'm fairly new to REST API calls, so I appreciate your help.

 

BrandonB
Alteryx
Alteryx

@Bill_Richardson UiPath is actually an Alteryx partner and there are Connectors that have been built specifically for this purpose: https://www.alteryx.com/partners/uipath 

 

You can find the UiPath tool download here: https://community.alteryx.com/t5/Community-Gallery/UiPath-Tool/ta-p/877904 

 

Can you take a look at this and let me know if it works for your use case? It might help avoid reinventing the wheel if the functionality you need is present in the connector. 

Bill_Richardson
7 - Meteor

@BrandonB ,

 

Thanks for the suggestion, but as far as I understand, the Alteryx Connector only works with an on-prem UiPath Orchestrator.  Also, as I understand it, the Connector is intended for the Orchestrator API (seeing jobs, running jobs, status, etc.) and has nothing to do with the DataService API.

 

From Alteryx, I'm able to use the Download Tool to retrieve data from the UiPath DataService IF I do the "obtain token" part manually with PostMan and then hardcode the token in the Alteryx tool.

 

Several community posts hint that Alteryx is unable use APIs that involve Oauth2 with a redirect-uri.  I haven't been able to confirm this anywhere, but it is my experience so far.

 

Thanks for taking the time to help!

 

--BillR

BrandonB
Alteryx
Alteryx

@Bill_Richardson, for the obtain token part are you able to hit the "Code" button under save on the right hand side of the Postman UI? This may show you the curl equivalent of what Postman is doing to get the code and will make it much easier to translate into the Alteryx equivalent. Sometimes this gives hints into what might be missing. 

Raj_007
8 - Asteroid

Hi Brandon,

is it possible to share the workflow that has these 4 different types of authentication

BrandonB
Alteryx
Alteryx

Hi @Raj_007 

 

Unfortunately it won't allow me to respond here with a workflow attachment. If you create a post asking for it and tag me I can upload the workflow there. 

Raj_007
8 - Asteroid

thank you Brandon

Raj_007
8 - Asteroid

Hi Brandon

i did not see the option to tag - this is the link - thanks a lot

API pull from Alteryx which has oauth2/basic authe... - Alteryx Community

 

BrandonB
Alteryx
Alteryx

Perfect, I have attached the workflow to your post