【已解决】通过mysql catalog同步数据到doris,报错:'UNSUPPORTED_TYPE' in '`create_time`'

Viewed 90

--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;

2 Answers
  1. 可以把 insert 去掉,然后 执行下查询看能否查询;
  2. 另外你的版本是什么版本?旧版本可能会由于优化器回退导致,如果1 可以查询的话,在1 的语句前面加 explain 关键字,可以看见走的是新优化器还是老优化器,然后你再对比 在 insert 语句前面增加 explain 关键字,看一下是新优化器还是老优化器;
  3. 如果 insert 自动会退到老优化器,可以关闭优化器自动回退,然后再执行 insert 看是否报错
SET enable_fallback_to_original_planner=false;

你好,
doris是2.0版本的
直接查询也偶尔会成功,偶尔会失败报错:Unexpected exception: Nereids do not support type: UNSUPPORTED_TYPE