Cassandra连接和查询

cassandra开发

图形界面-NoSQL Manager for Cassandra

下载地址：http://www.mongodbmanager.com/cassandra/

登陆：

创建keyspace

Cassandra的存储抽象结构和数据库一样，keyspace对应关系数据库的database或schema，column family对应于table。
示例如下：
CREATE KEYSPACE iotstp WITH replication = {'class': 'SimpleStrategy','replication_factor': 1};
其它操作

ALTER KEYSPACE iotstp WITH replication = {'class': 'SimpleStrategy', 'replication_factor' : 4};
use iotstp;
DROP KEYSPACE iotstp;

查看当前有哪些keyspace使用命令desc keyspaces。

连接Cassandra数据库

private Session session;

    public UbuDSCassandraFactory(CassandraDSProperties poperties) {
        this.session = generSession(poperties);
    }


    public Session generSession(CassandraDSProperties properties) {
        Cluster cluster =
                Cluster.builder()
                        .addContactPoint("192.168.243.22")
                        .withPort(9042)
                        //.withCredentials(cassandraUsername, cassandraPassword)
                        //.withSSL(sslOptions)
                        .build();
        return cluster.connect("dev_test");
    }

创建Table

创建示例如下：

CREATE TABLE IF NOT EXISTS iotstp.user (
    id timeuuid,
    tenant_id timeuuid,
    email text,
    additional_info text,
    PRIMARY KEY (id, tenant_id)
);

修改示例：

ALTER TABLE iotstp.user ADD address varchar;

ALTER TABLE iotstp.user
       WITH comment = 'A most excellent and useful table'
       AND read_repair_chance = 0.2;

删除表

DROP TABLE [ IF EXISTS ] table_name

清空表
TRUNCATE [ TABLE ] table_name

查看当前有哪些table使用命令desc tables。

数据操作

select查询

SELECT name, occupation FROM users WHERE userid IN (199, 200, 207);
SELECT JSON name, occupation FROM users WHERE userid = 199;
SELECT name AS user_name, occupation AS user_occupation FROM users;

SELECT time, value
FROM events
WHERE event_type = 'myEvent'
  AND time > '2011-02-03'
  AND time <= '2012-01-01'

SELECT COUNT (*) AS user_count FROM users;

insert插入

INSERT INTO NerdMovies (movie, director, main_actor, year)
                VALUES ('Serenity', 'Joss Whedon', 'Nathan Fillion', 2005)
      USING TTL 86400;

INSERT INTO NerdMovies JSON '{"movie": "Serenity",
                              "director": "Joss Whedon",
                              "year": 2005}';
                              
insert into stuff（uid，name）values（now（），'my name'） //id的类型为timeuuid的

update更新

UPDATE NerdMovies USING TTL 400
   SET director   = 'Joss Whedon',
       main_actor = 'Nathan Fillion',
       year       = 2005
 WHERE movie = 'Serenity';

UPDATE UserActions
   SET total = total + 2
   WHERE user = B70DE1D0-9908-4AE3-BE34-5573E5B09F14
     AND action = 'click';

delete 删除

DELETE FROM NerdMovies USING TIMESTAMP 1240003134
 WHERE movie = 'Serenity';

DELETE phone FROM Users
 WHERE userid IN (C73DE1D3-AF08-40F3-B124-3FF3E5109F22, B70DE1D0-9908-4AE3-BE34-5573E5B09F14);

更新与删除只支持按主键进行，意思是where关键字后面必须携带主键字段。

建表

create table demo_student ( id int primary key, name varchar, age int );

查询字段

SELECT keyspace_name, table_name, column_name, clustering_order, column_name_bytes, kind, position, "type"
FROM system_schema.columns where keyspace_name = 'dev_test' and table_name = 'demo_student';

各种查询

查询所有表

select table_name FROM system_schema.tables where keyspace_name ='%s'

查询表信息

select table_name, engine, row_format, table_rows, avg_row_length, IFNULL(auto_increment,'') auto_increment, table_collation, table_comment from information_schema.tables
 where table_schema='%s' and table_name='%s' and table_type='BASE TABLE'

查询表字段

select table_name, column_name, type as column_type,  clustering_order, column_name_bytes, kind, position
from system_schema.columns where keyspace_name ='%s' and table_name = '%s'

查询索引

SELECT 
index_name ,
kind,
options
FROM system_schema.indexes 
WHERE keyspace_name='%s' AND   table_name = '%s'

查询主键

 select table_name, column_name, type as column_type,  clustering_order, column_name_bytes, position 
from system_schema.columns where keyspace_name ='%s' and table_name = '%s' and kind = 'partition_key' ALLOW FILTERING;

查询外键

select table_name,column_name,constraint_name, referenced_table_name, referenced_column_name, referenced_table_schema 
 from information_schema.key_column_usage where constraint_schema ='%s' and table_name = '%s' and referenced_table_name is not null 

修改表名称

alter table `%s` rename %s;

删除表

DROP TABLE IF EXISTS `%s`