Using a custom Connector in a Designer Orchestration Pipeline

In the Data Productivity Cloud, the Custom Connector feature can be leveraged to interact with REST APIs and load responses into your cloud data platform target. While there are different ways to interact with Custom Connectors after creation, we’ll focus on how to use Custom Connectors in an orchestration pipeline, the configurations required for use, and how to set configurable parameters for the Custom Connector. 

Our three-part series will take you through the complete process:

• Part 1 will outline how to set up a Custom Connector within the Matillion Hub and make use of the connector within a Designer orchestration pipeline
• Part 2 will outline how to enhance the usage and making use of pipeline variables and iterators to make the API requests from the connector even more dynamic and flexible.
• Part 3 will discuss how to handle the data from the API once it's landed (stay tuned).

Keep your eye peeled for parts 2 and 3!

Creating a Custom Connector

Let's start with creating the Custom Connector. First, log into your Hub account and navigate to Manage Custom Connectors:

Helpful Custom Connector tips

Once in the Custom Connector UI, we can begin to build the Custom Connector. Personally, I've always looked at Custom Connectors like a collection in Postman, where there is an overall name for the profile and then individual endpoints stored within the profile. 

For this example, I am building a Custom Connector to make requests to the SpaceX data API to pull data on SpaceX missions and their capsules.

While building the endpoints in the Custom Connector profile, there's one helpful feature that becomes more powerful the further you move into the process. When populating the URI for the request, users can populate part of the URI wrapped in {} characters to automatically add these as configurable parameters for the API call.

Setup the Custom Connector

To complete the setup of the Custom Connector, populate some valid sample values for the configurable parameters and click on the Send button in the UI to test the request. Then validate the structure of the response as well as the corresponding data types for the JSON object that was returned from the API. 

Once everything is validated, click on the Save button in the UI to save the configurations and endpoints for the Custom Connector.

NOTE: Keep in mind that when setting configurable parameters while building the Custom Connector, these values are for testing and will not be saved to the Custom Connector. However, the configurable nature of the parameter will be saved. We’ll cover this in more detail a little bit later. 

Using a Custom Connector in a Designer Orchestration Pipeline

After the Custom Connector has been saved, it's time to orchestrate API requests with a Designer pipeline!

Navigate to Designer from the Matillion Hub and create a new Orchestration pipeline. Once the orchestration pipeline is created, open the components panel to view all components available, and search for the name of the Custom Connector created earlier.

In this example, the name of the Custom Connector is “spacex_example.” 

Pro tip: All Custom Connector components in Designer will have a display image of “</>.”

Next, we drag and drop the component onto the canvas and start configuring our API calls as we would with any component in Designer.

There are certain properties that the component is expecting, and I will walk through those here. 

Setting connection properties

Connect section of the Custom Connector component

First, the component expects a Data Source to be selected. The Data Source property effectively selects which endpoint in the Custom Connector we want to make a request to.

In this example, my Custom Connector of Spacex_example had two endpoints created: 

• missions
• capsules

We can see both of these present in the Data Source property.

After the Data Source property, we will select authentication. This property will display any authentication that was enabled/configured during the creation of the Custom Connector.

In the example of the SpaceX API, there is no authentication required, so the only option available in the Authentication property on the component in Designer is “None”. However, if any authentication was enabled during the creation of the Custom Connector, that authentication will show up in the Authentication drop-down. 

Configure section of the Custom Connector component

Now, it's time to configure any specific parameters for the API request. In the configure section of the component, we have several properties available.

This is the configuration section where we can customize the API request to the selected endpoint with specific URI/query parameters, headers, POST body payloads (if needed), or page limits. 

Tip: If there were configurable parameters configured during the creation of the Custom Connector, we will see them in the corresponding property here and have an opportunity to set values for the parameters. 

During the creation of the Custom Connector in this example, I set a configurable URI parameter for “mission_id.” In the URI parameters property, I can see this value in the drop-down and can set a value relevant for this API request.

Pro tip: The parameter value can make a reference to a pipeline or project variable by using the notation of ${variable_name}.

Destination section of the Custom Connector component

In the Destination properties, there are a couple of options available. These are:

• The target Data Platform of your Project (for example Snowflake)
• Cloud Storage (for example S3, Blob etc)

Note that selecting Snowflake (or a different CDW) will take the response data from the API and load it into a table in the CDW based on the target table and table load strategy selected in the properties. Selecting Cloud storage will upload the responses as files to Cloud storage.

The power of Custom Connectors

After these configurations are completed, the component is now ready to run. Running the component will make an API request to the endpoint selected and will send the request with the specified parameters that were configured in the component. 

Creating and using Custom Connectors can be extremely powerful and flexible when it comes to ingesting data from an API. Whether it be executing the Custom Connector and underlying API calls via a Data Loader pipeline, or leveraging the Custom Connector in a wider orchestration pipeline in Designer, there are many ways of collecting the information to make it meaningful to overall data and business needs. 

Further References:

• SpaceX API
• Custom Connector Setup