一、Kettle启动设置
下载了pdi-ce-9.2.0.0-290,因为本机是jdk11启动不了,需要指定用jdk1.8启动
编辑Spoon.bat文件,加入
set JAVA_HOME=C:\Program Files\Java\jdk1.8.0_291
set PENTAHO_JAVA_HOME=%JAVA_HOME%
二、springboot项目,程序和lib库和config分离,整合kettle时报错
org.pentaho.di.core.exception.KettleException:
Unable to find plugin with ID 'Kettle'. If this is a test, make sure kettle-core tests jar is a dependency. If this is live make sure a kettle-password-encoder-plugins.xml exits in the classpath
追加自定义Class-Path路径:
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-jar-plugin</artifactId>
<configuration>
<archive>
<manifest>
<!--是否要把第三方jar放到manifest.mf的classpath中 -->
<addClasspath>true</addClasspath>
<!--生成的manifest.mf中classpath的前缀,因为要把第三方jar放到lib目录下,所以classpath的前缀是lib/ -->
<classpathPrefix>lib/</classpathPrefix>
<!-- 执行的主程序路径 -->
<mainClass>com.blemall.crcs.main.CrcsApplication</mainClass>
</manifest>
<!-- 追加自定义Class-Path路径 -->
<manifestEntries>
<Class-Path>. config/</Class-Path>
</manifestEntries>
</archive>
<!-- 排除target/classes下的config目录 -->
<excludes>
<exclude>config/**</exclude>
</excludes>
</configuration>
</plugin>
这样部署时,就能找到kettle-password-encoder-plugins.xml文件了
三、将kettle目录和xml文件,加到程序jar包中,本地run起来可以测试,部署又报错了
org.pentaho.di.core.exception.KettleXMLException:
The transformation path file:/C:/Users/mmm/Desktop/ok-crcs-1.0.0-SNAPSHOT/ok-crcs-1.0.0-SNAPSHOT.jar!/kettle/saasJob.ktr is invalid, and will not run successfully.
解决办法:就是编译的时候包含kettle目录和xml文件,打成jar包时排除
<build>
<plugins>
<!-- 打包时分离程序和依赖包 -->
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-jar-plugin</artifactId>
<configuration>
<archive>
<manifest>
<!--是否要把第三方jar放到manifest.mf的classpath中 -->
<addClasspath>true</addClasspath>
<!--生成的manifest.mf中classpath的前缀,因为要把第三方jar放到lib目录下,所以classpath的前缀是lib/ -->
<classpathPrefix>lib/</classpathPrefix>
<!-- 执行的主程序路径 -->
<mainClass>com.blemall.crcs.main.CrcsApplication</mainClass>
</manifest>
<!-- 追加自定义Class-Path路径 -->
<manifestEntries>
<Class-Path>. config/</Class-Path>
</manifestEntries>
</archive>
<!-- 排除target/classes下的config目录 -->
<excludes>
<exclude>config/**</exclude>
<!-- 排除kettle目录和kettle-password-encoder-plugins.xml文件 -->
<exclude>kettle/**</exclude>
<exclude>kettle-password-encoder-plugins.xml</exclude>
</excludes>
</configuration>
</plugin>
<!-- 拷贝src/main/resources下的配置文件到target/classes/config目录下,这样本地和junit能跑起来 -->
<plugin>
<artifactId>maven-resources-plugin</artifactId>
<executions>
<execution>
<id>copy-config</id>
<phase>process-sources</phase>
<goals>
<goal>copy-resources</goal>
</goals>
<configuration>
<outputDirectory>target/classes/config</outputDirectory>
<resources>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>*.yml</include>
<include>*.properties</include>
<include>*.xml</include>
<include>kettle/**</include>
</includes>
</resource>
</resources>
</configuration>
</execution>
</executions>
</plugin>
<!-- 拷贝依赖到jar外面的lib目录 -->
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-dependency-plugin</artifactId>
<executions>
<execution>
<id>copy-lib</id>
<phase>package</phase>
<goals>
<goal>copy-dependencies</goal>
</goals>
<configuration>
<!-- 输出路径 -->
<outputDirectory>target/lib</outputDirectory>
<!-- 排除传递依赖 -->
<excludeTransitive>false</excludeTransitive>
<!-- 复制的jar文件是否去掉版本信息 -->
<stripVersion>false</stripVersion>
<!-- 包含scope的范围 -->
<includeScope>runtime</includeScope>
</configuration>
</execution>
</executions>
</plugin>
<!-- 将配置文件复制到config目录下,然后将程序、lib目录、config目录,打成zip压缩包 -->
<plugin>
<artifactId>maven-assembly-plugin</artifactId>
<configuration>
<!-- 打包后的包名是否包含assembly的id名 -->
<appendAssemblyId>false</appendAssemblyId>
<!-- 引用的assembly配置文件 -->
<descriptors>
<descriptor>src/main/resources/assembly/assembly.xml</descriptor>
</descriptors>
</configuration>
<executions>
<execution>
<id>make-assembly</id>
<!-- 绑定到package生命周期阶段上 -->
<phase>package</phase>
<!-- 运行一次 -->
<goals>
<goal>single</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
<!-- 定义编译时的资源文件 -->
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
<!-- 打包加入资源文件 -->
<resource>
<directory>src/main/resources</directory>
<!-- 资源独立出来,打包时排除这些配置文件类型和resources下的其他目录 -->
<excludes>
<exclude>*.yml</exclude>
<exclude>*.properties</exclude>
<!-- <exclude>*.xml</exclude> -->
<exclude>logback.xml</exclude>
<exclude>generator/**</exclude>
<exclude>assembly/**</exclude>
<!-- <exclude>kettle/**</exclude> -->
</excludes>
<!-- 上面没有排除src/main/resources下的kettle目录和kettle-password-encoder-plugins.xml文件,
为了编译后,本地run起来能测试 -->
</resource>
<!-- 读取webapp下的静态文件 -->
<resource>
<directory>src/main/webapp</directory>
<targetPath>META-INF/resources</targetPath>
</resource>
</resources>
</build>
四、任务执行时间是每天6点:0 0 6 * * ?
但是一直报错,上班后手动执行任务,没问题
把定时任务改成5分钟一次,也没问题?怀疑是数据库网络访问有时间限制,改成每天9点执行试试
2021-11-25 06:02:07.419 [] ERROR [init of t_swzfmx_jdk_dj表输入.0 (Thread-204)] org.pentaho.di.trans.Trans :[saasJob.ktr] !TableInput.Log.ErrorOccurred!
2021-11-25 06:02:07.419 [] ERROR [init of t_swzfmx_jdk_dj表输入.0 (Thread-204)] org.pentaho.di.trans.Trans :[saasJob.ktr] Error initializing step [t_swzfmx_jdk_dj表输入]
2021-11-25 06:02:07.420 [] ERROR [init of t_kb_saas_zfmx表输入.0 (Thread-206)] org.pentaho.di.trans.Trans :[saasJob.ktr] !TableInput.Log.ErrorOccurred!
2021-11-25 06:02:07.420 [] ERROR [init of t_kb_saas_zfmx表输入.0 (Thread-206)] org.pentaho.di.trans.Trans :[saasJob.ktr] Error initializing step [t_kb_saas_zfmx表输入]
2021-11-25 06:02:07.421 [] ERROR [init of t_kb_saas_czmx表输入.0 (Thread-202)] org.pentaho.di.trans.Trans :[saasJob.ktr] !TableInput.Log.ErrorOccurred!
2021-11-25 06:02:07.421 [] ERROR [init of t_kb_saas_czmx表输入.0 (Thread-202)] org.pentaho.di.trans.Trans :[saasJob.ktr] Error initializing step [t_kb_saas_czmx表输入]
2021-11-25 06:02:07.421 [1637791200025-0900] ERROR [Thread-198] org.pentaho.di.trans.Trans :[saasJob.ktr] Step [t_kb_saas_czmx表输入.0] failed to initialize!
2021-11-25 06:02:07.421 [1637791200025-0900] ERROR [Thread-198] org.pentaho.di.trans.Trans :[saasJob.ktr] Step [t_swzfmx_jdk_dj表输入.0] failed to initialize!
2021-11-25 06:02:07.421 [1637791200025-0900] ERROR [Thread-198] org.pentaho.di.trans.Trans :[saasJob.ktr] Step [t_kb_saas_zfmx表输入.0] failed to initialize!
2021-11-25 06:02:07.421 [1637791200025-0900] INFO [Thread-198] org.pentaho.di.trans.Trans :[saasJob.ktr] !TableInput.Log.FinishedReadingQuery!
2021-11-25 06:02:07.426 [1637791200025-0900] INFO [Thread-198] org.pentaho.di.trans.Trans :[saasJob.ktr] !TableInput.Log.FinishedReadingQuery!
2021-11-25 06:02:07.429 [1637791200025-0900] INFO [Thread-198] org.pentaho.di.trans.Trans :[saasJob.ktr] !TableInput.Log.FinishedReadingQuery!
2021-11-25 06:02:07.431 [1637791200025-0900] INFO [Thread-198] org.pentaho.di.trans.Trans :[saasJob.ktr] !TableInput.Log.FinishedReadingQuery!
2021-11-25 06:03:40.438 [] INFO [Thread-198] com.xxl.job.core.thread.JobThread :>>>>>>>>>>> xxl-job JobThread stoped, hashCode:Thread[Thread-198,10,main]
报错:
org.pentaho.di.core.exception.KettleException:
We failed to initialize at least one step. Execution can not begin!
at org.pentaho.di.trans.Trans.prepareExecution(Trans.java:1287)
at org.pentaho.di.trans.Trans.execute(Trans.java:756)
at com.blemall.crcs.service.CommonService.runKettleTransfer(CommonService.java:214)
at com.blemall.crcs.service.SaasJobService.fetchData(SaasJobService.java:174)
at com.blemall.crcs.service.SaasJobService$$FastClassBySpringCGLIB$$c37cd414.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
at com.blemall.crcs.service.SaasJobService$$EnhancerBySpringCGLIB$$47164052.fetchData(<generated>)
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:498)
at com.blemall.crcs.service.CommonService.getProcess(CommonService.java:107)
at com.blemall.crcs.job.saasJob.SaasJobBatchMain.saasJobHandler(SaasJobBatchMain.java:65)
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:498)
at com.xxl.job.core.handler.impl.MethodJobHandler.execute(MethodJobHandler.java:31)
at com.xxl.job.core.thread.JobThread.run(JobThread.java:163)
五、kettle连接oracle sid方式和service name方式
发现问题,测试环境oracle配置的是sid,生产环境配置的是service name
两种方式在kettle上传的,主机名称和数据库名称不一样
sid:
192.168.10.125
sit2db
service name:
//192.168.10.125
/sit2db
service name要加斜杠
https://www.cnblogs.com/wanjianqifa/p/13588504.html
https://blog.csdn.net/wuzhangweiss/article/details/79841777