How to use DELETE to delete rows from a table - MaxCompute: Central de Documentação do Alibaba Cloud

Pré-requisitos

Antes de executar uma instrução DELETE ou UPDATE, verifique se você possui as permissões Select e Update na tabela transacional ou Delta Table de destino. Para obter detalhes, consulte Permissões do MaxCompute.

Limitações

As instruções DELETE e UPDATE funcionam apenas em tabelas transacionais e Delta Tables, estando sujeitas aos limites descritos em Parâmetros para Transaction Table e Delta Table.

A sintaxe UPDATE para Delta Tables não oferece suporte à modificação de colunas de chave primária (PK).

Sintaxe

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

Parâmetros

Parâmetro	Obrigatório	Descrição
`table_name`	Sim	Nome da tabela transacional ou Delta Table da qual as linhas serão excluídas.
`alias`	Não	Um alias para a tabela. Utilize um alias ao referenciar a tabela na subconsulta da cláusula `WHERE`.
`condition`	Não	Cláusula `WHERE` que identifica quais linhas devem ser excluídas. Omita esta cláusula para excluir todas as linhas da tabela. Para a sintaxe da cláusula `WHERE`, consulte Cláusula WHERE (WHERE_condition).

Observações de uso

Quando usar DELETE

A instrução DELETE gera arquivos delta em vez de reescrever os dados da tabela no local, portanto, não reduz o armazenamento imediatamente. Siga as orientações abaixo para escolher a operação adequada à sua carga de trabalho.

Cenário	Operação recomendada
Poucas linhas, exclusões infrequentes, leituras infrequentes	`DELETE` ou `UPDATE`. Após múltiplas operações, mesclar arquivos base e arquivos delta para recuperar espaço de armazenamento.
Mais de 5% das linhas, exclusões infrequentes, leituras frequentes	`INSERT OVERWRITE` ou `INSERT INTO`. Consulte Inserir ou sobrescrever dados.

Por exemplo, se um job exclui ou atualiza 10% das linhas de uma tabela dez vezes por dia, compare o custo total de varredura de dez execuções de DELETE com dez execuções de INSERT OVERWRITE. Para tabelas com muitas leituras, o INSERT OVERWRITE geralmente é mais eficiente.

Agrupe suas operações de exclusão

O MaxCompute executa cada instrução DELETE como um job em lote e cobra com base na quantidade de dados verificados. Evite padrões nos quais um script gera uma instrução por linha — o custo acumulado de varredura em muitas instruções pequenas é significativamente maior do que em uma única instrução que abrange todas as linhas afetadas.

Use uma subconsulta para atingir várias linhas em uma única instrução:

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

Reduzir o armazenamento após excluir dados

O comando DELETE grava arquivos delta sobre os arquivos base da tabela. Para recuperar o espaço liberado pelas linhas excluídas, mescle os arquivos base e os arquivos delta após concluir as operações de exclusão.

Exemplos

Exemplo 1: Excluir linhas de uma tabela transacional não particionada

Crie uma tabela transacional, insira linhas e, em seguida, exclua todas as linhas onde 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          |
+------------+

Exemplo 2: Excluir linhas de uma tabela transacional particionada

Crie uma tabela transacional particionada, insira linhas em duas partições e, em seguida, exclua as linhas onde a partição é 2019 e 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       |
+------------+------------+

Exemplo 3: Excluir linhas com base em uma junção com outra tabela

Crie uma tabela transacional de destino e uma tabela de referência e, em seguida, exclua as linhas da tabela de destino cujo id não apareça na tabela de referência.

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

Exemplo 4: Excluir linhas de uma Delta Table

Crie uma Delta Table, insira linhas e, em seguida, exclua as linhas onde val = 2 em uma partição 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 |
+------------+------------+----+----+

Próximos passos

UPDATE: Atualize valores de colunas em linhas que atendam a uma condição em tabelas transacionais particionadas ou não particionadas.
ALTER TABLE: Mescle arquivos base e arquivos delta para recuperar espaço de armazenamento após operações de exclusão.