The evolution of APIs has opened up exciting opportunities for businesses. RESTful APIs are a consistent, straightforward way that enables businesses to work with external data and offer access to their own data. In May 2021, the number of public APIs grew beyond 24,000, with about 2,000 APIs added just since 2019.

APIs continue to be beneficial to businesses 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 available API Profiles, you can instantly and codelessly query dozens of APIs from analytics platforms, enterprise databases, and custom applications. Organizations and developers can modify API Profiles from API Server to customize integrations and even create their own API Profiles.

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

Profile Metadata SQL Query

Tables SELECT * FROM SYS_TABLES

Views SELECT * FROM SYS_VIEWS

Stored Procedures SELECT * FROM SYS_PROCEDURES

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

With a number of cloud applications, mobile apps, and IoT devices emerging on the digital horizon, businesses are discovering valuable uses for these data sources.

Connect BI, Reporting, & ETL tools to live data from any application, database, or Web API. Built on the SQL engine that powers other CData Drivers, the CData API Driver enables simple codeless query access to APIs through a single client interface.