Query any REST API using JDBC quickly – Editing the auto generated schema (Part 3). If you are new, we would recommend you to start Part 1.
There can be times where you might want to rename a table or field name to avoid confusion for end users or change the data type for a certain field to a type that you think is appropriate or change the primary key to a different field in the generated table. In this tutorial, we will be walk you through the needed steps.
{ "categories":{ "#path":[ "https://api.yelp.com/v3/categories /categories" ], "alias":"VarChar(48)", "title":"VarChar(52),#key", "parent_aliases[]":"VarChar(34)", "country_whitelist[]":"VarChar(3)", "country_blacklist[]":"VarChar(3)" }, "events":{ "#path":[ ], "events[]":{ "attending_count":"Integer", "category":"VarChar(64)", "cost":"VarChar(64)", "cost_max":"VarChar(64)", "description":"VarChar(238)", "event_site_url":"VarChar(310)", "id":"VarChar(75)", "image_url":"VarChar(102)", "interested_count":"Integer", "is_canceled":"Boolean", "is_free":"Boolean", "is_official":"Boolean", "latitude":"Double", "longitude":"Double", "name":"VarChar(64)", "tickets_url":"VarChar(64)", "time_end":"Timestamp(0)", "time_start":"Timestamp(0)", "location":{ "address1":"VarChar(64)", "address2":"VarChar(64)", "address3":"VarChar(64)", "city":"VarChar(64)", "zip_code":"Integer", "country":"VarChar(64)", "state":"VarChar(64)", "display_address[]":"VarChar(64)", "cross_streets":"VarChar(64)" }, "business_id":"VarChar(64)" }, "total":"Integer,#key" }, "business":{ "#path":[ ], "businesses[]":{ "id":"VarChar(64)", "alias":"VarChar(64)", "name":"VarChar(64)", "image_url":"VarChar(102)", "is_closed":"Boolean", "url":"VarChar(295)", "review_count":"Integer", "categories[]":{ "alias":"VarChar(21)", "title":"VarChar(33)" }, "rating":"Double", "coordinates":{ "latitude":"Double", "longitude":"Double" }, "transactions[]":"VarChar(64)", "price":"VarChar(64)", "location":{ "address1":"VarChar(64)", "address2":"VarChar(64)", "address3":"VarChar(64)", "city":"VarChar(64)", "zip_code":"Integer", "country":"VarChar(64)", "state":"VarChar(64)", "display_address[]":"VarChar(40)" }, "phone":"VarChar(64)", "display_phone":"VarChar(64)", "distance":"Double" }, "total":"Integer,#key", "region":{ "center":{ "longitude":"Double", "latitude":"Double" } }, "location":{ "#type":"VarChar(64),#key", "#virtual":true, "#default":"27617", "#eq":"location" } }}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:
"categories":{ "#path":[ "https://api.yelp.com/v3/categories /categories" ], "alias":"VarChar(48),#key", "title":"VarChar(54)", "parent_aliases[]":"VarChar(34)", "country_whitelist[]":"VarChar(3)", "country_blacklist[]":"VarChar(3)" }#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:
https://api.yelp.com/v3/businesses/search?location=27617Now let us look at the resolved model entry for the location table:
"location":{ "#type":"VarChar(64),#key", "#virtual":true, "#default":"27617", "#eq":"location" }Notice the following elements that we did not see in our earlier example:
* #virtual: Specifies that this is a virtual field and this field does not appear in the response from the API.
* #default: Specifies the default value the connector uses if location is not specified in the SQL query.
In Part 1, we set this value to 27617. Therefore, it was accepted as a default value.
* #eq: Specifies the query parameter name the must be used to send the value
You can designate the primary key for a table by adding the #key element to a column object in the Model file. The #key element should follow the data type element, separated by a comma. In the following example, the "alias" column is the designated primary key.
"categories":{ "#path":[ "https://api.yelp.com/v3/categories /categories" ], "alias":"VarChar(48),#key", "title":"VarChar(52)", "parent_aliases[]":"VarChar(34)", "country_whitelist[]":"VarChar(3)", "country_blacklist[]":"VarChar(3)" }The next time you reconnect to the REST data "alias" will be used as the primary key.
You can manually configure the mapping of data types to a column.
For example, take the "alias" column: "alias
We can change the data type to LongVarChar with a precision of 3200 by modifying the column data type and size parameters this way:
"alias<categorycode>":"LongVarChar(3200),#key" Changes you make to data types will take effect when you reconnect.
Note: You may assign any data types supported by the connector.
Refer to Data types for details.
Just as with column names it may be useful to rename tables if tables names are confusing or lack real world context.
For example, in the Model file used to sample Yelp endpoints in Part 2, two categories tables were created: CATEGORIES and CATEGORIES_1. The first came from the categories endpoint, while the second came from the businesses endpoint.
In the following example, the name of the categories table found in the businesses endpoint has been changed to business_category:
"categories<business_category>[]:{}"To learn more about how changes to the Model file impact the relational schema, consider changing the file and running queries against your REST source. Please contact us with your questions.