REST Easy with CData Drivers

The CData Drivers for REST provide standards-based access to REST resources by modeling local and remote data as relational tables, views, and stored procedures. The high-performance processing operations hide the complexity of making HTTP requests and processing the response, allowing you to interact with REST resources using the familiarity and utility of SQL-92 queries.

In this article, we will look at some sample REST data and discuss how to configure the CData Drivers for REST to work with that data.

Connecting to a REST Resource

For this section, we are connecting to a REST resource with a single endpoint. The data returned is the City of Los Angeles 2010 Census data, grouped by ZIP code.

Data Extract

Below is an extract of the data found in the sample REST resource.

<response>
  <row>
    <row _id="228" _uuid="A47815EC-0DA0-499D-9CE6-616A23C4F155" _position="228" _address="http://data.lacity.org/resource/nxs9-385f/228">
      <zip_code>91371</zip_code>
      <total_population>1</total_population>
      <median_age>73.5</median_age>
      <total_males>0</total_males>
      <total_females>1</total_females>
      <total_households>1</total_households>
      <average_household_size>1.00</average_household_size>
    </row>
    <row _id="1" _uuid="1AE8EAF5-D6E6-4AF9-88FD-3CF179F2A8FC" _position="1" _address="http://data.lacity.org/resource/nxs9-385f/1">
      <zip_code>90001</zip_code>
      <total_population>57110</total_population>
      <median_age>26.6</median_age>
      <total_males>28468</total_males>
      <total_females>28642</total_females>
      <total_households>12971</total_households>
      <average_household_size>4.40</average_household_size>
    </row>
    ...
  </row>
</response>

Configuring the Connection

Now that we know what the data for the REST resource looks like, we are ready to configure the connection. The HTTP endpoint for the 2010 Census data for Los Angeles is available (as XML) at the URL https://data.lacity.org/api/views/nxs9-385f/rows.xml. Use this value for the URI property.

Because the REST resource returns XML data, set the Format property to "XML". Set the XPath property to "/response/row/row" so that each repeated row child element appears as a single row in the data table representation of the REST resource. With these properties set, you are ready to begin working with the data as a data table instead of a raw REST resource. The CData Drivers for REST parse the XML data and expose all of the available data in each row element, including the child elements and the XML attributes of each row.

If you wish to configure the driver to automatically generate schema file(s) set GenerateSchemaFiles to "OnUse" or "OnStart" and set Location to an accessible directory on disk. The completed connection string follows:

URI=https://data.lacity.org/api/views/nxs9-385f/rows.xml; Format=XML; XPath=/response/row/row; GenerateSchemaFiles=OnStart; Location=C:/Users/Administrator/MySchemaDirectory;

Using this connection string, the driver exposes a single table named XMLData that has as its columns the attributes and child elements in each row element of the data. Immediately below is a table containing a subset of returned data (some of which has been truncated for readability). In the section following, we provide a explanation of the generated schema file for the census data and an example of a custom schema file.

Modeling REST Resources as Databases

If the REST resource you are connecting to has a single endpoint, then using the connection string properties is the best way to create a database model. However, most REST resources expose data at several endpoints and may include endpoints for performing actions, such as sending a message, uploading a document, and more. In these cases, the best approach is to create schema files to model the REST endpoints as tables, views, and stored procedures.

LACensusData2010.rsd

Below is a schema file based on the REST resource for the 2010 Los Angeles census data, written using RSBScript. A detailed explanation of the schema file follows. For more information on using RSBScript, refer to the RSBScript Reference section in the online help documentation.

<rsb:script xmlns:rsb="http://www.rssbus.com/ns/rsbscript/2" xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <rsb:info title="LACensusData2010" desc="2010 Census data for the city of Los Angeles, grouped by ZIP code." xmlns:other="http://www.rssbus.com/ns/rsbscript/2/other">
    
    <attr name="ID"                    xs:type="integer" readonly="false" other:xPath="@_id"                    />
    <attr name="AverageHouseholdSize"  xs:type="double"  readonly="false" other:xPath="average_household_size"  />
    <attr name="MedianAge"             xs:type="double"  readonly="false" other:xPath="median_age"              />
    <attr name="TotalFemales"          xs:type="integer" readonly="false" other:xPath="total_females"           />
    <attr name="TotalHouseholds"       xs:type="integer" readonly="false" other:xPath="total_households"        />
    <attr name="TotalMales"            xs:type="integer" readonly="false" other:xPath="total_males"             />
    <attr name="TotalPopulation"       xs:type="integer" readonly="false" other:xPath="total_population"        />
    <attr name="ZIPCode"               xs:type="integer" readonly="false" other:xPath="zip_code"                />
  </rsb:info>

  <rsb:set attr="URI" value="https://data.lacity.org/api/views/nxs9-385f/rows.xml" />

  <rsb:set attr="RepeatElement" value="/response/row/row" />

  <!-- The GET method corresponds to SELECT. -->
  <rsb:script method="GET">
    <rsb:call op="xmlproviderGet">
      <rsb:push/>
    </rsb:call>
  </rsb:script>

  <!-- The POST method corresponds to INSERT. -->
  <rsb:script method="POST">
    <rsb:set attr="method" value="POST"/>
    <rsb:call op="xmlproviderGet">
      <rsb:throw code="500" desc="Inserts are not currently supported."/>
      <rsb:push/>
    </rsb:call>
  </rsb:script>

  <!-- The MERGE method corresponds to UPDATE. -->
  <rsb:script method="MERGE">
    <rsb:set attr="method" value="PUT"/>
    <rsb:call op="xmlproviderGet">
      <rsb:throw code="500" desc="Updates are not currently supported."/>
      <rsb:push/>
    </rsb:call>
  </rsb:script>

  <!-- The DELETE method corresponds to DELETE. -->
  <rsb:script method="DELETE">
    <rsb:set attr="method" value="DELETE"/>
    <rsb:call op="xmlproviderGet">
      <rsb:throw code="500" desc="Deletes are not currently supported."/>
      <rsb:push/>
    </rsb:call>
  </rsb:script>

</rsb:script>

There are several distinct parts of the schema definition, from the table and column definitions to specific configurations based on the REST resource being exposed to the definitions for handling different CRUD (create, read, update, delete) operations. These parts are described below:

Table Name & Description

<rsb:info title="LACensusData2010" desc="2010 Census data for the city of Los Angeles, grouped by ZIP code." xmlns:other="http://www.rssbus.com/ns/rsbscript/2/other">

The rsb:info element contains the table definition and has two important attributes: title is the name of the table or view and desc is the description of the table or view. These values determine what information is exposed in common BI, reporting, and ETL tools.

Column Definitions

...
    <attr name="ID"  xs:type="integer" readonly="false" other:xPath="@_id"  />
...

Each element has several attributes that provide information about the column:

• name: provides the name of the column, used in the projection of a SQL query and exposed in common BI, reporting, and ETL tools when working with the REST resource
• xs:type: provides the SQL data type for the column
• readonly: indicates whether the column is read-only or not, "false" by default
• other:xPath: configures how to parse the value for the column using XPath notation (details below)

REST Resource URI

<rsb:set attr="URI" value="https://data.lacity.org/api/views/nxs9-385f/rows.xml" />

The rsb:set keyword of RSBScript sets properties for use in the drivers. To indicate the file or endpoint for the REST resource for the table, set the attr and value XML attributes to "URI" and the URI for the resource, respectively. This value overrides any value set in the connection string, allowing you to configure each schema to point to a different REST resource.

Repeat Element

<rsb:set attr="RepeatElement" value="/response/row/row" />

To define XPath for this schema, set the attr attribute to "RepeatElement" and set the value attribute to the XML element to be used to split the REST resource into multiple rows. Setting the RepeatElement attribute in the schema definition overrides the value set in the XPath connection property.

XPath Details

Several properties and attributes used to configure the REST drivers use XPath notation. For the XPath connection property and the RepeatElement attribute of custom schemas, the XPath value defines the XML element on which to split the data into rows. The other:xPath attribute in a column definition defines exactly which XML attribute or child element to expose as the column value and can be absolute or relative (to XPath or RepeatElement).

For example, using the XML data exposed above, define the other:xPath attribute for the Id column using the absolute path /response/row/row@_id or the relative path @_id. Note that the @ character indicates the value is parsed from an XML attribute, not an XML element.

If you wish to expose the value of an immediate child element of the repeated XML element, omit the other:xPath attribute. By default, the drivers attempt to parse an element with the same name as the name attribute. For example, the column definition for the ZIPCode column could be as simple as the following:

<attr name="ZIPCode" xs:type="integer" />

Defining CRUD Operations

The schema above defines the behavior for an HTTP GET operation (SELECT), but throws errors for HTTP POST (SQL INSERT), MERGE (SQL UPDATE), and DELETE (SQL DELETE) operations, asserting that those operations are currently unsupported. If you are working with a read-only REST resource, or wish for your integration to be read-only, delete the rsb:script keywords for INSERT, UPDATE, and DELETE. This causes BI, reporting, and ETL tools to treat the exposed resource as a SQL view instead of a table.

Use RSBScript to customize the behavior for each operation, from adding URL parameters (as supported by the REST resource) based on columns and values found in the SQL query (typically used for SELECT and DELETE requests) to building PUT and POST data in order to support INSERT and UPDATE requests.

Sample Operation Implementations

The implementations below are examples of how to implement full CRUD support for a REST resource. (Note that the example REST resource for this article does not support write operations.) More details on implementing specific requests are found in the online Help documentation in the Modeling REST Data section.

• SELECT * FROM LACensusData2010 WHERE ([ZIPCode] = 90005)

  SELECT queries, like this one, often contain criteria in the WHERE clause. To implement the same kind of search criteria for the REST resource, define the GET method using RSBScript similar to that below, appending the criteria as URL parameters to the URI property for the REST resource.

  <rsb:script method="GET">
    <rsb:check attr="_input.ZIPCode">
      <rsb:set attr="uri" value="[uri]?zip_code=[_input.ZIPCode]"/>
    </rsb:check>
    ...
    <rsb:call op="xmlproviderGet">
      <rsb:push/>
    </rsb:call>
  </rsb:script>

• INSERT INTO LACensusData2010 (MedianAge, TotalFemales, ...) VALUES (25.5, 3587, ...)

  This INSERT query contains new data to be added to the XML resource. To implement the INSERT query, define the POST method using RSBScript similar to that below, building XML data to be used as the HTTP POST data.

  <rsb:script method="POST">
    <rsb:set attr=method value="POST"/>
    <rsb:validate attr="_input.MedianAge" desc="median_age is required to INSERT" />
    <rsb:validate attr="_input.TotalFemales" desc="total_females are required to INSERT" />
    ...
    <rsb:set attr="data">
      <row>
        <median_age>[_input.MedianAge]</median_age>
        <total_females>[_input.TotalFemales]</total_females>
        ...
      </row>
    </rsb:set>
    <rsb:call op="xmlproviderGet"/>
  </rsb:script>

• UPDATE LACensusData2010 SET [MedianAge] = 25.5 WHERE [Id] = 57

  This UPDATE query contains new data to be added to the XML resource. To implement the UPDATE query, define the POST method using RSBScript similar to that below, building XML data to be used as the HTTP PUT data.

  <rsb:script method="MERGE">
    <rsb:set attr=method value="PUT"/>
    <rsb:validate attr="_input.ID" desc="id is required to UPDATE" />
    <rsb:set attr="uri" value="[uri]([_input.ID])" />
    <rsb:set attr="data">
      <row>
        <rsb:check attr="_input.MedianAge">
        <median_age>[_input.MedianAge]</median_age>
        </rsb:check>
        ...
      </row>
    </rsb:set>
    <rsb:call op="xmlproviderGet"/>
  </rsb:script>

• DELETE FROM LACensusData2010 WHERE [Id]='57'

  This DELETE query contains the Id of an entry in the XML resource to be deleted. To implement the DELETE query, define the DELETE method using RSBScript similar to that below.

  <rsb:script method="DELETE">
    <rsb:set attr=method value="DELETE"/>
    <rsb:validate attr="_input.Id" desc="Id is required to UPDATE" />
    <rsb:set attr="uri" value="[uri]([_input.Id])" />
    <rsb:call op="xmlproviderGet"/>
  </rsb:script>

Get Started

The CData Drivers for REST allow you to easily work with REST resources as if they were a database, trading the complexities of REST requests for the familiarity of SQL in common technologies like JDBC, ODBC, ADO.NET, and more. Download a free, 30 day trial of any of our drivers and start working with REST resources in BI, reporting, ETL, and custom applications today. If you have any questions, reach out to our world-class Support Team.