1. 复现错误
今天在编写doris
语句:
CREATE TABLE IF NOT EXISTS test3.test (
`id` int COMMENT 'ID',
`bb` string COMMENT '字段bb',
`aa` date COMMENT '测试aa'
) ENGINE = olap UNIQUE KEY(`id`, `aa`) PARTITION BY RANGE(`aa`) (
FROM
('2023-10-13') TO ('2023-10-14') INTERVAL 1 DAY
) DISTRIBUTED BY HASH(`id`) BUCKETS 10 PROPERTIES("replication_num" = "1");
却报出如下错误:
即Key columns should be a ordered prefix of the schema. KeyColumns[1] (starts from zero) is aa, but corresponding column is bb in the previous columns declaration.
2. 分析错误
将错误Key columns should be a ordered prefix of the schema. KeyColumns[1] (starts from zero) is aa, but corresponding column is bb in the previous columns declaration.
翻译成中文键列应该是架构的有序前缀。KeyColumns[1](从零开始)是 aa,但在前面的列声明中对应的列是 bb
。
描述的很清楚,在id
后面语句应该是
`aa` date COMMENT '测试22'
而不是
`bb` string COMMENT '字段bb'
为什么是这样的呢?因为aa
是分区字段,而且通过UNIQUE KEY
来限制了,表示唯一键。
3. 解决错误
既然aa
是分区字段,又通过UNIQUE KEY
来表示唯一键。
我们便可把aa
字段放在bb
之前,如下代码所示:
CREATE TABLE IF NOT EXISTS test3.test (
`id` int COMMENT 'ID',
`aa` date COMMENT '测试aa',
`bb` string COMMENT '字段bb'
) ENGINE = olap UNIQUE KEY(`id`, `aa`) PARTITION BY RANGE(`aa`) (
FROM
('2023-10-13') TO ('2023-10-14') INTERVAL 1 DAY
) DISTRIBUTED BY HASH(`id`) BUCKETS 10 PROPERTIES("replication_num" = "1");
如此便可运行成功: