Pentaho Server 8.1 with MariaDB (MySql)

[Edited on june 13th to add the solution note to Sql parameters]

Migrate your Database

The official way to change the database is at pentaho-wiki and pentaho-doc. These are notes based on my 2011 post that modifies the DEMO to use MariaDB (or MySql) instead of the HSQLDB that comes with it.

Important: I verified this on a Ubuntu 18.04 and MariaDB 10.1, keeping the  jcr (Jackrabbit repository) in thefilesystem and have a problem with JINDI SQL Queries that I have to substitute with JDBC SQL Queries (see the final note).

 

I. DATABASE CONTENT

Driver

[Optional] Update the driver to Connector/J (mysql-connector-java-5.1.46.zip), choose older version and platform independent. Then unzip its content and move mysql-connector-java-5.1.46.jar to ~/Pentaho8/pentaho-server/tomcat/lib. The OPTION error on logs will go away. You should move the older driver to another location.

If you have the desktop tools, copy the driver also to ~/Pentaho8/report-designer/lib, ~/Pentaho8/data-integration/lib, ~/Pentaho8/shema-workbench/lib.

Create content in Database

Start the MariaDB engine and run the suplied scripts in ~/Pentaho8/pentaho-server/data/mysql5, to create required databases:

cd ~/Pentaho8/pentaho-server/data/mysql5
 mysql -u root-user -p root-password < create_quartz_mysql.sql
 mysql -u root-user -p root-password < create_repository_mysql.sql

Download the Sample Database (here), unzip and upload it:

mysql -u root-user -p root-password < SampleData_2018_mysql.sql

II. TOMCAT CONFIG

Quartz Settings

  1. Go to ~/Pentaho8/pentaho-server/pentaho-solutions/system/quartz/
  2. Open quartz.properties.
  3. Find #_replace_jobstore_properties and change it to
    org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate

Hibernte Settings

  1. Goto to ~/Pentaho8/server/pentaho-server/pentaho-solutions/system/hibernate
  2. Open hibernate-settings.xml.
  3. Find the  tag config-file and change it to
    system/hibernate/mysql5.hibernate.cfg.xml
  4. Check settings to MySQL Configuration according to your system (If you use defaults there is nothing to change) in mysql5.hibernate.cfg.xml.

System connections

  1. Go to ~/Pentaho8/pentaho-server/tomcat/webapps/pentaho/META-INF/
  2. Open context.xml
  3. Change classname, url and SQL query to:
    <?xml version="1.0" encoding="UTF-8"?>
    <Context path="/pentaho" docbase="webapps/pentaho/">
    	<Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
    		factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
    		maxWait="10000" username="hibuser" password="password"
    		driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/hibernate"
    		validationQuery="select 1" />
    
    	<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
    		factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
    		maxWait="10000" username="pentaho_user" password="password"
    		driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/quartz"
    		validationQuery="select 1"/>
    
    </Context>

Spring config

  1. Go to ~/Pentaho8/pentaho-server/Pentaho-solutions/system/
  2. Open applicationContext-spring-security-hibernate.properties
  3. Change driver, url and dialect to
    jdbc.driver=com.mysql.jdbc.Driver
    jdbc.url=jdbc:mysql://localhost:3306/hibernate
    jdbc.username=hibuser
    jdbc.password=password
    hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

Add datasources to jndi list

  1. Go to ~/Pentaho8/pentaho-server/pentaho-solutions/system/simple-jndi
  2. Open jdbc.properties
  3. Change the driver definitions to mysql
    SampleData/type=javax.sql.DataSource
    SampleData/driver=com.mysql.jdbc.Driver
    SampleData/url=jdbc:mysql://localhost:3306/sampledata
    SampleData/user=pentaho_user
    SampleData/password=password
    Hibernate/type=javax.sql.DataSource
    Hibernate/driver=com.mysql.jdbc.Driver
    Hibernate/url=jdbc:mysql://localhost:3306/hibernate
    Hibernate/user=hibuser
    Hibernate/password=password
    Quartz/type=javax.sql.DataSource
    Quartz/driver=com.mysql.jdbc.Driver
    Quartz/url=jdbc:mysql://localhost:3306/quartz
    Quartz/user=pentaho_user
    Quartz/password=password
    SampleDataAdmin/type=javax.sql.DataSource
    SampleDataAdmin/driver=com.mysql.jdbc.Driver
    SampleDataAdmin/url=jdbc:mysql://localhost:3306/sampledata
    SampleDataAdmin/user=pentaho_admin
    SampleDataAdmin/password=password

Stop HSQLDB from starting

  1. Go to ~/Pentaho8/pentaho-server/tomcat/webapps/pentaho/WEB-INF/
  2. Open web.xml
  3. Find the word HSQLDB. There are to points to edit. You will delete “–>” on the first line and “<–” on the last, to make it into a comment.
    In version 8.1 go to lines 25 and 167. They should look like this:

    <!-- [BEGIN HSQLDB DATABASES]
     <context-param>
     <param-name>hsqldb-databases</param-name>
     <param-value>sampledata@../../data/hsqldb/sampledata,hibernate@../../data/hsqldb/hibernate,quartz@../../data/hsqldb/quartz</param-value>
     </context-param>
     [END HSQLDB DATABASES] -->

    and

    <!-- [BEGIN HSQLDB STARTER]
     	<listener>
     	<listener-class>org.pentaho.platform.web.http.context.HsqldbStartupListener</listener-class>
     </listener>
     [END HSQLDB STARTER] -->

III. Change Web App Datasources

  1. Start your server and log in your browser (localhost:8080/pentaho) as Admin
  2. In the PUC (Pentaho User Console) page click at the left top menu: FileManage Data Sources
  3. You need to select SampleDataJDBC data-source and click on the gear icon to open the config window.
  4. Change the database type to MySql. Then change the Database Name to sampledata (the database name is in lowercase), port to 3306, type again the password (password) and test the connection. Click OK if its succesfull.
    ManageDatasource_v8.1

Here, you can define a connection to your databases or you can return later.

That’s it, you should have a working Server with MariaDB. To test it by using the samples: Browse: Samples → Public → Steel Wheels.

 

IV. Modify your SQL Queries that uses Parameters

But there is a problem if you want to use a query wit SQL with parameters. Maybe there is a missconfiguration in CDA or the repository, or a problem with the MySql driver for a MariaDB engine, as the parameters in the JNDI configuration report an error and do not work.

To get a result I use only SQL JDBC, by supplying these config parameters on each query

driver:   com.mysql.jdbc.Driver
 url=      jdbc:mysql://localhost:3306/sampledata?generateSimpleParameterMetadata=true
 user:     pentaho_admin
 password: password

 

Troubleshoot

If you have problems check the log file is ~/Pentaho8/pentaho-server/tomcat/logs/catalina.out its very verbose so look for ERROR lines

Hitachi Vantara

Today September 19, 2017, Hitachi Vantara was announced, it is a new business entity that will unify the operations of Hitachi Data Systems, Hitachi Insight Group, and Pentaho.

So we’ll be saying both Pentaho and Vantara for a while to refer to this BI suite.  🙂

This is the press release: https://www.hitachivantara.com/en-us/news-resources/press-releases/2017/gl170919.html

The main new Site: https://www.hitachivantara.com/

And new twitter account to follow: https://twitter.com/HitachiVantara

The Community Edition at Sourceforge: https://sourceforge.net/projects/pentaho/ with the Hitachi Logo and title “Hitachi Vantara | Pentaho”. The wiki in http://wiki.pentaho.com/display/COM/Community+Wiki+Home

Data integration is still “PDI” or “Kettle” in the community area: https://community.hds.com/docs/DOC-1009855

It seems (#HitachiNEXT) they’ve got a nice business and consulting strategy. Lets see what they do with the original Open Source philosophy.

Pentaho 6.1

As it was announced by Pentaho-Hitachi, version 6.1.01 was realeased on the 9th of april of 2016 in EE and CE editions.

Sourceforge.net: Data IntegrationBusiness Intelligence ServerReport DesignerPentaho Metadata and Big Data Shims.

There are lots of fixes: 179 of 180 issues have been resolved in PDI, 59 of 59 issues for the BI Server and 14 of 14 issues for the PRD.

These are the main improvements acording to Pedro Alvez: Services, ODBC, metadata injection, several steps improvement and enabling tests in the BI server from the PDI, this was lost since studio went down the tube.

Steps for upgrading must be the same as previously posted. If not I will get back to you and annote this post. [Which is very much unlikely].