Example REST Connector

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 REST Connection

First, create a new REST connection.

1. Open the Sources page of the navigation menu.

2. Click + Add Connection in the upper-right corner.

3. In the Add Connection dialog that opens, type REST to display the REST 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 REST connection.

Create the Table

Now that you have added a connection corresponding to a catalog, you can create tables for each of the API’s endpoints.

1. In your REST 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 must 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 must 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, desc 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:

    first	last	type	model	date	description
    John	Doe	car	Honda Civic	07-17-2017	oil change
    John	Doe	car	Honda Civic	01-03-2018	new tires
    John	Doe	truck	Dodge Ram	08-27-2017	new tires
    John	Doe	truck	Dodge Ram	01-08-2018	oil 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. 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.