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

Status
Not open for further replies.
S

Sumit Sarkar

Guest

Sumit Sarkar,Chief Data Evangelist, Progress DataDirect


Is one of our developer evangelists up to the task of developing a custom connector for REST data sources using DataDirect OpenAccess SDK?

I recently gave one of the developer evangelists on my team, Saikrishna Teja Bobba, a challenge—develop a process for accessing data no matter where it resides using Python and Progress® DataDirect Cloud®. I was really impressed with the result, so I decided to up the ante for him.

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 drivers for any data source or API. In this case, Saikrishna developed a connector that would allow the use of 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 custom ODBC connectors 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 installdirProgressDataDirectoaserver80

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.

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)ProgressDataDirectoaserver80logging. This is very useful for debugging and troubleshooting your ODBC connector.

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

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

  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




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

  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

  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.

Author information

sumit-sarkar-w75.jpg
Sumit Sarkar

Senior Principal Systems Engineer, Progress DataDirect at Progress

Data Access Evangelist, Speaker and World's leading consultant on open data standards for cloud data.

Sumit Sarkar has been working in the data connectivity field for 10 years. His 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 IUC34, IUC35, Progress Revolution, Oracle User's Group, Collaborate 12, Dreamforce 2012, Cloud Computing Expo 2012, PRGS13 Exchange, Dreamforce 2013, Oracle OpenWorld 14, PRGS14, DF14

Presented related webinars in collaboration with IOUG, DZone, Progress DataDirect and Progress Communities.

| Twitter | Google+ | LinkedIn |

The post How to Build a Custom ODBC Connector for REST Data Sources Using DataDirect OpenAccess SDK appeared first on Progress Newsroom.

Continue reading...
 
Status
Not open for further replies.
Top