Atualização e exclusão de dados no ClickHouse

Embora o ClickHouse seja voltado para cargas de trabalho analíticas de alto volume, em algumas situações é possível modificar ou excluir dados existentes. Essas operações são chamadas de "mutações" e são executadas com o comando ALTER TABLE.

Use o comando ALTER TABLE...UPDATE para atualizar linhas de uma tabela:

ALTER TABLE [<database>.]<table> UPDATE <column> = <expression> WHERE <filter_expr>

<expression> é o novo valor da coluna quando <filter_expr> é satisfeito. <expression> deve ter o mesmo tipo de dado da coluna ou poder ser convertido para esse tipo usando o operador CAST. <filter_expr> deve retornar um valor UInt8 (zero ou diferente de zero) para cada linha de dados. Várias instruções UPDATE <column> podem ser combinadas em um único comando ALTER TABLE, separadas por vírgulas.

Exemplos:

1. Uma mutação como esta permite substituir visitor_id por novos valores usando uma consulta em Dicionário:

   ALTER TABLE website.clicks
   UPDATE visitor_id = getDict('visitors', 'new_visitor_id', visitor_id)
   WHERE visit_date < '2022-01-01'

2. Modificar vários valores em um único comando pode ser mais eficiente do que executar vários comandos:

   ALTER TABLE website.clicks
   UPDATE url = substring(url, position(url, '://') + 3), visitor_id = new_visit_id
   WHERE visit_date < '2022-01-01'

3. As mutações podem ser executadas ON CLUSTER em tabelas com shards:

   ALTER TABLE clicks ON CLUSTER main_cluster
   UPDATE click_count = click_count / 2
   WHERE visitor_id ILIKE '%robot%'

Use o comando ALTER TABLE para excluir linhas:

ALTER TABLE [<database>.]<table> DELETE WHERE <filter_expr>

A <filter_expr> deve retornar um valor UInt8 para cada linha de dados.

Exemplos

1. Exclua quaisquer registros em que uma coluna esteja em um array de valores:

   ALTER TABLE website.clicks DELETE WHERE visitor_id in (253, 1002, 4277)

2. O que esta consulta altera?

   ALTER TABLE clicks ON CLUSTER main_cluster DELETE WHERE visit_date < '2022-01-02 15:00:00' AND page_id = '573'

Consulte a página da documentação da instrução DELETE para mais detalhes.

Outra opção para excluir linhas é usar o comando DELETE FROM, conhecido como exclusão leve. As linhas excluídas são marcadas como excluídas imediatamente e serão automaticamente filtradas de todas as consultas subsequentes, portanto você não precisa esperar pela mesclagem das partes nem usar a palavra-chave FINAL. A limpeza dos dados acontece de forma assíncrona em segundo plano.

DELETE FROM [db.]table [ON CLUSTER cluster] [WHERE expr]

Por exemplo, a consulta abaixo exclui todas as linhas da tabela hits em que a coluna Title contém o texto hello:

DELETE FROM hits WHERE Title LIKE '%hello%';

Algumas observações sobre exclusões leves:

• Esse recurso está disponível apenas para a família de mecanismos de tabela MergeTree.
• As exclusões leves são síncronas por padrão e aguardam que todas as réplicas processem a exclusão. Esse comportamento é controlado pela configuração lightweight_deletes_sync.