Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
Note: Alteryx Server received a number of enhancements with the 2021.4 release. For the 2021.4 release and beyond, please refer to this updated article on server runner macros.
Workflow orchestration is a topic that inevitably arises among seasoned Alteryx users. There are 3 scenarios we hear about most frequently.
- Sequential execution: run workflow A, then workflow B, then workflow C
- Simultaneous execution: run two instances of workflow A at the same time, with different inputs, to reduce overall processing time.
- Nested execution: within workflow A, run workflow B and wait for it to complete, then continue executing workflow A.
Batch macros are well suited for orchestrating workflows, but some use cases may be especially unique. If you’ve exhausted your options with batch macros, you may have turned to the Runner Tool or the CReW macros. While useful in Alteryx Designer, these tools are not supported on Alteryx Server. Fortunately, there is another option. This article will help you get started building your own “runner” macro – with supported tools – and in the process demystify the Alteryx Server API.
- Important
- Objective
- Example Macros
- Alteryx Server API
- API Access
- Key & Secret
- Run a Job on the Alteryx Server
- Step 1: Construct the URL
- Step 2: Construct the URL Parameters
- Step 3: Construct the Signature Base String
- Step 4: Construct the Signature String
- Step 5: Generate the Oauth Signature
- Step 6: Construct the Request String
- Step 7: Question Inputs (Optional)
- Step 8: Submit the Request
- Step 8: Parse the Response
- Orchestration Examples
- Sequential Execution
- Simultaneous Execution
- Nested Execution
- Considerations
- Additional Resources
Important
In a coming release, to streamline your asset management, subscriptions (studios) are going away. Accordingly, Server will also see changes to the API, including an Oauth2.0 framework. The steps below should only be referenced for versions 2021.3 and earlier. You should plan to document workflows interacting with a current version of the API as they will need to be updated as part of a Server upgrade.
Objective
This article is long and yet there is more that could be said on these subjects. With that in mind, this content is intended for users with a working understanding of RESTful APIs, JSON, building Alteryx macros, and the Alteryx Developer tools. It will not cover the basics. If you are new to these concepts, you can get started with the links under Additional Resources.
Example Macros
Most of what follows is a guide for constructing, authenticating, and submitting requests to the Alteryx Server API, rather than a step-by-step workflow tutorial. As a supplement, there are two working macro examples attached:
- Alteryx Server Runner: a macro that accepts user or data inputs and submits a request to run an app on the Server. This macro will output the parsed response including the job id. The macro submits 1 API request per row of incoming data.
- Alteryx Server Job Status: a macro that accepts user or data input and retrieves the status of a Server job. Optionally the macro can be configured to check the status intermittently until the status is “Completed”. The macro submits 1 API request per row of incoming data.
Feel welcome to reference these macros or modify them to suit your needs. Examples of how these macros can be used within a workflow are shown at the end of the article. You should thoroughly test these macros before using them in your production environment.
Alteryx Server API
The Alteryx Server API supports both user and administrative interactions with the Server. This article only focuses on user (subscription) endpoints, but the steps outlined are useful for either. Full documentation on the API is available at http(s)://your.companies.server/gallery/api-docs/.
API Access
Server administrators must enable API access in the Server UI. From the Admin portal, navigate to the subscription that should be granted access, toggle API Enabled to “Yes”, then save the changes.
Key & Secret
Once API access is enabled on the subscription, users within that subscription can obtain the Key and Secret. Log into the Server UI and choose My Profile while hovering over your name in the top right corner, the Key and Secret will be located on the Keys tab. The Key and Secret values used in the subsequent examples are not real. Never share your API credentials!
Run a Job on the Alteryx Server
With your Key and Secret in hand, you can now begin building out a request to the Server API. The following example will focus on running a workflow on the Server (creating a job), but the steps could be modified to interact with other API endpoints. In the macro examples, a Formula tool is used to create all parts of the request strings.
Step 1: Construct the URL
The components of the URL string are show in the diagram below. The base URL to your company’s Alteryx Server should be https. Documentation on all API endpoints is available at http(s)://your.companies.server/gallery/api-docs/.
This endpoint requires that you include the ID of the app that you want to execute. To obtain the app ID, navigate to the app in the Server UI and click into it. Copy the string following the final forward slash “/” in the URL, this is the app ID. App IDs may also be retrieved programmatically by submitting a GET request to the subscription endpoint.
Step 2: Construct the URL Parameters
The following parameters must be included in each request.
In the example macros, the following expressions were used to generate the parameter values below. The oauth_nonce parameter could be generated many ways; this is just one way.
- oauth_nonce: IntToHex(RandInt(100000000))
- oauth_timestamp: ToString(DateTimeDiff(DateTimeToUTC(DateTimeNow()),"1970-01-01 00:00:00","seconds"))
The parameter string is constructed by appending the parameter name with its value, separated by “=”. Combine all name value pairs separating each with “&”. Your parameter string will look like the diagram below.
Step 3: Construct the Signature Base String
The signature base string is one of the inputs used to generate the authentication signature. There are 3 components to the signature:
- HTTP Method: POST in this case, but it should match the API endpoint
- Percent encoded URL from Step 1: the example macros copy the URL to a new field then encode it using a Find Replace tool with values from a reference table.
- Encoded parameter string from Step 2: the example macros encode the parameter string using the built-in function UrlEncode().
Below is an example of what the signature base string will look like.
Step 4: Construct the Signature String
Append a single “&” to the end of the Secret obtained for your profile. This is the second input required for generating the authentication signature.
Step 5: Generate the Oauth Signature
Alteryx does not have a built-in function for HMAC-SHA1 encryption. For this step, you will need to leverage a script to generate the signature. The attached Create Oauth Signature macro will execute this step for you using Python. Or you could write your own! If you are using the attached macro to generate the signature, map the following fields:
- String: the signature base string from Step 3
- Key: the secret string from Step 4
The macro will output a new field containing the signature. This is the final parameter needed to authenticate your request.
Step 6: Construct the Request String
You now have all the pieces needed to create the final request string.
- Append “?” to the URL string created in Step 1
- Append the parameter string created in Step 2
- Append “&oauth_signature=”
- Append the signature created in Step 5
Step 7: Question Inputs (Optional)
If the app you are executing accepts inputs with Interface tools, you can pass these inputs in the body of the request. To do so, each input value must be associated with the Interface tool’s name. The name is defined in Alteryx Designer in the Configuration window, on the Annotation tab. In the example below, the Text Box displays “Enter your text”, but the tool’s name is “text_box_1”.
The JSON body below would pass a value of “This is my input!” to text_box_1 when the workflow is executed.
The example runner macro accepts question inputs using a List Box tool. The user can select fields to include, and they will be formatted and submitted in the request body. The field name must match the Interface tool’s name to execute correctly.
Step 8: Submit the Request
Use a Download tool to submit the request. On the Basic tab select the request field created in Step 6.
On the Headers tab, specify the Content-Type as application/json.
On the Payload tab, choose the correct HTTP action.
Finally, if you are submitting question inputs in the body, select this option on the Payload tab and map the body field.
Step 8: Parse the Response
Congratulations! You’ve submitted your first request to the Alteryx Server API. The Download tool outputs 2 fields:
- DownloadHeaders: if your request was successful, you will see a message like HTTP/1.1 200 OK in this field. If you see a different status code, you will need to troubleshoot your request. In addition to the status code, the header field will contain a description of the error.
- DownloadData: this field will contain the JSON response from the Server. This can be easily parsed using the JSON Parse tool. Upon completing a successful request, the response will contain an id field which is the newly created job id on the Server.
Orchestration Examples
Once you have your macros functional, you can use them in workflows to interact with other apps on the Server. Using the example macros, the three scenarios outlined at the beginning of the article are shown below.
Sequential Execution
In the example below, the runner macro is the final tool in the workflow. The Block Until Done tool is used to ensure all records are written to the database before the request to the Server API is made. The example runner macro will execute 1 API request per row of incoming data. A Sample tool is used prior to the runner macro to ensure that only 1 request is made. The runner macro creates a job on the Server for the next app in the sequence.
Simultaneous Execution
As mentioned, the example runner macro will submit 1 request to the Server API for each row of incoming data. The goal of simultaneous execution is to run the same workflow with different inputs. Running them simultaneously reduces the overall processing time. In the example below, the column text_box_1 contains 3 rows of data, or the three inputs we want to run through the app. The runner will create a new job on the Server for each input.
Nested Execution
When a job is created through the Server API, it does not wait until that job has completed before returning a response. It simply creates the job on the Server, then returns a response indicating if the job was successfully created. Using the example macros in tandem enables nested execution. In the example below, the runner creates a new job on the Server. The newly created job id is passed to the job status macro which checks the status each minute until the job is complete. Read the considerations before proceeding with a nested execution.
Considerations
As you can see, the Server API unlocks many powerful capabilities with the Alteryx platform. Third party applications can also interact with the API to execute Alteryx apps. That said, it is important to be mindful of your Server’s configuration so that you do not inadvertently cause issues. Partner with your server administrator and your IT team to determine who should have API access and in which contexts. Below are a few critical items to consider when using the Server API.
- Do you really need a runner macro? Batch macros were mentioned at the beginning of the article as another option for orchestration. Particularly for nested execution, these will be easier to create and implement.
- Simultaneous workflows: Alteryx Server is configured to execute a specified number of workflows simultaneously (on the Worker Configuration screen in System Settings). Once this number is reached, any scheduled or manually executed jobs will go into a queue. To understand the implications of runner macros, let’s look at a simple example.
- Suppose you have a single Alteryx Server on a 4-core machine and the number of simultaneous workflows is configured to 2 (the recommended configuration in this case). You schedule a workflow on the Server that will use a runner macro to create 10 jobs each time it runs. On average, it takes each job 5 minutes to complete.
10 app executions * 5 minutes / 2 simultaneous workflows = 25 minutes
Assuming no other jobs are running or queued on the Server, 2 will immediately begin executing while the remaining 8 will go into the queue. In total, the jobs will take 25 minutes to execute. Any new jobs that arrive during this time will be added to the bottom of the queue.
- Suppose you have a single Alteryx Server on a 4-core machine and the number of simultaneous workflows is configured to 2 (the recommended configuration in this case). You schedule a workflow on the Server that will use a runner macro to create 10 jobs each time it runs. On average, it takes each job 5 minutes to complete.
- Schedule Forecast: if you are using Alteryx Server 2021.1 or later, you may be familiar with the Schedule Forecast. Jobs created through the API are a manual run type and do not appear in the schedule forecast. Alternatively, you can monitor activity using the Server Usage Report.
- Cancel long-running jobs: Alteryx Server can be configured to cancel jobs that run longer than a defined number of seconds (on the Worker Configuration screen in System Settings). Consider if enabling this option is useful in the context of runner macros.
- Run Mode: if the default Server Run Mode is set to “Safe”, it may not be possible to use runner macros. The encryption step requires a Python, R, or Command Line script to generate the authentication signature. These tools are blocked in safe mode.
Additional Resources
- Get Started with APIs
- Gallery API Overview
- What is JSON?
- Getting Started with Batch Macros
- Interactive Lessons: Macros, Analytic Apps
- Developer Tools: Download, JSON Parse
