Getting started with the API Driver in Matillion ETL
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.
In order to get started developing with the API driver you will need the following.
- The help file with documentation of the driver - attached to this document
- A link to the trial driver - http://www.cdata.com/apidriver/download.aspx.
- A copy of squirrelsql - http://squirrel-sql.sourceforge.net/
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.
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.