You can translate the document:
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. Denodo has the ability to easily create new Data Services using the views available 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 data 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 are 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 Denodo infrastructure to 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 Data service has to be deployed.
  • On the consuming application side, they have to point to the new Data service.

Along with 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 layer (without this contract they have to implement the changes in the application after the new Data service is ready). In this example, the contract will be the WSDL of a SOAP Web service without implementation in Denodo and, in the next section, you will learn how to create an interface element that is very useful for these kinds 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

It is strongly advised to complete the Data Combination tutorial before starting this one, as we are going to be using the resources (data source, base views and integrations) created on it.

If you have already completed it, please proceed to the next section. Otherwise, follow the subsequent instructions:

  1. Launch the resources needed (check how in the Installation & Bootstrapping tutorial).
  2. Log in Denodo Design Studio (user/password: admin/admin)
  3. Import this VQL to Denodo by clicking into File > Import. Drag ‘n' drop the file and select "tutorial" as the Database:

If all steps have been executed correctly, you should observe the following in the Design Studio's elements tree:

Great! Now it's time to start the tutorial!

In Denodo, an interface is a special type of view that consists 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 in Denodo:

  1. Open the Design Studio and create a new folder under the tutorial database called 3 - business entities (see VDP Naming Conventions).
  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: customer.
  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:
  • customer_id: text
  • full_name: text
  • customer_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 Yes 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 in Denodo

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

  1. Open the Design Studio and create a new folder under the tutorial database called 5 - data services (see VDP Naming Conventions).
  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 customers using their ID. To do this, drag the previously created customer interface to the workspace.

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

  1. Click the '+' icon next to customer and rename the operation getCUSTOMERByCUSTOMER_... to: getCOSTUMERByID by clicking the small pencil next to the operation.

  1. As you are going to search customers using the ID, you only have to leave the input parameter customer_id and can drop the rest by clicking

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/HTTP Basic with VDP.
  • HTTP Digest.
  • HTTP SPNEGO (Kerberos).
  • OAuth 2.0.
  • WSS Basic/WSS Basic with VDP.
  • WSS Digest.

For this example, select HTTP 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 Denodo virtual layer.

Creating REST Web services in Denodo

Now, let's create your first REST Data Service in Denodo:

  1. Open the Web Design Studio and click on the option of the 5 - data services folder 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 schema of the REST data 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 Resource

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 customer interface into the workspace panel.
  2. Rename the REST Service customer by clicking the web service namespace at the top.
  3. Since you are only going to search for clients with their ID, uncheck the rest of fields from input customer in the dialog. Press + next to customer, click the input boxes except customer_id.

More Settings

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

In this tab you can complete the REST data 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 data 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 data service in Denodo. In the next section, you are going to see how to deploy them with the Denodo Internal Web Container.

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

When you choose the tutorial database from the dropdown menu you will see the data services we have created in the previous sections of this tutorial:

As you have noticed, both data services are not deployed yet so, let's deploy our web services!

You only have to click on the checkboxes next to each service and click on .

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.

Invoke a REST Web 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/costumer -> should return all client data.
  2. http://127.0.0.1:9090/server/tutorial/customer/views/costumer?costumer_id=C005 -> should return the data for the client whose client_id equals 'C005'.


You can learn more about other examples by clicking on the OpenAPI viewer of the home page (http://127.0.0.1:9090/server/tutorial/customer/). Expand the request examples to see detailed information about possible responses and test them specifying the parameters (For now, as explained above, the response will be "Error: response status is 500").

Invoke a SOAP Web service

SOAP Web services are invoked using a SOAP client, for this example, you can download and use Postman. 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/customerservice, click on the SOAP link (by default, use user/password: admin/admin) and finally, click on the wsdl link to see the content of the .wsdl file.

Now, open Postman, click on import and paste all the content inside http://127.0.0.1:9090/server/tutorial/customerservice/services/customerservice?wsdl. The application will create a request template for your operation getCUSTOMER. By default it returns all the clients, you can change the body to return the client with a specific customer_id. Don't forget to configure the authentication:

  • Username: admin
  • Password: admin
  • Type: Basic Auth



At this point, the infrastructure for our Data services 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 data 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 view and add it as the implementation of our customer interface.

You already know how to create integration views using the available operations (selection, union, join, flatten, etc). This topic was covered in the Denodo Basics and Data Combination tutorials. Hence, in this tutorial, we will quickly review this process.

The integration view we want to create must have fields that correspond to the definition of the customer interface. The schema of the interface is:

That schema is very similar to the schema of the iv_client_with_bills view, so let's reuse it!

  • customer_id: this field matches exactly with the customer_id field of the iv_client_with_bills view.
  • full_name: you don't have this field but you can concatenate the name and surname fields of iv_client_with_bills in order to get the full name.
  • customer_type: this field matches exactly with the client_type field.
  • full_address: just like the full_name field, you can just concatenate street, city, zip and state.
  • phone: this field matches primary_phone.
  • billing_due_date: this field matches due_date (you have to convert the data type to localdate).
  • balance: this field matches exactly (you have to convert the data type to decimal).

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 iv_client_with_bills view in the elements tree and select + New > Selection.
  2. Go to the Output tab and rename the view to: iv_customer.
  3. Remove the fields: client_id, name, surname, street, city, zip, state, code, type_value, amount_due, billing_id, billing_end_date, package_id, billing_start_date, billing_period_id, ssn, phone_center and tax_id (Keep only client_type, primary_phone, customer_id, due_date, and balance).

  1. Rename client_type to customer_type and primary_phone to phone.
  2. Edit the due_date filed to change the name to billing_due_date and to convert it to date using this expression: to_localdate('yyyy-MM-dd HH:mm:ss', iv_client_with_bills.due_date)
    This expression uses the to_localdate function which will convert the text data of due_date into the date data type using the format 'yyyy-MM-dd HH:mm:ss'). Click Ok to save it.

  1. Create a new field called full_name by concatenating name and surname. Click the New field button in the output dialog, and in the Field expression, write
    concat(iv_client_with_bills.name, ' ', iv_client_with_bills.surname)
    This calls the concat function which joins the name and surname with a space in-between. Click Ok.

  1. Create a new field called full_address by concatenating street, city, zip and state. Click the New field button button in the output dialog, and in the Field expression, write
    concat(iv_client_with_bills.street, ', ', iv_client_with_bills.city, ', ', iv_client_with_bills.zip, ', ', iv_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.

  1. Change the type of the balance field from text to decimal, opening the edit dialog and typing
    cast('decimal', iv_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. Set customer_id as Primary Key of the view

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

The last step of this tutorial is to configure the customer interface to use iv_customer view as its implementation view.

For doing that, open the customer interface, go to the Implementation tab and drag the implementation view(iv_customer) 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 of 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 of Services 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/customer -> now actually returns all client data.
  2. http://127.0.0.1:9090/server/tutorial/customer/views/customer?customer_id=C005 -> it actually returns the data for the client whose client_id equals 'C005'.


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

Thank you!