出处:https://www.huaweicloud.com/articles/4c3a9ed1fa19497c59ff979c7d77ec49.html
Cassandra
启动
cqlsh
关闭
exit
退出
quit
表空间
查看表空间
describe keyspaces;
创建表空间
设置复制策略和复制因子:
create keyspace mykeyspace with replication = {'class':'SimpleStrategy','replication_factor':3};
或者
create keyspace mykeyspace1 with replication = {'class':'SimpleStrategy','replication_factor':3} and durable_writes = false;
CREATE KEYSPACE语句有两个属性:replication和durable_writes,replication为复制策略,因为Cassandra是无中心的分布式NoSQL数据库,所以必需复制策略,replication中的class参数为具体的复制策略,replication为复制因子,复制的份数。
修改表空间:
alter keyspace mykeyspace with replication = {'class':'SimpleStrategy','replication_factor':5};
使用表空间
use mykeyspace;
删除表空间
drop keyspace mykeyspace1;
表
查看已创建的表
describe tables;
创建表
创建表(和SQL类似)
创建名为cache的表,其中有int型id属性、text类型的type、value、other三个属性
create table cache (
id int primary key,
type text,
value text,
other text
);
或者
CREATE TABLE excelsior.route (
race_id int,
race_name text,
point_id int,
lat_long tuple<text, tuple<float,float>>,
PRIMARY KEY (race_id, point_id)
);
Partition key & Clustering key & Primary Key
Partition Key(row_key):将数据分散到集群的 node 上
Primary Key:在 Single column Primary Key 情况下作用和 Partition Key 一样;在 Composite Primary Key 情况下,组合 Partition key 字段决定数据的分发的节点;
Clustering Key(column_key):决定同一个分区内相同 Partition Key 数据的排序,默认为升序,我们可以在建表语句里面手动设置排序的方式(DESC 或 ASC)
k_part_one 和 k_part_two 共同组成 Partition key,k_clust_one 和 k_clust_two 共同组成 Clustering key。
create table iteblog_multiple (
k_part_one text,
k_part_two int,
k_clust_one text,
k_clust_two int
data text,
PRIMARY KEY((k_part_one, k_part_two), k_clust_one, k_clust_two)
);
这种情况下有效的查询包括:
select * from iteblog_multiple;
select * from iteblog_multiple where k_part_one = 'iteblog' and k_part_two = 'hadoop';
select * from iteblog_multiple where k_part_one = 'iteblog' and k_part_two = 'hadoop' and k_clust_one = 'hbase';
select * from iteblog_multiple where k_part_one = 'iteblog' and k_part_two = 'hadoop' and k_clust_one = 'hbase' and k_clust_two = 'spark';
查看表结构
describe table tablename;
表空间内删除表
drop table cache;
修改表结构
新增列
在cache表中新增text类型的名为parent的列
alter table cache add parent text;
删除列
在cache表中删除名为parent的列
alter table cache drop parent;
索引
创建索引
在表cache中创建索引,索引是cache表中的type列
create index myindex on cache (type);
删除索引
删除表cache中名为myindex 的索引
drop index myindex;
批处理
大致相当于存储过程
BEGIN BATCH
/ /
APPLY BATCH
begin batch
insert into cache (id, type, value, other) values (2, 'test', 'Mytest2', 'No');
insert into cache (id, type, value, other) values (3, 'test', 'Mytest3', 'No');
apply batch;
截断表(清空表)
截断(清空)cache 表
truncate cache;
增删查改
插入数据:
insert into cache (id, type, value, other) values (1, 'test', 'Mytest1', 'no');
修改数据:
update cache set other = 'No' where id =1;
查询数据:
!!!where条件查询所有效的只能是加了索引的!!!
select * from cache;
select id, value from cache;
select id, value from cache where id = 1;
select * from cache where type = 'test';(此时的type列必须是加了索引的,参照前边创建索引的部分)
查询按row_key = ,column_key范围查询,最左匹配(类似复合索引)。
select * from iteblog_multiple where k_part_one = 'iteblog' and k_part_two = 1 and k_clust_one > 'hbase' and k_clust_two > 1 and k_clust_one < 'zzzz' and k_clust_two <10;
删除数据:
!!!删除数据的where条件只能是主键,不能使用其他条件!!!
delete from cache where id = 3;(正确)
delete from cache where type = 'test'; (错误,会报
InvalidRequest: Error from server: code=2200 [Invalid query] message="Some partition key parts are missing: id")
操作集合
操作lists
create table listcache (id int primary key, names list, email list); 新建表listcache,names和email属性都是list类型
insert into listcache (id, names, email) values (1, ['zhangsan','lisi'], ['zhangsan@163.com', 'lisi@163.com']); 插入数据,list类型数据用[]包起来
update listcache set names = names + ['wangwu'], email = email + ['wangwu@163.com'] where id = 1; 更新数据
操作set
create table setcache (id int primary key, name text, emails set); emails的数据类型是set
insert into setcache (id, name, emails) values (1, 'zhangsan', {'zhang1@163.com', 'zhang2@163.com'}); 插入数据,set数据使用{}包起来
update setcache set emails = emails + {'zhang3@163.com'} where id = 1; 更新数据,需要用旧数据+新数据,删除就用-号
操作Map
create table mapcache (id int primary key, info map);
insert into mapcache(id, info) values (1, {'zhangsan':'zhangsan@163.com', 'lisi':'lisi@163.com'});
update mapcache set info = info + {'wangwu':'wangwu@163.com'} where id = 1;