Query any REST API using ODBC in 5 mins – Editing the auto generated schema (Part 3)

In Part 1 and Part 2 of the tutorial series, we learned how you can sample and connect to multiple endpoints and query the data using SQL, without having to write a line of code. If you are new, recommend you start from Part 1.

When sampling REST endpoint, the Autonomous REST Connector heuristically generates a relational schema, including column names and data types, that should be user friendly. There can be situations where you might want to rename a table or field name to a value that avoids confusion, change the data type to one that you think is appropriate, or change the primary key to a different field. In this tutorial, we will guide you through the process of editing your schema to make these changes.

In part 2 of this tutorial series, we created a model file to specify the table names and the endpoints to sample.

This is the REST model in its simplest form. When you connect using a model file, Autonomous REST connector samples all the endpoints, normalizes the JSON structure to a relational schema and infers the metadata. All the mappings which it infers are stored in memory and you can access it by querying the _CONFIGURATION table.

In your database/query tool, such as WinSQL, Run the below query to get the auto-generated REST configuration created by Autonomous REST Connector.

Copy the data from CONTENT column to a text editor and paste it. It should take the following form:

Save your text file with a .rest extension. In the following examples, we will use the name YelpAuto.rest.

To modify the schema, you must point the driver to use the .rest file we just created.

1. Using the ODBC Administrator, open your data source definition you created in Part 1 in the Configuration Manger.
2. In the REST Config File field, specify the path to the Model file you just created.

For example:

This will make the Autonomous REST connector use this configuration instead of sampling the API every time you connect.

If you compare the model file we just created (YelpAuto.rest) against the simple one we created in Part 2 (yelp.rest), you will notice that the Autonomous REST Connector has populated the entries for the original endpoints with properties and nested objects from the service response, as well as assigned a data type for the fields. For example, here is the resolved entry for the categories endpoint:

#path: Specifies the endpoint that is sampled for the parent and child tables.

#key: Indicates the field that has been assigned as the primary key. In this example, the field named alias has been designated the primary key.

In the next example, we will look at an entry for an endpoint that required a query parameter. If you recall, the for the business search endpoint, we set a query parameter for the location field:

Now let us look at the resolved model entry for the location table:

Notice the following elements that we did not see in our earlier example:

• #virtual: Setting this parameter to true exposes the field as a virtual column. This is used when the field does not appear in the response as a simple key value pair.
• #default: Specifies the default value used to filter the result if none is provided by the SQL query. For example, we set a value of 27617 in Part 2 of the tutorial.
• #eg: Specifies the query parameter name that must be sent to the service.

You can easily change the primary key to a different field by using #keyelement. For example, in the categories table, let's change the primary key from the alias field to the title field. To designate the title field as the primary key, remove the #key element from the alias field entry and add it to the title entry. The updated syntax should take the following form:

The next time you connect, the driver will read the changes in the Model file and your new primary key for the categorizes table will be the title field.

You can change the name of the column in the relational schema by adding the new column name in angled brackets after the original name:

For example, the following syntax would rename the column alias to categorycode in the categories table

When you reconnect, the driver will read the updated Model file and apply your changes.

You can also change the default data type mapping for a column to any of those supported by the driver. A list of supported data types is available here.

When you reconnect, the driver will read the updated Model file and apply your changes.

When generating the relational schema from your endpoints, the Autonomous REST Connector sometimes generates ambiguous or non-desirable table names. If that occurs, you might want to manually change the name of your table.

For example, you might encounter a endpoint with a nested object that contains the same name. This may result in similarly named tables. You can see this in the relational mapping of the business endpoint, where there are tables named CATEGORIES and CATEGORIES_1.

CATEGORIES is the table that was created by sampling Categories endpoint. It contains all the categories that Yelp offers.

CATEGORIES_1 is one of the table that was created by sampling Businesses endpoint. If you look at the response from businesses endpoint, it contains a nested JSON array of categories to which that business is applicable.

To avoid confusion, rename the CATEGORIES_1 table BUSINESS_CATEGORY. You can rename tables using the following syntax:

Example:

When you reconnect, the driver will read the updated Model file and apply your changes.

You now know how to complete some basic customizations to your schema. Feel free to contact us if you have any issues.

In the next tutorial, we will look at configuring Autonomous REST Connector to page your results beyond the first page.