更新时间:2024-01-23 GMT+08:00
分享

数据模型

Doris的数据模型主要分为3类:

Aggregate模型

以实际的例子来说明什么是聚合模型,以及如何正确的使用聚合模型。

  • 示例1:导入数据聚合

    假设业务有以下模式:

    表1 参数说明

    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

    用户最小停留时间

    转换成建表语句,如下所示。

    CREATE TABLE IF NOT EXISTS demo.example_tbl
    (
        `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 1
    PROPERTIES (
        "replication_allocation" = "tag.location.default: 1"
    );

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

    表中的列按照是否设置了AggregationType,分为Key(维度列)和Value(指标列)。没有设置AggregationType的,如user_id、date、age、sex称为Key,而设置了AggregationType的称为Value。

    当导入数据时,对于Key列相同的行会聚合成一行,而Value列会按照设置的AggregationType进行聚合。AggregationType目前有以下四种聚合方式:
    • SUM:求和,多行的Value进行累加。
    • REPLACE:替代,下一批数据中的Value会替换之前导入过的行中的Value。
    • MAX:保留最大值。
    • MIN:保留最小值。
      表2 原始数据

      user_id

      date

      city

      age

      sex

      last_visit_date

      cost

      max_dwell_time

      min_dwell_time

      10000

      2017-10-01

      A

      20

      0

      2017-10-01 06:00:00

      20

      10

      10

      10000

      2017-10-01

      A

      20

      0

      2017-10-01 07:00:00

      15

      2

      2

      10001

      2017-10-01

      A

      30

      1

      2017-10-01 17:05:45

      2

      22

      22

      10002

      2017-10-02

      B

      20

      1

      2017-10-02 12:59:12

      200

      5

      5

      10003

      2017-10-02

      C

      32

      0

      2017-10-02 11:20:00

      30

      11

      11

      10004

      2017-10-01

      D

      35

      0

      2017-10-01 10:00:15

      100

      3

      3

      10004

      2017-10-03

      D

      35

      0

      2017-10-03 10:20:22

      11

      6

      6

    我们假设这是一张记录用户访问某商品页面行为的表。我们以第一行数据为例,解释如下:

    表3 参数说明

    数据

    说明

    10000

    用户id,每个用户唯一识别id

    2017-10-01

    数据入库时间,精确到日期

    A

    用户所在城市

    20

    用户年龄

    0

    性别男(1 代表女性)

    2017-10-01 06:00:00

    用户本次访问该页面的时间,精确到秒

    20

    用户本次访问产生的消费

    10

    用户本次访问,驻留该页面的时间

    10

    用户本次访问,驻留该页面的时间(冗余)

    那么当这批数据正确导入到Doris中后,Doris中最终存储如下:

    表4 插入数据

    user_id

    date

    city

    age

    sex

    last_visit_date

    cost

    max_dwell_time

    min_dwell_time

    10000

    2017-10-01

    A

    20

    0

    2017-10-01 07:00:00

    35

    10

    2

    10001

    2017-10-01

    A

    30

    1

    2017-10-01 17:05:45

    2

    22

    22

    10002

    2017-10-02

    B

    20

    1

    2017-10-02 12:59:12

    200

    5

    5

    10003

    2017-10-02

    C

    32

    0

    2017-10-02 11:20:00

    30

    11

    11

    10004

    2017-10-01

    D

    35

    0

    2017-10-01 10:00:15

    100

    3

    3

    10004

    2017-10-03

    D

    35

    0

    2017-10-03 10:20:22

    11

    6

    6

    可以看到,用户10000只剩下了一行聚合后的数据。而其余用户的数据和原始数据保持一致。这里先解释下用户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。

      经过聚合,Doris中最终只会存储聚合后的数据。换句话说,即明细数据会丢失,用户不能够再查询到聚合前的明细数据了。

  • 示例2:保留,明细数据。

    接示例1,将表结构修改如下:

    表5 参数说明

    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)。

    导入数据如下:

    表6 原始数据

    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

    A

    20

    0

    2017-10-01 06:00:00

    20

    10

    10

    10000

    2017-10-01

    2017-10-01 09:00:05

    A

    20

    0

    2017-10-01 07:00:00

    15

    2

    2

    10001

    2017-10-01

    2017-10-01 18:12:10

    A

    30

    1

    2017-10-01 17:05:45

    2

    22

    22

    10002

    2017-10-02

    2017-10-02 13:10:00

    B

    20

    1

    2017-10-02 12:59:12

    200

    5

    5

    10003

    2017-10-02

    2017-10-02 13:15:00

    C

    32

    0

    2017-10-02 11:20:00

    30

    11

    11

    10004

    2017-10-01

    2017-10-01 12:12:48

    D

    35

    0

    2017-10-01 10:00:15

    100

    3

    3

    10004

    2017-10-03

    2017-10-03 12:38:20

    D

    35

    0

    2017-10-03 10:20:22

    11

    6

    6

    那么当这批数据正确导入到Doris中后,Doris中最终存储如下:

    表7 数据结果

    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

    A

    20

    0

    2017-10-01 06:00:00

    20

    10

    10

    10000

    2017-10-01

    2017-10-01 09:00:05

    A

    20

    0

    2017-10-01 07:00:00

    15

    2

    2

    10001

    2017-10-01

    2017-10-01 18:12:10

    A

    30

    1

    2017-10-01 17:05:45

    2

    22

    22

    10002

    2017-10-02

    2017-10-02 13:10:00

    B

    20

    1

    2017-10-02 12:59:12

    200

    5

    5

    10003

    2017-10-02

    2017-10-02 13:15:00

    C

    32

    0

    2017-10-02 11:20:00

    30

    11

    11

    10004

    2017-10-01

    2017-10-01 12:12:48

    D

    35

    0

    2017-10-01 10:00:15

    100

    3

    3

    10004

    2017-10-03

    2017-10-03 12:38:20

    D

    35

    0

    2017-10-03 10:20:22

    11

    6

    6

  • 示例3:导入数据与已有数据聚合。

    接示例1中的参数列,插入以下表中数据。

    表8 原始数据

    user_id

    date

    city

    age

    sex

    last_visit_date

    cost

    max_dwell_time

    min_dwell_time

    10000

    2017-10-01

    A

    20

    0

    2017-10-01 07:00:00

    35

    10

    2

    10001

    2017-10-01

    A

    30

    1

    2017-10-01 17:05:45

    2

    22

    22

    10002

    2017-10-02

    B

    20

    1

    2017-10-02 12:59:12

    200

    5

    5

    10003

    2017-10-02

    C

    32

    0

    2017-10-02 11:20:00

    30

    11

    11

    10004

    2017-10-01

    D

    35

    0

    2017-10-01 10:00:15

    100

    3

    3

    10004

    2017-10-03

    D

    35

    0

    2017-10-03 10:20:22

    11

    6

    6

    在导入一批新的数据:

    表9 新数据

    user_id

    date

    city

    age

    sex

    last_visit_date

    cost

    max_dwell_time

    min_dwell_time

    10004

    2017-10-03

    D

    35

    0

    2017-10-03 11:22:00

    44

    19

    19

    10005

    2017-10-03

    E

    29

    1

    2017-10-03 18:11:02

    3

    1

    1

    那么当这批数据正确导入到Doris中后,Doris中最终存储如下:

    表10

    user_id

    date

    city

    age

    sex

    last_visit_date

    cost

    max_dwell_time

    min_dwell_time

    10000

    2017-10-01

    A

    20

    0

    2017-10-01 07:00:00

    35

    10

    2

    10001

    2017-10-01

    A

    30

    1

    2017-10-01 17:05:45

    2

    22

    22

    10002

    2017-10-02

    B

    20

    1

    2017-10-02 12:59:12

    200

    5

    5

    10003

    2017-10-02

    C

    32

    0

    2017-10-02 11:20:00

    30

    11

    11

    10004

    2017-10-01

    D

    35

    0

    2017-10-01 10:00:15

    100

    3

    3

    10004

    2017-10-03

    D

    35

    0

    2017-10-03 11:22:00

    55

    19

    6

    10005

    2017-10-03

    E

    29

    1

    2017-10-03 18:11:02

    3

    1

    1

    可以看到,用户10004的已有数据和新导入的数据发生了聚合。同时新增了10005用户的数据。

    数据的聚合,在Doris中有如下三个阶段发生:

    • 每一批次数据导入的ETL阶段。该阶段会在每一批次导入的数据内部进行聚合。
    • 底层BE进行数据Compaction的阶段。该阶段,BE会对已导入的不同批次的数据进行进一步的聚合。
    • 数据查询阶段。在数据查询时,对于查询涉及到的数据,会进行对应的聚合。

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

Unique模型

在某些多维分析场景下,用户更关注的是如何保证Key的唯一性,即如何获得Primary Key唯一性约束。因此,我们引入了Unique的数据模型。该模型本质上是聚合模型的一个特例,也是一种简化的表结构表示方式。举例说明:

Unique模型表,不推荐开启merge-on-write属性,默认使用merge-on-read。

表11 参数说明

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)。那么我们的建表语句如下:

CREATE TABLE IF NOT EXISTS example_db.expamle_tbl
(
    `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 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);

而这个表结构,完全同等于以下使用聚合模型描述的表结构:

表12 参数说明

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

用户注册时间

建表语句。

CREATE TABLE IF NOT EXISTS example_db.expamle_tbl
(
    `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 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);

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

Duplicate模型

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

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

处理时间

建表语句。

CREATE TABLE IF NOT EXISTS example_db.expamle_tbl
(
    `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`)
DISTRIBUTED BY HASH(`type`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);

这种数据模型区别于Aggregate和Unique模型。数据完全按照导入文件中的数据进行存储,不会有任何聚合。即使两行数据完全相同,也都会保留。 而在建表语句中指定的DUPLICATE KEY,只是用来指明底层数据按照那些列进行排序。(更贴切的名称应该为“Sorted Column”,这里取名“DUPLICATE KEY”只是用以明确表示所用的数据模型。在DUPLICATE KEY的选择上,我们建议适当的选择前2-4列就可以。

这种数据模型适用于既没有聚合需求,又没有主键唯一性约束的原始数据的存储。更多使用场景,可参见聚合模型的局限性。

聚合模型的局限

  • Aggregate模型&Unique模型。

    这里我们针对Aggregate模型(包括Unique模型),来介绍下聚合模型的局限性。

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

    假设表结构如下:

    表14 数据

    ColumnName

    Type

    AggregationType

    Comment

    user_id

    LARGEINT

    -

    用户ID

    date

    DATE

    -

    数据导入日期

    cost

    BIGINT

    SUM

    用户总消费

    假设存储引擎中有如下两个已经导入完成的批次的数据。

    batch1

    表15 数据

    user_id

    date

    cost

    10001

    2017-11-20

    50

    10002

    2017-11-21

    39

    batch2

    表16 数据

    user_id

    date

    cost

    10001

    2017-11-20

    1

    10001

    2017-11-21

    5

    10003

    2017-11-22

    22

    可以看到,用户10001分属在两个导入批次中的数据还没有聚合。但是为了保证用户只能查询到如下最终聚合后的数据。

    表17 数据

    user_id

    date

    cost

    10001

    2017-11-20

    51

    10001

    2017-11-21

    5

    10002

    2017-11-21

    39

    10003

    2017-11-22

    22

    建数据表。

    CREATE TABLE IF NOT EXISTS example_db.expamle_tb2
    (
        `user_id` LARGEINT NOT NULL COMMENT "用户id",
        `date` DATE NOT NULL COMMENT "数据导入日期时间",
        `cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费"
    )
    AGGREGATE KEY(`user_id`, `date`)
    DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
    PROPERTIES (
    "replication_allocation" = "tag.location.default: 1"
    );

    插入表15表16的数据。

    INSERT INTO example_db.expamle_tb2 (user_id,date,cost) VALUES('10001','2017-11-20','50'),('10002','2017-11-21','39'),('10001','2017-11-20','1'),('10001','2017-11-21','5'),('10003','2017-11-22','22');

    我们在查询引擎中加入了聚合算子,来保证数据对外的一致性。

    另外,在聚合列(Value)上,执行与聚合类型不一致的聚合类查询时,要注意语意。例如我们在如上示例中执行如下查询:

    mysql> SELECT MIN(cost) FROM example_db.expamle_tb2;
    +-------------+
    | min(`cost`) |
    +-------------+
    |           5 |
    +-------------+
    1 row in set (0.02 sec)

    得到的结果是5,而不是1。

    同时,这种一致性保证,在某些查询中,会极大的降低查询效率。

    我们以最基本的count(*)查询为例:

    SELECT COUNT(*) FROM table;

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

    batch1

    表18 数据

    user_id

    date

    cost

    10001

    2017-11-20

    50

    10002

    2017-11-21

    39

    batch2

    表19 数据

    user_id

    date

    cost

    10001

    2017-11-20

    1

    10001

    2017-11-21

    5

    10003

    2017-11-22

    22

    最终聚合结果如下表所示。

    表20 数据

    user_id

    date

    cost

    10001

    2017-11-20

    51

    10001

    2017-11-21

    5

    10002

    2017-11-21

    39

    10003

    2017-11-22

    22

    查询结果。

    mysql> SELECT COUNT(date) FROM example_db.expamle_tb2;
    +---------------+
    | count(`date`) |
    +---------------+
    |             4 |
    +---------------+
    1 row in set (0.01 sec)

    所以,select count(*) from table; 的正确结果应该为4。但如果我们只扫描user_i 这一列,如果加上查询时聚合,最终得到的结果是3(10001, 10002, 10003)。而如果不加查询时聚合,则得到的结果是5(两批次一共5行数据)。可见这两个结果都是不对的。

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

    因此,当业务上有频繁的count( * )查询时,我们建议用户通过增加一个值恒为1的,聚合类型为SUM的列来模拟count( * )。如刚才的例子中的表结构,我们修改如下:

    表21 参数说明

    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; 的结果将是一致的。并且这种方式,没有导入重复行的限制。

  • Duplicate

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

相关文档