Cloud Data Warehouse

Connecting to Actian Avalanche from Pentaho via JDBC

Actian Corporation

May 21, 2019

Actian delivers an operational data warehouse as a managed service in the cloud. Its developer-friendly high-performance analytics engine has been proven to outperform other popular analytics platforms without specialized hardware or complicated software development. Actian  requires minimal setup, provides automatic tuning to reduce database administration effort and enables highly-responsive end-user BI reporting.   

This is how to connect to Actian  from Pentaho, using the Actian JDBC driver.  

A prerequisite is to have the Actian Avalanche JDBC driver downloaded and available in the environment where the tools run. The JDBC Driver is available via the Actian console Drivers and Tools link.  

 

Extract the iijdbc.jar file from the downloaded JDBC package.

If you have not done so already, you will need to put your IP addresses on the Allow List in the Actian console (Manage Update Allow List IPs).  

 

 

The steps below are provided for Pentaho 8.2 and Pentaho Server 8.2. The same configuration should work in similar ways for other versions.   

This is how to connect from Pentaho and Pentaho Server to Actian. 

Pentaho

Copy the iijdbc.jar file to the data-integrationlib directory. 

Start Pentaho (spoon.bat or spoon.sh, depending on the OS). 

Create a new transformation. 

Go to Tools > Wizard > Create database connection…

Provide a name for the database connection, e.g. Actian JDBC.

Option 1

Select Generic database for the type of database and Native (JDBC) for the type of access. 

Click Next. 

Specify the JDBC connection URL, which is provided in the Actian Data Cloud console (Manage > Connect). 

Example: 
jdbc:ingres://01c77d46010046ec7.vpaasstage.actiandatacloud.com:27839/db;encryption=on;

 

For the driver class, type com.ingres.jdbc.IngresDriver. 

Click Next. 

Enter your user name (dbuser) and password.  

Click Finish to complete the connection setup. 

Option 2

Alternatively, you can use the Ingres JDBC Connector  

The Actian Data Cloud console details (Manage > Connect) from the Common Properties tab should be used. 

 

Example: 

 

After the connection setup is completed, edit the newly created connection and go to Options, then add a new parameter encryption with the value of on. 

 

At this point the connection should test successfully.

To use the newly created connection, for example, add a Table input step. Select the connection that was just created as your Table input connection. 

Pentaho Server

Copy the iijdbc.jar file to the pentaho-servertomcatlib directory. 

Start Pentaho Server by running start-pentaho 

Go to the Pentaho User Console and login as administrator.  

Go to Create New > Data Source  

 

Select the desired source type, e.g. Database Table(s). 

Click on the “+” sign to add a new connection.  

 

Provide a name for the connection, e.g. Avalanche JDBC Connection. 

 

Select Generic database for the Database type 

Select Native (JDBC) for the Access parameter. 

Populate the URL value from the Actian Data Cloud console details (Manage > Connect > JDBC).  

Add the driver class name value as com.ingres.jdbc.IngresDriver. 

Also fill out the user name and password.  

Now test the connector to confirm that the connection is successful and that’s it!

To learn more about Actian, our fully managed cloud data warehouse service, visit https://www.actian.com/avalanche/.

About Actian Corporation

Actian is helping businesses build a bridge to a data-defined future. We’re doing this by delivering scalable cloud technologies while protecting customers’ investments in existing platforms. Our patented technology has enabled us to maintain a 10-20X performance edge against competitors large and small in the mission-critical data management market. The most data-intensive enterprises in financial services, retail, telecommunications, media, healthcare and manufacturing trust Actian to solve their toughest data challenges.