🛢️ Loading from OData using the Autonomous REST Connector

Options
Henry Simms
Henry Simms Administrator
edited December 2023 in Tips and tricks

The Autonomous REST Connector JDBC driver packaged with Aperture allows you to load (and refresh) the results returned from REST API calls as Datasets.

OData is a standardized REST interface, which means that any data that can be queried via OData API can be loaded into Aperture.

This article will demonstrate how to use the Autonomous REST Connector to retrieve data from OData, using the OData V4 example TripPin service.

Create a model with the Autonomous REST Composer

The first step is to use the Composer to create the configuration (a "model" file) that the driver will use. To launch the Composer, locate the DSautorest.jar file (likely to be in D:\ApertureDataStudio\drivers\jdbc) and double-click it.

This will launch a browser window. From here, navigate to arc.html, and create a new model with base URL https://services.odata.org/TripPinRESTierService

image.png

Configure connection options

Next, we configure the model's connection options. The TripPin service example service is quite simple: It uses SSL but does not require authentication, so we can leave most of the Composer settings as default. In the Security tab, switch Validate Server Certificate to false, to avoid certificate validation issues in the browser as you build the model (you can switch this on later when using the connection in Aperture).

image.png

View data in the Composer

In the Configure Endpoints tab, we can check things are working by querying the API's /people endpoint. This should return normalized data based on the connector's sampling of the API:

image.png

Notice that as part of the normalization, the sampling has generated separate linked tables for FEATURES, ADDRESSINFO and EMAILS, which are arrays in the PEOPLE object which can contain multiple values. These table are linked to the relevant row of PEOPLE using the POSITION / VALUE_POSITION key:

image.png

Download the .rest file and use in Aperture

You're ready to download the model to use in Aperture.

  1. Download the .rest file from the Composer and copy it to a location accessible by Aperture.
  2. In Aperture, create a new External System with the following settings:
    1. Type: JDBC
    2. DBMS: Autonomous REST 6.0
    3. REST configuration file: full path to the .rest file you just downloaded, for example "C:\ApertureDataStudio\drivers\jdbc\RESTfiles\TripPin.rest"
    4. You will need to create credentials, but these can be empty (name: "Not Required", username: "", password: "")

Now you can create a new Dataset using this External System. The available tables should appear as they did in the Composer:

image.png

Configure and load these Datasets:

image.png

Finally, use a workflow to join the tables together using the keys (eg join PEOPLE to EMAIL on POSITION & VALUE POSITION

Refining the configuration

The steps above provide the basics to get started. There's other things you'll likely need to configure for your OData API including authentication, paging, and modifying data types or table names. You can make these changes in the Composer UI or by manually editing the .rest file.

Comments

Categories