8.0 Tutorials

You can translate the document:

First steps - Connecting to Sources

The MySQL database that you installed in the Installation & Bootstrapping section contains the data of the CRM of a company. This data is split into several tables:

  • a table for client data,
  • a table for client types (a client can be residential or business)
  • and a table for addresses.

The diagram of the organization of this database is the following:

Your goal here is to combine this data using a Data Virtualization approach, this will enable us to create views that are more meaningful for the consumers of the data without having to modify the underlying data source (in many real-world scenarios we are not the owners of the data, just consumers, so changing the data schema will not be possible).

The first step we need to follow to virtualize this relational database is to connect to it using the Denodo Platform. Connecting to the data source will allow us to introspect it and graphically select which of its tables are to be virtualized within the Denodo Platform. Once connected, we will create one base view per table in the CRM.

A base view is a representation, in the Denodo Platform, of existing data in a remote data source. This base view is only metadata that describes how the information is stored and accessed in the original datasource but it does not contain any data. When a base view is queried, the data is retrieved in real-time from the source, unless it is cached (see the Performance tutorial).

Importing relational databases

First, let's create the data source for the CRM database. In this case, we will create a JDBC connection to MySQL, but other possibilities are Oracle, Microsoft SQL Server, DB2, PostgreSQL, Hive, Nettezza, Teradata, Denodo VDP, etc.

The recommended way to connect to databases when using Denodo is through JDBC (this is an acronym referring to Java Database Connectivity), so let's start this tutorial creating a new JDBC data source to import a table with a primary key.

In the Installation & Bootstrapping section, you installed a MySQL database server and imported the libraries into the Virtual DataPort Extension management. With this driver added to the Administration Tool, you are ready to create the JDBC Data Source following these steps:

  1. Create two folders nested under the "1 - first steps" folder you have made, one for data sources called "1 - Data Sources" and another for base views called "2 - Base Views".
  2. Right-click on the "1 - Data Sources" folder and select "New > Data source > JDBC".
  3. Name the data source, for example, acme_crm_ds.
  4. Select "MySQL 5" as Database Adapter.
  5. Change the Database URI to match your MySQL installation, for example, jdbc:mysql://localhost:3306/acme_crm
  6. Enter the login and password fields: acme_user / acme_user.
  7. Click on "Test connections".
  8. Click on the "Save" button.
  9. Click the "Create base view" button at the top.

The Design Studio will show the introspected schema of the relational database:

To incorporate some of the tables into the Denodo virtual schema, you have to check the box near the tables or views you want to import. In this case, check address, client and client_type and then click on the Create selected button.

Later, you will be able to query these base views or combine them with other views.

When the importing process is finished, you will see the new views in the elements tree panel. If you double-click on the view name, the schema of the base view will be shown in the workspace.

Any primary key found in the source table gets marked in the Denodo base view. This is important because this information will be exposed to Denodo clients (for example, reporting tools).

NOTE

As you can see, each new base view will be listed in the Elements Tree :

  • In the folder where the view has been created ("2 - "Base Views").

If you are using Virtual DatatPort Administration tool then the base view will also be listed as a child of the data source the base view belongs to. Data Source child nodes cannot be moved to other folders. They are added to provide an easy way to see the base views created from a data source.

TIP

Finally, let's move the base views to the folder that we created for them by dragging them to the "2 - Base Views" folder.

After these steps are completed, we have a virtual representation of our CRM in Denodo. In the next section, we are going to learn how to query it to see how the data comes in real time from our MySQL database, and, after that, we will start creating data combinations that will add semantic value to the client applications that are consuming this data.