doris建表报错
问题背景
当我从Mpp库向doris库中导数据时,需要先创建对应的数据表,将Mpp库中表的建表语句略作修改后,在doris服务器上运行
CREATE TABLE opt_connect_box_v8 (
CNT_BOX_ID char(72) NOT NULL,
CNT_BOX_NO varchar(720) NOT NULL,
CNT_BOX_NAME varchar(720) DEFAULT NULL,
ALIAS varchar(140) DEFAULT NULL,
REGION_ID char(72) DEFAULT NULL,
STREET_ID char(72) DEFAULT NULL,
DOOR_NO varchar(120) DEFAULT NULL,
LOCATION varchar(900) DEFAULT NULL,
FIX_TYPE_ID bigint(20) DEFAULT NULL,
LINE_TYPE bigint(20) DEFAULT NULL,
LINE_ID char(72) DEFAULT NULL,
STAYPOINT_ID char(72) DEFAULT NULL,
MODEL varchar(60) DEFAULT NULL,
CAPACITY int(11) DEFAULT NULL,
INTEGRATIVE_ID char(3) DEFAULT NULL,
ADAPTER_ID bigint(20) DEFAULT NULL,
MELT_LOCATION varchar(90) DEFAULT NULL,
MNT_STATE_ID bigint(20) DEFAULT NULL,
OPR_STATE_ID bigint(20) DEFAULT NULL,
MNT_TYPE bigint(20) DEFAULT NULL,
DUTY_MAN varchar(120) DEFAULT NULL,
X decimalv3(20,10) DEFAULT NULL,
Y decimalv3(20,10) DEFAULT NULL,
Z decimalv3(20,10) DEFAULT NULL,
HEIGHT decimalv3(10,2) DEFAULT NULL,
NOTES varchar(800) DEFAULT NULL,
DELETE_STATE char(3) DEFAULT NULL,
DELETE_TIME datetime DEFAULT NULL,
STATION_ID char(72) DEFAULT NULL,
MODIFY_OP bigint(20) DEFAULT NULL,
MODIRY_DATE datetime DEFAULT NULL,
FACTORY varchar(240) DEFAULT NULL,
ASSET_CODE varchar(60) DEFAULT NULL,
MAPX decimalv3(20,10) DEFAULT NULL,
MAPY decimalv3(20,10) DEFAULT NULL,
FACT_CAPACITY bigint(20) DEFAULT NULL,
EMPTY_CAPACITY bigint(20) DEFAULT NULL,
OLD_ID_EQP varchar(800) DEFAULT NULL,
OLD_SP varchar(24) DEFAULT NULL,
PROP_CHAR_ID bigint(20) DEFAULT NULL,
DATACOLLECT_UNIT varchar(300) DEFAULT NULL,
DATA_COLLECTOR varchar(300) DEFAULT NULL,
DATADATACOLLECT_TIME datetime DEFAULT NULL,
OLD_PROJECT_NO varchar(300) DEFAULT NULL,
OLD_PROJECT_NAME varchar(300) DEFAULT NULL,
RESOURCE_FROM bigint(20) DEFAULT NULL,
EQP_MODEL_ID bigint(20) DEFAULT NULL,
EQP_TYPE_ID bigint(20) DEFAULT NULL,
SHELFDIRECTION bigint(20) DEFAULT NULL,
ZD_DZPY varchar(30) DEFAULT NULL,
ZD_ZCGMC varchar(90) DEFAULT NULL,
ZD_LS bigint(20) DEFAULT NULL,
ZD_LMKS bigint(20) DEFAULT NULL,
ZD_KLS bigint(20) DEFAULT NULL,
ZD_KHS bigint(20) DEFAULT NULL,
ZD_LPL varchar(30) DEFAULT NULL,
ZD_MPL varchar(30) DEFAULT NULL,
ZD_DZLPL varchar(30) DEFAULT NULL,
ZD_DZHPL varchar(30) DEFAULT NULL,
ZD_DZPF varchar(30) DEFAULT NULL,
ZD_DZQS bigint(20) DEFAULT NULL,
ZD_QYSJ datetime DEFAULT NULL,
ZD_SYSM bigint(20) DEFAULT NULL,
ZD_CQ varchar(90) DEFAULT NULL,
ZD_CJDW varchar(90) DEFAULT NULL,
ZD_CJR varchar(60) DEFAULT NULL,
ZD_CJRQ datetime DEFAULT NULL,
ZD_JGRQ datetime DEFAULT NULL,
ZD_GCBH varchar(210) DEFAULT NULL,
ZD_YGCBH varchar(100) DEFAULT NULL,
ZD_GCMC varchar(200) DEFAULT NULL,
ZD_YGCMC varchar(200) DEFAULT NULL,
ZD_SFCL bigint(20) DEFAULT NULL,
CREATE_DATE datetime DEFAULT NULL,
LAN_ID varchar(30) DEFAULT NULL,
ZD_STREET varchar(180) DEFAULT NULL,
CREATE_OP bigint(20) DEFAULT NULL,
PRIVATE_CAPITAL_NUMBER varchar(300) DEFAULT NULL,
IS_PRIVATE_CAPITAL char(3) DEFAULT NULL,
IS_GPS char(3) DEFAULT NULL,
IS_FIX_FINISH char(3) DEFAULT NULL,
LONG_LOCAL_ID bigint(20) DEFAULT NULL,
MNT_MAN varchar(800) DEFAULT NULL,
MNT_UNIT varchar(800) DEFAULT NULL,
BUILD_MODEL bigint(20) DEFAULT NULL,
FIX_END_TIME datetime DEFAULT NULL,
MNT_MAN_TEL varchar(800) DEFAULT NULL,
MFR_ID varchar(300) DEFAULT NULL,
USER_ACCESS_TYPE bigint(20) DEFAULT NULL,
CNT_BOX_GRADE bigint(20) DEFAULT NULL,
PROPERTY bigint(20) DEFAULT NULL,
WO_ID bigint(20) DEFAULT NULL,
MATERIAL_NO varchar(240) DEFAULT NULL,
USE_TIME datetime DEFAULT NULL,
WGS84_X decimalv3(20,10) DEFAULT NULL,
WGS84_Y decimalv3(20,10) DEFAULT NULL,
FLOW_STATE_ID bigint(20) DEFAULT NULL,
OLD_SYS_ID varchar(150) DEFAULT NULL,
COLLECT_TIME datetime DEFAULT NULL
) ENGINE=OLAP
DUPLICATE KEY(`CNT_BOX_ID`)
COMMENT '资源光交表'
DISTRIBUTED BY HASH(`CNT_BOX_ID`) BUCKETS 160
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"in_memory" = "false",
"storage_format" = "V2",
"disable_auto_compaction" = "false"
);
问题截图
运行后报错
报错信息
errCode = 2, detailMessage = Scale of decimal must between 0 and 9. Scale was set to: 10
错误代码 “2” 表示小数位数存在问题。错误信息 “小数必须在0到9之间。当前设置为:10” 表明小数的位数设置为了10,超出了有效范围。
参考资料
查看Doris官方1.2版本的decimal的说明文档
查看decimalv3的说明文档,发现可以解决问题
将表中所有的decimal类型改为decimalv3即可