Added functionality to the download tool for API queries

[cgoodman3]

When building API calls within Alteryx there are a few common steps required

1) Build out the URI for the API call (base URL plus any query parameters)

2) Deal with authentication, such as basic authentication requires taking a key and secret, base 64 encoding and passing this into the tool

3) parsing the results out and processing these downstream

For this idea I am specifically focusing on step 3 (but it would be great to have common authentication methods in-built within the download tool (step 2)!).

There are common steps required to parse out the results, such as using Filter (to check for a 200 response), JSON parse, text to columns and then cross tab to get the results into a readable format. These will all be common steps anyone who has worked with APIs will be familiar with:

This is all fine for a regular user to quickly add in and configure these tools. However there is no validation here for the JSON result being as expected, which when embedding an API into a batch macro or analytic app means it can easily fail.

One example of a failure which I've recently come across is where the output JSON doesn't have all fields (name:value pairs) depending the json response. For example using the UK Companies House API, when looking at the ceased to act field at this endpoint - https://developer-specs.company-information.service.gov.uk/companies-house-public-data-api/resources... the ceased to act field only appears in the results if a person has actually ceased to act. This is important if you have downstream tools such as a formula to create a field [Active] where you have:

IF ISNull([ceased_to_act]) THEN "Active" ELSE "Ceased to Act" ENDIF

However without modification the macro / app will error if any results are returned where there is not this field.

A workaround is to add in the Crew Ensure Fields or union on a list of fields, to ensure that the Cease to Act field is present in the output for all API calls. But looking at some other tools it would be good if an expected Schema could be built in to the download tool to do this automatically.

For example in Power Automate this is achieved as follows:

I am a big advocate of not making things unnecessarily complicated. Therefore I would categorise this as an ease of use feature to improve the experience of working with APIs within Alteryx and make APIs (as load of integrations are API based) accessible to as many users as possible.

[jarrod]

Yes!

These are great comments about the download tool. I'd add that it doesn't fail gracefully either, if there is an error in libcurl, it just gives up instead of trying again or passing back a decent response. This throws the downstream process off and causes havoc. Would love to see the tool handle auth as well. Creating signatures is as pain most of the time that should be automated within the tool.

[cgoodman3]

How do you deal with with errors? I typically set multiple calls up in a batch macro and write out the results for each call. Then if there are failures I re-run against a directory tool to compare completed results against the original input, but this isn't very automated. 

[jarrod]

that's pretty much what I do as well. Depends on the endpoints (and what they return as "errors"). The one I can't really account for is the libcurl error from the Alteryx engine - at that point, Alteryx returns an error (i don't think it even returns a record) and then I have to guess which call failed.

Sometimes I can find it if I know what calls to expect, but other times i just re-run the full process. Shopify is one that is curious. it consistently fails with 404 errors (I think), so I just filter out 200's and run the workflow again hoping that the next time around it will pick it up. but that relies on the knowledge of what calls did and didn't make it through. The odd thing here is that an iterative macro doesn't work for recovering the bad calls; I have to copy the macro over several times in the same workflow and reprocess after filtering the good records.

I can't help but think that the issue is with how Alteryx deals with the API calls with curl. Hoping I can figure something out in Python and create a custom tool... or maybe a new official API tool... ahem 🙂

[cgoodman3]

I’m definitely for the download tool being split out to individual tools, such as a separate one for api calls, one for webscraping, one for downloading files from links. But I wouldn’t know how to, but happy to learn!

[DanielG]

Anything to make APIs easy to work with, I am all for.

[SeanAdams]

@cgoodman3 - this is one of a series of important ideas about how to make API interaction more robust - thank you for this (and I believe @NicoleJohnson is looking at suggestions around API tooling)

Being able to have a robust way of working with APIs is a critical capability for cloud readiness for Alteryx - so this is clearly on the critical path for Alteryx Capability.

Also - really appreciate you taking the time to do the screenshots of how this works in PowerApps as a well worked example of one way of solving the problem.

Fully agree with you on the ability to retry; fail gracefully; deal with mandated fields; http error codes etc.