Denodo query delegation and OBL fields

Applies to: Denodo 8.0 , Denodo 7.0
Last modified on: 28 May 2020
Tags: Performance Web Services

Download document

You can translate the document:

Goal

This document describes the query delegation behavior of an obligatory (OBL) field used in the WHERE condition of a query fired against a Web Service data source.

Content

Some of the data sources such as Web Services may allow only limited query capabilities on its data. In those cases, one can define the query capabilities on the Search Methods tab of the base view.

In these kind of scenarios, when a field of the base view is defined as obligatory with the allowed operator as “=”, and the “IN” operator is used in the WHERE condition of the query, the execution engine of the Denodo will convert the query by using the “=” operator in order to make it compatible to the specified Search Methods and will delegate one request for each value given in the IN clause.

Once the output is received from the data source, Denodo will combine them in the virtual layer using the UNION operation to get the final output.

For example, assume there is a Web Service that provides the Product Details. According to the limitation of this data source, it is mandatory to pass the Product ID on every request. The implementation of this requirement in Denodo and the working behavior are as follows:

Schema of the base view bv_getallproductdetails:

Set the productid field as obligatory with the “=” parameter and multiplicity of one by executing this VQL:

ALTER TABLE bv_getallproductdetails

    ALTER SEARCHMETHOD bv_getallproductdetails (

        CONSTRAINTS (

            ADD productid (=) OBL 1

        )

    WRAPPER (ws bv_getallproductdetails)

);

Note: In Denodo 7.0 or previous, you can also do this graphically (via GUI) in the Virtual DataPort Administration Tool on the Search methods tab.

You should see now the mandatory fields in the Search methods tab:

Executing the base view  will ask for the input:

   

Using the ‘=’ operator in the query and provide value 1

Only one query is delegated to the data source with the Search Condition as productid = 1

Using IN operator in the query

The query is transformed using the ‘=’ operator with the search condition productid = 1 on one node and productid = 2 on another node.

The output is combined by using a UNION operation

References

Virtual DataPort VQL Guide: Query Capabilities: Search Methods and Wrappers 

Virtual DataPort VQL Guide: Example of How a Search Method Is Created

Questions

Ask a question
You must sign in to ask a question. If you do not have an account, you can register here

Featured content

DENODO TRAINING

Ready for more? Great! We offer a comprehensive set of training courses, taught by our technical instructors in small, private groups for getting a full, in-depth guided training in the usage of the Denodo Platform. Check out our training courses.

Training