Creating API Profiles Support
  • Dark
    Light

Creating API Profiles Support

  • Dark
    Light

Overview

This document looks at an example API query 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:

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 eventuality has 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 target="blank">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

    API paging can be enabled and managed at Response Configuration window of Manage Query profile interface of Generating API Query Profile process. For detail description on Manage Query Profile interface setup, please visit Manage Query Profiles document.

    Paging Enable

    Paging Enable

    If paging feature is enabled, you can see the line below will be added in the RSD generated 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" />
    
  3. When defining API Profiles (RSDs), sometimes an API response might contain a special character that causes for the field to not be parsed correctly. An example of this is : If the attribute has a period in the name. An auto-generated RSD from the Query Profile Wizard might generate an RSD that contains this definition. As shown in the example below, the attribute is "rating.2" and the value for this attribute will not be parsed correctly when defined as this:

    <attr name="rating2" xs:type="integer" readonly="false"  other:xPath="/json/reviews/rating.2"/>

    The workaround is to use square brackets around the attribute name. This definition will parse the value correctly:

    <attr name="rating2" xs:type="integer" readonly="false"  other:xPath="/json/reviews/[rating.2]" />

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.