Query of Web Services

From the Query tab you can build a request on the web service to retrieve data from it. The features of the wizard are basically the same we have already explained for querying a view. Therefore, in this section we will focus on the particularities of querying a web service when:

Note

The Query tab is only available for REST web services. Currently, there is no support for querying a SOAP web service.

Building a Query

A REST web service can contain several resources. Each resource publishes the data of a particular view. Therefore, the first step to build a request on a web service is to select the resource you want to query. The Resource selector in the toolbar shows all its available resources. Select one and the wizard in the Definition section will update accordingly.

Wizard to define a query on a web service graphically

Wizard to define a query on a web service graphically

The wizard is composed by the 5 panels you already know: Fields, Relationship fields, Output columns, Filters and Order by. Let us see each panel in more detail.

Fields

The Fields panel contains the list of all the fields in the schema of the current resource in Virtual DataPort. Each field is decorated with a set of icons to show its status:

  • mandatory The field is mandatory and the request must provide a value for it. As a requirement to execute the query, a filter on this field must exist.

  • input The field can be used as input of the request, which means that the field can participate on a filter. A field tagged with No searchable in the Schema tab will not show this icon.

  • output The field can be used as output of the request, which means that the field can appear on the Output columns and Order by panels. A field tagged with Do not ouput in the Schema tab will not show this icon.

As we will see, a query on a web service does not support aggregation fields. Therefore, the Fields panel does not include the extra field NUMBER_OF_RECORDS.

Relationship Fields

The Relationship fields panel shows the list of related resources, that is, the views published in the web service with which the current resource has a to-one association. Notice that related resources are labeled as <resource name> (<role name>). Unfold a related resource and access its expanded fields. They are decorated to show its status, in the same way as the elements in the Fields panel.

Unlike when you query a view, the expanded fields cannot be used anywhere you use a field from the current resource. You should consider the following restrictions:

  • If a related resource has a mandatory field, you cannot expand any of its fields.

  • Expanded fields cannot participate in a filter.

  • Only the expanded fields decorated as output fields can be used in the Output columns and Order by panels.

See also

Further information about using related resources 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 will be part of it and in which order they will appear.

The options you have to build the output schema of the request are more restricted than with views:

  • You can only add fields and expanded fields if they are decorated as output fields.

  • You can set all fields as the output schema, like with views.

  • Derived fields and aggregation fields are not supported.

  • You can remove and reorder fields, but you cannot edit them.

Filters

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

Take into account the following considerations on filters:

  • If a field is decorated as mandatory, you must provide a filter on that field to execute the request.

  • Only the fields decorated as input fields can participate in a filter.

  • Expanded fields cannot be used to create a filter.

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 accordingly.

Take into account that you can only use fields and expanded fields in the Order by panel if they are decorated as output fields.

Executing a Query

When the query is ready, you just need to click the Execute button in the toolbar to launch the request or the Export button to save the query results in the format you want.

The web service may require authentication to protect against unauthorized access. If that is the case, take into account that:

  • You will need to provide valid credentials if the web service is configured with one of the following authentication methods:

    • HTTP Basic

    • HTTP Basic with VDP

    • HTTP Digest

  • If the web service is configured with the authentication method HTTP SPNEGO (Kerberos) and you logged in Data Catalog using Kerberos as well, your credentials will be reused to authenticate the web service.

  • If the web service is configured with the authentication method HTTP SPNEGO (Kerberos) and you logged in Data Catalog using an authentication method different from Kerberos, you will need to provide valid credentials to authenticate the web service.

    Important

    The URL to query the web service is built from the URL of the Virtual DataPort server you are connected to. In case a web service is configured with the authentication method HTTP SPNEGO (Kerberos) it is important that the URL of the Virtual DataPort server uses the same host configured for its server principal name. For example, if the Virtual DataPort server is configured with the server principal name HTTP/denodo-prod.subnet.acme.com@ACME.COM, this server should be defined with the URL //denodo-prod.subnet.acme.com:9999/admin in the servers configuration of the Data Catalog.

  • Web services configured with one of the following authentication methods are not currently supported:

    • OAuth 2.0

    • SAML 2.0

Popup to provide the credentials required to authenticate the web service

Popup to provide the credentials required to authenticate the web service

In case you have provided credentials to query the web service, the toolbar will show the Authentication button. Click it if you want to modify the credentials.

There are other differences with querying a view that you should consider:

  • If the web service is not deployed, the query will fail with the message: The resource is not available.

  • Data Catalog needs to convert the query into a URL that points to the web service. To check the URL, click the WS URL button in the toolbar. A notification with the URL will appear. Note that if the web service is not deployed, the WS URL button will be disabled.

    Notification with the URL generated from your query

    Click the copy icon in the title of the notification to copy the URL to the clipboard

  • A binary value in the query results is displayed with the binary icon. Click it to download its value as a file.

    Note

    For a binary value to be downloadable, the underlying view of the current resource must have a primary key defined.

Taking Advantage of Associations

To create a REST web service in Virtual DataPort, you add the views you want to publish. If there are associations between these views, the web service will automatically add them.

A REST web services with several resources and their associations

A REST web services with several resources and their associations

See also

Further information on creating a REST web service with several views and their associations can be found in the Virtual DataPort Administration Guide.

As we have already seen with views, your queries can take advantage of the associations published within the web service:

  • The resources related by a to-one associations with the current resource are listed in the Relationship fields panel. You can expand their output fields. Just add them to the Output columns or Order by panels.

  • You can navigate through the associations when inspecting the query results. For each related resource there is a link in the results. Click the link of a specific tuple and access the tuples in other resources that are related to it.


See also

Find more details on how to build queries in the Query of Views section.