Creating Union Views¶

A union view merges the tuples from various views into a single view. These views can belong to different databases.

In standard relational algebra, all the relations or tables must have the same schema (same attributes with the same type). However, Virtual DataPort uses an extended union, 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.

To create a union view, right-click on the Server Explorer and click New > Union.

The Tool will open the “Union view” dialog and it will add the view that is selected in the Server Explorer. To add views to the union view, drag them from the Server Explorer to this dialog. You can drag views from different databases.

In our example, we will drag the base views phone_inc and internet_inc (we explain how to create these views in the section Creating Base Views from a JDBC Data Source).

The “Union view” dialog has six tabs:

1. Model: tab where you have to drag the views that will form the union and establish the associations between the fields of the views. The fields linked by an association are considered a sole attribute in the output schema of the view.

   By default, the Tool will create associations between the attributes of the same name and type. You can also add more associations by graphically linking an attribute of one view with other attribute of the other view. In views with many fields, you can use the Find option to locate the desired field in a view (right-click on the view).

   In order to associate two fields, they must have the same type or compatible. For example, you can associate a float field with a double, an int with a long, etc.

   If you want to delete a view from the union, click the button (on the top-right side of the view).

   In this tab, you can add “View parameters” by clicking on , at the top of the dialog. See more about this in the section Parameters of Derived Views.

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. There are two modes to add WHERE conditions:

   1. Simple condition mode: use it to create a list of conditions combined with the AND operator, where each condition has an attribute of the view as left operand.

      To add a new condition, click on . For each condition you have to indicate:

      1. An attribute of the view.

      2. The operator of the condition: =, <>, contains, etc.

         The condition operators are described in detail in the section Comparison Operators of the VQL Guide.

      3. The right operand. It can be either any expression using constants, view attributes and functions (see section Functions for Conditions and Derived Attributes of the VQL Guide).

         Click on the menu Help > Functions list to display the list of existing functions.

         If you want to enter a constant and depending on the type of the selected field, you can open the “Value editor” of the condition by clicking on :

         1. If the type of the field is text or xml, it will open the “Value editor”.

            This editor will surround the value with single quotes ('). If the value contains single quotes, it will escape them when you click Ok to close the editor.

         2. If the type of the selected field is compound (register or array), it will open the “Compound value editor” (see section Creating Conditions with the Compound Values Editor)

   2. Specify Where condition mode: used to write any condition. This is required, for instance, when you need to use the OR and NOT operators, or when the left operand of the condition is an expression. Press Ctrl+Space to display the auto-complete list that will help you form syntactically valid expressions.

4. Group By: tab that allows you to add GROUP BY fields to the view. To do this, select the Use group by check box, select a field of one of the views in the All fields list and click Add >>.

   To select two or more fields, hold Ctrl or Shift and click on each field.

   If the input views have many fields, press Ctrl+F to display a search box that will help you find the field you are looking for.

   Besides adding fields, you can define Group By expressions by clicking Add expression.

   To change the order of a field in the Group By clause of the view, select a field in the Selected group by fields and expressions list and click on the buttons and .

   After adding one or more group by fields or expressions, you can define a HAVING condition in the Having condition box.

   You can define aggregation expressions by clicking on the New aggr. field button of the Output tab.

   Note that if you add Group By fields, the view can only have Group By fields and aggregation expressions (see section Group BY Clause of the VQL Guide).

5. Output: in this tab you configure the output of the view:

   1. Change the name of the view.

   2. Change the fields’ name. If the view has many fields, press Ctrl+F to display a search box that will help you find the field you are looking for.

   3. Reorder the projected fields. To do this, select the check box beside the type of the field you want to reorder and click or to move the field up or down.

   4. Project the subfields of a register field by right-clicking it and then, on Project subfields of…

      Virtual DataPort models data types with a complex structure using the types register (indicated with the icon ) and array (indicated with the icon ).

      In this tab, you can project the inner fields of register fields. To do this, right-click on the field and then, on Project subfields of… Instead of projecting all the fields of a register, you can project only some of them by right-clicking on each field and then, on Project subfield…

      To project the content of an array field, create a Flatten view (see section Creating Flatten Views).

   5. Sort the fields alphabetically: right-click on the table of fields and click Sort fields alphabetically.

   6. Add derived attributes: click New field to open the expressions editor.

      You can edit these attributes later by clicking on , on the “Field Type” column.

      In this editor, you have to specify the name of the new field and an expression. This expression can have constants, attributes of the projected views and functions (see the section “Functions for Conditions and Derived Attributes” of the VQL Guide).

      Click on the menu Help > Functions list to display the list of existing functions.

      Press Ctrl+Space to display the auto-complete list that will help you form syntactically valid expressions.

      After defining a derived field, you can set its “Source type properties” by clicking on , in the cell that displays the field’s type. The section Viewing the Schema of a Base View explains what these properties are used for.

   7. If you have selected the Use group by check box in the Group by tab, you can add aggregation expressions to the view by clicking New. aggr. expression.

   8. Delete fields: select the check boxes of the fields you want to delete and click Remove Selected.

      Note

      To select several fields at once, select the first field, press Shift and then, select the last field. Also, you can right-click on each field and click Drop.

   9. If you have deleted one or more fields from the output of the view and you want to restore them, 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 output.

      If you want to reset the entire output schema of the view, click Restore > Schema. This will remove the Group by definition of the view and add to the “Output” all the fields from the source views.

   10. Remove the duplicate rows from the output: select the DISTINCT clause check box.

   11. Order the output by one or more fields: select a field in the ORDER BY fields drop-down and click on . After adding an ORDER BY field, select if you want to order the output in ascending (ASC) or descending (DESC) order.

   12. Define the primary key of the view: select the check box beside the type of the fields that form the primary key and click on Set selected as PK. In addition, you can right-click on the field and click Set field as PK.

       See more about the primary key of a view in the section Primary Keys of Views.

   13. Add a description to the field by clicking on , on the column “Description”. You can obtain the description of the fields from:

       • The JDBC interface.

       • The ODBC interface.

       • The Denodo stored procedure CATALOG_VDP_METADATA_VIEWS. The section CATALOG_VDP_METADATA_VIEWS of the VQL Guide describes this procedure.

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

7. Click Save ().

In our example:

• Click on the menu File > New > Union.

• Drag the views phone_inc and internet_inc to the “Model” tab and add the association internet_inc.summary = phone_inc.description. See Creating the union view of phone_inc and internet_inc (“Model” tab).

• In the “Output” tab (see Creating the union view of phone_inc and internet_inc (“Output” tab)):

  • Rename the view to incidents.

  • Remove the fields pinc_id, iinc_id, specific_field1, specific_field2 and specific_field3. To do this, select these fields and click on Remove selected.

  • Define the primary key of the view with the field taxid. To do this, select the check box beside the type of taxid and click on Set selected as PK.

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

Creating the union view of phone_inc and internet_inc (“Model” tab)¶

Creating the union view of phone_inc and internet_inc (“Output” tab)¶