普元数据开发平台
产品介绍
安装部署
FAQ

# 多个动态的库名下表数据抽取示例

# 一、场景介绍：

1、源库，有A1-A4 共4个库。存储库名的表。schema_table。示例库：MYSQL数据库，ceshi1229、ceshi1115、ceshi714；StarRocks数据库：dws_licy

-- ceshi1115.schema_table definition

CREATE TABLE `schema_table` (
  `ORDERCODE` bigint(20) NOT NULL,
  `SCHEMA` varchar(96) DEFAULT NULL,
  `TYPE` varchar(96) DEFAULT NULL,
  `CREATEUSER` varchar(96) DEFAULT NULL,
  `CREATETIME` datetime DEFAULT NULL,
  `UPDATETIME` datetime DEFAULT NULL,
  PRIMARY KEY (`ORDERCODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO ceshi1115.schema_table (ORDERCODE,`SCHEMA`,`TYPE`,CREATEUSER,CREATETIME,UPDATETIME) VALUES
	 (0,'ceshi1229','MYSQL','LICESHI','2022-10-11 00:00:00','2022-10-11 00:00:00'),
	 (1,'ceshi714','MYSQL','LICESHI','2025-07-11 00:00:00','2025-07-11 00:00:00'),
	 (2,'ceshi1115','MYSQL','LICESHI111','2024-07-11 00:00:00','2025-07-11 00:00:00'),
	 (3,'dws_licy','StarRocks','LICESHI','2024-07-11 00:00:00','2025-07-11 00:00:00');

2、每个库里都有一张相同表结构的表userinfo。

CREATE TABLE `userinfo` (
  `usercode` varchar(32) NOT NULL COMMENT '用户编码',
  `username` varchar(32) DEFAULT NULL COMMENT '用户姓名',
  `sex` varchar(16) DEFAULT NULL COMMENT '性别',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `unitcode` varchar(32) DEFAULT NULL COMMENT '所属单位',
  `deptcode` varchar(32) DEFAULT NULL COMMENT '所属部门',
  `telephone` varchar(32) DEFAULT NULL COMMENT '联系方式',
  `createtime` date DEFAULT NULL COMMENT '创建时间',
  `updatetime` date DEFAULT NULL COMMENT '更新时间',
  `test` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`usercode`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='用户info';

INSERT INTO ceshi1115.userinfo (usercode,username,sex,age,unitcode,deptcode,telephone,createtime,updatetime,test) VALUES
	 ('1','张聪','男',25,'普元信息','技术部','18091256632','2022-10-11','2018-12-20',NULL),
	 ('10','王林','男',25,'普元信息','技术部','15901214578','2022-10-11','2018-12-20','2'),
	 ('2','王强','男',25,'普元信息','售后','15389012334','2022-10-11','2018-12-20',NULL),
	 ('20','王s林','男',25,'普元信息','技术部','15901214578','2022-10-11','2018-12-20','2'),
	 ('21','张ff聪','男',25,'普元信息','技术部','18091256632','2022-10-11','2018-12-20',NULL),
	 ('22','王ss强','男',25,'普元信息','售后','15389012334','2022-10-11','2018-12-20',NULL),
	 ('23','魏s雨','女',26,'普元信息','售后','15309872351','2022-10-11','2018-12-20','3'),
	 ('3','魏雨','女',26,'普元信息','售后','15309872351','2022-10-11','2018-12-20','3'),
	 ('4','刘飞','男',25,'普元信息','事业部','15091345789','2022-10-11','2018-12-20',NULL),
	 ('5','许馨','女',25,'普元信息','售后','13090012019','2022-10-11','2018-12-20','4');
INSERT INTO ceshi1115.userinfo (usercode,username,sex,age,unitcode,deptcode,telephone,createtime,updatetime,test) VALUES
	 ('6','蔡亮','男',25,'普元信息','技术部','19081234789','2022-10-11','2018-12-20','5'),
	 ('7','张强','男',25,'普元信息','事业部','19087652123','2022-10-11','2018-12-20','6'),
	 ('8','杨孟','男',25,'普元信息','事业部','18790123567','2022-10-11','2018-12-20','6'),
	 ('9','李建国','男',25,'普元信息','技术部','15998760132','2022-10-11','2018-12-20',NULL);


INSERT INTO ceshi1229.userinfo (usercode,username,sex,age,unitcode,deptcode,telephone,createtime,updatetime,test) VALUES
	 ('30','林','男',25,'普元信息','技术部','15901214578','2022-10-11','2018-12-20','2'),
	 ('31','聪','男',25,'普元信息','技术部','18091256632','2022-10-11','2018-12-20',NULL),
	 ('32','强','男',25,'普元信息','售后','15389012334','2022-10-11','2018-12-20',NULL),
	 ('33','王s','男',25,'普元信息','技术部','15901214578','2022-10-11','2018-12-20','2'),
	 ('34','张ff','男',25,'普元信息','技术部','18091256632','2022-10-11','2018-12-20',NULL),
	 ('35','王s','男',25,'普元信息','售后','15389012334','2022-10-11','2018-12-20',NULL);


INSERT INTO ceshi714.userinfo (usercode,username,sex,age,unitcode,deptcode,telephone,createtime,updatetime,test) VALUES
	 ('40','蔡亮','男',24,'普元信息','技术部','19081234789','2018-12-12','2023-11-30',NULL),
	 ('41','张强','男',27,'普元信息','事业部11','19087652123','2018-12-12','2023-11-16',NULL),
	 ('42','杨孟','男',30,'普元信息','事业部','18790123567','2018-12-12','2019-12-12',NULL),
	 ('43','李建国','男',29,'普元信息','技术部aaaaa','15998760132','2018-12-12','2018-12-12',NULL),
	 ('44','张聪','男',30,'普元信息','技术部','18091256632','2018-12-12','2018-12-12',NULL),
	 ('45','王林','男',25,'普元信息','技术部','15901214578','2018-12-12','2018-12-12',NULL),
	 ('46','张聪','男',30,'普元信息','技术部','18091256632','2018-12-12','2018-12-12',NULL),
	 ('47','钟馗','男',120,'道111','道','444','1000-12-12','1000-12-12',NULL),
	 ('48','AA','男',10,'AA111','AA111','AA','2025-01-01','2025-02-01',NULL),
	 ('49','王强','男',25,'普元信息','售后','15389012334','2018-12-12','2018-12-12',NULL);
INSERT INTO ceshi714.userinfo (usercode,username,sex,age,unitcode,deptcode,telephone,createtime,updatetime,test) VALUES
	 ('50','魏雨','女',26,'普元信息','售后','15309872351','2018-12-12','2018-12-13',NULL),
	 ('51','刘飞','男',25,'普元信息','事业部','15091345789','2018-12-12','2018-12-12',NULL),
	 ('52','许馨','女',25,'普元信息','售后','13090012019','2018-12-12','2023-12-12',NULL);

3、在目标库X里，userinfo表已经建好。示例中目标库：MYSQL数据库，ceshi0808；目标表：userinfo

-- ceshi0808.userinfo definition
CREATE TABLE `userinfo` (
  `usercode` varchar(32) NOT NULL COMMENT '用户编码',
  `username` varchar(32) DEFAULT NULL COMMENT '用户姓名',
  `sex` varchar(16) DEFAULT NULL COMMENT '性别',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `unitcode` varchar(32) DEFAULT NULL COMMENT '所属单位',
  `deptcode` varchar(32) DEFAULT NULL COMMENT '所属部门',
  `telephone` varchar(32) DEFAULT NULL COMMENT '联系方式',
  `createtime` date DEFAULT NULL COMMENT '创建时间',
  `updatetime` date DEFAULT NULL COMMENT '更新时间',
  `test` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`usercode`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='用户info';

4、将A1.B-A4.B这4个库中，属于mysql类型的数据库，userinfo表的数据，合并到目标表X.userinfo里。

# 二、主流程

循环读取数据为编排作业：

主job结构：

【1】转换：查询表中库名——用来获取数据库名

【2】编排：获取变量并处理数据 —— 获取库名，设置参数，获取参数进行循环抽数。

若需要在作业调度模块调度执行，则需要将该编排作业在作业流中引用并提交。本示例仅介绍编排及转换的具体实现。

Snipaste_2025-08-08_16-58-13.png

# 三、子转换及子编排配置

# 1. 转换作业-获取数据库名

Snipaste_2025-08-08_17-00-02

  1. 获取数据库名

    Snipaste_2025-08-08_17-00-40

    这里用表输入组件，sql句子的含义是查询ceshi1115数据库中schema_table表，数据库类型

     SELECT `SCHEMA` FROM `ceshi1115`.`schema_table` where TYPE = 'MYSQL'

  2. 复制记录到结果

    Snipaste_2025-08-08_17-05-04

# 2. 转换作业-设置变量

Snipaste_2025-08-08_17-14-58

  1. 通过从结果获取记录获取上一步转换中的数据库名

    Snipaste_2025-08-08_17-16-11

  2. 设置变量

    Snipaste_2025-08-08_17-17-35

# 3. 转换作业-查询表数据

Snipaste_2025-08-08_17-06-40

  1. 通过变量查询指定数据库中userinfo表数据

    Snipaste_2025-08-08_17-08-29

    这里用表输入组件，sql句子的含义是通过变量查询指定数据库中userinfo表数据。注意要勾选替换sql语句中的变量。SCHEMANAME是设置变量步骤中设置的变量名。

     SELECT *FROM ${SCHEMANAME}.`userinfo`

  2. 目标表存储数据

    Snipaste_2025-08-08_17-13-04 这里用表输出组件，将数据同步到目标库的表中。

# 4. 编排作业-获取变量并处理数据

这个编排作业由2个转换组成，一个是设置变量，一个是处理数据。

Snipaste_2025-08-08_17-19-31

设置变量 处理数据

# 四、主编排作业配置

Snipaste_2025-08-08_17-30-50

Snipaste_2025-08-08_17-29-06

Snipaste_2025-08-08_17-29-46

引用的编排作业-获取参数循环抽数，需要勾选【执行每个输入行】

# 五、调试运行

Snipaste_2025-08-08_17-27-52

← FTP上传下载删除 →