Materialised view error in Cassandra

Question

I am new to Cassandra, I am trying to create a table and materialized view. but it not working.

My queries are:

-- all_orders

create table all_orders (
    id uuid,
    order_number bigint,
    country text,
    store_number bigint,
    supplier_number bigint,
    flow_type int,
    planned_delivery_date timestamp,
    locked boolean,
    primary key ( order_number,store_number,supplier_number,planned_delivery_date ));

-- orders_by_date

CREATE MATERIALIZED VIEW orders_by_date AS 
    SELECT 
        id,
        order_number,
        country,
        store_number,
        supplier_number,
        flow_type,
        planned_delivery_date,
        locked,
    FROM all_orders
    WHERE planned_delivery_date IS NOT NULL AND order_number IS NOT NULL 
    PRIMARY KEY ( planned_delivery_date )
    WITH CLUSTERING ORDER BY (store_number,supplier_number);

I am getting an exception like this:

SyntaxException: <ErrorMessage code=2000 [Syntax error in CQL query]
message="line 1:7 no viable alternative at input 'MATERIALIZED' ([CREATE] MATERI
ALIZED...)">

Cassandra version?... Also with PRIMARY KEY of MV there will be only one record for one delivery date... it should be PRIMARY KEY ( planned_delivery_date, store_number,supplier_number) if you are using clustering order — undefined_variable
A reminder about begging, from an earlier question: Pro posting tips: I notice you're adding a lot of chatty/pleading material (I need help, please help me, would anyone kindly help, etc). You may find that this is irritating to native English speakers, so doesn't actually encourage folks to help; in any case, since you're adding it to all your questions, that's more work for volunteer editors, who like to trim this sort of thing. Thank you if you can cut it down in the future. — halfer
Let's take a step back :) Materialized Views have gone back into experimental mode, which means their use is NOT recommended. My suggestion? Find a different way of doing what you want. :) — Pedro Gordo

dilsingi dilsingi · Accepted Answer · 2018-01-25T20:19:22

Materialized Views in Cassandra solves the use case of not having to maintain additional table(s) for querying by different partition keys. But comes with following restrictions

Use all base table primary keys in the materialized view as primary keys.
Optionally, add one non-PRIMARY KEY column from the base table to the materialized view's PRIMARY KEY.
Static columns are not supported as a PRIMARY KEY.

More documentation reference here.

So the correct syntax in your case of adding the materialized view would be

CREATE MATERIALIZED VIEW orders_by_date AS 
    SELECT  id, 
            order_number, 
            country, 
            store_number, 
            supplier_number, 
            flow_type, 
            planned_delivery_date, 
            locked
            FROM all_orders
            WHERE planned_delivery_date IS NOT NULL AND order_number IS NOT NULL AND store_number IS NOT NULL AND supplier_number IS NOT NULL
            PRIMARY KEY ( planned_delivery_date, store_number, supplier_number, order_number );

Here planned_delivery_date is the partition key and the rows are ordered by store_number, supplier_number, order_number (essentially the clustering columns). So there isn't a mandatory requirement to add "CLUSTERING ORDER BY" clause here.

Materialised view error in Cassandra

1 Answers