腾讯云数据仓库 TCHouse-D 数据删除-开发指南-文档中心-腾讯云

在使用腾讯云数据仓库 TCHouse-D 的过程中，您可以使用 Delete 操作、Truncate 操作或者批量删除方法删除数据。
Delete 操作
Delete 操作语句通过 MySQL 协议，对指定的 table 或者 partition 中的数据进行按条件删除。Delete 删除操作不同于基于导入的批量删除，它类似 Insert into 语句，是一个同步过程。所有的 Delete 操作在 Doris 中是一个独立的导入作业，一般 Delete 语句需要指定表和分区以及删除的条件来筛选要删除的数据，并将会同时删除 base 表和 rollup 表的数据。Delete 操作的语法详见 DELETE 语法，也可以在 Mysql 客户端命令行下输入 HELP DELETE 获取更多帮助信息。
1. 通过指定过滤谓词来删除
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: 做逻辑比较的值或值列表
可选的参数​﻿
PARTITION partition_name | PARTITIONS (partition_name [, partition_name])：指定执行删除数据的分区名，如果表不存在此分区，则报错。
table_alias: 表的别名
注意事项​﻿
使用表模型 AGGREGATE、UNIQUE 时，只能指定 Key 列上的条件。
当选定的 Key 列不存在于某个 Rollup 中时，无法进行 Delete。
条件之间只能是“与”的关系。若希望达成“或”的关系，需要将条件分写在两个 DELETE 语句中。
如果为分区表，需要指定分区，如果不指定，Doris 会从条件中推断出分区。两种情况下，Doris 无法从条件中推断出分区：
条件中不包含分区列
分区列的 op 为 not in。当分区表未指定分区，或者无法从条件中推断分区的时候，需要设置会话变量 delete_without_partition 为 true，此时 Delete 会应用到所有分区。
该语句可能会降低执行后一段时间内的查询效率。影响程度取决于语句中指定的删除条件的数量。指定的条件越多，影响越大。
2. 通过使用 Using 子句来删除
DELETE FROM table_name [table_alias]
    [PARTITION partition_name | PARTITIONS (partition_name [, partition_name])]
    [USING additional_tables]
    WHERE condition
必须的参数​﻿
table_name: 指定需要删除数据的表
WHERE condition: 指定一个用于选择删除行的条件
可选的参数​﻿
PARTITION partition_name | PARTITIONS (partition_name [, partition_name]): 指定执行删除数据的分区名，如果表不存在此分区，则报错。
table_alias: 表的别名
注意事项​﻿
此种形式只能在 UNIQUE KEY 模型表上使用
只能在表模型 UNIQUE Key 表模型上使用，只能指定 key 列上的条件。
使用示例​﻿
使用t2和t3表连接的结果，删除t1中的数据，删除的表只支持 unique 模型。
-- 创建t1, t2, t3三张表
CREATE TABLE t1
  (id INT, c1 BIGINT, c2 STRING, c3 DOUBLE, c4 DATE)
UNIQUE KEY (id)
DISTRIBUTED BY HASH (id)
PROPERTIES('replication_num'='1', "function_column.sequence_col" = "c4");
﻿
CREATE TABLE t2
  (id INT, c1 BIGINT, c2 STRING, c3 DOUBLE, c4 DATE)
DISTRIBUTED BY HASH (id)
PROPERTIES('replication_num'='1');
﻿
CREATE TABLE t3
  (id INT)
DISTRIBUTED BY HASH (id)
PROPERTIES('replication_num'='1');
﻿
-- 插入数据
INSERT INTO t1 VALUES
  (1, 1, '1', 1.0, '2000-01-01'),
  (2, 2, '2', 2.0, '2000-01-02'),
  (3, 3, '3', 3.0, '2000-01-03');
﻿
INSERT INTO t2 VALUES
  (1, 10, '10', 10.0, '2000-01-10'),
  (2, 20, '20', 20.0, '2000-01-20'),
  (3, 30, '30', 30.0, '2000-01-30'),
  (4, 4, '4', 4.0, '2000-01-04'),
  (5, 5, '5', 5.0, '2000-01-05');
﻿
INSERT INTO t3 VALUES
  (1),
  (4),
  (5);
﻿
-- 删除 t1 中的数据
DELETE FROM t1
  USING t2 INNER JOIN t3 ON t2.id = t3.id
  WHERE t1.id = t2.id;
预期结果为，删除了t1表中 id 为1的列。
+----+----+----+--------+------------+
| id | c1 | c2 | c3     | c4         |
+----+----+----+--------+------------+
| 2  | 2  | 2  |    2.0 | 2000-01-02 |
| 3  | 3  | 3  |    3.0 | 2000-01-03 |
+----+----+----+--------+------------+
3. 结果返回
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'}
2. 提交成功，但未可见。
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：字段会显示一些本次删除的详细信息。
3. 提交失败，事务取消。
如果 Delete 语句没有提交成功，将会被 Doris 自动中止，返回下列结果：
mysql> delete from test_tbl partition p1 where k1 > 80;
ERROR 1064 (HY000): errCode = 2, detailMessage = {错误原因}
如果ete 操作的语法详见 DELETE 语法一个删除超时，将会返回 timeout 时间和未完成的(tablet=replica)。
mysql> delete from test_tbl partition p1 where k1 > 80;
ERROR 1064 (HY000): errCode = 2, detailMessage = failed to delete replicas from job: 4005, Unfinished replicas:10000=60000, 10001=60000, 10002=60000
综上，对于 Delete 操作返回结果的正确处理逻辑为：
1. 如果返回结果为 ERROR 1064 (HY000)，则表示删除失败。
2. 如果返回结果为 Query OK，则表示删除执行成功。
如果 status 为 COMMITTED，表示数据仍不可见，用户可以稍等一段时间再用 show delete 命令查看结果。
如果 status 为 VISIBLE，表示数据删除成功。
4. Delete 操作相关 FE 配置
TIMEOUT 配置
总体来说，删除作业的超时时间计算规则为如下（单位：秒），默认超时时间限制在30秒到5分钟时间内：
TIMEOUT = MIN(load_straggler_wait_second, MAX(30, tablet_delete_timeout_second * tablet_num))
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 来增加超时时间，单位是秒。
IN 谓词配置
max_allowed_in_element_num_of_delete
如果用户在使用 in 谓词时需要占用的元素比较多，用户可以通过此项调整允许携带的元素上限，默认值为1024。
5. 查看 Delete 操作历史记录
用户可以通过 show delete 语句查看历史上已执行完成的删除记录。
语法如下：
SHOW DELETE [FROM db_name]
使用示例：
mysql> show delete from test_db;
+-----------+---------------+---------------------+-----------------+----------+
| TableName | PartitionName | CreateTime          | DeleteCondition | State    |
+-----------+---------------+---------------------+-----------------+----------+
| empty_tbl | p3            | 2020-04-15 23:09:35 | k1 EQ "1"       | FINISHED |
| test_tbl  | p4            | 2020-04-15 23:09:53 | k1 GT "80"      | FINISHED |
+-----------+---------------+---------------------+-----------------+----------+
2 rows in set (0.00 sec)
Truncate 操作
支持使用该语句清空指定表和分区的数据。
1. 语法
TRUNCATE TABLE [db.]tbl[ PARTITION(p1, p2, ...)];
该语句清空数据，但保留表或分区。
不同于 DELETE，该语句只能整体清空指定的表或分区，不能添加过滤条件。
不同于 DELETE，使用该方式清空数据不会对查询性能造成影响。
该操作删除的数据不可恢复。
使用该命令时，表状态需为 NORMAL，即不允许正在进行 SCHEMA CHANGE 等操作。
该命令可能会导致正在进行的导入失败。
2. 示例
1. 清空 example_db 下的表 tbl
TRUNCATE TABLE example_db.tbl;
2. 清空表 tbl 的 p1 和 p2 分区
TRUNCATE TABLE tbl PARTITION(p1, p2);
批量删除
引入基于导入的批量删除，主要基于以下原因：
使用 Delete 语句的方式删除时，每执行一次 Delete 都会生成一个空的 rowset 来记录删除条件，并产生一个新的数据版本。每次读取都要对删除条件进行过滤，如果频繁删除或者删除条件过多时，都会严重影响查询性能。
对于类似于从事务数据库中，通过 CDC 进行数据导入的场景，数据中 Insert 和 Delete 一般是穿插出现的，面对这种场景当前 Delete 操作也是无法实现。
基于数据导入的方式，数据有三种合并方式：
APPEND: 数据全部追加到现有数据中。
DELETE: 删除所有与导入数据 key 列值相同的行 (当表存在sequence列时，需要同时满足主键相同以及 sequence 列的大小逻辑才能正确删除)。
MERGE: 根据 DELETE ON 的决定 APPEND 还是 DELETE。
说明：
批量删除只能作用于 Unique 模型。
1. 基本原理
通过增加一个隐藏列__DORIS_DELETE_SIGN__实现，因为我们只是在 unique 模型上做批量删除，因此只需要增加一个类型为 bool 聚合函数为 replace 的隐藏列即可。在 BE 各种聚合写入流程都和正常列一样，读取方案有两个：
在 FE 遇到 select * 等扩展时去掉__DORIS_DELETE_SIGN__列，并且默认加上 __DORIS_DELETE_SIGN__ != true 的条件， BE 读取时都会加上一列进行判断，通过条件确定是否删除。
导入
导入时在 FE 解析时将隐藏列的值设置成 DELETE ON 表达式的值，其他的聚合行为和 replace 的聚合列相同。
读取
读取时在所有存在隐藏列的 olapScanNode 上增加__DORIS_DELETE_SIGN__ != true 的条件，BE 不感知这一过程，正常执行。
Cumulative Compaction
Cumulative Compaction 时将隐藏列看作正常的列处理，Compaction 逻辑没有变化。
Base Compaction
Base Compaction 时要删掉被标记为删除的行，以减少数据占用的空间。
2. 启用批量删除支持
启用批量删除支持有以下两种形式：
1. 通过在 FE 配置文件中增加enable_batch_delete_by_default=true 重启 FE 后新建表的都支持批量删除，此选项默认为 false。（2.0版本后，默认为 true）
2. 对于没有更改上述 FE 配置或对于以存在的不支持批量删除功能的表，可以使用如下语句： ALTER TABLE tablename ENABLE FEATURE "BATCH_DELETE" 来启用批量删除。本操作本质上是一个 schema change 操作，操作立即返回，可以通过show alter table column 来确认操作是否完成。
为确定一个表是否支持批量删除，可通过设置一个 session variable 来显示隐藏列 SET show_hidden_columns=true ，之后使用desc tablename，如果输出中有__DORIS_DELETE_SIGN__ 列则支持，如果没有则不支持。
3. 语法说明
导入的语法设计方面主要是增加一个指定删除标记列的字段的 colum 映射，并且需要在导入的数据中增加一列，各种导入方式设置的语法如下。
Stream Load
Stream Load 的写法在 header 中的 columns 字段增加一个设置删除标记列的字段， 示例 -H "columns: k1, k2, label_c3" -H "merge_type: [MERGE|APPEND|DELETE]" -H "delete: label_c3=1"。
Broker Load
Broker Load 的写法在 PROPERTIES 处设置删除标记列的字段，语法如下：
LOAD LABEL db1.label1
(
    [MERGE|APPEND|DELETE] DATA INFILE("hdfs://abc.com:8888/user/palo/test/ml/file1")
    INTO TABLE tbl1
    COLUMNS TERMINATED BY ","
    (tmp_c1,tmp_c2, label_c3)
    SET
    (
        id=tmp_c2,
        name=tmp_c1,
    )
    [DELETE ON label_c3=true]
)
WITH BROKER 'broker'
(
    "username"="user",
    "password"="pass"
)
PROPERTIES
(
    "timeout" = "3600"
);
Routine Load
Routine Load的写法在  columns字段增加映射，映射方式同上，语法如下：
CREATE ROUTINE LOAD example_db.test1 ON example_tbl 
 [WITH MERGE|APPEND|DELETE]
 COLUMNS(k1, k2, k3, v1, v2, label),
 WHERE k1 > 100 and k2 like "%doris%"
 [DELETE ON label=true]
 PROPERTIES
 (
     "desired_concurrent_number"="3",
     "max_batch_interval" = "20",
     "max_batch_rows" = "300000",
     "max_batch_size" = "209715200",
     "strict_mode" = "false"
 )
 FROM KAFKA
 (
     "kafka_broker_list" = "broker1:9092,broker2:9092,broker3:9092",
     "kafka_topic" = "my_topic",
     "kafka_partitions" = "0,1,2,3",
     "kafka_offsets" = "101,0,0,200"
 );
注意事项
1. 由于除Stream Load 外的导入操作在 Doris 内部有可能乱序执行，因此在使用MERGE 方式导入时如果不是Stream Load，需要与 load sequence 一起使用，具体的语法可以参照 Sequence 列 相关的文档。
2. DELETE ON 条件只能与 MERGE 一起使用。
如果在执行导入作业前按上文所述开启了 SET show_hidden_columns = true 的 session variable 来查看表是否支持批量删除，按示例完成 DELETE/MERGE 的导入作业后，如果在同一个 session 中执行 select count(*) from xxx 等语句时，需要执行 SET show_hidden_columns = false 或者开启新的 session, 避免查询结果中包含那些被批量删除的记录，导致结果与预期不符。
4. 使用示例
查看是否启用批量删除支持
mysql> SET show_hidden_columns=true;
Query OK, 0 rows affected (0.00 sec)
﻿
mysql> DESC test;
+-----------------------+--------------+------+-------+---------+---------+
| Field                 | Type         | Null | Key   | Default | Extra   |
+-----------------------+--------------+------+-------+---------+---------+
| name                  | VARCHAR(100) | No   | true  | NULL    |         |
| gender                | VARCHAR(10)  | Yes  | false | NULL    | REPLACE |
| age                   | INT          | Yes  | false | NULL    | REPLACE |
| __DORIS_DELETE_SIGN__ | TINYINT      | No   | false | 0       | REPLACE |
+-----------------------+--------------+------+-------+---------+---------+
4 rows in set (0.00 sec)
Stream Load 使用示例
1. 正常导入数据：
curl --location-trusted -u root: -H "column_separator:," -H "columns: siteid, citycode, username, pv" -H "merge_type: APPEND"  -T ~/table1_data http://127.0.0.1:8130/api/test/table1/_stream_load
其中的 APPEND 条件可以省略，与下面的语句效果相同：
curl --location-trusted -u root: -H "column_separator:," -H "columns: siteid, citycode, username, pv" -T ~/table1_data http://127.0.0.1:8130/api/test/table1/_stream_load
2. 将与导入数据 key 相同的数据全部删除：
curl --location-trusted -u root: -H "column_separator:," -H "columns: siteid, citycode, username, pv" -H "merge_type: DELETE"  -T ~/table1_data http://127.0.0.1:8130/api/test/table1/_stream_load
假设导入表中原有数据为：
+--------+----------+----------+------+
| siteid | citycode | username | pv   |
+--------+----------+----------+------+
|      3 |        2 | tom      |    2 |
|      4 |        3 | bush     |    3 |
|      5 |        3 | helen    |    3 |
+--------+----------+----------+------+
导入数据为：
3,2,tom,0
导入后数据变成：
+--------+----------+----------+------+
| siteid | citycode | username | pv   |
+--------+----------+----------+------+
|      4 |        3 | bush     |    3 |
|      5 |        3 | helen    |    3 |
+--------+----------+----------+------+
3. 将导入数据中与site_id=1 的行的 key 列相同的行。
curl --location-trusted -u root: -H "column_separator:," -H "columns: siteid, citycode, username, pv" -H "merge_type: MERGE" -H "delete: siteid=1"  -T ~/table1_data http://127.0.0.1:8130/api/test/table1/_stream_load
假设导入前数据为：
+--------+----------+----------+------+
| siteid | citycode | username | pv   |
+--------+----------+----------+------+
|      4 |        3 | bush     |    3 |
|      5 |        3 | helen    |    3 |
|      1 |        1 | jim      |    2 |
+--------+----------+----------+------+
导入数据为：
2,1,grace,2
3,2,tom,2
1,1,jim,2
导入后为：
+--------+----------+----------+------+
| siteid | citycode | username | pv   |
+--------+----------+----------+------+
|      4 |        3 | bush     |    3 |
|      2 |        1 | grace    |    2 |
|      3 |        2 | tom      |    2 |
|      5 |        3 | helen    |    3 |
+--------+----------+----------+------+
4. 当存在 sequence 列时，将与导入数据 Key 相同的数据全部删除。
curl --location-trusted -u root: -H "column_separator:," -H "columns: name, gender, age" -H "function_column.sequence_col: age" -H "merge_type: DELETE"  -T ~/table1_data http://127.0.0.1:8130/api/test/table1/_stream_load
当 Unique 表设置了 Sequence 列时，在相同 Key 列下，Sequence 列的值会作为 REPLACE 聚合函数替换顺序的依据，较大值可以替换较小值。当对这种表基于 DORIS_DELETE_SIGN 进行删除标记时，需要保证 Key 相同和 Sequence 列值要大于等于当前值。
假设有表，结构如下：
mysql SET show_hidden_columns=true;
Query OK, 0 rows affected (0.00 sec)
﻿
mysql DESC table1;
+------------------------+--------------+------+-------+---------+---------+
| Field                  | Type         | Null | Key   | Default | Extra   |
+------------------------+--------------+------+-------+---------+---------+
| name                   | VARCHAR(100) | No   | true  | NULL    |         |
| gender                 | VARCHAR(10)  | Yes  | false | NULL    | REPLACE |
| age                    | INT          | Yes  | false | NULL    | REPLACE |
| DORIS_DELETE_SIGN  | TINYINT      | No   | false | 0       | REPLACE |
| DORIS_SEQUENCE_COL | INT          | Yes  | false | NULL    | REPLACE |
+------------------------+--------------+------+-------+---------+---------+
4 rows in set (0.00 sec)
假设导入表中原有数据为：
+-------+--------+------+
| name  | gender | age  |
+-------+--------+------+
| li    | male   |   10 |
| wang  | male   |   14 |
| zhang | male   |   12 |
+-------+--------+------+
当导入数据为：
li,male,10
导入后数据后会变成：
+-------+--------+------+
| name  | gender | age  |
+-------+--------+------+
| wang  | male   |   14 |
| zhang | male   |   12 |
+-------+--------+------+
会发现数据：
li,male,10
被删除成功。但是假如导入数据为：
li,male,9
导入后数据会变成：
+-------+--------+------+
| name  | gender | age  |
+-------+--------+------+
| li    | male   |   10 |
| wang  | male   |   14 |
| zhang | male   |   12 |
+-------+--------+------+
会看到数据：
li,male,10
并没有被删除，这是因为在底层的依赖关系上，会先判断 key 相同的情况，对外展示 sequence 列的值大的行数据，然后在看该行的 DORIS_DELETE_SIGN 值是否为 1，如果为 1 则不会对外展示，如果为 0，则仍会读出来。
当导入数据中同时存在数据写入和删除时（例如 CDC 场景中），使用 Sequence 列可以有效的保证当数据乱序到达时的一致性，避免后到达的一个旧版本的删除操作，误删掉了先到达的新版本的数据。
数据删除

本页目录：

Delete 操作

1. 通过指定过滤谓词来删除

2. 通过使用 Using 子句来删除

3. 结果返回

4. Delete 操作相关 FE 配置

5. 查看 Delete 操作历史记录

Truncate 操作

1. 语法

2. 示例

批量删除

1. 基本原理

导入

读取

Cumulative Compaction

Base Compaction

2. 启用批量删除支持

3. 语法说明

Stream Load

Broker Load

Routine Load

注意事项

4. 使用示例

查看是否启用批量删除支持

Stream Load 使用示例
