Connecting to Actian Avalanche from Pentaho via JDBC
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.
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.
Select Generic database for the type of database and Native (JDBC) for the type of access.
Specify the JDBC connection URL, which is provided in the Actian Data Cloud console (Manage > Connect).
For the driver class, type com.ingres.jdbc.IngresDriver.
Enter your user name (dbuser) and password.
Click Finish to complete the connection setup.
Alternatively, you can use the Ingres JDBC Connector
The Actian Data Cloud console details (Manage > Connect) from the Common Properties tab should be used.
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.
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!