Clickhouse altering materialized view's select

Question

I have following setup:

CREATE TABLE IF NOT EXISTS request_income_buffer (
    timestamp UInt64,
    timestamp_micro Float32,
    traceId Int64,
    host String,
    type String,
    service String,
    message String,
    caller String,
    context String
) ENGINE = Kafka('kafka:9092', 'request_income', 'group', 'JSONEachRow');

CREATE MATERIALIZED VIEW IF NOT EXISTS request_income
ENGINE = MergeTree(date, microtime, 8192) AS
    SELECT
        toDate(toDateTime(timestamp)) AS `date`,
        toDateTime(timestamp) as `date_time`,
        timestamp,
        timestamp_micro AS `microtime`,
        traceId,
        host,
        type,
        service,
        message,
        caller,
        context
    FROM
        request_income_buffer;

I want to add new column, ex. ip to my request_income table. According to docs in order to do so I will need to follow next steps:

1. Detach view to stop receiving messages from Kafka.

   DETACH TABLE request_income;

2. Drop table that streams data from Kafka since Kafka engine doesn't support ALTER queries.

   DROP TABLE request_income_buffer

3. Recreate table that streams data from Kafka with new field.

   CREATE TABLE IF NOT EXISTS request_income_buffer ( timestamp UInt64, timestamp_micro Float32, traceId Int64, host String, ip String, type String, service String, message String, caller String, context String ) ENGINE = Kafka('kafka:9092', 'request_income', 'group', 'JSONEachRow');

4. According to this post update .inner table of the detached materialized view

   ALTER TABLE `.inner.request_income` ADD COLUMN ip String AFTER host;

5. According to post from above update view's select query

6. Attach view

   ATTACH TABLE request_income

Question is how to update view's select query?

Confidante

Answer 1

So it appears the way to update materialized view's select query is as follows:

1. Get path to views metadata

   SELECT metadata_path FROM system.tables WHERE name = 'request_income';

2. Use your favorite text editor to modify view's sql. In my case edited sql will look like

   ATTACH MATERIALIZED VIEW request_income ( date Date, date_time DateTime, timestamp UInt64, microtime Float32, traceId Int64, host String, ip String, type String, service String, message String, caller String, context String ) ENGINE = MergeTree(date, microtime, 8192) AS SELECT toDate(toDateTime(timestamp)) AS date, toDateTime(timestamp) AS date_time, timestamp, timestamp_micro AS microtime, traceId, host, ip, type, service, message, caller, context FROM default.request_income_buffer

3. Attach modified view back

   ATTACH TABLE request_income;

Confidante

How would this be influenced if the tables are of the ReplicatedMergeTree family? – Lsd

Answer 2

Try this out:

1. DETACH TABLE request_income;
2. ALTER TABLE `.inner.request_income` ADD COLUMN ip String AFTER host;

3. ATTACH MATERIALIZED VIEW request_income 
   ENGINE = MergeTree(date, microtime, 8192) AS
    SELECT
        toDate(toDateTime(timestamp)) AS `date`,
        toDateTime(timestamp) as `date_time`,
        timestamp,
        timestamp_micro AS `microtime`,
        traceId,
        host,
        ip,
        type,
        service,
        message,
        caller,
        context
    FROM
        request_income_buffer;
   

Allin

I'm doing this, but reattached materialized view does not contain the new column. What's wrong? – Training

It looks like TO materialized views (with explicit target table) work differently. I see ATTACH for them work incorrectly. However, for them you can just DROP and then CREATE them again. – Training