Connecting to Actian Avalanche from Pentaho via JDBC

Actian Avalanche 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 Avalanche 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 Avalanche from Pentaho, using the Actian Avalanche 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 Avalanche 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 Avalanche 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 Avalanche. 

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 Avalanche 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 Avalanche 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 Avalanche 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 Avalanche 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 Avalanche, our fully managed cloud data warehouse service, visit https://www.actian.com/avalanche/.