Cassandra中的物化视图错误

问题描述

我是Cassandra的新手，我正在尝试创建表和实例化视图。

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

我的查询是：

-all_orders

-- 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

-- 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);

我遇到这样的异常：

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

推荐答案

Cassandra中的材料化视图解决了没有维护其他表以通过不同的分区键进行查询。但是有以下限制

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

• 将物化视图中的所有基本表主键用作主键。


• （可选）从基表中向
  实例化视图的PRIMARY KEY中添加一个非PRIMARY KEY列。


• 静态列不作为主键。

更多文档参考。

因此，在添加实例化视图的情况下，正确的语法应为

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 );

在这里，planned_delivery_date是分区键，并且行按store_number，supplier_number，order_number（基本上是集群）进行排序列）。因此，此处没有强制性要求添加 CLUSTERING ORDER BY子句。

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.

这篇关于Cassandra中的物化视图错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！