Cassandra查询操作趟坑记录

例子表

CREATE TABLE employee
(
    name       		TEXT,
    age      		SMALLINT,
    phone 			TEXT,
    bornDate  		DATE,
    createDate  	timestamp,
    PRIMARY KEY ((bornDate),name, age,phone, createDate)
)
    WITH compression = {
        'chunk_length_in_kb' : 256,
        'class' : 'LZ4Compressor',
        'enabled' : true
        } AND CLUSTERING ORDER BY (age asc, bornDate asc, createDate desc )

1.主键顺序查询限制

cassandra主键是一个partition key主键和多个clustering key复合主键,而主键的查询顺序必须与定义表结构时一致.

也就是说下面这种查询错的

select * from employee where age = 1 and name = '张三' and bornDate='1999-01-01'

而正确写法应该是这样

select * from employee where bornDate = '1999-01-01' and name ='张三' and age=1

2. 分区主键查询限制

cassandra中分区主键只能以等号或in查询,不能使用范围查询

也就是不能以出生日期进行范围查询

select * from employee where  bornDate >='1999-01-01' and name='张三';

必须以出生日期in查询,由于in查询其实效率并不是太好,所以在表设计时应当注意

select * from employee where bornDate in ('1999-01-01','1999-01-02') and name = '张三'

3.范围主键查询限制

cassandra中范围查询只能放在条件查询的最后一个位置,例如,如果范围查询age,则就不能添加phone查询条件

也就是这么写法是错的

select * from employee where bornDate = '2019-01-01' and name ='张三' and age >18 and phone = '123456'

当然也并不是不能这么做,不过那样必须加上ALLOW FILTERING,但并不建议这么做

也就是下面这种写法是没问题的

select * from employee where bornDate = '2019-01-01' and name ='张三' and age >18 and phone = '123456'  allow filtering;

4.排序规则

cassandra在创建表时设置一个排序规则,默认以此进行规则排序,如当前表,默认以正序age,正序bornDate和倒序createDate, 手动设置倒序只有一种方式,即将所有排序字段全部颠倒,也就是必须像这样

select * from employee where bornDate in ('1999-01-01') and name = '张三' order by  age desc, bornDate desc, createDate asc

5.排序对分区主键条件的限制

cassandra中只要使用排序,无论是使用默认排序规则还是相反排序规则,分区主键只能使用等于查询,(可以使用in,但是只能IN一个数据),

所以这样写就是错误

select * from employee where bornDate in ('1999-01-01','1999-01-02') and name = '张三' order by  age desc, bornDate desc, createDate asc

应该

select * from employee where bornDate in ('1999-01-01') and name = '张三' order by  age desc, bornDate desc, createDate asc

或

select * from employee where bornDate = '1999-01-01' and name = '张三' order by  age desc, bornDate desc, createDate asc

6.使用In和Order by 时需要全局关闭分页,

Cluster.Builder()
    .AddContactPoints(cassandraUrls)
    //      设置pageSize为最大值,这样代表为关闭分页,可以使用in 和order by
    .WithQueryOptions(new QueryOptions().SetPageSize(int.MaxValue))
    .Build();