Doris建表报错

replication num should be less than the number of available backends. replication num is 3, available backend num is 1。

这个报错是因为doris只有一个实例，但是收到replication_num 表错误，因为默认值 3 是错误的。

在 starrocks 服务器上执行命令，设置 server 变量。

ADMIN SET FRONTEND CONFIG ('default_replication_num' ="1");

或在 Table Create 上设置它。

CREATE TABLE if not exists doris_ts.TABLE_HASH
(
K1 TINYINT,
K2 DEcimal(10,2) default "10.5",
k3 char(19) comment "fa",
k4 int not null default "10"
)
COMMENT "my first table"
Distributed by hash (k1) buckets 32 properties('replication_num' = '1');

2.columns should be a ordered prefix of the schema. KeyColumns[1] (starts from zero) is k4, but corresponding column

原SQL：

k1 Date,
k2 decimal(7,2) default 12.3,
k3 char (19) comment "大大哇",
k4 int not null default 1 comment "d达瓦达瓦",
)engine=olap
duplicate key (k1,k4)
comment "table"
partition by range (k1)
(
partition p1 values less than ("2021-12-01"),
partition p2 values less than ("2022-12-01"),
partition p3 values less than ("2023-12-01")
)
distributed by hash (k1) buckets 32
properties(
"replication_num"="1"
);

翻译起来就是建表时用于排序duplicate key或者uniue key 的所有字段必须放在前面，并且保证有序。

因此把k4字段挪到前面就不会报错。 新SQL：

k1 Date,
k4 int not null default 1 comment "d达瓦达瓦",
k2 decimal(7,2) default 12.3,
k3 char (19) comment "大大哇",
)engine=olap
duplicate key (k1,k4)
comment "table"
partition by range (k1)
(
partition p1 values less than ("2021-12-01"),
partition p2 values less than ("2022-12-01"),
partition p3 values less than ("2023-12-01")
)
distributed by hash (k1) buckets 32
properties(
"replication_num"="1"
);

3使用REPLACE or REPLACE_IF_NOT_NULL的列不应该随机分布，而应该使用分桶分在一个分区内

原SQL：

(
    k1 DATE,
    k2 INT,
    k3 SMALLINT,
    v1 VARCHAR(2048) REPLACE,
    v2 INT SUM DEFAULT "1"
)engine=olap
aggregate key (k1,k2,k3)
comment "丢你那"
partition by range(k1,k2)
(
partition p1 values [("2024-01-01","10"),("2024-01-01","20","100")),
partition p2 values [("2024-02-01","10"),("2024-02-01","20","100"))
)
-- DISTRIBUTED BY HASH(k2) BUCKETS 32
properties("replication_num"="1");

报错：

Create aggregate keys table with value columns of which aggregate type is REPLACE or REPLACE_IF_NOT_NULL should not contain random distribution desc

解决：

(
    k1 DATE,
    k2 INT,
    k3 SMALLINT,
    v1 VARCHAR(2048) REPLACE,
    v2 INT SUM DEFAULT "1"
)engine=olap
aggregate key (k1,k2,k3)
comment "丢你那"
partition by range(k1,k2)
(
partition p1 values [("2024-01-01","10"),("2024-01-01","20","100")),
partition p2 values [("2024-02-01","10"),("2024-02-01","20","100"))
)
DISTRIBUTED BY HASH(k2) BUCKETS 32
properties("replication_num"="1");