Getting started with the API Driver in Matillion ETL

Getting started with the API Driver in Matillion ETL


Overview

API Query Component in Matillion ETL allows you to integrate with almost any JSON or XML based API. The component converts the API into a pseudo SQL dialect that can then be queried in a similar way to any normal database. This data can then be copied to a table by the component.

This is a achieved by creating a series of RSD files that act as the definition for the API. This document will help you get started with the new driver.
 

Setting up

In order to get started developing with the API driver you will need the following.

Also attached are a set of RSD files that were developed for an integration to Zendesk. Their API documentation is here. These contain most of the necessary techniques to work with an API in the real world. The basic workflow is as follows:

1. Build the RSD files in a text editor and save.

2. Run simple queries (select *, where and order by etc) in squirrel to test.

3. When you are confident things are working upload the RSD files into the directory:

/usr/share/tomcat8/api_profiles/<profile name>/ (create a profile folder)

4. Restart the Matillion ETL Server

5. Configure the API Query component, your profile should match the profile you created and should be listed. You should also have a data source for each of the RSD's you create.
 

Setting up SquirrelSQL

After you have installed the files


Download and run squirrel and in the "Drivers" section add the driver files like below



If you switch to the sql tab you can start querying the data.