How to use DELETE to delete rows from a table - MaxCompute - Centro de documentación de Alibaba Cloud

Requisitos previos

Antes de ejecutar una instrucción DELETE o UPDATE, asegúrese de contar con los permisos Select y Update sobre la tabla transaccional o Delta Table de destino. Para obtener más información, consulte MaxCompute permissions.

Límites

Las instrucciones DELETE y UPDATE solo funcionan en tablas transaccionales y Delta Tables, y están sujetas a los límites descritos en Parameters for Transaction Table and Delta Table.

La sintaxis UPDATE para Delta Tables no admite la modificación de columnas de clave principal (PK).

Sintaxis

DELETE FROM <table_name> [[AS] alias] [WHERE <condition>];

Parámetros

Parameter	Required	Description
`table_name`	Sí	Nombre de la tabla transaccional o Delta Table de la cual se eliminarán las filas.
`alias`	No	Alias de la tabla. Utilice un alias cuando haga referencia a la tabla en la subconsulta de la cláusula `WHERE`.
`condition`	No	Cláusula `WHERE` que identifica las filas por eliminar. Omita esta cláusula para borrar todas las filas de la tabla. Para consultar la sintaxis de la cláusula `WHERE`, véase WHERE clause (WHERE_condition).

Notas de uso

Cuándo utilizar DELETE

La instrucción DELETE genera archivos delta en lugar de reescribir los datos de la tabla directamente, por lo que no reduce el almacenamiento de inmediato. Siga estas recomendaciones para elegir la operación adecuada según su carga de trabajo.

Scenario	Recommended operation
Pocas filas, eliminaciones poco frecuentes, lecturas esporádicas	`DELETE` o `UPDATE`. Tras realizar varias operaciones, combine los archivos base y los archivos delta para recuperar espacio de almacenamiento.
Más del 5% de las filas, eliminaciones poco frecuentes, lecturas constantes	`INSERT OVERWRITE` o `INSERT INTO`. Consulte Insert or overwrite data.

Por ejemplo, si un proceso elimina o actualiza el 10% de las filas de una tabla diez veces al día, compare el costo total de escaneo de diez ejecuciones de DELETE frente a diez ejecuciones de INSERT OVERWRITE. En tablas con muchas lecturas, INSERT OVERWRITE suele ser más eficiente.

Agrupe sus operaciones de eliminación

MaxCompute ejecuta cada instrucción DELETE como un trabajo por lotes y cobra según la cantidad de datos escaneados. Evite patrones donde un script genere una instrucción por cada fila; el costo acumulado de escaneo de muchas instrucciones pequeñas es significativamente mayor que el de una sola instrucción que abarque todas las filas afectadas.

Utilice una subconsulta para procesar varias filas en una misma instrucción:

-- Recommended: one statement covers all rows that need updating
UPDATE table1 SET col1 = (SELECT value1 FROM table2 WHERE table1.id = table2.id AND table1.region = table2.region);

-- Avoid: one statement per row generates one scan per statement
UPDATE table1 SET col1 = 1 WHERE id = '2021063001' AND region = 'beijing';
UPDATE table1 SET col1 = 2 WHERE id = '2021063002' AND region = 'beijing';

Reduzca el almacenamiento después de eliminar datos

La instrucción DELETE escribe archivos delta sobre los archivos base de la tabla. Para recuperar el espacio liberado por las filas eliminadas, combine los archivos base y los archivos delta una vez que haya completado sus operaciones de eliminación.

Ejemplos

Ejemplo 1: Eliminar filas de una tabla transaccional sin particiones

Cree una tabla transaccional, inserte filas y luego elimine todas aquellas donde id = 2.

-- Create a Transactional table.
CREATE TABLE IF NOT EXISTS acid_delete (id BIGINT) TBLPROPERTIES ("transactional"="true");

-- Insert data.
INSERT OVERWRITE TABLE acid_delete VALUES (1), (2), (3), (2);

-- View the inserted data.
SELECT * FROM acid_delete;

+------------+
| id         |
+------------+
| 1          |
| 2          |
| 3          |
| 2          |
+------------+

-- Delete all rows where id is 2.
-- On the MaxCompute client (odpscmd), confirm the operation by entering yes or no when prompted.
DELETE FROM acid_delete WHERE id = 2;

-- The following command is equivalent: it uses a table alias in the WHERE clause.
DELETE FROM acid_delete ad WHERE ad.id = 2;

-- View the result.
SELECT * FROM acid_delete;

+------------+
| id         |
+------------+
| 1          |
| 3          |
+------------+

Ejemplo 2: Eliminar filas de una tabla transaccional con particiones

Cree una tabla transaccional con particiones, inserte filas en dos particiones y luego elimine las filas donde la partición sea 2019 y id = 2.

-- Create a partitioned Transactional table.
CREATE TABLE IF NOT EXISTS acid_delete_pt (id BIGINT) PARTITIONED BY (ds STRING) TBLPROPERTIES ("transactional"="true");

-- Add partitions.
ALTER TABLE acid_delete_pt ADD IF NOT EXISTS PARTITION (ds = '2019');
ALTER TABLE acid_delete_pt ADD IF NOT EXISTS PARTITION (ds = '2018');

-- Insert data.
INSERT OVERWRITE TABLE acid_delete_pt PARTITION (ds = '2019') VALUES (1), (2), (3);
INSERT OVERWRITE TABLE acid_delete_pt PARTITION (ds = '2018') VALUES (1), (2), (3);

-- View the inserted data.
SELECT * FROM acid_delete_pt;

+------------+------------+
| id         | ds         |
+------------+------------+
| 1          | 2018       |
| 2          | 2018       |
| 3          | 2018       |
| 1          | 2019       |
| 2          | 2019       |
| 3          | 2019       |
+------------+------------+

-- Delete rows in the 2019 partition where id is 2.
-- On the MaxCompute client (odpscmd), confirm the operation by entering yes or no when prompted.
DELETE FROM acid_delete_pt WHERE ds = '2019' AND id = 2;

-- View the result.
SELECT * FROM acid_delete_pt;

+------------+------------+
| id         | ds         |
+------------+------------+
| 1          | 2018       |
| 2          | 2018       |
| 3          | 2018       |
| 1          | 2019       |
| 3          | 2019       |
+------------+------------+

Ejemplo 3: Eliminar filas mediante una unión con otra tabla

Cree una tabla transaccional de destino y una tabla de referencia; después, elimine de la tabla de destino las filas cuyo id no aparezca en la tabla de referencia.

-- Create a target Transactional table and a reference table.
CREATE TABLE IF NOT EXISTS acid_delete_t (id INT, value1 INT, value2 INT) TBLPROPERTIES ("transactional"="true");
CREATE TABLE IF NOT EXISTS acid_delete_s (id INT, value1 INT, value2 INT);

-- Insert data.
INSERT OVERWRITE TABLE acid_delete_t VALUES (2, 20, 21), (3, 30, 31), (4, 40, 41);
INSERT OVERWRITE TABLE acid_delete_s VALUES (1, 100, 101), (2, 200, 201), (3, 300, 301);

-- Delete rows from acid_delete_t where the id does not match any id in acid_delete_s.
-- On the MaxCompute client (odpscmd), confirm the operation by entering yes or no when prompted.
DELETE FROM acid_delete_t WHERE NOT EXISTS (SELECT * FROM acid_delete_s WHERE acid_delete_t.id = acid_delete_s.id);

-- The following command is equivalent: it uses table aliases.
DELETE FROM acid_delete_t a WHERE NOT EXISTS (SELECT * FROM acid_delete_s b WHERE a.id = b.id);

-- View the result. Only rows with id 2 and 3 remain.
SELECT * FROM acid_delete_t;

+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 20         | 21         |
| 3          | 30         | 31         |
+------------+------------+------------+

Ejemplo 4: Eliminar filas de una Delta Table

Cree una Delta Table, inserte filas y luego elimine aquellas donde val = 2 dentro de una partición específica.

-- Create a Delta Table with a primary key column.
CREATE TABLE IF NOT EXISTS mf_dt (
  pk BIGINT NOT NULL PRIMARY KEY,
  val BIGINT NOT NULL
) PARTITIONED BY (dd STRING, hh STRING)
TBLPROPERTIES ("transactional"="true");

-- Insert data.
INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='02') VALUES (1, 1), (2, 2), (3, 3);

-- View the inserted data.
SELECT * FROM mf_dt WHERE dd='01' AND hh='02';

+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 02 |
| 3          | 3          | 01 | 02 |
| 2          | 2          | 01 | 02 |
+------------+------------+----+----+

-- Delete rows where val is 2 in partition dd='01', hh='02'.
DELETE FROM mf_dt WHERE val = 2 AND dd='01' AND hh='02';

-- View the result. Only rows where val is 1 and 3 remain.
SELECT * FROM mf_dt WHERE dd='01' AND hh='02';

+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 02 |
| 3          | 3          | 01 | 02 |
+------------+------------+----+----+

Pasos siguientes

UPDATE: Actualice los valores de columna en filas que cumplan una condición dentro de tablas transaccionales con o sin particiones.
ALTER TABLE: Combine archivos base y archivos delta para recuperar espacio de almacenamiento tras las operaciones de eliminación.