• 欢迎访问金笔头博客,这是一个菜鸟(伪)程序员的自留地,欢迎访问我的github:点击进入

Pentaho Data Integration笔记(1)-mysql/sqlite间数据迁移

ETL eason 1074次浏览 0个评论 扫描二维码

系统环境

本文目标

将sqlite3数据库中表SYL_COURSE_LISTCOURSE_NAMECOURSE_DESCCOURSE_USERS字段数据转存入MSQL数据库表DST_COURSE_LIST中。

SYL_COURSE_LIST表结构

CREATE TABLE SYL_COURSE_LIST(
  COURSE_NAME CHAR(200) NOT NULL ,
  COURSE_DESC CHAR(200) NOT NULL ,
  COURSE_USERS CHAR(200) NOT NULL ,
  COURSE_URL CHAR(200) PRIMARY KEY ,
  COURSE_IMG CHAR(200) NOT NULL ,
  COURSE_TYPE CHAR(20) NOT NULL ,
  INDATE TIMESTAMP DEFAULT (datetime('now','localtime'))
);

DST_COURSE_LIST表结构

CREATE TABLE `DST_COURSE_LIST` (
  `COURSE_NAME` varchar(300) NOT NULL,
  `COURSE_DESC` varchar(300) NOT NULL,
  `COURSE_USERS` varchar(300) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2248 DEFAULT CHARSET=utf8;

实践过程

1.打开Spoon,从左侧分别拖入表输入步骤和表输出步骤到右侧空白区域

2.选中表输入步骤,按住”Shift+左键”,滑动到表输出步骤

Pentaho Data Integration笔记(1)-mysql/sqlite间数据迁移

3.双击表输入步骤,点击”新建”按钮,配置sqlite连接信息,主要设置在于连接类型选择Generic database,连接方式选择Native(JDBC),自定义连接URL填入jdbc:sqlite:D:\PycharmProjects\shiyanlou\syl.db3,自定义驱动类名称填org.sqlite.JDBC,用户名密码留空,参数配完可以点击“测试”按钮测试配置是否正确。

Pentaho Data Integration笔记(1)-mysql/sqlite间数据迁移

D:\PycharmProjects\shiyanlou\syl.db3是sqlite数据库文件的绝对路径

4.在SQL输入框填写相应的sql语句,你也可以通过点击“获取SQL查询语句”按钮来达到相同目的

Pentaho Data Integration笔记(1)-mysql/sqlite间数据迁移

5.双击表输入步骤,点击”新建”按钮,配置MySQL连接信息,配置过程和sqlite类似,连接类型选择MySQL,连接方式选择Native(JDBC),主机名称填mysq主机的ip地址,数据库名称填目标表所在数据库名,我这里是kettle,用户名和密码按实际情况填写,参数配完可以点击“测试”按钮测试配置是否正确。

Pentaho Data Integration笔记(1)-mysql/sqlite间数据迁移

初次配置完点击“测试”按钮可能会遇到如下错误:

错误连接数据库 [mysql] : org.pentaho.di.core.exception.KettleDatabaseException: 
Error occurred while trying to connect to the database

Driver class 'org.gjt.mm.mysql.Driver' could not be found, make sure the 'MySQL' driver (jar file) is installed.
org.gjt.mm.mysql.Driver


org.pentaho.di.core.exception.KettleDatabaseException: 
Error occurred while trying to connect to the database

Driver class 'org.gjt.mm.mysql.Driver' could not be found, make sure the 'MySQL' driver (jar file) is installed.
org.gjt.mm.mysql.Driver


    at org.pentaho.di.core.database.Database.normalConnect(Database.java:472)
    at org.pentaho.di.core.database.Database.connect(Database.java:370)
    at org.pentaho.di.core.database.Database.connect(Database.java:341)
    at org.pentaho.di.core.database.Database.connect(Database.java:331)
    at org.pentaho.di.core.database.DatabaseFactory.getConnectionTestReport(DatabaseFactory.java:80)
    at org.pentaho.di.core.database.DatabaseMeta.testConnection(DatabaseMeta.java:2783)
    at org.pentaho.ui.database.event.DataHandler.testDatabaseConnection(DataHandler.java:597)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.pentaho.ui.xul.impl.AbstractXulDomContainer.invoke(AbstractXulDomContainer.java:313)
    at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:157)
    at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:141)
    at org.pentaho.ui.xul.swt.tags.SwtButton.access$500(SwtButton.java:43)
    at org.pentaho.ui.xul.swt.tags.SwtButton$4.widgetSelected(SwtButton.java:137)
    at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source)
    at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
    at org.eclipse.jface.window.Window.runEventLoop(Window.java:820)
    at org.eclipse.jface.window.Window.open(Window.java:796)
    at org.pentaho.di.ui.xul.KettleDialog.show(KettleDialog.java:80)
    at org.pentaho.di.ui.xul.KettleDialog.show(KettleDialog.java:47)
    at org.pentaho.di.ui.core.database.dialog.XulDatabaseDialog.open(XulDatabaseDialog.java:116)
    at org.pentaho.di.ui.core.database.dialog.DatabaseDialog.open(DatabaseDialog.java:60)
    at org.pentaho.di.ui.trans.step.BaseStepDialog.showDbDialogUnlessCancelledOrValid(BaseStepDialog.java:779)
    at org.pentaho.di.ui.trans.step.BaseStepDialog$AddConnectionListener.widgetSelected(BaseStepDialog.java:1401)
    at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source)
    at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
    at org.pentaho.di.ui.trans.steps.tableoutput.TableOutputDialog.open(TableOutputDialog.java:885)
    at org.pentaho.di.ui.spoon.delegates.SpoonStepsDelegate.editStep(SpoonStepsDelegate.java:127)
    at org.pentaho.di.ui.spoon.Spoon.editStep(Spoon.java:8766)
    at org.pentaho.di.ui.spoon.trans.TransGraph.editStep(TransGraph.java:3217)
    at org.pentaho.di.ui.spoon.trans.TransGraph.mouseDoubleClick(TransGraph.java:783)
    at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source)
    at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
    at org.pentaho.di.ui.spoon.Spoon.readAndDispatch(Spoon.java:1366)
    at org.pentaho.di.ui.spoon.Spoon.waitForDispose(Spoon.java:8022)
    at org.pentaho.di.ui.spoon.Spoon.start(Spoon.java:9277)
    at org.pentaho.di.ui.spoon.Spoon.main(Spoon.java:692)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.pentaho.commons.launcher.Launcher.main(Launcher.java:92)
Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 
Driver class 'org.gjt.mm.mysql.Driver' could not be found, make sure the 'MySQL' driver (jar file) is installed.
org.gjt.mm.mysql.Driver

    at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:515)
    at org.pentaho.di.core.database.Database.normalConnect(Database.java:456)
    ... 55 more
Caused by: java.lang.ClassNotFoundException: org.gjt.mm.mysql.Driver
    at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
    at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:490)
    ... 56 more

主机名      : 192.168.192.128
端口          : 3306
数据库名:kettle

出现这个错误的原因是因为mysql驱动没找到,可从如下地址下载:https://dev.mysql.com/downloads/connector/j/

Pentaho Data Integration笔记(1)-mysql/sqlite间数据迁移

解压之后将文件mysql-connector-java-5.1.46.jar复制到Pentaho Data Integration所在目录的lib文件夹下,重启Spoon,重新配置并测试即可

6.目标表选择DST_COURSE_LIST,勾选“指定数据库字段”,在下方依次选择表字段和流字段的映射关系

Pentaho Data Integration笔记(1)-mysql/sqlite间数据迁移

为了防止出现中文乱码情况,我们还要指定编码,点击上图“编辑”按钮,点击“选项”,在右侧列表区添加参数名characterEncoding,参数值为UTF-8

Pentaho Data Integration笔记(1)-mysql/sqlite间数据迁移

7.点击左上角三角图标运行,成功结果如下

Pentaho Data Integration笔记(1)-mysql/sqlite间数据迁移

目标表DST_COURSE_LIST内容如下

Pentaho Data Integration笔记(1)-mysql/sqlite间数据迁移


金笔头博客, 版权所有丨如未注明 , 均为原创, 转载请注明Pentaho Data Integration笔记(1)-mysql/sqlite间数据迁移
喜欢 (0)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址