Cassandra物化视图创建报错求助：SyntaxException语法异常

嘿，我来帮你搞定这个问题！你遇到的SyntaxException主要是由几个语法和规则问题导致的，咱们一个个来修正：

错误原因分析 #

1. SELECT语句的语法错误：你的物化视图SELECT语句里，最后一个字段locked后面多了个逗号，这直接导致Cassandra解析器无法正确识别后续的MATERIALIZED关键字，这是触发报错的直接原因。
2. 物化视图主键规则违反：Cassandra要求物化视图的主键必须包含基表的所有分区键，你的基表all_orders的分区键是order_number，所以物化视图的主键里必须包含它，否则会违反规则。
3. 排序字段未包含在主键中：你指定了CLUSTERING ORDER BY (store_number,supplier_number)，但这两个字段没有出现在物化视图的主键里，Cassandra无法对不在主键中的字段设置聚类排序。

修正后的CQL语句 #

基表创建（原语句没问题，保留） #

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

修正后的物化视图创建语句 #

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, order_number, store_number, supplier_number) 
WITH CLUSTERING ORDER BY (order_number ASC, store_number ASC, supplier_number ASC);

关键修正点说明 #

• 移除了SELECT语句中locked字段末尾多余的逗号，修复了基础语法错误
• 在物化视图主键中加入了基表的分区键order_number，满足Cassandra的物化视图主键规则
• 将store_number和supplier_number加入聚类列部分，确保可以对它们设置排序规则
• 在WHERE子句中补充了store_number和supplier_number的非空判断，因为它们是主键的一部分，Cassandra不允许主键字段为NULL

内容的提问来源于stack exchange，提问作者Sun