Getting started with the API driver in Matillion ETL

Overview

The API Query component 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 article will help you get started with a new API driver.

Setting up

To start developing with the API driver, you will need the following:

The help file with documentation of the driver — attached to this article.
A link to the trial driver — https://www.cdata.com/apidriver/download/
A copy of SQuirreL SQL — http://squirrel-sql.sourceforge.net/

Also attached to this article is a set of RSD files. These RSD files were developed for an integration to Zendesk. These files contain most of the necessary techniques to work with an API in the real world. For more information, see the Zendesk API documentation.

The basic workflow is as follows:

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

2. Run simple queries (SELECT, WHERE, ORDER BY, etc.) in SQuirreL to test.

3. When you are confident things are working, create a profile directory here:
/usr/share/tomcat/api_profiles/<profile name>/
and upload the RSD files into this directory.

4. Restart the Matillion ETL server.

5. Configure the API Query component. You should find a profile listed that matches the profile you created, and a data source for each of the RSD files you created.

Setting up SQuirreL SQL

1. Download and install SQuirrel SQL from http://squirrel-sql.sourceforge.net/.

2. Run SQuirreL and in the Change Driver overlay, add the driver files downloaded from https://www.cdata.com/apidriver/download/.

Adding drivers to SQuirreL

3. You can then switch to the SQL tab and start querying the data.