Cassandra查询操作趟坑记录

待我称王封你为后i 2023-08-17 15:45 74阅读 0赞

### 例子表 ###

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

转载于:https://www.cnblogs.com/yan7/p/11423382.html