TDV Adapter for REST

Build 22.0.8462

Modeling REST Data

In this section we will show how to control the various schemes that the adapter offers to bridge the gap with relational SQL and Generic REST services. The Generic REST Adapter provides a managed way for you to use the two prevailing techniques for dealing with nested data:

  • Parsing the data structure and building a relational model based on the existing hierarchy.
  • Drilling down into the nested elements using horizontal and vertical flattening.

Parsing Hierarchical Data

By default, the adapter automatically detects the rows in a document, so that you do not need to know the structure of the underlying data to query it with SQL. Set the DataModel property to choose a basic configuration of how the adapter models the rows into tables.

Flattening Objects and Arrays into Rows

To flatten data, you only need to be familiar with two data structures -- objects and arrays. In JSON, these are literal structures. In XML, the analogous structures are below:

  • Object: Any parent element that does not repeat at the same height.
  • Array: Any element that repeats at the same height.

In the following example from the people collection, maintenance is an object array, since each maintenance node has child elements.

<maintenance>
  <date>07-17-2017</date>
  <desc>oil change</desc>
</maintenance>
<maintenance>
  <date>01-03-2018</date>
  <desc>new tires</desc>
</maintenance> 

Configuring Automatic Schema Discovery

The adapter discovers columns and data types by scanning the RowScanDepth count of objects in arrays. Set the FlattenObjects and FlattenArrays properties to configure how nested data is flattened into columns; see Automatic Schema Discovery for examples.

Executing SQL to REST

Any relation you can access through flattening you can also access with an ad-hoc SQL query. The adapter enables you to query nested data with the following capabilities:

Customizing Schemas

Customizing Schemas enables you to project your chosen relational structure on top of a document. This allows you to choose the names of columns, their data types, and the locations of their values in the document.

System Catalog

The System Tables reflect the schemas you configured, custom schemas or dynamically discovered. The Stored Procedures surface additional functionality in the adapter's data processing operations that cannot be modeled as SELECT, INSERT, UPDATE, or DELETE. You can find the reported stored procedures defined in .rsb files in the folder specified by Location -- if Location is not specified, the db subfolder of the installation directory.

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8462