Matillion ETL API - v1

Matillion ETL API - v1


Overview

This is a guide to providing details on the Matillion ETL API services and describe the general usage of the Matillion ETL API-v1. The Matillion ETL API is designed to make it easy to interact and enable you to extend the functionality of the product, and perform high volume data transfers and to interact with Matillion ETL programmatically.

The Matillion ETL API is available on standard REST-based APIs that uses HTTP or HTTPS request to GET, POST, and DELETE data. The API service is accessed through the Uniform Resource Identifier (URI).

Important Information

  • Users responsible for experimenting with Matillion ETL API services require access to the Matillion ETL instance and ought to know how to make REST API calls either using a REST API GUI client such as Postman or using a command-line interface like cURL.
  • For more information on "how to use GUI client Postman", please refer to our support documentation on Getting started with Postman.
  • For more information on "how to use Command-line interface cURL", please refer to our support documentation on Getting started with cURL.
  • For a hierarchical map of all v1 API endpoints, please see the API v1 Maps article.

Matillion ETL API

Base URI

http(s)://<InstanceAddress>/rest/v1/<endpoint>

API Endpoints and Description

Accessing endpoints through your browser will yield metadata that will help you navigate the API. Below is a list of all top-level endpoints and their descriptions.

Endpoint Base URL Description
apiprofile http://<InstanceAddress>/rest/v1/apiProfile/name APIs concerning API profiles set up in the Matillion instance. For detail information on the APIProfile API, please visit the API v1-APIProfile article.
audit http://<InstanceAddress>/rest/v1/audit For exporting Audit Logs from the Matillion instance. For detail information on the Audit API, please visit the API v1-Audit article.
credential http://<InstanceAddress>/rest/v1/credential APIs concerning the Credentials (AWS and GCP) set up in the Matillion instance.
group http://<InstanceAddress>/rest/v1/group/name APIs concerning entire groups within the Matillion instance. This includes accessing data on, importing and exporting entire Matillion ETL projects. For detail information on the Group API, please visit the API v1-Group article.
driver http://<InstanceAddress>/rest/v1/driver APIs concerning the Database Drivers set up in the Matillion ETL instance.
notice http://<InstanceAddress>/rest/v1/notice APIs concerning the Notice panel in the Matillion ETL client. For detail information on the Notice API, please visit the API v1-Notice article.
oauth http://<InstanceAddress>/rest/v1/oauth/name APIs concerning the thrid-party OAuths set up in Matillion ETL for use with the data staging ('Query') orchestration components. For detail information on the OAuth API, please visit the API v1-OAuth article.
permission http://<InstanceAddress>/rest/v1/permission APIs for viewing, importing and exporting user permissions.
queue http://<InstanceAddress>/rest/v1/queue APIs concerning the SQS ConfigurationSQS Configuration set up in the Matillion ETL instance. For detail information on the Queue API, please visit the API v1-Queue article.
task http://<InstanceAddress>/rest/v1/task APIs concerning the status and execution of tasks in the Matillion ETL instance. For detail information on the Task API, please visit the API v1-Task article.
userconfig http://<InstanceAddress>/rest/v1/userconfig View users and import/export their configurations. For detail information on the Userconfig API, please visit the API v1-Userconfig article.

Please Note

  • Matillion ETL API require authentication to make any REST API call. Matillion API uses HTTP Basic Authentication. The details of the HTTP basic authentication are beyond the scope of this document, but it essentially requires a username and password which are hashed employing a Matillion ETL instance.
  • If using a Bash or Python component to call the API on that same instance, use the instance's Private IP or local host (127.0.0.1:8080 for HTTP or 127.0.0.1:8443 for HTTPS) for the InstanceAddress.
  • Matillion ETL API can be accessed by adding /rest/v1 to the end of your instance location. For example:127.0.0.1/rest/v1/. To jump to the v1 API, just run {host:port}/rest/v1 in your browser.

Matillion ETL API v1-Graphical Flow

Matillion ETL APIs Flow

Below is the graphical illustration of the list of Matillion ETL API v1

Matillion ETL API v1 - Flow

Matillion ETL API v1 - Flow

Matillion ETL APIs and Endpoints Flow

Below is the graphical illustration of the Matillion ETL API v1 with all associated endpoints.

Matillion ETL API v1 Endpoints- Flow

Matillion ETL API v1 Endpoints- flow


Server Response Status Codes

Code Response Description Output Message
200 Success - The request was successfully received, understood, and accepted.
{
	"success": true,
	"msg": "xyz",
	"id": 123456
}
400 Bad Request - The request result of malformed request syntax, invalid request message parameters, or request routing, etc.
{
  "success": false,
  "msg": "Bad Request",
  "id": -1
}
401 Unauthorized - Authentication is required for the API request and it was not present in the request. Typically this means that the basic authentication information is required for the request.
{
  "success": false,
  "msg": "Unauthorized",
  "id": -1
}
403 Forbidden - Access to the specified resource is not permitted.
{
    "success": false,
    "msg": "Access Denied",
    "id": -1
}
404 Not Found - The requested resource does not exist.
{
    "success": false,
    "msg": "HTTP 404 Not Found.",
    "id": -1
}
405 Method Not Allowed - The HTTP method is not supported for the specified resource. Keep in mind that each resource may only support a subset of HTTP methods. For example, you are not allowed to DELETE the root resource.
{
    "success": false,
    "msg": "HTTP 405 Method Not Allowed.",
    "id": -1
}
409 Conflict - This is typically the response to a request to create or modify a property of an entity that is unique when an existing resource already exists with the same value for that property. For example, attempting to create a group with the same name as an existing group is not allowed.
{
    "success": false,
    "msg": "Name already exists.",
    "id": -1
500 Internal Server Error - 500 is the generic REST API error response. Therefore, it is reasonable to try again later and consider notifying Matillion Support.
500 Internal Server Error

URL Parameters and Description

Below is the list of endpoint parameters (used in the guide) and their brief description:

Parameters Name Description
<InstanceAddress> This is the server IP address or domain name.
<APIName> The name of the API such as "REST" or "Bulk.
<APINumber> This is the version number of the API such as v0, v1 etc. Matillion API using "v1" version number for all the API request.
<version> This is the API version (Not versions created in the tool).
<endpoint> Endpoint is the part of the API used to make a API call.
<groupName> The name of the group created in the Maillion ETL instance.
<projectName> Get the Project API detail created in the group by its name.
<scheduleName> Get the schedule API by its name.
<versionName> Get the version API detail by its name.
<jobName> Get the job name within the project by its name.
<delete> Delete the resource within the the available resources.

API Request and Examples

Once you have learned about authentication, and a base URI of Matillion API, you can start making API requests using Postman or cURL. A basic request has several components not including authentication.

The Matillion ETL REST API is available using the following URI pattern.

http(s)://<InstanceAddress>/<APIName>/<APINumber>/<endpoint>

Where <API Name> is the name of the API, such as 'rest', and <API Number>is the version of the API, such as "v1".

Therefore an example of a complete request would be as follows:

GET https://<InstanceAddress>/rest/v1/group

The endpoint in the above example is /group and HTTP Method is GET. That will return a list of available groups defined within the Matillion ETL instance.
A particular group can then be specified by the suffix group/name/<groupName>. For example, if we want to access the Example-group group then we would use:

 GET http://<InstanceAddress>/rest/v1/group/name/Example-group

HTTP Methods

There are 5 basic request patterns which apply to most of the Matillion API endpoints, and these are based on the HTTP Methods. The basic request patterns are:

Below are examples and detailed description of each pattern:

Retrieve a single resource (GET)

In the example below, we will be retrieving a single resource information, perform a GET request for that named resource endpoint.

Whenever you reach a named resource endpoint, the API will expose API metadata for that resource, including PATH, GET and POST and DELETE method options available. In the example below, the metadata would show PATH options for the Example-project.

  • Base URL
    http://<InstanceAddress>/rest/v1/group/name/<groupName>/project/name/<projectName>
  • Server Response
      {
      "endpoints": [
        {
          "httpMethod": "PATH",
          "name": "ProjectInstanceService",
          "children": [
            {
              "httpMethod": "PATH",
              "name": "getSchedule",
              "description": "Allows accessing Schedule APIs",
              "path": "/schedule",
              "children": [
                {
                  "httpMethod": "GET",
                  "name": "export",
                  "description": "Exports all of the schedules in the project",
                  "path": "/export",
    			},
                {
                  "httpMethod": "PATH",
                  "name": "getBySomething",
                  "description": "Get a Schedule API by query parameters",
                  "path": "/instance",
    			},
    				......
    

Please Note

The Resource names are case-sensitive and must be URL encoded where appropriate (for example, when the resource name contains a space).


Retrieve a list of available resources(GET)

To retrieve the list of projects available in the group, you could use the URI for the <group><project>. It is again a GET API request that will expose the list of projects available within the selected group.

  • Base URL
    http://<InstanceAddress>/rest/v1/group/name/<groupName>/project
  • Server Response
    [
        "Example-project ",
        "Example-project 1",
        "Example-project 2",
    ]
    

To attain a list of projects within that group that have been defined within the Matillion ETL instance. This process can be repeated to "dig down" into lower levels of the API, gaining access to increasingly specific resources such as individual jobs and tasks.

To illustrate this further, below is a diagram of the /group endpoint showing possible PATH, GET and POST and DELETE options.
 

Group endpoint Flow

Group endpoint Flow


Delete a resource (DELETE)

To remove any resource from the list, we will use DELETE API request. In this example, we will delete a schedule from within the project in the specific group.

Please Note

There are often multiple ways of achieving the same task within the API as resources and methods branch out and overlap.

  • Base URL
    http://<InstanceAddress>/rest/v1/group/name/<groupName>/project/name/<project Name>/schedule/name/<scheduleName>
  • Server Response
    {
      "success": true,
      "msg": "Successfully deleted schedule [Exampleschedule]",
      "id": 0
    }

Export a resource (GET)

To export the project, provide the projectname and use the /export endpoint. This will export the selected project and all associated information such as variables, environments, schedules, versions, jobs, and more details, alligned with the project.

  • Base URL
    http://<InstanceAddress>/rest/v1/group/name/<groupName>/project/name/<projectName>/export
  • Server Response
    	{
        "objects": [
            {
                "name": "APIProject",
                "variables": []
    		 "environmentExports": {
                    "objects": []
    			  "version": "1.44.11",
                    "environment": "redshift"
    				},
    		 "scheduleExports": {
                    "objects": [],
                    "version": "1.44.11",
                    "environment": "redshift"
                },
                "versionExports": {
                    "objects": []
    	}			
    

Please Note

Project exports using v1 API do not include passwords.


Importing a resource (POST)

Now that you have an exported project (see previous example), this time we use the API to import that project into a Matillion ETL instance. Note that, when importing, there is no "merge" option. If a resource of the same name already exists, you must delete the existing resource before importing the new.This will be a POST method API call as we will have to attach the project (in JSON form, as exported), in the body as a JSON file to import into the Matillion ETL instance.

Please Note

In the exmaple we are not naming a specific project as we hope to import one that does not yet exist.

  • Base URL
    http://<InstanceAddress>/rest/v1/group/name/<groupName>/project/import
  • POST (JSON) Body
    	{
        "objects": [
            {
                "name": "APIImported",
                "variables": []
    		 "environmentExports": {
                    "objects": []
    			  "version": "1.44.11",
                    "environment": "redshift"
    				},
    		 "scheduleExports": {
                    "objects": [],
                    "version": "1.44.11",
                    "environment": "redshift"
                },
                "versionExports": {
                    "objects": []
    	}			
    
  • Server Response
        {
      "name": "Projects",
      "children": [
            {
              "name": "APIImported",
              "statusList": [
                {
                  "success": true,
    			  "name": "APIImported",
              ],
            },
    

The API call will create this project in the Matillion ETL instance. Note that we have changed the project name to "APIImported" in the JSON, else we could get an error for importing a job whose name already exists (APIProject). This project can now be switched to in the Matillion ETL instance.

Group endpoint Overview

APIImported Project Flow

API Import conflicts - Explaination

There is an optional parameter for API Import: "onConflict", which determines what should happen if an import clashes with something that already exists, e.g. a project with a given name. The options are "ERROR", "SKIP", and "OVERWRITE".

This can happen when the user tries to import: Project Groups, Projects, Versions, Jobs, Passwords, Schedules, and Environments. Each outcome is as follows:

  • ERROR – Sends an error back.
  • SKIP – Skips importing any clashes, preserving the existing object.
  • OVERWRITE – Removes the existing object and replaces it with what has been imported.

Examples as given below (using cURL):

curl -X POST -u api-user:api-user "https://<InstanceAddress>/rest/v1/group/import" -H "Content-Type: application/json" --data-binary @mtln_project_grp.json

would become:
curl -X POST -u api-user:api-user "https://<InstanceAddress>/rest/v1/group/import?onConflict=ERROR" -H "Content-Type: application/json" --data-binary @mtln_project_grp.json

or
curl -X POST -u api-user:api-user "https://<InstanceAddress>/rest/v1/group/import?onConflict=SKIP" -H "Content-Type: application/json" --data-binary @mtln_project_grp.json

or
curl -X POST -u api-user:api-user "https://<InstanceAddress>/rest/v1/group/import?onConflict=OVERWRITE" -H "Content-Type: application/json" --data-binary @mtln_project_grp.json

Depending on what you want to happen when you try to import something that already exists. The default is ERROR.