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



Then in the "Aliases" section you can connect to your profile like this


Note: the "Location" is the folder containing the RSD files and the Logfile will write all the detail of the connection attempts. The Log file location is important as well. The log file will make it very clear what the driver is actually doing.

Once your done double click the alias you should see your RSD objects and their columns.



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