How to use an API to Download Data?

[JohnMaty]

Hi All,

I am trying to figure out how to use APIs to download data and update a table on my side.  I am using the following

Police Incident Blotter (Archive) - Blotter Data (UCR Coded) - CKAN (wprdc.org)

It says there are APIs available (see photo) but I cant figure out how to use them.

Basically I am trying to create a table with all the data and then update the table with new data on a daily basis.  I am unsure what to do becuase I thought APIs required keys.  Please help!

[JamesCharnley]

Hi @JohnMaty 

In regards to the concept of API keys and authorization, that's all dependent on the API in question, this API doesn't seem to need authorization, which makes our lives easier.

To use an API within Alteryx, put simply, we can use the download tool pointing at a url. Within your screenshot you can see the endpoints available, and in the 'querying' tab there are some examples calls that work. For example I've taken the first example url which queries the first five results in the table, pasted it into a text input, and simply connected a download tool with no extra authorization and json parsed it.

You'll then, I assume, just need to first build a workflow that calls the API to create the whole table, and then make another workflow with a separate call that searches for the most recent date in the dataset as described in the documentation, and then schedule the workflow to run daily and add the new records.

[JamesCharnley · Accepted solution]

I happen to be putting together some API training right now so played around a bit more and found that the results of this particular API are paginated to a maximum of 100 results per page. That means an iterative macro is needed to take the 'result._links.next' url and call the API again to get the next 100 results until all results are found. It looks like this, which is a fairly common design pattern when dealing with paginated APIs:

I've attached the macro set to a maximum number of iterations of 10 so I didn't slam the API too much unnecessarily but it proves it works. Let me know if you have any questions.

[JohnMaty]

Wow!  I thought it was me but I was only getting 100 results returned and did not know why.  Thanks!

[JohnMaty]

Dumb question mainly becuase I never used an iterative macro before but where to you set the iterations?

[JamesCharnley]

@JohnMaty  you can set the maximum number of iterations within the Interface Designer's properties tab! You can access it via View -> Interface Designer or the shortcut Ctrl + Alt + D

[JohnMaty]

OK so I tried that and it still gives me only 100 records

I am being thick but it still only briging back 100 records, regardless of iteration.

[JamesCharnley]

Hi @JohnMaty 

Sorry for missing this. The macro will only ever show the first 100 records when you run it, but if you connect an input with the correct url to the macro in a separate workflow it should work. 

[KorkuSep]

any of you come across the Download tool throwing an error like this? I am trying to pull data, the first part work downloading the token but the second part is not able to GET data, a read around it suggest to disable the ssl by using (-k --insecure) don't know where in the Download tool to do that.

Note: Run Command tool is able to show the data but does not output it to a browse too or output file.

I will appreciate any help.