Pentaho BIServer Community Edtion 6.1 使用教程 第二篇 迁移元数据 [HSQLDB TO MySQL]

发布时间:2017-3-27 8:52:27 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"Pentaho BIServer Community Edtion 6.1 使用教程 第二篇 迁移元数据 [HSQLDB TO MySQL] ",主要涉及到Pentaho BIServer Community Edtion 6.1 使用教程 第二篇 迁移元数据 [HSQLDB TO MySQL] 方面的内容,对于Pentaho BIServer Community Edtion 6.1 使用教程 第二篇 迁移元数据 [HSQLDB TO MySQL] 感兴趣的同学可以参考一下。

第一部分  迁移原因

Pentaho BI 社区版服务的很多元数据信息存储在数据库汇总,其默认使用HSQLDB 数据库,即借助它存储自身的资料库,比如 Quartz 调度信息、业务资料库连接信息(数据源)等。HSQLDB是不能够支撑真实的企业应用的。生产环境必须替换它,因此我们需要将HSQLDB迁移至MySQL

第二部分 迁移步骤

1 创建MySQL 数据库

数据库脚本存放在 ${biserver-ce}/data/mysql5下

Image

服务器本地登录MySQL(脚本内IP使用 localhost)

执行以下脚本

source /opt/ptools/biserver-ce/data/mysql5/create_jcr_mysql.sql

创建:

Image

source /opt/ptools/biserver-ce/data/mysql5/create_quartz_mysql.sql

Image

source /opt/ptools/biserver-ce/data/mysql5/create_repository_mysql.sql

Image

2  给 pentaho 添加 MySQL JDBC 文件

mysql-connector-java-5.1.32-bin.jar

放在 /opt/ptools/biserver-ce/tomcat/lib/ 目录下

3 配置

3 –1 /opt/ptools/biserver-ce/pentaho-solutions/system 目录下

(1) pentaho.xml 

关闭前台显示用户列表
<login-show-users-list>false</login-show-users-list>

image

注释测试数据
<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>

image

(2)applicationContext-spring-security-jdbc.properties

注释HSQLDB 配置 ,新增MySQL 配置

datasource.driver.classname=com.mysql.jdbc.Driver

image

datasource.url=jdbc:mysql://localhost:3306/hibernate

image

datasource.username=hibuser

image

datasource.password=password

image

datasource.validation.query=SELECT 1
image

(3)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.MySQL5Dialect
image

(4)hibernate/hibernate-settings.xml

修改配置文件为 MySQL 配置

<config-file>system/hibernate/mysql5.hibernate.cfg.xml</config-file>

image

(5)quartz/quartz.properties 

org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate

image

(6)jackrabbit/repository.xml

注释内容:
<FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
    <param name="path" value="${rep.home}/repository"/>
</FileSystem>

image
取消注释:
<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="jcr_user"/>
    <param name="password" value="password"/>
    <param name="schema" value="mysql"/>
    <param name="schemaObjectPrefix" value="fs_repos_"/>
</FileSystem>

image

注释内容:

<DataStore class="org.apache.jackrabbit.core.data.FileDataStore"/>
image

取消注释:
<DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
    <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>
    <param name="user" value="jcr_user"/>
    <param name="password" value="password"/>
    <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>

image
      

注释内容:
<FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
    <param name="path" value="${wsp.home}"/>
</FileSystem>

image
取消注释:
<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="jcr_user"/>
    <param name="password" value="password"/>
    <param name="schema" value="mysql"/>
    <param name="schemaObjectPrefix" value="fs_ws_"/>
</FileSystem>

image

注释内容:
<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>

image
取消注释:
<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager">
    <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>
    <param name="user" value="jcr_user" />
    <param name="password" value="password" />
    <param name="schema" value="mysql"/>
    <param name="schemaObjectPrefix" value="${wsp.name}_pm_ws_"/>
</PersistenceManager>
image

注释内容:
<FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
    <param name="path" value="${rep.home}/version" />
</FileSystem>
image

取消注释:
<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="jcr_user"/>
    <param name="password" value="password"/>
    <param name="schema" value="mysql"/>
    <param name="schemaObjectPrefix" value="fs_ver_"/>
</FileSystem>

image
注释内容:
<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>
image

取消注释:
<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager">
    <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>
    <param name="user" value="jcr_user" />
    <param name="password" value="password" />
    <param name="schema" value="mysql"/>
    <param name="schemaObjectPrefix" value="pm_ver_"/>
</PersistenceManager>

image

(7)simple-jndi/jdbc.properties 

注释HSQL 配置:

#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

image

新增 MySQL配置:

SampleData/type=javax.sql.DataSource
SampleData/driver=com.mysql.jdbc.Driver
SampleData/url=jdbc:mysql://localhost:3306/hibernate
SampleData/user=hibuser
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/hbibernate
Shark/user=hibuser
Shark/password=password
SampleDataAdmin/type=javax.sql.DataSource
SampleDataAdmin/driver=com.mysql.jdbc.Driver
SampleDataAdmin/url=jdbc:mysql://localhost:3306/hibernate
SampleDataAdmin/user=hibuser
SampleDataAdmin/password=password

image

(8)删除测试数据

rm default-content/*.zip*

rm –rf jackrabbit/repository/

3-2 /opt/ptools/biserver-ce/tomcat/ 目录下

(1)webapps/pentaho/META-INF/context.xml

将HSQLDB 的配置 更改为 MySQL

<Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
               factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxTotal="20" maxIdle="5"
               maxWaitMillis="10000" username="hibuser" password="password"
               driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhosti:3306/hibernate"
               validationQuery="select 1" />

<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
               factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxTotal="20" maxIdle="5"
               maxWaitMillis="10000" username="pentaho_user" password="password"
               driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/quartz"
               validationQuery="select 1"/>

image

(2)webapps/pentaho/WEB-INF/web.xml

注释内容:

<!-- [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] -->

image

注释内容:

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

image

(3)清理 Tomcat 临时文件

rm –rf conf/Catalina/

rm -rf work/Catalina/

4 启动服务进行测试

进入 /opt/ptools/biserver-ce 文件夹

./start-pentaho.sh & >> 1.log &

首页:http://IP:8080/pentaho


上一篇:浅谈MySQL中优化sql语句查询常用的30种方法
下一篇:C++ 初始化列表

相关文章

相关评论

本站评论功能暂时取消,后续此功能例行通知。

一、不得利用本站危害国家安全、泄露国家秘密,不得侵犯国家社会集体的和公民的合法权益,不得利用本站制作、复制和传播不法有害信息!

二、互相尊重,对自己的言论和行为负责。

好贷网好贷款