Doris数据模型

桃扇骨 2022-09-09 06:49 555阅读 0赞

列可以分为两大类：Key 和 Value。从业务角度看，Key 和 Value 可以分别对应维度列和指标列。

Apache Doris主要有3种数据模型：

*  明细模型：`Duplicate`（重复，复制）模型，表中的Key值（类似关系模型中的主键）可以重复，和插入数据行一一对应。
 *  聚合模型：`Aggregate`（聚合，合计）模型，表中key值不重复，对于插入的数据数据按照key值对value值进行聚合函数合并。
 *  更新模型：`UNIQUE` 模型，聚合类型的特殊情况，key满足唯一性，最新插入的数据替换掉对应key的数据行。

### 1、明细模型（Duplicate） ###

#### 1.1 说明 ####

*  明细模型是 DORIS 默认使用的数据模型
 *  该数据模型不会对导入的数据进行任何处理，保留导入的原始数据
 *  明细模型中, 可以指定部分的维度列为排序键; 而聚合模型和更新模型中, 排序键只能是全体维度列
 *  事实表中一类`事务事实表`，用于存储随业务不断产生的数据，一旦产生不再变化。例如`交易流水、操作日志、出库入口记录`等。这类需求，推荐使用明细模型。

#### 1.2 样例测试 ####

（1）表结构

<table> 
 <thead> 
  <tr> 
   <th>ColumnName</th> 
   <th>Type</th> 
   <th>SortKey</th> 
   <th>Comment</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>visitor_id</td> 
   <td>INT</td> 
   <td>Y</td> 
   <td>访问者ID</td> 
  </tr> 
  <tr> 
   <td>session_id</td> 
   <td>INT</td> 
   <td>Y</td> 
   <td>会话ID</td> 
  </tr> 
  <tr> 
   <td>client_ip</td> 
   <td>CHAR(32)</td> 
   <td></td> 
   <td>客户端IP</td> 
  </tr> 
  <tr> 
   <td>city</td> 
   <td>CHAR(32)</td> 
   <td></td> 
   <td>城市</td> 
  </tr> 
 </tbody> 
</table>

（2）建表语句

CREATE TABLE IF NOT EXISTS test.session_log
    (
    	visitor_id INT NOT NULL COMMENT "访问者ID",
        session_id INT NOT NULL COMMENT "SessionID",
    	client_ip CHAR(32) NOT NULL COMMENT "客户端IP",
        city CHAR(32) NOT NULL COMMENT "城市"
    )
    DUPLICATE  KEY(visitor_id,session_id)
    DISTRIBUTED BY HASH(visitor_id) BUCKETS 8
    PROPERTIES ("replication_num"="1");

说明：建表语句中指定的 DUPLICATE KEY，只是用来指明底层数据按照那些列进行排序。

mysql> CREATE TABLE IF NOT EXISTS test.session_log
        -> (
        ->     visitor_id INT NOT NULL COMMENT "访问者ID",
        ->     session_id INT NOT NULL COMMENT "SessionID",
        ->     client_ip CHAR(32) NOT NULL COMMENT "客户端IP",
        ->     city CHAR(32) NOT NULL COMMENT "城市"
        -> )
        -> DUPLICATE  KEY(visitor_id,session_id)
        -> DISTRIBUTED BY HASH(visitor_id) BUCKETS 8
        -> PROPERTIES ("replication_num"="1");
    Query OK, 0 rows affected (0.01 sec)

（3）插入测试数据

mysql> insert into test.session_log values(1,1,'192.163.9.101','shanghai');
    Query OK, 1 row affected (0.05 sec)
    {'label':'insert_c36ae31ecb8549ef-923daa82fda8dc78', 'status':'VISIBLE', 'txnId':'235'}
    
    mysql> insert into test.session_log values(1,1,'192.163.9.101','shanghai');
    Query OK, 1 row affected (0.06 sec)
    {'label':'insert_fcad117e62e84ee7-908fc1e118b13368', 'status':'VISIBLE', 'txnId':'236'}
    
    mysql> select * from test.session_log;
    +------------+------------+---------------+----------+
    | visitor_id | session_id | client_ip     | city     |
    +------------+------------+---------------+----------+
    |          1 |          1 | 192.163.9.101 | shanghai |
    |          1 |          1 | 192.163.9.101 | shanghai |
    +------------+------------+---------------+----------+
    2 rows in set (0.03 sec)
    
    mysql> insert into test.session_log values(1,2,'192.163.100.10','beijing');
    Query OK, 1 row affected (0.04 sec)
    {'label':'insert_fde1a90815f64a1d-a3e48a02ebce7bc6', 'status':'VISIBLE', 'txnId':'237'}
    
    mysql> select * from test.session_log;
    +------------+------------+----------------+----------+
    | visitor_id | session_id | client_ip      | city     |
    +------------+------------+----------------+----------+
    |          1 |          1 | 192.163.9.101  | shanghai |
    |          1 |          1 | 192.163.9.101  | shanghai |
    |          1 |          2 | 192.163.100.10 | beijing  |
    +------------+------------+----------------+----------+
    3 rows in set (0.01 sec)
    
    mysql>

### 2、聚合模型（Aggregate） ###

#### 2.1 说明 ####

*  聚合模型需要用户在建表时显式的将列分为 Key 列和 Value 列。
 *  该模型会自动的对Aggregate Key 相同的行，在 Value 列上进行聚合操作。
 *  目前支持SUM/MIN/MAX/REPLACE聚合操作。
    
     *  SUM：求和，多行的 Value 进行累加。
     *  REPLACE：替代，下一批数据中的 Value 会替换之前导入过的行中的 Value。
     *  MAX：保留最大值。
     *  MIN：保留最小值。
 *  Aggregate模型可以提前聚合数据，适合报表和多维业务。

#### 2.2 样例测试 ####

（1）表结构

<table> 
 <thead> 
  <tr> 
   <th>ColumnName</th> 
   <th>Type</th> 
   <th>AggregationType</th> 
   <th>Comment</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>user_id</td> 
   <td>INT</td> 
   <td></td> 
   <td>用户id</td> 
  </tr> 
  <tr> 
   <td>user_name</td> 
   <td>VARCHAR(32)</td> 
   <td></td> 
   <td>用户名</td> 
  </tr> 
  <tr> 
   <td>last_visit</td> 
   <td>DATETIME</td> 
   <td>REPLACE</td> 
   <td>用户最后一次访问时间</td> 
  </tr> 
  <tr> 
   <td>max_dwell_time</td> 
   <td>INT</td> 
   <td>MAX</td> 
   <td>用户最大停留时间</td> 
  </tr> 
  <tr> 
   <td>min_dwell_time</td> 
   <td>INT</td> 
   <td>MIN</td> 
   <td>用户最小停留时间</td> 
  </tr> 
  <tr> 
   <td>pv</td> 
   <td>BIGINT</td> 
   <td>SUM</td> 
   <td>点击量</td> 
  </tr> 
  <tr> 
   <td>dt</td> 
   <td>DATE</td> 
   <td></td> 
   <td>数据灌入日期时间</td> 
  </tr> 
 </tbody> 
</table>

（2）建表语句

CREATE TABLE IF NOT EXISTS test.user_visit
    (
        dt DATE NOT NULL COMMENT "数据灌入日期时间"
    	user_id INT NOT NULL COMMENT "用户id",
        last_visit DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "最近访问时间",
        max_dwell_time INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
        min_dwell_time INT MIN DEFAULT "99999" COMMENT "用户最小停留时间",
    	pv BIGINT SUM DEFAULT "0" COMMENT "点击量"
    )
    AGGREGATE KEY(dt,user_id)
    DISTRIBUTED BY HASH(user_id) BUCKETS 8
    PROPERTIES ("replication_num"="1");

mysql> CREATE TABLE IF NOT EXISTS test.user_visit
        -> (
        ->     dt DATE NOT NULL COMMENT "数据灌入日期时间",
        ->     user_id INT NOT NULL COMMENT "用户id",
        ->     last_visit DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "最近访问时间",
        ->     max_dwell_time INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
        ->     min_dwell_time INT MIN DEFAULT "99999" COMMENT "用户最小停留时间",
        ->     pv BIGINT SUM DEFAULT "0" COMMENT "点击量"
        -> )
        -> AGGREGATE KEY(dt,user_id)
        -> DISTRIBUTED BY HASH(user_id) BUCKETS 8
        -> PROPERTIES ("replication_num"="1");                                                                                                                     Query OK, 0 rows affected (0.03 sec)
    
    mysql>

（3）插入数据

mysql> insert into test.user_visit values("2021-08-31",1,"2021-08-30 10:20:22",2,1,1);
    Query OK, 1 row affected (0.11 sec)
    {'label':'insert_d275799ba2b842c5-aa89f42c97d28e12', 'status':'VISIBLE', 'txnId':'152'}
    
    mysql> select * from test.user_visit;
    +------------+---------+---------------------+----------------+----------------+------+
    | dt         | user_id | last_visit          | max_dwell_time | min_dwell_time | pv   |
    +------------+---------+---------------------+----------------+----------------+------+
    | 2021-08-31 |       1 | 2021-08-30 10:20:22 |              2 |              1 |    1 |
    +------------+---------+---------------------+----------------+----------------+------+
    1 row in set (0.02 sec)
    
    mysql>

mysql> insert into test.user_visit values("2021-08-31",1,"2021-08-30 11:30:22",10,0,2);
    Query OK, 1 row affected (0.05 sec)
    {'label':'insert_546f947cc80b4832-849e85afc78be4fc', 'status':'VISIBLE', 'txnId':'154'}
    
    mysql> select * from test.user_visit;
    +------------+---------+---------------------+----------------+----------------+------+
    | dt         | user_id | last_visit          | max_dwell_time | min_dwell_time | pv   |
    +------------+---------+---------------------+----------------+----------------+------+
    | 2021-08-31 |       1 | 2021-08-30 11:30:22 |             10 |              0 |    3 |
    +------------+---------+---------------------+----------------+----------------+------+
    1 row in set (0.03 sec)
    
    mysql>

### 3、更新模型（UNIQUE） ###

#### 3.1 说明 ####

*  数据仓库中有一类`累计快照事实表`，覆盖一个完整的事务或产品的生命周期（无固定周期），通常有多个日期字段，记录生命周期的关键时间点，比如`订单记录快照事实表`有付款日期，发货日期和收货日期时间点。
 *  针对这种数据更新更新场景，传统处理方式是业务结束时间进行分区，未结束的业务结束日期统一定义为`9999-12-31`。Doris采用更新模型来满足这种需求。
 *  排序键满足唯一性约束, 成为主键

#### 3.2 样例测试 ####

（1）表结构

<table> 
 <thead> 
  <tr> 
   <th>ColumnName</th> 
   <th>Type</th> 
   <th>IsKey</th> 
   <th>Comment</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>order_id</td> 
   <td>BIGINT</td> 
   <td>Y</td> 
   <td>订单id</td> 
  </tr> 
  <tr> 
   <td>order_status</td> 
   <td>TINYINT</td> 
   <td></td> 
   <td>订单状态</td> 
  </tr> 
  <tr> 
   <td>user_id</td> 
   <td>varchar(32)</td> 
   <td></td> 
   <td>用户ID</td> 
  </tr> 
  <tr> 
   <td>order_amount</td> 
   <td>DOUBLE</td> 
   <td></td> 
   <td>订单金额</td> 
  </tr> 
 </tbody> 
</table>

（2）建表语句

CREATE TABLE IF NOT EXISTS test.user_order
    (
    	order_id BIGINT NOT NULL COMMENT "订单ID",
        order_status TINYINT COMMENT "订单状态",
    	user_id varchar(32) NOT NULL COMMENT "用户ID",
        order_amount DOUBLE COMMENT "订单金额"
    )
    UNIQUE KEY(order_id)
    DISTRIBUTED BY HASH(order_id) BUCKETS 8
    PROPERTIES ("replication_num"="1");

（3）插入数据

mysql> CREATE TABLE IF NOT EXISTS test.user_order                                                                                                                  -> (
        ->     order_id BIGINT NOT NULL COMMENT "订单ID",
        ->     order_status TINYINT COMMENT "订单状态",
        ->     user_id varchar(32) NOT NULL COMMENT "用户ID",
        ->     order_amount DOUBLE COMMENT "订单金额"
        -> )
        -> UNIQUE KEY(order_id)
        -> DISTRIBUTED BY HASH(order_id) BUCKETS 8
        -> PROPERTIES ("replication_num"="1");
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> insert into  test.user_order values(1001,1,'hadron',98.5);
    Query OK, 1 row affected (0.06 sec)
    {'label':'insert_5fabf4aca7074801-b5156a1523160ec9', 'status':'VISIBLE', 'txnId':'238'}
    
    mysql> insert into  test.user_order values(1001,2,'hadron',98.5);
    Query OK, 1 row affected (0.04 sec)
    {'label':'insert_58b051467da94422-b49c606b6f54f1f3', 'status':'VISIBLE', 'txnId':'239'}
    
    mysql> select * from test.user_order;
    +----------+--------------+---------+--------------+
    | order_id | order_status | user_id | order_amount |
    +----------+--------------+---------+--------------+
    |     1001 |            2 | hadron  |         98.5 |
    +----------+--------------+---------+--------------+
    1 row in set (0.03 sec)
    
    mysql>