Query

From the “Query” tab, you can query the view.

To display all the data of the view, click image0. You can also add conditions to filter the data obtained from the view.

There are two ways to filter the data obtained from the view:

  • Simple mode: below the “Filter conditions” label, select the name of a field, an operator and enter a value. Click image1 to add another condition. The conditions added this way are AND conditions. I.e. if you add a condition “EmployeeID = 1” and another one, “EmployeeID = 2”, the query sent to Virtual DataPort will be SELECT… FROM … WHERE EmployeeID = 1 AND EmployeeID = 2.

    To build more complex filter conditions use the “Advanced” mode described below.

    As in the “Advanced” mode, you can use date, time and timestamp literals in your queries. In the case of the Simple mode, if the selected field is of date type, an example value with the appropriate format (taking into account the subtype of the field) is shown.

    Advanced query (timestamp value)

    Advanced query (timestamp value)

  • Advanced mode. To enter this mode, click image2. You will see a window like the one Advanced query. In this mode you can enter:

    • Complex conditions to filter the data of the view.

    • A GROUP BY clause [1] with fields or an expression and a HAVING condition. This tab allows you to add GROUP BY fields to the view. To do this, select the “Group By” check box, choose some fields from the “All fields” list and drag them (or click image3) to the “Selected group by fields”. Besides adding fields, you can also define Group By expressions by clicking “Add Expression”. If you add an expression in this section, it is automatically added to the list of fields of the “Output” section with an autogenerated name that you can modify later and add it to the “Selected Output fields”. For instance, suppose an Employee view with a field for the salary and other for the bonus. You could add a group by expression like salary+bonus.

      Note

      Because the syntaxis for the expanded fields is <role_name>/<field>, the / cannot be used as an operator. The function DIV has to be used instead.

    • In the “Output” section, select the fields at the output and in which order they appear. You change the order by drag-and-drop a field to the position you want.

      In this section, a field is marked with the icon image4 when the field is used in the condition mapping of an association with cardinality 1. In this case, you can click this icon to select which fields of the associated view you want to add to the result.

      For example, if you want to see the data of the view “order” and there is an association between “order” and “customer” with cardinality 1 in the end point of the customer view. In this case, you can click on this icon to select the fields of the “customer” view you want to add to the result.

      Click New Field to add a new derived field to the output. In the expression assigned to this field you cannot use expanded fields.

      Click New Aggr. field to add an aggregation field.

    • Order the results by one or more fields.

Note

You can use expanded fields (of associations with cardinality 1) in the following places of the query:

  • Conditions
  • GROUP BY clause
  • HAVING clause

The expanded fields are used in the same way as the fields of the view being queried and will be shown with the syntax <role_name>/<field>.

Advanced query

Advanced query

In these wizards, the expression editor provides the autocomplete functionality and highlights the different elements that make up the expression with different colors (function names, fields, literals, operators, etc.). Besides, the fragments not identified as any of the possible element types, will be highlighted in red.

If the queried view has associations with another views, you can browse them by clicking image6. A new pop-up will be opened with the data at the other side of the associations. It is also possible to expand associations (see section Expanding Associations for more information).

Regarding the results, there are two special cases:

  • Compound fields: click the icon to see the value of its subfields on a pop-up.
  • Binary fields:
    • In views with primary key, there is a link to view the binary.
    • In view without primary key, there is no link and you cannot see its value.
Query results for Department view

Query results for Department view

In each cell of the table, if the content is too long, it will not increase the height of the row, but a tool-tip will be used to show the rest of the content and the height will be kept uniform for all the rows.

The first column of each row contains an icon (image8) that shows in a dialog (Transposed table for first row) the data from that row in a vertical format (it can be helpful when a table has too many columns, because it is difficult to see the results of a complete row).

Transposed table for first row

Transposed table for first row

At the top right corner of the results table, there is a link (image10) to save the query executed to obtain these results. It is associated to the current user and server and will be shown in the saved queries section (see section Saved Queries). This is useful if the executed query is complex and you are going to execute it often or if you want to deploy it to the Virtual DataPort server.

You can also export the results to a file by clicking image11. You can select the following:

  • The output format: CSV, HTML, Excel or Tableau (see A Note about Exporting to Tableau for more information about Tableau).
  • The number of exported results:
    • All results.
    • Only visible results. I.e. the part of the results you are currently seeing.
    • Only the first n results.
Export options

Export options

Note

If the view contains compound fields or expanded associations “to Many” (see section Expanding Associations for more information), those fields are going to be exported as a JSON string.

Note

When exporting to Excel the maximum number of results that will be exported is 1.048.576

Expanding Associations

Expanding an association between two entities means that related tuples/entities are included inline in the results. For instance, suppose a scenario with entities Order and Customer, as shown in Expanding an association (“to One”). Each tuple in Order is associated with a tuple in Customer (thanks to PK-FK relationship). This way, when querying an order you can eagerly retrieve the data from the customer that ordered it. The same way, when querying a customer, you can retrieve all his/her orders.

Expanding an association (“to One”)

Expanding an association (“to One”)

Following with this example, you can have two scenarios:

  1. Associations “to One”: like in the former example, where an order belongs to a customer (and only to that customer). This way, as conceptually shown in Expanding an association (“to One”), you can select the fields from the associated view (Customer) you want to eagerly retrieve when retrieving the data from the order. To do that, you have to select the fields you want to expand (see Selecting fields from an association to be expanded) and they will appear in the result (see Association expanded).
Selecting fields from an association to be expanded

Selecting fields from an association to be expanded

Association expanded

Association expanded

  1. Associations “to Many”: a tuple in a view is related to one or more tuples in other view. It is the case of a customer that may have done several orders. This way, you can traverse the association and see all orders done by a particular customer. To do that, you have to select the fields you want to expand (see Selecting fields to expand the orders of a customer) and an icon to expand the association will be added to the table (see Link to expand the orders for a customer). You can click on the icon image12 and a new pop-up will be displayed with the orders for the desired customer.
Selecting fields to expand the orders of a customer

Selecting fields to expand the orders of a customer

Pop-up with the expanded association

Pop-up with the expanded association

A Note About Exporting to Tableau

Tableau needs some native libraries in order to work. These libraries are not included with the Denodo Platform, so they must be manually downloaded and installed.

In order to export data to Tableau you have to download the Data Extract API, which is available on the Tableau website. Go to https://www.tableau.com/data-extract-api and choose the appropriate version for your platform. The programming language must be Java.

The native libraries and the tdeserver file must be on one of the folders included in the native library path used by the executable of the web container where the Information Self-Service Tool is deployed:

  • On Windows systems, after uncompressing the downloaded file you have to copy the files included in the folder “bin” to a folder included in the native library path. For example you can copy them to C:/WINDOWS/SYSTEM32 or to any folder included in the environment variablePATH.
  • On Linux systems, after uncompressing the downloaded file you have to copy the files included in the folders “bin” and “lib/dataextract” to a folder included in the native library path. For example, you can copy them to a standard library folder of your SO (e.g. /lib or /usr/lib) or use the environment variable LD_LIBRARY_PATH to specify another folder.

Footnotes

[1]It is possible to specify expanded fields in a GROUP BY clause (but they cannot be used in the HAVING condition) when the cardinality of the other side of the association is 1.