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.
Top comments (0)