API = Application Programming Interface
API allow different applications to communicate with each other.
APIs are useful because they help systems share data and perform actions automatically. For example, a weather app can use an API to get live weather data, or a business can use APIs to connect systems such as Salesforce or a reporting tool, and so on.
Common API terminology
- Endpoint
The URL where the API request is sent
- Method
The action being requested such as
- GET - retrieve data from the API
- POST - send data to the API to create something new
- PUT - replace or fully update an existing record
- PATCH - update only part of an existing record
- DELETE - remove a record
- Parameters
Values sent with the request to specify what data or results you want
- Headers
Additional information send with the request, often for authentication or data format
- Response
The information returned by the API
API application on Alteryx
Text Input
Drag the Text Input tool onto the workflow, then create a field named URL. Enter the API URL into the field and replace the API key placeholder with your own API key.
Download
Field = URL: Tell Alteryx which field contains the API address
Output = String: Keep the response as a string because the API will return JSON text
Data Encodede As = Unicode UTF-8: How to read the characters in the text correctly. (Unicode UTF-8 is the most common encoding used on the web and by APIs)
After running the workflow, the download tool returns the API response in the DownloadData field. The DownloadHeaders field also shows the HTTP status. A status of 200 OK confirms that the request was successful.
JSON Parse
Add the JSON Parse tool after the Download Tool. Select DownloadData as the JSON field. This converts the API's JSON response into a structured format that can be processed in Alteryx.
The JSON Parse tool converts the API response into separate name-value pairs. JSON_Name shows the field being returned by the API, while JSON_ValueString contains the corresponding value.
Filter
Filter JSON output to only keep the field you need for analysis. In this example, I will keep name main temp main.humidity wind.speed weather.0.main fields.
Cross Tab
Cross tab to reshape the parsed JSON from rows into columns. Set JSON_Name as the column headers and JSON_ValueString as the values for new columns. This creates a table structure for analysis.
Select
Add a Select Tool to rename fields, remove unnecessary columns, and set appropriate data types.
This is the workflow
I demonstrated how to use an API in Alteryx to retrieve weather data, parse JSON response and reshape into a table. This is a simple example of how Alteryx can connect directly to an API and bring external data into a workflow.
Thanks for reading!