全部产品
Search
文档中心

云数据库 SelectDB 版:数据模型

更新时间:May 13, 2024

当您需要在不同业务场景中使用云数据库 SelectDB 版时,深入了解其数据模型对于您的业务设计具有重要帮助。本文档将指导您了解其数据模型,以帮助您设计出更优的数据存储方案。

基本概念

云数据库 SelectDB 版中,数据以表(Table)的形式进行逻辑上的描述。 一张表包括行(Row)和列(Column),Row即您数据表中的一行数据,Column用于描述一行数据中不同的字段。

Column可以分为两大类:Key和Value。从业务角度看,Key和Value可以分别对应维度列和指标列。在SelectDB建表语句的列中,关键字DUPLICATE KEYAGGREGATE KEYUNIQUE KEY指定的列即是Key列,其他列是Value列。

上述关键字对应SelectDB中的3种数据模型,本文将对这些数据模型进行详细介绍:

  • Aggregate模型

  • Unique模型

  • Duplicate模型

Aggregate模型

对于写入的数据,SelectDB会根据不同数据模型,根据建表所选不同模型的Key列中,数据相同的行(Row)进行不同的处理。对Aggregate模型而言,指定的所有Key列数据相同的行,多行数据会进行合并,Value列按照建表时字段定义中设置的AggregationType进行预聚合,最终只保留一行数据。

这意味着Aggregate模型可以通过预聚合,极大地降低聚合查询时所需扫描的数据量和查询的计算量,非常适合报表类统计分析场景。该模型对count(*)查询不友好,因为固定了Value列上的聚合方式,在进行其他类型的聚合查询时,需要考虑语义正确性。

通过以下示例来阐述什么是聚合模型,以及如何正确地使用Aggregate聚合模型。

示例1:导入数据聚合

数据表example_tbl1的结构定义表。

ColumnName

Type

AggregationType

Comment

user_id

LARGEINT

用户ID

date

DATE

数据写入日期

city

VARCHAR(20)

用户所在城市

age

SMALLINT

用户年龄

sex

TINYINT

用户性别

last_visit_date

DATETIME

REPLACE

用户最后一次访问时间

cost

BIGINT

SUM

用户总消费

max_dwell_time

INT

MAX

用户最大停留时间

min_dwell_time

INT

MIN

用户最小停留时间

创建表example_tbl1(省略建表语句中的Partition和Distribution信息),示例如下。

CREATE TABLE IF NOT EXISTS test.example_tbl1
(
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `date` DATE NOT NULL COMMENT "数据写入日期时间",
    `city` VARCHAR(20) COMMENT "用户所在城市",
    `age` SMALLINT COMMENT "用户年龄",
    `sex` TINYINT COMMENT "用户性别",
    `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
    `cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
    `max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
    `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间"
)
AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 16;

这是一个典型的用户信息和访问行为的事实表。在一般星型模型中,用户信息和访问行为一般分别存放在维度表和事实表中。这里我们为了更加方便的解释SelectDB的数据模型,将两部分信息统一存放在一张表中。

表中的列按照是否设置AggregationType,分为Key(维度列)和Value(指标列)。没有设置AggregationType的,如user_iddateage 等称为Key,而设置了AggregationType的称为Value。当我们导入数据时,对于指定的所有Key列值完全相同时,多行数据会进行合并,而Value列会按照设置的AggregationType进行聚合,最终只保留一行数据。

AggregationType目前有如下聚合方式。

聚合方式参数

参数说明

SUM

求和。适用数值类型。

MIN

求最小值。适合数值类型。

MAX

求最大值。适合数值类型。

REPLACE

替换。对于维度列相同的行,指标列会按照导入的先后顺序,后导入的替换先导入的。

REPLACE_IF_NOT_NULL

非空值替换。和REPLACE的区别在于对于null值,不做替换。这里要注意的是字段默认值要给NULL,而不能是空字符串,如果是空字符串,会给你替换成空字符串。

HLL_UNION

HLL类型的列的聚合方式,通过HyperLogLog算法聚合。

BITMAP_UNION

BITMAP类型的列的聚合方式,进行位图的并集聚合。

example_tbl1表中写入如下数据。

user_id

date

city

age

sex

last_visit_date

cost

max_dwell_time

min_dwell_time

10000

2017-10-01

北京

20

0

2017-10-01 06:00:00

20

10

10

10000

2017-10-01

北京

20

0

2017-10-01 07:00:00

15

2

2

10001

2017-10-01

北京

30

1

2017-10-01 17:05:45

2

22

22

10002

2017-10-02

上海

20

1

2017-10-02 12:59:12

200

5

5

10003

2017-10-02

广州

32

0

2017-10-02 11:20:00

30

11

11

10004

2017-10-01

深圳

35

0

2017-10-01 10:00:15

100

3

3

10004

2017-10-03

深圳

35

0

2017-10-03 10:20:22

11

6

6

通过SQL导入数据,示例如下。

INSERT INTO example_db.example_tbl_agg1 VALUES
(10000,"2017-10-01","北京",20,0,"2017-10-01 06:00:00",20,10,10),
(10000,"2017-10-01","北京",20,0,"2017-10-01 07:00:00",15,2,2),
(10001,"2017-10-01","北京",30,1,"2017-10-01 17:05:45",2,22,22),
(10002,"2017-10-02","上海",20,1,"2017-10-02 12:59:12",200,5,5),
(10003,"2017-10-02","广州",32,0,"2017-10-02 11:20:00",30,11,11),
(10004,"2017-10-01","深圳",35,0,"2017-10-01 10:00:15",100,3,3),
(10004,"2017-10-03","深圳",35,0,"2017-10-03 10:20:22",11,6,6);

数据写入到SelectDB后,SelectDB中最终存储结果如下。

user_id

date

city

age

sex

last_visit_date

cost

max_dwell_time

min_dwell_time

10000

2017-10-01

北京

20

0

2017-10-01 07:00:00

35

10

2

10001

2017-10-01

北京

30

1

2017-10-01 17:05:45

2

22

22

10002

2017-10-02

上海

20

1

2017-10-02 12:59:12

200

5

5

10003

2017-10-02

广州

32

0

2017-10-02 11:20:00

30

11

11

10004

2017-10-01

深圳

35

0

2017-10-01 10:00:15

100

3

3

10004

2017-10-03

深圳

35

0

2017-10-03 10:20:22

11

6

6

通过最终存储结果分析,用户ID为10000的用户只剩下了一行聚合后的数据。而其余用户的数据和原始数据保持一致。用户ID为10000的用户数据聚合后的数据解释如下。

前5列没有变化,从第6列last_visit_date开始。

  • 2017-10-01 07:00:00:因为last_visit_date列的聚合方式为REPLACE,所以2017-10-01 07:00:00替换了2017-10-01 06:00:00保存了下来。

    说明

    在同一个导入批次中的数据,对于REPLACE这种聚合方式,替换顺序不做保证。如在这个例子中,最终保存下来的,也有可能是2017-10-01 06:00:00。而对于不同导入批次中的数据,可以保证,后一批次的数据会替换前一批次。

  • 35:因为cost列的聚合类型为SUM,所以由20+15累加获得35。

  • 10:因为max_dwell_time列的聚合类型为MAX,所以10和2取最大值,获得10。

  • 2:因为min_dwell_time列的聚合类型为MIN,所以10和2取最小值,获得2。

经过聚合,SelectDB中最终只会存储聚合后的数据。即明细数据将丢失,您将无法再查询到聚合前的明细数据。

示例2:导入数据与已有数据聚合

创建表example_tbl2(省略建表语句中的Partition和Distribution信息),示例如下。

CREATE TABLE IF NOT EXISTS test.example_tbl2
(
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `date` DATE NOT NULL COMMENT "数据写入日期时间",
    `city` VARCHAR(20) COMMENT "用户所在城市",
    `age` SMALLINT COMMENT "用户年龄",
    `sex` TINYINT COMMENT "用户性别",
    `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
    `cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
    `max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
    `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间"
)
AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 16;

example_tbl2表中写入如下数据。

user_id

date

city

age

sex

last_visit_date

cost

max_dwell_time

min_dwell_time

10000

2017-10-01

北京

20

0

2017-10-01 07:00:00

35

10

2

10001

2017-10-01

北京

30

1

2017-10-01 17:05:45

2

22

22

10002

2017-10-02

上海

20

1

2017-10-02 12:59:12

200

5

5

10003

2017-10-02

广州

32

0

2017-10-02 11:20:00

30

11

11

10004

2017-10-01

深圳

35

0

2017-10-01 10:00:15

100

3

3

10004

2017-10-03

深圳

35

0

2017-10-03 10:20:22

11

6

6

通过SQL导入数据,示例如下。

INSERT INTO test.example_tbl2 VALUES
(10000,"2017-10-01","北京",20,0,"2017-10-01 06:00:00",20,10,10),
(10000,"2017-10-01","北京",20,0,"2017-10-01 07:00:00",15,2,2),
(10001,"2017-10-01","北京",30,1,"2017-10-01 17:05:45",2,22,22),
(10002,"2017-10-02","上海",20,1,"2017-10-02 12:59:12",200,5,5),
(10003,"2017-10-02","广州",32,0,"2017-10-02 11:20:00",30,11,11),
(10004,"2017-10-01","深圳",35,0,"2017-10-01 10:00:15",100,3,3),
(10004,"2017-10-03","深圳",35,0,"2017-10-03 10:20:22",11,6,6);

再向表example_tbl2中写入如下数据。

user_id

date

city

age

sex

last_visit_date

cost

max_dwell_time

min_dwell_time

10004

2017-10-03

深圳

35

0

2017-10-03 11:22:00

44

19

19

10005

2017-10-03

长沙

29

1

2017-10-03 18:11:02

3

1

1

通过SQL导入数据,示例如下。

INSERT INTO test.example_tbl2 VALUES
(10004,"2017-10-03","深圳",35,0,"2017-10-03 11:22:00",44,19,19),
(10005,"2017-10-03","长沙",29,1,"2017-10-03 18:11:02",3,1,1);

数据写入到SelectDB后,SelectDB中最终存储结果如下。

user_id

date

city

age

sex

last_visit_date

cost

max_dwell_time

min_dwell_time

10000

2017-10-01

北京

20

0

2017-10-01 07:00:00

35

10

2

10001

2017-10-01

北京

30

1

2017-10-01 17:05:45

2

22

22

10002

2017-10-02

上海

20

1

2017-10-02 12:59:12

200

5

5

10003

2017-10-02

广州

32

0

2017-10-02 11:20:00

30

11

11

10004

2017-10-01

深圳

35

0

2017-10-01 10:00:15

100

3

3

10004

2017-10-03

深圳

35

0

2017-10-03 11:22:00

55

19

6

10005

2017-10-03

长沙

29

1

2017-10-03 18:11:02

3

1

1

通过最终存储结果分析,用户ID为10004的用户的已有数据和新导入的数据发生了聚合。同时新增了用户ID为10005用户数据。

数据的聚合,在SelectDB中发生在如下三个阶段。

  1. 每一批次数据导入的ETL阶段。该阶段会在每一批次导入的数据内部进行聚合。

  2. 计算集群进行数据Compaction的阶段。该阶段,计算集群会对已导入的不同批次的数据进行进一步的聚合。

  3. 数据查询阶段。在数据查询时,对于查询涉及到的数据,会进行对应的聚合。

数据在不同时间聚合的程度可能不一致。比如一批数据刚导入时,可能还未与之前已存在的数据进行聚合。但是对于您而言,您只能查询到聚合后的数据。即不同的聚合程度对于您的查询而言是透明的。您只需要始终认为数据以最终完成的聚合程度存在,而不需要假设某些聚合还未发生。

示例3:保留明细数据

数据表example_tbl3的结构定义表如下。

ColumnName

Type

AggregationType

Comment

user_id

LARGEINT

用户ID

date

DATE

数据写入日期

timestamp

DATETIME

数据写入时间,精确到秒

city

VARCHAR(20)

用户所在城市

age

SMALLINT

用户年龄

sex

TINYINT

用户性别

last_visit_date

DATETIME

REPLACE

用户最后一次访问时间

cost

BIGINT

SUM

用户总消费

max_dwell_time

INT

MAX

用户最大停留时间

min_dwell_time

INT

MIN

用户最小停留时间

增加了一列timestamp,记录精确到秒的数据写入时间。 同时,将AGGREGATE KEY设置为AGGREGATE KEY(user_id, date, timestamp, city, age, sex)

创建表example_tbl3(省略建表语句中的Partition和Distribution信息),示例如下。

CREATE TABLE IF NOT EXISTS test.example_tbl3
(
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `date` DATE NOT NULL COMMENT "数据写入日期时间",
    `timestamp` DATETIME NOT NULL COMMENT "数据写入时间,精确到秒",
    `city` VARCHAR(20) COMMENT "用户所在城市",
    `age` SMALLINT COMMENT "用户年龄",
    `sex` TINYINT COMMENT "用户性别",
    `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
    `cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
    `max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
    `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间"
)
AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 16;

向表example_tbl3中写入如下数据。

user_id

date

timestamp

city

age

sex

last_visit_date

cost

max_dwell_time

min_dwell_time

10000

2017-10-01

2017-10-01 08:00:05

北京

20

0

2017-10-01 06:00:00

20

10

10

10000

2017-10-01

2017-10-01 09:00:05

北京

20

0

2017-10-01 07:00:00

15

2

2

10001

2017-10-01

2017-10-01 18:12:10

北京

30

1

2017-10-01 17:05:45

2

22

22

10002

2017-10-02

2017-10-02 13:10:00

上海

20

1

2017-10-02 12:59:12

200

5

5

10003

2017-10-02

2017-10-02 13:15:00

广州

32

0

2017-10-02 11:20:00

30

11

11

10004

2017-10-01

2017-10-01 12:12:48

深圳

35

0

2017-10-01 10:00:15

100

3

3

10004

2017-10-03

2017-10-03 12:38:20

深圳

35

0

2017-10-03 10:20:22

11

6

6

通过SQL导入数据,示例如下。

INSERT INTO test.example_tbl3 VALUES
(10000,"2017-10-01","2017-10-01 08:00:05","北京",20,0,"2017-10-01 06:00:00",20,10,10),
(10000,"2017-10-01","2017-10-01 09:00:05","北京",20,0,"2017-10-01 07:00:00",15,2,2),
(10001,"2017-10-01","2017-10-01 18:12:10","北京",30,1,"2017-10-01 17:05:45",2,22,22),
(10002,"2017-10-02","2017-10-02 13:10:00","上海",20,1,"2017-10-02 12:59:12",200,5,5),
(10003,"2017-10-02","2017-10-02 13:15:00","广州",32,0,"2017-10-02 11:20:00",30,11,11),
(10004,"2017-10-01","2017-10-01 12:12:48","深圳",35,0,"2017-10-01 10:00:15",100,3,3),
(10004,"2017-10-03","2017-10-03 12:38:20","深圳",35,0,"2017-10-03 10:20:22",11,6,6);

数据写入到SelectDB后,SelectDB中最终存储结果如下。

user_id

date

timestamp

city

age

sex

last_visit_date

cost

max_dwell_time

min_dwell_time

10000

2017-10-01

2017-10-01 08:00:05

北京

20

0

2017-10-01 06:00:00

20

10

10

10000

2017-10-01

2017-10-01 09:00:05

北京

20

0

2017-10-01 07:00:00

15

2

2

10001

2017-10-01

2017-10-01 18:12:10

北京

30

1

2017-10-01 17:05:45

2

22

22

10002

2017-10-02

2017-10-02 13:10:00

上海

20

1

2017-10-02 12:59:12

200

5

5

10003

2017-10-02

2017-10-02 13:15:00

广州

32

0

2017-10-02 11:20:00

30

11

11

10004

2017-10-01

2017-10-01 12:12:48

深圳

35

0

2017-10-01 10:00:15

100

3

3

10004

2017-10-03

2017-10-03 12:38:20

深圳

35

0

2017-10-03 10:20:22

11

6

6

存储的数据和导入的数据完全一样,没有发生任何聚合。因为这批数据中加入了timestamp列,导致每一行的Key都不完全相同。只要确保导入的数据中,每一行的Key都不完全相同,即使在聚合模型下,也可以保存完整的明细数据。

Unique模型

在某些多维分析场景下,更关注的是如何保证Key的唯一性,即如何获得主键唯一性约束。因此,云数据库 SelectDB 版引入了Unique表引擎。在早期版本中,该模型本质上是聚合模型的一个特例,也是一种简化的表结构表示方式,由于聚合模型的实现方式是读时合并MOR(Merge on Read),因此在一些聚合查询上性能不佳。在3.0版本SelectDB引入了Unique模型新的实现方式,写时合并MOW(Merge on Write),通过在写入时做一些额外的工作,实现最优的查询性能。

对Unique模型而言,指定的所有Key列数据相同时多行数据会进行覆盖,仅保留最新导入的行,提供类似关系型数据库中的唯一性约束。

Unique模型针对需要唯一性约束的场景,提供了主键唯一性约束,可用于满足订单等关系型数据分析场景。对于聚合查询有较高性能需求的场景,推荐使用新版本引入的写时合并实现,但是该表引擎无法利用 ROLLUP等预聚合带来的查询优势。

说明

在Unique数据模型中,推荐使用写时合并的实现方式

我们将通过以下示例来阐述两种不同的实现方式

写时合并(MOW)

Unqiue表引擎的写时合并实现,与聚合表引擎就是完全不同的两种模型,查询性能更接近于上文的Duplicate模型,在有主键约束需求的场景上相比聚合模型有较大的查询性能优势,尤其是在聚合查询以及需要用索引过滤大量数据的查询中。

写时合并在3.0版本中,作为一个新的特性,默认关闭,您可以通过如下属性property来开启。

"enable_unique_key_merge_on_write" = "true"

数据表example_tbl6的结构定义表。

ColumnName

Type

AggregationType

Comment

user_id

BIGINT

用户ID

username

VARCHAR(50)

用户昵称

city

VARCHAR(20)

NONE

用户所在城市

age

SMALLINT

NONE

用户年龄

sex

TINYINT

NONE

用户性别

phone

LARGEINT

NONE

用户电话

address

VARCHAR(500)

NONE

用户住址

register_time

DATETIME

NONE

用户注册时间

创建表example_tbl6(省略建表语句中的Partition和Distribution信息),修改表property属性。示例如下。

CREATE TABLE IF NOT EXISTS test.example_tbl6
(
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `username` VARCHAR(50) NOT NULL COMMENT "用户昵称",
    `city` VARCHAR(20) COMMENT "用户所在城市",
    `age` SMALLINT COMMENT "用户年龄",
    `sex` TINYINT COMMENT "用户性别",
    `phone` LARGEINT COMMENT "用户电话",
    `address` VARCHAR(500) COMMENT "用户地址",
    `register_time` DATETIME COMMENT "用户注册时间"
)
UNIQUE KEY(`user_id`, `username`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 16
PROPERTIES (
    "enable_unique_key_merge_on_write" = "true"
);

这种建表语句所建立的表结构与聚合表引擎完全不同。

在开启了写时合并选项的Unique表上,数据在导入阶段就会去将被覆盖和被更新的数据进行标记删除,同时将新的数据写入新的文件。在查询的时候,所有被标记删除的数据都会在文件级别被过滤掉,读取出来的数据就都是最新的数据。这消除掉了读时合并中的数据聚合过程,并且能够在很多情况下支持多种谓词的下推。因此在许多场景都能带来比较大的性能提升,尤其是在有聚合查询的情况下。

说明
  • 新的Merge-on-Write实现默认关闭,且只能在建表时通过指定属性property的方式打开。

  • 旧的Merge-on-Read的实现无法无缝升级到新版本的实现(数据组织方式完全不同),如果需要改为写时合并的实现,需要手动执行INSERT INTO unique-mow-table SELECT * FROM source_table

  • 在Unique引擎上独有的delete sign和sequence col,在写时合并的新版实现中仍可以正常使用,用法没有变化。

读时合并(MOR)

数据表example_tbl4的结构定义表。

ColumnName

Type

IsKey

Comment

user_id

BIGINT

Yes

用户ID

username

VARCHAR(50)

Yes

用户昵称

city

VARCHAR(20)

No

用户所在城市

age

SMALLINT

No

用户年龄

sex

TINYINT

No

用户性别

phone

LARGEINT

No

用户电话

address

VARCHAR(500)

No

用户住址

register_time

DATETIME

No

用户注册时间

这是一个典型的用户基础信息表。这类数据没有聚合需求,只需保证主键唯一性。(这里的主键为user_id+username)。

创建表example_tbl4(省略建表语句中的Partition和Distribution信息),示例如下。

CREATE TABLE IF NOT EXISTS test.example_tbl4
(
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `username` VARCHAR(50) NOT NULL COMMENT "用户昵称",
    `city` VARCHAR(20) COMMENT "用户所在城市",
    `age` SMALLINT COMMENT "用户年龄",
    `sex` TINYINT COMMENT "用户性别",
    `phone` LARGEINT COMMENT "用户电话",
    `address` VARCHAR(500) COMMENT "用户地址",
    `register_time` DATETIME COMMENT "用户注册时间"
)
UNIQUE KEY(`user_id`, `username`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 16;

数据表example_tbl5的结构定义表。

ColumnName

Type

AggregationType

Comment

user_id

BIGINT

用户ID

username

VARCHAR(50)

用户昵称

city

VARCHAR(20)

REPLACE

用户所在城市

age

SMALLINT

REPLACE

用户年龄

sex

TINYINT

REPLACE

用户性别

phone

LARGEINT

REPLACE

用户电话

address

VARCHAR(500)

REPLACE

用户住址

register_time

DATETIME

REPLACE

用户注册时间

创建表example_tbl5(省略建表语句中的Partition和Distribution信息),示例如下。

CREATE TABLE IF NOT EXISTS test.example_tbl5
(
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `username` VARCHAR(50) NOT NULL COMMENT "用户昵称",
    `city` VARCHAR(20) REPLACE COMMENT "用户所在城市",
    `age` SMALLINT REPLACE COMMENT "用户年龄",
    `sex` TINYINT REPLACE COMMENT "用户性别",
    `phone` LARGEINT REPLACE COMMENT "用户电话",
    `address` VARCHAR(500) REPLACE COMMENT "用户地址",
    `register_time` DATETIME REPLACE COMMENT "用户注册时间"
)
AGGREGATE KEY(`user_id`, `username`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 16;

上述表example_tbl4的表结构,完全等同于使用聚合表引擎example_tbl5的表结构。

即Unique引擎的读时合并实现完全可以用聚合模型中的REPLACE方式替代。其内部的实现方式和数据存储方式也完全一样。

Duplicate模型

在某些多维分析场景下,数据既没有主键,也没有聚合需求。因此,引入Duplicate数据模型来满足这类需求。

Duplicate模型而言,指定的所有Key列数据相同时多行数据同时存储在系统中,互不影响,没有预聚合、唯一性约束。

Duplicate模型适合任意维度的Ad-hoc查询,可用于满足日志等明细数据分析场景。该表引擎无法利用预聚合带来的性能提升、唯一性约束带来的自动更新便利性。

我们将通过以下示例来阐述Duplicate模型的实现方式

数据表example_tbl7的结构定义表。

ColumnName

Type

SortKey

Comment

timestamp

DATETIME

Yes

日志时间

type

INT

Yes

日志类型

error_code

INT

Yes

错误码

error_msg

VARCHAR(1024)

No

错误详细信息

op_id

BIGINT

No

负责人ID

op_time

DATETIME

No

处理时间

创建表example_tbl7(省略建表语句中的Partition和Distribution信息)。示例如下。

CREATE TABLE IF NOT EXISTS test.example_tbl7
(
    `timestamp` DATETIME NOT NULL COMMENT "日志时间",
    `type` INT NOT NULL COMMENT "日志类型",
    `error_code` INT COMMENT "错误码",
    `error_msg` VARCHAR(1024) COMMENT "错误详细信息",
    `op_id` BIGINT COMMENT "负责人id",
    `op_time` DATETIME COMMENT "处理时间"
)
DUPLICATE KEY(`timestamp`, `type`, `error_code`)
DISTRIBUTED BY HASH(`type`) BUCKETS 16;

这种表模型区别于Aggregate和Unique模型,数据完全按照导入文件中的数据进行存储,不会有任何聚合。即使两行数据完全相同,也都会保留。 而在建表语句中指定的DUPLICATE KEY,只是用来指明底层数据按照那些列进行排序。在DUPLICATE KEY的选择上,建议适当的选择前2-4列即可。

数据模型比较

Aggregate表引擎的局限性

在聚合表引擎中,模型对外展现的是最终聚合后的数据。也就是说,任何还未聚合的数据(比如说两个不同导入批次的数据),必须通过某种方式保证对外展示的一致性。通过以下示例来进行说明。

example_tbl8结构如下。

ColumnName

Type

AggregationType

Comment

user_id

LARGEINT

用户ID

date

DATE

数据写入日期

cost

BIGINT

SUM

用户总消费

向表example_tbl8中分两个批次写入如下数据。

  • 第一次批量导入的数据如下。

user_id

date

cost

10001

2017-11-20

50

10002

2017-11-21

39

  • 第二次批量导入的数据如下。

user_id

date

cost

10001

2017-11-20

1

10001

2017-11-21

5

10003

2017-11-22

22

可以看到,用户ID为10001的数据在两个批次中导入,在SelectDB内部的合并任务未执行完毕前,底层存储会包含上述5条原始数据。但是为了保证用户只能查询到如下最终聚合后的数据,我们会在查询引擎中自动加入了聚合算子,来保证数据对外的一致性。用户查询结果如下。

user_id

date

cost

10001

2017-11-20

51

10001

2017-11-21

5

10002

2017-11-21

39

10003

2017-11-22

22

例如,执行如下SQL,得到的结果是5,而不是1。

SELECT MIN(cost) FROM example_tbl8;

同时,这种一致性保证,在某些查询中,会极大的降低查询效率。我们以最基本的count(*)查询为例。

SELECT COUNT(*) FROM example_tbl8;

在其他数据库中,这类查询都会很快的返回结果。因为,可以通过如“导入时对行进行计数,保存count的统计信息”,或者在查询时“仅扫描某一列数据,获得count值”的方式,只需很小的开销,即可获得查询结果。但是在聚合表引擎中,这种查询的开销非常大。

以上述的数据为例。

执行SQLselect count(*) from example_tbl8;的结果应该为4。但如果只扫描user_id这一列,且加上查询时聚合,最终得到的结果是3(10001, 10002, 10003)。而不加查询时聚合,则得到的结果是5(两批次一共5行数据)。可见这两个结果都是不对的。

因为当聚合列非常多时,count()查询需要扫描大量的数据。为了得到正确的结果,我们必须同时读取user_iddate这两列的数据,再加上查询时聚合,才能返回4这个正确的结果。即在count()查询中,必须扫描所有的AGGREGATE KEY列(这里就是user_iddate),并且聚合后,才能得到正确的结果

因此,当业务上有频繁的count(*)查询时,我们建议通过增加一个值恒为1、聚合类型为SUM的列来模拟count(*)。例如在example_tbl8的表结构中添加列count,修改后结构定义表如下。

ColumnName

Type

AggregateType

Comment

user_id

BIGINT

用户ID

date

DATE

数据写入日期

cost

BIGINT

SUM

用户总消费

count

BIGINT

SUM

用于计算count

增加一个count列,并且导入数据中,该列值恒为1。则select count(*) from table;的结果等价于select sum(count) from table;。而后者的查询效率将远高于前者。不过这种方式也有使用限制,就是需要您自行保证,不会重复导入AGGREGATE KEY列都相同的行。否则,select sum(count) from table;只能表述原始导入的行数,而不是select count(*) from table;的语义。

另一种方式,就是将如上的count列的聚合类型改为REPLACE,且依然值恒为1。那么select sum(count) from table;select count(*) from table;的结果将是一致的。并且这种方式,没有导入重复行的限制。

Unique表引擎的写时合并实现

Unique引擎的写时合并实现没有聚合引擎的局限性,还是以上方的数据为例,写时合并为每次导入的rowset增加了对应的delete bitmap,来标记哪些数据被覆盖。第一批数据导入后状态如下。

  • 第一次批量导入的结果数据如下。

user_id

date

cost

delete bit

10001

2017-11-20

50

false

10002

2017-11-21

39

false

当第二批数据导入完成后,第一批次导入的数据中重复的行就会被标记为已删除,此时两批数据状态如下。

  • 第一批次导入的数据被标记后结果如下。

user_id

date

cost

delete bit

10001

2017-11-20

50

true

10002

2017-11-21

39

false

  • 第二批次导入的数据结果如下。

user_id

date

cost

delete bit

10001

2017-11-20

1

false

10001

2017-11-21

5

false

10003

2017-11-22

22

false

在查询时,所有在delete bitmap中被标记删除的数据都不会读出来,因此也无需进行做任何数据聚合,上述数据中有效的行数为4行,查询出的结果也应该是4行,也就可以采取开销最小的方式来获取结果,即前面提到的“仅扫描某一列数据,获得count值”的方式。

在测试环境中,count(*)查询在Unique引擎的写时合并实现上的性能,相比聚合模型有10倍以上的提升。

Duplicate表引擎的局限性

Duplicate 模型没有聚合引擎的这个局限性。因为该引擎不涉及聚合语意,在做count(*)查询时,任意选择一列查询,即可得到语意正确的结果。

Key列

Duplicate、Aggregate、Unique模型在使用过程中,都会在建表时指定Key列,然而实际上有所区别。

对于Duplicate模型,表的Key列,可以认为只是 “排序列”,并非起到唯一标识的作用。

对于Aggregate、Unique模型,在这种聚合类型的表里,Key列兼顾 “排序列” 和 “唯一标识列”,是真正意义上的“Key列”。

数据模型的选择建议

因为数据模型在建表时就已经确定,且无法修改。所以,选择一个合适的数据模型非常重要。

  1. Aggregate模型可以通过预聚合,极大地降低聚合查询时所需扫描的数据量和查询的计算量,从而大幅提升查询性能。适合有固定查询模式或需要聚合分析的报表类场景。该模型对count(*)查询不友好;同时因为固定了Value列的聚合方式,在进行其他类型的聚合查询时,需要考虑语义正确性。

  2. Unique模型针对需要唯一性约束的场景,可以保证主键唯一性。适合订单、交易等关系型数据的分析场景。该模型无法利用ROLLUP等预聚合带来的查询优势。当您对于聚合查询有较高性能需求,推荐使用自1.2版本加入的写时合并实现。

  3. Duplicate模型虽然同样无法利用预聚合的特性,但是不受聚合模型的约束,可以充分发挥列存的优势(只读取相关列,而不需要读取所有Key列)。适合针对日志数据、明细数据等进行任意维度的Ad-hoc查询。

  4. 如果有部分列更新的需求,请查阅文档部分列更新获取相关使用建议。