Easily Integrate with Any RESTful API Using the CData API Driver

RESTful APIs offer a consistent, straightforward way for businesses to work with external data and offer access to their own data. In June 2019, the number of public APIs grew beyond 22,000, with a growth rate of more than 2,000 APIs per year since 2015. APIs continue to provide value to organizations and developers, and with the CData API Driver, it is easier than ever to integrate with APIs.

Why Use the API Driver?

The CData API Driver is built with the same goal as all the other CData Drivers: to ease the process of integrating your data. With the API Driver and the available API Profiles, you can instantly and codelessly query dozens of APIs (including any APIs built using the CData API Server) from BI, data integration, and custom applications. Thanks to its extensible design, organizations and developers can modify API Profiles from API Server to customize integrations and even create their own API Profiles. Easily expand the connectivity offered by API Driver to any RESTful API.

This article walks through configuring the API Driver to connect to the StackExchange API using the existing API Profile and querying StackExchange once connected.

Configuring the API Driver

The API Driver is like any other enterprise database driver and uses connection properties to establish a connection to data. The API Driver has two key connection properties used to connect to an API:

• Profile: The .apip file on disk for the profile you would like to connect
• ProfileSettings: A semi-colon separated list of name-value pairs required by your chosen profile

Configuring the connection is dependent upon the flavor of connector you are using (JDBC, ODBC, ADO.NET, etc.). Sample connections (using the StackExchange Profile) are shown below:

JDBC Connection String

jdbc:apis:Profile=/PATH/TO/StackExchange.apip;ProfileSettings='Site=dba;APIKey=abcdafeoefiwojfweoewf';

ADO.NET Connection String

Profile=/PATH/TO/StackExchange.apip;ProfileSettings='Site=dba;APIKey=abcdafeoefiwojfweoewf';

ODBC DSN Configuration

With a connection to an API configured, we are ready to start working with the API using SQL queries.

Working with APIs Using SQL Queries

The CData API Driver grants you SQL access to API data. For this article, we will explore the data using DbVisualizer and the JDBC API Driver, but the methods and queries shown can be used with any of our API Driver technologies.

Connecting

Use a connection string like those described above to connect to a specific API Profile.

Explore Metadata

One thing of note is you do not need a live connection to explore the metadata for a given API Profile. You can look over the available tables (read/write API endpoints), views (read-only API endpoints), and stored procedures (API functions that do not have a direct table/view corollary) for an API Profile without having authentication credentials.

Explore the metadata by expanding the table/view/procedure structure in a database management tool (like DbVisualizer) or by querying the metadata directly using SQL.

Metadata in DbVisualizer

Metadata through Direct Queries

Querying APIs with SQL

Once you have established your connection, you can begin working with your API using SQL, including read and write functionality (as supported by the API).

Reading data from an API is as simple as constructing a SELECT query based on the table/view definition exposed by the driver. For example, we can use the following SQL query to request all of the unanswered questions created after December 2, 2019 that have the "python" tag:

SELECT 
  AnswerCount, 
  CreationDate, 
  Title, 
  Tags, 
  ViewCount 
FROM 
  Questions 
WHERE 
  CreationDate > '12/2/2019 23:59:59' AND 
  AnswerCount < 1 AND 
  Tags LIKE '%python%';

The results of the query (in DbVisualizer) follow:

The API Driver can be used in any tool or application that supports standard database connectivity. With the API Driver, you get instant access to APIs from the BI, reporting, ETL, and custom applications you already use to drive business.

More Information & Free Trial

At this point, you know how to connect to and query an API through the API Driver. To learn more, visit the API Driver page or read about editing or creating API Profiles in our corresponding Knowledge Base articles (Editing API Driver Profiles and Creating API Driver Profiles). Download the API Driver and start working with live data from RESTful APIs instantly and codelessly.