CQL操作

最新推荐文章于 2024-09-02 07:06:40 发布

weixin_33690963

最新推荐文章于 2024-09-02 07:06:40 发布

阅读量323

点赞数

文章标签： python 大数据数据库

http://docs.datastax.com/en/cql/3.1/pdf/cql31.pdf

CQL是Cassandra Query Language的缩写，目前作为Cassandra默认并且主要的交互接口。CQL和SQL比较类似，主要的区别是Cassandra不支持join或子查询，除了支持通过Hive进行批处理分析。要说这个Cassandra以前的接口主要是Thrift API，这个没有用过，不做评价。

Cassandra在CQL语言层面支持多种数据类型^[12]。

CQL类型	对应Java类型	描述
ascii	String	ascii字符串
bigint	long	64位整数
blob	ByteBuffer/byte[]	二进制数组
boolean	boolean	布尔
counter	long	计数器，支持原子性的增减，不支持直接赋值
decimal	BigDecimal	高精度小数
double	double	64位浮点数
float	float	32位浮点数
inet	InetAddress	ipv4或ipv6协议的ip地址
int	int	32位整数
list	List	有序的列表
map	Map	键值对
set	Set	集合
text	String	utf-8编码的字符串
timestamp	Date	日期
uuid	UUID	UUID类型
timeuuid	UUID	时间相关的UUID
varchar	string	text的别名
varint	BigInteger	高精度整型

cqlsh语法

cqlsh [options] [host [port]]

python cqlsh [options] [host [port]]

Options

-C, --color

Always use color output.

--debug

Show additional debugging information.

--cqlshrc path

Use an alternative cqlshrc file location, path. (Cassandra 2.1.1)

-e cql_statement, --execute cql_statement

Accept and execute a CQL command in Cassandra 2.1 and later. Useful for saving CQL output to a file.

-f file_name, --file=file_name

Execute commands from file_name, then exit.

-h, --help

Show the online help about these options and exit.

-k keyspace_name

Use the given keyspace. Equivalent to issuing a USE keyspace command immediately after starting cqlsh.

--no-color

Never use color output.

-p password

Authenticate using password. Default = cassandra.

-t transport_factory_name, --transport=transport_factory_name

Use the provided Thrift transport factory function.

-u user_name

Authenticate as user. Default = cassandra.

--version

Show the cqlsh version.

启动CQL命令是cqlsh，我下面的例子是window上的，cassandra版本是2.1.14

示例：

#debug
D:\soft\cassandra\apache-cassandra-2.1.14-bin\bin>cqlsh.bat --debug
Using CQL driver: <module 'cassandra' from 'D:\soft\cassandra\apache-cassandra-2.1.14-bin\bin\..\lib\cassandra-driver-in
ternal-only-2.7.2.zip\cassandra-driver-2.7.2\cassandra\__init__.py'>
Using connect timeout: 5 seconds
Connected to Test Cluster at 127.0.0.1:9042.
[cqlsh 5.0.1 | Cassandra 2.1.14 | CQL spec 3.2.1 | Native protocol v3]
Use HELP for help.
WARNING: pyreadline dependency missing.  Install to enable tab completion.

#version
D:\soft\cassandra\apache-cassandra-2.1.14-bin\bin>cqlsh.bat --version
cqlsh 5.0.1
#Saving CQL output in a file导出
D:\soft\cassandra\apache-cassandra-2.1.14-bin\bin>cqlsh.bat -e "select * from duansf.users">myoutput.txt

导出的文件如下：

D:\soft\cassandra\apache-cassandra-2.1.14-bin\bin>cqlsh.bat

结果：cqlsh Can't detect Python version!

安装python，我安装的是64位的2.7版本，并配置下环境变量path中增加python的安装根路径。安装好后再执行cqlsh.bat

D:\soft\cassandra\apache-cassandra-2.1.14-bin\bin>cqlsh.bat

D:\soft\cassandra\apache-cassandra-2.1.14-bin\bin>cqlsh.bat
Connected to Test Cluster at 127.0.0.1:9042.
[cqlsh 5.0.1 | Cassandra 2.1.14 | CQL spec 3.2.1 | Native protocol v3]
Use HELP for help.
WARNING: pyreadline dependency missing.  Install to enable tab completion.

一、创建keyspace

作为对照，你可以把keyspace理解成一个SQL数据库实例，当然它们毕竟是不同的：Cassandra的keyspace是用来定义数据是如何在节点间复制的。通常情况下，应该为一个应用程序建立一个keyspace。

CREATE KEYSPACE IF NOT EXISTS pimin_net
WITH REPLICATION = {'class': 'SimpleStrategy','replication_factor':1};

上面语句的意思是判断是否存在keyspace，如果不存在则建立keyspace；使用的副本策略是简单策略，复制因子是1。暂时先不管里面深层次的东西，我们先按照简单原则实现。

二、创建表

虽然说Cassandra是面向列的分布式数据库，但是它也有表的概念。创建之前先use pimin_net。

USE pimin_net;
 
CREATE TABLE users (
id int,
user_name varchar,
PRIMARY KEY (id) );

这样就建立了一张用户表，为了简单起见，就只有两个字段，看起来和oracle、mysql这些是不是很像？

三、对表的CRUD

已经有了一张用户表，我们就向里面插入一些数据，对它进行查询、更新和删除操作。

INSERT INTO users (id,user_name) VALUES (1,'china');
INSERT INTO users (id,user_name) VALUES (2,'taiwan');
SELECT * FROM users;

结果：

cqlsh:pimin_net> SELECT * FROM users;

 id | user_name
----+-----------

(0 rows)
cqlsh:pimin_net> INSERT INTO users (id,user_name) VALUES (1,'china');
cqlsh:pimin_net> INSERT INTO users (id,user_name) VALUES (2,'taiwan');
cqlsh:pimin_net> SELECT * FROM users;

 id | user_name
----+-----------
  1 |     china
  2 |    taiwan

(2 rows)
cqlsh:pimin_net>

UPDATE users SET user_name = 'china2014' WHERE id = 1;
SELECT * FROM users;
DELETE FROM users WHERE id = 1;
SELECT * FROM users;

结果：

cqlsh:pimin_net> UPDATE users SET user_name = 'china2014' WHERE id = 1;
cqlsh:pimin_net> SELECT * FROM users;

 id | user_name
----+-----------
  1 | china2014
  2 |    taiwan

(2 rows)
cqlsh:pimin_net> DELETE FROM users WHERE id = 1;
cqlsh:pimin_net> SELECT * FROM users;

 id | user_name
----+-----------
  2 |    taiwan

(1 rows)
cqlsh:pimin_net>

重要：不同于传统的RDBMS，Cassandra不能使用DELETE FROM users;这样的表达式，必须有WHERE条件！

重要：不同于传统的RDBMS，Cassandra不能使用DELETE FROM users;这样的表达式，必须有WHERE条件！

示例2:

cqlsh:usermanager> use duansf

1.创建keyspace

cqlsh:usermanager> create keyspace duansf WITH REPLICATION = {'class': 'SimpleStrategy','replication_factor':1};

创建一个名为duansf的keyspace，副本策略SimpleStrategy，复制因子为1.

2.创建Column family

cqlsh>use duansf;
cqlsh:duansf> create columnfamily users(
           key varchar primary key,
           password varchar,
           gender varchar,
           session_token varchar,
           state varchar,
           birth_year bigint);

创建一个名为users的column family

... KEY varchar PRIMARY KEY,该columnfamily下有一个Key

和5列

... password varchar,

... gende rvarchar,

... session_token varchar,

... state varchar,

... birth_year bigint);

3．插入和检索Columns

cqlsh:duansf> insert into users(key,password) values('jsmith','chadsfl') using ttl 86400;

向passwod这一列插入数据

cqlsh:duansf> select * from users where key='jsmith';

 key    | birth_year | gender | password | session_token | state
--------+------------+--------+----------+---------------+-------
 jsmith |       null |   null |  chadsfl |          null |  null

(1 rows)
cqlsh:duansf>

向session_token这一列插入数据

cqlsh:duansf> insert into users(key,session_token) values('jsmith','test') using ttl 86400;
cqlsh:duansf> select * from users where key='jsmith';

 key    | birth_year | gender | password | session_token | state
--------+------------+--------+----------+---------------+-------
 jsmith |       null |   null |  chadsfl |          test |  null

3.向Column family中增加Column

cqlsh:duansf> alter table user add coupon_code varchar;

注意：其他已经存在的列不会进行更新。

4. 更改Column的元数据

cqlsh:duansf> alter table users alter coupon_code type int;
ConfigurationException: <ErrorMessage code=2300 [Query invalid because of configuration issue] message="Cannot change co
upon_code from type text to type int: types are incompatible.">

注意：已经存在的数据不会转成此类型，新插入的数据才是该类型的。

5.使用TTL属性设置列的到期时间

cqlsh:duansf> update users using ttl 432000 set password='asldkjsfsdf' where key = 'jsmith';

更新密码列的到期时间为5天。

6.删除列元数据

cqlsh:duansf> alter table users drop coupon_code;

7.索引Column

cqlsh:duansf> create index state_key on users(state);
cqlsh:duansf> create index birth_year_key on users(birth_year);

8.删除列或者行

cqlsh:duansf> delete session_token from users where key='jsmith';  //删除session_token列
cqlsh:duansf> select * from users;

 key    | birth_year | gender | password    | session_token | state
--------+------------+--------+-------------+---------------+-------
 jsmith |       null |   null | asldkjsfsdf |          null |  null

(1 rows)
cqlsh:duansf> delete from users where key='jsmith';  //删除key=jsmith的行
cqlsh:duansf> select * from users;

 key | birth_year | gender | password | session_token | state
-----+------------+--------+----------+---------------+-------

(0 rows)
cqlsh:duansf>

9. 删除columnfamily和keyspace

cqlsh:duansf> drop columnfamily users;
cqlsh:duansf> insert into users(key,password) values('jsmith','chadsfl') using ttl 86400;
InvalidRequest: code=2200 [Invalid query] message="unconfigured columnfamily users"
cqlsh:duansf>

删除keyspace

cqlsh:duansf> drop keyspace duansf;
cqlsh:duansf> use duansf;
InvalidRequest: code=2200 [Invalid query] message="Keyspace 'duansf' does not exist"
cqlsh:duansf>

10.查看结构信息

cqlsh:usermanager> desc users;

CREATE TABLE usermanager.users (
    key blob PRIMARY KEY,
    age text,
    name text
) WITH bloom_filter_fp_chance = 0.01
    AND caching = '{"keys":"ALL", "rows_per_partition":"NONE"}'
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy'}
    AND compression = {'sstable_compression': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99.0PERCENTILE';