ETL-使用kettle批量复制sqlserver数据到mysql数据库

title: ETL-使用kettle批量复制sqlserver数据到mysql数据库
date: 2023-11-21 10:21:53
tags: ETL
cover: https://gulimall-ayu.oss-cn-chengdu.aliyuncs.com/blog/QQ%E5%9B%BE%E7%89%8720231121133353.png

1、安装sqlserver数据库

#安装之前我们准备好挂载文件夹：/opt/module/mssql
#并且修改文件夹所有者：  chown -R 10001:0 ./opt/module/mssql

docker run \
 --name mssql \
 -e 'ACCEPT_EULA=Y' \
 -e 'MSSQL_SA_PASSWORD=XLYqwe123' \
 -p 1433:1433 \
 -v /opt/module/mssql:/var/opt/mssql \
 --restart=always \
 -d mcr.microsoft.com/mssql/server:2017-latest



#进入容器命令：
docker exec -it 容器id /bin/bash


#登录命令：
 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "XLYqwe123"


#然后我们就可以创建一些表用来模拟传输数据

2、下载kettle

kettle在外网下载起来非常慢，这是我使用的版本
链接：https://pan.baidu.com/s/142eHrLx5AjmGxwCEbabfCw?pwd=uqmh 
提取码：uqmh

3、业务分析

现在一共是四百多张表在sqlserver里面，直接用navicat的传输工具要报错，
在kettle里面是这样解决的，先根据sqlserver的表生成mysql的建表语句（ddl），然后
在将sqlserver的表格数据插入过去。

4、详细流程

流程完全是copy的这个文章：
https://blog.csdn.net/xuyang2059/article/details/124431556?spm=1001.2014.3001.5502

总共涉及到两个工作流，4个转换算子

（1）转换1：获取sqlserver所有表格名字，将记录复制到结果

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME;

（2）转换2：从结果设置变量

（3）转换3：生成建表的DDL(我按照自己的业务修改了sql)

declare @table varchar(100) = '${TNAME}'
declare @sql table(s varchar(1000), id int identity)
-- 创建语句
insert into  @sql(s) values ('create table if not exists ${TNAME} (')

-- 获取注释
SELECT A.name  AS table_name,
       B.name  AS column_name,
       C.value AS column_description
into #columnsproperties
FROM sys.tables A
         INNER JOIN sys.columns B ON B.object_id = A.object_id
         LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id
WHERE A.name = @table

-- 获取列的列表，拼接语句
insert into @sql(s)
SELECT 
  '  `' + REPLACE(LOWER(c.COLUMN_NAME), ' ', '') + '` ' +
  CASE 
    WHEN c.DATA_TYPE = 'nvarchar' AND (c.CHARACTER_MAXIMUM_LENGTH = -1 OR c.CHARACTER_MAXIMUM_LENGTH = 4000) THEN 'text'
    WHEN c.DATA_TYPE = 'datetime2' THEN 'datetime'
    WHEN c.DATA_TYPE = 'datetimeoffset' THEN 'datetime'
    WHEN c.DATA_TYPE = 'smalldatetime' THEN 'datetime'
    WHEN c.DATA_TYPE = 'money' THEN 'decimal(19,4)'
    WHEN c.DATA_TYPE = 'smallmoney' THEN 'decimal(19,4)'
    WHEN c.DATA_TYPE = 'nchar' THEN 'varchar'
    WHEN c.DATA_TYPE = 'ntext' THEN 'text'
    WHEN c.DATA_TYPE = 'nvarchar' THEN 'varchar'
    WHEN c.DATA_TYPE = 'char' THEN 'varchar'
    WHEN c.DATA_TYPE = 'real' THEN 'float'
    WHEN c.DATA_TYPE = 'numeric' THEN 'decimal'
    WHEN c.DATA_TYPE = 'uniqueidentifier' THEN 'varchar(36)'
    WHEN c.DATA_TYPE = 'xml' THEN 'text'
    WHEN c.DATA_TYPE = 'image' THEN 'longblob'
    WHEN c.DATA_TYPE = 'bit' THEN 'int'
    WHEN c.DATA_TYPE = 'float' THEN 'decimal(10,2)'
    ELSE c.DATA_TYPE
  END +
  COALESCE(
    CASE 
      WHEN c.DATA_TYPE = 'nvarchar' AND (c.CHARACTER_MAXIMUM_LENGTH = -1 OR c.CHARACTER_MAXIMUM_LENGTH = 4000) THEN ''
      WHEN c.DATA_TYPE IN ('image', 'xml', 'ntext', 'text') THEN ''
      ELSE '(' + CAST(ABS(c.CHARACTER_MAXIMUM_LENGTH) AS VARCHAR) + ')' 
    END, ''
  ) + ' ' +
  (CASE WHEN c.IS_NULLABLE = 'NO' THEN 'NOT ' ELSE '' END) + 'NULL ' +
  REPLACE(REPLACE(COALESCE('DEFAULT ' + c.COLUMN_DEFAULT, ''), '(', ''), ')', '') +
  CASE
    WHEN ISNULL(cp.COLUMN_DESCRIPTION, '') <> ''
    THEN '/**' + ISNULL(CONVERT(VARCHAR, cp.COLUMN_DESCRIPTION), '') + '**/,'
    ELSE ',' 
  END AS target_column
FROM INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN (
  SELECT 
    cp1.name AS column_name,
    cp1.value AS column_description,
    ROW_NUMBER() OVER (PARTITION BY cp1.name ORDER BY cp1.name) AS rn
  FROM sys.columns sc
  INNER JOIN sys.tables st ON st.object_id = sc.object_id
  LEFT JOIN sys.extended_properties cp1 ON cp1.major_id = sc.object_id AND cp1.minor_id = sc.column_id
  WHERE st.name = @table
) cp ON c.COLUMN_NAME = cp.column_name AND cp.rn = 1
WHERE c.TABLE_NAME = @table
ORDER BY c.ORDINAL_POSITION

-- etl日期字段
-- insert into @sql(s)
-- values ('  etl_date datetime NOT NULL ,')

-- 主键
declare @pkname varchar(100)
select @pkname = constraint_name
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where table_name = @table
  and constraint_type = 'PRIMARY KEY'
if (@pkname is not null)
    begin
        insert into @sql(s) values ('  PRIMARY KEY (')
        insert into @sql(s)
        select '   ' + COLUMN_NAME + ','
        from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        where constraint_name = @pkname
        order by ordinal_position
        -- 去除尾部多余的字符
        update @sql set s=left(s, len(s) - 1) where id = @@identity
        insert into @sql(s) values ('  )')
    end
else
    begin
        -- 去除尾部多余的字符
        update @sql set s=left(s, len(s) - 1) where id = @@identity
    end
-- 继续拼接
insert into @sql(s)
values (')')

drop table #columnsproperties

-- 输出结果
select stuff((select CHAR(10) + s from @sql order by id for xml path('')), 1, 1, '') as ddl

（4）转换4：迁移数据到mysql

（5）工作流1：单表同步流程

（6）工作流2：主流程