You can translate the document:
DATA CONSUMPTION

In previous sections, you created several views with Denodo to allow client applications to retrieve the information directly from the Denodo server. In particular, you have created the view iv_amount_due_by_client which combines data from several sources and exposes the information about the billing balance of a company's clients.

You already know how to execute queries over that view using the Design Studio, but now it's time to connect to the Denodo server from other applications. Denodo Platform is based on a client-server architecture, where client applications issue requests or queries to the Denodo server.

These requests can be sent by any consumer using one of the following interfaces:

  • JDBC / ODBC / ADO .Net: Denodo provides its own JDBC and ODBC drivers. Clients must send VQL (a SQL-like language) queries to Denodo.
  • RESTful Web service, OData and GraphQL endpoints: Denodo includes support for these interfaces which are useful for applications that cannot use the JDBC or ODBC interfaces to connect to Denodo.

In this tutorial, you will see several ways to access Denodo from external applications:

  • Using a Third Party JDBC Client.
  • Using ODBC.
  • Consume a Denodo RESTful Web service.
  • How to Invoke Denodo Views Using OData and GraphQL Endpoints.

It is strongly advised to complete the Data Services 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:

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!

JDBC (Java DataBase Connectivity) is a Java data access technology from Oracle Corporation. JDBC provides an API for the Java programming language for database-independent connectivity, and it is based on the use of drivers for each database. A client application requires separate drivers, usually vendor supplied, to connect to different types of databases.

In this section, you are going to see how to access the Denodo server using a JDBC client. This information is valid for any Java-based application. For example, we will use DBeaver (a generic database management tool for developers) but feel free to use any other JDBC client.

The first thing that you have to do when connecting using JDBC is to add Denodo's JDBC driver to the client application. To use the JDBC driver in your client, you have to add the denodo-vdp-jdbcdriver.jar file to the classpath of your application.

For adding the Denodo JDBC driver in DBeaver, you have to open DBeaver, go to Database > Driver Manager... and in the Driver Manager window click on New and complete the fields with the following information:

  • Class Name: com.denodo.vdp.jdbc.Driver
  • URL Template: jdbc:denodo://{host}:{port}/{database}

Then, go to Libraries tab, click on Add File and select the path to the denodo-vdp-jdbcdriver.jar file.

Finally, click on to connect to the Denodo database. Select the one you just created, Denodo 9.0, and click Next. Enter the host (localhost in our case), the database and don't forget to put the password (by default it is admin), test it is connected by clicking on Test Connection and then click on Finish.

Now that you are connected, you will see the base views listed as tables and integration views listed as views in the left panel of the window

If you run any query from the SQL Editor (To open it, right click on tutorial and SQL Editor > New SQL script), you will get the same results that you were seeing before from the Design Studio. Try, for example:

SELECT * FROM bv_crm_client

Cool! Now, go to the next section to learn more ways to connect to Denodo.

ODBC (Open DataBase Connectivity) is a standard to access databases originally developed by Microsoft. ODBC provides an API to make the code independent of database systems and operating systems.

Denodo provides an ODBC interface, but it requires the installation of the ODBC driver. Like any other ODBC driver, you have to install it on the machine where the client application is running.

In this section you will learn how to access the Denodo server using an ODBC client. This information is also valid for any other ODBC connection. For example, we will use MS Excel but feel free to use any other ODBC client, like MS Power BI.

Creating the DSN

The first thing that we have to do when connecting using ODBC is to install the Denodo ODBC driver. .

To install the driver, you have to extract the files of the package and run one of the programs inside the msi folder.

Once you've installed the ODBC driver you will need to add a new user data source:

  1. Open the ODBC Data Sources applet of the Windows Administrative Tools (Control Panel).
  1. Open the System DSN tab and click Add. The difference between a "System DSN" and a "User DSN" is that the "User DSN" can only be used by the current user and the "System DSN" can be used by all the users of the system. If you create a "User DSN", do so with the same user name you run the application that will connect to Denodo.
  2. Select the DenodoODBC Unicode and click on the Finish button.

In the configuration dialog fill in the following information:

  1. Data Source: name of the ODBC source (e.g. Denodo Tutorial).
  2. Database: database in Denodo. (e.g. tutorial).
  3. Server: host name of the Denodo server (e.g. localhost).
  4. SSL Mode: disable
  5. Port: port of the Denodo server (e.g. 9996).
  6. User Name / Password: credentials to connect to Denodo (e.g. admin/admin).

Now, you have to configure some of the Advanced properties by clicking on the Datasource button (a pop-up will open). Select the same options shown in the screenshots below and write "SET QUERYTIMEOUT TO 3600000" in the Connect Settings Box on page 2:

Finally, click the Ok button and then click Save button to finish.

Accessing Denodo from MS Excel

Now, you have your environment ready to connect to Denodo using ODBC (remember that the previous steps are only valid to connect to the "tutorial" virtual database, so if you want to connect to another database you will have to create a new DSN).

For an example of an ODBC client application you can use the well-known Microsoft® Excel®. You will only have to select this DSN as a data provider to import the customer data into the spreadsheet.

Please, follow these steps and see the results:

  1. Open Excel and, under the Data tab, click Get Data > From Other Source > From ODBC.

  1. Select DenodoODBC DSN.

  1. Click tutorial [1] > tutorial [10] > iv_amount_due_by_client and Load the data.

And voilà! The results from Denodo are populated into the MS Excel spreadsheet!

RESTful Web services apply the ideas of the web to data-delivery, by providing scalable, flexible and stateless access to data assets based on well-known protocols and formats. Additionally to traditional SQL-type access methods such as JDBC or ODBC, all the views in Denodo can be accessed using the RESTful interface.

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 standard representation formats: XML, JSON and HTML (more user-friendly)

This Web service allows Denodo to work inside applications following the REST service architecture style and provides support for linked data in the enterprise deployment (see next section for more information about this).

When accessed from a browser, the Denodo RESTful endpoint will look like this:

See the examples below to know how to query this service:

Representations

We mentioned at the beginning of this section that Denodo supports three representation formats (XML, JSON and HTML). You saw in the previous examples the HTML format using a browser. How can you get the response in the other formats? The answer is easy, by adding a query parameter to the URL:

Linked Data

Now you will see how to enable linked data using a new Denodo element: associations.

In the Denodo Basics tutorial, when you imported the base views from the crm database, Denodo prompted about creating associations from the foreign keys automatically.

The associations in Denodo will mirror the foreign key constraints of the tables/views in the source database, so it is always a good idea to create the associations.

Based on the definition of the associations, the Denodo RESTful Web service will show links that will allow you to traverse the associations. Let's see how it works with an example, by using the views created in previous sections of the tutorial.

Now it's time to return to the RESTful Web service and get the results of the client view: http://localhost:9090/denodo-restfulws/tutorial/views/bv_crm_client

As you can see in the screenshot below, two columns with links are added to the output table and the text of the link is the reference base view. In this example, if we click on the bv_crm_address link for the customer John Smith, Denodo will follow the association and display his address.

In the same way, the bv_crm_client_type link will load the associated client type information when clicked.

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. Denodo provides an OData 4.0 compliant interface, through its Denodo OData service.

The Denodo OData Service is available by default at:
http://localhost:9090/denodo-odata4-service/denodo-odata.svc/tutorial, but you can get the direct link of any view from the Design Studio:

  1. Just click on the more options button of any view and click on the Open OData Service option.

  1. A dialog box including the OData link opens:

Click on the link to view that 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/bv_crm_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/bv_crm_address?$format=JSON&$orderby=city%20asc

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 for the tutorial database, go to http://localhost:9090/denodo-graphql-service/graphql/tutorial/schema.json

The data sources for this tutorial contains the GraphQL Playground application so you can use it for testing the Denodo GraphQL service.

CORS configuration

Important! Before using GraphQL Playground, make sure that CORS is configured in Denodo. If not, you will receive an error trying to connect to the GraphQL endpoint from a client application!

  1. Open \resources\apache-tomcat\conf\web.xml and comment the content inside security-constraint:

  1. Open \resources\apache-tomcat\webapps\denodo-graphql-service\WEB-INF\classes\application.properties and change the property cors.allowed-origins=* to cors.allowed-origins=http://localhost:4000
  2. Restart Denodo Virtual Dataport server

Invoking a Denodo view

Now, let's access the view bv_crm_address in the tutorial database from GraphQL. For this, we have to provide the Endpoint URL and a HTTP Header for the authentication:

For executing a view we have to provide the query (using the GraphQL language) to fetch certain columns of the bv_crm_address view:

{
 bv_crm_address{
     client_fid
     street
     city
     zip
     state
 }
}

And click on the Play button:

The same result can be achieved using other client application like Postman (remember to configure Authorization as Basic Auth admin/admin):

Congratulations, well done! You have finished this tutorial! 🙂