What is an API?

API (Application Programming Interface), is a software communication protocol that allows two applications to talk to each other. By exchanging data, one application would be able to access certain information from the other so certain tasks can be performed. When we pull our phone for weather data, a lot of data objects would hit your weather app from a server that stores all the weather information.

Most modern applications use APIs to connect with each other and exchange data. What if you want to pull data from an API endpoint? That means that you’ll need an app that would understand the API and the data behind it.

API data formats

There are mainly 3 types of data that and API returns. They’re JSON objects, CSV, and XML. Each format has its own particular strengths and weaknesses.

JSON objects

JSON is the most popular format for API calls. It consists of key-value pairs and arrays that look like the example below.

{

"crust": "original",

"toppings": ["cheese","pepperoni", "garlic"],

"status": "cooking"

}

One core advantage of using JSON is speed. Since it is compact and more parsable, it runs faster than XML, another popular data interchange standard prior to JSON. It’s also very scalable and supports a larger number of data objects sent from the server. Though many would argue that it’s not as secure as XML or CSV format.

CSV

CSV is a data storage format that stores data values (plain text) in a list format separated by commas. It’s actually more compact and even faster than JSON, but it’s rarely used in a web development environment since it lacks a hierarchical structure. For what it lacks in scalability, it is more accessible due to its tabular format. A spreadsheet or relational database can easily access a CSV file while providing a variety of functional features in analytics and data manipulations.

XML

XML, as previously mentioned, was the data exchange format for API prior to JSON. It’s a markup language that’s both human and machine readable. Though it’s not compact and optimized for read speed. It’s considered verbose and redundant when compared to JSON. Though XML is more suited for combining information sets from different systems such as metadata.

How to pull data from an API?

Solution 1: Pull API data manually on a browser

The most straightforward way of accessing data from an API endpoint is simply viewing it in the browser. As long as you’re connected to the internet, you should be able to preview most API’s data regardless of its format.

Updating the data however requires you manually refreshing the URL address and requesting new data points from the API endpoint. This may work well if you only want to download a few responses. When your API updates more frequently, viewing it on a web browser will not be effective.

Solution 2: Pull API data via Python

Python can be a powerful way to collect data from an API. Though it’s a programming language, it comes with many useful packages that support not only data collection but also data manipulation and storage.

To get started, you’ll need to install Python. Then you can import the required libraries such as “requests” and “JSON”. In the example below, you can print out the status of the API before pulling the data.

After reading the JSON objects, you may now normalize them into a relational table for further analysis.

Solution 3: Save API data automatically into a database

Another way of pulling data from an API is through a cloud database management software, such as Acho. Here you can use a pre-build API connector to systematically request an API endpoint and store the data in a database for future applications.

The main advantage of using this method is that you can pull and store the data automatically. The setup can be configured upfront, then there are minimum maintenance efforts needed afterward.

Now, we will use Acho as an example to demonstrate how to connect to your API with no coding.

1. Configure the API endpoint

An API endpoint can be complex. It requires a good understanding of its technical design to parse it into a system effectively. For example, your API looks like this

You can directly paste the URL endpoint into the URL input box. Alternatively, you can enter the parameters respectively. Then, you can click “Test” to see if the request is successful. If all the configurations are correct, you should see the results as shown below.

If you didn’t see any result from your API or a system warning shows up, you should check your API documentation again and make sure all configurations are filled out. Here are some common issues that APIs are failed to be connected:

• Your API requires a specific authorization, such as Basic Auth or Bearer Token.➜ Specify it in the “Authorization” tab
  
• Your API need to fill out something in headers. ➜ Specify it in the “Headers” tab
  
• The result of API is a downloadable CSV file. ➜ Go to the “Data Format” tab and select “CSV”
  
• Your API contains curl brackets, {}.➜ The value within the brackets are dynamic. You have to give a list of values to the system so that it can change the value inside the brackets. We will talk about it later in this article.

2. Create an API resource

After you confirm the API results, click “Create Resource” to build the API connection. The API resource will show up on your resource page.

If you want to make the API connector update automatically, you can go to the “Data Sync” tab in the setting and turn on the scheduler. The update mode allows you to determine how to update the table: append new data to the existing data (Incremental) or replace the existing data with new data (Full refresh).

3. Store data into a database

After data is pulled from the API and the initial sync is complete, it will be stored in a tabular database that will sync with the resource. When the API updates, the resource should update its content and your project table should reflect that update as well.

4. Transform the API data

Within the database, you can freely transform it into your desirable table format. Generally, API data is stored in a nested data format, such as JSON objects or arrays. You can use the “Flatten” action to flatten the data into a tabular format. Also, there’re a number of built-in SQL-based actions you can apply to this data pulled from the API.

5. Export the data to an application

Once your table is built, you can use it to power an application such as a dashboard, spreadsheet, or an embedded table on your website.

Click the “Export in Workflow” button and then it will bring you to Workflow. In Workflow, you can send the API data to any desired destinations.

6. Check and maintain the pipeline

After the connection has been made between your Acho data project and your destination application, you should check and maintain the pipeline to ensure that data is transferred timely with no delays or breakages.

How to send multiple requests at a time?

In the previous example, we only retrieve one ticker’s stock price. However, if you want to get more than one ticker’s data and allow you to retrieve one ticker’s data for each API call, you can use the “for loop” method in the “Multiple Requests” tab of the API connector.

First, build an API resource and get the list of values you want to loop. If your API provider doesn’t provide an API to get the list, you can upload a CSV file containing all the values you want to loop instead. In this case, we use the API connector to get all Nasdaq stock tickers and import the resource to a project.

Then, create another API resource. Fill {{ticker}} in the value of the “tickers” key. It tells the system that this value is dynamic.

Go to the "Multiple Requests" tab and specify the path to the list of tickers. You can choose a column in a project table and the system will loop the values in the column to replace the value in the curl bracket.

Now, you can see all Nasdaq stock prices.

How to authenticate your API connection?

Another powerful feature with Acho's API connector is the ability to authenticate your API connection in 3 different ways: Basic Auth, Bearer Token, and OAuth Client.

For Basic Auth, all you need to fill in is the set of credentials that contains the user name and password. For Bearer Token, it's simply a unique string of characters that you should receive from the API provider. OAuth is more advanced - you can basically configure your own OAuth client's credentials in a form.

How to clean data from an API?

If your data are still displayed in a JSON format or an array, you can follow the steps below to flatten your API data.

As you can see in the above image, the data we need is in the “result_data” columns and all values are stored in a single cell. Below are the full texts in the cell.

{
  "AAPL":[
     {
        "date":"2022-07-21",
        "volume":62986150,
        "high":155.57,
        "low":151.94,
        "adj_close":155.35,
        "close":155.35,
        "open":154.5
     },
     {
        "date":"2022-07-20",
        "volume":63805728,
        "high":153.72,
        "low":150.37,
        "adj_close":153.04,
        "close":153.04,
        "open":151.12
     }
  ]
}

The curl brackets, {}, represent a JSON object that contains key-value pairs in a format of {key:value}. The square brackets refer to an array of values.

{"AAPL": [JSON1, JSON2]}

This nested data structure is easy to save or retrieve data but not easy to view or analyze data. Thus, we want to flatten the nested data into a tabular format by applying the Flatten action. In this case, we should flatten the data three times.

First, click the Flatten action and choose “Flatten JSON”. Then, choose the “result_data” column and tell the system to retrieve data from the “AAPL” key.

You’ll see your data become several rows after the array is flattened. The number of rows depends on the number of values in the array.

Since data is still displayed in a JSON format, we should apply the flatten action again.

Here is the result:

Now your API data should be parsed and stored in a database now, you can perform analytics or use it to support an application. If there's a certain application that you need help setting up, feel free to contact us through the chatbox on the bottom right or by email.

How to turn APIs into an Application?

Once the data is cleaned and prepared. The data can now be productized into an interactive application for users. In the App Builder, simply drag the API project from the left panel into the canvas so you can now query against the API project table.

The next step is query the data and save it into a "data node". Once the data is "materialized" into a data node, you can now use it to build any interactive elements.

Now, you can pick an element such as a chart, or table to turn your API data node into a user-facing application!

Follow us on Twitter @team_acho for the latest product news.

Email us contact@acho.io for any questions you may have.