Matillion ETL API - v1
    • Dark
      Light

    Matillion ETL API - v1

    • Dark
      Light

    Article Summary

    Overview

    This guides details the Matillion ETL API services and describes 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).

    Note

    • 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 target="_blank">Getting started with Postman.
    • For more information on "how to use Command-line interface cURL", please refer to our support documentation target="_blank">Getting started with cURL.
    • For a hierarchical map of all v1 API endpoints, please see the API v1 Maps article.

    The best-practice method for running jobs as part of a pipeline is to use a messaging queue such as:



    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.

    EndpointBase URLDescription
    apiprofilehttp://<InstanceAddress>/rest/v1/apiProfile/nameAPIs concerning API profiles set up in the Matillion instance. For detail information on the APIProfile API, please visit the API v1-APIProfile article.
    audithttp://<InstanceAddress>/rest/v1/auditFor exporting Audit Logs from the Matillion instance. For detail information on the Audit API, please visit the API v1-Audit article.
    credentialhttp://<InstanceAddress>/rest/v1/credentialAPIs concerning the Credentials (AWS and GCP) set up in the Matillion instance.
    grouphttp://<InstanceAddress>/rest/v1/group/nameAPIs 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.
    driverhttp://<InstanceAddress>/rest/v1/driverAPIs concerning the Database Drivers set up in the Matillion ETL instance.
    noticehttp://<InstanceAddress>/rest/v1/noticeAPIs concerning the Notice panel in the Matillion ETL client. For detail information on the Notice API, please visit the API v1-Notice article.
    oauthhttp://<InstanceAddress>/rest/v1/oauth/nameAPIs 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.
    permissionhttp://<InstanceAddress>/rest/v1/permissionAPIs for viewing, importing and exporting user permissions.
    queuehttp://<InstanceAddress>/rest/v1/queueAPIs 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.
    taskhttp://<InstanceAddress>/rest/v1/taskAPIs 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.
    userconfighttp://<InstanceAddress>/rest/v1/userconfigView 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

    CodeResponse DescriptionOutput Message
    200Success - The request was successfully received, understood, and accepted.
    {
    "success": true,
    "msg": "xyz",
    "id": 123456
    }
    400Bad Request - The request result of malformed request syntax, invalid request message parameters, or request routing, etc.
    {
    "success": false,
    "msg": "Bad Request",
    "id": -1
    }
    401Unauthorized - 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
    }
    403Forbidden - Access to the specified resource is not permitted.
    {
    "success": false,
    "msg": "Access Denied",
    "id": -1
    }
    404Not Found - The requested resource does not exist.
    {
    "success": false,
    "msg": "HTTP 404 Not Found.",
    "id": -1
    }
    405Method 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
    }
    409Conflict - 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
    500Internal 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 NameDescription
    <InstanceAddress>This is the server IP address or domain name.
    <APIName>The name of the API such as "rest" or "bulk. Matillion API are "rest" APIs, which uses URIs and HTTP protocol and JSON for data format.
    <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.
    <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 detail within the selected project by its name, in a specific group of the instance.
    <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 example 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). You can now switch to this project in your Matillion ETL instance.

    Group endpoint Overview

    APIImported Project Flow

    API Import conflicts - Explanation

    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.


    Video Example