Creating Union Views

1. A union view merges the tuples from various views into a single view. These views can belong to different databases.
2. In standard relational algebra, all the relations or tables must have the same schema (same attributes with the same type). Virtual DataPort provides the standard relational algebra UNION operation. However, Virtual DataPort also has an extended union operation, whereby if any of the input views has an attribute that is not present in the other views, it is added to the output view.

Extended Union Views

Standard Union Views

Partitioned Union Views

Creating Join Views

A join view executes the relational algebra operation JOIN on a series of input views. These views can belong to different databases.

To create a join view, click Join on the File > New menu or, right-click the Server Explorer and click Join on the menu New.

The Tool will open the “Join view” dialog and it will add the view that is currently selected in the Server Explorer. To add more views to the join view, click the Model tab and drag the views from the Server Explorer to this tab.

You can join more than two views in the same join view.

In our example, we will join the view incidents created in the previous section and the Web service view average_monthly_sales, created in the section Creating Base Views from a SOAP Web Service.

The “Join view” dialog has six tabs:

1. Model: drag the views that will form the join. Consider this:

• • When you add two views to this tab, the Administration Tool will automatically create a join condition if there is an association between these views and this association is a referential constraint (i.e. it is a primary key/foreign key relationship). The join condition will be the same as the condition mappings of the association.

  • When adding a view to a join view, the Administration Tool now suggests the views you may want to add to the join view, based on the associations of that view. The suggestions are next to the label Associated views; just click the view to add it automatically.

To define a simple join condition do this:

• • If you use the Administration Tool, drag the field of one view to the field of the other view. To configure the operator used in this JOIN condition, double-click the arrow that joins both fields and select the operator. To add more complex join conditions, go to the “Join Conditions” tab.

  • If you use the Design Studio, first, click on the link button (on the top-right side of the source view) and drag it to the target view. After connecting the views, click the link and a dialog will show up at the bottom of the Model tab with the fields of both views. In this dialog, link a field of the left view with another of the right view. The source view and the target view are the left view of the join and the right view of the join respectively.

Connecting internet_inc with phone_inc using the Design Studio

To define a cross join, just drag the views into the dialog and do not link any field.

In the “Model” tab of the view, you can add “View parameters”. The section Parameters of Derived Views explains what are view parameters and the rules you need to follow when using them.

1. Join Conditions: tab where you can define the join strategy and the join conditions:

   1. Configure the join strategy. To do this, click on the triangle icon located between the names involved in the join. The options are:

      1. Join type: Inner, Left outer, Right outer and Full.

      2. Execution method: Hash, Merge, Nested and Nested parallel. If you select nested parallel, you have to provide the maximum number of parallel subqueries (“Nested parallel number”). If you select the option “Any” method, the Server will try to select the best suitable join method.

      3. Order in which the input views are considered. If it is “Ordered”, the view on the left of the join will be considered the first view.

      4. If you want to define a join condition between a field of a simple type (int, long, etc.) and a subfield of a compound type, the subfield of the compound type has to be placed in the left side of the join condition.

         For example, let us say that you want to create a join view with these two views:

         1. view_a, which has a field f1 of type int

         2. view_b, which has a field f2 of type array. This array has two int subfields: f2_a and f2_b.

         If you want the join condition to be f1 = f2_b, you have to define the join condition as ... JOIN ... ON (f2).f2_b = f1 That is, the subfield of the array must be placed on the left side of the join condition.

      Note: These preferences may have a significant impact on the performance of queries. Read the section Optimizing Join Operations for more information about this.

   2. Edit the join conditions added in the “Model” tab, by clicking on them.

      Click Add new condition to define a new join condition and Add cross join to define a cross join between two views.

      In simple scenarios, you can define join conditions and cross joins graphically from the “Model” tab. However, in complex situations you need to define these from the “Join Conditions tab”:

      1. By default, the conditions added with the “New Join Condition” dialog are added to the most internal join that contains all the fields of the condition.

         In complex joins, you may need to specify to which join the new condition belongs. To do that, from the “New Join Condition” dialog, select the check box at the bottom and select the views involved in the target join.

      2. From the “Model” tab, you cannot specify a join between a view and the result of a cross join of two views. E.g.:

         (view_a CROSS JOIN view_b) INNER JOIN view_c ON view_a.field1 = view_c.fieldA

         Note: In the Design Studio, to define a join between a view and the result of a cross join of two views, follow these steps:

         1. Connect the views view_a and view_b (do not add join conditions)

         2. Connect view_a and view_c.

         3. Add the condition by connecting the field view_a.field1 with the field iew_c.fieldA.

         The Add cross join button is not available in the Design Studio.

         To create this view, follow these steps:

         1. Drag the three views into the “Model” tab.

         2. In the “Join Conditions” tab, click Add cross join, select view_a and view_b and click Ok.

         3. Click Add join condition and enter the condition view_a.field1 = view_c.fieldA. Then, select the check box at the bottom of the dialog. In the Left subview list, select (view_a CROSS JOIN view_b) and in Right subview, view_c.

   The section Join Conditions with Similarity Operators explains the meaning of the operator ~.

   Note: To create Cross Join views, you just have to drag the views into the “Model” tab and do not define any join condition in the “Model” tab, the “Join” tab or the “Where condition” tab.

   Note: Do not use fields that are “View parameters” in the join conditions.

3. Where Conditions: tab that allows you to add WHERE conditions to the definition of the view.

4. Group By: tab that allows you to add GROUP BY attributes to the view.

5. Output: tab that allows you to configure the output of the view. That is, renaming the view and its fields, add derived attributes, define the primary key of the view, etc.

6. Metadata: tab that allows you to define the folder where the new view will be stored and provide a description for the new view.

The tabs Where Conditions, Group By, Output and Metadata work in the same way as in the Union view dialog. The section Creating Union Views explains in more detail how to use them.

In our example:

• Click on Join on the menu File > New.

• Drag the views incidents and average_monthly_sales to the “Model” tab.

• Add the join condition incidents.taxId=average_monthly_sales.taxId by dragging the name of the field taxId of one of the tables, to the field taxId of the other table.

• In the “Output” tab:

  • Rename the view to incidents_sales.

  • Remove the field incidents.taxId. To do this, select this field and click on Remove selected.

After this, click Save to create the view. Then, the Tool will display the schema of the new view.

A Selection view executes the relational algebra operations of selection (filtering) and projection on an input view. This view can belong to a different database.

To create a selection view, click Selection on the File > New menu or right-click on the Server Explorer and click Selection on the New menu.

The Tool will open the “Selection view” dialog and it will add the view that is currently selected in the Server Explorer. To change the input view, click the button X to remove the current view from the “Model” tab and drag another view from the Server Explorer.

In our example, we will drag the view incidents_sales created in the section Creating Join Views.

The “Selection view” dialog has five tabs:

4. Model: tab where you have to drag the view that will be the source of the new view. You can drag a view from another database.

   In this tab, you can add view parameters.

5. Where Conditions: tab that allows you to add WHERE conditions to the definition of the view.

   If you add WHERE conditions, you have to select one of the WITH CHECK OPTION clause options to decide if you want to force every row that is inserted or updated, to conform to the definition of this view (see section Use of WITH CHECK OPTION of the VQL Guide). The available options are:

   1. None: nothing is checked when a client executes INSERT operations on this view.

   2. Local: the selection condition of this view is checked when a row is inserted or updated. It does not check the conditions of the lower-level views (the views that participate in the definition of this view).

   3. Cascade: the projection conditions of this view and the lower-level views are checked when a row is inserted or updated.

   If the “Automatic simplification of queries” is enabled, when executing an INSERT/UPDATE/DELETE query over a derived view, the Server assumes that this view was created with the option “WITH CHECK OPTION”. As a result, the Server checks that the data inserted/updated/deleted meets the WHERE condition of the definition of the view.

   To check if this option is enabled, click “Queries optimization” on the menu Administration > Server configuration.

6. Group By: tab that allows you to add GROUP BY fields to the view.

7. Output: tab that allows you to configure the output of the view. That is, renaming the view and its fields, add derived attributes, define the primary key of the view, etc.

8. Metadata: tab that allows you to define the folder where the new view will be stored and provide a description for the new view.

The tabs Where Conditions, Group By, Output and Metadata work in the same way as in the Union view dialog. The section Creating Union Views explains in more detail how to use them.

You will now create two views for our example:

• Right-click on the view incidents_sales in the Server Explorer and click Selection, on the menu File > New. You can see that the Tool has added this view to the Model tab.

• In the Where Conditions tab, add the condition revenue>600 to obtain data only of those clients with an average monthly volume of sales that exceeds 600 euros.

• In the Output tab, rename the view to pref_clients_inc_sales.

After this, click Save to create the view. Then, the Tool will display the schema of the new view.

Now, you have to create the second view, which has a GROUP BY field:

• Right-click on the view pref_clients_inc_sales in the Server Explorer and click Select, on the File > New menu.

• In the Group By tab, do the following:

  • Select the Use group by check box.

  • Select the taxTd and revenue fields and click Add >>.

• In the Output tab, do the following:

  • Rename the view to inc_grouped_by_pref_clients.

  • Click New Aggr. expression. The name of the new field isnum_incidents and the expression is COUNT (*).

• Click Save.

Creating Conditions with the Compound Values Editor

NOTE: The Compound values editor is not currently available on the Design Studio and it is necessary to write the value directly using VQL syntax. Notice that future updates will extend the current options to add the Compound values editor.

All the dialogs to create and edit derived views (join, union, minus, intersect, flatten and selection) have a Where Conditions tab to add WHERE conditions to the definition of the view. When using the Simple condition mode of this tab, you have two options to create constants of compound types (register or array):

1. Write the value directly in VQL syntax. E.g. { ROW( 'B78596011' ), ROW( 'B78596012' ) }

   See section Conditions with Compound Values of the VQL Guide for more details about this syntax.

2. Or, use the Compound values editor. To open it, click  beside the right operand (only available for register or array fields).

As an example, we explain how to create a new Selection view that has a WHERE condition with a compound value. But first, we need to create a new base view:

1. Open the Web Service data source sales (created in the section Importing SOAP Web Service Sources) and click Create base view.

2. Click Create base view beside the operation getSumRevenuebyTaxTds.

3. Rename the new base view to RevenueSum .

4. Rename its input attribute to clients and the return one, to totalrevenue.

5. The clients attribute is an array of registers of the type getsumrevenuebyids_in0. Virtual DataPort generates this type automatically when it creates the view.

6. Each element in this array will be a register of the type getsumrevenuebyids_in0_string, which is also automatically generated by Virtual DataPort.

7. Rename the element of the clients array to taxTd.

8. Click Save to create the base view.

Parameters of Derived Views

A view can have View Parameters in addition to regular columns. View parameters are useful when you want to build a view containing a where condition but you do not want to specify a fixed filter value on the view definition, as you want it to be dependent on the query instead.

For example, let us say that we have two views:

1. A view client with these fields: name, income, and state.

2. And a view wealthy_client_by_state defined as:

CREATE VIEW wealthy_client_by_state AS
SELECT state, COUNT(*)
FROM client
WHERE income > 1000000
GROUP BY state

There is a limitation in the second view: the limit of income to consider a client wealthy is static. Therefore, we have to know this limit before creating the view. If we wanted to change this limit at runtime, we could remove the WHERE condition and add the field INCOME to the GROUP BY fields. But then, we would have to add this field to the GROUP BY clause and we might not want to do that.

To avoid this problem, add a “View parameter” to the view. When you add a view parameter to a view, that parameter will be part of the output schema and you can use it in the same places you use a regular field.

Using view parameters, you can modify our previous example so you can specify the limit of income at runtime. To do this, edit the view and follow these steps:

1. Click the Model tab and then, click Edit  beside “View parameters”. In this dialog, click Add new parameter to add the new view parameter.

   Set the name of the parameter to wealthy_client_income_limit, select the type long and set the default value of the parameter to 1000000.

2. Click Where Conditions and set the condition income > wealthy_client_income_limit.

The view will have a new output field wealthy_client_income_limit, which you can use to change the condition at runtime. For example,

SELECT *
FROM wealthy_client_by_state
WHERE wealthy_client_income_limit = 250000
Will take into account clients with an income greater than 250,000.

As the parameter has a default value, if you execute

SELECT *
FROM wealthy_client_by_state
The query will take into account clients with an income greater than 1,000,000.

Considerations about View Parameters

View parameters are a special type of fields and they behave differently so take the following into account.

• There are two ways of assigning a value to a view parameter:

  1. In the WHERE condition of the query, with the operator = : WHERE <view parameter> = <value>

     Where <value> is one of these:

     • A literal

     • An expression that only uses literals, not other columns. For example: WHERE <view parameter> = ADDYEAR( LOCALTIMESTAMP, -10)

     • Another view parameter.

  2. In the JOIN condition of the query, also with the operator =, not with other operators. In this case, you can use the name of the column of the other view. For example: <view A> INNER JOIN <view B> ON <view A>.<column 1> = <view B>.<view parameter>

• If the view parameter does not have a default value, it is mandatory for the query to assign a value to the parameter. Otherwise, the execution engine will reject the query.

• If a query has a condition that compares a view parameter with another value, using an operator other than =, this query will use this condition to filter the results, not to assign a value to the parameter. For example, if the query has this condition: WHERE wealthy_client_income_limit > 100000

  This condition uses the operator > so it is not assigning a value to the view parameter “wealthy_client_income_limit”. In this case, if this view parameter does not have a default value, the query will fail. If this parameter has a default value, the query will use the default value of this parameter and it will return the rows that meet the condition “wealthy_client_income_limit > 100000”.

• If a query uses the operator NOT, the query is not assigning a value to the parameter even if it uses the operator =. For example: WHERE NOT (wealthy_client_income_limit = 100000)

  This condition is NOT assigning a value to the parameter.

• If a query uses the operator IN to assign a value to a parameter, the result is a UNION. For example:

  FROM wealthy_client_by_state

  WHERE wealthy_client_income_limit IN (100000, 2000000)

  The result of this query is equivalent to this:

  SELECT ...

  FROM wealthy_client_by_state

  WHERE wealthy_client_income_limit = 100000

  UNION

  SELECT ...

  FROM wealthy_client_by_state

  WHERE wealthy_client_income_limit = 2000000

• Do not assign the result of a subquery to a view parameter because it will not be considered an assignment of value. For example, let us say that you execute this query:

  SELECT *
  FROM wealthy_client_by_state
  WHERE wealthy_client_income_limit = ( SELECT MAX(income_limit) FROM state_limit WHERE state = 'CA' )

  The result of the subquery will not be assigned to the parameter wealthy_client_income_limit, even if you use the operator “=”. The condition will be used to filter the results and if the parameter does not have a default value, the execution engine will reject the query.

When Should You Use View Parameters

There are four main situations where view parameters are useful:

• Enforce a filter condition

• Group By Bypass

• Outer Join Bypass

• Partitioned UNIONs

Virtual DataPort supports modeling data types with a complex structure using the types “register” and “array”. An element of the type array can be considered a sub-view and an array type always has an associated register type that acts like the schema of the sub-view it is modeling.

Sometimes you may need to “flatten” a compound field that contains an array of registers. This is particularly frequent when processing XML and Web service base views. This section describes how to do it.

NOTE: It is also possible to perform the inverse operation of “flatten” to create array elements from sets of tuples or register elements from several fields. This can be done by defining a GROUPBY operation using the NEST or REGISTER functions. See information about the NEST and REGISTER aggregation functions in the VQL Guide.

NOTE: If you need to “flatten” the fields of a register and not an array, do not create a Flatten view. Instead, create a Selection view and in the “Output” tab of the view, right-click on the register and click Project subfields of…

To follow the examples of this guide, before explaining how to create a Flatten view you have to create a Web service base view over the “sales” Web service data source (this data source was created in the section Importing SOAP Web Service Sources):

1. Double-click on the data source “sales”, in the Server Explorer and then, click Create base view.

2. Click Create base view beside the operation getAverageMonthlyRevenue and select Do not stream output.

3. Rename the base view to average_revenue_array.

4. Click Save ().

This view returns an array of elements. Therefore, it is very difficult to combine its data with data from other views. Thus, you will create a “flatten” view with the two attributes of the array: taxId and revenue.

The Flatten view dialog is very similar to the Union view dialog, except that it does not have the Associations tab. Read the section Creating Union Views to learn how the tabs of the Flatten view work.

In the “Model” tab of the view, you can add “View parameters”. The section Parameters of Derived Views explains what are view parameters and the rules you need to follow when using them.

Follow these steps to create the Flatten view of the example:

1. Right-click on the view average_revenue_array and click Flatten on the menu New.

   You can see that the Tool has added this view to the Model tab. You can add a view from another database.

2. In the “Model” tab, you can see a selector next to each array field. You can select as many arrays as you want to flatten. Each time you change the selected array fields, the preview is updated showing the output schema. Following with this example, select the array return. After this, the flatten preview has two attributes: taxId and revenue.

3. In the Output tab, rename the new view to flat_revenue.

4. Click Save to create the view.

Follow these steps to create the Flatten view of the example using the Design Studio:

1. click on the three-dots icon next to average_revenue_array and click Flatten on the menu New.

   You can see that the Tool has added this view to the Model tab. You can add a view from another database.

2. In the “Model” tab, click the flatten icon highlighted in this figure next to the array return. After this, the target view has two attributes: taxId and revenue.

3. In the Output tab, rename the new view to flat_revenue.

4. Click Save to create the view.

An Intersect view executes the intersection operation, which selects the common rows of two or more input views. These views can belong to other databases.

For example, if we have three views with the following contents:

View_A

If we create an intersection view over the views “View_A”, “View_B” and “View_C” and query this new view, the result will be the following:

View_A Intersect View_B Intersect View_C

A Minus view selects all the rows from the first input view and then removes from the result the rows of the second view. If the minus view has a third input view, its rows are also removed from the result. Minus views can have two or more input views. These views can belong to different databases.

For example, if we have three views with the following contents:

View_A

If we create a minus view over the views View_A, View_B and View_C and query this new view, the Server executes the query View_A minus View_B first. The result is the following:

View_A Minus View_B

Then, the Server executes <first intermediate result> minus View_C.

The result of querying the view View A Minus View B Minus View C is the following:

View_A Minus View_B Minus View_C

As you can see, the second row of View_A is removed from the result because View_C also contains this row.

The wizard to create new minus views is very similar to the wizard of union views (see section Creating Union Views)

To create a minus view, click Minus on the File > New menu or right-click the Server Explorer and click Minus on the menu New.

The Tool will open the “Minus view” dialog and it will add the view(s) that are currently selected in the Server Explorer. To add more views, click the Model tab and drag the views from the Server Explorer to this tab.

The “Minus view” dialog has six tabs:

1. Model: tab where you drag the input views of the new minus view and associate the fields of the views. The wizard automatically associates the fields with the same name, but you have to associate the rest.

   It is mandatory to add associations between all the fields of the input views.

   In the View order box, you have to indicate the order in which the minus operation is performed. Note that the view order may affect the results of the view.

   E.g. if the view order is View_a \ View_b \ View_c, the Server performs the minus operation between View_a and View_b and then, between this intermediate result and View_c.

   If the view order is View_c \ View_b \ View_a, the Server performs the minus operation between View_c and View_c and then, between this intermediate result and View_a. Although in both “View orders” the views involved are the same, the result may be different.

   In this tab, you can add “View parameters”. The section Parameters of Derived Views explains what are view parameters and the rules you need to follow when using them.

2. Associations: tab that lists the associations between fields. You can delete them by clicking on .

3. Where Conditions: tab that allows you to add WHERE conditions to the definition of the view.

4. Group By: tab that allows you to add GROUP BY attributes to the view.

5. Output: tab that allows you to configure the output of the view. That is, renaming the view and its fields, add derived attributes, etc.

6. Metadata: tab that allows you to define the folder where the new view will be stored and provide a description for the new view.

The tabs Where Conditions, Group By, Output and Metadata work in the same way as in the Union view dialog (see section Creating Union Views).

Interface views are a special type of views that consist only of a definition of fields and a reference to another view. Interface views decouple the consumer of the view from the implementation of the view. The two main use cases are:

• Contract-based development:

  • The client application and the manager of the project in Denodo agree on a view schema.

  • Afterwards, the team that develops the views in Denodo builds the implementation view for this interface view.

• Data model abstraction:

  • Useful in projects that migrate the underlying data stores to the cloud but you want to make sure the view schema does not change.

When the Server executes a query that involves the interface, the query is delegated to the implementation view. Therefore, the operations that can be executed on an interface view (SELECT, INSERT, UPDATE or DELETE) are the same that can be performed on its implementation view.

The status of an interface view can be:

• Ok: all the fields of the interface view have a valid “implementation expression”. The queries involving this view will work.

• Without implementation: the interface was created without an implementation view or the implementation view has been deleted. The queries involving this view will fail.

• Invalid: the interface has an implementation view, but some of the fields of the interface do not have an implementation expression or it is invalid. This may happen if you create an interface with an implementation view and later, the implementation is modified so the expressions are no longer valid. The queries involving this view will fail.

Consider this regarding the “source type properties” of the fields of an interface view (e.g. Type, Type size…):

• The fields of the interface views may or may not define their own “source type properties”:

  • If in an interface view, a field does not have its own properties, Virtual DataPort propagates these properties from the implementation view to the interface view. Thus, if the implementation view changes, the properties of the fields of the interface view will change as well.

  • If in an interface view, a field defines its own property values, these will not be updated when the implementation view changes.

  For example, let us say we have an interface view “I_1” with a field called “f_1” of type “text”, without an explicit source type, and that in its implementation view, the source type of “f_1” is “VARCHAR” with length 15. If you query “I_1”, the type of “f_1” reported by the Denodo JDBC driver will be “VARCHAR(15)”.

  If you edit the implementation view and change the source type to “NVARCHAR”, the driver will report the type “NVARCHAR”. But if you leave the implementation view with “VARCHAR” and you edit the interface column to “NVARCHAR”, the driver will report “NVARCHAR” and future changes of the property in the implementation view will not be reflected on the interface.

• Because of the above, to change the “source type properties” of a field, do it in the “Edit view” dialog of the implementation view and not in the interface view if you want to always propagate implementation values.

  See more about these properties and why are they useful in the section Viewing the Schema of a Base View.

• If you use a view as a template to build an interface, by dropping the view on the “Definition” tab, all the field properties of the dropped view will be copied to the interface, that is, properties will be set explicitly at interface level.

When dropping a view used by the implementation of an interface or used directly as the implementation of an interface, the Administration Tool will warn that such interface will change to Without implementation status.

To create an interface view, click Interface on the File > New menu, or right-click on the Server Explorer and click Interface on the New menu. The definition of the interface will be populated with the fields of the selected view.

The Interface View wizard has three tabs:

1. Definition: in this tab, you can configure the definition of the view:

   1. Change the name of the interface view.

   2. Manage the name and type of the fields of the interface:

      1. If you want to base the definition of the interface on the schema of an existing view, drag a view to this tab; the existing schema will be replaced with the schema of the dragged view.

      2. To add a new field to the interface, click New field.

         In the box beside the name of the field, you can select the type of the field. By default, this box only contains the basic data types (int, float, text, etc.). If you want to select a compound field, select the Enable compound types check box.

      3. To delete a field from the interface, select its check box and click Remove selected.

      4. If you have deleted a field accidentally and you want to restore it, click Restore fields. The Tool will display the “Restore Fields” dialog where you have to select the fields that you want to add again to the interface view.

2. Implementation: in this tab, you define how the fields of the interface view are mapped to the fields of the implementation view.

   First, you have to add an implementation view by dragging it from the Server Explorer. The implementation view can belong to a different database than the interface view.

   When adding the view, the fields with the same name are automatically linked. You can link the others by dragging a field of the definition to the field you want to link in the implementation view. Alternatively, enter a field name or an expression below, in the “Implementation Expression” column.

   In the figure Creating a new interface view: “Implementation” tab you can see the following:

   • A field can be mapped to a field of the implementation (ttime and taxid) or to a complex expression (summary).

     The implementation expressions must have the same type or compatible with the types of the definition. For example, if the interface has a field F_1 of type double, the implementation expression must have type double or a compatible type: int, long or float. If, for example, the field is type int, at runtime the values of this field are converted to the type of the interface, double.

   • The fields of an interface view that are mapped to a complex expression are not updateable. That is, INSERT, UPDATE or DELETE operations that involve these fields will fail.

   • The implementation view can have more fields than the definition. The extra fields are just ignored. In the figure Creating a new interface view: “Implementation” tab, the field “inc_type” of the implementation view is not mapped to any field of the definition.

   When there is an error in the interface view, this tab is marked with the icon and a tooltip explaining the error. The possible reasons are:

   1. The interface view does not have an implementation view.

   2. Some fields of the definition do not have an “Implementation expression”.

3. Metadata: tab that allows you to define the folder where the interface will be stored and provide a description.