Weird Behaviour or Materialized views

[Ingmar Poese]

Hi All,

I've been working with materialized views, and i've run across a situation that leaves me confused. I've tried to minimize the example as much as possible while preserving the error...

So, basically, i have two table. One that holds interface counters of network card in buckets, and one that gives me a filter for those network cards based on certain machine/interface combinations.

So, first i create the two tables i want to add the data to;

create Database test2;
create table test2.bucket (date Date, bucketTimestamp DateTime, router String, interface Int32, bytes Int64) ENGINE = MergeTree(date, (bucketTimestamp, router, interface), 8192);
create table test2.filter (date Date, dayTimestamp UInt32, router String, interface Int32) ENGINE = MergeTree (date, (dayTimestamp), 8192);

Bucket has a time resolution much smaller than filter and (unfortunately) they also use different types. But i don't think this matter here now. So, what i want to do now is build a sum over the bytes in test2.bucket,but only for those machine/interface types that are present in the filter table.

thus, i use this select:

select date, dayTimestamp, sum(bytes) from (select date, toDate(toUInt32(toString(bucketTimestamp, 'UTC'))) as dayTimestamp, router, interface, bytes from test2.bucket ) ALL INNER JOIN (select dayTimestamp, router, interface from test2.filter) using (dayTimestamp, router, interface) group by date, dayTimestamp;

Which works fine.

Next, to always have this available for fast lookup, i create a materialized view from the select (note that i removed the group by and assume the DBEngine to do the summing for me):

CREATE MATERIALIZED VIEW test2.summary (date Date, dayTimestamp UInt32, bytes UInt64) ENGINE = SummingMergeTree(date, (dayTimestamp), 8192) AS select date, dayTimestamp, bytes from (select date, toDate(toUInt32(toString(bucketTimestamp, 'UTC'))) as dayTimestamp, router, interface, bytes from test2.bucket ) ALL INNER JOIN (select dayTimestamp, router, interface from test2.filter) using (dayTimestamp, router, interface);

So far, i've not inserted any data into the bucket - lets do that now:

insert into test2.bucket values ('2016-01-01', 1451606400, 'R1', 0, 100);

This throws the following error:

Received exception from server:
Code: 10. DB::Exception: Received from localhost:9000, ::1. DB::Exception: Not found column dayTimestamp in block. There are only columns: date, bucketTimestamp, router, interface, bytes.

I have no idea why dayTimestamp cannot be resolved when inserting, while i can be resolves when i run the query manually...

Thanks to anyone in advance who can help me fix this.

[Vadim Sichkarev]

Hi

Hi

On today ClickHouse has small problem with materialized columns (in particular in my case with use Distributed tables). May be your bug associated with it. May be not :)

See more information by link (Russian): https://groups.google.com/d/msg/clickhouse/XEYRRwZrsSc/dPB5XDJKBgAJ

In my case can create table in three steps:

1. Create table A with materialized columns

2. Create full clone table B: INSERT INTO B FROM select a,b,c,d,e from A (list all columns from table A)

3. Create table C: (Distributed table or materialized view) on table B (without materialized columns)

May be this approach will help you

As temporary solution naturally :)

понедельник, 21 ноября 2016 г., 18:46:05 UTC+5 пользователь Ingmar Poese написал:

[Igor Hatarist]

I've stumbled upon this bug myself a while ago.

Basically, you can't have VIEWs that have aliased columns, since it messes up the processing of the original table.

You should `DROP TABLE test2.summary` to make INSERTs work again.

Here's the issue on Github: https://github.com/yandex/ClickHouse/issues/127