kettle-数据迁移场景

场景描述：

        大家都知道，物联网项目的数据量大，如何高效的存储处理数据是一个非常棘手的问题。但是，往往在项目初期是不可能准确的预估到数据体量，随着项目的演进，就会发现现数据库性能会直线下降，甚至会导致服务挂掉。此时项目的架构也要随之进行革新，数据库也势必要切换到性能更强的数据库（比如，从关系型数据库到NoSql或时序数据库）。此时，kettle这个强大的ETL工具的用武之地就到了。那么kettle是如何在不同的数据库之间进行迁移的呢？下面就以一个真实的项目为例，详细讲解解决问题的思路，以及实践过程。

问题分析：

        前段时间我开发了一个物联网项目，大致的业务流程是这样的：采集红外对射管上的信号，当两个红外之间的信号被遮挡，红外管就会发出高电平信号也就是1，否则就发出低电平信号也就是0。最后通过4G终端以每10秒1次的上传频率，将信号推送到物联网平台。起初，项目是非常健壮的，但随着项目在生产环境中的大量使用，平台的数据量就会激增，就会发现平台的响应速度越来越慢。我就去查询原因，最后的结论就是数据库性能太差。因为项目周期非常短，就采用了mysql作为存储介质，myql作为一种关系型数据库，用它来存储时序数据是非常不合适的。针对数据库问题，我又做了大量的调研，最后决定使用TimescaleDB来替换mysql数据库，当然只是替换时序数据的存储，业务数据依然由mysql存储。这就涉及到需要将历史数据从mysql迁移到TimescaleDB，kettle就非常适合来解决这个问题。

解决方案：

数据迁移过程必须保证以下两点：

1. 数据完整性，数据不能有丢失，有重复；
2. 数据容错性，记录数据成功的数量，以及失败的记录；

迁移步骤：

• 查看mysql时序表，并在TimescaleDB创建相同的表

  mysql建表语句：

CREATE TABLE `rope_tskv` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `device_code` varchar(50) NOT NULL COMMENT '设备编码',
  `attr_key` varchar(50) NOT NULL COMMENT '属性key',
  `attr_value` varchar(50) NOT NULL COMMENT '属性value',
  `meter_ts` bigint(20) NOT NULL COMMENT '采集时间戳数字类型',
  `meter_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '采集时间戳日期类型',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `create_user` bigint(20) DEFAULT NULL COMMENT '创建人',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  `update_user` bigint(20) DEFAULT NULL COMMENT '修改人',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `idx_unq_dkm` (`device_code`,`attr_key`,`meter_ts`) USING BTREE COMMENT '唯一约束'
) ENGINE=InnoDB AUTO_INCREMENT=1471318125332408363 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='时序数据表';

 mysql数据预览，目前已有100000+条数据。

mysql时序表

TimescaleDB建表语句，这里去掉了id，create_user，update_time，update_user字段，因为时序数据是不能修改的，而且创建人永远是固定的。

CREATE TABLE "public"."rope_tskv" (
  "device_code" varchar(100) COLLATE "pg_catalog"."default" NOT NULL,
  "attr_key" varchar(100) COLLATE "pg_catalog"."default" NOT NULL,
  "attr_value" varchar(100) COLLATE "pg_catalog"."default" NOT NULL,
  "meter_ts" int8 NOT NULL,
  "meter_time" timestamptz(6) NOT NULL,
  "create_time" timestamptz(6) NOT NULL,
  CONSTRAINT "idx_unq_damm" UNIQUE ("device_code", "attr_key", "meter_ts", "meter_time")
)
;

ALTER TABLE "public"."rope_tskv" 
  OWNER TO "postgres";

COMMENT ON COLUMN "public"."rope_tskv"."device_code" IS '设备编码';

COMMENT ON COLUMN "public"."rope_tskv"."attr_key" IS '属性key';

COMMENT ON COLUMN "public"."rope_tskv"."attr_value" IS '属性value';

COMMENT ON COLUMN "public"."rope_tskv"."meter_ts" IS '采集时间戳数字类型';

COMMENT ON COLUMN "public"."rope_tskv"."meter_time" IS '采集时间戳日期类型';

COMMENT ON COLUMN "public"."rope_tskv"."create_time" IS '创建时间';

COMMENT ON CONSTRAINT "idx_unq_damm" ON "public"."rope_tskv" IS '唯一约束';


CREATE EXTENSION IF NOT EXISTS timescaledb;    -- 添加timescaledb扩展

SELECT create_hypertable('rope_tskv', 'meter_time');    -- 指定超表字段

TimescaleDB预览：没有任何数据

•  编写kettle转换文件

选型转换节点：

编写转换文件的第一步就是要确定使用哪些节点。很明显整个过程需要将数据从不同类型的数据库之间进行迁移，“表输入”节点是用于从数据库中抽取数据，“表输出”节点是向数据库中写入数据，这两个节点刚好符合要求。如果在迁移过程中有什么错误，就需要将错误信息报错到excel中，方便后续的排错处理。所以需要使用“表输入”、“表输出”、“Excel输出”这三个节点。

编写转换：

就是将以上的是三个节点拼装在一起。具体操作如下图所示。     

配置转换：

“表输入”节点需要连接mysql数据库，并配置对应的sql脚本；

“表输出”节点需要连接TimescaleDB数据库，并配置与mysql数据表的字段映射关系；

“Excel输出”节点需要配置错误文件的输出路径；

• 执行迁移

执行开始：

执行结果：

       根据上面的执行步骤，可以看出共迁移了108212条数据，错误数据为0条；

错误分析：

        因为没有错误，所以无法进行分析。那就需要人为的创建错误，来验证转换的正确性。最好的方法就是，再次执行这个转换，因为迁移已经完成了，所以所有的数据都会报错，全部都会保存于excel文件中。重新执行过程，如下图所示：

 从表输出的执行过程发现，所有的数据都会被拒绝，然后会将所有的数据保存到excel表格中。

excel数据如下：

 总结：

        从实际的案例出发，讲解如何动手去设计、实践、验证一个数据迁移的过程。在实践过程中，要注意不同的数据库连接，要注意字段之间的对应关系。总之，只要遵守设计、实践、验证这套方法论下来，就能高效的解决问题。