01-kettle

1. 转换

image-20220627095447375

1. 表输入

image-20220627093806941
image-20220704142118424

2. 输出

image-20220627094307997
image-20220627094438663
image-20220704195447074
image-20220627095145660

3. 值映射

image-20220704110804430

4. UUID

1. 源库生成

# MySQL中有个UUID()；达梦中没有，达梦有guid()和newid()

# mysql
select UUID() as id;

# DM
select newid() as id;
# B86C5D48-B268-492B-51C8-95C7E52EFF85

select guid() as id;
# D2439CC079BE411949A61FA9C4191EBB

2. kettle生成

image-20220704200603218

2. 作业

image-20220627095528091
image-20220627095910743
image-20220627100044957
image-20220627100228331

3. crontab

# 软件包是 cron 的主程序
yum -y install vixie-cron
# 软件包是用来安装、卸装、或列举用来驱动 cron 守护进程的表格的程序
yum -y install crontabs


# crontab运行状态
service crond status
# crontab重启
service crond restart
# crontab开机启动
chkconfig crond on

# crontab日志
tail -f /var/log/cron

# 设置
vi /etc/crontab
crontab -e

# vi /etc/crontab
SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
HOME=/

# For details see man 4 crontabs

# Example of job definition:
# .---------------- minute (0 - 59)
# |  .------------- hour (0 - 23)
# |  |  .---------- day of month (1 - 31)
# |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...
# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# |  |  |  |  |
# *  *  *  *  * user-name command to be executed

*/1 * * * * root /root/al/hello.sh

# crontab -e
*/1 * * * *  /root/al/hello.sh

# 每天凌晨1点执行
0 1 * * * /usr/local/test.sh

# 每天晚上21:30执行一次
30 21 * * * /usr/local/test.sh

# 每个月1号、10号、22号凌晨4:45执行
45 4 1,10,22 * * /usr/local/test.sh

1. 测试脚本

#! /bin/bash
echo "hello world!" >> /root/al/hello.log

4. linux_kettle

1. kjb

# 杀死kettle后台进程
ps aux|grep kettle|awk '{print $2}'|xargs kill -9

# 启动kjb文件，具备定时任务的功能
./kitchen.sh -file=/root/al/jobs.kjb >> /root/al/job.log
# 开启了一堆线程

root     21155  0.0  0.0 106116  1332 pts/3    S+   13:51   0:00 /bin/sh /root/al/soft/kettle8.0/data-integration/spoon.sh -main org.pentaho.di.kitchen.Kitchen -initialDir /root/al/soft/kettle8.0/data-integration/ -file=/root/al/jobs.kjb
root     21190  0.0  0.0 106116   484 pts/3    S+   13:51   0:00 /bin/sh /root/al/soft/kettle8.0/data-integration/spoon.sh -main org.pentaho.di.kitchen.Kitchen -initialDir /root/al/soft/kettle8.0/data-integration/ -file=/root/al/jobs.kjb
root     21191  0.0  0.0 106116   480 pts/3    S+   13:51   0:00 /bin/sh /root/al/soft/kettle8.0/data-integration/spoon.sh -main org.pentaho.di.kitchen.Kitchen -initialDir /root/al/soft/kettle8.0/data-integration/ -file=/root/al/jobs.kjb
root     21192  0.0  0.0 106116   584 pts/3    S+   13:51   0:00 /bin/sh /root/al/soft/kettle8.0/data-integration/spoon.sh -main org.pentaho.di.kitchen.Kitchen -initialDir /root/al/soft/kettle8.0/data-integration/ -file=/root/al/jobs.kjb
root     21193  0.0  0.0 106116   624 pts/3    S+   13:51   0:00 /bin/sh /root/al/soft/kettle8.0/data-integration/spoon.sh -main org.pentaho.di.kitchen.Kitchen -initialDir /root/al/soft/kettle8.0/data-integration/ -file=/root/al/jobs.kjb
root     21195  4.8  6.2 5860828 1004404 pts/3 Sl+  13:51   2:31 /usr/local/jdk/jdk1.8.0_11/bin/java -Xms1024m -Xmx2048m -XX:MaxPermSize=256m -Dhttps.protocols=TLSv1,TLSv1.1,TLSv1.2 -Djava.library.path=./../libswt/linux/x86_64/ -DKETTLE_HOME= -DKETTLE_REPOSITORY= -DKETTLE_USER= -DKETTLE_PASSWORD= -DKETTLE_PLUGIN_PACKAGES= -DKETTLE_LOG_SIZE_LIMIT= -DKETTLE_JNDI_ROOT= -jar /root/al/soft/kettle8.0/data-integration/launcher/pentaho-application-launcher-8.0.0.0-28.jar -lib ./../libswt/linux/x86_64/ -main org.pentaho.di.kitchen.Kitchen -initialDir /root/al/soft/kettle8.0/data-integration/ -file=/root/al/jobs.kjb

2. ktr

1. 单次同步

  • 后台开启线程运行，运行日志transfer_user.log
#! /bin/bash
export JAVA_HOME=/usr/local/jdk/jdk1.8.0_11
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
/root/al/soft/kettle/data-integration/pan.sh -file=/root/al/soft/kettle/dm_tml.ktr -log=/root/al/soft/kettle/dm_tml.log >/dev/null &
  • 带日志测试执行
/root/al/soft/kettle/data-integration/pan.sh -file=/root/al/soft/kettle/dm_tml.ktr -log=/root/al/soft/kettle/dm_tml.log

2. 定时任务

# crontab（一分钟执行一次）
*/1 * * * *  /root/al/ktr.sh

5. 连接达梦

D:\Program Files\dmdbms\drivers\jdbc

1. 将Dm_jdbc驱动copy到kettle

image-20220629150724673
image-20220629150903386

2. 重启kettle

3. 编辑DM连接

image-20220629162614360

jdbc:dm://127.0.0.1:5236/DMSERVER

jdbc:dm://192.168.90.25:5236/DMSERVER

dm.jdbc.driver.DmDriver

SYSDBA

SYSDBA

99. bug

1. id为NUMERIC

  • "ID" NUMERIC(20,0) NOT NULL,
image-20220701145636645
  • 导致出现莫名奇妙的问题，36994条数据导入mysql的varchar(36)只剩下99条
# 转换字段类型
SELECT
  cast(ID as varchar(36)) as ID
, NAME
FROM D5000.DMS_TERMINAL_INFO

2. 乱码

1. 源DB、目标DB编码

  • 《表输出》和《插入/更新》的数据库链接设置为characterEncoding utf8
image-20220701155814004

2. 允许简易转换

image-20220705160802917

3. 清缓存

image-20220705182709562

3. enum

  • 两个db的enum不一致，通过《值映射》转换
image-20220704110804430

4. timestamp处理

  • source_db将10位时间戳转换为日期格式
image-20220704142819558
  • 接收字段必须为datetime，不能为timestamp，应该是因为本地时区设置问题
image-20220704194847892
# 时间戳 ==> 日期格式
select from_unixtime(1590940800);

5. windows换行符

/bin/bash^M: bad interpreter: No such file or directory

  • windows换行符在linux的问题，重新输入即可