在实施etl过程中，经常会遇到不同类型之间的转换，方式有很多种，下面是项目中使用etl-engine进行数据类型转换的收集整理，方便日后工作中查阅。
etl-engine转换的方式有多种，一种是通过sql语句直接转换（比较方便），另一种是通过在输出节点的beforeout 标签中嵌入go脚本对相应字段按业务要求进行转换（功能强大），下面只介绍第一种情况。
元数据Metadata配置说明
元数据文件定义目标数据格式（如输出节点中定义的renameOutputFields或renameOutputTags所对应的字段名称及字段类型）
outputFields是输入节点中数据结果集中的字段名称， 将outputFields定义的字段转换成renameOutputFields定义的字段，其renameOutputFields转换格式通过元数据文件来定义。
| 属性 | 说明 | 适合 |
|---|---|---|
| id | 唯一标示 | |
| field | ||
| name | 输出数据源的字段名称 | renameOutputFields, renameOutputTags |
| type | 输出数据源的字段类型 | string,int,int32,float, str_timestamp,decimal, datetime,timestamp,bool,blob |
| default | 默认值 | 当nullable为false时，如果输出值为空字符串，则可以通过default来指定输出的默认值 |
| nullable | 是否允许为空 | false是不允许为空，必须和default配合使用。true是允许为空。 通过增加一个field并设置nullable为true可以实现增加字段功能。 |
| errDefault | 如果输入数据向输出数据类型转换失败时，是否启动默认值 | 如果设置值,则转换出错时也能向下执行,即出错的值使用该默认值, 如果不设置该值,则转换出错时不能向下执行。 |
| dataFormat | 对日期输出格式的配置 | string -> datetime 或 datetime -> string 需要配置日期格式 日期格式配置如: YYYY-MM-DD hh:mm:ss 或YYYY-MM-DD hh:mm:ssZ+8h |
| dataLen | 对小数位格式的配置 | string -> decimal 格式设置输出数字小数点位数,代表保留小数点后几位 ; 或 输出string类型时 格式设置代表输出内容的总长度 |
样本
mysql
字符串类型 转换 日期类型
| 源数据库 | 字符串string | 目标数据库 | 日期datetime |
|---|---|---|---|
| mysql | 2023-01-01 10:11:12 | mysql | 元数据输出字段无需特殊配置 type=“datetime” |
| mysql | 2023-01-01 10:11:12 | sqlserver | 元数据输出字段无需特殊配置 type=“datetime” |
| mysql | 2023-01-01 10:11:12 | postgres | 元数据输出字段无需特殊配置 type=“datetime” |
| mysql | 2023-01-01 10:11:12 | oracle | 元数据输出字段无需特殊配置 type=“datetime” |
日期类型 转换 字符串类型
| 源数据库 | 日期datetime | 目标数据库 | 字符串string |
|---|---|---|---|
| mysql | 2023-01-01 10:11:12 | mysql | 元数据输出字段无需特殊配置 type=“string” |
| mysql | 2023-01-01 10:11:12 | sqlserver | 元数据输出字段无需特殊配置 type=“string” |
| mysql | 2023-01-01 10:11:12 | postgres | 元数据输出字段无需特殊配置 type=“string” |
| mysql | 2023-01-01 10:11:12 | oracle | 元数据输出字段无需特殊配置 type=“string” |
日期类型 转换 日期类型
| 源数据库 | 日期datetime | 目标数据库 | 日期datetime |
|---|---|---|---|
| mysql | 2023-01-01 10:11:12 | mysql | 元数据输出字段无需特殊设置 type=“datetime” 有的情况需要配置+8小时 dataFormat=“YYYY-MM-DD hh:mm:ssZ+8h” dataLen=“19” |
| mysql | 2023-01-01 10:11:12 | sqlserver | 元数据输出字段无需特殊设置 type=“datetime” |
| mysql | 2023-01-01 10:11:12 | postgres | 元数据输出字段无需特殊设置 type=“datetime” |
| mysql | 2023-01-01 10:11:12 | oracle | 元数据输出字段无需特殊设置 type=“datetime” |
mysql样本表
CREATE TABLE `t_source_1` (
`f1` varchar(32) NOT NULL,
`f2` varchar(32) DEFAULT NULL,
`f3` int(11) DEFAULT NULL,
`f4` varchar(32) DEFAULT NULL,
`f5` datetime DEFAULT NULL
);
CREATE TABLE `t_target_1` (
`c1` varchar(32) NOT NULL,
`c2` varchar(32) DEFAULT NULL,
`c3` int(11) DEFAULT '0',
`c4` varchar(32) DEFAULT NULL,
`c5` datetime DEFAULT NULL
);
INSERT INTO t_source_1 (f1, f2, f3, f4, f5)
VALUES('1', 'a01', 1, '2022-07-10 23:20:22', '2022-07-10 23:20:22');
INSERT INTO t_source_1 (f1, f2, f3, f4, f5)
VALUES('10', '测试人员10', 10, '2022-07-10 23:20:13', '2022-07-10 23:20:13');
INSERT INTO t_source_1 (f1, f2, f3, f4, f5)
VALUES('11', '测试人员11', 11, '2022-07-10 23:20:14', '2022-07-10 23:20:14');
INSERT INTO t_source_1 (f1, f2, f3, f4, f5)
VALUES('12', '测试人员12', 12, '2022-07-10 23:20:15', '2022-07-10 23:20:15');
INSERT INTO t_source_1 (f1, f2, f3, f4, f5)
VALUES('13', '测试人员13', 13, '2022-07-10 23:20:16', '2022-07-10 23:20:16');
INSERT INTO t_source_1 (f1, f2, f3, f4, f5)
VALUES('14', '测试人员14', 14, '2022-07-10 23:20:17', '2022-07-10 23:20:17');
INSERT INTO t_source_1 (f1, f2, f3, f4, f5)
VALUES('15', '测试人员15', 15, '2022-07-10 23:20:18', '2022-07-10 23:20:18');
INSERT INTO t_source_1 (f1, f2, f3, f4, f5)
VALUES('16', '测试人员16', 16, '2022-07-10 23:20:19', '2022-07-10 23:20:19');
INSERT INTO t_source_1 (f1, f2, f3, f4, f5)
VALUES('17', '测试人员17', 17, '2022-07-10 23:20:20', '2022-07-10 23:20:20');
INSERT INTO t_source_1 (f1, f2, f3, f4, f5)
VALUES('18', '测试人员18', 18, '2022-07-10 23:20:21', '2022-07-10 23:20:21');
INSERT INTO t_source_1 (f1, f2, f3, f4, f5)
VALUES('19', '测试人员19', 19, '2022-07-10 23:20:01', '2022-07-10 23:20:01');
INSERT INTO t_source_1 (f1, f2, f3, f4, f5)
VALUES('2', 'a01', 2, '2022-07-10 23:20:23', '2022-07-10 23:20:23');
INSERT INTO t_source_1 (f1, f2, f3, f4, f5)
VALUES('20', '测试人员20', 20, '2022-07-10 23:20:02', '2022-07-10 23:20:02');
INSERT INTO t_source_1 (f1, f2, f3, f4, f5)
VALUES('3', 'c01', 3, '2022-07-10 23:20:24', '2022-07-10 23:20:24');
INSERT INTO t_source_1 (f1, f2, f3, f4, f5)
VALUES('4', 'd01', 4, '2022-07-10 23:20:25', '2022-07-10 23:20:25');
INSERT INTO t_source_1 (f1, f2, f3, f4, f5)
VALUES('5', 'e01', 5, '2022-07-10 23:20:26', '2022-07-10 23:20:26');
INSERT INTO t_source_1 (f1, f2, f3, f4, f5)
VALUES('6', 'f01', 6, '2022-07-10 23:20:27', '2022-07-10 23:20:27');
INSERT INTO t_source_1 (f1, f2, f3, f4, f5)
VALUES('7', 'g01', 7, '2022-07-10 23:20:28', '2022-07-10 23:20:28');
INSERT INTO t_source_1 (f1, f2, f3, f4, f5)
VALUES('8', 'h01', 8, '2022-07-10 23:20:29', '2022-07-10 23:20:29');
INSERT INTO t_source_1 (f1, f2, f3, f4, f5)
VALUES('9', 'i01', 9, '2022-07-10 23:20:12', '2022-07-10 23:20:12');
sqlserver
字符串类型 转换 日期类型
| 源数据库 | 字符串string | 目标数据库 | 日期datetime |
|---|---|---|---|
| sqlserver | 2023-01-01 10:11:12 | mysql | 元数据输出字段设置 type=“datetime” dataFormat=“YYYY-MM-DD hh:mm:ssZ+8h” dataLen=“19” 其实是模拟成sqlserver字符串格式转换成mysql日期格式进行入库 |
| sqlserver | 2023-01-01 10:11:12 | sqlserver | 元数据输出字段设置 type=“datetime” |
| sqlserver | 2023-01-01 10:11:12 | postgres | 元数据输出字段设置 type=“datetime” |
| sqlserver | 2023-01-01 10:11:12 | oracle | 元数据输出字段设置 type=“datetime” |
日期类型 转换 字符串类型
| 源数据库 | 日期datetime | 目标数据库 | 字符串string |
|---|---|---|---|
| sqlserver | 2023-01-01 10:11:12.000 | mysql | 元数据输出字段设置 type=“datetime” dataFormat=“YYYY-MM-DDThh:mm:ssZ+8h” dataLen=“19” 其实是模拟成sqlserver日期格式转换成mysql的日期格式进行入库 |
| sqlserver | 2023-01-01 10:11:12.000 | sqlserver | 源数据查询语句中将日期字段如:f5 通过conver函数转换成YYYY-MM-DD HH:MI:SS格式 CONVERT(VARCHAR, f5, 120) as f5,元数据输出字段设置 type=“string” |
| sqlserver | 2023-01-01 10:11:12.000 | postgres | 源数据查询语句中将日期字段如:f5 通过conver函数转换成YYYY-MM-DD HH:MI:SS格式 CONVERT(VARCHAR, f5, 120) as f5,元数据输出字段设置 type=“string” |
| sqlserver | 2023-01-01 10:11:12.000 | oracle | 源数据查询语句中将日期字段如:f5 通过conver函数转换成YYYY-MM-DD HH:MI:SS格式 CONVERT(VARCHAR, f5, 120) as f5,元数据输出字段设置 type=“string” |
日期类型 转换 日期类型
| 源数据库 | 日期datetime | 目标数据库 | 日期datetime |
|---|---|---|---|
| sqlserver | 2023-01-01 10:11:12.000 | sqlserver | 元数据输出字段设置 type=“string” dataFormat=“YYYY-MM-DDThh:mm:ss” dataLen=“19” |
| sqlserver | 2023-01-01 10:11:12.000 | mysql | 元数据输出字段设置 type=“string” dataFormat=“YYYY-MM-DDThh:mm:ss” dataLen=“19” |
| sqlserver | 2023-01-01 10:11:12.000 | postgres | 元数据输出字段设置 type=“datetime” dataFormat=“YYYY-MM-DDThh:mm:ss” dataLen=“19” |
| sqlserver | 2023-01-01 10:11:12.000 | oracle | 元数据输出字段设置 type=“datetime” dataFormat=“YYYY-MM-DDThh:mm:ss” dataLen=“19” |
样本表
CREATE TABLE t_source_1 (
f1 varchar(32) NOT NULL,
f2 varchar(32) DEFAULT NULL,
f3 integer DEFAULT '0',
f4 varchar(32) ,
f5 datetime
) ;
CREATE TABLE t_target_1 (
c1 varchar(32) NOT NULL,
c2 varchar(32) DEFAULT NULL,
c3 integer DEFAULT '0',
c4 varchar(32),
c5 datetime
) ;
日期转换字符串样本
postgres
字符串类型 转换 日期类型
| 源数据库 | 字符串string | 目标数据库 | 日期datetime |
|---|---|---|---|
| postgres | 2023-01-01 10:11:12 | mysql | 元数据输出字段设置 type=“datetime” dataFormat=“YYYY-MM-DD hh:mm:ssZ+8h” dataLen=“19” 其实是模拟成pg字符串格式转换成mysql日期格式进行入库 |
| postgres | 2023-01-01 10:11:12 | sqlserver | 元数据输出字段设置 type=“datetime” |
| postgres | 2023-01-01 10:11:12 | postgres | 元数据输出字段设置 type=“datetime” |
| postgres | 2023-01-01 10:11:12 | oracle | 元数据输出字段设置 type=“datetime” |
日期类型 转换 字符串类型
| 源数据库 | 日期datetime | 目标数据库 | 字符串string |
|---|---|---|---|
| postgres | 2023-01-01 10:11:12.000 | mysql | 源数据查询语句中将日期字段如:f5 通过to_char函数转换成YYYY-MM-DD HH:MI:SS格式,并加8小时 to_char(f5 + INTERVAL ‘8 hours’, ‘YYYY-MM-DD HH24:MI:SS’) as f5 ,元数据输出字段设置 type=“string” |
| postgres | 2023-01-01 10:11:12.000 | sqlserver | 源数据查询语句中将日期字段如:f5 通过to_char函数转换成YYYY-MM-DD HH:MI:SS格式,to_char(f5 , ‘YYYY-MM-DD HH24:MI:SS’) as f5 ,元数据输出字段设置 type=“string” |
| postgres | 2023-01-01 10:11:12.000 | postgres | 源数据查询语句中将日期字段如:f5 通过to_char函数转换成YYYY-MM-DD HH:MI:SS格式,to_char(f5 , ‘YYYY-MM-DD HH24:MI:SS’) as f5 ,元数据输出字段设置 type=“string” |
| postgres | 2023-01-01 10:11:12.000 | oracle | 源数据查询语句中将日期字段如:f5 通过to_char函数转换成YYYY-MM-DD HH:MI:SS格式,to_char(f5 , ‘YYYY-MM-DD HH24:MI:SS’) as f5 ,元数据输出字段设置 type=“string” |
日期类型 转换 日期类型
| 源数据库 | 日期datetime | 目标数据库 | 日期datetime |
|---|---|---|---|
| postgres | 2023-01-01 10:11:12.000 | mysql | 元数据输出字段设置 type=“string” dataFormat=“YYYY-MM-DDThh:mm:ss” dataLen=“19” |
| postgres | 2023-01-01 10:11:12.000 | sqlserver | 元数据输出字段设置 type=“datetime” dataFormat=“YYYY-MM-DDThh:mm:ss” dataLen=“19” |
| postgres | 2023-01-01 10:11:12.000 | postgres | 元数据输出字段设置 type=“string” dataFormat=“YYYY-MM-DDThh:mm:ss” dataLen=“19” |
| postgres | 2023-01-01 10:11:12.000 | oracle | 元数据输出字段设置 type=“datetime” dataFormat=“YYYY-MM-DDThh:mm:ss” dataLen=“19” |
样本表
CREATE TABLE t_source_1 (
f1 varchar(32) NOT NULL,
f2 varchar(32) DEFAULT NULL,
f3 integer DEFAULT '0',
f4 varchar(32) ,
f5 timestamp
) ;
CREATE TABLE t_target_1 (
c1 varchar(32) NOT NULL,
c2 varchar(32) DEFAULT NULL,
c3 integer DEFAULT '0',
c4 varchar(32),
c5 timestamp
) ;
oracle
字符串类型 转换 日期类型
| 源数据库 | 字符串string | 目标数据库 | 日期datetime |
|---|---|---|---|
| oracle | 2023-01-01 10:11:12 | mysql | 元数据输出字段设置 type=“datetime” dataFormat=“YYYY-MM-DD hh:mm:ssZ+8h” dataLen=“19” 注意：由于oracle字段名称读出来是大写，因此要注意sql语句中字段大写,outputFields属性要设置大写,如outputFields=“F1;F2;F3;F4;F5”,否则读不出数据 |
| oracle | 2023-01-01 10:11:12 | sqlserver | 元数据输出字段设置 type=“datetime” dataFormat=“YYYY-MM-DD hh:mm:ss” dataLen=“19” 注意：由于oracle字段名称读出来是大写，因此要注意sql语句中字段大写,outputFields属性要设置大写,如outputFields=“F1;F2;F3;F4;F5”,否则读不出数据 |
| oracle | 2023-01-01 10:11:12 | postgres | 元数据输出字段设置 type=“datetime” 注意：由于oracle字段名称读出来是大写，因此要注意sql语句中字段大写,outputFields属性要设置大写,如outputFields=“F1;F2;F3;F4;F5”,否则读不出数据 |
| oracle | 2023-01-01 10:11:12 | oracle | 元数据输出字段设置 type="datetime"注意：由于oracle字段名称读出来是大写，因此要注意sql语句中字段大写,outputFields属性要设置大写,如outputFields=“F1;F2;F3;F4;F5”,否则读不出数据 |
日期类型 转换 字符串类型
| 源数据库 | 日期datetime | 目标数据库 | 日期string |
|---|---|---|---|
| oracle | 2023-01-01 10:11:12.000 | mysql | 源数据查询语句中将日期字段如:f5 通过to_char函数转换成YYYY-MM-DD hh24:mi:ss格式, to_char(F5,‘YYYY-MM-DD hh24:mi:ss’) AS F5,元数据输出字段设置 type=“string” |
| oracle | 2023-01-01 10:11:12.000 | sqlserver | 源数据查询语句中将日期字段如:f5 通过to_char函数转换成YYYY-MM-DD hh24:mi:ss格式, to_char(F5,‘YYYY-MM-DD hh24:mi:ss’) AS F5,元数据输出字段设置 type=“string” |
| oracle | 2023-01-01 10:11:12.000 | postgres | 源数据查询语句中将日期字段如:f5 通过to_char函数转换成YYYY-MM-DD hh24:mi:ss格式, to_char(F5,‘YYYY-MM-DD hh24:mi:ss’) AS F5,元数据输出字段设置 type=“string” |
| oracle | 2023-01-01 10:11:12.000 | oracle | 源数据查询语句中将日期字段如:f5 通过to_char函数转换成YYYY-MM-DD hh24:mi:ss格式, to_char(F5,‘YYYY-MM-DD hh24:mi:ss’) AS F5,元数据输出字段设置 type=“string” |
日期类型 转换 日期类型
| 源数据库 | 日期datetime | 目标数据库 | 日期datetime |
|---|---|---|---|
| oracle | 2023-01-01 10:11:12.000 | mysql | 源数据查询语句中将日期字段如:f5 通过to_char函数转换成YYYY-MM-DD hh24:mi:ss格式, to_char(F5,‘YYYY-MM-DD hh24:mi:ss’) AS F5,元数据输出字段设置 type=“datetime” dataFormat=“YYYY-MM-DD hh:mm:ssZ+8h” dataLen=“19” （这里加了8小时） 注意：由于oracle字段名称读出来是大写，因此要注意sql语句中字段大写,outputFields属性要设置大写,如outputFields=“F1;F2;F3;F4;F5”,否则读不出数据 |
| oracle | 2023-01-01 10:11:12.000 | sqlserver | 源数据查询语句中将日期字段如:f5 通过to_char函数转换成YYYY-MM-DD hh24:mi:ss格式,to_char(F5,‘YYYY-MM-DD hh24:mi:ss’) AS F5, 元数据输出字段设置 type=“datetime” dataFormat=“YYYY-MM-DD hh:mm:ss” dataLen=“19” （这里没加8小时） 注意：由于oracle字段名称读出来是大写，因此要注意sql语句中字段大写,outputFields属性要设置大写,如outputFields=“F1;F2;F3;F4;F5”,否则读不出数据 |
| oracle | 2023-01-01 10:11:12.000 | postgres | 源数据查询语句中将日期字段如:f5 通过to_char函数转换成YYYY-MM-DD hh24:mi:ss格式,to_char(F5,‘YYYY-MM-DD hh24:mi:ss’) AS F5, 元数据输出字段设置 type=“datetime” dataFormat=“YYYY-MM-DD hh:mm:ss” dataLen=“19” （这里没加8小时） 注意：由于oracle字段名称读出来是大写，因此要注意sql语句中字段大写,outputFields属性要设置大写,如outputFields=“F1;F2;F3;F4;F5”,否则读不出数据 |
| oracle | 2023-01-01 10:11:12.000 | oracle | 源数据查询语句中将日期字段如:f5 通过to_char函数转换成YYYY-MM-DD hh24:mi:ss格式,to_char(F5,‘YYYY-MM-DD hh24:mi:ss’) AS F5, 元数据输出字段设置 type="datetime"注意：由于oracle字段名称读出来是大写，因此要注意sql语句中字段大写,outputFields属性要设置大写,如outputFields=“F1;F2;F3;F4;F5”,否则读不出数据 |
样本表
CREATE TABLE t_source_1 (
f1 varchar2(32) NOT NULL,
f2 varchar2(32) DEFAULT NULL,
f3 integer DEFAULT '0',
f4 varchar2(32) ,
f5 timestamp
) ;
CREATE TABLE t_target_1 (
c1 varchar2(32) NOT NULL,
c2 varchar2(32) DEFAULT NULL,
c3 integer DEFAULT '0',
c4 varchar2(32),
c5 timestamp
) ;
