Kettle：SqlServer批量导入数据到Mysql

系列文章目录

---

前言

Kettle是一款开源的大数据ETL工具，功能很强大，但功能也很复杂；相应的网上也有很多基础教程和ETL的Blogs，此处不在赘述。
本文主要记录工作中的一次使用Kettle进行同步数据到Mysql的实施过程。

---

一、总体流程

• 涉及2个工作流、4个转换算子：
• 主工作流：串联全流程
• 子工作流：执行单表同步流程
• 转换一：从指定表中获取所有需要同步的原表及对应的目标表
• 转换二：设置原表名、目标表名到环境变量中
• 转换三：根据原表的ddl生成目标表的ddl，并保存到环境变量中
• 转换四：从原表中读取数据写入到目标表

二、详细设计

1. 转换一：读取ETL表配置

从指定的表中查询需要迁移的所有表配置，可以从表、导入文件等等多种途径进行配置。
从配置中读取迁移配置的目的在于过滤，因为sqlserver可能存在系统表中记录有临时表，但是该session没有办法查询到临时表的ddl。

• etl_job_config：从指定的表中查询需要迁移的表
• collect：过滤需要的字段source_id、target_id
• copy rows to result：将查询结果写入到result中

-- etl_job_config
SELECT
  id
, job_id
, source_id
, target_id
, source_config_id
, target_config_id
FROM dbo.etl_job_config
where is_pull = 0

2. 转换二：表配置写入环境变量

从转换一中写入结果中读取信息，写入到环境变量中
source_id -> SOURCE_ID
target_id -> TARGET_ID

3. 转换三：生成DDL

根据source_id，通过sql拼接出目标表的ddl，并将ddl预计写入到环境变量“DDL”
使用表输入进行脚本查询输出，SQL节点只能用于DDL执行，不会返回结果。

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

-- 获取注释
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(a.column_name),' ','') + '` ' +
       case data_type
           when 'datetime2' then 'datetime'
           when 'datetimeoffset' then 'datetime'
           when 'smalldatetime' then 'datetime'
           when 'money' then 'decimal(19,4)'
           when 'smallmoney' then 'decimal(19,4)'
           when 'nchar' then 'varchar'
           when 'ntext' then 'text'
           when 'nvarchar' then 'varchar'
           when 'char' then 'varchar'
           when 'real' then 'float'
           when 'numeric' then 'decimal'
           when 'uniqueidentifier' then 'varchar(40)'
           when 'xml' then 'text'
           when 'image' then 'longblob'
           else data_type
           end +
       coalesce(case data_type when 'image' then '' else '(' + cast(abs(character_maximum_length) as varchar) + ')' end, '') + ' ' +
       (case when IS_NULLABLE = 'NO' then 'NOT ' else '' end) + 'NULL ' +
       replace(replace(coalesce('DEFAULT ' + COLUMN_DEFAULT, ''), '(', ''), ')', '') +
       case
           when isnull(convert(varchar, b.column_description), '') <> ''
               then '/**' + isnull(convert(varchar, b.column_description), '') + '**/,'
           else ',' end
from INFORMATION_SCHEMA.COLUMNS a
         left join #columnsproperties b
                   on convert(varchar, a.column_name) = convert(varchar, b.column_name)
where a.table_name = @table
order by 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. 转换四：单表数据同步

通过表输入和表输出，将SqlServer同步到Mysql

子工作流：单表同步全流程

完成单表迁移，包括读取环境变量自动生成DDL创建表，并将数据同步完成
在流程中分别配置：读取配置 -> 生成ddl -> 执行ddl创建表 -> 同步表数据

主流程：主Job

整库迁移同步主Job，完成一键全量覆盖式的同步；
流程：获取迁移表配置 -> 针对每一行数据执行“同步自流程”

---

三、遗留问题：

1. 没有实现增量+拉链的抽取方式
2. 字段名必须对应，没办法自定义对应，没办法加减字段
3. 部分表同步失败：
   a. MsSql中有部分字段为中划线"-"，导致异常
   b. 多个大字段varchar(4000/8000)，超过表限制
   c. 中文表名、字段等，无法转换

---

总结

第一个版本，还是比较简陋的，中间遇到的主要问题就是生成ddl的时候，数据库不一致导致的类型、长度、语法、保留字等差异。