kettle mysql nodata_Pentaho Data Integration[kettle]笔记（1）-mysql/sqlite间数据迁移

系统环境

Pentaho Data Integration v7.1

windows 10

sqlite3

MySQL 5.7.21

本文目标

将sqlite3数据库中表SYL_COURSE_LIST的COURSE_NAME、COURSE_DESC、COURSE_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+左键”，滑动到表输出步骤

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

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

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

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

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

错误连接数据库 [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-connector-java-5.1.46.jar复制到Pentaho Data Integration所在目录的lib文件夹下，重启Spoon，重新配置并测试即可

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

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

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

目标表DST_COURSE_LIST内容如下

更多原创文章，尽在金笔头博客