Query of Views

From the Query tab you can build a query on the view to retrieve data from it. With the help of a wizard, you can customize several aspects of the query: select the subset of fields to retrieve, filter the tuples using conditions, sort the results, and more. When the query is ready, execute it and check the results.

In this section, we will see:

Note

The Query tab is only available if you have privileges to execute the view.

Building a Query

The Query tab is divided into two sections: Definition and Results. In the Definition section there is a wizard that will help you building queries in a graphical way.

Wizard to define a query on a view graphically

Wizard to define a query on a view graphically

A query consists of three parts, which are represented in the following panels of the wizard:

  • Output columns: The list of fields and expressions that will conform the schema of the query results.

  • Filters: The list of conditions that a tuple has to meet to appear in the results.

  • Order by: The list of sorts applied to the query results.

On the left side of the wizard you have two panels, Fields and Relationship fields, which contain all the available fields. To build a query, just drag-and-drop fields from these two panels to the ones that define the query, Output columns, Filters and Order by. It is that simple.

Let us see each panel in more detail.

Fields

The Fields panel contains the list of all the fields in the schema of the view in Virtual DataPort.

Note

If there are some fields you are not allowed to use in a query according to your column privileges, they will not be listed here.

In addition, it has an extra field called NUMBER_OF_RECORDS, which represents the number of tuples in the query results. It is equivalent to count(*) in VQL.

Fields of the view available to use in the query

Fields of the view available to use in the query

You have several ways of using these fields to build a query:

  • Drag-and-drop a field in the list to Output columns, Filters or Order by.

  • Select some fields in the list and drag-and-drop them to Output columns or Order by.

  • Select some fields in the list, click the menu-title icon in the title and select one of the options in the menu: Add to output columns or Add to order by.

Relationship Fields

The Relationship fields panel contains the list of related views, that is, the views with which the current view has a to-one association. Notice that related views are labeled as <view name> (<role name>).

Note

A related view may appear more than once in the list, since the current view can have several to-one associations with the same view. You can use the role name to differentiate each association.

Each related view has a expand-view icon next to its name. Click it to unfold the fields of the related view in Virtual DataPort. Henceforth, we will call them the expanded fields.

Note

If there are some expanded fields you are not allowed to use in a query according to your column privileges in the related view, they will not be listed here.

Expanded fields of the view available to use in the query

Expanded fields of the view available to use in the query

You can use an expanded field anywhere you can use a field from the current view:

  • Drag-and-drop an expanded field in the list to Output columns, Filters or Order by.

  • Drag-and-drop a related view in the list to Output columns or Order by. All its expanded fields will be added.

  • Select some expanded fields in the list and drag-and-drop them to Output columns or Order by.

  • Select some expanded fields in the list, click the menu-title icon in the title and select one of the options in the menu: Add to output columns or Add to order by.

Notice that in the Output columns, Filters or Order by panels, the expanded fields are labeled as <role name>/<field name>.

See also

Further information about using related views in queries can be found below in the Taking Advantage of Associations section.

Output Columns

The Output columns panel defines the schema of the query results, that is, which fields and expressions will be part of it and in which order they will appear.

The Output columns panel defines the schema of the query results

The Output columns panel defines the schema of the query results

You have the following options to build the output schema of the query.

Add a Field

You can add fields from the current view or from the related views to the output schema. For each field added, a column will be created in the query results with its corresponding data.

We have already seen how to add fields and expanded fields using the Fields and Relationship fields panels. There are other ways:

  • Drag-and-drop a field from the Order by panel.

  • Select some fields in the Order by panel and drag-and-drop them all.

  • Click the menu-title icon in the title of the Output columns panel and select the Add option. A dialog will appear to define a new output field. Select one field on the list. If you want to edit the name of the column in the query results, click the edit icon.

    Dialog to add a field to the Output columns panel

    Dialog to add a field to the Output columns panel

Set all Fields

If you want that the output schema of the query reflects the schema of the view, you can add all its fields to the Output columns as we have already seen. However, this approach has a problem. Let us imagine that you build a query in this way and save it for later. In the meantime, the schema of the view changes because someone added a new field. The query you saved will no longer reflect the schema of the view.

There is an alternative way of building the query that solves this problem:

  1. Click the menu-title icon in the title of the Output columns panel.

  2. Check the All fields option.

If you save a query like this and the schema of the view changes in the future, your query will automatically change too. This way, the output schema of your query will always reflect the schema of the view.

Note

When you check the All fields option, the Output columns will be locked. If you want to modify your output schema, you will need to uncheck the All fields option first.

Add a Derived Field

You can create new fields by transforming or combining the available fields in the Fields and Relationship fields panels. For instance, from the firstname and lastname fields you can build a derived field with the value concat(firstname, ' ', lastname).

To build a derived field, you have to:

  1. Click the menu-title icon in the title of the Output columns panel.

  2. Select the Add option.

  3. Click the edit icon and give the derived field a new name.

  4. Write the expression that describes how to calculate the derived field.

Dialog to add a derived field to the Output columns panel

Dialog to add a derived field to the Output columns panel

The editor counts with the following features to help you write an expression:

  • Highlighting. Each component of an expression is highlighted with a different color: functions, operators, literals, fields, etc.

  • Autocomplete. Press Ctrl + Space and a menu with suggestions will appear.

  • Validation. Every time you type in the editor, the expression is validated. If the expression is correct, you will see a valid icon. If it is incorrect, you will see a invalid icon. Put the mouse over the icon and a tooltip will explain why it is invalid.

Note

Because the syntax for the expanded fields is <role_name>/<field>, you cannot use the / as an operator. Use the function DIV instead.

See also

A comprehensive list with all the functions you can use to build a derived field can be found in the Virtual DataPort VQL Guide.

Add an Aggregation Field

A derived field usually works by transforming the data on a single tuple. Its expression takes the data of the tuple, apply some functions on them, and produces a new value. However, there is special case of functions that do not follow this pattern: aggregation functions. An aggregation function takes data from several tuples and produces a single value by combining them all. Examples of aggregation functions are MAX, SUM or AVG. A derived field that uses an aggregation function is called an aggregation field.

To build an aggregation field you have the following options:

  • Click the menu-item icon of a field in Output columns and select the option Apply aggregation function. A menu will appear with all the aggregation functions you can apply to the field. Select one of them.

    A menu with all the aggregation functions you can apply on a field

    A menu with all the aggregation functions you can apply on a field

  • Create a derived field as explained before and write an expression that uses an aggregation function.

  • Add the NUMBER_OF_RECORDS field from the Fields panel, since it is equivalent to the COUNT aggregation function.

Note

Aggregation fields are decorated with the aggregation icon in the Output columns panel, so you can differentiate them from non-aggregation fields.

Once the Output columns contains an aggregation field, the semantic of the query changes as follows:

  1. The tuples of the view are partitioned in groups. For each distinct value of the non-aggregation fields, a group of tuples is generated with all the tuples that contain that value. In case all the fields in Output columns are aggregation fields, then one group is generated with all the tuples of the view.

  2. For each group, aggregation fields are calculated by applying the corresponding aggregation function on the tuples of the group.

  3. There will be one tuple in the query results for each group. The tuple will consist of:

    • The values of the non-aggregation fields that generated the group.

    • The values of the aggregation fields calculated for the group.

Results for a query with aggregation fields in Output columns

For each distinct value in the non-aggregation fields, the results contain a tuple with all the aggregation fields

See also

A comprehensive list with all the aggregation functions you can use to build an aggregation field can be found in the Virtual DataPort VQL Guide.

Edit a Field

To edit a field in Output columns you need to:

  1. Click the menu-item icon next to the field you want to edit.

  2. Select the Edit option.

  3. Update the name of the field or its expression.

Dialog to edit a field in the Output columns panel

Dialog to edit a field in the Output columns panel

Note

All fields in Output columns are editable, except for NUMBER_OF_RECORDS.

Remove a Field

If you want to remove a field from the Output columns panel, proceed as follows:

  1. Click the menu-item icon next to the field you want to remove.

  2. Select the Remove option.

If you want to remove several fields, you have the following alternative:

  1. Select those fields in Output columns you want to remove.

  2. Click the menu-title icon in the title.

  3. Select the Remove option.

Reorder a Field

The order of the fields in the Output columns panel determines how they will appear in the query results. To reorder a field in the output schema, you have to:

  1. Click the menu-item icon next to the field you want to reorder.

  2. Select the Move up or Move down option.

  3. Repeat this process until the field is in the correct place.

If you want to reorder several fields, you have the following alternative:

  1. Select those fields in Output columns you want to reorder.

  2. Click the menu-title icon in the title.

  3. Select the Move up or Move down option.

  4. Repeat this process until the fields are in the correct place.

Filters

In the Filters panel you can define a list of conditions on the tuples of the view. For a tuple to appear in the query results, it must meet all conditions.

If the view has mandatory fields, a filter for each one will be automatically added to the Filters panel. They appear in red as a reminder to give them a value. Note that in case you delete a filter on a mandatory field, you will need to create another one before executing the query, since the field must have a value.

The Filters panel contains the conditions a tuple has to meet to appear in the results

The Filters panel contains the conditions a tuple has to meet to appear in the results

You have the following options to build the filters of the query.

Add a Quick Filter

A quick filter is a condition that consists of:

  1. A field or an expanded field.

  2. An operator

  3. The values required by the operator, which can range from zero to a list of values.

There are several ways of adding a quick filter:

  • Drag-and-drop a field or expanded field from Fields, Relationship fields, Output columns or Order by.

  • Click the menu-title icon in the title of the Filters panel and select the Add Quick Expression option.

A dialog will appear to define a filter on the selected field.

Dialog to add a quick filter to the Filters panel

Dialog to add a quick filter to the Filters panel

Take into account the following considerations:

  • The Field name selector contains the list of fields and expanded fields labeled as <field name> (field type).

  • The Operator selector shows the list of operators supported by the selected field according to the view configuration.

  • The dialog will ask for as many values as the operator requires. Specifically, the in and not in operators require a list of values as parameters. To add a value to the list, write an expression in the editor and click the add-value icon. To remove a value, click the remove icon.

  • The value editor supports all the features we have already seen: highlighting, autocomplete and validation. In addition, if the selected field is of type datetime or interval, the editor will suggest a literal value with the appropriate format.

    See also

    Further information on building datetime and interval values is available in the Virtual DataPort VQL Guide.

Add a Complex Filter

In case a quick filter is too restricted for your needs, you can create a complex filter by expressing your condition in VQL. For instance, it may depend on derived fields, use logical operators to combine other conditions, and more.

To add a complex filter you have the following ways:

  • Drag-and-drop a derived field from Output columns or Order by.

  • Click the menu-title icon in the title of the Filters panel and select the Add Expression option.

A dialog will appear to define a complex filter. Just type the condition you need using the rules of VQL.

Dialog to add a complex filter to the Filters panel

Dialog to add a complex filter to the Filters panel

See also

Further information on the capabilities of VQL to write conditions and the syntax a condition must follow can be found in the Virtual DataPort VQL Guide.

Edit a Filter

To edit a condition in the Filters panel you need to:

  1. Click the menu-item icon next to the filter you want to edit.

  2. There are two options in the menu to edit the condition:

    • Quick Edit. This option opens a dialog to edit the components of a quick filter: field, operator and values. It is only available for quick filters.

    • Edit Expression. This option opens an editor where you can update your condition using VQL. It is available both for quick and complex filters.

  3. Select the option you want and update the filter.

Remove a Filter

To remove a filter you can follow the same steps explained before for output columns.

Reorder a Filter

To reorder a filter you can follow the same steps explained before for output columns.

Order By

In the Order by panel you can define a list of fields that will determine how the tuples in the query results are arranged. By default it is empty, so the order in the results will be undefined. Add several fields to the Order by panel and the tuples will be sorted as follows:

  1. The tuples will be sorted by the values in the first field.

  2. For those tuples with the same value in the first field, the value of the second field in the Order by panel will be used to sort the tuples.

  3. This process continues for all the fields in the Order by panel.

The Order by panel contains a list of fields that determine how to sort the query results

The Order by panel contains a list of fields that determine how to sort the query results

You have the following options to define the order of the query results.

Add a Sort

You can sort your query results by:

  • A field from the Fields panel.

  • An expanded field from the Relationship fields panel.

  • A derived field from the Output columns panel.

  • An aggregation field from the Output columns panel.

To add a sort for your query, you have the following options:

  • Drag-and-drop a field from the Fields, Relationship fields or Output columns panels.

  • Click the menu-title icon in the title of the Order by panel and select the Add option. A dialog will appear with all the fields available in the Fields, Relationship fields or Output columns panels. Select one of them.

Change the Order

Next to a field in the Order by panel there is an icon that indicates which order will be applied when sorting by the field:

  • ascending Ascending order.

  • descending Descending order.

By default, when you add a field to Order by, the tuples are sorted in ascending order. However, you can change its order by one of the following ways:

  • Click the icon next to the field in the Order by panel. It is a toggle button: it will alternate from ascending to descending, and vice versa.

  • Click the menu-item icon of the field in the Order by panel. According to your current order, one of the following options will be available: Change to DESC or Change to ASC. Select it.

Remove a Sort

To remove a sort you can follow the same steps explained before for output columns.

Reorder a sort

The position of the fields in the Order by panel determines the order in which the sorts will be applied. First, the tuples are sorted by the first field, then by the second field, an so on.

To change the order in which the sorts are applied, you can reorder the fields in the Order by panel as explained before for output columns.


Once you have defined your query, you can save it for later, which would be useful in the following scenarios:

  • The query is not finished and you want to continue later.

  • The query is too complex and you will need to execute it often.

  • You want to share the query with other person in your company.

  • You want to deploy the query as a view in a Virtual DataPort server.

To save the query, click the Save button in the toolbar. A dialog will appear where you have to give the query a name and, optionally, a description. You will find your saved query in the My Queries section.

Dialog to save a query for later

Dialog to save a query for later

Note

Take into account that:

  • No other user will have access to your saved query, unless you share it.

  • Your saved queries are associated to the Virtual DataPort server you are connected to.

See also

For further information on what actions you can perform with saved queries, check the My Queries section.

Executing a Query

When the query is ready, you just need to click the Execute button in the toolbar and the query will be executed in Virtual DataPort. Internally, this process consists of several steps:

  1. Data Catalog needs to convert the query you have graphically composed into a VQL sentence that Virtual DataPort understands. Click the VQL button in the toolbar and a popup will appear with the VQL sentence that will be sent to Virtual DataPort.

    Popup with the VQL sentence generated from your query

    Popup with the VQL sentence generated from your query

  2. The VQL sentence is sent to Virtual DataPort. The Data Catalog displays an animation to show that it is waiting for the query results. If the query takes too long, you can stop it and maybe modify its definition. Click the Cancel button in the toolbar while the animation is present and the query will stop.

    Use the Cancel button to stop the query

    Use the Cancel button to stop the query

  3. When the response arrives at the Data Catalog, it automatically loads the Results section and shows a table with the query results.

Table with the results for the query

Table with the results for the query

Take into account the following considerations on the query results:

  • The query results are not all loaded at once. The Data Catalog asks for the first 100 tuples and, as soon as they arrive, it shows the table with them. Scroll down the table and before you reach the end, the next 100 tuples will be loaded. You can check above the table how many rows have already been loaded. This number increases as you scroll down until there are no more results.

  • If a tuple has no value for a field, it shows the <null> value.

  • A binary value is shown as [BINARY DATA] and its length in bytes. It cannot be downloaded.

  • Too long values are truncated. Put the mouse over the cell and its actual value will be shown in a tooltip.

  • Virtual DataPort supports two compound data types for representing hierarchical data:

    • Register: A tuple with several fields of different type.

    • Array: A list of registers with the same inner structure.

    Compound values are shown in the query results with the compound icon. Click it and a popup will appear with its actual value.

    Popup with the value for a compound data type

    Popup with the value for a compound data type

  • At the beginning of each row there is a full-row icon. Click it and a popup will appear with all the data from that row in a vertical format. It can be helpful when the table has too many columns, since it may be difficult to see the values for all fields at a glance.

    Popup with all the values from a row in the query results

    Popup with all the values from a row in the query results

In addition to see the query results in the Data Catalog, you can export them to a file:

  1. Click the Export button in the toolbar. The dialog to export the query results will appear.

    Dialog to export the query results to a file

    Dialog to export the query results to a file

  2. Select the export format:

  3. Select the number of rows to export:

    • All results

    • Only visible results. Only the results that have already been loaded will be included in the export file.

    • Only the first n. Type a number to limit the number of results to export.

  4. Optionally, if you are exporting to CSV, HTML or Excel, you can include a header with the column names by checking the Export header option. It is not available for Tableau formats because .tde and .hyper files always include this header.

Note

When exporting the query results, you need to consider that:

  • Compound values in the query results will be exported as text with JSON format.

  • If you have selected the Excel format, the maximum number of rows you can export is 1,048,576.

  • An administrator of the Data Catalog may have disabled some export formats or limit the number of rows to export.

  • An administrator of the Data Catalog can personalize the field delimiter for the CSV format.

Taking Advantage of Associations

Two views have a relationship with each other when one tuple from a view is related with zero, one or more tuples in the other view, and vice versa. For instance, let us imagine the views customers and orders. One order is related to one and only one customer, whereas a customer may be related to zero, one or many orders. In Virtual DataPort this kind of relationships are modeled by associations.

Depending on the cardinality of the relationship, a view can maintain two types of associations with other views:

  • To-one association. A tuple in the view is related at most with one tuple in the other view.

  • To-many association. A tuple in the view can be related with more than one tuple in the other view.

Let us see how to exploit these associations when querying a view.

See also

In the Associations of Views section you can find information on how to explore the relationships of a view.

Expanding To-One Associations

To-one associations play an important role when building a query because they can be expanded. For a view, expanding an association means that each tuple can include, in addition to its data, the data from the related tuple in the associated view. In the previous example, the orders view has a to-one association with the customers view. You can expand this association and build a query that retrieves, for each order, the data from the customer who ordered it.

But expanding an association is not limited to eagerly retrieve data from a related view. You can use the expanded fields anywhere you use a field from the view: aggregation fields, filters, sorts, etc. This way, the schema of a view is enlarged with the fields of those views with which it maintains a to-one association.

In the Definition section of the wizard, the views related by a to-one association and their fields appear in the Relationship fields panel. To expand the association just drag-and-drop an expanded field to any other panel in the wizard as if it was another field in the view.

Expanding fields from a to-one association

Expanding fields from a to-one association