CQL操作
http://docs.datastax.com/en/cql/3.1/pdf/cql31.pdf
CQL是Cassandra Query Language的縮寫，目前作為Cassandra默認(rèn)并且主要的交互接口。CQL和SQL比較類似，主要的區(qū)別是Cassandra不支持join或子查詢，除了支持通過Hive進(jìn)行批處理分析。要說這個(gè)Cassandra以前的接口主要是Thrift API，這個(gè)沒有用過，不做評(píng)價(jià)。
Cassandra在CQL語(yǔ)言層面支持多種數(shù)據(jù)類型[12]。
| CQL類型 | 對(duì)應(yīng)Java類型 | 描述 |
|---|---|---|
| ascii | String | ascii字符串 |
| bigint | long | 64位整數(shù) |
| blob | ByteBuffer/byte[] | 二進(jìn)制數(shù)組 |
| boolean | boolean | 布爾 |
| counter | long | 計(jì)數(shù)器，支持原子性的增減，不支持直接賦值 |
| decimal | BigDecimal | 高精度小數(shù) |
| double | double | 64位浮點(diǎn)數(shù) |
| float | float | 32位浮點(diǎn)數(shù) |
| inet | InetAddress | ipv4或ipv6協(xié)議的ip地址 |
| int | int | 32位整數(shù) |
| list | List | 有序的列表 |
| map | Map | 鍵值對(duì) |
| set | Set | 集合 |
| text | String | utf-8編碼的字符串 |
| timestamp | Date | 日期 |
| uuid | UUID | UUID類型 |
| timeuuid | UUID | 時(shí)間相關(guān)的UUID |
| varchar | string | text的別名 |
| varint | BigInteger | 高精度整型 |
cqlsh語(yǔ)法
cqlsh [options] [host [port]]
python cqlsh [options] [host [port]]
Options
-C, --colorAlways use color output.--debugShow additional debugging information.--cqlshrc pathUse an alternative cqlshrc file location, path. (Cassandra 2.1.1)-e cql_statement, --execute cql_statementAccept and execute a CQL command in Cassandra 2.1 and later. Useful forsaving CQL outputto a file.-f file_name, --file=file_nameExecute commands from file_name, then exit.-h, --helpShow the online help about these options and exit.-k keyspace_nameUse the given keyspace. Equivalent to issuing a USEkeyspacecommand immediately after starting cqlsh.--no-colorNever use color output.-p passwordAuthenticate using password. Default = cassandra.-t transport_factory_name, --transport=transport_factory_nameUse the provided Thrift transport factory function.-u user_nameAuthenticate as user. Default = cassandra.--versionShow the cqlsh version.
啟動(dòng)CQL命令是cqlsh，我下面的例子是window上的，cassandra版本是2.1.14
示例：
#debug D:softcassandraapache-cassandra-2.1.14-binin>cqlsh.bat --debug Using CQL driver: <module 'cassandra' from 'D:softcassandraapache-cassandra-2.1.14-binin..libcassandra-driver-in ternal-only-2.7.2.zipcassandra-driver-2.7.2cassandra\__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:softcassandraapache-cassandra-2.1.14-binin>cqlsh.bat --version cqlsh 5.0.1 #Saving CQL output in a file導(dǎo)出 D:softcassandraapache-cassandra-2.1.14-binin>cqlsh.bat -e "select * from duansf.users">myoutput.txt
導(dǎo)出的文件如下：
D:softcassandraapache-cassandra-2.1.14-binin>cqlsh.bat
結(jié)果：cqlsh Can't detect Python version!
安裝python，我安裝的是64位的2.7版本，并配置下環(huán)境變量path中增加python的安裝根路徑。安裝好后再執(zhí)行cqlsh.bat
D:softcassandraapache-cassandra-2.1.14-binin>cqlsh.bat
D:softcassandraapache-cassandra-2.1.14-binin>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.
一、創(chuàng)建keyspace
作為對(duì)照，你可以把keyspace理解成一個(gè)SQL數(shù)據(jù)庫(kù)實(shí)例，當(dāng)然它們畢竟是不同的：Cassandra的keyspace是用來定義數(shù)據(jù)是如何在節(jié)點(diǎn)間復(fù)制的。通常情況下，應(yīng)該為一個(gè)應(yīng)用程序建立一個(gè)keyspace。
CREATE KEYSPACE IF NOT EXISTS pimin_net
WITH REPLICATION = {'class': 'SimpleStrategy','replication_factor':1};
上面語(yǔ)句的意思是判斷是否存在keyspace，如果不存在則建立keyspace；使用的副本策略是簡(jiǎn)單策略，復(fù)制因子是1。暫時(shí)先不管里面深層次的東西，我們先按照簡(jiǎn)單原則實(shí)現(xiàn)。
二、創(chuàng)建表
雖然說Cassandra是面向列的分布式數(shù)據(jù)庫(kù)，但是它也有表的概念。創(chuàng)建之前先use pimin_net。
USE pimin_net; CREATE TABLE users ( id int, user_name varchar, PRIMARY KEY (id) );
這樣就建立了一張用戶表，為了簡(jiǎn)單起見，就只有兩個(gè)字段，看起來和oracle、mysql這些是不是很像？
三、對(duì)表的CRUD
已經(jīng)有了一張用戶表，我們就向里面插入一些數(shù)據(jù)，對(duì)它進(jìn)行查詢、更新和刪除操作。
INSERT INTO users (id,user_name) VALUES (1,'china'); INSERT INTO users (id,user_name) VALUES (2,'taiwan'); SELECT * FROM users;
結(jié)果：
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;
結(jié)果：
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>
重要：不同于傳統(tǒng)的RDBMS，Cassandra不能使用DELETE FROM users;這樣的表達(dá)式，必須有WHERE條件！
重要：不同于傳統(tǒng)的RDBMS，Cassandra不能使用DELETE FROM users;這樣的表達(dá)式，必須有WHERE條件！
示例2:
cqlsh:usermanager> use duansf
1.創(chuàng)建keyspace
cqlsh:usermanager> create keyspace duansf WITH REPLICATION = {'class': 'SimpleStrategy','replication_factor':1};
創(chuàng)建一個(gè)名為duansf的keyspace，副本策略SimpleStrategy，復(fù)制因子為1.
2.創(chuàng)建Columnfamily
cqlsh>use duansf;
cqlsh:duansf> create columnfamily users(
key varchar primary key,
password varchar,
gender varchar,
session_token varchar,
state varchar,
birth_year bigint);
創(chuàng)建一個(gè)名為users的columnfamily
...KEYvarcharPRIMARYKEY,該columnfamily下有一個(gè)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這一列插入數(shù)據(jù)
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這一列插入數(shù)據(jù)
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.向Columnfamily中增加Column
cqlsh:duansf> alter table user add coupon_code varchar;
注意：其他已經(jīng)存在的列不會(huì)進(jìn)行更新。
4.更改Column的元數(shù)據(jù)
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.">
注意：已經(jīng)存在的數(shù)據(jù)不會(huì)轉(zhuǎn)成此類型，新插入的數(shù)據(jù)才是該類型的。
5.使用TTL屬性設(shè)置列的到期時(shí)間
cqlsh:duansf> update users using ttl 432000 set password='asldkjsfsdf' where key = 'jsmith';
更新密碼列的到期時(shí)間為5天。
6.刪除列元數(shù)據(jù)
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.查看結(jié)構(gòu)信息
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';
總結(jié)
- 上一篇： 大连潜艇部队二期士官学历要求
- 下一篇： 广岛原子弹爆炸对中国留学生的影响？