Cassandra常用命令

http://www.cnblogs.com/youzhibing/p/6617986.html
https://www.w3cschool.cn/cassandra/cassandra_create_index.html

0. 连接Cassandra： /bigdata/cassandra3113/bin/cqlsh -u cassandra -p cassandra 172.16.96.xxx 9042

/hadoop/cassandra/bin/cqlsh -u cassandra -p cassandra 192.168.1.xxx 9042

bin/sstableloader -d 172.16.96.xxx -u cassandra -pw cassandra -t 100  /var/lib/cassandra/data/mydb/user-77470310dc9111e6b83b3767ed5523d2/

1. 增删查改： 
SELECT count(1) FROM nbiot_device_data where imei < '865484021149042' ALLOW FILTERING ;
默认：第一主键只能用=，如果其他的需要加ALLOW FILTERING 
生存周期：ttl
insert into teacher(id,address,name,age,height) values(7,'sx','lida',20,88) using ttl 5;
更新数据：update
UPDATE teacher USING TTL 5 SET age = 22  where id=2 and address='guangdong' and name='Lixiao';
UPDATE nbiot_device_data SET alert = 2  where imei=' 865484021149166' and ts='2018-08-21 06:47:15.272000+0000';
清空表数据： TRUNCATE TABLE nbiot_device_data;

select * from teacher where token(id)=token(1) and (address,name)>('guangxi','lihao') limit 2 ALLOW FILTERING;

select gps,ts from nbiot_device_data where imei='865484021149356' and ts >= '2018-08-21 06:47:15' and ts <= '2018-08-21 06:47:18' order by ts desc;

select gps,ts from nbiot_device_data where imei='865484021149356' and ts >= '1534716453000' and ts <= '1834716455000' order by ts desc;

select gps,ts from nbiot_device_data where imei='865484021149166' and ts >= '1535772451111' and ts <= '1835772452053' order by ts desc;

insert into nbiot_device_data (imei,ts,gps,temperature,acceleration,voltage,acc_status,create_time,battery_level, alert)
values('865484021149356','1534745254125','{"accuracy":7.66,"altitude":613.9,"bearing":0,"lat":34.055337,"lon":111.036881,"speed":0,"time":1534745246999}','','',36,0,'2018-08-20 14:13:08','',1);

2. 建表：
CREATE TABLE nbiot_tracker.nbiot_device_data (
    imei text,
    ts timestamp,
    acc_status int,
    acceleration text,
    alert int,
    battery_level text,
    create_time text,
    gps text,
    temperature text,
    voltage int,
    PRIMARY KEY (imei, ts)
) WITH CLUSTERING ORDER BY (ts DESC);
---------------------------------
create table teacher(
    id int,
    address text,
    name text,
    age int,
    height int,
    primary key(id,address,name)
) WITH CLUSTERING ORDER BY(address DESC, name ASC);
----------------------------------------------
CREATE TABLE nbiot_tracker.teacher (
    id int,
    address text,
    name text,
    age int,
    height int,
    PRIMARY KEY (id, address, name)
) WITH CLUSTERING ORDER BY (address ASC, name ASC);

3. 创建索引： CREATE INDEX idx_teacher_age on teacher(age);
注意：索引列只可以用=号查询，select * from teacher where age=32 and height>30 ALLOW FILTERING;

4. 排序：WITH CLUSTERING ORDER BY(address DESC, name ASC);
SELECT gps,ts FROM nbiot_device_data where imei = '865484021149042' and ts > '2018-08-30 10:33:20' order by ts asc;

SELECT * FROM teacher WHERE id=1 AND address='guangxi' ORDER BY address DESC, name DESC;
注意：a、必须有第一主键的=号查询；cassandra的第一主键是决定记录分布在哪台机器上，也就是说cassandra只支持单台机器上的记录排序。
  　　b、只能根据第二、三、四…主键进行有序的，相同的排序。有序：order by后面只能是先二、再三、再四…这样的顺序，有四，前面必须有三；有三，前面必须有二，以此类推。相同的顺序：参与排序的主键要么与建表时指定的顺序一致，要么全部相反，具体会体现在下面的示例中
      c、  不能有索引查询
      
5. 时序查询
SELECT * FROM nbiot_device_data where imei < '865484021149042' and ts > '2018-08-30 15:33:20' ALLOW FILTERING;
SELECT gps,ts FROM nbiot_device_data where imei = '865484021149042' and ts > '2018-08-30 10:33:20' ALLOW FILTERING;

6. 创建keyspaces： 
CREATE KEYSPACE nbiot_tracker WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '1'}  AND durable_writes = true;

7. 描述： desc ks_name; desc tb_name;

8. 修改表列：
ALTER TABLE teacher add age int;
ALTER TABLE teacher  DROP age;

修改表生存周期：
ALTER TABLE test.test_ttl WITH default_time_to_live = 120;

9. 清空表数据：TRUNCATE TABLE tableName

10. 自定义数据类型: create type address ( ... );

11. 触发器,需要JAVA实现： 
create trigger myTrigger on myTable using 'org.apache.cassandra.triggers.InvertedIndex';

12. 数据类型：
List： 
CREATE TABLE data(name text PRIMARY KEY, email list<text>);
INSERT INTO data(name, email) VALUES ('ramu', ['abc@gmail.com','cba@yahoo.com'])；
UPDATE data SET email = email +['xyz@tutorialspoint.com'] where name = 'ramu';

Set：
CREATE TABLE data2 (name text PRIMARY KEY, phone set<varint>);
INSERT INTO data2(name, phone)VALUES ('rahman',    {9848022338,9848022339});
UPDATE data2 SET phone = phone + {9848022330} where name = 'rahman';

Map：
CREATE TABLE data3 (name text PRIMARY KEY, address map<timestamp, text>);
INSERT INTO data3 (name, address) VALUES ('robin', {'home' : 'hyderabad' , 'office' : 'Delhi' } );
UPDATE data3 SET address = address+{'office':'mumbai'} WHERE name = 'robin';

14.批量操作：
BEGIN  BATCH
...
APPLY BATCH

15. 导入导出数据：
COPY mydb.user TO '/usr/usr.csv';
COPY mydb.user FROM '/usr/user.csv'; 
COPY nbiot_tracker.nbiot_device_data TO '/bigdata/cassandraData/bakeup/nbiot_device_data.csv';
COPY nbiot_tracker.nbiot_device_data FROM '/bigdata/cassandraData/bakeup/nbiot_device_data.csv';

16. CONTAINS :
对集合结构的条件 可以使用关键字 contains; 但是前提是要对集合结构建立索引  对于map更为特殊 create index on tableName(keys(mapName));

17. 表重命名： https://blog.csdn.net/zzxzz00001/article/details/70058394

18. 常见错误：
 1）Coordinator node timed out waiting for replica nodes' responses
 2）Timestamp时区问题，导致少了8小时；
 
19. 内存优化
 1）查看内存： /bigdata/cassandra3113/bin/nodetool info
 2）内存调整： MAX_HEAP_SIZE, HEAP_NEWSIZE

20. 查看表状态： nodetool cfstats nbiot_device_data;

21. 写入性能：逐条写入：11w/s，用时每条：4.41ms； 批量写入：137905.75点，每个用时：3.62ms；

22. 索引：索引的使用非常简单，参考下面的代码以及相应的注释：

-- 创建索引 CREATE INDEX artist_names ON playlists( artist );   -- 查询 SELECT * FROM playlists WHERE artist = 'Fu Manchu';   -- 优化查询 SELECT * FROM playlists WHERE id = 62c36092-82a1-3a00-93d1-46196ee77204 AND  artist = 'Fu Manchu';

ReadTimeout: Error from server: code=1200 [Coordinator node timed out waiting for replica nodes' responses] message="Operation timed out - received only 0 responses." info={'received_responses': 0, 'required_responses': 1, 'consistency': 'ONE'}

转载于:https://my.oschina.net/looten/blog/3100530