Load and refresh JSON dataset from REST API using desktop tool

Question

Currently, I query WebTrends API to download data into Excel. However, now I am searching for a more reliable method/tool for getting hold of the data in JSON/XML format, then convert it to CSV in order to use it in a variety of data stores and analysis tools.

It would be a bonus if the tool can automate an update to the feed on regular intervals by appending new data to existing datasets.

Oh it needs to be Free/Opensource.

My research led me to ETL tools like Talend, JaspersoftETL and CloverETL...etc. But I am struggling to determine if they can do the job and which one is easier! Does anyone know how to achieve the above job in an 1-free 2-easy 3-lightweight manner?

WebTrends API URL looks like this: http://ws.webtrends.com/v2_1/ReportService/profiles/dnGpm8jbgl7/reports/1RxjilSo4n7/?totals=none&period=2017m02d27*26&format=json&suppress_error_codes=true

Thanks,

Answer 1

If you can use Ruby, then Kiba ETL is a lightweight free OSS solution which you can use for that type of scenarios.

Typically the code will look like:

source YourJSONHTTPDownloadSource, urls: [url_1, url_2]

transform do |row|
  # remap the fields as needed
  row
end

destination YourCSVDestination, filename: my_filename  

And you would run it using the Kiba command line.

You will have to write YourJSONHTTPDownloadSource (a Kiba source to fetch the data from the url and yield each JSON/XML element) and YourCSVDestination (for more details on that, check out the readme).

PS: the URL your provided requires authentication, which means we cannot see anything.