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:
How to create a query.
How to take advantage of associations.
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.
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.
The Fields panel contains the list of all the fields in the schema of the view in Virtual DataPort.
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
represents the number of tuples in the query results. It is equivalent to
count(*) in VQL.
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 icon in the title and select one of the options in the menu: Add to output columns or Add to order by.
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>).
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 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.
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.
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 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>.
Further information about using related views in queries can be found below in the Taking Advantage of Associations section.
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.
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 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 icon.
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:
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.
When you check the All fields option, the Output columns will be locked. To modify your output schema, clear All fields 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
lastname fields you can build a derived field with the
concat(firstname, ' ', lastname).
To build a derived field, you have to:
Select the Add option.
Write the expression that describes how to calculate the derived field.
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.
Spaceand 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 icon. If it is incorrect, you will see a icon. Put the mouse over the icon and a tooltip will explain why it is invalid.
Because the syntax for the expanded fields is
you cannot use the
/ as an operator. Use the function
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
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 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.
Create a derived field as explained before and write an expression that uses an aggregation function.
NUMBER_OF_RECORDSfield from the Fields panel, since it is equivalent to the
Aggregation fields are decorated with the 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:
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.
For each group, aggregation fields are calculated by applying the corresponding aggregation function on the tuples of the group.
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.
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:
Select the Edit option.
Update the name of the field or its expression.
All fields in Output columns are editable, except for
Remove a Field¶
If you want to remove a field from the Output columns panel, proceed as follows:
If you want to remove several fields, you have the following alternative:
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:
Select the Move up or Move down option.
Repeat this process until the field is in the correct place.
If you want to reorder several fields, you can proceed as follows:
Select those fields in Output columns you want to reorder.
Select the Move up or Move down option.
Repeat this process until the fields are in the correct place.
As an alternative, you can drag-and-drop a field to its final place in the panel or select those fields you want to reorder and drag-and-drop them all.
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.
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:
A field or an expanded field.
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 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.
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
not inoperators require a list of values as parameters. To add a value to the list, write an expression in the editor and click the icon. To remove a value, click the 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.
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 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.
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:
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.
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.
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:
The tuples will be sorted by the values in the first field.
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.
This process continues for all the fields in the Order by panel.
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:
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:
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:
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.
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.
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:
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.
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.
When the response arrives at the Data Catalog, it automatically loads the Results section and shows a table with the query results.
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
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 icon. Click it and a popup will appear with its actual value.
At the beginning of each row there is a 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.
In addition to see the query results in the Data Catalog, you can export them to a file:
Click the Export button in the toolbar. The dialog to export the query results will appear.
Select the export format:
Tableau (.tde). For this option to be available, you need an administrator of the Data Catalog to install the Tableau Data Extract API.
Tableau (.hyper). For this option to be available, you need an administrator of the Data Catalog to install the Extract API 2.0.
Select the number of rows to export:
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.
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
.hyperfiles always include this header.
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.
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.