Cassandra cqlsh命令大全

一：CQL 简介

CQL是Cassandra Query Language的缩写，目前作为Cassandra默认并且主要的交互接口。CQL和SQL语法很相似，主要的区别是cql不支持join和子查询,相对来说没有sql那么强大。

二：Shell 命令

// 登录shellD:\Java\apache-cassandra-3.11.0\bin>cqlshD:\Java\apache-cassandra-3.11.0\bin>cqlsh --helpD:\Java\apache-cassandra-3.11.0\bin>cqlsh --version// 使用用户名和密码登录，默认用户名和密码都是cassandraD:\Java\apache-cassandra-3.11.0\bin>cqlsh -u 'cassandra' -p 'cassandra'// 启动时执行cql（可用于导入数据，或者执行文件中的cql）D:\Java\apache-cassandra-3.11.0\bin>cqlsh --file="D:\users.cql"// 帮助命令cqlsh> help// 捕获命令，所有的select查询的结果都将保存在output文件中cqlsh> capture 'D:\Java\apache-cassandra-3.11.0\data\output'// 关闭捕获cqlsh:test> capture off;// 复制命令copy to, 将表中的数据写入到文件中 cqlsh:test> copy users(id, username, age) to 'D:\myfile'// 扩展命令，使用命令后select输出的结果展示形式不一样;cqlsh:test> expand on;Now Expanded output is enabledcqlsh:test> select * from users;@ Row 1------------+-------------------------------------- id         | 2 age        | 36 birthday   | 1989-06-06 createtime | 2017-09-02 12:06:29.477000+0000 height     | 145.5 hobbies    | ['java', 'php'] ip         | 192.168.11.11 isvip      | False salt       | 2718b240-8fd7-11e7-b82c-9340daca092f scores     | {'china': 90, 'english': 99} skills     | {'drink', 'happy', 'play'} tags       | ('gg', 'rmb') username   | mengdee@ Row 2------------+--------------------------------------id         | 1age        | 26birthday   | 1990-10-26createtime | 2017-09-02 12:04:00.023000+0000height     | 135.5hobbies    | ['java', 'iOS']ip         | 192.168.1.1isvip      | Truesalt       | c80b339f-4d2a-4928-9974-603edc65785cscores     | {'china': 100}skills     | {'drink', 'eat'}tags       | ('girl', '$')username   | mengday// 关闭扩展命令cqlsh:test> expand off;// show命令:显示当前cqlsh会话的详细信息cqlsh:test> show host;Connected to Test Cluster at 127.0.0.1:9042.cqlsh:test> show version;[cqlsh 5.0.1 | Cassandra 3.11.0 | CQL spec 3.4.4 | Native protocol v4]// 关闭shellcqlsh:test> exit// 退出shellcqlsh:test> quit

三：数据类型cql_type

CQL支持一组丰富的数据类型(cql_type)，包括

• 原生类型(native_type)、
• 集合类型(collection_type)、
• 用户定义类型(user_defined_type)、
• 元组类型(tuple_type)
• 自定义类型(custom_type)

原生类型native_type

所谓原生类型就是关系型数据库支持的常用的数据类型和Cassandra扩展的一些基本数据类型。

• tinyint(8位有符号整数)、smallint(16位有符号整数)、int(32位有符号整数)、bigint(64-bit有符号long)、varint(任意精度整数)
• float、double、decimal(可变精度小数)
• ascii(ASCII字符串)、varchar、text(UTF-8编码字符串)
• date(yyyy-mm-dd)、time(hh:mm:ss)、timestamp
• boolean
• blob(任意十六进制字节)
• inet(ipv4或ipv6格式的IP地址)
• uuid(标准uuid，使用uuid()函数生成uuid，blobAsUuid(timeuuidAsBlob(now())))
• timeuuid(时间相关的uuid，可以使用now()作为值)
• duration(具有纳秒精度的持续时间)
• counter(为64位分布式计数器值)
• ma (JSON风格的元素集合)
• udt(自定义类型)

集合数据类型collection_type

list<T>    [value, value,...]set<T>   {value, value, ...}map<T, T>  {'key1':value1, 'key2':value2}  使用column['key']来访问tuple<text, text>      (value, value, ...)frozen(元组，集合，用户定义的类型, 存储Cassandra类型)

用户定义类型UDT

// 创建类型CREATE TYPE <NAME> (   column cql_type,   column cql_type);cqlsh:test>create type address (                proivnce text,                city text,                region text,                town text            );// 列举所有的类型cqlsh:test> describe types;// 查看某个类型cqlsh:test> describe type address;// 添加字段ALTER TYPE <name> ADD column cql_type; cqlsh:test> alter type address add way text;// 重命名字段ALTER TYPE <name> RENAME <COLUMN> TO <new_name>cqlsh:test> alter type address rename way to road;// 删除类型DROP TYPE <name>;cqlsh:test> drop type address;

四：键空间keyspace的基本操作

键空间：是列族（表）、索引等容器，类似于mysql中的数据库database，类似于oracle中的表空间。

• Ⅰ 列举键空间
  DESCRIBE KEYSPACES;

  For instance:

    // 系统默认创建了几个keyspace    cqlsh> describe keyspaces;    system_schema  system_auth  system  system_distributed  system_traces// 查看某个键空间的相关信息(包含 键空间表keyspaces、索引表indexes、视图表views、函数表functions、触发器表triggers、聚合表aggregates、类型表types等)cqlsh> describe keyspace system_schema;

• Ⅱ 创建键空间

  CREATE KEYSPACE <name> WITH REPLICATION = {'class':'Strategy Name','replication_factor': int}  AND durable_writes = boolean; 

  • 策略class
    
    • SimpleStrategy：简单策略，在一个数据中心的情况下使用
    • NetworkTopologyStrategy：网络拓扑策略，用于多个数据中心
  • 复制因子replication_factor：副本数
  • 持久写入属性：durable_write：boolean值,默认true

For instance:

cqlsh> CREATE KEYSPACE test WITH replication = {'class': 'SimpleStrategy', 'replication_factor' : 3};cqlsh> CREATE KEYSPACE test2 WITH replication = {'class': 'NetworkTopologyStrategy', 'DC1' : 1, 'DC2' : 3} AND durable_writes = true;

• Ⅲ 修改键空间的replication 和durable_writes

  ALTER KEYSPACE <name>WITH REPLICATION = {'class': 'strategy name', 'replication_factor': int} AND durable_writes = boolean;

  For instance:

  cqlsh> ALTER KEYSPACE test WITH REPLICATION = {'class': 'SimpleStrategy', 'replication_factor' : 1} AND durable_writes = true;

• Ⅳ 使用键空间
  DROP KEYSPACE <name>

  cqlsh> drop keyspace test2;

• Ⅴ 删除键空间

  USE <keyspace_name>

  
cqlsh> use test;
cqlsh:test>


---

五：表（列族）的操作

• 创建表

  CREATE TABLE [IF NOT EXISTS] <name> (    column cql_type,    column cql_type,    column cql_type,    RIMARY KEY(column, column)) [WITH property = value AND property = value ];

  For instance:

  cqlsh:test>create table IF NOT EXISTS users (                id bigint primary key,                username text,                age int,                height double,                brithday date,                isvip boolean,                salt uuid,                ip inet,                hobbies list<text>,                skills set<text>,                scores map<text, int>,                tags tuple<text, text>,                createtime timestamp,            ) with comment = 'user info table';

• 查看表的信息
  DESCRIBE TABLE name;

  cqlsh:test> describe table users;

• 修改表：

  • 添加一列
    ALTER TABLE <name> ADD column cql_type

    cqlsh:test> alter table users add temp varchar

  • 删除一列

  ALTER TABLE <name> DROP column

  cqlsh:test> alter table users drop temp;

  • 删除多列

  ALTER TABLE <name> DROP (column, column)

• 删除表
  drop table <name>

• 截断表: 删除表中的所有行
  truncate <name>

• 列出所有表
  DESCRIBE TABLES

六：索引

• 创建索引
  CREATE INDEX [name] ON <table_name(column)>

  cqlsh:test> create index users_username_idx on users(username);

• 删除索引
  DROP INDEX [IF EXISTS] <name>;

---

七：增删改查CURD

• insert

INSERT INTO <name>(column, column)VALUES(value, value) [USING TTL seconds];INSERT INTO <name> JSON ' {"key1": "value", "key2": "value"} '

For instance:

cqlsh:test>insert into users(    id,     username,     age,     height,     birthday,    isvip,     salt,    ip,    hobbies,     skills,     scores,     tags,     createtime)values(    1,     'mengday',     26,     135.5,     '1990-10-26',    true,    uuid(),    '192.168.1.1',     ['java', 'iOS'],     {'eat', 'drink'},     {'china': 80, 'english': 90},     ('mm', 'money'),     dateof(now()));cqlsh:test> insert into users(    id,     username,     age,     height,     birthday,    isvip,     salt,    ip,    hobbies,     skills,     scores,     tags,     createtime)values(    2,     'mengdee',     36,     145.5,     '1989-06-06',    false,    blobAsUuid(timeuuidAsBlob(now())),    '192.168.11.11',     ['java', 'php'],     {'play', 'happy'},     {'china': 90, 'english': 99},     ('gg', 'rmb'),     dateof(now()));cqlsh:test>insert into users json ' {"id": 3, "username": "mengdie", "age": 16}'using ttl 3600;

• select
  SELECT column, column FROM<name> WHERE <condition>;

For instance:

cqlsh:test> select * from users;// 如果where条件中使用的字段没有创建索引，需要使用allow filtering表示强制查询cqlsh:test cqlsh:test> select id, username, createtime, tags from users where id in(1, 2) and age > 18 and tags = ('mm', 'money')  allow filtering;

• update

UPDATE <tableName> [USING TTL seconds]SET         column = value,        column = valueWHERE <condition>

For instance:

cqlsh:test> update users using ttl 60 set username = 'hehe' where id = 3;// 当更新的条件不满足时相当于insert操作cqlsh:test> update users set username = 'admin' where id = 999999;

• delete
  
  • 删除行

    DELETE FROM <name> WHERE <condition>

• 删除字段

 DELETE column FROM <name> WHERE <condition>；

• batch

begin batch        <insert-stmt>;        <update-stmt>;        <delete-stmt>;apply batch;

cqlsh:test>begin batch        insert into users json ' {"id": 4, "username": "test", "age": 16}';        update users set age = 20 where id = 4;        delete age from users where id = 4;    apply batch;

八：集合操作CRUD

• set 无序集合

创建表声明时使用set<cql_type>,使用时使用一对花括号将多个值括起{value, value}// 添加元素cqlsh:test> update users set skills = skills + {'eat', 'drink', 'mm'} where id = 2;// 删除元素cqlsh:test> update users set skills = skills -  {'eat', 'mm'} where id = 2;

• list 有序集合, 允许重复，通过索引访问某个元素

声明时需要指定元素的数据类型list<cql_type>使用时使用中括号将集合元素括起来, [value, value]// 添加元素cqlsh:test>update users set hobbies = hobbies + ['php', 'javascript'] where id = 1;cqlsh:test>update users set hobbies = ['go'] +  hobbies where id = 1;// 删除元素cqlsh:test> update users set hobbies = hobbies - ['php', 'javascript'] where id = 1;// 修改指定位置的元素cqlsh:test> update users set hobbies[0] = 'golang' where id = 1;// 删除指定位置的元素cqlsh:test> delete hobbies[0] from users where id = 1;

• map

声明时使用<cql_type, cql_type>使用时是 {'key':value, 'key':value}// 添加元素cqlsh:test> update users set scores = scores + {'math': 80, 'physics': 88} where id = 1;// 删除元素cqlsh:test> update users set scores = scores - {'math', 'physics'} where id = 1;cqlsh:test> delete scores['english'] from users where id = 1;// 修改元素cqlsh:test> update users set scores['china'] = 100 where id = 1;

• tuple

声明时使用tuple<cql_type, ..., cql_type>使用时使用(value, ...., value)cqlsh:test> update users set tags = ('girl', '$') where id = 1;

• 集合数据过滤contains

// 使用contains 对list、set集合中的元素进行过滤cqlsh:test> select * from users where hobbies contains 'php' allow filtering;// 使用contains key 对map集合进行过滤cqlsh:test> select * from users where scores contains key 'english' allow filtering;

九： 物化视图（Materialized View）

• 创建视图

  CREATE MATERIALIZED VIEW [IF NOT EXISTS] <name> AS    select_statementPRIMARY KEY (column, column)[with table_options];

  For instance:

  cqlsh:test> create materialized view user_view as select id, username, salt, isvip from users where username is not nullprimary key (id, username)with comment = 'users view';cqlsh:test> select * from user_view; id | username | isvip | salt----+----------+-------+--------------------------------------  2 |  mengdee | False | 2718b240-8fd7-11e7-b82c-9340daca092f  4 |     test |  null |                                 null  1 |  mengday |  True | c80b339f-4d2a-4928-9974-603edc65785c

• 修改视图选项
  ALTER MATERIALIZED VIEW <name> WITH table_options

• 删除视图
  DROP MATERIALIZED VIEW [IF EXISTS] <name>

十：函数

• 预定义函数

  • count()：求行数count(*)
  • now(): 当前时间
  • uuid(): 生成一个uuid值
  • min()：求最小值
  • max()：求最大值
  • sum()：求和
  • avg()：求平均数
  • cast(column as cql_type): 转换成其他基本数据类型
  • minTimeuuid(): minTimeuuid(‘2013-02-02 10:00+0000’)
  • maxTimeuuid(): maxTimeuuid(‘2013-01-01 00:05+0000’)

  timeuuid、date、timestamp、bigInt 之间的相互转换函数
  Function name、Input type 、Description

  • toDate timeuuid Converts the timeuuid argument into a date type
  • toDate timestamp Converts the timestamp argument into a date type
  • toTimestamp timeuuid Converts the timeuuid argument into a timestamp type
  • toTimestamp date Converts the date argument into a timestamp type
  • toUnixTimestamp timeuuid Converts the timeuuid argument into a bigInt raw value
  • toUnixTimestamp timestamp Converts the timestamp argument into a bigInt raw value
  • toUnixTimestamp date Converts the date argument into a bigInt raw value
  • dateOf timeuuid Similar to toTimestamp(timeuuid) (DEPRECATED)
  • unixTimestampOf timeuuid Similar totoUnixTimestamp(timeuuid) (DEPRECATED)

cqlsh:test> select cast(height as int) from users;cqlsh:test> select count(*) as count, min(height) as min, max(height) as max, sum(height) as sum, avg(height) as avg, now() as now, uuid() as uuid from users;    

• 自定义函数

create [or replace] function [if not exists] <function_name>(arg1 int, arg2 text, ...)    returns null on null input    returns <cql_type>    language java    as $$// some java codereturn arg;$$;    create function if not exists <keyspace>.<function_name>(argname cql_type)    called on null input    returns <cql_type>    language java    as $$// some java code$$;