DATA SERVICES

Data Services layer is an abstraction layer which can provide data to multiple consumers and is usually deployed at an enterprise-wide scale. Data Virtualization software has the ability to easily create new Data Services using the views in the virtual layer. In many projects, it's necessary to have a common, consistent and scalable data services infrastructure.

What are the most common issues providing these services?

  • Complex architectures where the integration of different systems becomes a difficult and tedious task.
  • Rigid infrastructure that hinders the launching of new services quickly to the demands of its customers.
  • Overdependence on the IT department.

So, what are the benefits of having a Data Services layer?

  • A single data layer for accessing all the information for every business application.
  • Many problems solved by using only the Data Services layer only.
  • Access to any source as if they were homogeneous.
  • Simple plugins to ESB software.

Let's suppose the following scenario:

Another department has an application that connects to the company's CRM using a specific Web service (developed only for this purpose), but now they want to use the Data Virtualization infrastructure to also get the unified customer data (to avoid connecting to the physical source systems). They have to migrate their application to point to another Web service with similar operations as the previous one but with more output fields.

Two actions have to be done:

  • On the Denodo side, a new Web service has to be deployed.
  • On the consuming application side, they have to point to the new service.

Along withat that setup, for the development team, it would be useful to have a contract to make the changes in the application at the same time as the Denodo Data Virtualization Layer (without this contract they have to redeploy after the new Web service is ready). In this example, the contract will be the WSDL of a Web service without implementation in Denodo and, in the next section, you will learn how to create an interface element that are very useful for these kind of projects.

With this situation in mind, in this tutorial we will use Denodo to learn how to:

  • Create Interfaces
  • Create SOAP Services
  • Create REST Services
  • Publish/Deploy Created Services
  • Invoke Services from External Systems
  • Denodo views via OData and GraphQL

In Denodo, an interface is a special type of view that consist only of a definition of fields and its data types. The most common usage is in a top-down design where you first define the fields and later associate the implementation(data) of the interface.

How to Create a New Interface

Follow these steps to create an interface view:

  1. Launch the Web Design Studio and create a new folder under the tutorial database called "2 - data services".
  2. Then, click on button of new folder and select New > Interface.

  1. This will open the Interface view dialog. This dialog has three tabs, but we are only going to use the first one (Definition) to define your final view.

  1. Change the name of the interface view to: i_client_info.
  2. To add a new field to the interface, click the New field button. In the box to the right of the name of the new field, you can select the type of the field. Create the following structure:
  • client_id: text
  • full_name: text
  • client_type: text
  • full_address: text
  • phone: text
  • billing_due_date: localdate
  • balance: decimal
  1. Finally click on (you will see a dialog warning "You have not set an implementation view for this interface. Queries that involve this interface view will fail.", but don't worry you will add it later in this tutorial.
  2. Press Ok to create the interface view!

In the next section you will learn how to create Data Services using this interface.

Web services are software systems designed to support interoperable machine-to-machine interactions over a network. In this section, we are going to cover SOAP Web services. These Web services use SOAP (Simple Object Access Protocol) protocol to exchange messages. Following the specifications, the messages are:

  • Formatted using XML.
  • Typically conveyed using HTTP (but FTP, SMTP or JMS can be used too).
  • Described using WSDL files (Web Services Description Language: an XML-based language used to describe the functionality offered by the Web service).

Denodo can publish base/derived views and interfaces as Web services to enable external applications that cannot use the JDBC or ODBC interfaces, to retrieve data from the Data Virtualization layer.

Creating SOAP Web Services

Let's create your first SOAP Data Service in Denodo:

  1. We will use the Web Design Studio for creating SOAP Data service. Open the Web Design Studio and create a new folder called SOAP under your 2 - data services folder.
  2. Right-click on this new folder and select New > Data service > SOAP Web service.

  1. This will open the create new SOAP Web service dialog. This dialog has several tabs:
  • Operations: here you can define the list of operations of the Web service.
  • Settings: manages the configuration.
  • Advanced: in this tab you can change the parameters of the connection between the Web service and Denodo server.

New Operation

In the first tab, Operations, you can add new operations to the Web service. You only have to drag any view/stored procedure/interface into this dialog. In this example, you are going to create a SOAP Web service to search clients using their ID. To do this, drag the previously created i_client_info interface to the workspace.

  1. Now, you can rename the Web service. Change the name to customerws by typing in the input box labeled "web service" at the top.

  1. Click the '+' icon next to i_client_info and rename the operation getI_CLIENT_INFOByCLIENT_ID... to: getClientById by clicking the small pencil next to the operation.

  1. As you are going to search clients using the ID, you only have to leave the input parameter client_id and can drop the rest. Drop.

Security

Ok, your SOAP Web service definition is complete but, before we save it, there is an important point to keep in mind: security. By default, created Web services do not have any authentication method which means anybody can use it. You will definitely want to protect the access to your services by configuring authentication methods. This task can be done in the second tab (Settings).

There are several authentication methods available:

  • HTTP Basic/Basic with VDP.
  • HTTP Digest.
  • HTTP SPNEGO (Kerberos).
  • OAuth 2.0.
  • WSS Basic/Basic with VDP.
  • WSS Digest.

For this example, select WSS Basic with VDP method, leaving the Accepted user(s) field empty, to delegate authentication to the Denodo server (only users with privileges to connect and execute queries in the Denodo Platform will have access to the Web service).

That's all! Finally, click on the Save button to create the SOAP Web service.

In the next section you are going to see a similar process but for creating REST Web services.

You already know that Web services are software systems designed to support interoperable machine-to-machine interaction over a network. In this section, we are going to talk about another type of Web service: REST Web services. This kind of service uses HTTP for communication and is based on the REST (REpresentational State Transfer) architectural style as protocol for exchanging messages.

Following the specification:

  • The primary purpose of REST is to manipulate representations of Web resources using a uniform set of stateless operations.
  • It does not require XML-based protocols (like SOAP and WSDL) to support its light-weight interfaces.
  • It defines a set of operations using predefined HTTP verbs (GET, PUT, POST, or DELETE).
  • The format of the information returned (representation) is typically HTML, XML or JSON, although it may be an image, plain text, or other format.

Just like SOAP, Denodo can publish any base/derived views and interfaces as REST Web services to enable external applications (or users using their browser) to retrieve data from the Data Virtualization layer.

Creating REST web services in Denodo

To create your first REST Service:

  1. Open the Web Design Studio and create a new folder called "REST".
  2. Click on the option of the "2 - Data Services" and select New > Data service > REST Web service.

  1. This will open the new REST Web service dialog. This dialog has similar tabs to the "Create SOAP Web service" dialog:
  • Resources: here you can define the REST service.
  • Settings: manages the configuration, representation formats and authentication.
  • Advanced: in this tab you can change the parameters of the connection between the Web service and Denodo server.

New Operation

In the first tab, Resources, you can add views to the Web service. You only have to drag any view/stored procedure/interface into this dialog to add them.

  1. First, drag the i_client_info interface into the workspace panel.
  2. Rename the REST Service customer by clicking the web service name space at the top.
  3. Since you are only going to search for clients with their ID, remove the rest of fields from input i_client_info in the dialog. Press + next to i_client_info, click on icon except client_id.

More Settings

REST web services have more configurations available in the second tab (Settings).

In this tab you can complete the REST Web service definition by selecting the output representation format. By default, HTML, XML and JSON are made available.

Another important configuration is the authentication method. By default, created REST services don't have any authentication method and any connection could use it. In this example, simply select the HTTP Basic with VDP method (leaving the Accepted user(s) field empty) to delegate the authentication to the Denodo server.

Finally, click on Save, to create the REST Web service.


Now, you have created a SOAP and a REST web service in Denodo. In the next section, you are going to see how to deploy them with the Denodo Internal Web Container.

The Web Design studio has a Web Container Status Window for managing REST and SOAP services. To show this window in the workspace, you have to click the Tools > Web services container menu option. The Web Design studio will show a table listing the Web services created in a particular Denodo Database. The table has the following information when you choose the tutorial database from the dropdown menu (also see screenshot below):

  • Name: name given to the web service.
  • Type: interaction protocol of the Web service.
  • Description: description given to the web service when it was created
  • Status: info whether the service is deployed or not (if a data service was modified in Denodo an * will appear).
  • Context Path: path to the service in the Web container.
  • User: the credentials of the user the service uses to run a query (if "Not required", it passes through the current credentials of the service client).

Above the table you will find buttons to call functions on selected web services:

  • New: opens a wizard to create a new web service.
  • Edit: opens a wizard to edit the selected web service.
  • Delete: deletes the selected web services.
  • Deploy: starts the web service.
  • Redeploy: restarts the web service.
  • Undeploy: ends the web service.
  • Export: links to generate the .war and .wsdl file (if you want to deploy them in another external web container).
  • Database: choose which database you want to inspect web services for.

So, let's deploy our web services. After clicking the checkboxes next to each service, click on Deploy, the table will change to reflect the new status.

As you can see, the Context Path now shows the relative path of the services. The complete URIs will be:

Try to open those URLs in your browser to check if they are running correctly (remember you will need to login with a valid Denodo user to access these services admin / admin).

In the next section of this tutorial, you will find information about how to invoke these Data Services.

At this point you have deployed your SOAP and REST Data Services in the Denodo internal web container, let's see how to invoke each of them. Also, we will see how to invoke Denodo views as OData and GrahQL services.

Invoke a REST Web service

If you have completed the Basics Tutorial, you already know how to invoke a REST service. Specifically, in Basics Tutorial / Connecting from your App / RESTful Web service you learned how to query a RESTful web service. REST services will work the same way as the RESTful one but, in this case, you only have access to the views created with this REST service.

From a browser, open the URL shown in the Context path of the service:
http://127.0.0.1:9090/server/tutorial/customer.

The browser will ask for the user to connect (admin / admin). Then, it will load a page like this one:

See the examples below on how to query this service:

  1. http://127.0.0.1:9090/server/tutorial/customer/views/i_client_info -> should return all client data.
  2. http://127.0.0.1:9090/server/tutorial/customer/views/i_client_info?client_id=C005 -> should return the data for the client whose client_id equals 'C005'.


This is expected. Remember, you only created the interface to define the contract with your clients. We will add the implementation later.

Invoke a SOAP Web service

SOAP Web services are invoked using a SOAP client, for this example, you can download and use SoapUI. It only needs the WSDL file to create a client for the service, after installation.

To get the WSDL for this service, you can open a browser and go to the URL of the service: http://127.0.0.1:9090/server/tutorial/customerws, click on the SOAP link and finally, click on the wsdl link to see the content of the .wsdl file.


Now, open SoapUI and create a new project using the URL of the WSDL. The application will create a request template for your operation. Don't forget to configure the authentication!:

  • Username: admin
  • Password: admin
  • WSS-PasswordType: PasswordText



At this point, the infrastructure for our Web service is completed. Now, every team can work as their own:

  • One team can work on the application side to modify the app which will use your new Denodo service.
  • The other team (Denodo team), can work on the implementation of the service without modifying the Web service. All changes will be in the Denodo views, and there is no need to re-create the service (if the contract doesn't change).

The next section of the tutorial will show you how to add the implementation to your interface and how the services will return the results without needing to redeploy them.

You have figured out how to integrate consuming applications via web services, now you can work with Denodo to populate the services with data. Our goal now is to create a derived views and add it as the implementation of our i_client_info interface.

Creating derived views

You already know how to create derived views using the available operations (selection, union, join, flatten, minus, intersect). This topic was covered in the Basics Tutorial / DV First Steps / Simple Derived Views using the Web Design tool. Hence, in this tutorial, you will create this derived view in the Web Design Studio.

The derived view we want now must have fields that correspond to the definition of the interface. In our i_client_info interface, the schema is:

This schema is very similar to the schema of the client_with_bills view, let's reuse it!

  • i_client_info field: corresponding client_with_bills field(s).
  • client_id: this field matches exactly with the client_with_bills view.
  • full_name: you don't have this field in any of your views, but we can concatenate the name and surname fields of client_with_bills in order to get the full name.
  • client_type: this field matches exactly.
  • full_address: just like the full_name field, we will just concatenate street, city, zip and state.
  • phone: this field matches primary_phone.
  • billing_due_date: this field matches due_date.
  • balance: this field matches exactly.

Since we only need one view, to create the new derived view you should use the selection operation to model the proper schema. You can follow these steps:

  1. Right-click the client_with_bills view in the elements tree and select New > Selection.
  2. Go to the Output tab and rename the view to: client_info_impl.
  3. Remove the fields: name, surname, code, value, street, city, zip, state, ssn, billing_end_date, phone_center, billing_period_id, billing_start_date, billing_id, return_customer_id, package_id, amount_due and tax_id (Keep only client_id, client_type, primary phone, due_date, and balance).

  1. Rename primary_phone to phone.
  2. Rename due_date to billing_due_date and edit this field to convert it to date.

Open the edit dialog by clicking the small pencil next to billing_due_date or right-clicking the field and selecting edit.

Under Field expression, write to_localdate('yyyy-MM-dd HH:mm:ss', client_with_bills.due_date) (this calls the to_date function which will convert the text data of due_date into the date data type with format 'yyyy-MM-dd HH:mm:ss').

Click Ok

  1. Create a new text field called full_name by concatenating name and surname. Click the New field button at the bottom of the output dialog.
  2. Enter the Field name full_name. In Field expression, write concat(client_with_bills.name, ' ', client_with_bills.surname) (this calls the concat function which joins the name and surname with a space in-between) and Click Ok.

  1. Create a new field called full_address by concatenating street, city, zip and state.
  2. Click the New field button at the bottom of the output dialog. Enter the Field name full_address and in Field expression, write concat(client_with_bills.street, ', ', client_with_bills.city, ', ', client_with_bills.zip, ', ', client_with_bills.state) (this calls the concat function which joins each row's street, city, zip, and state with a comma and space in-between each). Finally click Ok.
  3. Change the type of the balance field from text to decimal.
  4. Open the edit dialog of the balance field. Type cast('decimal', client_with_bills.balance) in the Field expression (If possible, this will convert the text of the field into a decimal data type).Click Ok.

  1. Finally, click the button to create the view.


Configuring interface implementation

The last step of this tutorial is to configure the i_client_info interface to use client_info_impl as its implementation view. Open i_client_info in the interface in the workspace by double-clicking on it and then open the Edit dialog with the button at the top.

Here, open the Implementation tab and all you have to do is drag the implementation view we just created into the dialog:

As you can see, Denodo associates fields with matching names automatically. We're all set, click the button to add the implementation your interface.


Sweet, your work is done! Well, almost, there's only one last step. Let's test our services (the same steps covered in the Invocation Section) to see the implementation we just setup be queried.

Test the REST data service using these URLs again:

  1. http://127.0.0.1:9090/server/tutorial/customer/views/i_client_info -> now actually returns all client data.
  2. http://127.0.0.1:9090/server/tutorial/customer/views/i_client_info?client_id=C005 -> now actualy returns the data for the client whose client_id equals 'C005'.

Test the SOAP data service using SOAPui with the same request as before:


Pretty awesome, huh? Congratulations! You just finished the Denodo Data Services Tutorial set.

Thank you!

At this point you have implemented Denodo views as SOAP and REST Data Services in the Denodo internal web container, let's see how to invoke Denodo views as OData and GrahQL services.

Invoke Denodo views using OData Service

Open Data Protocol (OData) is a REST-based protocol for querying and updating data using simple HTTP messages. It is an OASIS standard based on technologies such as HTTP, Atom/XML and JSON.Virtual DataPort provides an OData 4.0 compliant interface, through its Denodo OData service.

The OData Service is available at:
https://denodo-server.acme.com:9090/denodo-odata4-service/denodo-odata.svc/database_name

To use the Denodo OData Service:

  1. Login to Design Studio by providing your Username and Password.
  2. Click on the more options button and Click on the Open OData Service.

  1. A dialog box with two links opens,

Click on the Connect to view link to view the Denodo view through Denodo OData service. A new browser window opens with the default ATOM format. Use the default username and password (admin/admin) to view,

http://localhost:9090/denodo-odata4-service/denodo-odata.svc/tutorial/address

To change the default format and view the data in JSON format, provide $format=JSON and order by the results using the Advanced Querying in the OData URL,

http://localhost:9090/denodo-odata4-service/denodo-odata.svc/tutorial/address?$format=JSON&$orderby=city%20asc

There are more other options like $select, $filter, $orderby and $expand, which can be used with the OData URL and customize the results.

Invoke Denodo views using GraphQL service

GraphQL is a data query language, and a runtime for executing those queries against your data.

  • It gives clients the power to ask for exactly what they need.
  • It gets many resources in a single request, reducing the number of API requests.

Denodo GraphQL Service enables the execution of GraphQL queries against the Denodo virtual data model, allowing graphQL-like queries on top of any data source. The GraphQL Service can be accessed via tools like Postman, GraphQL Playground, GraphiQL, etc.

To access the GraphQL service, go to

http://localhost:9090/denodo-graphql-service/graphql/database/schema.json

This service is always available once you start Virtual DataPort. You can execute GraphQL queries against the Virtual DataPort Server by using the Denodo GraphQL service.

As a Prerequisite, download and install the GraphQL application from its official site.

By default, Denodo GraphQL Service responses are represented in JSON format. The minimum Privileges required to access the service will be Connect privilege at database level and Minimum of Execute privilege at view level.

Let us access the view "address" in the tutorial VDB from GraphiQL.Provide,

For example, if the username and password is admin/admin, then convert this to Base64 encoded value in the https://www.base64encode.org/, which will be

{"Authorization":"Basic YWRtaW46YWRtaW4="}

Let us provide the query to fetch certain columns of the "address" view from the tutorial database on the left panel of GraphiQL,

{
address{
client_fid
street
city
zip
state
}
}

Invoke Denodo views using RESTful-WS

The Denodo RESTful Web service is an HTTP service deployed by default in the URL http://localhost:9090/denodo-restfulws that exposes resources like databases and views in the following standard representation formats such as XML, JSON and HTML. You can refer to the Connecting from your application - RESTful Web service where RESTful service is explained in detail.

Congratulations!