clickhouse建表create table报错DB::Exception: Primary key must be a prefix of the sorting key, but in position

在Clickhouse的MergeTree引擎家族，排序列必须以主键列开头，否则报错DB::Exception: Primary key must be a prefix of the sorting key, but in position 0 its column is seqid, not collectdate.

报错样例

localhost :) create table t2(seqid int, name varchar(100), collectdate date,value decimal(16,3))engine=MergeTree() primary key seqid order by collectdate partition by toYYYYMMDD(collectdate);

CREATE TABLE t2
(
    `seqid` int,
    `name` varchar(100),
    `collectdate` date,
    `value` decimal(16, 3)
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(collectdate)
PRIMARY KEY seqid
ORDER BY collectdate

Query id: 2aa434f1-531c-449b-a467-6e17a0580295


0 rows in set. Elapsed: 0.017 sec.

Received exception from server (version 21.4.5):
Code: 36. DB::Exception: Received from localhost:9000. DB::Exception: Primary key must be a prefix of the sorting key, but in position 0 its column is seqid, not collectdate.

解决方案

将主键列放到排序列前面

localhost :) create table t2(seqid int, name varchar(100), collectdate date,value decimal(16,3))engine=MergeTree() primary key seqid partition by toYYYYMMDD(collectdate) order by (seqid,collectdate);

CREATE TABLE t2
(
    `seqid` int,
    `name` varchar(100),
    `collectdate` date,
    `value` decimal(16, 3)
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(collectdate)
PRIMARY KEY seqid
ORDER BY (seqid, collectdate)

Query id: 53da06de-5486-4fd3-b28f-33ab2f1420a3

Ok.

0 rows in set. Elapsed: 0.044 sec.

localhost :) show create table t2;

SHOW CREATE TABLE t2

Query id: ed164b0c-1bda-42e9-b86b-7bde9f44a932

┌─statement──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE testdb.t2
(
    `seqid` Int32,
    `name` String,
    `collectdate` Date,
    `value` Decimal(16, 3)
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(collectdate)
PRIMARY KEY seqid
ORDER BY (seqid, collectdate)
SETTINGS index_granularity = 8192 │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.004 sec.

localhost :)