MaxCompute:UPDATE | DELETE

Pré-requisitos

Antes de executar operações DELETE ou UPDATE, é necessário ter as permissões Select e Update na tabela Transactional ou Delta Table de destino. Para obter mais informações sobre autorização, consulte Permissões do MaxCompute.

Introdução aos recursos

Assim como em bancos de dados tradicionais, os recursos DELETE e UPDATE do MaxCompute permitem excluir ou atualizar linhas específicas de uma tabela.

Se vários jobs forem executados simultaneamente na mesma tabela de destino, poderão ocorrer conflitos entre eles. Para mais informações, consulte Semântica ACID.

Casos de uso

Os recursos DELETE e UPDATE são adequados para exclusões ou atualizações aleatórias e de baixa frequência de uma pequena quantidade de dados em uma tabela ou partição. Por exemplo, é possível realizar periodicamente exclusões ou atualizações em lote em menos de 5% das linhas de uma tabela ou partição diariamente (T+1).

Esses recursos DELETE e UPDATE não são indicados para atualizações de alta frequência, exclusões frequentes ou gravações em tempo real na tabela de destino.

Limitações

Precauções

Excluir dados

A operação DELETE remove uma ou mais linhas que atendem a condições especificadas de uma tabela Transactional ou Delta Table.

• Sintaxe

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

• Parâmetros

  Parâmetro	Obrigatório	Descrição
  table_name	Sim	Nome da tabela Transactional ou Delta Table na qual você deseja executar a operação DELETE.
  alias	Não	Alias da tabela.
  where_condition	Não	Cláusula WHERE para filtrar dados que atendem à condição. Para obter mais informações sobre a cláusula WHERE, consulte Sintaxe SELECT. Se nenhuma cláusula WHERE for incluída, todos os dados da tabela serão excluídos.

• Exemplos

  • Exemplo 1: Crie uma tabela não particionada chamada acid_delete, importe dados e execute a operação DELETE para remover linhas que atendam a uma condição específica. Os comandos de exemplo são:

    -- Create a Transactional table named acid_delete.
    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 rows where id is 2. If you run this command on the MaxCompute client (odpscmd), you must enter yes or no to confirm.
    DELETE FROM acid_delete WHERE id = 2;
    -- The following command is equivalent to the one above.
    DELETE FROM acid_delete ad WHERE ad.id = 2;
    
    -- View the result. The table now contains only data for 1 and 3.
    SELECT * FROM acid_delete;
    
    +------------+
    | id         |
    +------------+
    | 1          |
    | 3          |
    +------------+

  • Exemplo 2: Crie uma tabela particionada chamada acid_delete_pt, importe dados e execute a operação DELETE para remover linhas que atendam a uma condição específica. Os comandos de exemplo são:

    -- Create a Transactional table named acid_delete_pt.  
    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 data where the partition is 2019 and id is 2. If you run this command on the MaxCompute client (odpscmd), you must enter yes or no to confirm.
    DELETE FROM acid_delete_pt WHERE ds = '2019' AND id = 2;
    
    -- View the result. The data where the partition is 2019 and id is 2 has been deleted.
    SELECT * FROM acid_delete_pt;
    
    +------------+------------+
    | id         | ds         |
    +------------+------------+
    | 1          | 2018       |
    | 2          | 2018       |
    | 3          | 2018       |
    | 1          | 2019       |
    | 3          | 2019       |
    +------------+------------+

  • Exemplo 3: Crie uma tabela de destino chamada acid_delete_t e uma tabela associada chamada acid_delete_s. Em seguida, exclua linhas que atendam a uma condição especificada por meio de uma operação de junção. Os comandos de exemplo são:

    -- Create a target Transactional table named acid_delete_t and an associated table named acid_delete_s. 
    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 the acid_delete_t table where the id does not match an id in the acid_delete_s table. If you run this command on the MaxCompute client (odpscmd), you must enter yes or no to confirm.
    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 to the one above.
    DELETE FROM acid_delete_t a WHERE NOT EXISTS (SELECT * FROM acid_delete_s b WHERE a.id = b.id);
    
    -- View the result. The table now contains only data for id 2 and 3.
    SELECT * FROM acid_delete_t;
    
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 2          | 20         | 21         |
    | 3          | 30         | 31         |
    +------------+------------+------------+

  • Exemplo 4: Crie uma Delta Table chamada mf_dt, importe dados e execute a operação DELETE para excluir linhas que atendam a uma condição especificada. Os comandos de exemplo são:

    -- Create a target Delta Table named mf_dt. 
    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';
    
    -- The following result is returned:
    +------------+------------+----+----+
    | pk         | val        | dd | hh |
    +------------+------------+----+----+
    | 1          | 1          | 01 | 02 |
    | 3          | 3          | 01 | 02 |
    | 2          | 2          | 01 | 02 |
    +------------+------------+----+----+
    
    -- Delete data where the partition is 01 and 02, and val is 2.
    DELETE FROM mf_dt WHERE val = 2  AND dd='01' AND hh='02';
    
    -- View the result. The table now contains only data where val is 1 and 3.
    SELECT * FROM mf_dt WHERE dd='01' AND hh='02';
    
    -- The following result is returned:
    +------------+------------+----+----+
    | pk         | val        | dd | hh |
    +------------+------------+----+----+
    | 1          | 1          | 01 | 02 |
    | 3          | 3          | 01 | 02 |
    +------------+------------+----+----+

Limpar dados de colunas

Use o comando CLEAR COLUMN para limpar dados de colunas em uma tabela padrão. Essa operação exclui dados que não são mais utilizados do disco e define os valores da coluna como NULL, ajudando a reduzir os custos de armazenamento.

• Sintaxe

  ALTER TABLE <table_name> 
    [PARTITION ( <pt_spec>[, <pt_spec>....] )] 
    CLEAR COLUMN column1[, column2, column3, ...]
    [WITHOUT TOUCH];

• Parâmetros

  Parâmetro	Descrição
  table_name	Nome da tabela cujos dados de coluna você deseja limpar.
  column1 , column2 ...	Nomes das colunas cujos dados você deseja limpar.
  PARTITION	Especifica a partição. Se não for especificada, a operação se aplica a todas as partições.
  pt_spec	Descrição da partição, no formato (partition_col1 = PARTITION_col_value1, PARTITION_col2 = PARTITION_col_value2, ...).
  WITHOUT TOUCH	Se especificado, o LastDataModifiedTime não é atualizado. Se não for especificado, o LastDataModifiedTime é atualizado.

  Nota

  Atualmente, WITHOUT TOUCH é especificado por padrão. Em uma versão futura, haverá suporte para o comportamento de limpar dados de coluna sem especificar WITHOUT TOUCH. Isso significa que, se WITHOUT TOUCH não for especificado, o LastDataModifiedTime será atualizado.

• Limites

  • Não é possível realizar uma operação de limpeza de coluna em colunas com restrição NOT NULL. Você pode remover manualmente a restrição NOT NULL:

    ALTER TABLE <table_name> change COLUMN <old_col_name> NULL;

  • A limpeza de dados de coluna não é suportada em tabelas ACID.

  • A limpeza de dados de coluna não é suportada em tabelas clusterizadas.

  • Não há suporte para limpeza de dados de coluna dentro de tipos aninhados.

  • Não há suporte para limpar todas as colunas. O comando DROP TABLE atinge o mesmo efeito com melhor desempenho.

• Precauções

  • A operação CLEAR COLUMN não altera a propriedade Archive da tabela.

  • A operação CLEAR COLUMN em uma coluna de tipo aninhado pode falhar.

    Essa falha ocorre se você executar uma operação CLEAR COLUMN em uma tabela que contém tipos aninhados colunares enquanto o armazenamento colunar para tipos aninhados está desabilitado.

  • O comando CLEAR COLUMN depende do Storage Service online. A tarefa pode ficar lenta se precisar entrar na fila durante períodos de alto volume de jobs.

  • A operação CLEAR COLUMN requer recursos de computação para ler e gravar dados. Para usuários de assinatura, isso consome recursos de computação. Para usuários de pagamento conforme o uso, incorre nas mesmas taxas de um job SQL. (Este recurso está atualmente em preview por convite e é temporariamente gratuito.)

• Exemplos

  • -- Create a table. 
    CREATE TABLE IF  NOT EXISTS mf_cc(key STRING, 
         value STRING, 
         a1 BIGINT , 
         a2 BIGINT , 
         a3 BIGINT , 
         a4 BIGINT)  
         PARTITIONED BY(ds STRING, hr STRING);
                     
    -- Add a partition.
    ALTER TABLE mf_cc ADD IF  NOT EXISTS PARTITION (ds='20230509', hr='1641');
    
    -- Insert data.
    INSERT INTO mf_cc PARTITION (ds='20230509', hr='1641') 
         VALUES("key","value",1,22,3,4);
         
    -- Query data.     
    SELECT * FROM mf_cc WHERE ds='20230509' AND  hr='1641';
    -- The following result is returned:
    +-----+-------+------------+------------+--------+------+---------+-----+
    | key | value | a1         | a2         | a3     | a4   | ds      | hr  |
    +-----+-------+------------+------------+--------+------+---------+-----+
    | key | value | 1          | 22         | 3      | 4    | 20230509| 1641|
    +-----+-------+------------+------------+--------+------+---------+-----+
    -- Clear column data.
    ALTER TABLE mf_cc PARTITION(ds='20230509', hr='1641') 
                      CLEAR COLUMN key,a1 
        WITHOUT TOUCH;
    -- Query data.     
    SELECT * FROM mf_cc WHERE ds='20230509' AND  hr='1641';
    -- The following result is returned. The data in the key and a1 columns has become null.
    +-----+-------+------------+------------+--------+------+---------+-----+
    | key | value | a1         | a2         | a3     | a4   | ds      | hr  |
    +-----+-------+------------+------------+--------+------+---------+-----+
    | null| value | null       | 22         | 3      | 4    | 20230509| 1641|
    +-----+-------+------------+------------+--------+------+---------+-----+

  • A figura a seguir mostra a alteração no tamanho total de armazenamento da tabela lineitem (no formato AliORC) à medida que cada coluna é limpa usando o comando CLEAR COLUMN. A tabela lineitem possui 16 colunas de vários tipos, incluindo BIGINT, DECIMAL, CHAR, DATE e VARCHAR.

    Como pode ser visto, depois que as 16 colunas da tabela são sequencialmente definidas como NULL pelo comando CLEAR COLUMN, o espaço total de armazenamento é reduzido em 99,97% (de 186.783.526 bytes iniciais para 236.715 bytes).

    Nota

    • A quantidade de espaço economizada pela operação CLEAR COLUMN depende do tipo de dados e dos valores reais armazenados na coluna. Por exemplo, limpar a coluna l_extendedprice, que é do tipo DECIMAL, economizou 24,2% do espaço (de 146.538.799 bytes para 111.138.117 bytes), o que é significativamente melhor que a média.

    • Quando todas as colunas são definidas como NULL, o tamanho da tabela é de 236.715 bytes, e não zero. Isso ocorre porque a estrutura de arquivos da tabela ainda existe. Campos NULL ocupam uma pequena quantidade de espaço de armazenamento, e o sistema também precisa reter as informações de rodapé do arquivo.

Atualizar dados

A operação UPDATE atualiza os valores em uma ou mais colunas para linhas em uma tabela Transactional ou Delta Table.

• Sintaxe

  -- Method 1
  UPDATE <table_name> [[AS] alias] SET <col1_name> = <value1> [, <col2_name> = <value2> ...] [WHERE <where_condition>];
  
  -- Method 2
  UPDATE <table_name> [[AS] alias] SET (<col1_name> [, <col2_name> ...]) = (<value1> [, <value2> ...]) [WHERE <where_condition>];
  
  -- Method 3
  UPDATE <table_name> [[AS] alias]
      SET <col1_name> = <value1> [, <col2_name> = <value2>, ...]
      [FROM <additional_tables>]
      [WHERE <where_condition>];

• Parâmetros

  • table_name: Obrigatório. O nome da tabela Transactional ou Delta Table para a operação UPDATE.

  • alias: Opcional. O alias da tabela.

  • col1_name, col2_name: Obrigatório. Os nomes das colunas a serem modificadas. Você deve atualizar pelo menos uma coluna.

  • value1, value2: Obrigatório. Os novos valores para as colunas. Você deve atualizar pelo menos um valor de coluna.

  • where_condition: Opcional. Uma cláusula WHERE para filtrar dados. Para obter mais informações sobre a cláusula WHERE, consulte Sintaxe SELECT. Se você não incluir uma cláusula WHERE, todos os dados da tabela serão atualizados.

  • additional_tables: Opcional. Uma cláusula FROM.

    A instrução UPDATE suporta a cláusula FROM, o que pode simplificar a instrução UPDATE. A tabela a seguir compara uma instrução UPDATE que usa uma cláusula FROM com uma que não usa.

    Cenário	Código de exemplo
    Sem cláusula FROM	UPDATE target SET v = (SELECT MIN(v) FROM src GROUP BY k WHERE target.k = src.key) WHERE target.k IN (SELECT k FROM src);
    Com cláusula FROM	UPDATE target SET v = b.v FROM (SELECT k, MIN(v) AS v FROM src GROUP BY k) b WHERE target.k = b.k;

    Conforme mostrado nos exemplos de código:

    • Ao atualizar uma linha na tabela de destino usando várias linhas da tabela de origem, você deve usar uma operação de agregação para garantir que os dados de origem sejam únicos, pois o sistema não sabe qual linha de origem usar. A sintaxe que não usa uma cláusula FROM é menos concisa. A sintaxe com uma cláusula FROM é mais simples e fácil de entender.

    • Ao realizar uma atualização com junção, se você quiser atualizar apenas a interseção dos dados, a sintaxe que não usa uma cláusula FROM exige uma condição WHERE extra e é menos concisa do que a sintaxe que usa uma cláusula FROM.

• Exemplos

  • Exemplo 1: Crie uma tabela não particionada chamada acid_update, importe dados e execute a operação UPDATE para atualizar as colunas das linhas que atendem a uma condição especificada. Os comandos de exemplo são:

    -- Create a Transactional table named acid_update.  
    CREATE TABLE IF  NOT EXISTS acid_update(id BIGINT) tblproperties ("transactional"="true");
    
    -- Insert data.
    INSERT OVERWRITE TABLE acid_update VALUES(1),(2),(3),(2);
    
    -- View the inserted data.
    SELECT * FROM acid_update; 
    
    -- The following result is returned:
    +------------+
    | id         |
    +------------+
    | 1          |
    | 2          |
    | 3          |
    | 2          |
    +------------+
    
    -- Update the id value to 4 for all rows where id is 2.
    UPDATE acid_update SET id = 4 WHERE id = 2; 
    
    -- View the update result. 2 has been updated to 4.
    SELECT * FROM acid_update; 
    
    -- The following result is returned:
    +------------+
    | id         |
    +------------+
    | 1          |
    | 3          |
    | 4          |
    | 4          |
    +------------+

  • Exemplo 2: Crie uma tabela particionada chamada acid_update, importe dados e execute a operação UPDATE para atualizar as colunas das linhas que atendem a uma condição especificada. Os comandos de exemplo são:

    -- Create a Transactional table named acid_update_pt. 
    CREATE TABLE IF  NOT EXISTS acid_update_pt(id BIGINT) PARTITIONED BY(ds STRING) tblproperties ("transactional"="true");
    
    -- Add a partition.
    ALTER TABLE acid_update_pt ADD IF  NOT EXISTS PARTITION (ds= '2019');
    
    -- Insert data.
    INSERT OVERWRITE TABLE acid_update_pt PARTITION (ds='2019') VALUES(1),(2),(3);
    
    -- View the inserted data.
    SELECT * FROM acid_update_pt WHERE ds = '2019'; 
    
    -- The following result is returned:
    +------------+------------+
    | id         | ds         |
    +------------+------------+
    | 1          | 2019       |
    | 2          | 2019       |
    | 3          | 2019       |
    +------------+------------+
    
    -- Update a column in a specified row. Set the id value to 4 for all rows where the partition is 2019 and id is 2.
    UPDATE acid_update_pt SET id = 4 WHERE ds = '2019' AND id = 2; 
    
    -- View the update result. 2 has been updated to 4.
    SELECT * FROM acid_update_pt WHERE ds = '2019';
    
    -- The following result is returned:
    +------------+------------+
    | id         | ds         |
    +------------+------------+
    | 4          | 2019       |
    | 1          | 2019       |
    | 3          | 2019       |
    +------------+------------+

  • Exemplo 3: Crie uma tabela de destino chamada acid_update_t e uma tabela associada chamada acid_update_s para atualizar vários valores de coluna ao mesmo tempo. Os comandos de exemplo são:

    -- Create a target Transactional table to be updated, named acid_update_t, and an associated table named acid_update_s.
    CREATE TABLE IF  NOT EXISTS acid_update_t(id INT,value1 INT,value2 INT) tblproperties ("transactional"="true");
    CREATE TABLE IF  NOT EXISTS acid_update_s(id INT,value1 INT,value2 INT); 
    
    -- Insert data.
    INSERT OVERWRITE TABLE acid_update_t VALUES(2,20,21),(3,30,31),(4,40,41);
    INSERT OVERWRITE TABLE acid_update_s VALUES(1,100,101),(2,200,201),(3,300,301);
    
    -- Method 1: Update with constants.
    UPDATE acid_update_t SET (value1, value2) = (60,61);
    -- Query the result data in the target table for Method 1.
    SELECT * FROM acid_update_t;
    
    -- The following result is returned:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 2          | 60         | 61         |
    | 3          | 60         | 61         |
    | 4          | 60         | 61         |
    +------------+------------+------------+
    
    -- Method 2: Join update. The rule is a left join from acid_update_t to acid_update_s.
    UPDATE acid_update_t SET (value1, value2) = (SELECT value1, value2 FROM acid_update_s WHERE acid_update_t.id = acid_update_s.id);
    -- Query the result data in the target table for Method 2.
    SELECT * FROM acid_update_t;
    
    -- The following result is returned:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 2          | 200        | 201        |
    | 3          | 300        | 301        |
    | 4          | NULL       | NULL       |
    +------------+------------+------------+
    
    -- Method 3 (update based on the result of Method 2): Join update. The rule is to add a filter condition to update only the intersection.
    UPDATE acid_update_t SET (value1, value2) = (SELECT value1, value2 FROM acid_update_s WHERE acid_update_t.id = acid_update_s.id) WHERE acid_update_t.id IN (SELECT id FROM acid_update_s);
    -- Query the result data in the target table for Method 3.
    SELECT * FROM acid_update_t;
    
    -- The following result is returned:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 2          | 200        | 201        |
    | 3          | 300        | 301        |
    | 4          | NULL       | NULL       |
    +------------+------------+------------+
    
    -- Method 4 (update based on the result of Method 3): Join update with aggregate results.
    UPDATE acid_update_t SET (id, value1, value2) = (SELECT id, MAX(value1),MAX(value2) FROM acid_update_s WHERE acid_update_t.id = acid_update_s.id GROUP BY acid_update_s.id) WHERE acid_update_t.id IN (SELECT id FROM acid_update_s);
    -- Query the result data in the target table for Method 4.
    SELECT * FROM acid_update_t;
    
    -- The following result is returned:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 2          | 200        | 201        |
    | 3          | 300        | 301        |
    | 4          | NULL       | NULL       |
    +------------+------------+------------+

  • Exemplo 4: Uma consulta de junção simples envolvendo duas tabelas. Os comandos de exemplo são:

    -- Create a target table for update, acid_update_t, and an associated table, acid_update_s. 
    CREATE TABLE IF  NOT EXISTS acid_update_t(id BIGINT,value1 BIGINT,value2 BIGINT) tblproperties ("transactional"="true");
    CREATE TABLE IF  NOT EXISTS acid_update_s(id BIGINT,value1 BIGINT,value2 BIGINT);
    
    -- Insert data.
    INSERT OVERWRITE TABLE acid_update_t VALUES(2,20,21),(3,30,31),(4,40,41);
    INSERT OVERWRITE TABLE acid_update_s VALUES(1,100,101),(2,200,201),(3,300,301);
    
    -- Query data from the acid_update_t table.
    SELECT * FROM acid_update_t;
    -- The following result is returned:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 2          | 20         | 21         |
    | 3          | 30         | 31         |
    | 4          | 40         | 41         |
    +------------+------------+------------+
    
    -- Query data from the acid_update_s table.
    SELECT * FROM acid_update_s;
    -- The following result is returned:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 1          | 100        | 101        |
    | 2          | 200        | 201        |
    | 3          | 300        | 301        |
    +------------+------------+------------+
    
    -- Join update. Add a filter condition to the target table to update only the intersection.
    UPDATE acid_update_t SET value1 = b.value1, value2 = b.value2
    FROM acid_update_s b WHERE acid_update_t.id = b.id;
    -- The following command is equivalent to the one above.
    UPDATE acid_update_t a SET a.value1 = b.value1, a.value2 = b.value2 
    FROM acid_update_s b WHERE a.id = b.id;
    
    -- View the update result. 20 is updated to 200, 21 to 201, 30 to 300, and 31 to 301.
    SELECT * FROM acid_update_t;
    -- The following result is returned:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 4          | 40         | 41         |
    | 2          | 200        | 201        |
    | 3          | 300        | 301        |
    +------------+------------+------------+                          

  • Exemplo 5: Uma consulta de junção complexa envolvendo várias tabelas. Os comandos de exemplo são:

    -- Create a target table for update, acid_update_t, and an associated table, acid_update_s. 
    CREATE TABLE IF  NOT EXISTS acid_update_t(id BIGINT,value1 BIGINT,value2 BIGINT) tblproperties ("transactional"="true");
    CREATE TABLE IF  NOT EXISTS acid_update_s(id BIGINT,value1 BIGINT,value2 BIGINT);
    CREATE TABLE IF  NOT EXISTS acid_update_m(id BIGINT,value1 BIGINT,value2 BIGINT);
    
    -- Insert data.
    INSERT OVERWRITE TABLE acid_update_t VALUES(2,20,21),(3,30,31),(4,40,41),(5,50,51);
    INSERT OVERWRITE TABLE acid_update_s VALUES (1,100,101),(2,200,201),(3,300,301),(4,400,401),(5,500,501);
    INSERT OVERWRITE TABLE acid_update_m VALUES(3,30,101),(4,400,201),(5,300,301);
    
    -- Query data from the acid_update_t table.
    SELECT * FROM acid_update_t;
    -- The following result is returned:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 2          | 20         | 21         |
    | 3          | 30         | 31         |
    | 4          | 40         | 41         |
    | 5          | 50         | 51         |
    +------------+------------+------------+
    
    -- Query data from the acid_update_s table.
    SELECT * FROM acid_update_s;
    -- The following result is returned:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 1          | 100        | 101        |
    | 2          | 200        | 201        |
    | 3          | 300        | 301        |
    | 4          | 400        | 401        |
    | 5          | 500        | 501        |
    +------------+------------+------------+
    
    -- Query data from the acid_update_m table.
    SELECT * FROM acid_update_m;
    -- The following result is returned:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 3          | 30         | 101        |
    | 4          | 400        | 201        |
    | 5          | 300        | 301        |
    +------------+------------+------------+
    
    -- Join update, and filter both the source and target tables in the WHERE clause.
    UPDATE acid_update_t 
    SET value1 = acid_update_s.value1, value2 = acid_update_s.value2 
    FROM acid_update_s 
    WHERE acid_update_t.id = acid_update_s.id 
    AND acid_update_s.id > 2 
    AND acid_update_t.value1 NOT IN 
    (SELECT value1 FROM acid_update_m WHERE id = acid_update_t.id) 
    AND acid_update_s.value1 NOT IN 
    (SELECT value1 FROM acid_update_m WHERE id = acid_update_s.id);
    
    -- View the update result. Only the data in the acid_update_t table with id 5 meets the condition. The corresponding value1 is updated to 500, and value2 is updated to 501.
    SELECT * FROM acid_update_t;
    -- The following result is returned:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 5          | 500        | 501        |
    | 2          | 20         | 21         |
    | 3          | 30         | 31         |
    | 4          | 40         | 41         |
    +------------+------------+------------+

  • Exemplo 6: O comando a seguir é um exemplo de como criar uma Delta Table chamada mf_dt, importar dados e executar uma operação UPDATE para excluir linhas que atendem a uma condição especificada:

    -- Create a target Delta Table named mf_dt.
    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';
    -- The following result is returned:
    +------------+------------+----+----+
    | pk         | val        | dd | hh |
    +------------+------------+----+----+
    | 1          | 1          | 01 | 02 |
    | 3          | 3          | 01 | 02 |
    | 2          | 2          | 01 | 02 |
    +------------+------------+----+----+
    
    -- Update a column in a specified row. Set the val value to 30 for all rows where the partition is 01 and 02, and pk is 3.
    -- Method 1
    UPDATE mf_dt SET val = 30 WHERE pk = 3 AND dd='01' AND hh='02';
    
    -- Method 2
    UPDATE mf_dt SET val = delta.val 
                  FROM (SELECT pk, val FROM VALUES (3, 30) t (pk, val)) delta 
                  WHERE delta.pk = mf_dt.pk AND mf_dt.dd='01' AND mf_dt.hh='02';
    
    -- View the update result.
    SELECT * FROM mf_dt WHERE dd='01' AND hh='02';
    -- The following result is returned. The val value for the row with pk=3 is updated to 30.
    +------------+------------+----+----+
    | pk         | val        | dd | hh |
    +------------+------------+----+----+
    | 1          | 1          | 01 | 02 |
    | 3          | 30         | 01 | 02 |
    | 2          | 2          | 01 | 02 |
    +------------+------------+----+----+

Mesclar arquivos de tabela transacional

O armazenamento físico subjacente de uma tabela Transactional consiste em arquivos base e arquivos Delta, que não são diretamente legíveis. Quando você executa uma operação UPDATE ou DELETE em uma tabela Transactional, os arquivos base não são modificados. Em vez disso, arquivos Delta são anexados. Isso significa que, quanto mais atualizações ou exclusões você realizar, mais armazenamento a tabela ocupará. O acúmulo de muitos arquivos Delta pode aumentar o armazenamento e os custos de consultas subsequentes.

Executar várias operações UPDATE ou DELETE na mesma tabela ou partição gera muitos arquivos Delta. Quando o sistema lê dados, ele precisa carregar esses arquivos Delta para determinar quais linhas foram atualizadas ou excluídas. Muitos arquivos Delta podem degradar o desempenho de leitura de dados. Nesse caso, você pode mesclar os arquivos base e os arquivos Delta para reduzir o armazenamento e melhorar o desempenho de leitura de dados.

• Sintaxe

  ALTER TABLE <table_name> [PARTITION (<partition_key> = '<partition_value>' [, ...])] compact {minor|major};

• Parâmetros

  Parâmetro	Obrigatório	Descrição
  table_name	Sim	Nome da tabela Transactional cujos arquivos você deseja mesclar.
  partition_key	Não	Se a tabela Transactional for uma tabela particionada, especifique o nome da coluna da chave de partição.
  partition_value	Não	Se a tabela Transactional for uma tabela particionada, especifique o valor para a coluna da chave de partição.
  major|minor	Sim	Você deve selecionar um. As diferenças são: minor: Mescla apenas os arquivos base e todos os seus arquivos Delta subjacentes, eliminando os arquivos Delta. major: Não apenas mescla os arquivos base e todos os seus arquivos Delta subjacentes para eliminar os arquivos Delta, mas também mescla pequenos arquivos dentro dos arquivos base correspondentes da tabela. Se um arquivo base for pequeno (menos de 32 MB) ou se existirem arquivos Delta, isso equivale a executar uma operação INSERT OVERWRITE na tabela novamente. No entanto, se o arquivo base for grande o suficiente (maior ou igual a 32 MB) e não existirem arquivos Delta, ele não será reescrito.

• Precauções

  Pequenos arquivos mesclados pela operação compact são excluídos após um dia. Se você usar o recurso de backup local para restaurar um histórico que depende desses pequenos arquivos, a recuperação falhará porque os arquivos estarão ausentes.

• Exemplos

  • Exemplo 1: Mescle arquivos para a tabela Transactional acid_delete. O comando de exemplo é:

    ALTER TABLE acid_delete compact minor; 

    O seguinte resultado é retornado:

    Summary:
    Nothing found to merge, set odps.merge.cross.paths=true if cross path merge is permitted.
    OK

  • Exemplo 2: Mescle arquivos para a tabela Transactional acid_update_pt. O comando de exemplo é:

    ALTER TABLE acid_update_pt PARTITION (ds = '2019') compact major; 

    O seguinte resultado é retornado:

    Summary:
    table name: acid_update_pt /ds=2019  instance count: 2  run time: 6
      before merge, file count:        8  file size: 2613  file physical size: 7839
       after merge, file count:        2  file size: 679  file physical size: 2037
    
    OK

Perguntas frequentes

• Problema 1:

  • Descrição do problema: Ao executar uma instrução UPDATE, o erro ODPS-0010000:System internal error - fuxi job failed, caused by: Data Set should contain exactly one row é relatado.

  • Causa: As linhas a serem atualizadas não têm uma correspondência um para um com os dados no resultado da subconsulta. O sistema não consegue determinar qual linha atualizar. O comando de exemplo é:

    UPDATE store SET (s_county, s_manager) = (SELECT d_country, d_manager FROM store_delta sd WHERE sd.s_store_sk = store.s_store_sk) WHERE s_store_sk IN (SELECT s_store_sk FROM store_delta);

    A subconsulta SELECT d_country, d_manager FROM store_delta sd WHERE sd.s_store_sk = store.s_store_sk é usada para juntar com store_delta, e os dados de store_delta são usados para atualizar store. Suponha que a coluna s_store_sk na tabela store contenha três linhas de dados: [1, 2, 3]. Se a coluna s_store_sk na tabela store_delta tiver duas linhas de dados, [1, 1], não existirá uma correspondência um para um e a execução falhará.

  • Solução: Garanta que as linhas a serem atualizadas tenham uma correspondência um para um com os dados no resultado da subconsulta.

• Problema 2:

  • Descrição do problema: Ao executar o comando compact no DataWorks DataStudio, o erro ODPS-0130161:[1,39] Parse exception - invalid token 'minor', expect one of 'StringLiteral','DoubleQuoteStringLiteral' é relatado.

  • Causa: A versão do cliente MaxCompute no grupo de recursos exclusivo do DataWorks não oferece suporte ao comando compact.

  • Solução: Entre em contato com a equipe de suporte técnico através do grupo DingTalk do DataWorks para atualizar a versão do cliente MaxCompute no grupo de recursos exclusivo.