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>
      </GUI>
    </step>

</transformation>