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.
TashaA
Alteryx Alumni (Retired)

Connecting to a REST API from within Alteryx is a great skill to have. However, API documentation can be difficult to navigate, and figuring out where to place the required headers, query strings, and payloads can become confusing. To show how simple it can be, we will attempt to retrieve some stock data from Quandl, an online resource for free and premium datasets, in 5 minutes or less. We will be using the Quick Start guide for the API, set your timer…

 

1. Environment Setup

Open a new workflow, add a Text Input tool with a column titled 'url' and connect a Download Tool to its output.

base_workflow.png 

2. Request URL

The example in the quick start guide shows us what a URL should look like for a request for Facebook stock data, so we will add that as the value of the 'url' field in the Text Input tool. 

 

https://www.quandl.com/api/v3/datasets/WIKI/FB/data.csv

This will work for now, but in the future we may consider splitting the distinct pieces of the request url into unique fields, and compiling the string using the Formula tool, so that the same workflow may be used to download many different datasets. You may also notice the .csv at the end of the request string, this indicates that we want the request for data  to be returned in a .csv format, but we could also specify .json or .xml based on the Quandl documentation. 

 

3. Authentication

Based on the quick start guide, requests should be authenticated with an API key. Go ahead and sign up for a key. Add another field to the Text Input tool, name the field api_key and add your key as the value for that field. The Text Input tool configuration should look something like this:

 

 text_input.png

 

4. Configuring the Download Tool

Basic Tab

Select your url field from the dropdown for URL, all other Basic configurations can remain the same.

 

Basic_tab.png

 

Headers

Default Settings

 

Payload

The HTTP method will be the default selection,  'GET (or FTP)'. The radio button for 'Compose Query String' should be selected. A query string is what comes after the question mark in the URL path. From the list of fields available, select the field containing your API key.

 

payload_tab.png

 

Connection

Default Settings

 

5. Workflow Execution

Add a browse tool to the end, or add an Output Data tool if you would like to save the dataset. Run the workflow and the download tool will add two fields to your workflow, Download Headers and Download Data. The Download Data field will have the Facebook stock data in csv format. Realizing that this may not be the easiest to read, you can add a Text to Columns tool after this to parse the data into a more readable format. 

 

executionResults.png

 

Coming soon, I will take a deeper dive into the multiple Download Tool configurations, as well as what to look for in API documentation. Look forward to coverage on other advanced topics such as  batching your request data, paging for large sets of results, and other authentication methods.

 

What questions do you have about the Download Tool? 

Tasha Alfano

Tasha loves working with Alteryx products, building time-saving tools, and talking to customers! When she isn't at the Colorado Alteryx office, she's usually running or biking on a trail nearby. Twitter: @Tasha_Alfano

Tasha loves working with Alteryx products, building time-saving tools, and talking to customers! When she isn't at the Colorado Alteryx office, she's usually running or biking on a trail nearby. Twitter: @Tasha_Alfano

Comments
mix_pix
10 - Fireball

Hi Tasha,

 

Nice article.  Why would I not get any actual data back?  I just seem to get headers but no data rows.  I'm still a novice with this, so I suspect it's user error. :-)

 

rest api.png

TashaA
Alteryx Alumni (Retired)

Good Morning @mix_pix! In your workflow it looks like the DownloadData column is populated with the data, you may just need to click inside that cell to inspect the data. The response will only be in one cell, and then it's up to you to parse the cell containing the data into rows. 

 

Let me know if you have any other questions!

mix_pix
10 - Fireball

Hi Tasha,

 

Sorry for the slow reply...yes, you're right...there was data by I failed to double-click the Download Data column.  Still figuring things out with this tool. :-)  Thanks again for the great article.

 

-Mike

NickR
5 - Atom

Hi Tasha,

Thank you for the great description on this topic!  Definetly under 5 minutes.  

 

How might one pass in a parameter to restrict data returned to a value, such as limiting results to a date range (date between 2015-12-01 and 2015-12-31?).  I'm more interested in how to apply the condition to the call, not so much the input method (i.e.: pick list).

 

Nick

TashaA
Alteryx Alumni (Retired)

Hey @NickR

 

Great question! 

 

I referenced the complex data request section of the Quandl documentation to see how to name the parameters for start and end date. 

 

You can simply add 2 fields to your text input tool named start_date and end_date with your desired dates like so: 

 

date_parameters.png

 

In the Download Tool, select these fields on the Payload tab in the "add values from these fields" section. 

 

date_parameter_download.png

 

You can continue to add parameters to your payload in accordance with the API documentation in this manner. 

 

Let me know if you have any other questions!

NickR
5 - Atom

Wow.  Too easy.  Thank you Tasha!

Aamir
7 - Meteor

Has anyone had any experience using the download tool to access the Slack API. I can successfully post messages to slack channels using the download tool. This is very straight forward and similar to how you guys have used the download tool to pull data in from Quandl. What I am trying to do is push an image created in alteryx into a slack channel. See the following page for what I am trying to do.

 

https://api.slack.com/methods/files.upload

StephenW
Alteryx Alumni (Retired)

@Aamir I haven't been able to test this but try converting the image to a blob, using the Blob Convert tool, and use the result as your payload.

Aamir
7 - Meteor

@StephenW While I'm able to read in the image as a blob and attach it in my payload I still am unable to successfully push the file to Slack.

 

I am able successfully to push an image to slack using Postman. The keys I provide are the same as what I am entering into the download tools payload - so am lost for words as to why it is not working. See below for screen grabs of my workflow.

 

FileUpload

Create Text tool has URL, Token & channels provided as key values

Blob input tool reads in PNG file as blob.

Append fields tool appends blob to the url & key values.

This flows into the download tool which is configured as a POST payload.

 

Download Tool Basic Config

Download Tool Headers Config

Download Tool Payload ConfigDownload Tool Response

 

While the workflow runs error free I get the following response from Slack - which doesn't make sense to me as I've clearly provided the token and all required information is in the API call.

 

If someone else is using slack can they please test this with their own credentials - it's strange that I can get it to work with Postman but not in Alteryx.

 

Any more ideas are welcome.

StephenW
Alteryx Alumni (Retired)

@Aamir Can you post your screenshots from Postman?  Uncheck token within the Payload tab and rename the token field to Authorization and check the box for it under the Headers tab.

Aamir
7 - Meteor

@StephenW Changing token to authorization and putting it in Headers tab instead of payload returns the same error message as above.

 

Here are screencaps of my successful call of the api using Postman.

 

Postman File.Upload Slack API Call

Here are what the body response looks like as well as Headers.

 

File.Upload Slack Body Response

 

And Headers

Headers Files.Upload Slack API

 

mcwallendjack
8 - Asteroid

Hi Tasha,

 

Very informative article! I am new to using the Download tool and this has helped me a lot.

 

Would you know how to connect LinkedIn APIs to the download tool? I understand the overall process but am unsure about the finer details.

 

Thank you again for your help.

 

-Michael

 

TashaA
Alteryx Alumni (Retired)

Thanks @mcwallendjack

 

I looked around a little bit and saw that LinkedIn has a very interactive REST console that you can use to build and test your API requests. I would start there to make sure you get your request working how you want, and then move on to incorporating the request into Alteryx with the Download Tool. 

 

LinkedIn REST console

 

Let us know if you run into any issues!

 

 

-Tasha 

 

mcwallendjack
8 - Asteroid

Hi Tasha,

 

My aologies for the late response. My notification of your post was in the spam of my email account!

 

I am going to take a look into getting my request up and running. Thanks!

mcwallendjack
8 - Asteroid

Hi Tasha, 

 

Do you recommend a good start point to learn how to interpret various website's API documentation? As you mentioned in your very first post from 11/23/2015 it's really hard to navigate!

 

Basically I get consfused with the lingo/vocab as soon as I start reading. Is there a good website or article to read to get a foundation for the basic strcture of APIs?

 

Thank you!

TashaA
Alteryx Alumni (Retired)

Hey @mcwallendjack

 

I think that webconcepts youtube channel has a really excellent video about the basics of using a REST API.

 

Aside from that, I would look for any information that talks about consuming REST APIs, probably from the perspective of a web developer.  This will hopefully help you avoid any information that is specific to building APIs-could be an interesting read though! 

 

I will send along any other resources I come across that give a good overview of these concepts. 

 

 

-Tasha 

 

 

TashaA
Alteryx Alumni (Retired)

Another great resource for understanding RESTful APIs. 

 

http://schoolofdata.org/2013/11/18/web-apis-for-non-programmers/

 

 

mcwallendjack
8 - Asteroid

Good Morning Tasha!

 

Thanks for these articles.

 

Could you help me understand what OAuth 2.0 Redirect URLs are?

 

https://developer.linkedin.com/docs/oauth2

 

I'm trying to work with LinkedIn Analytics foro their API. 


Thanks!

TashaA
Alteryx Alumni (Retired)

Good Afternoon @mcwallendjack

 

I am actually in the process of putting together a connector SDK for building data connectors in Alteryx. This gives me a great idea to include a brief REST API dictionary along with it as well! 

 

The redirect url is what you provide to the api you are working with that tells them where to send the user when they finish authenticating to the resource, often with the authentication token appended as a parameter on the url. Think of the way you sign in to different web services with something like Facebook, and Facebook then takes you directly to the site you were logging in to. 

 

Many REST APIs include an endpoint to authenticate to by sending a client id and secret, and that is how many connectors are built for Alteryx. 

 

Without this type of endpoint to request an authentication token from, it makes it very difficult to authenticate to the service since Alteryx is a desktop application-without getting into building an HTML5 tool for it. 

 

-Tasha

mcwallendjack
8 - Asteroid

 Hi Tasha, 

 

Thanks so much for your help. You posts are helping me learn the lingo of APIs. I would certainly love to hear/see the connector SDK for data connectors when its completed!

 

Also, will you be posting additionaly content on APIs? Any and all things API related would be well received!

AndyC
Alteryx Alumni (Retired)

Love this article!!!

KY
5 - Atom

 

Tasha - Great article.  Question for you. I have Python script that pulls down call volume from an API;however, the API is limited to 4,000 records per GET statement. I need a way to paginate calls multipe times in Alteryx. 

 

Here is an example of my URL. 

https://xxxxxxxxxxxxxx.com/xxxx/xxxx/xxxx /xxxx /1171.xml?oauth_token=xxxxxxxxxxxxxx&from=2016-01-01&to=2016-04-30&limit=4000 

 

As you can see, the date range is specifed in the URL. Also the limit is set at the end of the URL 4000 records per call ( API limitation ). My workflow runs perfectly ( pic below) but only pulls down the first 4000.  Any clues on how to paginate calls or loop thru? 

 

Capture.JPG

 

 

 

 

 

 

TashaA
Alteryx Alumni (Retired)

Good Morning @KY

 

Thanks for your question. In order to paginate requests, either through the use of a Next Page Token or some other indicator, the approach you are looking for is an iterative macro. I have a knowledge base article that outlines this information and includes an example of the tool sequences that you would use to accomplish this. 

 

Paginating responses is talked about at the bottom here: http://community.alteryx.com/t5/Alteryx-Knowledge-Base/Guide-to-Creating-Your-Own-Connector-Generati...

 

Regards, 

 

Tasha Alfano 

andrewschindler
5 - Atom

Hi Tasha & members,

 

Has anyone successfully connected to the Adobe Omniture API (https://marketing.adobe.com/developer/api-explorer) in this manner?  I'm an novice to APIs and trying to learn everything.  I can use the API link referenced earlier to Queue a report that contains different parameters (project, elements, metrics, dates, segments, etc.), obtain that new report ID, and then Get the results.  I'm trying to automate this via Alteryx as the standard connectors that are currently available do not have enough flexibility for me.  I haven't been able to find much documentation on Alteryx/Adobe so I thought I would ask as this thread seems to hold a lot of value.

 

Thanks!

 

Andrew

Coxta45
11 - Bolide

Andrew,

 

Check out this post...

 

http://community.alteryx.com/t5/Data-Sources/Adobe-Analytics-Connector-Toolkit/m-p/32593#U32593

 

The connector that I've developed will likely get you what you need from Omniture, and includes a README text file with some information about how Adobe Analytics API works.  Feel free to crack open the Adobe Connector tool in this packages to dig into the methods used to queue reports, retrieve reports, and parse the JSON response.

 

Taylor

andrewschindler
5 - Atom

Hi Taylor,

 

Thanks for the link and the connector you created.  I'm able to utilize it for a lot of the queries I need.  Just curious, do you have any plans in the works to modify/enhance it at all?  I have a couple of use cases that have challenged me so far: 

1. Most of what I pull is dynamic in terms of dates where I need the previous 13 weeks and the year-over-year previous 13 weeks of data.  I can get that by using the "Last 24 months" from the date drop-down or by manually typing in the dates I need.  Is there a way for me to feed in dynamic date parameters to the tool?

2.  I have some reporting that needs deeper than 2 elements and segments.  Is there a way to allow for additional cuts?

 

Again, this tool is awesome, so the above questions aren't criticisms but more questions to see how far I can push it.

 

Thanks!

 

Andrew

don_sevcik
5 - Atom

I tried this and received an "Error transferring data: Failure when receiving data from the peer.

 

I've confirmed my API as well as testing it directly in the browser.

patrick_mcauliffe
14 - Magnetar
14 - Magnetar

@don_sevcik - Sounds like you're either behind a company firewall or using a proxy.

 

You'll need essentially the same solution as what I described here:

https://community.alteryx.com/t5/Data-Sources/Download-Fail-Proxy-Authentication-issue/m-p/765#M3

 

If you're on a laptop you can validate by taking your machine to a public/home WiFi and re-running your workflow.

When I'm at work and need to figure out if network security is interferring, I'll set up my phone as a hotspot for a few minutes and have my laptop connect to that before re-running.

sagar_agarwal
8 - Asteroid

Hi - I am unable to connect to an API using the above method - the error that I am getting is - "Error transferring data: Failure when receiving data from the peer".

 

It works fine in browser separately, and it is an internal API - not an external one which can get blocked by firewall.

 

Connect to API_1.PNG

 

 

 

 

Connect to API_2.PNG

patrick_mcauliffe
14 - Magnetar
14 - Magnetar

@sagar_agarwal  What is the purpose of fields 'a' and 'b' in your payload?

I would try it without those first.

sagar_agarwal
8 - Asteroid

@patrick_mcauliffe

They are the parameters that are passed after the "?" part in the URL. The expected result is C = ab

 

Below is the IE screenshot of the same API call:

 

Connect to API_3.PNG

patrick_mcauliffe
14 - Magnetar
14 - Magnetar

@sagar_agarwal 

Does your connection setting use a proxy?

(Control Panel --> Network & Internet --> Internet Options --> Connections tab --> LAN Settings)

ProxyConf.png

 

sagar_agarwal
8 - Asteroid

Hi Patrick - unfortunately, yes! And to top of that, it has been disabled and can only be changed by an IT admin.

 

Nevertheless, can you please guide me to the correct settings/alternative workarounds/ techniques et all; I will work with the IT security team to get this figured out.

 

 

Unrelated Q: How do I tag people in my posts / comments? Looked up a lot but couldn't figure it out.

 

Thank you for your time and help.

 

patrick_mcauliffe
14 - Magnetar
14 - Magnetar

@sagar_agarwal

Answering to second question first: Just type the "@" sign (without quotes) then start typing their name.  There should be some predictive text that starts to pull up their name.  You can click on it once their name is showing,

 

Your IT department will need to disable the proxy or allow you to disable it.  That's the easiest way I've found.

There is this alternative which I haven't tried but may be worth a try for you:

http://community.alteryx.com/t5/Data-Sources/Change-proxy-connection-in-download-tool/m-p/12860#M978

 

 

 

KaneG
Alteryx Alumni (Retired)

Hi @sagar_agarwal,

 

Firstly, you are always better off asking these questions in the main forums as then everyone will see it and you'll be able to tag it, as opposed to posting here where only people who've posted/starred/followed on this blog post get notified about it.

 

If it is an internal API then proxy shouldn't be an issue. However, if the DNS redirects to another domain then it may but at the very least, if there is nothing in internet settings then the proxy should not be a problem. 

 

Try the following 2 command prompts from Alteryx. It could be that Port 80 or Port 8080 is being blocked. The below should help you/your IT troubleshoot. I can't attach the module as it's a comment thread not a forum post... so here it is in images:

 

Image 001 - 20161107 - 211112.png

 

Image 001 - 20161107 - 211122.png

 

If you start a thread in Data Sources then comment on what you have tried through suggestions from @patrick_mcauliffe and myself, so you don't get advised to do the same again.

 

Kane

pbrink
8 - Asteroid

I have set up the text imput and the download tool, but continually get the error transferrring data unsupported protocol.2016-12-14_9-42-22.png2016-12-14_9-42-43.png2016-12-14_9-43-04.png2016-12-14_9-43-21.png

DeniseF
Alteryx
Alteryx

@pbrink,

 

What version of Alteryx are you running? 

 

Regards,

 

Denise Frey

 

Alteryx

Content Engineer

 

pbrink
8 - Asteroid

I am using Version 11.0.0.22867 Beta.  Thanks

DeniseF
Alteryx
Alteryx

@pbrink

 

I created a workflow with the settings you show above. I do not receive an error.

 

Try running the workflow I shared below on Dropbox and please let me know if you still receive an error.

 

Denise

 

https://www.dropbox.com/s/q53kxmhgm63hunm/DownloadTool.yxmd?dl=0

 

pbrink
8 - Asteroid

You correct no error.  I will have to compare to see why mine was failing.  Thank you for your help.

DeniseF
Alteryx
Alteryx

@pbrink

 

You are welcome! Glad to hear it is working! 

 

Regards,

 

Denise

JFoster
6 - Meteoroid

Hi there,

 

Has anyone tried using the download tool with Google Distance Matrix API?

 

We have just updated to the premium plan but are now unable to download any data - with an authetication error. URLs are working fine in the browser but not through Alteryx. 

 

Any help would be great. Many thanks

patrick_mcauliffe
14 - Magnetar
14 - Magnetar

@sagar_agarwal Did you ever get this working?

TashaA
Alteryx Alumni (Retired)

Hey @JFoster,

 

Do you have an example of the request you are trying to make? Or maybe a breakdown of the types of values that are in the payload? 

 

Something I always like to do in this sort of situation is to execute the request in the browser where I know it works, and view the outgoing request with Fiddlr. 

 

Then I repeat the request with Alteryx, and view that outgoing request with Fiddlr and compare the 2 to see where there are differences.

 

I have some other troubleshooting suggestions if you end up needing them. 

-

Tasha 

JFoster
6 - Meteoroid

Hi @TashAlfano,

 

Thanks for your help regarding this!

 

An example of the URL is:

 

https://maps.googleapis.com/maps/api/distancematrix/json?origins=52.48212,-1.952852&destinations=53....]

 

and signature within the payload

 

The complete URL works fine within the browser, but within Alteryx I get the following error message within the Download Data field of the Download results:

 

Unable·to·authenticate·the·request.·Provided·'signature'·is·not·valid·for·the·provided·client·ID,·or·the·provided·'client'·is·not·valid.

 

Best, Joanna 

 

sagar_agarwal
8 - Asteroid

@patrick_mcauliffe  @KaneG

Apologies as I forgot to come back to share the outcomes - I couldn't get this working unfortunately. After doing some back and forth with my tech team, I learned that the API URL was sitting behind an authentication protocol which I had no idea how to implement that in Alteryx (I am learning this as a side project in Alteryx currently).

 

As I was running short on time, I simply wrote a Java code performing those authentications, hosted it on our application server, made the module accessible via a URL and return back the data; and then used that Java code URL in the Alteryx Download Tool. That worked like a charm!

 

Have a happy 2017!

Dheeraj
5 - Atom

Hello Team,

 

How An R code/Python Code can be implemented into API which can be plugged into Client Site / Client System

brad_j_crep
8 - Asteroid

Hello, I'm trying to bring in a excel file from this location:

https://www.ers.usda.gov/data-products/food-access-research-atlas/download-the-data/

the file name of the file is 'Food Access Research Atlas Data Download 2015.'  I've tried the 5 minute method above and I've tried to a temporary file.  No luck.  The DownloadData column ends up as PK and some characters.

Not sure what I'm doing wrong?  Any help is appreciated.  Thank you.

 

Download.png

patrick_mcauliffe
14 - Magnetar
14 - Magnetar

Hey @brad_j_crep

You're pretty close.

Instead of downloading the website, try copying the link to the spreadsheet and use that as your URL to download.

Think about a web page as your C:\ drive.  To get to a document within the page/drive you need to go down to the next folder.  In this case that's the URL which links to the document.

If you were going to automate this process, then that's where you would download the entire page.  Once you had the entire page, you would find the location in html of the link that gets updated when the file is changed and then you would trim down to just that and use it to download.

Let me know if that helps.

mattamidus
7 - Meteor

Hi Tasha,

 

I followed your diections to the t for the NOAA data sets.  But it doesnt seem to be working.  Any ideas?

 

https://www.ncdc.noaa.gov/cdo-web/webservices/v2#gettingStarted