Pentaho Kettle 8.1運作環境切換使用MySQL作為存儲資料庫

原文位址：https://help.pentaho.com/Documentation/8.1/Setup/Installation/Archive/MySQL_Repository

        參考了很多網上的切換配置，互相說明有沖突，也沒說明改配置的意義是啥，是以去官網幫助文檔查了一下，說明的比較詳細，就翻譯出來存檔一下。如果您是大神就可以關閉不看了，本文是準備給我像我一樣的新手的。

Use MySQL as Your Repository Database (Archive Installation)

使用MySQL作為您的存儲資料庫（壓縮包方式安裝）

Last updated  May 22, 2018

Prerequisite: Before you prepare your Pentaho Repository, you must prepare either a Windows or Linux environment.

先決條件:在準備Pentaho存儲庫之前，必須準備Windows或Linux環境。

The Pentaho Repository resides on the database that you installed during the Windows or Linux environment preparation step, and consists of the following four components:

Pentaho存儲庫駐留在Windows或Linux環境準備步驟中安裝的資料庫上，由以下四個元件組成:

• Jackrabbit contains the solution repository, examples, security data, and content data from reports that you use Pentaho software to create.
• Jackrabbit包含您使用Pentaho軟體建立的解決方案存儲庫、示例、安全資料和來自報表的内容資料。
• Quartz holds data that is related to scheduling reports and jobs.
• Quartz儲存與排程報表和作業相關的資料。
• Hibernate holds data that is related to audit logging.
• Hibernate儲存與審計日志記錄相關的資料。
• The optional Pentaho Operations Mart reports on system usage and performance.
• 可選的Pentaho Operations Mart報告系統使用情況和性能。

Initialize MySQL Pentaho Repository Database

初始化MySQL Pentaho存儲庫資料庫

To initialize MySQL so that it serves as the Pentaho Repository, you will need to run a few SQL scripts to create the Hibernate, Quartz, Jackrabbit (JCR), and Pentaho Operations Mart databases.

為了初始化MySQL，使其作為Pentaho存儲庫，您需要運作一些SQL腳本來建立Hibernate、Quartz、Jackrabbit (JCR)和Pentaho Operations Mart資料庫。

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.

運作這些腳本時使用ASCII字元集。不要使用UTF-8，因為存在可能導緻腳本失敗的文本字元串長度限制。

The next few sections take you through the steps to initialize the MySQL Pentaho Repository database.

接下來的幾節将介紹初始化MySQL Pentaho存儲庫資料庫的步驟。

Step 1: Change Default Passwords

步驟1:更改預設密碼

For your production server, we highly recommend that you change the default passwords in the following SQL script files to make the databases more secure.

做為您的生産伺服器，我們強烈建議您更改以下SQL腳本檔案中的預設密碼，以使資料庫更加安全。

If you are evaluating Pentaho, you might want to skip this step.

如果您正在評估Pentaho，您可以跳過這一步。

To change the passwords, go to the pentaho/server/pentaho-server/data/mysql5 directory and use any text editor to change the passwords in these SQL scripts:

要更改密碼，請轉到pentaho/server/pentaho-server/data/mysql5目錄，并使用任何文本編輯器更改這些SQL腳本中的密碼:

• create_jcr_mysql.sql
• create_quartz_mysql.sql
• create_repository_mysql.sql
• pentaho_mart_mysql.sql

Step 2: Run SQL Scripts

步驟2:運作SQL腳本

You will need to run these SQL scripts in the table below.

您需要運作下表中的這些SQL腳本。

These scripts require administrator permissions on the server in order to run them.

這些腳本需要伺服器上的管理者權限才能運作。

If you have a different port or different password, make sure that you change the passwords and port numbers in these examples to match those in your configuration.

如果您有不同的端口或密碼，請確定您更改了這些示例中的密碼和端口号，以比對配置中的密碼和端口号。

Run these scripts from the MySQL Command Prompt window or from MySQL Workbench.

從MySQL指令行或MySQL管理工具運作這些腳本。

Action	SQL Script
Create Quartz	> source <your filepath>/create_quartz_mysql.sql
Create Hibernate repository	> source <your filepath>/create_repository_mysql.sql
Create Jackrabbit	> source <your filepath>/create_jcr_mysql.sql
Create Pentaho Operations mart	> source <your filepath>/pentaho_mart_mysql.sql

Step 3: Verify MySQL Initialization

步驟3:驗證MySQL初始化

After you run the scripts, perform the following steps to verify that databases and user roles have been created:

運作腳本後，執行以下步驟來驗證資料庫和使用者角色是否已經建立:

1. Open the MySQL Workbench tool. MySQL Workbench is freely available at the MySQL development site.打開MySQL管理工具。MySQL管理工具可以在MySQL開發站點上免費獲得。
2. Log in as hibuser.以hibuser的身份登入。
3. Make sure that the Quartz, Jackrabbit (JCR), Hibernate, and Pentaho Operations Mart databases are present.請確定Quartz、Jackrabbit (JCR)、Hibernate和Pentaho Operations Mart資料庫已經存在。
4. Exit from the MySQL Workbench.退出MySQL工作台。

Configure MySQL Pentaho Repository Database

配置MySQL Pentaho存儲資料庫

Now that you have initialized your repository database, you will need to configure Quartz, Hibernate, Jackrabbit, and Pentaho Operations Mart for a MySQL database.

現在你已經初始化了存儲資料庫，接下來需要切換Quartz、Hibernate、Jackrabbit和Pentaho Operations Mart 為MySQL資料庫。

By default, the examples in this section are for a MySQL database that runs on port 3306. The default password is also in these examples.

預設情況下，本節中的示例用于在端口3306上運作的MySQL資料庫。預設密碼也在這些示例中。

If you have a different port or different password, make sure that you change the password and port number in these examples to match those in your configuration.

如果您有不同的端口或密碼，請確定您更改了這些示例中的密碼和端口号，以比對配置中的密碼和端口号。

Step 1: Set Up Quartz on MySQL Pentaho Repository Database

步驟1:切換基于MySQL的Quartz庫為的Pentaho存儲資料庫

Event information, such as scheduled reports, is stored in the Quartz JobStore. During the installation process, you must indicate where the JobStore is located, by modifying the quartz.properties file using the following steps.

事件資訊(例如預定的報告)存儲在Quartz JobStore中。在安裝過程中，你必須通過按下述步驟修改位于quartz.properties檔案中JobStore配置。

1. Open the pentaho/server/pentaho-server/pentaho-solutions/system/quartz/quartz.properties file in any text editor.使用任何文本編輯器打開pentaho/server/pentaho-server/pentaho-solutions/system/quartz/quartz.properties檔案。
2. Locate the #_replace_jobstore_properties section and set the org.quartz.jobStore.driverDelegateClass as shown here.找到 #_replace_jobstore_properties部分設定org.quartz.jobStore.driverDelegateClass參數，如下所示。（我配置時本行是注釋掉的，去掉注釋就行）

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

3. Locate the # Configure Datasources section and set the org.quartz.dataSource.myDS.jndiURL equal to Quartz, like this.找到# Configure Datasources 部分設定org.quartz.dataSource.myDS.jndiURL等于Quartz，如下所示。（我配置時，本行是生效的）

   1	org.quartz.dataSource.myDS.jndiURL = Quartz

4. Save the file and close the text editor.存儲修改并退出文本編輯器。

Step 2: Set Hibernate Settings for MySQL

步驟2：切換基于MySQL的Hibernate設定

Modify the Hibernate settings file to specify where Pentaho should find the Pentaho Repository’s Hibernate configuration file.

修改Hibernate設定檔案，指定Pentaho服務應該在哪裡找到Pentaho Hibernate庫的配置檔案。

The Hibernate configuration file specifies driver and connection information, as well as dialects and how to handle connection closes and timeouts.

Hibernate配置檔案指定驅動程式和連接配接資訊、資料庫方言以及如何處理連接配接、關閉和逾時。

The files in this section are located in the pentaho/server/pentaho-server/pentaho-solutions/system/hibernate directory.

本節中的檔案位于 pentaho/server/pentaho-server/pentaho-solutions/system/hibernate目錄中。

Perform the following steps to specify where Pentaho can find the Hibernate configuration file.

執行以下步驟來指定Pentaho可以在哪裡找到Hibernate配置檔案。

1. Open the hibernate-settings.xml file in a text editor. Find the <config-file> tags and change postgresql.hibernate.cfg.xml to mysql5.hibernate.cfg.xml as shown.使用文本編輯器打開hibernate-settings.xml 檔案。找到<config-file>标記，并将postgresql.hibernate.cfg.xml更改為mysql5.hibernat .cfg.xml，如下所示。

   From:

   1	< config-file >system/hibernate/postgresql.hibernate.cfg.xml</ config-file >

   To:

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

2. Save and close the file.儲存并關閉檔案。
3. Open the mysql5.hibernate.cfg.xml file in a text editor.使用文本編輯器打開mysql5.hibernate.cfg.xml檔案。
4. Make sure that the password and port number match the ones you specified in your configuration. Make changes if necessary, then save and close the file.確定密碼和端口号與您在配置中指定的密碼和端口号比對。如果需要，進行更改，然後儲存并關閉檔案。

Step 3: Replace Default Version of Audit Log File with MySQL Version

步驟3:用MySQL替換預設的審計日志檔案

Since you are using MySQL to host the Pentaho Repository, you need to replace the audit_sql.xml file with one that is configured for MySQL.

由于使用MySQL來托管Pentaho存儲庫，是以需要修改audit_sql.xml檔案将其更改為MySQL配置。

1. Locate the pentaho-solutions/system/dialects/mysql5/audit_sql.xml file.找到pentaho-solutions/system/dialects/mysql5/audit_sql.xml 檔案。
2. Copy it into the pentaho-solutions/system directory.複制到pentaho-solutions/system目錄。

Step 4: Modify Jackrabbit Repository Information for MySQL

步驟4:按MySQL配置修改Jackrabbit存儲庫資訊

Change the default jackrabbit repository to MySQL using the following steps.

使用以下步驟将預設的jackrabbit存儲庫更改為MySQL。

1. Navigate to the pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit and open the repository.xml file with any text editor.使用文本編輯器打開位于pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit的repository.xml檔案。
2. Following the table below, locate and change the code so that the MySQL lines are not commented out, but the PostgreSQL, MS SQL Server, and Oracle lines are commented out.根據下表，定位并更改代碼，使MySQL行不被注釋掉，而PostgreSQL、MS SQL Server和Oracle行被注釋掉。

If you have a different port or different password, make sure that you change the password and port number in these examples to match those in your configuration.

如果您有不同的端口或密碼，請確定您更改了這些示例中的密碼和端口号，以比對配置中的密碼和端口号。

Item:	Code Section:
Repository	<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"/> … </FileSystem>
DataStore	<DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore"> <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/> … </DataStore>
Workspaces	<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"/> … </FileSystem>
PersistenceManager (1st part)	<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager"> <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/> … </PersistenceManager>
Versioning	<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"/> … </FileSystem>
PersistenceManager (2nd part)	<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager"> <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/> … </PersistenceManager>
DatabaseJournal	<Journal class="org.apache.jackrabbit.core.journal.DatabaseJournal"> <param name="revision" value="${rep.home}/revision.log"/> <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="J_C_"/> <param name="janitorEnabled" value="true"/> <param name="janitorSleep" value="86400"/> <param name="janitorFirstRunHourOfDay" value="3"/> </Journal>

Perform Tomcat-Specific Connection Tasks

完成Tomcat-Specific連接配接任務

After you configure your repository, you must configure the web application servers to connect to the Pentaho Repository. In this section, you will make JDBC and JNDI connections to the Hibernate, Jackrabbit, and Quartz databases.

在配置存儲庫之後，必須配置web應用伺服器以連接配接到Pentaho存儲庫。在本節中，您将建立到Hibernate、Jackrabbit和Quartz資料庫的JDBC和JNDI連接配接。

By default, the Pentaho Server software is configured to be deployed and run on the Tomcat server. As such, connections have already been specified and only the Tomcat context.xml file must be modified.

預設情況下，Pentaho伺服器軟體被配置為部署并運作在Tomcat伺服器上。是以，必須修改已經指定且唯一指定了連接配接配置的Tomcat context.xml檔案。

The next couple of sections guide you through the process of working with the JDBC drivers and connection information for Tomcat.

接下來的幾個部分将指導您處理Tomcat的JDBC驅動程式和連接配接資訊。

Step 1: Download Drivers and Apply to the Pentaho Server

步驟1:下載下傳驅動程式并應用到Pentaho伺服器

To connect to a database, including the Pentaho Repository database, you will need to download and copy a JDBC driver to the appropriate places for Pentaho Server as well as on the web application server.

要連接配接到資料庫，包括Pentaho存儲資料庫，您需要下載下傳JDBC驅動程式并将其複制到适合Pentaho伺服器和web應用程式伺服器的位置。

Due to licensing restrictions, Pentaho cannot redistribute some third-party database drivers. You will have to download and install the file yourself.

由于許可限制，Pentaho不能重新分發一些第三方資料庫驅動程式。您必須自己下載下傳并安裝該檔案。

1. Download a JDBC driver JAR from your database vendor or a third-party driver developer.從資料庫供應商或第三方驅動程式開發人員下載下傳JDBC驅動程式JAR。
2. Copy the JDBC driver JAR you just downloaded to the pentaho/server/pentaho-server/tomcat/lib folder.将剛剛下載下傳的JDBC驅動程式JAR複制到pentaho/server/pentaho-server/tomcat/lib檔案夾。
3. Copy the hsqldb-2.3.2.jar file to pentaho-server/tomcat/lib if you want to retain the sample provided by Pentaho.複制hsqldb-2.3.2。如果您想保留Pentaho提供的示例，可以将jar檔案儲存到Pentaho -server/tomcat/lib。

Step 2: Modify JDBC Connection Information in the Tomcat context.xml File

步驟2:在Tomcat context.xml檔案中修改JDBC連接配接資訊

Database connection and network information, such as the username, password, driver class information, IP address or domain name, and port numbers for your Pentaho Repository database are stored in the context.xml file. Modify this file to reflect the database connection and network information to reflect your operating environment. If you have chosen to use a Pentaho Repository database other than MySQL, modify the values for the validationQuery parameters in this file.

Pentaho存儲資料庫的資料庫連接配接和網絡資訊，例如使用者名、密碼、驅動程式類資訊、IP位址或域名，以及端口号等都存儲在context.xml檔案中。此檔案修改驗證查詢參數的值。

If you have a different port, password, user, driver class information, or IP address, make sure that you change the password and port number in these examples to match those in your configuration environment.

如果您有不同的端口、密碼、使用者、驅動程式類資訊或IP位址，請確定您更改了這些示例中的密碼和端口号，以比對配置環境中的密碼和端口号。

1. Consult your database documentation to determine the JDBC class name and connection string for your Pentaho Repository database.請參閱你的資料庫文檔，以确定Pentaho存儲資料庫的JDBC類名和連接配接字元串
2. Go to the pentaho-server/tomcat/webapps/pentaho/META-INF directory and open the context.xml file with any file editor.使用任意文本編輯器打開位于 pentaho-server/tomcat/webapps/pentaho/META-INF目錄的context.xml檔案。
3. Comment out the resource references that refer to databases other than MySQL, such as PostgreSQL, MS SQL Server, and Oracle. Then, add the following code to the file if it does not already exist. Be sure to adjust the port numbers and passwords to reflect your environment, if necessary.注釋掉引用MySQL以外的資料庫的資源引用，例如PostgreSQL、MS SQL Server和Oracle。然後，如果檔案不存在，則将以下代碼添加到檔案中。如果需要，請確定調整端口号和密碼以反映您的環境。
4. Make sure that the validationQuery variable for your database is set as follows: validationQuery="select 1"  確定你的資料庫validationQuery變量設定如下:validationQuery="select 1"
5. Save the context.xml file, then close it.存儲對context.xml檔案的變更并關閉它。

<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"/>
           

Start Your Server

啟動伺服器

Now that you have completed the initial installation steps, you are ready to start the Pentaho Server.

現在您已經完成了初始安裝步驟，可以啟動Pentaho伺服器了。