How to use DELETE to delete rows from a table - MaxCompute - Centre de documentation Alibaba Cloud

Prérequis

Avant d'exécuter une instruction DELETE ou UPDATE, assurez-vous de disposer des autorisations Select et Update sur la table transactionnelle ou la table Delta cible. Pour plus d'informations, consultez la rubrique MaxCompute permissions.

Limites

Les instructions DELETE et UPDATE fonctionnent uniquement avec les tables transactionnelles et les tables Delta, et sont soumises aux limites décrites dans la section Parameters for Transaction Table and Delta Table.

La syntaxe UPDATE pour les tables Delta ne prend pas en charge la modification des colonnes de clé primaire (PK).

Syntaxe

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

Paramètres

Paramètre	Obligatoire	Description
`table_name`	Oui	Nom de la table transactionnelle ou de la table Delta dont vous souhaitez supprimer les lignes.
`alias`	Non	Alias de la table. Utilisez un alias lorsque vous faites référence à la table dans la sous-requête de la clause `WHERE`.
`condition`	Non	Clause `WHERE` identifiant les lignes à supprimer. Omettez cette clause pour supprimer toutes les lignes de la table. Pour connaître la syntaxe de la clause `WHERE`, consultez la section WHERE clause (WHERE_condition).

Notes d'utilisation

Quand utiliser DELETE

L'instruction DELETE génère des fichiers delta au lieu de réécrire les données de la table sur place ; elle ne réduit donc pas immédiatement l'espace de stockage. Suivez les recommandations ci-dessous pour choisir l'opération adaptée à votre charge de travail.

Scénario	Opération recommandée
Petit nombre de lignes, suppressions peu fréquentes, lectures peu fréquentes	`DELETE` ou `UPDATE`. Après plusieurs opérations, fusionnez les fichiers de base et les fichiers delta pour récupérer l'espace de stockage.
Plus de 5 % des lignes, suppressions peu fréquentes, lectures fréquentes	`INSERT OVERWRITE` ou `INSERT INTO`. Consultez la rubrique Insert or overwrite data.

Par exemple, si un job supprime ou met à jour 10 % des lignes d'une table dix fois par jour, comparez le coût total d'analyse de dix exécutions DELETE avec celui de dix exécutions INSERT OVERWRITE. Pour les tables fortement sollicitées en lecture, l'instruction INSERT OVERWRITE est généralement plus efficace.

Regroupez vos opérations de suppression

MaxCompute exécute chaque instruction DELETE sous forme de job par lots et facture en fonction du volume de données analysées. Évitez les scripts qui génèrent une instruction par ligne : le coût cumulé de l'analyse pour de nombreuses petites instructions est nettement supérieur à celui d'une seule instruction couvrant toutes les lignes concernées.

Utilisez une sous-requête pour cibler plusieurs lignes en une seule instruction :

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

Réduisez l'espace de stockage après la suppression des données

L'instruction DELETE écrit des fichiers delta par-dessus les fichiers de base de la table. Pour récupérer l'espace libéré par les lignes supprimées, fusionnez les fichiers de base et les fichiers delta une fois vos opérations de suppression terminées.

Exemples

Exemple 1 : Supprimer des lignes d'une table transactionnelle non partitionnée

Créez une table transactionnelle, insérez des lignes, puis supprimez toutes les lignes où 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          |
+------------+

Exemple 2 : Supprimer des lignes d'une table transactionnelle partitionnée

Créez une table transactionnelle partitionnée, insérez des lignes dans deux partitions, puis supprimez les lignes où la partition est 2019 et 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       |
+------------+------------+

Exemple 3 : Supprimer des lignes sur la base d'une jointure avec une autre table

Créez une table transactionnelle cible et une table de référence, puis supprimez de la table cible les lignes dont l'id n'apparaît pas dans la table de référence.

-- 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         |
+------------+------------+------------+

Exemple 4 : Supprimer des lignes d'une table Delta

Créez une table Delta, insérez des lignes, puis supprimez les lignes où val = 2 dans une partition spécifique.

-- 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 |
+------------+------------+----+----+

Étapes suivantes

UPDATE : mettez à jour les valeurs des colonnes des lignes répondant à une condition dans des tables transactionnelles partitionnées ou non partitionnées.
ALTER TABLE : fusionnez les fichiers de base et les fichiers delta pour récupérer l'espace de stockage après les opérations de suppression.