ALTER TABLE ... MODIFY QUERY

Você pode modificar a consulta SELECT especificada quando uma visão materializada foi criada com a instrução ALTER TABLE ... MODIFY QUERY, sem interromper o processo de ingestão. Esse comando foi criado para alterar uma visão materializada criada com a cláusula TO [db.]name. Ele não altera a estrutura da tabela de armazenamento subjacente nem a definição das colunas da visão materializada; por isso, sua aplicação é muito limitada em visões materializadas criadas sem a cláusula TO [db.]name. Exemplo com a tabela TO

CREATE TABLE events (ts DateTime, event_type String)
ENGINE = MergeTree ORDER BY (event_type, ts);

CREATE TABLE events_by_day (ts DateTime, event_type String, events_cnt UInt64)
ENGINE = SummingMergeTree ORDER BY (event_type, ts);

CREATE MATERIALIZED VIEW mv TO events_by_day AS
SELECT toStartOfDay(ts) ts, event_type, count() events_cnt
FROM events
GROUP BY ts, event_type;

INSERT INTO events
SELECT DATE '2020-01-01' + interval number * 900 second,
       ['imp', 'click'][number%2+1]
FROM numbers(100);

SELECT ts, event_type, sum(events_cnt)
FROM events_by_day
GROUP BY ts, event_type
ORDER BY ts, event_type;

┌──────────────────ts─┬─event_type─┬─sum(events_cnt)─┐
│ 2020-01-01 00:00:00 │ click      │              48 │
│ 2020-01-01 00:00:00 │ imp        │              48 │
│ 2020-01-02 00:00:00 │ click      │               2 │
│ 2020-01-02 00:00:00 │ imp        │               2 │
└─────────────────────┴────────────┴─────────────────┘

-- Vamos adicionar a nova medida `cost`
-- e a nova dimensão `browser`.

ALTER TABLE events
  ADD COLUMN browser String,
  ADD COLUMN cost Float64;

-- As colunas não precisam corresponder em uma visão materializada e na tabela TO
-- (tabela de destino), portanto, o próximo alter não interrompe a inserção.

ALTER TABLE events_by_day
    ADD COLUMN cost Float64,
    ADD COLUMN browser String after event_type,
    MODIFY ORDER BY (event_type, ts, browser);

INSERT INTO events
SELECT Date '2020-01-02' + interval number * 900 second,
       ['imp', 'click'][number%2+1],
       ['firefox', 'safary', 'chrome'][number%3+1],
       10/(number+1)%33
FROM numbers(100);

-- As novas colunas `browser` e `cost` estão vazias porque ainda não alteramos a Visão Materializada.

SELECT ts, event_type, browser, sum(events_cnt) events_cnt, round(sum(cost),2) cost
FROM events_by_day
GROUP BY ts, event_type, browser
ORDER BY ts, event_type;

┌──────────────────ts─┬─event_type─┬─browser─┬─events_cnt─┬─cost─┐
│ 2020-01-01 00:00:00 │ click      │         │         48 │    0 │
│ 2020-01-01 00:00:00 │ imp        │         │         48 │    0 │
│ 2020-01-02 00:00:00 │ click      │         │         50 │    0 │
│ 2020-01-02 00:00:00 │ imp        │         │         50 │    0 │
│ 2020-01-03 00:00:00 │ click      │         │          2 │    0 │
│ 2020-01-03 00:00:00 │ imp        │         │          2 │    0 │
└─────────────────────┴────────────┴─────────┴────────────┴──────┘

ALTER TABLE mv MODIFY QUERY
  SELECT toStartOfDay(ts) ts, event_type, browser,
  count() events_cnt,
  sum(cost) cost
  FROM events
  GROUP BY ts, event_type, browser;

INSERT INTO events
SELECT Date '2020-01-03' + interval number * 900 second,
       ['imp', 'click'][number%2+1],
       ['firefox', 'safary', 'chrome'][number%3+1],
       10/(number+1)%33
FROM numbers(100);

SELECT ts, event_type, browser, sum(events_cnt) events_cnt, round(sum(cost),2) cost
FROM events_by_day
GROUP BY ts, event_type, browser
ORDER BY ts, event_type;

┌──────────────────ts─┬─event_type─┬─browser─┬─events_cnt─┬──cost─┐
│ 2020-01-01 00:00:00 │ click      │         │         48 │     0 │
│ 2020-01-01 00:00:00 │ imp        │         │         48 │     0 │
│ 2020-01-02 00:00:00 │ click      │         │         50 │     0 │
│ 2020-01-02 00:00:00 │ imp        │         │         50 │     0 │
│ 2020-01-03 00:00:00 │ click      │ firefox │         16 │  6.84 │
│ 2020-01-03 00:00:00 │ click      │         │          2 │     0 │
│ 2020-01-03 00:00:00 │ click      │ safary  │         16 │  9.82 │
│ 2020-01-03 00:00:00 │ click      │ chrome  │         16 │  5.63 │
│ 2020-01-03 00:00:00 │ imp        │         │          2 │     0 │
│ 2020-01-03 00:00:00 │ imp        │ firefox │         16 │ 15.14 │
│ 2020-01-03 00:00:00 │ imp        │ safary  │         16 │  6.14 │
│ 2020-01-03 00:00:00 │ imp        │ chrome  │         16 │  7.89 │
│ 2020-01-04 00:00:00 │ click      │ safary  │          1 │   0.1 │
│ 2020-01-04 00:00:00 │ click      │ firefox │          1 │   0.1 │
│ 2020-01-04 00:00:00 │ imp        │ firefox │          1 │   0.1 │
│ 2020-01-04 00:00:00 │ imp        │ chrome  │          1 │   0.1 │
└─────────────────────┴────────────┴─────────┴────────────┴───────┘

-- !!! Durante o `MODIFY ORDER BY`, a PRIMARY KEY foi introduzida implicitamente.

SHOW CREATE TABLE events_by_day FORMAT TSVRaw

CREATE TABLE test.events_by_day
(
    `ts` DateTime,
    `event_type` String,
    `browser` String,
    `events_cnt` UInt64,
    `cost` Float64
)
ENGINE = SummingMergeTree
PRIMARY KEY (event_type, ts)
ORDER BY (event_type, ts, browser)

-- !!! A definição das colunas não foi alterada, mas isso não importa, pois não estamos consultando
-- a MATERIALIZED VIEW, estamos consultando a tabela TO (de armazenamento).
-- A seção SELECT foi atualizada.

SHOW CREATE TABLE mv FORMAT TSVRaw;

CREATE MATERIALIZED VIEW test.mv TO test.events_by_day
(
    `ts` DateTime,
    `event_type` String,
    `events_cnt` UInt64
) AS
SELECT
    toStartOfDay(ts) AS ts,
    event_type,
    browser,
    count() AS events_cnt,
    sum(cost) AS cost
FROM test.events
GROUP BY
    ts,
    event_type,
    browser

Exemplo sem a tabela TO A aplicação é muito limitada, porque você só pode alterar a seção SELECT sem adicionar novas colunas.

CREATE TABLE src_table (`a` UInt32) ENGINE = MergeTree ORDER BY a;
CREATE MATERIALIZED VIEW mv (`a` UInt32) ENGINE = MergeTree ORDER BY a AS SELECT a FROM src_table;
INSERT INTO src_table (a) VALUES (1), (2);
SELECT * FROM mv;

┌─a─┐
│ 1 │
│ 2 │
└───┘

ALTER TABLE mv MODIFY QUERY SELECT a * 2 as a FROM src_table;
INSERT INTO src_table (a) VALUES (3), (4);
SELECT * FROM mv;

┌─a─┐
│ 6 │
│ 8 │
└───┘
┌─a─┐
│ 1 │
│ 2 │
└───┘

Privilégios necessários

ALTER TABLE ... MODIFY QUERY requer o privilégio ALTER VIEW MODIFY QUERY na VIEW. A nova consulta é executada com a SQL security da VIEW; por isso, a instrução também exige as concessões necessárias para criar uma VIEW com essa SQL security:

SQL SECURITY DEFINER com um definer que não seja o usuário atual: SET DEFINER nesse definer.
SQL SECURITY NONE: ALLOW SQL SECURITY NONE.

Quando a mesma instrução altera a SQL security com MODIFY SQL SECURITY, essas concessões são exigidas para a nova SQL security, e não para a antiga. Com ON CLUSTER, a instrução é autorizada no host em que é executada; portanto, a VIEW precisa existir nesse host. Caso contrário, a instrução falha com UNKNOWN_TABLE.

GRANT ALTER VIEW MODIFY QUERY ON db.mv TO alice;
-- Only if `db.mv` runs as another user, for example `bob`:
GRANT SET DEFINER ON bob TO alice;

Instrução ALTER TABLE … MODIFY REFRESH

ALTER TABLE ... MODIFY REFRESH altera os parâmetros de atualização de uma view materializada atualizável, incluindo o agendamento, as dependências, a aleatorização e as configurações de atualização.

ALTER TABLE [db.]name MODIFY REFRESH EVERY|AFTER ... [RANDOMIZE FOR ...] [DEPENDS ON ...] [SETTINGS ...]

O agendamento (EVERY ou AFTER) é obrigatório: a instrução substitui todos os parâmetros de atualização de uma só vez. Qualquer cláusula não especificada — RANDOMIZE FOR, DEPENDS ON ou SETTINGS — é removida ou redefinida para os valores padrão. Para alterar apenas as configurações de atualização, repita o agendamento atual. O comando atualiza a configuração de atualização da visão existente in loco, sem recriar a visão materializada ou sua tabela de destino, limpar os dados existentes na tabela de destino ou cancelar uma atualização já em execução. As leituras continuam na tabela de destino existente enquanto a configuração é alterada. É seguro repetir o comando com a mesma especificação REFRESH completa. Certifique-se de repetir todas as cláusulas que devem permanecer configuradas, pois cada execução substitui todos os parâmetros de atualização conforme descrito acima.

-- Change the schedule.
ALTER TABLE rmv MODIFY REFRESH EVERY 30 MINUTE;

-- Change retry settings (schedule must be repeated).
ALTER TABLE rmv MODIFY REFRESH EVERY 30 MINUTE
SETTINGS refresh_retries = 5,
         refresh_retry_initial_backoff_ms = 500,
         refresh_retry_max_backoff_ms = 60000;

-- Add or keep a dependency.
ALTER TABLE rmv MODIFY REFRESH EVERY 6 HOUR DEPENDS ON other_rmv;

-- Drop the dependency by omitting `DEPENDS ON`.
ALTER TABLE rmv MODIFY REFRESH EVERY 6 HOUR;

Limitações:

ALTER TABLE ... MODIFY SETTING não tem suporte em visões materializadas; as configurações de atualização só podem ser alteradas por meio de MODIFY REFRESH.
Não há suporte para adicionar ou remover APPEND.
A configuração de atualização all_replicas não pode ser alterada após a criação da visão.

A lista completa das configurações de atualização está documentada em Configurações de atualização. O status da atualização, incluindo as configurações aplicadas no momento, pode ser consultado em system.view_refreshes.

Referência de SQL

Tipos de dados

Motores

Funções

Formatos

Configurações

Tabelas de sistema

Lagos de dados

Privilégios necessários

Instrução ALTER TABLE … MODIFY REFRESH

​Privilégios necessários

​Instrução ALTER TABLE … MODIFY REFRESH

Privilégios necessários

Instrução ALTER TABLE … MODIFY REFRESH