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:

B78596011
B78596012
B78596013

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';

In section Configuring the Values to Be Used in a Parameterized Query the various sources that can be used to assign possible values to variables are described.

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.

Syntax of Parameterized Queries

A parameterized query is an expression depending on variables which generates a character string representing a query as a result. Variables are specified by prefixing them with the symbol ‘@’, followed by the name of the variable, provided that this name is an alphanumeric character string (letters and the characters ‘#’ and ‘_’). Variables with a name that includes any other character can be specified including the name between the symbols ‘@{‘ and ‘}’.

Note

When any of the symbols ‘@’, ‘', ‘^’, ‘{‘, ‘}’ appear in the constant parts of the parameterized statement, they must be escaped by the character ‘' (i.e. \@, \, \^, \{, \}).

Configuring the Values to Be Used in a Parameterized Query

Parameterized queries can obtain their values from different data sources. Scheduler allows data to be obtained from a CSV file, from a query against a database or from a manually introduced list of values. The configuration needed for each type of source is as follows:

  • CSV. A CSV data source that has been created previously.
  • DATABASE (JDBC or VDP). A JDBC or VDP data source needs to be selected and a non-parameterized query to be executed against the database (Query (non-parameterized) field) specified.
  • LIST. A list of values needs to be specified (Values field) separated by the character specified in the Separator field.

In the case of the LIST type, each tuple only consists of a field and, therefore, can only assign values to a variable. In the case of DATABASE and CSV sources, a tuple can include various fields and, therefore, can assign values to more than one variable.

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

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

Suppose that a data source for accessing a CSV file that contains a list of tuples with two fields each has also been configured. The data in the CSV file is as follows:

COMPANYNAME;COMPANYINDUSTRY
Denodo;Information Technologies
Acme Drinks;Beverages

Then the CSV source fields could be assigned to the variables so that they generate queries as follows to obtain all the job data:

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

Once the data sources have been added and configured, it is necessary to define for which query variables does each source return values. This can be done in two different ways:

  • Implicit association. This type of association is only applicable for those sources that return tuples with field names (DATABASE and CSV files that specify header). In these cases, it is assumed that the variables used in the parameterized query have the same name as some of the fields returned by the data sources.

  • Explicit association. CSV and DATABASE sources allow defining associations between the variables in the query (Query Parameter) and the name of the field in the source (Source Parameter).

    • In the case of CSV files that include a header that specifies the field names, the association is done with the name of the field; for CSV files without header the association is carried out by the position of the field, starting at 0.
    • In the case of DATABASE sources, the association is made between the name of the variable in the query and the name of the field in the source.

    It is important to take into account that several sources cannot return values for the same variable.

It is also possible to define a VDP job for querying an ITP wrapper, without creating its correspondent view in VDP. The syntax of the query would be as following:

QUERY WRAPPER ITP <name:identifier>
      [ ( <name:identifier> = <value:literal>
            [, <name:identifier> = <value:literal>]* )]

Using this syntax, ITP wrappers can be queried by using JDBC, to obtain parameters values to be used in parameterized queries.

[1]If the “Refresh parameter values on retry” option is checked and the sources change between retries, the retried values could be different from the ones that failed.