HSQLDb导出数据到Mysql

HSQLDb导出数据到Mysql

文章http://wiki.pentaho.com/display/COM/Use+Kettle+to+Migrate+Sample+Data+to+MySQL 给出了解决方案，

就是采用kettle将完成数据转换。

具体如下：

1> 保证已经安装好了kettle，并且已经安装步骤一中启动了pentaho

2> 将转换文件hprsnic-mysql-sampledata.ktr 放到kettle的repository目录

3> 在mysql里创建数据库sampledata并赋予权限：

create  database sampledata;
grant all on sampledata.* to 'pentaho_user'@'localhost' identified by 'password'

4> 点击“产生需要运行这个转换的SQL”，会弹出SQL语句的对话框，点击执行，会在mysql里创建DEPARTMENT_MANAGERS 与QUADRANT_ACTUALS两张表

 

5> 在kettle里点击运行按钮，会把数据从hsql中导入到mysql

 

############  附录 hprsnic-mysql-sampledata.ktr ############

<?xml version="1.0" encoding="UTF-8"?>

<transformation>

<info>

<name/>

<directory>/</directory>

<log>

<read/>

<write/>

<input/>

<output/>

<update/>

<connection/>

<table/>

<use_batchid>Y</use_batchid>

<use_logfield>N</use_logfield>

</log>

<maxdate>

<connection/>

<table/>

<field/>

<offset>0.0</offset>

<maxdiff>0.0</maxdiff>

</maxdate>

<size_rowset>350</size_rowset>

<sleep_time_empty>1</sleep_time_empty>

<sleep_time_full>1</sleep_time_full>

<dependencies>

</dependencies>

</info>

<notepads>

</notepads>

<connection>

<name>mysql-sampledata</name>

<server>localhost</server>

<type>MYSQL</type>

<access>Native</access>

<database>sampledata</database>

<port>3306</port>

<username>pentaho_user</username>

<password>Encrypted 2be98afc86aa7f2e4bb18bd63c99dbdde</password>

<servername/>

<data_tablespace/>

<index_tablespace/>

<attributes>

<attribute><code>PORT_NUMBER</code><attribute>3306</attribute></attribute>

</attributes>

</connection>

<connection>

<name>hpsnc-sampledata</name>

<server>localhost</server>

<type>HYPERSONIC</type>

<access>Native</access>

<database>sampledata</database>

<port>9001</port>

<username>pentaho_user</username>

<password>Encrypted 2be98afc86aa7f2e4bb18bd63c99dbdde</password>

<servername/>

<data_tablespace/>

<index_tablespace/>

<attributes>

<attribute><code>PORT_NUMBER</code><attribute>9001</attribute></attribute>

</attributes>

</connection>

<order>

<hop> <from>hprsnc-dm</from><to>my-dm</to><enabled>Y</enabled> </hop> <hop> <from>hprsnc-QA</from><to>my-qa</to><enabled>Y</enabled> </hop> </order>

 

<step>

<name>hprsnc-QA</name>

<type>TableInput</type>

<description/>

<distribute>Y</distribute>

<copies>1</copies>

<connection>hpsnc-sampledata</connection>

<sql>SELECT

REGION

, DEPARTMENT

, POSITIONTITLE

, ACTUAL

, BUDGET

, VARIANCE

FROM QUADRANT_ACTUALS

</sql>

<limit>0</limit>

<lookup/>

<execute_each_row>N</execute_each_row>

<variables_active>N</variables_active>

<GUI>

<xloc>94</xloc>

<yloc>198</yloc>

<draw>Y</draw>

</GUI>

</step>

 

<step>

<name>hprsnc-dm</name>

<type>TableInput</type>

<description/>

<distribute>Y</distribute>

<copies>1</copies>

<connection>hpsnc-sampledata</connection>

<sql>SELECT

REGION

, MANAGER_NAME

, EMAIL

FROM DEPARTMENT_MANAGERS

</sql>

<limit>0</limit>

<lookup/>

<execute_each_row>N</execute_each_row>

<variables_active>N</variables_active>

<GUI>

<xloc>95</xloc>

<yloc>106</yloc>

<draw>Y</draw>

</GUI>

</step>

 

<step>

<name>my-dm</name>

<type>InsertUpdate</type>

<description/>

<distribute>Y</distribute>

<copies>1</copies>

<connection>mysql-sampledata</connection>

<commit>100</commit>

<update_bypassed>Y</update_bypassed>

<lookup>

<table>DEPARTMENT_MANAGERS</table>

<key>

<name>REGION</name>

<field>REGION</field>

<condition>=</condition>

<name2/>

</key>

<key>

<name>MANAGER_NAME</name>

<field>MANAGER_NAME</field>

<condition>=</condition>

<name2/>

</key>

<key>

<name>EMAIL</name>

<field>EMAIL</field>

<condition>=</condition>

<name2/>

</key>

<value>

<name>REGION</name>

<rename>REGION</rename>

<update>N</update>

</value>

<value>

<name>MANAGER_NAME</name>

<rename>MANAGER_NAME</rename>

<update>N</update>

</value>

<value>

<name>EMAIL</name>

<rename>EMAIL</rename>

<update>N</update>

</value>

</lookup>

<GUI>

<xloc>223</xloc>

<yloc>112</yloc>

<draw>Y</draw>

</GUI>

</step>

 

<step>

<name>my-qa</name>

<type>InsertUpdate</type>

<description/>

<distribute>Y</distribute>

<copies>1</copies>

<connection>mysql-sampledata</connection>

<commit>100</commit>

<update_bypassed>Y</update_bypassed>

<lookup>

<table>QUADRANT_ACTUALS</table>

<key>

<name>REGION</name>

<field>REGION</field>

<condition>=</condition>

<name2/>

</key>

<key>

<name>DEPARTMENT</name>

<field>DEPARTMENT</field>

<condition>=</condition>

<name2/>

</key>

<key>

<name>POSITIONTITLE</name>

<field>POSITIONTITLE</field>

<condition>=</condition>

<name2/>

</key>

<key>

<name>ACTUAL</name>

<field>ACTUAL</field>

<condition>=</condition>

<name2/>

</key>

<key>

<name>BUDGET</name>

<field>BUDGET</field>

<condition>=</condition>

<name2/>

</key>

<key>

<name>VARIANCE</name>

<field>VARIANCE</field>

<condition>=</condition>

<name2/>

</key>

<value>

<name>REGION</name>

<rename>REGION</rename>

<update>N</update>

</value>

<value>

<name>DEPARTMENT</name>

<rename>DEPARTMENT</rename>

<update>N</update>

</value>

<value>

<name>POSITIONTITLE</name>

<rename>POSITIONTITLE</rename>

<update>N</update>

</value>

<value>

<name>ACTUAL</name>

<rename>ACTUAL</rename>

<update>N</update>

</value>

<value>

<name>BUDGET</name>

<rename>BUDGET</rename>

<update>N</update>

</value>

<value>

<name>VARIANCE</name>

<rename>VARIANCE</rename>

<update>N</update>

</value>

</lookup>

<GUI>

<xloc>232</xloc>

<yloc>206</yloc>

<draw>Y</draw>

1. </GUI>

2. </step>

3.  

4. </transformation>