ClickHouse Alter Inalified View添加列

Question

env：Clikchouse版本：22.3.3.44;数据库引擎：Atomic

我有一个原始表和MV，类似的模式：

CREATE TABLE IF NOT EXISTS test.Income_Raw on cluster '{cluster}' (
  Id Int64,
  DateNum Date,
  Cnt Int64,
  LoadTime DateTime
) ENGINE==MergeTree
PARTITION BY toYYYYMMDD(LoadTime)
ORDER BY (Id, DateNum);

CREATE MATERIALIZED VIEW test.Income_MV on cluster '{cluster}'
ENGINE = ReplicatedAggregatingMergeTree('/clickhouse/{shard}/{database}/{table}', '{replica}')
PARTITION BY toYYYYMM(DateNum)
ORDER BY (Id, DateNum)
TTL DateNum+ INTERVAL 100 DAY
AS SELECT
    DateNum,
    Id,
    argMaxState(Cnt,  LoadTime) as Cnt ,
    maxState( LoadTime)  as latest_loadtime
FROM test.Income_Raw
GROUP BY Id, DataNum;

现在我想将名为“价格”的列添加到Raw Table和MV， 因此，我按照以下步骤运行SQL：

// first I alter raw table
1. alter table test.Income_Raw on cluster '{cluster}' add column Price Int32

// below sqls, I run to alter MV
2. detach test.Income_MV on cluster '{cluster}'

3. alter test.`.inner_id.{uuid}` on cluster '{cluster}' add column Price Int32

// step 4, basically I just use 'attach' replace 'create' and add 'Price' to select query
4. attach MATERIALIZED VIEW test.Income_MV on cluster '{cluster}'
ENGINE = ReplicatedAggregatingMergeTree('/clickhouse/{shard}/{database}/{table}', '{replica}')
PARTITION BY toYYYYMM(DateNum)
ORDER BY (Id, DateNum)
TTL DateNum+ INTERVAL 100 DAY
AS SELECT
    DateNum,
    Id,
    Price,
    argMaxState(Cnt,  LoadTime) as Cnt ,
    maxState( LoadTime)  as latest_loadtime
FROM test.Income_Raw
GROUP BY Id, DataNum, Price;

但是，在步骤4中，我遇到的错误

Code: 80. DB::Exception: Incorrect ATTACH TABLE query for Atomic database engine. Use one of the following queries instead:
1. ATTACH TABLE Income_MV;
2. CREATE TABLE Income_MV <table definition>;
3. ATTACH TABLE Income_MV FROM '/path/to/data/' <table definition>;
4. ATTACH TABLE Income_MVUUID '<uuid>' <table definition>;. (INCORRECT_QUERY) (version 22.3.3.44 (official build))

这些SQL是我从下面的参考文献中遵循的。

https://kb.altinity.com/altinity.com/altinity.com/altinity-kb-kb-schema -design/soletialized-views/

a>

所以我的问题是，如何修改MV选择查询，我错了哪一步？

Answer 1

我想知道只需要：

准备：使用明确的目标表代替内部表格

1 alter mv目标表

2下降MV

3重新创建MV，并使用新查询