SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Data Virtualization Tools and Software Denodo Platform and Data Virtuality Tutorials and Downloads for SQL Database Developer

Connect Amazon Redshift Database from Denodo Data Virtualization Platform

This Denodo guide shows how a JDBC database connection can be created to connect Amazon Redshift from Denodo Virtual DataPort Administrator Tool. Before you create database connection to Redshift database using JDBC connection from Denodo, it is better to follow the best practices provided by Denodo for creating a virtual database and establishing a folder structure for your projects. The first 2 sections are more about following these best practices like to create virtual database and create data sources folder for database connections that you will create for your project. Last section in the tutorial, shows the step to create a JDBC database connection to Amazon Redshift database on Denodo Platform.

Create New Virtual Database on Denodo Platform

After the data virtualization architect or developer launched Denodo Virtual DataPort Tool and log on to the application, you will see the Server Explorer window on the left side with previously created data sources and virtual databases. If you are new with Denodo Virtual DataPort and if you are using the Virtual DataPort for the first time, the view of the tool will be similar as below.

Denodo Virtual DataPort Administrator Tool

The first step will be creating a new virtual database.
Please follow menu options "Administration > Database management"

Denodo Virtual Database Management

When the Database Management screen is displayed, you will see the existing virtual databases.
One for admin database and another for itpilot database. These are the default databases shipped with every Denodo Platform installation.

default virtual databases on Denodo Platform

Now the data virtualization architect is ready to define a new virtual database for the project he is working on at the moment. For exampls, if the architect or virtualization developer is building a data lake project, he or she can create a new virtual database named "Data Lake"

Click on New icon to launch the wizard to create a new virtual database

new virtual database

Provide the name of the virtual database and a short description about the virtual database. For example, a "data lake" is the sink where all data sources including structured and unstructured can be joined, so the naem of the virtual database and its purpose is entered briefly.

Before you press OK to complete the creation of the new virtual database, just a reminder. The name should not contain a space character but you can use "_" for example. And all capital letters are changed into lower case letters automatically when the definition is saved.

create new virtual database on Denodo Virtual DataPort

Press OK to save and create the new virtual database in the Denodo Virtual DataPort Tool.
Now in Server Explorer window, you wil be able to see the new database too.

Why is the virtual database is important. One reason is that it is possible to define one caching database for the virtual database sources. If you plan to cache data in different target data warehouse or database applications, then you can split the virtual database and its sources into two or more different virtual databases.

Create Data Sources Folder for Database Connections

Now on Server Explorer window highlight the virtual database "data_lake". Then right click on the database name on on context menu choose "New > Folder" option

create new folder

Create a new folder named "Data Source"

create new data source folder

Create JDBC Connection to Amazon Redshift from Denodo Virtual DataPort Tool

Right click on "Data Source" folder and follow menu options "New > Data source > JDBC"

create new JDBC data source on Denodo Data Virtualization tool

Here is the default JDBC creation screen for Generic database adapter for data virtualization developers.

generic JDBC data source connection template for Denodo developer

Since we are planning to connect to Amazon Redshift database using JDBC, we can choose "Amazon Redshift" from "Database adapter" combobox. Changing the database adapter changes the default template values displayed on the screen. This makes the task of creating a new data source easier for the data virtualization architects and developers.

Denodo to Amazon Redshift database JDBC connection template

Replace the template parameters with your data source specific values like name, database URI, login and password.

JDBC connection propertied to connect Amazon Redshift database from Denodo Platform

Click on "Test connection" button to see if the connection paramter values are correctly provided and a valid successfull connection can be established from Denodo Virtual DataPort Server and Amazon Redshift database.

If you are able to see the "JDBC connection tested successfully" message, then click OK to close the message box.

test JDBC connection

Press Save button on data source connection configuration screen.
Now within the "Data Sources" folder under the virtual database "data_lake" that we have previously created, we have our JDBC connection to Amazon Redshift database.

If you plan to add connections to different Redshift databases, you have to define separate connections as new data sources.

Data Virtualization

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.