Query API Data in MySQL Workbench

MySQL Workbench allows users to administer MySQL environments and gain better visibility into databases. When paired with CData Connect (on-premise or Connect Server), you get live access to API data as if it were a MySQL database. This article shows how to create a virtual database for API in Connect and work with live API data in MySQL Workbench.

Create a Virtual MySQL Database for API Data

CData Connect uses a straightforward, point-and-click interface to connect to data sources and generate APIs.

1. Login to Connect and click Connections.
2. Select "API" from Available Data Sources.
3. Enter the necessary authentication properties to connect to API.

   To connect to your API, configure the following properties on the Global Settings page:

   • In Authentication, select the Type and fill in the required properties
   • In Headers, add the required HTTP headers for your API
   • In Pagination, select the Type and fill in the required properties

   After the configuring the global settings, navigate to the Tables to add tables. For each table you wish to add:

   1. Click "+ Add"
   2. Set the Name for the table
   3. Set Request URL to the API endpoint you wish to work with
   4. (Optional) In Parameters, add the required URL Parameters for your API endpoint
   5. (Optional) In Headers, add the required HTTP headers for the API endpoint
   6. In Table Data click " Configure"
   7. Review the response from the API and click "Next"
   8. Select which element to use as the Repeated Elements and which elements to use as Columns and click "Next"
   9. Preview the tabular model of the API response and click "Confirm"
4. Click Save Changes
5. Click Privileges -> Add and add the new user (or an existing user) with the appropriate permissions.

With the virtual database created, you are ready to connect to API from MySQL Workbench.

Query API from MySQL Workbench

The steps below outline connecting to the virtual API database in Connect from MySQL Workbench and issuing basic queries to work with live API data.

Connect to API through Connect

1. In MySQL Workbench, click to add a new MySQL connection.
2. Name the connection (CData Connect).
3. Set the Hostname, Port, and Username parameters to connect to the SQL Gateway.
4. Click Store in Vault to set and store the password.
5. Click Test Connection to ensure the connection is configured properly and click OK.

Query API Data

1. Open the connection you just created (CData Connect).
2. Click File -> New Query Tab.
3. Write a SQL query to retrieve API data, like SELECT * FROM apidb.posts;

With access to live API data from MySQL Workbench, you can easily query and update API, just like you would a MySQL database. Request a demo of the CData Connect and start working with API just like a MySQL database today.