Got REST, but not JSON? Progress Autonomous REST Connector works with XML and CSV REST services too!
Introduction
For several years now, the Progress Autonomous REST Connector has provided ODBC and JDBC SQL access to REST sources that return JSON data. These are the most common types of REST services, but what can you do if the REST service you want to access returns data in a different format?
Well, Progress has you covered here too. Recently the Progress Autonomous REST Connector has added support for REST services that return responses with XML and CSV data formats. And, a few months earlier, we had added the ARC Composer, a graphical user interface to make it even easier to configure new ARC connectors. To demonstrate using the ARC Composer with an XML REST service, I chose the United States Geological Survey (USGS) REST service described here:
https://earthquake.usgs.gov/fdsnws/event/1/
I used the ARC configuration manager to sample the XML data from this endpoint:
I could also test this API with a CSV format by modifying the request URL:
The USGS API supports a number of query parameters to filter the output. I’m using starttime, endtime, and minmagnitude. I also used format, limit, and orderby to configure the REST service output. Otherwise, this is a fairly simple API. There is no authentication or pagination setup required.
I’ve sampled that REST endpoint using the ARC Composer.
The ARC Composer recognizes these query parameters as potential filter columns. If it receives SQL using these columns in Where clauses, it will push them down to the REST service in the request.
The ARC Composer creates an Entity/Relationship Diagram. With complex REST responses, ARC will normalize the data into separate virtual tables. In this case, the REST endpoint maps to two separate tables and it displays the columns that can be used as filters to the REST service.
I can use the ARC Composer to connect to the REST source and run some test SQL queries. In this case I’m looking for seismic events with a minimum magnitude of 5 that occurred between '2022-06-01' and '2022-07-01'. ARC will push those 3 filters down to the REST API.
SELECT * from EVENT where EVENTS_STARTTIME = '2022-06-01' and EVENTS_ENDTIME = '2022-07-01' and EVENTS_MINMAGNITUDE = 5
When you’re first getting started it’s useful to set up the ARC Debug Record tracing. This will show you the incoming SQL, the REST request, the response time of the REST service, the HTTP status code, and the REST response. You can see in this trace that the REST response is XML.
After downloading the REST config file (or “model”) generated by the ARC Composer, you can add it the JDBC URL or point to it in an ODBC datasource.
Here is the ARC USGS model file created by the ARC Composer:
{
"Events": {
"#path": [
"https://earthquake.usgs.gov/fdsnws/event/1/query /q:quakeml/
eventParameters"
],
"@publicID": "VarChar(229)",
"event[]": {
"@catalog:datasource": "VarChar(3)",
"@catalog:eventsource": "VarChar(3)",
"@catalog:eventid": "VarChar(12)",
"@publicID": "VarChar(123)",
"description": {
"type": "VarChar(22)",
"text": "VarChar(73)"
},
"origin": {
"@catalog:datasource": "VarChar(3)",
"@catalog:dataid": "VarChar(15)",
"@catalog:eventsource": "VarChar(3)",
"@catalog:eventid": "VarChar(12)",
"@publicID": "VarChar(123)",
"time": {
"value": "Timestamp(9)"
},
"longitude": {
"value": "Double"
},
"latitude": {
"value": "Double"
},
"depth": {
"value": "Integer",
"uncertainty": "Integer"
},
"originUncertainty": {
"horizontalUncertainty": "Integer",
"preferredDescription": "VarChar(33)"
},
"quality": {
"usedPhaseCount": "Integer",
"standardError": "Double",
"azimuthalGap": "Double",
"minimumDistance": "Double",
"usedStationCount": "Integer"
},
"evaluationMode": "VarChar(9)",
"creationInfo": {
"agencyID": "VarChar(3)",
"creationTime": "Timestamp(9)",
"version": "VarChar(64)"
}
},
"magnitude": {
"@catalog:datasource": "VarChar(3)",
"@catalog:dataid": "VarChar(15)",
"@catalog:eventsource": "VarChar(3)",
"@catalog:eventid": "VarChar(12)",
"@publicID": "VarChar(138)",
"mag": {
"value": "Double",
"uncertainty": "Double"
},
"type": "VarChar(7)",
"stationCount": "Integer",
"originID": "VarChar(123)",
"evaluationMode": "VarChar(9)",
"creationInfo": {
"agencyID": "VarChar(3)",
"creationTime": "Timestamp(9)"
}
},
"preferredOriginID": "VarChar(123)",
"preferredMagnitudeID": "VarChar(138)",
"type": "VarChar(25)",
"creationInfo": {
"agencyID": "VarChar(3)",
"creationTime": "Timestamp(9)",
"version": "VarChar(64)"
}
},
"creationInfo": {
"creationTime": "Timestamp(3),#key"
},
"minmagnitude": {
"#type": "Integer,#key",
"#virtual": true,
"#eq": "minmagnitude",
"#default": 3
},
"format": {
"#type": "VarChar(64),#key",
"#virtual": true,
"#eq": "format",
"#default": "xml"
},
"limit": {
"#type": "Integer,#key",
"#virtual": true,
"#eq": "limit",
"#default": "2000"
},
"endtime": {
"#type": "Date,#key",
"#virtual": true,
"#eq": "endtime",
"#default": "2022-08-01"
},
"orderby": {
"#type": "VarChar(64),#key",
"#virtual": true,
"#eq": "orderby",
"#default": "magnitude"
},
"starttime": {
"#type": "Date,#key",
"#virtual": true,
"#eq": "starttime",
"#default": "2022-01-01"
}
}
}
Now that I have a working ARC driver for the United States Geological Survey REST service, I can use it with any ODBC or JDBC SQL tool. In this case, I’ve used it to build a Tableau workbook mapping seismic events over the world by date, magnitude, and seismic event type.
To summarize, REST as a standard sometimes seems like the Wild West. No two REST APIs are set up exactly the same and it requires custom work to consume each one. Progress Autonomous REST Connector simplifies this by providing standardized SQL access to the “normal” JSON REST services and now, even to the “wilder” XML and CSV REST services.
