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:
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.
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.
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:
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 ouputin 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
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.
Further information about using related resources 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 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.
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 this into account:
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.
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 with VDP
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.
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/adminin the servers configuration of the Data Catalog.
Web services configured with one of the following authentication methods are not currently supported:
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 Web Service URL button in the toolbar. A notification with the URL will appear. Note that if the web service is not deployed, the Web Service URL button will be disabled.
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.
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.
Find more details on how to build queries in the Query of Views section.