Download Tool - API Question (POST Action)

[DanielG]

Hi,

I have been using Alteryx for a while, but am new to the world of APIs and using the Download tool to interact with an API.  

I have datasets of about 2000-3000 records that I need to "send up" but I have set it up to run each row individually rather than create 30 payloads of 100 records per.  

With that I am getting inconsistent runs where the majority of time it handles all the rows, other times it only returns some of them.  No real errors in the Alteryx process that I can see.  It appears to solely be that when the API returns less records it throws "401 unauthorized on those rows".  That doesnt make sense because all of the other "OK" records use the same key and authorization.  And sometimes the runs are completely success and those same rows come bake "OK".

Does anyone have any thoughts?  Or can someone just confirm if I should/shouldnt be pushing each row individually?  

Within the basic tab of the download tool, my text string "URL" field is picked for the URL field.  And the checkbox for Encode URL text IS checked.

In headers, I load the two headers (as constants) that show in the samples from the URL syntax within the API documentation.  No boxes are checked below.

In the payload I select the "Take Query String/Body from Field" which is a field that I took sample of the payload from URL documentation and I replace the placeholders with my pertinent data fields.

I did not alter anything in the Connection tab, so it shows the name/pw as empty and the last 2 boxes show max connections = 2 and timeout = 600

I apologize for my lack of API knowledge, hopefully I explained this well enough to be understood.  If not, please let me know what I am missing.

[bbak91]

Do you have access to the API documentation? Your "30 payloads of 100 records per" most likely just needs your query string/body to have JSON built that accommodates batches of records rather than a single one. 

[DanielG]

@bbak91 - thanks for replying.

Yes.  The API documentation (https://smartystreets.com/docs/cloud/us-street-api) says a POST request can have a maximum of 100 records per request.   

I will attempt passing more than 1 item at a time in the payload by concatenating individual items and making sure the JSON syntax is structured correctly.  I will let you know how successful I am with it...  🙂

What is the reason for 1 item not working though, as my JSON in the individual payload matches what the API documentation shows AND they work most of the time.  So I am missing something here.  Probably has to do with my lack of knowledge of APIs and what they do behind the curtain.  Does a POST require it to be more than 1 item and a GET is for a standalone item?  That is something I see in the documentation, but again I dont fully understand all of this yet.  Thanks!!!

[bbak91 · Accepted solution]

Based on the way that this API has been set up by the company, it does in fact look like you perform a GET request for a single address and a POST request with a query string for multiple addresses:

 

My best guess for why this sometimes doesn't work is because it calls out that they have to be in one of three specific combinations in order to work properly. If the address doesn't contain these elements it looks like it will fail:

 Other potential issues that you might find with the API call can be found in the Download Headers column of your output. It should return some additional information that might point you in the right direction: 

 

[bbak91]

Are you seeing a specific Download Header when it doesn't work right?

[DanielG]

I usually get the 401 Unauthorized which is why I am confused.  Sometimes it works and sometimes it doesnt, but I am always passing the same credentials each time via the URL

[bbak91]

Are you using an auth-id and auth-token? Do these change over time or are they meant to be static?

[DanielG]

@bbak91 

Yes.  I use ID and token.  They are static.

[DanielG]

@bbak91 - thanks for your help thus far.  It has really helped out.  Still not quite there.

There has to be something with this API.  I successfully created the payload with 50 records in it.  Consistently getting 1800 rows after JSON Parse tool which immediately follows the Download tool.  I ran it ALOT to see that it was working.  I then add in some other tools AFTER the JSON Parse to get the JSON outputs into a easy to read format and I suddenly get the unauthorized error.

Since I converted the payload to multi-records they all start out with HTTP/1.1 100 Continue.  When working that information is followed by the HTTP/1.1 200 OK line, but what seems to be arbitrarily I get these unauthorized results which show like this: 

HTTP/1.1 100 Continue

HTTP/1.1 401 Unauthorized

Aren't APIs supposed to make my life easier?  haha.  🙂 

[bbak91]

In theory, yes! Also, have you done a "Cache and run workflow" yet? If you right click on a tool and click cache and run workflow it will execute the workflow up until that point and the next time you run it, it will start from there. This may be a good idea if the API is throttling you while you develop your process. It sounds like this may be the case. 

I know that you already have an address validation service that your company pays for, but I would HIGHLY recommend taking a look at our location insights package. We have CASS Standardization/address validation as well as things like drive time calculations and a variety of different spatial layers. It also functions as a local installation so you don't need to make API calls in order to do it (natively integrated within Alteryx), and you have unlimited use of it. 

https://www.alteryx.com/sites/default/files/2019-02/Location-Business-Insights-Datasheet.pdf

I know that your objective is to get your API working for now, but I wanted to let you know about our Location Insights in case it might be of interest. My recommendation for your current workflow is definitely to "Cache and Run" by right clicking on a tool after the download tool so that way it doesn't have to make the call again while you are building out the rest of your parsing.