Cassandra cqlsh命令大全

一：CQL 简介

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

二：Shell 命令

// 登录shell
D:\Java\apache-cassandra-3.11.0\bin>cqlsh
D:\Java\apache-cassandra-3.11.0\bin>cqlsh --help
D:\Java\apache-cassandra-3.11.0\bin>cqlsh --version

// 使用用户名和密码登录，默认用户名和密码都是cassandra
D:\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 enabled
cqlsh: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         | 1
age        | 26
birthday   | 1990-10-26
createtime | 2017-09-02 12:04:00.023000+0000
height     | 135.5
hobbies    | ['java', 'iOS']
ip         | 192.168.1.1
isvip      | True
salt       | c80b339f-4d2a-4928-9974-603edc65785c
scores     | {'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]

// 关闭shell
cqlsh:test> exit

// 退出shell
cqlsh: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 = value
WHERE <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_statement
PRIMARY 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 null
primary 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 fro
m 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 code
		return 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
	$$;