【已解决】Doris通过catalog查另一个Doris中的视图报错

Viewed 79
catalog 2.0

Doris版本：2.0.8
通过catalog查另一个Doris中改的视图：

select * from dorisxy_zxbi.self_bi.v_xy_overlay_capacity_realtime_ByMin;

报错：

[HY000][1105] errCode = 2, detailMessage = Unexpected exception: java.util.UnknownFormatConversionException: Conversion = '.'

视图创建语句：

CREATE VIEW `v_xy_overlay_capacity_realtime_ByMin` COMMENT 'VIEW' AS SELECT `deviceCode` AS `deviceCode`, `time` AS `time`, `modelId` AS `modelId`, `tagCode` AS `tagCode`, `orgCode` AS `orgCode`, `value` AS `value`, `sendTime` AS `sendTime`, CASE WHEN `de`.`area_num` = 1 THEN '一车间' WHEN `de`.`area_num` = 2 THEN '二车间' WHEN `de`.`area_num` = 3 THEN '三车间' ELSE '其他' END AS `workshop_name`, CASE WHEN `de`.`line_num` = 1 THEN '1线' WHEN `de`.`line_num` = 2 THEN '2线' WHEN `de`.`line_num` = 3 THEN '3线' WHEN `de`.`line_num` = 4 THEN '4线' WHEN `de`.`line_num` = 5 THEN '5线' END AS `line_name` FROM `default_cluster:scada_xy`.`xy_overlay_capacity` ca  INNER JOIN `ems_xy_scada`.`default_cluster:xy_scada`.`t_device` de ON `de`.`device_code` = `ca`.`deviceCode` WHERE CAST(`ca`.`time` AS DATETIME) >= now() - INTERVAL 2 HOUR AND right(`ca`.`tagCode`, 2) = lpad(date_format(CAST(`ca`.`time` AS DATETIME), '%H'), 2, '0');

视图中表信息
scada_xy.xy_overlay_capacity

CREATE TABLE `xy_overlay_capacity` (
  `deviceCode` varchar(128) NULL COMMENT '设备编码',
  `time` varchar(128) NULL COMMENT '采集时间',
  `modelId` text NULL COMMENT '模型ID',
  `tagCode` text NULL COMMENT '总产能编码',
  `orgCode` text NULL COMMENT '基地编码',
  `value` text NULL COMMENT '数据',
  `sendTime` text NULL COMMENT '发送数据时间'
) ENGINE=OLAP
UNIQUE KEY(`deviceCode`, `time`)
COMMENT '咸阳叠焊机'
DISTRIBUTED BY HASH(`deviceCode`) BUCKETS 3
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"is_being_synced" = "false",
"storage_format" = "V2",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"binlog.enable" = "false",
"binlog.ttl_seconds" = "86400",
"binlog.max_bytes" = "9223372036854775807",
"binlog.max_history_nums" = "9223372036854775807",
"enable_single_replica_compaction" = "false"
);

ems_xy_scada.xy_scada.t_device

CREATE TABLE `t_device` (
  `device_id` varchar(36) NOT NULL COMMENT '设备ID',
  `device_name` varchar(100) NOT NULL COMMENT '设备名称',
  `device_code` varchar(100) NOT NULL COMMENT '设备编码',
  `area_num` int(11) NOT NULL COMMENT '车间号',
  `line_num` int(11) NOT NULL COMMENT '线别',
  `collection_path` varchar(100) NOT NULL COMMENT '采集路径',
  `type_id` varchar(36) NULL COMMENT '设备类型ID',
  `collector_id` int(11) NULL COMMENT '采集器ID',
  `state` int(11) NULL DEFAULT "0" COMMENT '是否采集',
  `location_code` varchar(100) NULL COMMENT '位置码',
  `status` varchar(50) NULL COMMENT '设备状态',
  `collection_status` bigint(20) NOT NULL DEFAULT "0" COMMENT '连接状态',
  `status_path` varchar(100) NULL COMMENT '设备状态采集点',
  `test_path` varchar(255) NULL COMMENT '测试连接状态',
  `capacity` varchar(500) NULL COMMENT '产能',
  `channel_id` varchar(36) NULL COMMENT '通道id',
  `isvirtual` int(11) NOT NULL DEFAULT "0" COMMENT '是否虚拟设备',
  `reform_flag` int(11) NOT NULL DEFAULT "0" COMMENT '改造标记',
  `reference_device_id` int(11) NOT NULL DEFAULT "0" COMMENT '引用设备',
  `cal_cap_flag` int(11) NULL DEFAULT "0" COMMENT '计算产能标记',
  `cal_break_flag` int(11) NULL DEFAULT "0" COMMENT '计算故障率标记',
  `radio` float NULL DEFAULT "1.00" COMMENT '倍率',
  `ip` varchar(50) NULL COMMENT 'IP地址',
  `port` int(11) NULL COMMENT '端口',
  `digital_twin` json NULL COMMENT '数字孪生位置,模型字段',
  `protocol_id` int(11) NULL COMMENT '通信类型',
  `parent_id` varchar(36) NOT NULL DEFAULT "-1" COMMENT '网关子设备的父节点id其他-1',
  `main_device_code` varchar(25) NULL DEFAULT "" COMMENT '主设备编码（EAM设备编码）',
  `connect_type` int(11) NULL DEFAULT "0" COMMENT '设备连接类型;2网关类型、1子设备类型、0直连类型',
  `branch` varchar(10) NULL,
  `online_rate_flag` int(11) NULL DEFAULT "0" COMMENT '在线率计算标记'
) ENGINE=JDBC_EXTERNAL_TABLE;

fe.log详细报错

2024-07-22 16:04:49,208 WARN (mysql-nio-pool-853|11681) [StmtExecutor.executeByLegacy():819] execute Exception. stmt[38404, c1939a0552d84610-b
50fd558f5f244a7]
org.apache.doris.common.AnalysisException: errCode = 2, detailMessage = Unexpected exception: java.util.UnknownFormatConversionException: Conv
ersion = '.'
        at org.apache.doris.qe.StmtExecutor.analyze(StmtExecutor.java:1039) ~[doris-fe.jar:1.2-SNAPSHOT]
        at org.apache.doris.qe.StmtExecutor.executeByLegacy(StmtExecutor.java:712) ~[doris-fe.jar:1.2-SNAPSHOT]
        at org.apache.doris.qe.StmtExecutor.execute(StmtExecutor.java:484) ~[doris-fe.jar:1.2-SNAPSHOT]
        at org.apache.doris.qe.StmtExecutor.execute(StmtExecutor.java:452) ~[doris-fe.jar:1.2-SNAPSHOT]
        at org.apache.doris.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:435) ~[doris-fe.jar:1.2-SNAPSHOT]
        at org.apache.doris.qe.ConnectProcessor.dispatch(ConnectProcessor.java:584) ~[doris-fe.jar:1.2-SNAPSHOT]
        at org.apache.doris.qe.ConnectProcessor.processOnce(ConnectProcessor.java:849) ~[doris-fe.jar:1.2-SNAPSHOT]
        at org.apache.doris.mysql.ReadListener.lambda$handleEvent$0(ReadListener.java:52) ~[doris-fe.jar:1.2-SNAPSHOT]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_392]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_392]
        at java.lang.Thread.run(Thread.java:750) ~[?:1.8.0_392]
2024-07-22 16:04:49,229 INFO (mysql-nio-pool-853|11681) [QeProcessorImpl.unregisterQuery():149] not found query 69e09c3534eb4987-a6a0f7fa893e13f0 when unregisterQuery
2024-07-22 16:04:49,847 INFO (replayer|91) [Env.replayJournal():2548] replayed journal id is 9520877, replay to journal id is 9520878
海中之龙11
1 Answers
zy-kkk516