1 简介
Pentaho是世界上最流行的开源商务智能软件,以工作流为核心的,强调面向解决方案而非工具组件的,基于java平台的商业智能(Business Intelligence,BI)套件BI,之所以说是套件是因为它包括一个web server平台和几个工具软件:报表,分析,图表,数据集成,数据挖掘等,可以说包括了商务智能的方方面面。它整合了多个开源项目,目标是和商业BI相抗衡。它偏向于与业务流程相结合的BI解决方案,侧重于大 中型企业应用。它允许商业分析人员或开发人员创建报表,仪表盘,分析模型,商业规则和 BI 流程。
注:以上感觉高大上,但笔者比较鄙视Pentaho这个项目,从布署的角度看,完全没有支撑该项目的标准文档,只有零星的资料,要部署好只能靠反复测试和摸索。
2 实践部分
2.1 环境部分
2.1.1 运行环境
IP Address = 10.168.0.76
HostName = pentaho.cmdschool.org
OS = CentOS 7.3
2.1.2 安装辅助工具
1
|
yum
install
-y unzip
|
2.1.3 配置防火墙
1
2
3
|
firewall-cmd --permanent --add-port 8080
/tcp
firewall-cmd --reload
firewall-cmd --list-all
|
2.2 MySQL配置
2.2.1 配置MySQL的源
1
|
vim
/etc/yum
.repos.d
/mysql56-community
.repo
|
输入如下内容:
1
2
3
4
5
6
7
|
# Enable to use MySQL 5.6
[mysql56-community]
name=MySQL 5.6 Community Server
baseurl=http:
//repo
.mysql.com
/yum/mysql-5
.6-community
/el/7/
$basearch/
enabled=1
gpgcheck=1
gpgkey=http:
//repo
.mysql.com
/RPM-GPG-KEY-mysql
|
2.2.2 安装相关包
1
|
yum
install
-y mysql-community-server mysql-community-devel mysql-community-client
|
2.2.3 启动并配置默认开机启动
1
2
|
systemctl start mysqld
systemctl
enable
mysqld
|
2.2.4 初始化数据库
1
|
mysql_secure_installation
|
向导如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
|
[...]
Set root password? [Y
/n
] y
New password:
Re-enter new password:
[...]
Remove anonymous
users
? [Y
/n
] y
[...]
Disallow root login remotely? [Y
/n
] n
[...]
Remove
test
database and access to it? [Y
/n
] y
[...]
Reload privilege tables now? [Y
/n
] y
[...]
|
2.3 Java配置
2.3.1 创建java目录
1
|
mkdir
/usr/java
|
2.3.2 下载JDK
1
2
|
cd
/usr/java
wget http:
//download
.oracle.com
/otn/java/jdk/8u121-b13/e9e7ea248e2c4826b92b3f075a80e441/jdk-8u121-linux-x64
.
tar
.gz?AuthParam=1495873209_eea482e2b59774918c970cf5dc383fdb
|
2.3.3 解压安装包
1
|
tar
-xf jdk-8u121-linux-x64.
tar
.gz
|
2.3.4 配置环境变量
1
|
vim
/etc/profile
|
末尾加入如下内容:
1
2
3
4
|
export
JAVA_HOME=
/usr/java/jdk1
.8.0_121
export
JRE_HOME=${JAVA_HOME}
/jre
export
CLASSPATH=.:${JAVA_HOME}
/lib
:${JRE_HOME}
/lib
export
PATH=${JAVA_HOME}
/bin
:$PATH
|
2.3.5 使环境变量生效
1
|
source
/etc/profile
|
2.3.6 测试环境变量
1
|
java -version
|
2.4 Pentaho安装包配置
2.4.1 下载程序包
1
2
3
4
5
|
mkdir
/usr/pentaho
cd
/usr/pentaho
wget https:
//nchc
.dl.sourceforge.net
/project/pentaho/Business
%20Intelligence%20Server
/7
.0
/pentaho-server-ce-7
.0.0.0-25.zip
wget https:
//nchc
.dl.sourceforge.net
/project/pentaho/Data
%20Integration
/7
.0
/pdi-ce-7
.0.0.0-25.zip
wget https:
//nchc
.dl.sourceforge.net
/project/pentaho/Report
%20Designer
/7
.0
/prd-ce-7
.0.0.0-25.zip
|
注:下载链接,https://sourceforge.net/projects/pentaho/files/
2.4.2 解压程序包
1
2
3
|
unzip pentaho-server-ce-7.0.0.0-25.zip
unzip pdi-ce-7.0.0.0-25.zip
unzip prd-ce-7.0.0.0-25.zip
|
2.4.3 更换mysql连接驱动
1
2
3
4
5
|
cd
~
wget https:
//dev
.mysql.com
/get/Downloads/Connector-J/mysql-connector-java-5
.1.41.
tar
.gz
tar
-xf mysql-connector-java-5.1.41.
tar
.gz
cp
mysql-connector-java-5.1.41
/mysql-connector-java-5
.1.41-bin.jar
/usr/pentaho/pentaho-server/tomcat/lib/
mv
/usr/pentaho/pentaho-server/tomcat/lib/mysql-connector-java-5
.1.17.jar ~/
|
注:下载链接,https://dev.mysql.com/downloads/connector/j/
2.4.4 修改Tomcat运行环境变量
1
|
vim
/usr/pentaho/pentaho-server/tomcat/bin/setenv
.sh
|
修改内容如下:
1
2
3
4
5
6
7
8
|
export
JAVA_HOME=
/usr/java/jdk1
.8.0_121
export
JRE_HOME=${JAVA_HOME}
/jre
export
CLASSPATH=.:${JAVA_HOME}
/lib
:${JRE_HOME}
/lib
export
PATH=${JAVA_HOME}
/bin
:$PATH
export
PENTAHO_JAVA_HOME=${JAVA_HOME}
export
INSTALL_HOME=
/usr/pentaho/pentaho-server
export
DI_HOME=${INSTALL_HOME}
/pentaho-solutions/system/kettle
export
CATALINA_OPTS=
"-Xms4096m -Xmx6144m -XX:MaxPermSize=256m -Dsun.rmi.dgc.client.gcInterval=3600000 -Dsun.rmi.dgc.server.gcInterval=3600000 -DDI_HOME=$DI_HOME"
|
2.4.5 创建程序运行用户
1
|
useradd
-s
/sbin/nologin
-b
/usr/pentaho
|
确认用户家目录
1
|
ls
-ld
/usr/pentaho
|
2.4.6 预配置esapi目录
1
|
mkdir
/usr/pentaho/esapi
|
2.4.7 配置目录权限
1
2
|
chown
-R pentaho:pentaho
/usr/pentaho/
chmod
-R 770
/usr/pentaho
|
2.5 数据库服务配置
2.5.1 修改quartz库的授权
1
|
vim
/usr/pentaho/pentaho-server/data/mysql5/create_quartz_mysql
.sql
|
注释掉如下行:
1
|
# grant all on quartz.* to 'pentaho_user'@'localhost' identified by 'password';
|
2.5.2 修改hibernate库的授权
1
|
vim
/usr/pentaho/pentaho-server/data/mysql5/create_repository_mysql
.sql
|
注释掉如下行:
1
|
# GRANT ALL ON hibernate.* TO 'hibuser'@'localhost' identified by 'password';
|
2.5.3 修改jackrabbit库的授权
1
|
vim
/usr/pentaho/pentaho-server/data/mysql5/create_jcr_mysql
.sql
|
注释掉如下行:
1
|
# grant all on jackrabbit.* to 'jcr_user'@'localhost' identified by 'password';
|
2.5.4 导入数据库
1
2
3
|
mysql -uroot -p <
/usr/pentaho/pentaho-server/data/mysql5/create_quartz_mysql
.sql
mysql -uroot -p <
/usr/pentaho/pentaho-server/data/mysql5/create_repository_mysql
.sql
mysql -uroot -p <
/usr/pentaho/pentaho-server/data/mysql5/create_jcr_mysql
.sql
|
2.5.5 配置数据库权限
1
2
3
4
5
|
mysql -uroot -p
grant all on quartz.* to
'pentaho'
@
'localhost'
identified by
'passwd'
;
grant all on hibernate.* to
'pentaho'
@
'localhost'
identified by
'passwd'
;
grant all on jackrabbit.* to
'pentaho'
@
'localhost'
identified by
'passwd'
;
flush privileges;
|
2.5.6 测试数据库
1
|
mysql -upentaho -ppasswd
|
2.6 迁移数据库等配置
2.6.1 配置Quartz
1
2
3
|
cd
/usr/pentaho/pentaho-server/
cp
pentaho-solutions
/system/quartz/quartz
.properties pentaho-solutions
/system/quartz/quartz
.properties.default
vim pentaho-solutions
/system/quartz/quartz
.properties
|
修改并启用如下参数:
1
2
|
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
org.quartz.dataSource.myDS.jndiURL = Quartz
|
2.6.2 配置hibernate数据源
1
2
3
|
cd
/usr/pentaho/pentaho-server/
cp
pentaho-solutions
/system/hibernate/mysql5
.hibernate.cfg.xml pentaho-solutions
/system/hibernate/mysql5
.hibernate.cfg.xml.default
vim pentaho-solutions
/system/hibernate/mysql5
.hibernate.cfg.xml
|
修改如下标签参数:
1
2
|
<property name=
"connection.username"
>pentaho<
/property
>
<property name=
"connection.password"
>
passwd
<
/property
>
|
2.6.3 配置Hibernate
1
2
3
|
cd
/usr/pentaho/pentaho-server/
cp
pentaho-solutions
/system/hibernate/hibernate-settings
.xml pentaho-solutions
/system/hibernate/hibernate-settings
.xml.default
vim pentaho-solutions
/system/hibernate/hibernate-settings
.xml
|
修改如下标签参数:
1
|
<config-
file
>system
/hibernate/mysql5
.hibernate.cfg.xml<
/config-file
>
|
2.6.4 配置审计日志
1
2
3
|
cd
/usr/pentaho/pentaho-server/
cp
pentaho-solutions
/system/audit_sql
.xml pentaho-solutions
/system/audit_sql
.xml.default
cp
pentaho-solutions
/system/dialects/mysql5/audit_sql
.xml pentaho-solutions
/system/
|
2.6.5 配置repository.xml
1
2
3
|
cd
/usr/pentaho/pentaho-server/
cp
pentaho-solutions
/system/jackrabbit/repository
.xml pentaho-solutions
/system/jackrabbit/repository
.xml.default
vim pentaho-solutions
/system/jackrabbit/repository
.xml
|
注解以下标签和内容:
1
2
3
|
<FileSystem class=
"org.apache.jackrabbit.core.fs.local.LocalFileSystem"
>
<param name=
"path"
value=
"${rep.home}/repository"
/>
<
/FileSystem
>
|
去掉以下标签和内容注解并修改账号密码:
1
2
3
4
5
6
7
8
|
<FileSystem class=
"org.apache.jackrabbit.core.fs.db.DbFileSystem"
>
<param name=
"driver"
value=
"com.mysql.jdbc.Driver"
/>
<param name=
"url"
value=
"jdbc:mysql://localhost:3306/jackrabbit"
/>
<param name=
"user"
value=
"pentaho"
/>
<param name=
"password"
value=
"passwd"
/>
<param name=
"schema"
value=
"mysql"
/>
<param name=
"schemaObjectPrefix"
value=
"fs_repos_"
/>
<
/FileSystem
>
|
注解以下标签和内容:
1
|
<DataStore class=
"org.apache.jackrabbit.core.data.FileDataStore"
/>
|
去掉以下标签和内容注解并修改账号密码:
1
2
3
4
5
6
7
8
9
10
11
12
|
<DataStore class=
"org.apache.jackrabbit.core.data.db.DbDataStore"
>
<param name=
"url"
value=
"jdbc:mysql://localhost:3306/jackrabbit"
/>
<param name=
"user"
value=
"pentaho"
/>
<param name=
"password"
value=
"passwd"
/>
<param name=
"databaseType"
value=
"mysql"
/>
<param name=
"driver"
value=
"com.mysql.jdbc.Driver"
/>
<param name=
"minRecordLength"
value=
"1024"
/>
<param name=
"maxConnections"
value=
"3"
/>
<param name=
"copyWhenReading"
value=
"true"
/>
<param name=
"tablePrefix"
value=
""
/>
<param name=
"schemaObjectPrefix"
value=
"ds_repos_"
/>
<
/DataStore
>
|
注解以下标签和内容:
1
2
3
|
<FileSystem class=
"org.apache.jackrabbit.core.fs.local.LocalFileSystem"
>
<param name=
"path"
value=
"${wsp.home}"
/>
<
/FileSystem
>
|
去掉以下标签和内容注解并修改账号密码:
1
2
3
4
5
6
7
8
|
<FileSystem class=
"org.apache.jackrabbit.core.fs.db.DbFileSystem"
>
<param name=
"driver"
value=
"com.mysql.jdbc.Driver"
/>
<param name=
"url"
value=
"jdbc:mysql://localhost:3306/jackrabbit"
/>
<param name=
"user"
value=
"pentaho"
/>
<param name=
"password"
value=
"passwd"
/>
<param name=
"schema"
value=
"mysql"
/>
<param name=
"schemaObjectPrefix"
value=
"fs_ws_"
/>
<
/FileSystem
>
|
注解以下标签和内容:
1
2
3
4
|
<PersistenceManager class=
"org.apache.jackrabbit.core.persistence.pool.H2PersistenceManager"
>
<param name=
"url"
value=
"jdbc:h2:${wsp.home}/db"
/>
<param name=
"schemaObjectPrefix"
value=
"${wsp.name}_"
/>
<
/PersistenceManager
>
|
去掉以下标签和内容注解并修改账号密码:
1
2
3
4
5
6
7
8
|
<PersistenceManager class=
"org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager"
>
<param name=
"driver"
value=
"com.mysql.jdbc.Driver"
/>
<param name=
"url"
value=
"jdbc:mysql://localhost:3306/jackrabbit"
/>
<param name=
"user"
value=
"pentaho"
/>
<param name=
"password"
value=
"passwd"
/>
<param name=
"schema"
value=
"mysql"
/>
<param name=
"schemaObjectPrefix"
value=
"${wsp.name}_pm_ws_"
/>
<
/PersistenceManager
>
|
注解以下标签和内容:
1
2
3
|
<FileSystem class=
"org.apache.jackrabbit.core.fs.local.LocalFileSystem"
>
<param name=
"path"
value=
"${rep.home}/version"
/>
<
/FileSystem
>
|
去掉以下标签和内容注解并修改账号密码:
1
2
3
4
5
6
7
8
|
<FileSystem class=
"org.apache.jackrabbit.core.fs.db.DbFileSystem"
>
<param name=
"driver"
value=
"com.mysql.jdbc.Driver"
/>
<param name=
"url"
value=
"jdbc:mysql://localhost:3306/jackrabbit"
/>
<param name=
"user"
value=
"pentaho"
/>
<param name=
"password"
value=
"passwd"
/>
<param name=
"schema"
value=
"mysql"
/>
<param name=
"schemaObjectPrefix"
value=
"fs_ver_"
/>
<
/FileSystem
>
|
注解以下标签和内容:
1
2
3
4
|
<PersistenceManager class=
"org.apache.jackrabbit.core.persistence.pool.H2PersistenceManager"
>
<param name=
"url"
value=
"jdbc:h2:${rep.home}/version/db"
/>
<param name=
"schemaObjectPrefix"
value=
"version_"
/>
<
/PersistenceManager
>
|
去掉以下标签和内容注解并修改账号密码:
1
2
3
4
5
6
7
8
|
<PersistenceManager class=
"org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager"
>
<param name=
"driver"
value=
"com.mysql.jdbc.Driver"
/>
<param name=
"url"
value=
"jdbc:mysql://localhost:3306/jackrabbit"
/>
<param name=
"user"
value=
"pentaho"
/>
<param name=
"password"
value=
"passwd"
/>
<param name=
"schema"
value=
"mysql"
/>
<param name=
"schemaObjectPrefix"
value=
"pm_ver_"
/>
<
/PersistenceManager
>
|
2.6.6 配置Tomcat的JDBC连接信息context.xml
1
2
3
|
cd
/usr/pentaho/pentaho-server/
cp
tomcat
/webapps/pentaho/META-INF/context
.xml tomcat
/webapps/pentaho/META-INF/context
.xml.default
vim tomcat
/webapps/pentaho/META-INF/context
.xml
|
修以下配置:
1
2
3
4
5
6
7
|
maxWaitMillis=
"10000"
username=
"pentaho"
password=
"passwd"
driverClassName=
"com.mysql.jdbc.Driver"
url=
"jdbc:mysql://localhost/hibernate"
validationQuery=
"select 1"
/>
maxWaitMillis=
"10000"
username=
"pentaho"
password=
"passwd"
driverClassName=
"com.mysql.jdbc.Driver"
url=
"jdbc:mysql://localhost/quartz"
validationQuery=
"select 1"
/>
|
2.6.7 配置web.xml
1
2
3
|
cd
/usr/pentaho/pentaho-server
cp
tomcat
/webapps/pentaho/WEB-INF/web
.xml tomcat
/webapps/pentaho/WEB-INF/web
.xml.default
vim tomcat
/webapps/pentaho/WEB-INF/web
.xml
|
删除以下标签和配置:
1
2
3
4
5
6
|
<!-- [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] -->
|
删除以下标签和配置:
1
2
3
4
5
|
<!-- [BEGIN HSQLDB STARTER] -->
<listener>
<listener-class>org.pentaho.platform.web.http.context.HsqldbStartupListener<
/listener-class
>
<
/listener
>
<!-- [END HSQLDB STARTER] -->
|
2.6.8 配置applicationContext-spring-security-hibernate.properties(可选)
1
2
3
|
cd
/usr/pentaho/pentaho-server/
cp
pentaho-solutions
/system/applicationContext-spring-security-hibernate
.properties pentaho-solutions
/system/applicationContext-spring-security-hibernate
.properties.defautl
vim pentaho-solutions
/system/applicationContext-spring-security-hibernate
.properties
|
修改如下参数:
1
2
3
4
5
|
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql:
//localhost
:3306
/hibernate
jdbc.username=pentaho
jdbc.password=
passwd
hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
|
2.6.9 配置applicationContext-spring-security-jdbc.properties(可选)
1
2
3
|
cd
/usr/pentaho/pentaho-server/
cp
pentaho-solutions
/system/applicationContext-spring-security-jdbc
.properties pentaho-solutions
/system/applicationContext-spring-security-jdbc
.properties.default
vim pentaho-solutions
/system/applicationContext-spring-security-jdbc
.properties
|
修改如下参数:
1
2
3
4
5
|
datasource.driver.classname=com.mysql.jdbc.Driver
datasource.url=jdbc:mysql:
//localhost
:3306
/hibernate
datasource.username=pentaho
datasource.password=
passwd
datasource.validation.query=SELECT 1
|
2.6.10 配置jdbc.properties(可选)
1
2
3
|
cd
/usr/pentaho/data-integration
cp
simple-jndi
/jdbc
.properties simple-jndi
/jdbc
.properties.default
vim simple-jndi
/jdbc
.properties
|
注释掉原来参数并增加如下参数且修改账号密码:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
SampleData
/type
=javax.sql.DataSource
SampleData
/driver
=com.mysql.jdbc.Driver
SampleData
/url
=jdbc:mysql:
//localhost
:3306
/hibernate
SampleData
/user
=pentaho
SampleData
/password
=
passwd
Hibernate
/type
=javax.sql.DataSource
Hibernate
/driver
=com.mysql.jdbc.Driver
Hibernate
/url
=jdbc:mysql:
//localhost
:3306
/hibernate
Hibernate
/user
=pentaho
Hibernate
/password
=
passwd
Quartz
/type
=javax.sql.DataSource
Quartz
/driver
=com.mysql.jdbc.Driver
Quartz
/url
=jdbc:mysql:
//localhost
:3306
/quartz
Quartz
/user
=pentaho
Quartz
/password
=
passwd
Shark
/type
=javax.sql.DataSource
Shark
/driver
=com.mysql.jdbc.Driver
Shark
/url
=jdbc:mysql:
//localhost
:3306
/hbibernate
Shark
/user
=pentaho
Shark
/password
=
passwd
SampleDataAdmin
/type
=javax.sql.DataSource
SampleDataAdmin
/driver
=com.mysql.jdbc.Driver
SampleDataAdmin
/url
=jdbc:mysql:
//localhost
:3306
/hibernate
SampleDataAdmin
/user
=pentaho
SampleDataAdmin
/password
=
passwd
|
2.6.11 配置pentaho.xml(可选)
1
2
3
|
cd
/usr/pentaho/pentaho-server/
cp
pentaho-solutions
/system/pentaho
.xml pentaho-solutions
/system/pentaho
.xml.defautl
vim pentaho-solutions
/system/pentaho
.xml
|
修改如下配置:
1
2
|
<login-show-
users
-list>
false
<
/login-show-users-list
>
<login-show-sample-
users
-hint>
false
<
/login-show-sample-users-hint
>
|
注解或删除如下配置:
1
2
3
4
5
6
7
8
9
10
11
12
13
|
<sampledata-datasource>
<name>SampleData<
/name
>
<host>localhost<
/host
>
<
type
>Hypersonic<
/type
>
<port>9001<
/port
>
<access>NATIVE<
/access
>
<username>pentaho_user<
/username
>
<password>password<
/password
>
<max-active>20<
/max-active
>
<max-idle>5<
/max-idle
>
<max-wait>1000<
/max-wait
>
<query>
select
count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES<
/query
>
<
/sampledata-datasource
>
|
2.7 测试和配置服务启动
2.7.1 测试服务启动
1
|
sudo
-u pentaho
/usr/pentaho/pentaho-server/tomcat/bin/startup
.sh
|
2.7.2 监视日志排错
建议监控如下日志:
1
|
tail
-f
/usr/pentaho/pentaho-server/tomcat/logs/catalina
.out
|
建议搜索如下日志:
1
|
grep
-i -E
"error|fail"
/usr/pentaho/pentaho-server/tomcat/logs/catalina
.out |
sort
-u
|
2.7.3 网页测试