Using a custom connector in a Designer pipeline with configurable values and iterators

In this blog, I will expand on the concept outlined in part one to expand the usage of a Custom Connector to make the query/URI/Header or POST body details for a specific API call configurable. Additionally, I will show how to utilize Orchestration components to loop through different parameters for an API call, which can help automate and manage loading data from an API with varying values. 

Let's take the SpaceX API again here as an example. In the last blog, I created a Custom Connector with technically a configurable query parameter for the endpoint, but set it to a static value ultimately. This will lead to the Custom Connector being useful for only one query parameter. This would be equal to the following configuration:

This may be useful for an API request that needs to be re-used with the same parameters. However, what if there are a set of values that need to be passed into the API request to make multiple API calls?

Let's take the Missions endpoint again in this example and set it to have configurable query parameters again by wrapping the part of the endpoint that needs to be dynamic in ‘{}’ with a meaningful name for the parameter:

Now, just provide this parameter a value to test the endpoint, and click save. The custom connector is now configured to have dynamic parameters, which will be used later!

The way to use the Custom Connector in a Designer pipeline will be the same as outlined in Part 1 of this blog series, but let's look at how we can utilize iterators to invoke multiple API calls (either in parallel or sequentially).

First, let's set up some pipeline variables that will be used in this flow. After having your Custom Connector in your pipeline, go to the Variables menu option and create a pipeline variable of type text (or number if that makes more sense for your API). The behavior of the variable should be set to copied, which means that every thread of a pipeline/iteration will get the original default value of a variable. This is a crucial requirement for parallel iterations leveraging variables. See more about variable behavior in the Variable Behavior reference documentation.

Note: The variable doesn't technically have to have a default value, although without one the pipeline may face validation errors prior to being executed. Given this, it's best practice to set all variables to have a default value. 

Let's start by using the Fixed Iterator to loop through multiple Mission IDs for this endpoint. The fixed iterator effectively takes a list of values to loop through, and the list needs to be set at the pipeline's design time. 

To do this, search for the Fixed Iterator in the components list and drag it onto the canvas in Designer. From here, select the Fixed Iterator component, and drag a connector line from the blue circle at the bottom of the component, and connect it to the Custom Connector component to attach it to the component:

Once the iterator is connected to the component, select the Fixed Iterator component to expose its properties so we can start to configure it. 

The Fixed Iterator component is going to require 3 properties to be configured:

• Concurrency
• Variables to Iterate
• Iteration Values

Set the Concurrency property to concurrent to enforce that all iterations happen in parallel with each other.

For the Variables to iterate property, let's select the variable we created previously, ‘mission_id’.

In the Iteration values property, we can create a list of values that need to be passed into the mission_id variable. 

Now, let's configure the Custom Connector component to accommodate these variable iteration values into properties such as the target table it will create upon each iteration, as well as the URI parameters it will use when it makes each API request.

In the URI parameters property, select the configurable URI parameter that was set during the creation of the Custom Connector in the parameter name drop-down menu (in this case, as I set the configurable value in the Custom Connector definition to have configurable URI parameters, but if there were configurable query parameters set for instance, you would see your configurable values in the query parameters property in the component). Set the parameter value to the pipeline variable, which is ${mission_id}. This will make it so that upon each iteration of the fixed iterator, the parameter value will be updated. 

Finally, set the Table Name property to a base table name, followed by the pipeline variable name. This will make it so each iteration of the API creates its own table in the warehouse that reflects the value of the iteration:

After this, everything is configured and ready to run! Overall, this will behave the following way:

• The iterator will loop through all of its defined values
• Upon each iteration, it will pass in the iteration value into the pipeline variable (mission_id in this case) to pass it into the query parameter for the API request
• Upon each iteration of the API request, it will create a table in the data warehouse that reflects the value of the iteration

To show an example of what this looks like after its run:

Here are the following tables that were created in my Snowflake warehouse:

Note that a similar result can be achieved with other iterators, such as the Table Iterator. The Table iterator will loop through records in a table in the data warehouse, and will update pipeline variables based on the record it is looping through. This can be exceptionally useful if there is a ‘control table’ that is holding values that need to be passed into an API request or if specific data points need to be leveraged as part of an API request. 

In part three, I will be covering what the initial data looks like when it lands in my warehouse and how we can extract raw JSON data from API responses via Designer transformation pipelines. Stay tuned!

References:

• Variables
• Fixed Iterator
• Table Iterator
• Space X API