Pessoal,
Neste post será mostrado como integrar o PostgreSQL 9.1 ao Pentaho BI Server CE 3.10.0. Podemos atribuir à referida integração, um nível de dificuldade médio, porém, será necessário uma atenção especial para edição dos arquivos, pois uma "tag" que não for fechada, ou a falta da declaração de um driver ou um driver escrito incorretamente, podem comprometer o resultado final.
A integração consiste na migração dos bancos de dados "hibernate" e "quartz" do SGDB HSQLDB, que acompanha o pacote original do Pentaho BI, para o PostgreSQL. No entanto, o HSQLDB vai continuar funcionando no ambiente para prover suporte ao Data Warehouse (DW) SampleData, para mostragem dos testes dos projetos "Steel Wheels" e "BI Developer". Para você que tem interesse em colocar o Pentaho BI em produção, sem os projetos de testes, vamos mostrar em outra oportunidade, aqui no Sob Demanda, como desabilitar o "start" do HSQLDB e desinstalar os projetos de testes do ambiente.
Então, vamos iniciar.
Instalação SGDB PostgreSQL
Se você não estiver com o PostgreSQL instalado, utilize o post Instalação PostgreSQL 9.1 32 bits em distro Debian ou Ubuntu para fazê-lo. O objetivo é integrar o PostgreSQL 9.1 ao Pentaho Bi, más se você já tiver outra versão instalada, os procedimentos podem ser realizados sem problemas, lembrando que apenas o driver jdbc deve corresponder à versão de sua instalação.
Certifique que o serviço do postgres esteja em "up".
~# ps U postgres
PID TTY STAT TIME COMMAND
6915 pts/0 S 0:00 /opt/postgresql-9.1.0/bin/postmaster -D /opt/postgresql-9.1.0/pgdata
6923 ? Ss 0:00 postgres: logger process
6925 ? Ss 0:00 postgres: writer process
6926 ? Ss 0:00 postgres: wal writer process
6927 ? Ss 0:00 postgres: autovacuum launcher process
6928 ? Ss 0:00 postgres: stats collector process
Instalação dos bancos de dados
Crie a estrutura básica para o Pentaho BI no SGDB.
~# su - postgres
~$ psql -p 5432 -f /opt/pentaho/biserver-ce/data/postgresql/create_repository_postgresql.sql
psql:/opt/pentaho/biserver-ce/data/postgresql/create_repository_postgresql.sql:7: NOTICE: database "hibernate" does not exist, skipping
DROP DATABASE
psql:/opt/pentaho/biserver-ce/data/postgresql/create_repository_postgresql.sql:8: NOTICE: role "hibuser" does not exist, skipping
DROP ROLE
CREATE ROLE
CREATE DATABASE
GRANT
You are now connected to database "hibernate" as user "hibuser".
BEGIN
psql:/opt/pentaho/biserver-ce/data/postgresql/create_repository_postgresql.sql:20: NOTICE: table "datasource" does not exist, skipping
DROP TABLE
psql:/opt/pentaho/biserver-ce/data/postgresql/create_repository_postgresql.sql:22: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "datasource_pkey" for table "datasource"
CREATE TABLE
COMMIT
~$ psql -p 5432 -f /opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql
psql:/opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql:7: NOTICE: database "quartz" does not exist, skipping
DROP DATABASE
psql:/opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql:8: NOTICE: role "pentaho_user" does not exist, skipping
DROP ROLE
CREATE ROLE
CREATE DATABASE
GRANT
You are now connected to database "quartz" as user "pentaho_user".
BEGIN
psql:/opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql:20: NOTICE: table "qrtz_job_listeners" does not exist, skipping
DROP TABLE
psql:/opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql:21: NOTICE: table "qrtz_trigger_listeners" does not exist, skipping
DROP TABLE
psql:/opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql:22: NOTICE: table "qrtz_fired_triggers" does not exist, skipping
DROP TABLE
psql:/opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql:23: NOTICE: table "qrtz_paused_trigger_grps" does not exist, skipping
DROP TABLE
psql:/opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql:24: NOTICE: table "qrtz_scheduler_state" does not exist, skipping
DROP TABLE
psql:/opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql:25: NOTICE: table "qrtz_locks" does not exist, skipping
DROP TABLE
psql:/opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql:26: NOTICE: table "qrtz_simple_triggers" does not exist, skipping
DROP TABLE
psql:/opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql:27: NOTICE: table "qrtz_cron_triggers" does not exist, skipping
DROP TABLE
psql:/opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql:28: NOTICE: table "qrtz_blob_triggers" does not exist, skipping
DROP TABLE
psql:/opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql:29: NOTICE: table "qrtz_triggers" does not exist, skipping
DROP TABLE
psql:/opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql:30: NOTICE: table "qrtz_job_details" does not exist, skipping
DROP TABLE
psql:/opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql:31: NOTICE: table "qrtz_calendars" does not exist, skipping
DROP TABLE
psql:/opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql:45: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "qrtz_job_details_pkey" for table "qrtz_job_details"
CREATE TABLE
psql:/opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql:55: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "qrtz_job_listeners_pkey" for table "qrtz_job_listeners"
CREATE TABLE
psql:/opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql:77: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "qrtz_triggers_pkey" for table "qrtz_triggers"
CREATE TABLE
psql:/opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql:89: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "qrtz_simple_triggers_pkey" for table "qrtz_simple_triggers"
CREATE TABLE
psql:/opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql:100: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "qrtz_cron_triggers_pkey" for table "qrtz_cron_triggers"
CREATE TABLE
psql:/opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql:110: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "qrtz_blob_triggers_pkey" for table "qrtz_blob_triggers"
CREATE TABLE
psql:/opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql:120: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "qrtz_trigger_listeners_pkey" for table "qrtz_trigger_listeners"
CREATE TABLE
psql:/opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql:128: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "qrtz_calendars_pkey" for table "qrtz_calendars"
CREATE TABLE
psql:/opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql:135: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "qrtz_paused_trigger_grps_pkey" for table "qrtz_paused_trigger_grps"
CREATE TABLE
psql:/opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql:151: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "qrtz_fired_triggers_pkey" for table "qrtz_fired_triggers"
CREATE TABLE
psql:/opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql:160: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "qrtz_scheduler_state_pkey" for table "qrtz_scheduler_state"
CREATE TABLE
psql:/opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql:166: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "qrtz_locks_pkey" for table "qrtz_locks"
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
COMMIT
~$ psql -p 5432 -f /opt/pentaho/biserver-ce/data/postgresql/create_sample_datasource_postgresql.sql
You are now connected to database "hibernate" as user "hibuser".
BEGIN
INSERT 0 1
COMMIT
Verifique se os bancos foram criados com sucesso.
~$ psql -p 5432
psql (9.1.0)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+--------------+----------+-------------+-------------+-------------------------------
hibernate | hibuser | UTF8 | pt_BR.UTF-8 | pt_BR.UTF-8 | =Tc/hibuser +
| | | | | hibuser=CTc/hibuser
postgres | postgres | UTF8 | pt_BR.UTF-8 | pt_BR.UTF-8 |
quartz | pentaho_user | UTF8 | pt_BR.UTF-8 | pt_BR.UTF-8 | =Tc/pentaho_user +
| | | | | pentaho_user=CTc/pentaho_user
(3 rows)
Backup do sistema de arquivos
Pare os serviços do Pentaho Administration Console (PUC) e Pentaho Administration Console (PAC). Como é de costume, vamos considerar alguns parâmetros de posts anteriores, como o path de instalaçao a ser utilizado, na pasta "/opt/pentaho/", para refereiciar os procedimentos a seguir.
~# cd /opt/pentaho/administration-console
~# ./stop-pac.sh &
~# cd ../biserver-ce/
~# ./stop-pentaho.sh &
Em seguida, no sistema de arquivos, faça um backup da pasta "biserver-ce".
~# cd /opt/pentaho
~# cp -r biserver-ce biserver-ce.ori
Configurações
Utilizando um editor de sua preferência, altere os arquivos indicados a seguir.
- Arquivo: /opt/pentaho/biserver-ce/tomcat/webapps/pentaho/WEB-INF/web.xml
Inclua o path do repositório de soluções.
Alterar de:
<context-param>
<param-name>solution-path</param-name>
<param-value></param-value>
</context-param>
Para:
<context-param>
<param-name>solution-path</param-name>
<param-value>/opt/pentaho/biserver-ce/pentaho-solutions</param-value>
</context-param>
Altere a url de acesso utilizando o ip do host.
Altera de:
<context-param>
<param-name>fully-qualified-server-url</param-name>
<param-value>http://localhost:8080/pentaho/</param-value>
</context-param>
Para:
<context-param>
<param-name>fully-qualified-server-url</param-name>
<param-value>http://192.168.1.10:8080/pentaho/</param-value>
</context-param>
Exclua as informações de start do hiernate e do quartz no hsqldb, mantendo apenas o sampledata.
Alterar de:
<!-- [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] -->
Para:
<!-- [BEGIN HSQLDB DATABASES] -->
<context-param>
<param-name>hsqldb-databases</param-name>
<param-value>sampledata@../../data/hsqldb/sampledata</param-value>
</context-param>
<!-- [END HSQLDB DATABASES] -->
- Arquivo: /opt/pentaho/biserver-ce/pentaho-solutions/system/applicationContext-spring-security-jdbc.xml
Alterar de:
<!-- This is only for Hypersonic. Please update this section for any other database you are using -->
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="org.hsqldb.jdbcDriver" />
<property name="url"
value="jdbc:hsqldb:hsql://localhost:9001/hibernate" />
<property name="username" value="hibuser" />
<property name="password" value="password" />
</bean>
Para:
<!-- This is only for Hypersonic. Please update this section for any other database you are using -->
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="org.postgresql.Driver" />
<property name="url"
value="jdbc:postgresql://localhost:5432/hibernate" />
<property name="username" value="hibuser" />
<property name="password" value="password" />
</bean>
- Arquivo: /opt/pentaho/biserver-ce/pentaho-solutions/system/applicationContext-spring-security-hibernate.properties
Alterar de:
jdbc.driver=org.hsqldb.jdbcDriver
jdbc.url=jdbc:hsqldb:hsql://localhost:9001/hibernate
jdbc.username=hibuser
jdbc.password=password
hibernate.dialect=org.hibernate.dialect.HSQLDialect
Para:
jdbc.driver=org.postgresql.Driver
jdbc.url=jdbc:postgresql://localhost:5432/hibernate
jdbc.username=hibuser
jdbc.password=password
hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
- Arquivo: /opt/pentaho/biserver-ce/tomcat/webapps/pentaho/META-INF/context.xml
Alterar de:
<?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.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
maxWait="10000" username="hibuser" password="password"
driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/hibernate"
validationQuery="select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES" />
<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
maxWait="10000" username="pentaho_user" password="password"
driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/quartz"
validationQuery="select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES"/>
</Context>
Para:
<?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.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
maxWait="10000" username="hibuser" password="password"
driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate"
/>
<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
maxWait="10000" username="pentaho_user" password="password"
driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/quartz"
/>
</Context>
- Arquivo: /opt/pentaho/biserver-ce/tomcat/conf/Catalina/localhost/pentaho.xml
Repita os mesmos procedimentos adotados no arquivo anterior (context.xml).
- Arquivo: /opt/pentaho/biserver-ce/pentaho-solutions/system/quartz/quartz.properties
Na linha 300, alterar de:
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
Para:
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate
- Arquivo: /opt/pentaho/biserver-ce/pentaho-solutions/system/hibernate/hibernate-settings.xml
Alterar de:
<config-file>system/hibernate/hsql.hibernate.cfg.xml</config-file>
Para:
<config-file>system/hibernate/postgresql.hibernate.cfg.xml</config-file>
- Arquivo: /opt/pentaho/biserver-ce/pentaho-solutions/system/hibernate/postgresql.hibernate.cfg.xml
Apenas confira as configurações:
<property name="connection.driver_class">org.postgresql.Driver</property>
<property name="connection.url">jdbc:postgresql://localhost:5432/hibernate</property>
<property name="dialect">org.hibernate.dialect.PostgreSQLDialect</property>
<property name="connection.username">hibuser</property>
<property name="connection.password">password</property>
- Arquivo: /opt/pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
Alterar de:
SampleData/type=javax.sql.DataSource
SampleData/driver=org.hsqldb.jdbcDriver
SampleData/url=jdbc:hsqldb:hsql://localhost/sampledata
SampleData/user=pentaho_user
SampleData/password=password
Hibernate/type=javax.sql.DataSource
Hibernate/driver=org.hsqldb.jdbcDriver
Hibernate/url=jdbc:hsqldb:hsql://localhost/hibernate
Hibernate/user=hibuser
Hibernate/password=password
Quartz/type=javax.sql.DataSource
Quartz/driver=org.hsqldb.jdbcDriver
Quartz/url=jdbc:hsqldb:hsql://localhost/quartz
Quartz/user=pentaho_user
Quartz/password=password
Shark/type=javax.sql.DataSource
Shark/driver=org.hsqldb.jdbcDriver
Shark/url=jdbc:hsqldb:hsql://localhost/shark
Shark/user=sa
Shark/password=
SampleDataAdmin/type=javax.sql.DataSource
SampleDataAdmin/driver=org.hsqldb.jdbcDriver
SampleDataAdmin/url=jdbc:hsqldb:hsql://localhost/sampledata
SampleDataAdmin/user=pentaho_admin
SampleDataAdmin/password=password
Para:
SampleData/type=javax.sql.DataSource
SampleData/driver=org.hsqldb.jdbcDriver
SampleData/url=jdbc:hsqldb:hsql://localhost/sampledata
SampleData/user=pentaho_user
SampleData/password=password
Hibernate/type=javax.sql.DataSource
Hibernate/driver=org.postgresql.Driver
Hibernate/url=jdbc:postgresql://localhost:5432/hibernate
Hibernate/user=hibuser
Hibernate/password=password
Quartz/type=javax.sql.DataSource
Quartz/driver=org.postgresql.Driver
Quartz/url=jdbc:postgresql://localhost:5432/quartz
Quartz/user=pentaho_user
Quartz/password=password
Shark/type=javax.sql.DataSource
Shark/driver=org.hsqldb.jdbcDriver
Shark/url=jdbc:hsqldb:hsql://localhost/shark
Shark/user=sa
Shark/password=
SampleDataAdmin/type=javax.sql.DataSource
SampleDataAdmin/driver=org.hsqldb.jdbcDriver
SampleDataAdmin/url=jdbc:hsqldb:hsql://localhost/sampledata
SampleDataAdmin/user=pentaho_admin
SampleDataAdmin/password=password
Opcionalmente, poderão ser alterados nos arquivos, o usuário, senha e porta visando maior segurança no ambiente, e até mesmo a utilização de um SGDB em host diferente daquele no qual está instalado o serviço do Pentaho BI, apesar de não ser recomendado.
Driver JDBC
Para concluir, instale o driver jdbc do PostgreSQL. Neste momento, a instalação do driver é para atender à solução de BI e portanto vamos realizar a instalação na pasta "/opt/pentaho/biserver-ce/tomcat/lib". Para utilização de DW utilizando PostgreSQL, o driver deverá ser copiado para a pasta "/opt/pentaho/administration-console/jdbc/", de acordo com a versão do mesmo.
Em resumo:
Pasta "/opt/pentaho/biserver-ce/tomcat/lib": Driver jdbc da solução de BI.
Pasta "/opt/pentaho/administration-console/jdbc/": Drivers jdbc do(s) DW('s) a ser(em) acessado(s) pela solução.
Faça o download do driver postgresql 9.1 e copie para a pasta do PUC.
~# wget http://jdbc.postgresql.org/download/postgresql-9.1-901.jdbc3.jar -P /opt/pentaho/biserver-ce/tomcat/lib/
--2012-02-12 10:04:33-- http://jdbc.postgresql.org/download/postgresql-9.1-901.jdbc3.jar
Resolvendo jdbc.postgresql.org... 98.129.198.124
Conectando-se a jdbc.postgresql.org|98.129.198.124|:80... conectado.
A requisição HTTP foi enviada, aguardando resposta... 200 OK
Tamanho: 509746 (498K) [application/java-archive]
Salvando em: `/opt/pentaho/biserver-ce/tomcat/lib/postgresql-9.1-901.jdbc3.jar'
100%[===================================================================================================================>] 509.746 77,1K/s em 6,8s
2012-02-12 00:04:41 (73,3 KB/s) -`/opt/pentaho/biserver-ce/tomcat/lib/postgresql-9.1-901.jdbc3.jar' salvo [509746/509746]
Inicialização dos serviços
Inicie os serviços do PUC e o PAC.
~# cd /opt/pentaho/biserver-ce
~# ./start-pentaho.sh &
~# cd ../administration-console/
~# ./start-pac.sh &
Utilizando um browser de sua preferência, realize o teste.
Veja também:
No mais, é sob demanda.
Última atualização Fevereiro 22, 2012.
Arquivo a ser corrigido "/opt/pentaho/biserver-ce/tomcat/webapps/pentaho/adhoc/waqr.html".
Postagens
