Skip to main content

​
Summary

This page acts as an example of the steps required to configure a connection to an API from start to finish. This example uses an API that contains people’s maintenance schedules for their vehicles.

​
Create the API Connection

First, create a new API connection.
1
Open the Sources page of the Connect AI navigation menu.
2
Click + Add Connection in the upper-right corner.
3
In the Add Connection dialog that opens, type API to display the API connector tile, then click the tile.
4
On the Global Settings Tab, name the connection. This example uses “TestAPI.”
5
Select the Authentication method. Since no authentication is required for this API, select No Auth.
6
Leave this section blank, because no headers are required for this connection.
7
Set the pagination. This API uses a next page URL, providing the next page URL as part of the response. The following example shows the relevant part of the response from the Vehicles endpoint:
{
  "people": [
    {
        ...
    },
    ...
  ],
  "nextLink": "https://my_vehicles/api?$skiptoken='ERNSH'"
}
8
Set the Pagination Type to URL.
9
Set the URL Path to /nextLink.
10
Click the Create button at the top of the page to save the API connection.

​
Create the Table

Now that you’ve added a connection corresponding to a catalog, you can create tables for each of the API’s endpoints.
1
In your API connection, click Tables to open the Tables tab.
2
Click Add to add a new table.
3
Provide a Name for your table. This is the name that the table displays when accessing it from clients. For example: Vehicles.
4
Leave the Response Type as JSON and the Request call as GET.
5
Set the Request URL to your API endpoint URL. This example uses https://my_vehicles/api.
6
Leave the Parameters section blank, since there are none for this API.
7
Similarly, leave Headers blank.
8
In the Table Data section, click Configure to automatically fill out the Table Data information. This opens the Preview Request, which shows the following API response:
{
  "people": [
    {
      "personal": {
        "age": 21,
        "gender": "M",
        "name": {
          "first": "John",
          "last": "Doe"
        }
      },
      "vehicles": [
        {
          "type": "car",
          "model": "Honda Civic",
          "insurance": {
            "company": "ABC Insurance",
            "policy_num": "12345"
          },
          "features": [
            "sunroof",
            "rims"
          ],
          "maintenance": [
            {
              "date": "07-17-2017",
              "desc": "oil change"
            },
            {
              "date": "01-03-2018",
              "desc": "new tires"
            }
          ]
        },
        {
          "type": "truck",
          "model": "Dodge Ram",
          "insurance": {
            "company": "ABC Insurance",
            "policy_num": "12345"
          },
          "features": [
            "lift kit",
            "tow package"
          ],
          "maintenance": [
            {
              "date": "08-27-2017",
              "desc": "new tires"
            },
            {
              "date": "01-08-2018",
              "desc": "oil change"
            }
          ]
        }
      ]
    },
    ...
  ]
}
9
Click Next to access the Add Columns menu. Here, you need to select a root path, and any columns to expose. The Root Path is the JSON element that contains repeating sub-elements and determines the rows of the table. In this case, you need to select multiple root paths because multiple elements repeat in this document. Because the root elements are nested, some data repeats for each row, as shown in this example. Select the people element first, because it repeats for each person, as shown in the following snippet from the response:
{
     "people": [
         {
         <Person 1 data>
         },
         {
         <Person 2 data>
         }, 
         ...
     ]
 }
Also select the vehicles and maintenance elements, because data from those two repeated sections is also needed. Note: The JSON array uses square brackets [] to contain an array of JSON objects which are each contained within angle brackets {}.
10
For this example, select first, last from the people array, type, model from the vehicles array, and date, description from the maintenance array as the columns to expose.
11
Click Next to access the Table Preview. This renders the first 10 rows of the table as currently configured. If configured correctly, you should see data for each column selected:
firstlasttypemodeldatedescription
JohnDoecarHonda Civic07-17-2017oil change
JohnDoecarHonda Civic01-03-2018new tires
JohnDoetruckDodge Ram08-27-2017new tires
JohnDoetruckDodge Ram01-08-2018oil change
12
Click Confirm to automatically add your Table Data configuration. The data selected should be reflected in the Root Path and Columns fields in the Table Data section.
13
Click Save to save this table.

​
Test Queries

After configuring your connection and your table, you can test it by running queries in the Data Explorer.
1
In Connect AI, navigate to Data Explorer.
2
In the Connections list, select the TestAPI connection created earlier. This expands all of that connection’s tables, including the Vehicles table created in this example.
3
Click the ellipsis next to this table and select query. This automatically generates the SQL query to retrieve all vehicles:
  • SELECT * FROM [TestAPI].[REST].[Vehicles] You can modify this SQL query as needed. For example, you can add a WHERE clause to select only vehicles of a specific model:
  • SELECT * FROM [TestAPI].[REST].[Vehicles] WHERE model='Honda Civic'
4
Click Execute to run this query. If everything is configured correctly, the results are visible immediately.
To connect to this API data from your preferred client, refer to the details documented for the specific client on the Integrations page. To access this data, you might need to set the catalog (or database, depending on the client) to TestAPI.