VDP Extraction Section¶

To configure the extraction section for VDP-type jobs a VDP-type data source needs to be selected. Once selected, the query to be performed against the VDP server needs to be specified using a parameterized query statement (Parameterized query field).

A parameterized query is a query expressed in the server’s query language (VQL), which can include variables prefixed with the character @ (the detailed syntax for including variables is explained in the section Syntax of Parameterized Queries). A parameterized query that includes variables represents a group of queries that you want to execute against the server. The different queries will be generated by replacing each variable with a value included in a list of input values. The lists of input values are obtained from a data source (see section Data Sources).

Example: Suppose that the next parameterized query has been configured to obtain data from a view called CLIENTS in the Virtual DataPort server:

SELECT *
FROM CLIENTS
WHERE taxId = '@TaxId'

Suppose also that a data source for accessing a CSV file that contains a list of Tax Ids as follows has been configured:

and that these values have been associated with the variable @TaxId (the section Configuring the Values to Be Used in a Parameterized Query explains in detail how to do it). Then Denodo Scheduler would generate the following queries on Virtual DataPort to get all the job data:

SELECT * FROM CLIENTS WHERE TaxId = 'B78596011';
SELECT * FROM CLIENTS WHERE TaxId = 'B78596012';
SELECT * FROM CLIENTS WHERE TaxId = 'B78596013';

The section Configuring the Values to Be Used in a Parameterized Query describes the sources that can be used to assign values to variables.

Each source can provide value to one or more variables simultaneously. Where there are different sources, Scheduler generates as many queries from the parameterized statement as possible combinations in the data returned from each of the sources that provide values to variables.

Additionally, it is possible to configure the number of query combinations to run from the same parameterized query and the level of concurrence in the execution:

• Maximum number of iterations. Specifies the maximum number of queries to be generated from the parameterized query statement specified. If the value specified is greater than the number of query combinations generated, it is ignored. If it is not specified, then all the combinations are executed.

• Maximum number of concurrent iterations. Specifies the maximum number of queries that will be executed in parallel from the queries generated by the parameterized query. The concurrent execution is performed by blocks, i.e. while the execution of the queries of the first block has not ended, execution of the next block of queries is not initiated. If this value is not specified, all queries will be executed sequentially.

It is important to note that Scheduler logs query combinations that have been successfully executed to distinguish them from those that have not been executed yet or have returned some type of error. Therefore, it is possible to associate a handler for retries with the job (see section Handler Section) that repeats the execution of those queries that have returned an error in their last execution and those that have not been executed yet (useful in the event that, for unknown reasons, the server has performed its execution in an irregular way). It will also be possible to force the execution of failed queries from the “Scheduler” perspective using the action Start with state (see section Jobs). In both cases, if the option Refresh parameter values on retry is checked (default configuration), the sources will be queried in each execution (including retries) and their values will be retrieved in blocks, while if it is not checked, their values are read at once, cached and used in future retries or when starting with state. Note that in both cases only combinations of values that failed will be used for the execution of a retry or a new execution with state. As a recommendation, if the values from the sources change rarely 1 or they are too many, keep this option checked in order to reduce memory consumption.

SELECT *
FROM COMPANY
WHERE NAME = '@COMPANYNAME' AND INDUSTRY = '@COMPANYINDUSTRY'

COMPANYNAME;COMPANYINDUSTRY
Denodo;Information Technologies
Acme Drinks;Beverages

SELECT * FROM COMPANY WHERE NAME = 'Denodo' AND INDUSTRY = 'Information Technologies';
SELECT * FROM COMPANY WHERE NAME = 'Acme Drinks' AND INDUSTRY = 'Beverages';