Matillion

Welcome, Guest Login

Support Center

Creating API Profiles Support

Last Updated: Jan 21, 2019 01:59PM UTC

Creating API Profiles Support


Important (possible breaking change): API Profiles ("RSD’s") that handle paging may need to be tweaked to disable “auto” paging. Please see  here for more details.

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

We have a series of articles which cover many different aspects of writing API profiles:

For information on getting started with writing API profiles and to understand the basics of rsd files please see the article here.

Breakdown of an example RSD file:

Lines 5 to 12 give details of the location of the data in the JSON file which are to provide the output columns in the table which is 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. Available datatypes are: integer, string, datetime, double and boolean. Further details on this is available on the Writing API Profiles page. A brief summary of the parameters used in the Attributes, Inputs and Outputs tags are given in the sections at the bottom of this page.

   <!-- 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" />  

Lines 14 to 20 are 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. Further details of parameterisation is available here. Line 20 is a dummy column used for paging the data from the API. Further details on paging is available here:

   <!--     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"/>

 </rsb:info>

Line 25 is the URI to access the data via. In this example the URI has an input parameter for from date and a fixed to date:

 <!-- Set the RESTlet URI -->

 <rsb:set attr="uri"  value="https://api.dummyexample.com/fromdate=[_input.fromdate]&amptodate=2020-01-01" />

Tip in here you can insert the name of a local file to test with for example:

 <rsb:set attr="uri"  value="testdata.json" />

The file testdata.json can be created by clicking on the plus to add a new table into the API profile and naming it testdata.json. The sample JSON file can simply be copied in here.

Lines 28 and 29 are the Repeat Elements and Sub Repeat Elements. This defines the granularity of the data to be accessed as well as the path of the data. Only one Repeat Element and one Sub Repeat Element is allowed per RSD file. 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 -->
 <rsb:set  attr="RepeatElement"        value="/feed/records" />
 <rsb:set attr="SubRepeatElement" value="/feed/records/addresses"/>  

Please note that 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 is the content-type header telling Matillion that the API is a JSON API:

 <!-- Set the 'content-type' header -->
 <rsb:set attr="ContentType" value="application/json" />

Lines 35 and 36 are 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 -->
 <rsb:set  attr="Header:Name#"   value="Authorization" />
 <rsb:set  attr="Header:Value#"  value="[_connection.auth]" />

Lines 38 is describing the REST Operation - in this case an HTTP GET request:

 <rsb:script method="GET" >

Lines 40 to 58 are for the paging. Here an HTTP post request is made to request the next rows of data. Further details on paging is available here

   <!-- Request an HTTP POST -->

<rsb:set attr=method value="POST"/>      
<!-- Set the JSON POST body to the required ranges -->    
<rsb:check attr="Rows@Next">      
<rsb:set item="userns" attr="startpoint" value="[_input.Rows@Next]"/>     
<rsb:set item="userns" attr="endpoint" value="[userns.startpoint | add(5000)]"/>
<rsb:else>      
<rsb:set item="userns" attr="startpoint" value="0"/>      
<rsb:set item="userns" attr="endpoint" value="5000"/>   
</rsb:else>      
<rsb:set item="userns" attr="startpoint" value="0"/>     
<rsb:set item="userns" attr="endpoint" value="5000"/>   

</rsb:else> t],     "max":[userns.endpoint],     "searchID":"customsearch_ppj_generic_transaction_ss" }</rsb:set>

 

It is also vital that you enable API paging by adding the following to the <rsb:script> section of your rsd.

<rsb:set attr="EnablePaging" value="TRUE" />


Line 59 is calling the API:

   <rsb:call op="jsonproviderGet">

Finally Lines 61 to 65 are getting the next rows of data:

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

</rsb:script>

This example RSD file shows what could be included in an RSD file and hopefully gives an idea of what is possible. Obviously all API endpoints differ and we can’t cover all eventualities. For further help please contact our support team.

 

Other notes:

Adding an ‘&’ in the URI requires you to put &amp

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

  1. Reference individual items by number e.g.
    <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:
    <attr name="tags" xs:type="string" other:xPath="tags" other:valueformat="aggregate" columnsize="20000" />
    
    	This comma separated string can then be separated and pivoted using a transformation job.
    
 

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.

Contact Us

97b446f1c457c4801f4b2f9f7bae4eb1@matillion.desk-mail.com
https://cdn.desk.com/
false
desk
Loading
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
about
false
Invalid characters found
/customer/en/portal/articles/autocomplete