Setup Pentaho ETL server Linux

Todo

• rotate logs
• startup script for carte server

Software

1. Setup centos 6.5
2. set hostname in /etc/hosts
3. start sshd
4. turn off iptables
5. add EPEL repo

Copy  wget http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
  wget http://rpms.famillecollet.com/enterprise/remi-release-6.rpm
  sudo rpm -Uvh remi-release-6*.rpm epel-release-6*.rpm

1. yum install htop acpi iotop iftop ftop wget man mlocate
2. create users pentaho and postgres
3. Java setup package via http://wiki.centos.org/HowTos/JavaOnCentOS

• download (urgh) from (puke) Oracle
• yum localinstall jdk-7u51-linux-x64.rpm

1. home variables

Copy$ vi /etc/profile.d/java.csh
export JAVA_HOME=/usr/java/latest
export PATH=$PATH:/usr/java/latest/bin

PostgrSQL install instructions for Centos from EPEL

1. https://wiki.postgresql.org/wiki/YUM_Installation Show archive.org snapshot
2. change pg_hba.conf local auth from peer to ident
3. connect to standard db

MySQL Java Connector

1. download dev.mysql.com/downloads/connector/j/‎
2. find it on public ftp to avoid account login on oracle site
3. cp mysql-connector-java-5.1.29-bin.jar /opt/pentaho/data-integration/lib/

SETUP business integration community edition

1. download bi-server-ce http://sourceforge.net/projects/pentaho/files/Business%20Intelligence%20Server/5.0.1-stable/
2. unpack to /opt/
3. change owner to pentaho:pentaho
4. prepare database
5. run scripts like so:
   psql -U postgres < /opt/pentaho/biserver-ce/data/postgresql/create_*
6. turn off demo db HSQLDB

• http://interestingittips.wordpress.com/2011/05/05/complete-pentaho-installation-on-ubuntu-part-2/
• http://anonymousbi.wordpress.com/2013/12/15/pentaho-bi-server-5-0-1ce-mysql-installation-guide/

1. in tomcat/webapps/pentaho/WEB-INF/web.xml comment out those parts

Copy    [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] 
    [BEGIN HSQLDB STARTER]
    <listener>
    <listener-class>org.pentaho.platform.web.http.context.HsqldbStartupListener</listener-class>
    </listener>
    [END HSQLDB STARTER] 

1. add repository configuration as described in "Install BA Server with Your Own BA Repository"
   NB: this manual references a lot of stuff from the enterprise edition (i.e. the quartz db is called di_quartz, but should be quartz only)
2. solve all Java error trace stack problems
   NB: again, make sure there are no funny community ed. / enterprise ed. confusions, also check postgresql authorization configuration
   • ./start-pentaho.sh

Asides

• Case sensitive Bug checks for table "QRTZ"

• ERROR [EmbeddedQuartzSystemListener] EmbeddedQuartzSystemListener.ERROR_0007_SQLERROR
• org.postgresql.util.PSQLException: ERROR: syntax error at or near "RUNSCRIPT"

• Service conf on CentOS
  • add to chkconfig for start on boot
  • chkconfig --add service
  • chkconfig --level 234 service on