参考文档:
https://www.cnblogs.com/chinas/p/6544981.html
Pentaho Server 默认内置的是HSQLDB数据库,对于大型生产环境及后续的运维不便利,因此将数据库切换到MYSQL。
本文以阿里云RDS(mysql)为目标数据库进行切换。
一、导入数据库文件
1、迁移用脚本
pentaho-server项目已经带了迁移mysql用的数据库脚本。
cd /opt/ptools/pentaho-server/data/mysql5/
create_jcr_mysql.sql :创建jackrabbit数据库,用于存储ETL运行代码等资源。
create_quartz_mysql.sql :创建quartz数据库,为Quartz计划任务器创建资源库。
create_repository_mysql.sql :创建hibernate 数据库,用于存储用户授权认证,solution repository以及数据源。
注: 以上三个脚本会创建三个数据库hibernate、quartz、jackrabbit 和对应的三个用户hibuser、pentaho_user、jcr_user 密码都为password如果在这里你想将密码改成自己的那么以下配置时将对应密码改成你修改的密码即可。
※在此版本中缺少了sample_data脚本,可以从5.4之前的版本的包中找到,不过正式系统不需要。
2、基于阿里云RDS创建数据库
因我使用的阿里云RDS没有创建高权账号,因此需手工创建数据库及数据库用户。因此打开三个脚本分析后自己创建。看到脚本里面使用latin1编码,专门去查了一下官方明确说UTF-8有问题
参考官方文档:https://help.pentaho.com/Documentation/8.1/Setup/Installation/Archive/MySQL_Repository
官方说明:Use the ASCII character set when you run these scripts. Do not use UTF-8 because there are text string length limitations that might cause the scripts to fail.
需要匹配UTF-8的看这篇文章(已验证)《MySQL上使用utf8编码安装Pentaho》
2.1create_jcr_mysql.sql
脚本只创建了库及对应的账号。
CREATE DATABASE IF NOT EXISTS `jackrabbit` DEFAULT CHARACTER SET utf8;
grant all on jackrabbit.* to 'jcr_user'@'localhost' identified by 'password';
commit;
2.2create_repository_mysql.sql
脚本只创建了库及对应的账号。
CREATE DATABASE IF NOT EXISTS `hibernate` DEFAULT CHARACTER SET utf8;
USE hibernate;
GRANT ALL ON hibernate.* TO 'hibuser'@'localhost' identified by 'password';
commit;
2.3create_quartz_mysql.sql
脚本创建了库及对应的账号。然后创建了表。
#
# Quartz seems to work best with the driver mm.mysql-2.0.7-bin.jar
#
# In your Quartz properties file, you'll need to set
# org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
#
CREATE DATABASE IF NOT EXISTS `quartz` DEFAULT CHARACTER SET utf8;
grant all on quartz.* to 'pentaho_user'@'localhost' identified by 'password';
USE `quartz`;
DROP TABLE IF EXISTS QRTZ5_JOB_LISTENERS;
DROP TABLE IF EXISTS QRTZ5_TRIGGER_LISTENERS;
DROP TABLE IF EXISTS QRTZ5_FIRED_TRIGGERS;
DROP TABLE IF EXISTS QRTZ5_PAUSED_TRIGGER_GRPS;
DROP TABLE IF EXISTS QRTZ5_SCHEDULER_STATE;
DROP TABLE IF EXISTS QRTZ5_LOCKS;
DROP TABLE IF EXISTS QRTZ5_SIMPLE_TRIGGERS;
DROP TABLE IF EXISTS QRTZ5_CRON_TRIGGERS;
DROP TABLE IF EXISTS QRTZ5_BLOB_TRIGGERS;
DROP TABLE IF EXISTS QRTZ5_TRIGGERS;
DROP TABLE IF EXISTS QRTZ5_JOB_DETAILS;
DROP TABLE IF EXISTS QRTZ5_CALENDARS;
CREATE TABLE QRTZ5_JOB_DETAILS
(
JOB_NAME VARCHAR(200) NOT NULL,
JOB_GROUP VARCHAR(200) NOT NULL,
DESCRIPTION VARCHAR(250) NULL,
JOB_CLASS_NAME VARCHAR(250) NOT NULL,
IS_DURABLE VARCHAR(1) NOT NULL,
IS_VOLATILE VARCHAR(1) NOT NULL,
IS_STATEFUL VARCHAR(1) NOT NULL,
REQUESTS_RECOVERY VARCHAR(1) NOT NULL,
JOB_DATA BLOB NULL,
PRIMARY KEY (JOB_NAME,JOB_GROUP)
);
CREATE TABLE QRTZ5_JOB_LISTENERS
(
JOB_NAME VARCHAR(200) NOT NULL,
JOB_GROUP VARCHAR(200) NOT NULL,
JOB_LISTENER VARCHAR(200) NOT NULL,
PRIMARY KEY (JOB_NAME,JOB_GROUP,JOB_LISTENER),
FOREIGN KEY (JOB_NAME,JOB_GROUP)
REFERENCES QRTZ5_JOB_DETAILS(JOB_NAME,JOB_GROUP)
);
CREATE TABLE QRTZ5_TRIGGERS
(
TRIGGER_NAME VARCHAR(200) NOT NULL,
TRIGGER_GROUP VARCHAR(200) NOT NULL,
JOB_NAME VARCHAR(200) NOT NULL,
JOB_GROUP VARCHAR(200) NOT NULL,
IS_VOLATILE VARCHAR(1) NOT NULL,
DESCRIPTION VARCHAR(250) NULL,
NEXT_FIRE_TIME BIGINT(13) NULL,
PREV_FIRE_TIME BIGINT(13) NULL,
PRIORITY INTEGER NULL,
TRIGGER_STATE VARCHAR(16) NOT NULL,
TRIGGER_TYPE VARCHAR(8) NOT NULL,
START_TIME BIGINT(13) NOT NULL,
END_TIME BIGINT(13) NULL,
CALENDAR_NAME VARCHAR(200) NULL,
MISFIRE_INSTR SMALLINT(2) NULL,
JOB_DATA BLOB NULL,
PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP),
FOREIGN KEY (JOB_NAME,JOB_GROUP)
REFERENCES QRTZ5_JOB_DETAILS(JOB_NAME,JOB_GROUP)
);
CREATE TABLE QRTZ5_SIMPLE_TRIGGERS
(
TRIGGER_NAME VARCHAR(200) NOT NULL,
TRIGGER_GROUP VARCHAR(200) NOT NULL,
REPEAT_COUNT BIGINT(7) NOT NULL,
REPEAT_INTERVAL BIGINT(12) NOT NULL,
TIMES_TRIGGERED BIGINT(10) NOT NULL,
PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP),
FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)
REFERENCES QRTZ5_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP)
);
CREATE TABLE QRTZ5_CRON_TRIGGERS
(
TRIGGER_NAME VARCHAR(200) NOT NULL,
TRIGGER_GROUP VARCHAR(200) NOT NULL,
CRON_EXPRESSION VARCHAR(200) NOT NULL,
TIME_ZONE_ID VARCHAR(80),
PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP),
FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)
REFERENCES QRTZ5_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP)
);
CREATE TABLE QRTZ5_BLOB_TRIGGERS
(
TRIGGER_NAME VARCHAR(200) NOT NULL,
TRIGGER_GROUP VARCHAR(200) NOT NULL,
BLOB_DATA BLOB NULL,
PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP),
FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)
REFERENCES QRTZ5_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP)
);
CREATE TABLE QRTZ5_TRIGGER_LISTENERS
(
TRIGGER_NAME VARCHAR(200) NOT NULL,
TRIGGER_GROUP VARCHAR(200) NOT NULL,
TRIGGER_LISTENER VARCHAR(200) NOT NULL,
PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_LISTENER),
FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)
REFERENCES QRTZ5_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP)
);
CREATE TABLE QRTZ5_CALENDARS
(
CALENDAR_NAME VARCHAR(200) NOT NULL,
CALENDAR BLOB NOT NULL,
PRIMARY KEY (CALENDAR_NAME)
);
CREATE TABLE QRTZ5_PAUSED_TRIGGER_GRPS
(
TRIGGER_GROUP VARCHAR(200) NOT NULL,
PRIMARY KEY (TRIGGER_GROUP)
);
CREATE TABLE QRTZ5_FIRED_TRIGGERS
(
ENTRY_ID VARCHAR(95) NOT NULL,
TRIGGER_NAME VARCHAR(200) NOT NULL,
TRIGGER_GROUP VARCHAR(200) NOT NULL,
IS_VOLATILE VARCHAR(1) NOT NULL,
INSTANCE_NAME VARCHAR(200) NOT NULL,
FIRED_TIME BIGINT(13) NOT NULL,
PRIORITY INTEGER NOT NULL,
STATE VARCHAR(16) NOT NULL,
JOB_NAME VARCHAR(200) NULL,
JOB_GROUP VARCHAR(200) NULL,
IS_STATEFUL VARCHAR(1) NULL,
REQUESTS_RECOVERY VARCHAR(1) NULL,
PRIMARY KEY (ENTRY_ID)
);
CREATE TABLE QRTZ5_SCHEDULER_STATE
(
INSTANCE_NAME VARCHAR(200) NOT NULL,
LAST_CHECKIN_TIME BIGINT(13) NOT NULL,
CHECKIN_INTERVAL BIGINT(13) NOT NULL,
PRIMARY KEY (INSTANCE_NAME)
);
CREATE TABLE QRTZ5_LOCKS
(
LOCK_NAME VARCHAR(40) NOT NULL,
PRIMARY KEY (LOCK_NAME)
);
INSERT INTO QRTZ5_LOCKS values('TRIGGER_ACCESS');
INSERT INTO QRTZ5_LOCKS values('JOB_ACCESS');
INSERT INTO QRTZ5_LOCKS values('CALENDAR_ACCESS');
INSERT INTO QRTZ5_LOCKS values('STATE_ACCESS');
INSERT INTO QRTZ5_LOCKS values('MISFIRE_ACCESS');
commit;
2.4手工创建阿里云RDS数据库,修改了数据库名称和用户名称。
2.5使用脚本创建quartz库的表。
因前一步已经创建了库及账号,使用脚本,跳过创库及账号部分直接创表。
二、添加MySQL驱动
复制mysql的驱动包到 /opt/ptools/pentaho-server/tomcat/lib/ 目录下
复制mysql的驱动包到 /opt/ptools/pentaho-server/tomcat/webapps/pentaho/WEB-INF/lib/ 目录下
三、配置文件修改
1、/opt/ptools/pentaho-server/pentaho-solutions/system/quartz/目录下
(1)quartz.properties
#类似于Hibernate的dialect,用于处理DB之间的差异,StdJDBCDelegate能满足大部分的DB
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
可选参数,留存备查
org.quartz.impl.jdbcjobstore.StdJDBCDelegate(用于完全符合JDBC的驱动程序)
org.quartz.impl.jdbcjobstore.MSSQLDelegate(对于Microsoft SQL Server和Sybase)
org.quartz.impl.jdbcjobstore.PostgreSQLDelegate
org.quartz.impl.jdbcjobstore.WebLogicDelegate(对于WebLogic驱动程序)
org.quartz.impl.jdbcjobstore.oracle.OracleDelegate
org.quartz.impl.jdbcjobstore.oracle.WebLogicOracleDelegate(对于Weblogic中使用的Oracle驱动程序)
org.quartz.impl.jdbcjobstore.oracle.weblogic.WebLogicOracleDelegate(对于在Weblogic中使用的Oracle驱动程序)
org.quartz.impl.jdbcjobstore.CloudscapeDelegate
org.quartz.impl.jdbcjobstore.DB2v6Delegate
org.quartz.impl.jdbcjobstore.DB2v7Delegate
org.quartz.impl.jdbcjobstore.DB2v8Delegate
org.quartz.impl.jdbcjobstore.HSQLDBDelegate
org.quartz.impl.jdbcjobstore.PointbaseDelegate
org.quartz.impl.jdbcjobstore.SybaseDelegate
2、/opt/ptools/pentaho-server/pentaho-solutions/system/hibernate/ 目录下
(1)hibernate-settings.xml
注释原有hsql配置,修改配置文件为 MySQL 配置
<!-- <config-file>system/hibernate/hsql.hibernate.cfg.xml</config-file> -->
<config-file>system/hibernate/mysql5.hibernate.cfg.xml</config-file>
(2)mysql5.hibernate.cfg.xml
修改MYSQL配置
<!-- MySQL Configuration -->
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="connection.url">jdbc:mysql://localhost:3306/hibernate</property>
<property name="dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>
<property name="connection.username">hibuser</property>
<property name="connection.password">password</property>
<property name="connection.pool_size">10</property>
<property name="show_sql">false</property>
<property name="hibernate.jdbc.use_streams_for_binary">true</property>
3、/opt/ptools/pentaho-server/pentaho-solutions/system/jackrabbit/目录下
(1)repository.xml
根据下表,定位并更改代码,使MySQL行不被注释掉,而PostgreSQL、MS SQL Server和Oracle行被注释掉。
Item: | Code Section: |
---|---|
Repository |
|
DataStore | |
Workspaces | |
PersistenceManager (1st part) |
|
Versioning |
|
PersistenceManager (2nd part) | |
DatabaseJournal | |
(2)重要:需注释掉上表对应的本地路径配置
<!-- <FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
<param name="path" value="${rep.home}/repository"/>
</FileSystem>
-->
<!-- <DataStore class="org.apache.jackrabbit.core.data.FileDataStore"/> -->
<!-- <FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
<param name="path" value="${wsp.home}"/>
</FileSystem> -->
<!-- <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> -->
<!-- <FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
<param name="path" value="${rep.home}/version" />
</FileSystem> -->
<!-- <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> -->
<!-- <Journal class="org.apache.jackrabbit.core.journal.MemoryJournal"/> -->
4、/opt/ptools/pentaho-server/pentaho-solutions/system/ 目录下
(1)applicationContext-spring-security-jdbc.properties
用#注释HSQLDB 配置 ,新增MySQL 配置
datasource.driver.classname=com.mysql.jdbc.Driver
datasource.url=jdbc:mysql://localhost:3306/hibernate #(改成实际的数据库实例地址及数据库名称)
datasource.username=hibuser #(改成实际可以访问这个数据库的用户名)
datasource.password=password #(改成与用户名匹配的密码)
datasource.validation.query=SELECT USER()
(2)applicationContext-spring-security-hibernate.properties
用#注释HSQLDB 配置 ,新增MySQL 配置
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/hibernate #(改成实际的数据库实例地址及数据库名称)
jdbc.username=hibuser #(改成实际可以访问这个数据库的用户名)
jdbc.password=password #(改成与用户名匹配的密码)
hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
(3)systemListeners.xml
<!--
<bean id="nonPooledDataSourceSystemListener" class="org.pentaho.platform.engine.services.connection.datasource.dbcp.NonPooledDatasourceSystemListener"/>
-->
5、/opt/ptools/pentaho-server/pentaho-solutions/system/simple-jndi/目录下
(1)jdbc.properties
注释HSQL 配置,添加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 Shark/type=javax.sql.DataSource Shark/driver=com.mysql.jdbc.Driver Shark/url=jdbc:mysql://localhost:3306/shark Shark/user=sa Shark/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
四、关闭示例库配置
1、/opt/ptools/pentaho-server/pentaho-solutions/system/ 目录下
(1) pentaho.xml
关闭前台显示用户列表
<login-show-users-list>false</login-show-users-list>
<login-show-sample-users-hint>false</login-show-sample-users-hint>
注释测试数据
<!--
<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> -->
五、修改tomcat配置
1、/opt/ptools/pentaho-server/tomcat/webapps/pentaho/
(1)META-INF/context.xml
<?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="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/hibernate"
validationQuery="select user()" />
<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="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/quartz"
validationQuery="select user()"/>
-->
<Resource validationQuery="select 1" url="jdbc:mysql://localhost:3306/hibernate" driverClassName="com.mysql.jdbc.Driver" password="password" username="hibuser" initialSize="0" maxActive="20" maxIdle="10" maxWait="10000" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/Hibernate"/>
<Resource validationQuery="select 1" url="jdbc:mysql://localhost:3306/hibernate" driverClassName="com.mysql.jdbc.Driver" password="password" username="hibuser" initialSize="0" maxActive="20" maxIdle="10" maxWait="10000" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/Audit"/>
<Resource validationQuery="select 1" url="jdbc:mysql://localhost:3306/quartz" driverClassName="com.mysql.jdbc.Driver" password="password" username="pentaho_user" initialSize="0" maxActive="20" maxIdle="10" maxWait="10000" maxTotal="20" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/Quartz"/>
<Resource validationQuery="select 1" url="jdbc:mysql://localhost:3306/pentaho_operations_mart" driverClassName="com.mysql.jdbc.Driver" password="password" username="hibuser" initialSize="0" maxActive="20" maxIdle="10" maxWait="10000" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/pentaho_operations_mart"/>
<Resource validationQuery="select 1" url="jdbc:mysql://localhost:3306/pentaho_operations_mart" driverClassName="com.mysql.jdbc.Driver" password="password" username="hibuser" initialSize="0" maxActive="20" maxIdle="10" maxWait="10000" maxTotal="20" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/PDI_Operations_Mart"/>
</Context>
(2)WEB-INF/web.xml
关闭HSQLDB 数据库
<!-- [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] -->
(3)JDBC驱动
下载与数据库匹配的mysql-jdbc驱动,我的是mysql-connector-java-5.1.47.jar,放入下列两个路径
/opt/ptools/pentaho-server/tomcat/lib/
/opt/ptools/pentaho-server/tomcat/webapps/pentaho/WEB-INF/lib/
(4)删除缓存
配置完成后删除缓存,保障运行正常
/opt/ptools/pentaho-server/pentaho-solutions/system/jackrabbit/repository
/opt/ptools/pentaho-server/tomcat/conf/Catalina
/opt/ptools/pentaho-server/tomcat/work/*
/opt/ptools/pentaho-server/tomcat/temp/*
六、启动bi Server
启动,并监控启动日志。如有报错百度查找原因。
sh /opt/ptools/pentaho-server/start-pentaho.sh
tail -f /opt/ptools/pentaho-server/tomcat/logs/catalina.out
打开浏览器,http://localhost:8080/pentaho/
admin/password