ApsaraDB for SelectDB:Delete data by using the DELETE statement

Overview

The DELETE statement is different from other data import methods. The DELETE statement performs a synchronous operation, which is similar to the INSERT INTO statement. Each DELETE operation is a separate import job in ApsaraDB for SelectDB. In most cases, when you execute the DELETE statement, you must specify the table, partitions, and delete conditions to filter the data that you want to delete. Data is deleted from both the base table and its rollup tables.

Usage method

You can execute the DELETE statement to delete data from a specific table and its rollup tables.

Syntax

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

Parameters

Returned results

The DELETE statement is an SQL statement that synchronously returns the results. The results are returned in the following cases:

The transaction is executed

If the DELETE statement is executed and the results are visible, the following results are returned. Query OK indicates that the statement is executed.

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'}

The transaction is committed but the results are invisible

A transaction is committed in two-phase commit mode in ApsaraDB for SelectDB: The transaction is committed and a version is released for the transaction.

The results are visible until the version is released. If the transaction is committed, a version will be eventually released. After the transaction is committed, ApsaraDB for SelectDB waits for a period of time until a version is released for the transaction. If the version is still not released after the timeout period elapses, ApsaraDB for SelectDB returns a message, which indicates that the transaction is committed.

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' }

The following table describes the parameters in the JSON string of the returned results.

The transaction fails to be committed and is canceled

If a transaction fails to be committed, ApsaraDB for SelectDB automatically aborts the transaction and returns the following results:

DELETE FROM test_tbl PARTITION p1 WHERE k1 > 80;
ERROR 1064 (HY000): errCode = 2, detailMessage = {Cause of the error}

If the DELETE operation times out, the system returns the timeout period and the replicas from which data is not completely deleted.

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

The preceding examples show that one of the following results may be returned for the DELETE statement:

• If ERROR 1064 (HY000) is returned, the DELETE operation fails.

• If Query OK is returned, the DELETE operation is successful.

  • If COMMITTED is returned for the status parameter, the data is still invisible. You can wait for a period of time and then execute the SHOW DELETE statement to query the results.

  • If VISIBLE is returned for the status parameter, the data is deleted.

Query the deletion records

You can execute the SHOW DELETE statement to query the deletion records of the DELETE statement.

Syntax:

SHOW DELETE [FROM db_name]

Sample code:

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)

Related FE configurations

Timeout configurations

The timeout period of a deletion job in ApsaraDB for SelectDB ranges from 30 seconds to 5 minutes. You can specify a timeout period by using the following configuration items:

• tablet_delete_timeout_second

  The timeout period of a deletion job varies based on the number of tablets in a partition. This configuration item specifies the average timeout period that is contributed by a tablet. Default value: 2. Unit: seconds. If five tablets exist in the partition that is specified for this deletion job, the timeout period that is allowed for this deletion job is 10 seconds. However, the timeout period of 10 seconds is shorter than the minimum timeout period of 30 seconds. In this case, the final timeout period for this deletion job is 30 seconds.

• load_straggler_wait_second

  If you want to delete a large amount of data and need a timeout period that exceeds 5 minutes, you can adjust the timeout period that is allowed for this deletion job by specifying this configuration item. Default value: 300. Unit: seconds. The timeout period that is allowed for a deletion job is calculated based on the following formula:

  TIMEOUT = MIN(load_straggler_wait_second, MAX(30, tablet_delete_timeout_second * tablet_num))

• query_timeout

  The DELETE statement is an SQL statement that is also limited by session variables. The timeout period is also affected by the value of the query_timeout session variable. You can increase the timeout period by executing the SET query_timeout = xxx statement. Default value: 900. Unit: seconds.

Configurations of the IN predicate

• max_allowed_in_element_num_of_delete

  If you need to use a large number of elements when you use the IN predicate, you can modify the maximum number of elements that are allowed. By default, this parameter is set to 1024.