Home Partners Company
How to Build a Custom ODBC Driver for REST Data Sources Using DataDirect OpenAccess SDK

How to Build a Custom ODBC Driver for REST Data Sources Using DataDirect OpenAccess SDK

August 06, 2015 0 Comments

A hot trend we’re seeing is out-of-box connectivity to REST APIs to Business Intelligence and Data Warehousing infrastructure.  For example, there’s a thread on SAP’s community site to connect Lumira Data Discovery and Visualization tool to SuccessFactors RESTful API.

The developer evangelism team at DataDirect always get really excited about any challenge to expose data through industry standards (see Python SaaS tutorial).  This time, the goal was to develop a custom connector for REST data sources using DataDirect® OpenAccess SDK®. OpenAccess SDK opens up a world of possibilities as a platform for quickly developing custom connectors for any REST API. In this case, Saikrishna Teja Bobba – Developer Evangelist, developed a connector that would expose data from Meetup.com with Microsoft’s SQL Server Integration Services (SSIS) and Tableau for data analytics.

Building a Custom ODBC Connector

It’s easy to build an ODBC driver for any REST Service using OpenAccess SDK. To start, download and install OpenAccess SDK from the Progress driver download form (choose “custom connectivity” from the data category drop-down). Once complete, the client and server can be deployed as a single tier for ease of distribution. Documentation is available to help you through the installation process.

  1. The first step after installing OpenAccess SDK is to download the Once downloaded, extract the jar file using command jar xvf oa_rest_ip_generator_8_0_0.jar, which generates a folder named ‘Rest Generator’. Copy the folder to the location installdir\Progress\DataDirect\oaserver80\

Contents of the ‘Rest Generator’ folder

 

Contents of the ‘Rest Generator’ folder

  1. Define the schemas that you intend to create for the REST service and save them as XML files. The following is one of the schemas that I have built for Meetup.com. For your reference, the below schema is built for the OpenEvents API model. Refer to the OpenAccess documentation for descriptions of different data types and the codes that should be used in their respective schema files.
<?xml version="1.0" encoding="UTF-8"?>
<tables>
     <table name="Meetup">
          <column name="id" dataType="-9" userData=""/>
          <column name="name" dataType="-9" userData=""/>
           <column name="headcount" dataType="4" userData=""/>
           <column name="distance" dataType="-9" userData=""/>
           <column name="visibility"  dataType="-9" userData=""/>
           <column name="waitlist_count"  dataType="4" userData=""/>
           <column name="description" dataType="-9" />
           <column name="event_url" dataType="-9" userData=""/>
           <column name="maybe_rsvp_count" dataType="4" userData=""/>
           <column name="yes_rsvp_count" dataType="4" userData=""/>
           <column name="status" dataType="-9" userData=""/>
           <column name="zip" dataType="4" userData=""/>
           <column name="country" dataType="-9" userData=""/>
           <column name="city" dataType="-9" userData=""/>
           <column name="duration" dataType="-9" userData=""/>
           <column name="how_to_find_us" dataType="-9" userData=""/>
           <column name="created" dataType="11" userData=""/>
           <column name="time" dataType="11" userData=""/>
           <column name="updated" dataType="11" userData=""/>
           <column name="state" dataType="-9" userData=""/>
           <stat nonUnique="0" indexName="IDXMEETUPID" indexType="1" seqInIndex="1" columnName="id" cardinality="-1" pages="-1" filterConditions="" />
           <pkfk pkColumnName="id" keySeq="1" updateRule="-1" deleteRule="-1" pkName="PKMEETUPID" />
     </table>
</tables>
  1. Once you have created schemas, edit the input.props file to include database name, catalog name, schema files location and PATHTOWRITE (location where template IP code will be generated) and base_URL that is used to connect to REST data source. Following is input.props that I created for Meetup.com:
DATABASE_NAME=Meetup
CATALOG_NAME=MUCATALOG
SCHEMA_NAME=Meetup
DATABASE_VERSION=1.0
SCHEMA_LOCATION=schema
SCHEMA_TYPE=XML
PATHTOWRITE=Meetup/src
BASEPACKAGE=com.ddtek
DATASOURCE=Meetup
BASE_URL=https://api.meetup.com/2/open_events
  1. With the help of these details you can generate template IP source code for your REST data source by running the command java -jar oarestipgen.jar input.props. This creates your template IP code in the folder PATHTOWRITE that you mention in input.props. You can use eclipse or any other IDE to work on the IP code.
  1. In general, a separate file is created for each table that you have defined in schema and it appears under data processor package. These are the files where you would have to build request URL based on the query you get and parse the JSON response you get from the REST data source. This is a crucial concept in building your connector. Learn more about the files generated on page 25 of the OpenAccess REST IP Generator User’s Guide.

image2

 

Files generated by the schema definition

  • Tip: If you are using eclipse, and if it shows that you are missing libraries, you can find the all the needed libraries in Rest generator/lib folder.
  1. Once you are done with coding follow the steps on page 33 of the User’s Guide to build your connector and create OpenAccess service.
  2. To enable logging for troubleshooting purposes, change the service debug log level to 127 and enable full IP tracing as shown below.
  3. Once the service is started you can find all your logs in C:\Program Files (x86)\Progress\DataDirect\oaserver80\logging. This is very useful for debugging and troubleshooting your ODBC connector.

Enabling logging for troubleshooting

 

Enabling logging for troubleshooting

  1. To use this service on the client side, you need to create an entry in ODBC Administrator. In “userdsn” tab, provide the server address, port number, data source name and service data source. You can test the connection to server using “Test Connection” button in the administrator.
  2. To actually query the REST data source through your ODBC connector, you can use the OpenAccess SDKs Interactive SQL application or consume the data using Tableau or Microsoft SSIS and many other options.

Viewing Meetup.com data in Interactive SQL

 

Viewing Meetup.com data in Interactive SQL

Connectivity with Tableau

  1. Now that your connector is up and running, you can use it to connect your REST data source with analytics software like Tableau. To do this, simply select “Other Databases (ODBC)” from the “Connect” menu. Then select your driver and fill in service host, port and data source as shown in the following image.

Connection settings in Tableau

 

Connection settings in Tableau

  1. The following are some of the visualizations that I was able to create with help of Tableau which used the ODBC connector that I created for Meetup.com using DataDirect OpenAccess SDK.

Visualization of people interested in various technology based Meetups in California

 

Visualization of people interested in various technology based Meetups in California

 

Connectivity with SSIS

  1. It’s also possible to access your data through SQL Server Integration Services (SSIS). To do that, simply create a new SSIS project, create a data flow task, create a new connection and select ODBC. Then, press “Add Connection” and “Add…”

Adding a connection in SSIS

 

Adding a connection in SSIS

  1. In the next window, select “New,” then in the “Use user or system data source name:” drop-down, select the data source that you want to use and press OK.

Configuring connection in SSIS

 

Configuring connection in SSIS

  1. With the help of SSIS, I was able to read the data from Meetup.com Rest Service and write that into SQL Server /Excel files and many more.

Now it’s Your Turn

Now you have everything you need to build your own ODBC, JDBC, ADO.NET and OLEDB data connectors, go ahead and download DataDirect OpenAccess SDK and get started! 

Reach out to us online with any questions you may have, or leave us a message in the comments section below.

Sumit Sakar

Sumit Sarkar

Sumit Sarkar is a Chief Data Evangelist at Progress, with over 10 years experience working in the data connectivity field. The world's leading consultant on open data standards connectivity with cloud data, Sumit's interests include performance tuning of the data access layer for which he has developed a patent pending technology for its analysis; business intelligence and data warehousing for SaaS platforms; and data connectivity for aPaaS environments, with a focus on standards such as ODBC, JDBC, ADO.NET and ODATA. He is an IBM Certified Consultant for IBM Cognos Business Intelligence and TDWI member. He has presented sessions on data connectivity at various conferences including Dreamforce, Oracle OpenWorld, Strata Hadoop, MongoDB World and SAP Analytics and Business Objects Conference, among many others. 

Read next Standards-Based Connectivity vs. Native Coding: What’s Best?
Comments
Comments are disabled in preview mode.