--mysql数据库：create_time timestamp
CREATE TABLE media_account_application (
id bigint(20) NOT NULL AUTO_INCREMENT,
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
PRIMARY KEY (id),
) ENGINE=InnoDB AUTO_INCREMENT=784810 DEFAULT CHARSET=utf8mb4;
--doris2.0创建测试表create_time_datetime datetime、create_time_varchar varchar(256)
CREATE TABLE ods_uniagency.test_media_account_application (
id bigint(20) NULL,
create_time_datetime datetime NULL COMMENT '创建时间',
update_time_datetime datetime NULL COMMENT '更新时间',
create_time_varchar varchar(256) NULL COMMENT '创建时间',
update_time_varchar varchar(256) NULL COMMENT '更新时间',
create_time_string string NULL COMMENT '创建时间',
update_time_string string NULL COMMENT '更新时间'
) ENGINE=OLAP
DUPLICATE KEY(id)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(id) BUCKETS 5
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"is_being_synced" = "false",
"storage_format" = "V2",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false"
);
--插入sql，dolphins任务调度器中执行
--1
INSERT INTO ods_uniagency.test_media_account_application
( create_time_datetime,
update_time_datetime)
SELECT
CAST(create_time AS DATETIME) AS create_time_datetime,
CAST(update_time AS DATETIME) AS update_time_datetime
FROM hwsg_ua.uni_agency.media_account_application;
--2
INSERT INTO ods_uniagency.test_media_account_application
(
create_time_datetime,
update_time_datetime
)
SELECT
create_time AS create_time_datetime,
update_time AS update_time_datetime
FROM hwsg_ua.uni_agency.media_account_application;
--3
INSERT INTO ods_uniagency.test_media_account_application
( create_time_varchar,
update_time_varchar)
SELECT
DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s') AS create_time_string,
DATE_FORMAT(update_time, '%Y-%m-%d %H:%i:%s') AS update_time_string
FROM hwsg_ua.uni_agency.media_account_application;
