Apache Doris 数据更新与删除：Update数据更新；Delete数据删除；Sequence 列；批量删除

6第六章 Doris数据更新与删除

6.1Update数据更新

如果我们需要修改或更新Doris中的数据，如何使用UPDATE命令来操作。数据更新对Doris的版本有限制，只能在Doris Version 0.15.x + 才可以使用。

Update 数据更新只能在 Unique 数据模型的表中执行，使用场景为：对满足某些条件的行进行修改值或小范围数据更新，待更新的行最好是整个表非常小的一部分。

6.1.1Update数据更新原理

Doris利用查询引擎自身的 where 过滤逻辑，从待更新表中筛选出需要被更新的行。再利用 Unique 模型自带的 Value 列新数据替换旧数据的逻辑，将待更新的行变更后，再重新插入到表中，从而实现行级别更新。

Update 语法在Doris中是一个同步语法，即 Update 语句执行成功，更新操作也就完成了，数据是可见的。

Update 语句的性能和待更新的行数以及筛选条件的检索效率密切相关。

• 待更新的行数：待更新的行数越多，Update 语句的速度就会越慢。这和导入的原理是一致的。 Doris 的更新比较合适偶发更新的场景，比如修改个别行的值。 Doris 并不适合大批量的修改数据。大批量修改会使得 Update 语句运行时间很久。
• 筛选条件的检索效率：Doris 的 Update 实现原理是先将满足 where条件的行读取处理，所以如果 where条件的检索效率高，则 Update 的速度也会快。 where条件列最好能命中索引或者分区分桶裁剪，这样 Doris 就不需要扫全表，可以快速定位到需要更新的行，从而提升更新效率。 强烈不推荐 where 条件 列中包含UNIQUE模型的value列。

默认情况下，并不允许同一时间对同一张表并发进行多个 Update 操作。主要原因是，Doris 目前支持的是行更新，这意味着，即使用户声明的是 SET v2 = 1，实际上，其他所有的 Value 列也会被覆盖一遍（尽管值没有变化）。这就会存在一个问题，如果同时有两个 Update 操作对同一行进行更新，那么其行为可能是不确定的，也就是可能存在脏数据。

但在实际应用中，如果用户自己可以保证即使并发更新，也不会同时对同一行进行操作的话，就可以手动打开并发限制。通过修改 FE 配置 enable_concurrent_update，当配置值为 true 时，则对更新并发无限制。

6.1.2Updata数据更新案例

1. 创建Doris表并插入数据

#创建Doris表
CREATE TABLE IF NOT EXISTS example_db.update_tbl
(
`order_id` LARGEINT NOT NULL COMMENT "订单id",
`order_amount` LARGEINT COMMENT "订单金额",
`order_status` VARCHAR(500) COMMENT "订单状态"
)
UNIQUE KEY(`order_id`)
DISTRIBUTED BY HASH(`order_id`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);

#向表中插入数据
mysql> insert into example_db.update_tbl values (1,100,"待付款"),(2,200,"已付款"),(3,300,"待发货"),(4,400,"已发货"),(5,500,"已签收")

#查询表中数据
mysql> select * from update_tbl;
+----------+--------------+--------------+
| order_id | order_amount | order_status |
+----------+--------------+--------------+
| 1        | 100          | 待付款       |
| 2        | 200          | 已付款       |
| 3        | 300          | 待发货       |
| 4        | 400          | 已发货       |
| 5        | 500          | 已签收       |
+----------+--------------+--------------+

1. 对表中数据进行 update 修改

#对表中id=1数据的order_amout修改为1000，order_status修改为待发货
mysql> update update_tbl set order_amount = 1000 ,order_status="待发货" where order_id = 1;

1. 查询表中数据结果

#查询表中数据
mysql> select * from update_tbl;
+----------+--------------+--------------+
| order_id | order_amount | order_status |
+----------+--------------+--------------+
| 1        | 1000         | 待发货       |
| 2        | 200          | 已付款       |
| 3        | 300          | 待发货       |
| 4        | 400          | 已发货       |
| 5        | 500          | 已签收       |
+----------+--------------+--------------+
5 rows in set (0.04 sec)

以上执行Updata 命令后，Doris内部会进行如下三步:

第一步：读取满足 WHERE 订单id=1 的行 （1，100，‘待付款’）

第二步：变更该行的订单状态，从’待付款’改为’待发货’ （1，1000，‘待发货’）

第三步：将更新后的行再插入回表中，从而达到更新的效果。

由于表 update_tbl 是 UNIQUE 模型，所以相同 Key 的行，之后后者才会生效，所以才有最终效果。

6.1.3Updata使用注意点

1. Update 语法在Doris中是一个同步语法，即 Update 语句执行成功，更新操作也就完成了，数据是可见的。
2. Update 语句的性能和待更新的行数以及 condition 的检索效率密切相关。 强烈不推荐 condition 列中包含 UNIQUE 模型的 value 列。
3. 默认情况下，并不允许同一时间对同一张表并发进行多个 Update 操作。
4. 由于 Doris 目前支持的是行更新，并且采用的是读取后再写入的两步操作，则如果 Update 语句和其他导入或 Delete 语句刚好修改的是同一行时，存在不确定的数据结果。

6.2Delete数据删除

Doris 支持通过两种方式对已导入的数据进行删除。一种是通过 DELETE FROM 语句，指定 WHERE 条件对数据进行删除。这种方式比较通用， 适合频率较低的定时删除任务。

另一种删除方式仅针对 Unique 主键唯一模型， 通过导入数据的方式将需要删除的主键行数据进行导入。 Doris 内部会通过删除标记位对数据进行最终的物理删除。 这种删除方式适合以实时的方式对数据进行删除。

Delete是一个同步过程，与Insert into相似，所有的Delete操作在Doris中是一个独立的导入作业，一般Delete语句需要指定表和分区以及删除的条件来筛选要删除的数据，并将会同时删除base表和rollup表的数据。下面介绍delete 删除数据方式。

6.2.1Delete 语法

Delete 删除数据的语法如下:

DELETE FROM table_name [table_alias] [PARTITION partition_name | PARTITIONS (partition_name [, partition_name])]
WHERE
column_name op { value | value_list } [ AND column_name op { value | value_list } ...];

以上语法参数解释如下：

• table_name: 指定需要删除数据的表
• column_name: 属于table_name的列
• op: 逻辑比较操作符，可选类型包括：=, >, <, >=, <=, !=, in, not in
• value | value_list: 做逻辑比较的值或值列表

使用删除语句时有以下注意点：

1. 不同于 Insert into 命令，delete 不能手动指定label
2. 使用聚合类的表模型（AGGREGATE、UNIQUE）只能指定 key 列上的条件。
3. 当选定的 key 列不存在于某个 rollup 中时，无法进行 delete。
4. 条件之间只能是"与"的关系。若希望达成"或"的关系，需要将条件分写在两个 DELETE 语句中。
5. 如果为分区表，需要指定分区，如果不指定，doris 会从条件中推断出分区。以下两种情况下，doris 无法从条件中推断出分区: 1. 条件中不包含分区列；2. 分区列的操作为 not in；

当分区表未指定分区，或者无法从条件中推断分区的时候，需要设置会话变量 delete_without_partition 为 true，此时 delete 会应用到所有分区。

1. 该语句可能会降低执行后一段时间内的查询效率。影响程度取决于语句中指定的删除条件的数量。指定的条件越多，影响越大。

6.2.2Delete删除返回结果

Delete命令是一个SQL命令，返回结果是同步的，分为以下几种：

1. 执行成功

如果Delete顺利执行完成并可见，将返回下列结果，Query OK表示成功

mysql> delete from test_tbl PARTITION p1 where k1 = 1;
Query OK, 0 rows affected (0.04 sec)
{'label':'delete_e7830c72-eb14-4cb9-bbb6-eebd4511d251', 'status':'VISIBLE', 'txnId':'4005'}

1. 提交成功，但未可见

Doris的事务提交分为两步：提交和发布版本，只有完成了发布版本步骤，结果才对用户是可见的。若已经提交成功了，那么就可以认为最终一定会发布成功，Doris会尝试在提交完后等待发布一段时间，如果超时后即使发布版本还未完成也会优先返回给用户，提示用户提交已经完成。若如果Delete已经提交并执行，但是仍未发布版本和可见，将返回下列结果：

mysql> delete from test_tbl PARTITION p1 where k1 = 1;
Query OK, 0 rows affected (0.04 sec)
{'label':'delete_e7830c72-eb14-4cb9-bbb6-eebd4511d251', 'status':'COMMITTED', 'txnId':'4005', 'err':'delete job is committed but may be taking effect later' }

结果会同时返回一个json字符串：

• affected rows：表示此次删除影响的行，由于Doris的删除目前是逻辑删除，因此对于这个值是恒为0；
• label：自动生成的 label，是该导入作业的标识。每个导入作业，都有一个在单 database 内部唯一的 Label；
• status：表示数据删除是否可见，如果可见则显示VISIBLE，如果不可见则显示COMMITTED；
• txnId：这个Delete job对应的事务id；
• err：字段会显示一些本次删除的详细信息。

1. 提交失败，事务取消

如果Delete语句没有提交成功，将会被Doris自动中止，返回下列结果：

mysql> delete from test_tbl partition p1 where k1 > 80;
ERROR 1064 (HY000): errCode = 2, detailMessage = {错误原因}

综上，对于Delete操作返回结果的正确处理逻辑为：

• 如果返回结果为ERROR 1064 (HY000)，则表示删除失败；
• 如果返回结果为Query OK，则表示删除执行成功； - 如果status为COMMITTED，表示数据仍不可见，用户可以稍等一段时间再用show delete命令查看结果；- 如果status为VISIBLE，表示数据删除成功。

6.2.3Delete删除案例

1. 创建Doris表，并插入数据

CREATE TABLE IF NOT EXISTS example_db.delete_tbl
(
`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 "用户最小停留时间"
)
ENGINE=OLAP
AGGREGATE KEY(`user_id`, `date`, `timestamp`, `city`, `age`, `sex`)
PARTITION BY RANGE(`date`)
(
PARTITION `p1` VALUES [("2017-10-01"),("2017-10-02")),
PARTITION `p2` VALUES [("2017-10-02"),("2017-10-03")),
PARTITION `p3` VALUES [("2017-10-03"),("2017-10-04"))
)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);

insert into example_db.delete_tbl 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);

1. 删除表中数据

#对于分区表删除数据要么需要指定分区，要么设置delete_without_partition为true
mysql> set delete_without_partition=true;

#删除数据
mysql> delete from delete_tbl where user_id =10004;

#查看结果数据
mysql> select * from delete_tbl;
+---------+------------+---------------------+--------+-
| user_id | date       | timestamp           | city   | 
+---------+------------+---------------------+--------+-
| 10000   | 2017-10-01 | 2017-10-01 08:00:05 | 北京   | 
| 10000   | 2017-10-01 | 2017-10-01 09:00:05 | 北京   | 
| 10001   | 2017-10-01 | 2017-10-01 18:12:10 | 北京   | 
| 10002   | 2017-10-02 | 2017-10-02 13:10:00 | 上海   | 
| 10003   | 2017-10-02 | 2017-10-02 13:15:00 | 广州   | 
+---------+------------+---------------------+--------+-

1. 查看已完成的删除记录

用户可以通过show delete语句查看历史上已执行完成的删除记录。语法如下：

SHOW DELETE [FROM db_name]

查询刚刚删除的语句：

mysql> show delete from example_db;
+------------+---------------+---------------------+--------------------+----------+
| TableName  | PartitionName | CreateTime          | DeleteCondition    | State    |
+------------+---------------+---------------------+--------------------+----------+
| delete_tbl | *             | 2023-04-01 15:14:42 | user_id EQ "10004" | FINISHED |
+------------+---------------+---------------------+--------------------+----------+

6.2.4Delete 相关配置

Doris的删除作业的超时时间限制在30秒到5分钟时间内，具体时间可通过下面配置项调整，以下参数都是在FE节点上进行配置。

• tablet_delete_timeout_second

delete自身的超时时间是可受指定分区下tablet的数量弹性改变的，此项配置为平均一个tablet所贡献的timeout时间，默认值为2。

假设此次删除所指定分区下有5个tablet，那么可提供给delete的timeout时间为10秒，由于低于最低超时时间30秒，因此最终超时时间为30秒。

• load_straggler_wait_second

如果用户预估的数据量确实比较大，使得5分钟的上限不足时，用户可以通过此项调整timeout上限，默认值为300。

• query_timeout

因为delete本身是一个SQL命令，因此删除语句也会受session限制，timeout还受Session中的query_timeout值影响，可以通过SET query_timeout = xxx来增加超时时间，单位是秒。

• max_allowed_in_element_num_of_delete

如果用户在使用in谓词时需要占用的元素比较多，用户可以通过此项调整允许携带的元素上限，默认值为1024。

6.3Sequence 列

为了能更好的了解Doris中批量删除，我们需要了解Sequence列。Unique模型主要针对需要唯一主键的场景，可以保证主键唯一性约束，但是由于使用REPLACE聚合方式，在同一批次中导入的数据，替换顺序不做保证。替换顺序无法保证则无法确定最终导入到表中的具体数据，存在了不确定性。

为了解决这个问题，Doris支持了sequence列，通过用户在导入时指定sequence列，相同key列下，REPLACE聚合类型的列将按照sequence列的值进行替换，较大值可以替换较小值，反之则无法替换。该方法将顺序的确定交给了用户，由用户控制替换顺序。

sequence 列目前只支持 Unique 存储模型。

6.3.1基本原理

可以在unique 类型的数据存储表中指定某列为sequence列，指定后，Doris会针对该表增加一个隐藏列__DORIS_SEQUENCE_COL__实现，该隐藏列就是为sequence的列。在向表中导入数据时会根据此列的值判断数据大小，数值大的会被保留，数值小的会被替换。

在向指定了sequence列的Unique类型表中导入数据时，fe在解析的过程中将隐藏列的值设置成 order by 表达式的值(broker load和routine load)，或者function_column.sequence_col表达式的值(stream load)，value列将按照该值进行替换。

6.3.2使用语法

在创建Unique类型存储表时有两种方式来指定Sequence列，一种是建表时设置sequence_col属性，一种是建表时设置sequence_type属性。

6.3.2.1设置sequence_col（推荐）

创建Uniq表时，指定sequence列到表中其他column的映射,如下:

PROPERTIES (
"function_column.sequence_col" = 'column_name',
);

sequence_col用来指定sequence列到表中某一列的映射，该列可以为整型和时间类型（DATE、DATETIME），创建后不能更改该列的类型。

导入方式和没有sequence列时一样，使用相对比较简单，推荐使用。

6.3.2.2设置sequence_type

创建Uniq表时，指定sequence列类型，如下:

PROPERTIES (
"function_column.sequence_type" = 'Date',
);

sequence_type用来指定sequence列的类型，可以为整型和时间类型（DATE、DATETIME）。

导入时需要指定sequence列到其他列的映射，例如使用Stream Load方式向表导入数据时，需要在Header中的function_column.sequence_col字段添加隐藏列对应的source_sequence的映射， 示例

curl --location-trusted -u root -H "columns: k1,k2,source_sequence,v1,v2" -H "function_column.sequence_col: source_sequence" -T testData http://host:port/api/testDb/testTbl/_stream_load

关于其他导入方式如何指定sequence_type对应的列参考：https://doris.apache.org/zh-CN/docs/dev/data-operate/update-delete/sequence-column-manual/#设置sequence_type

6.3.3Sequence列使用案例

下面以Stream Load向Doris Unquie表中加载数据为例来展示Sequence列作用。

1. 创建Doris Unquie 表

#创建Doris Unquie 表
CREATE TABLE example_db.sequence_unique_tbl
(
    user_id bigint,
    date date,
    group_id bigint,
    modify_date date,
    keyword VARCHAR(128)
)
UNIQUE KEY(user_id, date, group_id)
DISTRIBUTED BY HASH (user_id) BUCKETS 1
PROPERTIES(
    "function_column.sequence_col" = 'modify_date',
    "replication_num" = "1",
    "in_memory" = "false"
);

#查看表结构
mysql> desc sequence_unique_tbl;
+-------------+--------------+------+-------+---------+---------+
| Field       | Type         | Null | Key   | Default | Extra   |
+-------------+--------------+------+-------+---------+---------+
| user_id     | BIGINT       | Yes  | true  | NULL    |         |
| date        | DATE         | Yes  | true  | NULL    |         |
| group_id    | BIGINT       | Yes  | true  | NULL    |         |
| modify_date | DATE         | Yes  | false | NULL    | REPLACE |
| keyword     | VARCHAR(128) | Yes  | false | NULL    | REPLACE |
+-------------+--------------+------+-------+---------+---------+

#显示隐藏列
mysql> SET show_hidden_columns=true;

#再次显示表结构
mysql> desc sequence_unique_tbl;
+------------------------+--------------+------+-------+---------+---------+
| Field                  | Type         | Null | Key   | Default | Extra   |
+------------------------+--------------+------+-------+---------+---------+
| user_id                | BIGINT       | Yes  | true  | NULL    |         |
| date                   | DATE         | Yes  | true  | NULL    |         |
| group_id               | BIGINT       | Yes  | true  | NULL    |         |
| modify_date            | DATE         | Yes  | false | NULL    | REPLACE |
| keyword                | VARCHAR(128) | Yes  | false | NULL    | REPLACE |
| __DORIS_DELETE_SIGN__  | TINYINT      | No   | false | 0       | REPLACE |
| __DORIS_SEQUENCE_COL__ | DATE         | Yes  | false | NULL    | REPLACE |
+------------------------+--------------+------+-------+---------+---------+

1. 向 unique 表中加载数据

#在node1  中准备/root/data/testdata.txt文件写入如下数据
1,2020-02-22,1,2020-02-26,a
1,2020-02-22,1,2020-03-15,b
1,2020-02-22,1,2020-02-22,c

#以Stream load方式向表中加载数据
[root@node1 ~]# curl --location-trusted -u root:123456 -T /root/data/testdata.txt -H "column_separator:,"  http://node1:8030/api/example_db/sequence_unique_tbl/_stream_load
{
    "TxnId": 30055,
    "Label": "53cf3133-863e-4db6-943e-2dc8cd21f6e6",
    "TwoPhaseCommit": "false",
    "Status": "Success",
    "Message": "OK",
    "NumberTotalRows": 3,
    "NumberLoadedRows": 3,
    "NumberFilteredRows": 0,
    "NumberUnselectedRows": 0,
    "LoadBytes": 168,
    "LoadTimeMs": 122,
    "BeginTxnTimeMs": 3,
    "StreamLoadPutTimeMs": 15,
    "ReadDataTimeMs": 0,
    "WriteDataTimeMs": 64,
    "CommitAndPublishTimeMs": 35
}

1. 导入数据后查询结果

mysql> select * from sequence_unique_tbl;                                                                                              
+---------+------------+----------+-------------+---------+
| user_id | date       | group_id | modify_date | keyword |
+---------+------------+----------+-------------+---------+
|       1 | 2020-02-22 |        1 | 2020-03-15  | b       |
+---------+------------+----------+-------------+---------+

在这次导入中，因sequence column的值（也就是modify_date中的值）中’2020-03-05’为最大值，所以keyword列中最终保留了b。

1. 第二次向表中导入数据，再次查询结果

#在node1 /root/data/testdata2.txt 中准备如下数据
1,2020-02-22,1,2020-02-22,a
1,2020-02-22,1,2020-02-23,b

#再次通过Stream Load 向表sequence_unique_tbl中加载数据
[root@node1 data]# curl --location-trusted -u root:123456 -T /root/data/testdata2.txt -H "column_separator:,"  http://node1:8030/api/example_db/sequence_unique_tbl/_stream_load
{
    "TxnId": 30056,
    "Label": "4dad06e2-9113-4f96-aada-9a4f04119a64",
    "TwoPhaseCommit": "false",
    "Status": "Success",
    "Message": "OK",
    "NumberTotalRows": 2,
    "NumberLoadedRows": 2,
    "NumberFilteredRows": 0,
    "NumberUnselectedRows": 0,
    "LoadBytes": 56,
    "LoadTimeMs": 68,
    "BeginTxnTimeMs": 1,
    "StreamLoadPutTimeMs": 4,
    "ReadDataTimeMs": 0,
    "WriteDataTimeMs": 27,
    "CommitAndPublishTimeMs": 33
}

#再次查看数据表中数据，还是没有变
mysql> select * from sequence_unique_tbl;                                                                                              
+---------+------------+----------+-------------+---------+
| user_id | date       | group_id | modify_date | keyword |
+---------+------------+----------+-------------+---------+
|       1 | 2020-02-22 |        1 | 2020-03-15  | b       |
+---------+------------+----------+-------------+---------+

在这次导入的数据中，会比较所有已导入数据的sequence column（也就是modify_date)，其中’2020-03-05’为最大值，所以keyword列中最终保留了b。

1. 第三次向表中导入数据，再次查询结果

#在node1  /root/data/testdata3.txt 中准备如下数据
1,2020-02-22,1,2020-02-22,a
1,2020-02-22,1,2020-03-23,w

#再次通过Stream Load 向表sequence_unique_tbl中加载数据
[root@node1 data]# curl --location-trusted -u root:123456 -T /root/data/testdata3.txt -H "column_separator:,"  http://node1:8030/api/example_db/sequence_unique_tbl/_stream_load
{
    "TxnId": 30057,
    "Label": "fd45a993-5742-4e63-ba19-e091368d607a",
    "TwoPhaseCommit": "false",
    "Status": "Success",
    "Message": "OK",
    "NumberTotalRows": 2,
    "NumberLoadedRows": 2,
    "NumberFilteredRows": 0,
    "NumberUnselectedRows": 0,
    "LoadBytes": 56,
    "LoadTimeMs": 70,
    "BeginTxnTimeMs": 2,
    "StreamLoadPutTimeMs": 5,
    "ReadDataTimeMs": 0,
    "WriteDataTimeMs": 21,
    "CommitAndPublishTimeMs": 39
}

#再次查看数据表中数据
mysql> select * from sequence_unique_tbl;
+---------+------------+----------+-------------+---------+
| user_id | date       | group_id | modify_date | keyword |
+---------+------------+----------+-------------+---------+
|       1 | 2020-02-22 |        1 | 2020-03-23  | w       |
+---------+------------+----------+-------------+---------+
1 row in set (0.01 sec)

此时就可以替换表中原有的数据。综上，在导入过程中，会比较所有批次的sequence列值，选择值最大的记录导入Doris表中。

6.4批量删除

对于数据的删除目前只能通过delete语句进行删除，使用delete 语句的方式删除时，每执行一次delete 都会生成一个新的数据版本，如果频繁删除会严重影响查询性能，并且在使用delete方式删除时，是通过生成一个空的rowset来记录删除条件实现，每次读取都要对删除条件进行过滤，同样在条件较多时会对性能造成影响。

我们可以使用批量删除方式来解决以上问题， 批量删除只针对 Unique 模型的存储表。

6.4.1批量删除原理

目前Doris 支持 Broker Load，Routine Load， Stream Load 等多种导入方式，针对一张已经存在的Unique表，通过不同的导入方式向表中增加数据时，导入的数据有三种合并方式：

• APPEND: 数据全部追加到现有数据中【默认】；
• DELETE: 删除所有与导入数据key 列值相同的行(当表存在sequence列时，需要同时满足主键相同以及sequence列的大小逻辑才能正确删除)；
• MERGE: 根据 DELETE ON 的决定 APPEND 还是 DELETE。

我们可以通过想Unique表中导入数据时指定DELETE 模式来删除表中相同key的数据，Unique表底层有一个隐藏列__DORIS_DELETE_SIGN__，该隐藏列底层实际为true或者false，分别使用TyinInt 类型1和0代表，决定了Unique表中最终展示给用户的数据。向Unique表中导入数据进行删除时会自动根据导入的相同key的数据进行该隐藏列的标记进行删除，用户读取时扫描数据时会自动增加__DORIS_DELETE_SIGN__ != true 的条件，即查询__DORIS_DELETE_SIGN__为0的数据最终展示给用户。

6.4.2批量删除案例

这里以向unique数据存储模型表导入数据进行表数据删除演示，关于其他导入数据方式参考官网：https://doris.apache.org/zh-CN/docs/dev/data-operate/update-delete/batch-delete-manual#语法说明。

Unique表中有无 Sequence 列，在通过导入数据达到删除数据目的上效果不同，具体区别如以下案例所示。

6.4.2.1Unique表没有Sequence列

1. 创建没有Sequence 列的 Unique 存储表，并加载数据

#创建表
CREATE TABLE example_db.delete_tbl2
(
    name VARCHAR(128),
    gender VARCHAR(10),
    age int
)
UNIQUE KEY(name)
DISTRIBUTED BY HASH (name) BUCKETS 1
PROPERTIES(
    "replication_num" = "1"
);

#向表中插入如下数据
mysql> insert into delete_tbl2 values ("li","male",10),("wang","male",14),("zhang","male",12);

#设置开启/关闭 隐藏列
mysql> SET show_hidden_columns=false;

#查询表中数据
mysql> select * from delete_tbl2;
+-------+--------+------+
| name  | gender | age  |
+-------+--------+------+
| li    | male   |   10 |
| wang  | male   |   14 |
| zhang | male   |   12 |
+-------+--------+------+

1. 通过 Stream Load 向表中加载数据并删除相同 key 数据

#在node1 准备/root/data/del_data.txt 写入如下数据
li,male,9

#执行stream load命令，将数据导入到表 delete_tbl2中
[root@node1 data]# curl --location-trusted -u root:123456 -H "column_separator:," -H "merge_type: DELETE"  -T /root/data/del_data.txt http://node1:8030/api/example_db/delete_tbl2/_stream_load
{
    "TxnId": 30060,
    "Label": "a09fa8fa-7b01-4df2-a2ca-fe1a8e891177",
    "TwoPhaseCommit": "false",
    "Status": "Success",
    "Message": "OK",
    "NumberTotalRows": 1,
    "NumberLoadedRows": 1,
    "NumberFilteredRows": 0,
    "NumberUnselectedRows": 0,
    "LoadBytes": 11,
    "LoadTimeMs": 60,
    "BeginTxnTimeMs": 4,
    "StreamLoadPutTimeMs": 7,
    "ReadDataTimeMs": 0,
    "WriteDataTimeMs": 19,
    "CommitAndPublishTimeMs": 26
}

#查询表 delete_tbl2中的数据
mysql> select * from delete_tbl2;
+-------+--------+------+
| name  | gender | age  |
+-------+--------+------+
| wang  | male   |   14 |
| zhang | male   |   12 |
+-------+--------+------+

可以看到对没有sequence列的Unique存储表通过Stream Load方式导数据数据达到删除数据目的时，需要指定-H "merge_type: DELETE"参数，只要是导入数据中有与表中相同key的数据，该key对应数据会被删除。

6.4.2.2Unique表有Sequence列

当 unique 表设置了 sequence 列时，在相同 key 列下， sequence 列的值会作为 REPLACE 聚合函数替换顺序的依据，较大值可以替换较小值。 当对这种表基于 DORIS_DELETE_SIGN 进行删除标记时，需要保证 key 相同和 sequence 列值要大于等于当前值。

1. 创建带有Sequence 列的 Unique 存储表，并加载数据

#创建表
CREATE TABLE example_db.delete_tbl3
(
    name VARCHAR(128),
    gender VARCHAR(10),
    age int
)
UNIQUE KEY(name)
DISTRIBUTED BY HASH (name) BUCKETS 1
PROPERTIES(
    "function_column.sequence_col" = 'age',
    "replication_num" = "1"
);

#向表中插入如下数据
mysql> insert into delete_tbl3 values ("li","male",10),("wang","male",14),("zhang","male",12);

#设置开启/关闭 隐藏列
mysql> SET show_hidden_columns=false;

#查询表中数据
mysql> select * from delete_tbl3;
+-------+--------+------+
| name  | gender | age  |
+-------+--------+------+
| li    | male   |   10 |
| wang  | male   |   14 |
| zhang | male   |   12 |
+-------+--------+------+

1. 通过 Stream Load 向表中加载数据并删除相同 key 数据

#在node1 准备/root/data/del_data2.txt 写入如下数据
li,male,9
wang,male,30

#执行stream load命令，将数据导入到表 delete_tbl3中
[root@node1 ~]# curl --location-trusted -u root:123456 -H "column_separator:," -H "merge_type: DELETE"  -T /root/data/del_data2.txt http://node1:8030/api/example_db/delete_tbl3/_stream_load
{
    "TxnId": 30065,
    "Label": "0c7b038b-0717-47c4-aaad-b2df2c80fe4f",
    "TwoPhaseCommit": "false",
    "Status": "Success",
    "Message": "OK",
    "NumberTotalRows": 2,
    "NumberLoadedRows": 2,
    "NumberFilteredRows": 0,
    "NumberUnselectedRows": 0,
    "LoadBytes": 23,
    "LoadTimeMs": 54,
    "BeginTxnTimeMs": 2,
    "StreamLoadPutTimeMs": 5,
    "ReadDataTimeMs": 0,
    "WriteDataTimeMs": 25,
    "CommitAndPublishTimeMs": 18
}

#查询表 delete_tbl3中的数据
mysql> select * from delete_tbl3;
+-------+--------+------+
| name  | gender | age  |
+-------+--------+------+
| li    | male   |   10 |
| zhang | male   |   12 |
+-------+--------+------+

以上结果主要原因如下：

• 由于表设置了sequence列，针对相同key列下，sequence列的值会作为REPLACE聚合函数替换顺序的依据，较大值可以替换较小值，所以name为li的数据不会被删除。
• 对含有sequence列的unquie存储这种表基于__DORIS_DELETE_SIGN__进行删除标记时，需要保证key相同和sequence列值要大于等于当前值。所以name为wang的数据由于该key导入的数据对应的sequence列大于源表中该key对应的sequence列的值，所以会被删除。