Creating API Profiles Support

Creating API Profiles Support


Overview

This document looks at an example API profile with a view to showing the options available and where to find further information about the functionality of these options.

Important Information

This is one of a series of documents which cover many different aspects of writing API profiles:

Warning

  • The RSD syntax used by Matillion ETL has changed significantly from version 1.41. For the latest information, please refer to Paging with the REST API Query Component 2020.
  • Due to these changes, the syntax of examples used in this document and RSD files on Matillion ETL may differ slightly from current API standards—for example, <rsb: is now <api:.
  • Other updates include the need for users to setup input parameters as inputs rather than dummy columns, and only returning the date on Date data types as opposed to the datetime as previously.

Breakdown of an RSD File

This example RSD file shows what could be included in an RSD file and hopefully gives an idea of what is possible. As all API endpoints differ, not every eventualities have been covered in this section. For further help please contact the support team.

Example RSD file – Line 1 to 39

  • Line 5 to 12

    Details of the location of the data in the JSON file which provide the output columns in the table to be created. These give the column names, location of the data and data type. Whether the column is a primary key or required can also be specified here:

    <!-- Output columns -->
    
    <attr name="id" xs:type="integer" other:xPath="internal_id" /> <attr name="record_type" xs:type="string" other:xPath="record_type" /> <attr name="name" xs:type="string" other:xPath="record_name" /> <attr name="value" xs:type="double" other:xPath="value" /> <attr name="date" xs:type="datetime" other:xPath="record_date" /> <attr name="address_line1" xs:type="string" other:xPath="addresses/address_line1" />

    Please Note

    • Available datatypes are: integer, string, datetime, double and boolean
    • Further details on this is available on the Manage API Profiles document. A brief summary of the parameters used in the <attr, <input and <output tags are given in the sections at the bottom of this page.
  • Line 14 to 20

    Additional columns which are not necessarily required in the output table. The first of these additional columns on Line 17 is to support an input parameter, while Line 20 is a dummy column used for paging the data from the API:

    <!-- Add the other columns here -->
    
    <!-- Dummy column for where clause --> <input name="fromdate" xs:type="datetime" />
    <!-- Paging meta column --> <input name="Rows@Next" desc="Identifier for the next page of results"/>
  • Line 24 to Line 26

    The URI to access the data via (the name of a local file can also be used). In this example, the URI has an input parameter for from date and a fixed to date:

    <!-- Set the RESTlet URI -->
    <api:set attr="uri" value="https://api.dummyexample.com/fromdate=[_input.fromdate]&todate=2020-01-01" />
    <api:set attr="uri" value="testdata.json" />
    

    Please Note

    A test file can be created by adding a new table into the API Profile by clicking on the + on the Configure API Profiles windows. This sample JSON code can then be copied into the dialog box provided.

  • Line 28 and 30

    The Repeat Elements and Sub Repeat Elements define the granularity of the data to be accessed as well as the path of the data. A Sub Repeat Element is required if some data is at a lower level than other required data. For example, the data may contain details of customers and each customer may have more than one address. The Sub Repeat element would be defined at the address level to pull through all addresses:

    <!-- Set the Repeat Element -->
    <api:set  attr="RepeatElement"    value="/feed/records" />
    <api:set  attr="SubRepeatElement" value="/feed/records/addresses"/>
    

    Please Note

    Only one sub repeat element is allowed per RSD file. In the example above, if there was also a credit card record for customers and each customer could have more than one credit card then multiple passes will be required.

  • Line 32 and 33

    The content-type header telling Matillion that the API is a JSON API:

    <!-- Set the 'content-type' header -->
    <api:set  attr="ContentType"  value="application/json" />
    
  • Line 35 to 37

    The HTTP request Headers. In this example the Authorisation is being passed through the header with a connection parameter called auth. Other Header values can be set in here if required. For further details on Authenticating API profiles please see here:

    <!-- Set the 'Authorization' header -->
    <api:set  attr="Header:Name#"   value="Authorization" />
    <api:set  attr="Header:Value#"  value="[_connection.auth]" />
    

Example RSD file – Line 36 to 74

  • Line 39

    Description of the REST Operation—in this case an HTTP GET request:

    <api:script method="GET" >
    
  • Line 41 to 58

    Dealing with paging, an HTTP POST request is made to request the next rows of data:

    <!-- Request an HTTP POST -->
    <api:set attr=method value="POST"/>
    
    <!-- Set the JSON POST body to the required ranges --> <api:check attr="Rows@Next"> <api:set item="userns" attr="startpoint" value="[_input.Rows@Next]" /> <api:set item="userns" attr="endpoint" value="[userns.startpoint | add(5000)]" /> <api:else> <api:set item="userns" attr="startpoint" value="0" /> <api:set item="userns" attr="endpoint" value="5000" /> </api:else> </api:check>
    <api:set attr="data">{ "min":[userns.startpoint], "max":[userns.endpoint], "searchID":"customsearch_ppj_generic_transaction_ss" }</api:set>
  • Line 60

    It is also vital that API paging is enabled by adding the following to the <rsb:script> section of the RSD file:

    <api:set    attr="EnablePaging" value="TRUE" />
    
  • Line 62

    call the API:

    <api:call   op="jsonproviderGet">
    
  • Line 64 to 68

    Get the next rows of data:

    <!-- Increment the total row counter -->
    <api:set item="userns" attr="startpoint" value="[userns.startpoint | add(1)]"/>
    
    <!-- Set Rows@Next to invoke paging --> <api:set attr="Rows@Next" value="[userns.startpoint]" />

Other Notes

Items in an array can be referenced in one of two ways:

  1. Reference individual items by number, for example:

    <attr name="tag1" xs:type="string" other:xPath="tags[0]" />
    <attr name="tag2" xs:type="string" other:xPath="tags[1]" />
    <attr name="tag3" xs:type="string" other:xPath="tags[2]" />
    
  2. Return the array as a comma-separated string using the other:valueformat="aggregate" attribute (this can then be separated and pivoted using a transformation job):

    <attr name="tags" xs:type="string" other:xPath="tags" other:valueformat="aggregate" columnsize="20000" />
    

Attributes

Possible attribute (<attr tag) parameters:

  • name: The alphanumeric string that defines the name of the column.
  • xs:type: The data type of the column. The string, int, double, datetime, and boolean types are supported.
  • other: Attributes prefixed with 'other:' that provide extra information. These other properties can be operation specific. For example, other:xPath specifies the XPath to a node in a local or remote resource. The XPath can be relative to a RepeatElement.
  • desc[ription]: A short description of the column.
  • key: Whether the column is part of the primary key in the table.
  • readonly: Whether the column can be updated. Allowed values are true and false.
  • req[uired]: Whether the column must be specified in an insert. Allowed values are true and false.
  • def[ault]: The default value for the column if none is specified.
  • values: A comma-separated list of the valid values for the column. If specified, the engine will throw an error if the specified column value does not match one of the allowed values.
  • reference[s]: The foreign key to the primary key of another table. The foreign key is specified with the following syntax: table.key. For example: "Employees.EmployeeId".
  • columnsize: The maximum character length of a string or the precision of a numeric column. The precision of a numeric column is the number of digits.
  • scale | decimaldigits: The scale of a decimal column. The scale is the number of digits to the right of the decimal point.
  • isnullable: Indicates whether the column accepts null values. Note that this does not prevent sending or receiving a null value.

Inputs

Possible Input (<input tag) parameters:

  • name: The name of the input. An alphanumeric string that may additionally contain the following: "#" denotes that the input can have multiple values, "myprefix:*" denotes a set of inputs with the same prefix, and a value of "*" denotes arbitrary input parameters.
  • desc[ription]: A short description of the input.
  • xs:type: The data type of the input. The string, int, double, datetime, and boolean types are supported.
  • def[ault]: The default value to be used when no input value is supplied in the script call.
  • key: Whether the input is a primary key.
  • req[uired]: Whether the input is required. The engine will throw an error if the required input is not supplied and there is no default value defined. Allowed values are true and false.
  • values: A comma-separated list of the allowed values for the input. If specified, the engine will throw an error if the specified input does not match one of the allowed values.
  • other: Attributes prefixed with "other:" that provide extra information.
  • alias: The alias of the input.

Outputs

Possible Output (<output tag)  parameters:

  • name: The name of the output. "myprefix:*" denotes a set of outputs with the same prefix, and a value of "*" denotes arbitrary output parameters.
  • xs:type: The data type of the output. The string, int, double, datetime, and boolean types are supported.
  • desc[ription]: A short description of the output.
  • columnsize: The maximum character length of a string or the precision of a numeric output. The precision is the number of digits.
  • other: Attributes prefixed with 'other:' that provide extra information about the output. For example, other:xPath specifies the XPath to a node in a local or remote resource. The XPath can be relative to a RepeatElement.