Apache Cassandra入门

bigdata

Hello Cassandra

Hello Cassandra(单机模式)

http://wiki.apache.org/cassandra/GettingStarted

添加环境变量并source生效,使得可以在任意位置执行cassandra/bin安装目录下的命令 

1
2
export CASSANDRA_HOME="/Users/zhengqh/Soft/apache-cassandra-2.0.16"
export PATH="$CASSANDRA_HOME/bin:$PATH"

前台启动Cassandra进程, sudo cassandra -f

日志输出: 创建了一些系统目录,并监听了thrift

1
2
3
4
5
INFO 09:24:50,569 Starting listening for CQL clients on localhost/127.0.0.1:9042...
INFO 09:24:50,691 Using TFramedTransport with a max frame size of 15728640 bytes.
INFO 09:24:50,693 Binding thrift service to localhost/127.0.0.1:9160
INFO 09:24:50,727 Using synchronous/threadpool thrift server on localhost : 9160
INFO 09:24:50,727 Listening for thrift clients...

启动一个新的终端, 启动客户端查询: cqlsh

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE KEYSPACE mykeyspace  
WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };

USE mykeyspace;  
CREATE TABLE users (
  user_id int PRIMARY KEY,
  fname text,
  lname text
);

INSERT INTO users (user_id,  fname, lname) VALUES (1745, 'john', 'smith');  
INSERT INTO users (user_id,  fname, lname) VALUES (1744, 'john', 'doe');  
INSERT INTO users (user_id,  fname, lname) VALUES (1746, 'john', 'smith');

CREATE INDEX ON users (lname);  
SELECT * FROM users WHERE lname = 'smith';

下面是详细的步骤和输出: 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
$ cqlsh
Connected to Test Cluster at localhost:9160.
[cqlsh 4.1.1 | Cassandra 2.0.16 | CQL spec 3.1.1 | Thrift protocol 19.39.0]
Use HELP for help.
cqlsh> CREATE KEYSPACE mykeyspace
   ... WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };
cqlsh> USE mykeyspace;
cqlsh:mykeyspace>
cqlsh:mykeyspace> CREATE TABLE users (
              ...   user_id int PRIMARY KEY,
              ...   fname text,
              ...   lname text
              ... );
cqlsh:mykeyspace> INSERT INTO users (user_id,  fname, lname) VALUES (1745, 'john', 'smith');
cqlsh:mykeyspace> INSERT INTO users (user_id,  fname, lname) VALUES (1744, 'john', 'doe');
cqlsh:mykeyspace> INSERT INTO users (user_id,  fname, lname) VALUES (1746, 'john', 'smith');
cqlsh:mykeyspace> SELECT * FROM users;

 user_id | fname | lname
---------+-------+-------
    1745 |  john | smith
    1744 |  john |   doe
    1746 |  john | smith

cqlsh:mykeyspace> SELECT * FROM users WHERE lname = 'smith';
Bad Request: No indexed columns present in by-columns clause with Equal operator
cqlsh:mykeyspace> CREATE INDEX ON users (lname);
cqlsh:mykeyspace> SELECT * FROM users WHERE lname = 'smith';

 user_id | fname | lname
---------+-------+-------
    1745 |  john | smith
    1746 |  john | smith

观察服务端的输出: 分别创建了keyspace,table,index. keyspace可以理解为DataBase. 

1
2
3
4
INFO 09:29:21,981 Create new Keyspace: mykeyspace, rep strategy:SimpleStrategy{}, strategy_options: {replication_factor=1}, durable_writes: true
INFO 09:29:45,822 Initializing mykeyspace.users
INFO 09:31:21,102 Initializing mykeyspace.users.users_lname_idx
INFO 09:31:21,230 Index build of users.users_lname_idx complete

使用nodetool工具查看集群状态: nodetool -h localhost -p 7199 status,或者直接bin/nodetool status

1
2
3
4
5
6
7
Note: Ownership information does not include topology; for complete information, specify a keyspace
Datacenter: datacenter1
=======================
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
--  Address    Load       Tokens  Owns   Host ID                               Rack
UN  127.0.0.1  77.2 KB    256     100.0%  9605a4d0-99ca-4582-8a7d-49a7cb9bd942  rack1

由于我们在$CASSANDRA_HOME/conf/cassandra.yaml中修改了data路径
可以看到在cql中创建的keyspace,table,index都对应了本地的目录和文件. 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
➜  ~  cd data/cassandra
➜  cassandra  ll
drwxr-xr-x   4 root  staff   136B  6 25 09:24 commitlog
drwxr-xr-x   3 root  staff   102B  6 25 09:29 mykeyspace
drwxr-xr-x  18 root  staff   612B  6 25 09:24 system
drwxr-xr-x   4 root  staff   136B  6 25 09:24 system_traces
➜  cassandra  ll mykeyspace
drwxr-xr-x  16 root  staff   544B  6 25 09:31 users
➜  cassandra  ll mykeyspace/users
-rw-r--r--  1 root  staff    43B  6 25 09:31 mykeyspace-users-jb-1-CompressionInfo.db
-rw-r--r--  1 root  staff   134B  6 25 09:31 mykeyspace-users-jb-1-Data.db
-rw-r--r--  1 root  staff    16B  6 25 09:31 mykeyspace-users-jb-1-Filter.db
-rw-r--r--  1 root  staff    54B  6 25 09:31 mykeyspace-users-jb-1-Index.db
-rw-r--r--  1 root  staff   4.3K  6 25 09:31 mykeyspace-users-jb-1-Statistics.db
-rw-r--r--  1 root  staff    80B  6 25 09:31 mykeyspace-users-jb-1-Summary.db
-rw-r--r--  1 root  staff    79B  6 25 09:31 mykeyspace-users-jb-1-TOC.txt
-rw-r--r--  1 root  staff    43B  6 25 09:31 mykeyspace-users.users_lname_idx-jb-1-CompressionInfo.db
-rw-r--r--  1 root  staff    80B  6 25 09:31 mykeyspace-users.users_lname_idx-jb-1-Data.db
-rw-r--r--  1 root  staff    16B  6 25 09:31 mykeyspace-users.users_lname_idx-jb-1-Filter.db
-rw-r--r--  1 root  staff    36B  6 25 09:31 mykeyspace-users.users_lname_idx-jb-1-Index.db
-rw-r--r--  1 root  staff   4.3K  6 25 09:31 mykeyspace-users.users_lname_idx-jb-1-Statistics.db
-rw-r--r--  1 root  staff    79B  6 25 09:31 mykeyspace-users.users_lname_idx-jb-1-Summary.db
-rw-r--r--  1 root  staff    79B  6 25 09:31 mykeyspace-users.users_lname_idx-jb-1-TOC.txt

kill cassandra:

pkill -f CassandraDaemon
ps -ef | grep CassandraDeameon后sudo kill -9 pid

终端Ctrl+C中断进程输出

1
2
3
4
5
6
^C INFO 09:48:33,868 Stop listening to thrift clients
 INFO 09:48:33,909 Stop listening for CQL clients
 INFO 09:48:33,911 Announcing shutdown
 INFO 09:48:33,912 Node localhost/127.0.0.1 state jump to normal
 INFO 09:48:35,952 Waiting for messaging service to quiesce
 INFO 09:48:35,956 MessagingService has terminated the accept() thread

配置文件默认值

配置项 说明 默认值 配置文件
data_file_directories 数据 /var/lib/cassandra/data cassandra.yaml
commitlog_directory 提交日志 /var/lib/cassandra/commitlog cassandra.yaml
saved_caches_directory 缓存 /var/lib/cassandra/saved_caches cassandra.yaml
log4j.appender.R.File 日志 /var/log/cassandra log4j-server.properties

Cassandra Cluster

http://blog.csdn.net/rzliuwei/article/details/40430031

vi cassandra.yaml

1
2
3
4
5
6
7
8
seed_provider:
    - class_name: org.apache.cassandra.locator.SimpleSeedProvider
      parameters:
          - seeds: "192.168.6.52,192.168.6.53"

listen_address: 192.168.6.52

rpc_address:

注意:

  1. seeds 可以选择集群中比较稳定的机器, 不需要写下所有的节点.
  2. listen_address在每台机器上都要修改成自己本机的IP地址.
  3. rpc_address修改为空,或者跟listen_address一样,为本机IP.

将cassandra整个目录都分发到集群中(更改目录权限,建立软链接,创建日志目录,更改日志目录权限).

1
2
3
4
5
scp -r apache-cassandra-2.0.16 qihuang.zheng@192.168.6.52:/usr/install 
sudo chown admin:admin -R /usr/install/apache-cassandra-2.0.16
sudo -u admin ln -s /usr/install/apache-cassandra-2.0.16 /usr/install/cassandra
sudo mkdir /var/log/cassandra
sudo chown admin:admin -R /var/log/cassandra

在每个节点都启动Cassandra服务: 先启动seeds,然后启动其他节点

1
sudo -u admin /usr/install/cassandra/bin/cassandra

随便在某一台机器安装了cassandra的节点查看整个集群所有节点的状态:

1
2
3
4
5
6
7
8
9
10
11
[qihuang.zheng@dp0652 apache-cassandra-2.0.16]$ bin/nodetool status
Datacenter: datacenter1
=======================
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
--  Address       Load       Tokens  Owns (effective)  Host ID                               Rack
UN  192.168.6.52  46.21 KB   256     41.9%             69d3a2ab-cad8-4fdc-b609-7b9c0ce3243a  rack1
UN  192.168.6.53  62.46 KB   256     40.7%             dc884efd-5f26-4746-b5c6-73ac7d446796  rack1
UN  192.168.6.55  76.73 KB   256     37.9%             180e65c1-7ac1-4a2c-b8a6-c9ae3dc08de1  rack1
UN  192.168.6.56  14.21 KB   256     40.9%             6a1a7d22-ffdf-4598-8402-fe0ffa62663d  rack1
UN  192.168.6.57  14.21 KB   256     38.6%             d35ceda3-f8b2-49a6-98fd-390b2341481c  rack1

下面是依次启动52~57, 在seeds节点52可以看到下面的输出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
...
ERROR 10:18:22,502 Directory /var/lib/cassandra/data doesn't exist
ERROR 10:18:22,509 Directory /var/lib/cassandra/commitlog doesn't exist
ERROR 10:18:22,510 Directory /var/lib/cassandra/saved_caches doesn't exist
...

 INFO 10:18:25,654 Node /192.168.6.52 state jump to normal
 INFO 10:18:25,761 Waiting for gossip to settle before accepting client requests...
 INFO 10:18:25,791 Compacted 4 sstables to [/var/lib/cassandra/data/system/local/system-local-jb-5,].  5,935 bytes to 5,784 (~97% of original) in 187ms = 0.029498MB/s.  4 total partitions merged to 1.  Partition merge counts were {4:1, }
 INFO 10:18:33,763 No gossip backlog; proceeding
 INFO 10:18:33,930 Starting listening for CQL clients on /192.168.6.52:9042...
 INFO 10:18:34,015 Using TFramedTransport with a max frame size of 15728640 bytes.
 INFO 10:18:34,017 Binding thrift service to /192.168.6.52:9160
 INFO 10:18:34,031 Using synchronous/threadpool thrift server on dp0652 : 9160
 INFO 10:18:34,032 Listening for thrift clients...
 INFO 10:19:59,689 Handshaking version with /192.168.6.53
 INFO 10:20:00,551 Node /192.168.6.53 is now part of the cluster
 INFO 10:20:00,557 InetAddress /192.168.6.53 is now UP

 INFO 10:21:29,743 Handshaking version with /192.168.6.55
 INFO 10:21:32,596 Node /192.168.6.55 is now part of the cluster
 INFO 10:21:32,599 InetAddress /192.168.6.55 is now UP
 INFO 10:22:06,329 [Stream #f921b340-1ae0-11e5-ab43-1146c6cf1690] Received streaming plan for Bootstrap
 INFO 10:22:06,505 [Stream #f921b340-1ae0-11e5-ab43-1146c6cf1690] Session with /192.168.6.55 is complete
 INFO 10:22:06,512 [Stream #f921b340-1ae0-11e5-ab43-1146c6cf1690] All sessions completed

 INFO 10:22:32,683 Handshaking version with /192.168.6.56
 INFO 10:22:34,681 Node /192.168.6.56 is now part of the cluster
 INFO 10:22:34,684 InetAddress /192.168.6.56 is now UP
 INFO 10:22:42,996 Handshaking version with /192.168.6.57
 INFO 10:22:45,688 Node /192.168.6.57 is now part of the cluster
 INFO 10:22:45,697 InetAddress /192.168.6.57 is now UP
 INFO 10:23:07,647 [Stream #1db71650-1ae1-11e5-bd7c-33c4208ae8ae] Received streaming plan for Bootstrap
 INFO 10:23:07,795 [Stream #1db71650-1ae1-11e5-bd7c-33c4208ae8ae] Session with /192.168.6.56 is complete
 INFO 10:23:07,796 [Stream #1db71650-1ae1-11e5-bd7c-33c4208ae8ae] All sessions completed
 INFO 10:23:20,299 [Stream #253d8260-1ae1-11e5-8387-4d9d1ede5533] Received streaming plan for Bootstrap
 INFO 10:23:20,415 [Stream #253d8260-1ae1-11e5-8387-4d9d1ede5533] Session with /192.168.6.57 is complete
 INFO 10:23:20,416 [Stream #253d8260-1ae1-11e5-8387-4d9d1ede5533] All sessions completed

seed节点53的输出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
INFO 10:20:09,212 Listening for thrift clients...
INFO 10:21:29,678 Handshaking version with /192.168.6.55
INFO 10:21:31,965 Node /192.168.6.55 is now part of the cluster
INFO 10:21:31,968 InetAddress /192.168.6.55 is now UP
INFO 10:22:32,618 Handshaking version with /192.168.6.56
INFO 10:22:36,055 Node /192.168.6.56 is now part of the cluster
INFO 10:22:36,058 InetAddress /192.168.6.56 is now UP
INFO 10:22:42,930 Handshaking version with /192.168.6.57
INFO 10:22:44,965 Node /192.168.6.57 is now part of the cluster
INFO 10:22:44,967 InetAddress /192.168.6.57 is now UP
INFO 10:23:07,611 [Stream #1db71650-1ae1-11e5-bd7c-33c4208ae8ae] Received streaming plan for Bootstrap
INFO 10:23:07,684 [Stream #1db71650-1ae1-11e5-bd7c-33c4208ae8ae] Session with /192.168.6.56 is complete
INFO 10:23:07,691 [Stream #1db71650-1ae1-11e5-bd7c-33c4208ae8ae] All sessions completed
INFO 10:23:20,230 [Stream #253d8260-1ae1-11e5-8387-4d9d1ede5533] Received streaming plan for Bootstrap
INFO 10:23:20,312 [Stream #253d8260-1ae1-11e5-8387-4d9d1ede5533] Session with /192.168.6.57 is complete
INFO 10:23:20,313 [Stream #253d8260-1ae1-11e5-8387-4d9d1ede5533] All sessions completed

由于前面两个seeds节点我们采用了-f启动, 所以我们关掉53节点, 在52上观测到53当掉了

1
2
3
INFO 10:37:19,533 InetAddress /192.168.6.53 is now DOWN
INFO 10:37:20,001 Handshaking version with /192.168.6.53
INFO 10:37:28,042 GC for ParNew: 316 ms for 1 collections, 201429600 used; max is 8375238656

此时再观察集群的状态, 53的第一列状态为DN(DownNode) 

1
2
3
4
5
6
--  Address       Load       Tokens  Owns (effective)  Host ID                               Rack
UN  192.168.6.52  46.21 KB   256     41.9%             69d3a2ab-cad8-4fdc-b609-7b9c0ce3243a  rack1
DN  192.168.6.53  62.46 KB   256     40.7%             dc884efd-5f26-4746-b5c6-73ac7d446796  rack1
UN  192.168.6.55  76.73 KB   256     37.9%             180e65c1-7ac1-4a2c-b8a6-c9ae3dc08de1  rack1
UN  192.168.6.56  96.73 KB   256     40.9%             6a1a7d22-ffdf-4598-8402-fe0ffa62663d  rack1
UN  192.168.6.57  97.07 KB   256     38.6%             d35ceda3-f8b2-49a6-98fd-390b2341481c  rack1

在集群中任意一个节点建表和插入数据, 注意不同的是副本因子replication_factor=3 

1
2
3
4
5
6
7
8
9
10
11
12
CREATE KEYSPACE mykeyspace
WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 3 };

USE mykeyspace;
CREATE TABLE users ( user_id int PRIMARY KEY, fname text, lname text );

INSERT INTO users (user_id, fname, lname) VALUES (1745, 'john', 'smith');
INSERT INTO users (user_id, fname, lname) VALUES (1744, 'john', 'doe');
INSERT INTO users (user_id, fname, lname) VALUES (1746, 'john', 'smith');

CREATE INDEX ON users (lname);
SELECT * FROM users WHERE lname = 'smith';

在另外的节点上查看数据,注意cqlsh要加上host地址,随便集群中的哪个节点都可以,但不能都不跟. 

1
2
3
4
5
6
7
8
[qihuang.zheng@dp0655 apache-cassandra-2.0.16]$ bin/cqlsh 192.168.6.55
Connected to Test Cluster at 192.168.6.55:9160.
cqlsh> use mykeyspace;
cqlsh:mykeyspace> SELECT * FROM users WHERE lname = 'smith';
 user_id | fname | lname
---------+-------+-------
    1745 |  john | smith
    1746 |  john | smith

PrimaryKey

Primary Key包含了Partition Key和Cluster Key两种类型.

Partition Key: controls how the data is spread around the cluster
Cluster Key: controls how the data is sorted on disk

Rows are spread around the cluster based on a hash of the partition key, which is the first element of the PRIMARY KEY
每一行数据分散在集群中, 根据的是partition-key的hash值. partition-key指的是primary-key的第一个字段(第一部分,可能不止一个字段哦).

Primary keys can be composite; the first part of the primary key is the partition key and determines the primary node for the partition
主键可以是复合的, 主键的第一部分是分区键,决定了分区的主节点.

单一主键

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE users(
  username varchar,
  email varchar,
  birth timestamp,
  PRIMARY KEY(username)
);
INSERT INTO users(username,email,birth)VALUES('john.doe','johndoe@gmail.com','1970-01-01');
select * from users;
 username | birth                    | email
----------+--------------------------+-------------------
 john.doe | 1970-01-01 01:00:00+0800 | johndoe@gmail.com

复合主键(Composite Primary Key)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE user_attr(
  username varchar,
  key varchar,
  value varchar,
  PRIMARY KEY(username,key)
);
INSERT INTO user_attr(username,key,value)VALUES('john.doe','married_to','janedoe');
INSERT INTO user_attr(username,key,value)VALUES('john.doe','last_login','2014-01-04T12:00:00');
INSERT INTO user_attr(username,key,value)VALUES('anna','last_login','2014-04-03T13:00:00');

select * from user_attr;
 username | key        | value
----------+------------+---------------------
     anna | last_login | 2014-04-03T13:00:00
 john.doe | last_login | 2014-01-04T12:00:00
 john.doe | married_to |             janedoe

复合主键示例中, username是Partition Key, key是Clustering Columns.
PartitionKey是未排序的, 而Clustering Columns是会进行排序的.
比如相同partition-key的username=john.doe,其中clustring columns的顺序是last_login先于married_to.因为字母表中l在m前面.

Note that the PRIMARY KEY has two components: username, which is the partitioning key, and key, which is called the clustering key.
This will give us one partition per username每个用户只有一个分区. Within a particular partition (user), rows will be ordered by key.

复合主键在磁盘上的Partition Layout(cluster column1到2分别根据cluster column进行排序):

primary key cluster column 1 cluster column 2
username:anna last_login:2014-04-03T13:00:00 -
username:john.doe last_login:2014-01-04T12:00:00 married_to:janedoe

复合分区键(Compound Partition Key,随机)

If we have thousands or millions of small groups with hundreds of users each, we’ll get a pretty even spread(分区键很多,聚合键数量少).
But if there’s one group with millions of users in it(聚合键数量很多), the entire burden will be shouldered by one node (or one set of replicas)
The basic technique is to add another column to the PRIMARY KEY to form a compound partition key.

上面的表结构对于一个用户有较少的字段,并且用户数很多的情况下, 可以均衡地在集群中分布数据. 但是如果有一个用户字段数(key)非常多,则节点的负载会很重.
可以给partition-key添加一个字段组成复合primary-key,就可以比较均衡地分布数据. 但是这种代价是读取时由于一个username由多个分区组成,读负载会增大. 

1
2
3
4
5
6
7
8
CREATE TABLE groups (
    groupname text,
    username text,
    email text,
    age int,
    hash_prefix int,
    PRIMARY KEY ((groupname, hash_prefix), username)
)

The new column hash_prefix, holds a prefix of a hash of the username. For example, it could be the first byte of the hash modulo four.
Together with groupname, these two columns form the compound partition key. Instead of a group residing on one partition,
it’s now spread across four partitions. Our data is more evenly spread out, but we now have to read four times as many partitions.

根据时间范围组合分区键求TopN

1
2
3
4
5
6
7
8
9
CREATE TABLE group_join_dates (
    groupname text,
    joined timeuuid,
    join_date text,
    username text,
    email text,
    age int,
    PRIMARY KEY ((groupname, join_date), joined)
) WITH CLUSTERING ORDER BY (joined DESC)

Each day, a new partition will start. When querying the X newest users, we will first query today’s partition,
then yesterday’s, and so on, until we have X users.

参考文档

数据模型的基本规则: http://www.datastax.com/dev/blog/basic-rules-of-cassandra-data-modeling

Query Performance

  • Single-partition queries are fast!
  • Queries for ranges on clustering columns are fast!
  • Queries for multiple partitions are slow
  • Use secondary indexes with caution

twissandra : Twitter with Cassandra Backend Demo

https://github.com/twissandra/twissandra

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
CREATE TABLE users (
    username text PRIMARY KEY,
    password text
);
CREATE TABLE friends (
    username text,
    friend text,
    since timestamp,
    PRIMARY KEY (username, friend)
);
CREATE TABLE followers (
    username text,
    follower text,
    since timestamp,
    PRIMARY KEY (username, follower)
);
CREATE TABLE tweets (
    tweet_id uuid PRIMARY KEY,
    username text,
    body text
);
CREATE TABLE userline (
    username text,
    time timeuuid,
    tweet_id uuid,
    PRIMARY KEY (username, time)
) WITH CLUSTERING ORDER BY (time DESC);
CREATE TABLE timeline (
    username text,
    time timeuuid,
    tweet_id uuid,
    PRIMARY KEY (username, time)
) WITH CLUSTERING ORDER BY (time DESC);

用户表users的主键是username
朋友friends和粉丝followers的主键是自己的username加上对方的username
推文的主键是UUID
用户的时间线userline和所有的时间线timeline的主键是用户名username和创建时间
时间线的cluster key是time字段,两个时间线的表的排序字段都是desc,因为最新的推文要排列在前面

CQL:TTL & Delete

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
CREATE TABLE emp (
  empID int,
  deptID int,
  first_name varchar,
  last_name varchar,
  PRIMARY KEY (empID, deptID));

插入数据,第二条指定了过去时间=60秒
INSERT INTO emp (empID, deptID, first_name, last_name) VALUES (104, 15, 'jane', 'smith');
INSERT INTO emp (empID, deptID, first_name, last_name) VALUES (105, 17, 'jane', 'smith') USING TTL 60;

cqlsh:mykeyspace> SELECT empid,WRITETIME(first_name),TTL(last_name) from emp;
 empid | ttl(last_name)
-------+----------------
   105 |            35
   104 |           null
过了60秒之后, 再次查询, 只有一条记录104. 105因为60秒过去了,所以被删除. 

更新过期时间, 可以在上面60秒还没有过期的时候执行(如果已经过期了不在了,效果跟上面的一样,那就是插入了)
INSERT INTO emp (empID, deptID, first_name, last_name) VALUES (105, 17, 'miaomiao', 'han') USING TTL 600;
cqlsh:mykeyspace> SELECT empid,TTL(last_name) from emp;
 empid | ttl(last_name)
-------+----------------
   105 |            345
   104 |           null

删除一行记录, 并不会真正删除,查询时,还在!
cqlsh:mykeyspace> delete from emp where empid=104 and deptid=105;
cqlsh:mykeyspace> select * from emp;
 empid | deptid | first_name | last_name
-------+--------+------------+-----------
   105 |     17 |   miaomiao |       han
   104 |     15 |       jane |     smith

删除一列,这一列为null. 但是不能删除主键.
cqlsh:mykeyspace> DELETE last_name FROM emp WHERE empid=104 and deptid=15;
cqlsh:mykeyspace> 
cqlsh:mykeyspace> select * from emp;
 empid | deptid | first_name | last_name
-------+--------+------------+-----------
   105 |     17 |   miaomiao |       han
   104 |     15 |       jane |      null

上面是对一行数据的所有列都设置了TTL(当然除了primary-key外), 还可以针对一些列设置TTL.

http://cjwebb.github.io/blog/2015/03/02/cassandra-ttl-is-per-column/

CQL Collection

http://www.datastax.com/dev/blog/cql3_collections

集合Set: 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
CREATE TABLE users (
  user_id text PRIMARY KEY,
  first_name text,
  last_name text,
  emails set<text>
);

列的顺序并不一定是插入的顺序. 而是按照字母顺序, 比如e,f,l.  集合set中的元素顺序也是字母顺序.
插入的是f先于baggins, 而显示时因为字母表中b在f之前, 所以为baggins,f.
cqlsh:test> INSERT INTO users (user_id, first_name, last_name, emails) VALUES('frodo','Frodo', 'Baggins', {'f@baggins.com', 'baggins@gmail.com'});
cqlsh:test> select * from users;
 user_id | emails                                 | first_name | last_name
---------+----------------------------------------+------------+-----------
   frodo | {'baggins@gmail.com', 'f@baggins.com'} |      Frodo |   Baggins

添加一个元素, fb因为在所有集合元素中的字母顺序最后, 所以显示在集合最后
cqlsh:test> UPDATE users SET emails = emails + {'fb@friendsofmordor.org'} WHERE user_id = 'frodo';
cqlsh:test> select * from users;
 user_id | emails                                                           | first_name | last_name
---------+------------------------------------------------------------------+------------+-----------
   frodo | {'baggins@gmail.com', 'f@baggins.com', 'fb@friendsofmordor.org'} |      Frodo |   Baggins

添加一个ab,在字母表最前面, 所以显示在集合的第一个: 
cqlsh:test> UPDATE users SET emails = emails + {'ab@friendsofmordor.org'} WHERE user_id = 'frodo';
cqlsh:test> select * from users;
 user_id | emails                                                                                     | first_name | last_name
---------+--------------------------------------------------------------------------------------------+------------+-----------
   frodo | {'ab@friendsofmordor.org', 'baggins@gmail.com', 'f@baggins.com', 'fb@friendsofmordor.org'} |      Frodo |   Baggins

设置为空和删除列都会使得该列数据为null, delete并不会把列真正删掉.
cqlsh:test> UPDATE users SET emails = {} WHERE user_id = 'frodo';
cqlsh:test> select * from users;
 user_id | emails | first_name | last_name
---------+--------+------------+-----------
   frodo |   null |      Frodo |   Baggins

cqlsh:test> DELETE emails FROM users WHERE user_id = 'frodo';
cqlsh:test> select * from users;
 user_id | emails | first_name | last_name
---------+--------+------------+-----------
   frodo |   null |      Frodo |   Baggins

列表List: 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
在已有的表结构上添加一列
cqlsh:test>   ALTER TABLE users ADD top_places list<text>;
cqlsh:test> UPDATE users SET top_places = [ 'rivendell', 'rohan' ] WHERE user_id= 'frodo';
cqlsh:test> select * from users;
 user_id | emails | first_name | last_name | top_places
---------+--------+------------+-----------+------------------------
   frodo |   null |      Frodo |   Baggins | ['rivendell', 'rohan']
   
在列表头部添加元素
cqlsh:test> UPDATE users SET top_places = [ 'home' ] + top_places WHERE user_id = 'frodo';
cqlsh:test> select * from users;
 user_id | emails | first_name | last_name | top_places
---------+--------+------------+-----------+--------------------------------
   frodo |   null |      Frodo |   Baggins | ['home', 'rivendell', 'rohan']

在列表尾部添加元素
cqlsh:test> UPDATE users SET top_places = top_places + [ 'mordor' ] WHERE user_id = 'frodo';
cqlsh:test> select * from users;
 user_id | emails | first_name | last_name | top_places
---------+--------+------------+-----------+------------------------------------------
   frodo |   null |      Frodo |   Baggins | ['home', 'rivendell', 'rohan', 'mordor']

更新元素: 索引从0开始, 下面的意思是更新列表的第三个元素的值. 问题是能不能在指定位置插入元素?
cqlsh:test> UPDATE users SET top_places[2] = 'riddermark' WHERE user_id ='frodo';
cqlsh:test> select * from users;
 user_id | emails | first_name | last_name | top_places
---------+--------+------------+-----------+-----------------------------------------------
   frodo |   null |      Frodo |   Baggins | ['home', 'rivendell', 'riddermark', 'mordor']

根据索引删除列表某个元素, 索引也是从0开始. 下面表示删除列表第四个元素.  注意用的是delete
cqlsh:test> DELETE top_places[3] FROM users WHERE user_id = 'frodo';
cqlsh:test> select * from users;
 user_id | emails | first_name | last_name | top_places
---------+--------+------------+-----------+-------------------------------------
   frodo |   null |      Frodo |   Baggins | ['home', 'rivendell', 'riddermark']

根据内容删除指定的元素.  注意用的是update更新操作
cqlsh:test> UPDATE users SET top_places = top_places - ['rivendell'] WHERE user_id = 'frodo';
cqlsh:test> select * from users;
 user_id | emails | first_name | last_name | top_places
---------+--------+------------+-----------+------------------------
   frodo |   null |      Frodo |   Baggins | ['home', 'riddermark']

映射Map: 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
添加Map数据结构, key为timestamp,value为文本
cqlsh:test> ALTER TABLE users ADD todo map<timestamp, text>;

没有存在,则插入
cqlsh:test> UPDATE users SET todo =  { '2012-9-24' : 'entermordor', '2012-10-2 12:00' : 'throwring into mount doom' } WHERE user_id = 'frodo';
cqlsh:test> select * from users;
 user_id | emails | first_name | last_name | todo                                                                                                 | top_places
---------+--------+------------+-----------+------------------------------------------------------------------------------------------------------+------------------------
   frodo |   null |      Frodo |   Baggins | {'2012-09-24 00:00:00+0800': 'entermordor', '2012-10-02 12:00:00+0800': 'throwring into mount doom'} | ['home', 'riddermark']

已经存在,则更新
cqlsh:test> UPDATE users SET todo['2012-10-2 12:00'] = 'throw my precious intomount doom' WHERE user_id = 'frodo';
cqlsh:test> select * from users;
 user_id | emails | first_name | last_name | todo                                                                                                        | top_places
---------+--------+------------+-----------+-------------------------------------------------------------------------------------------------------------+------------------------
   frodo |   null |      Frodo |   Baggins | {'2012-09-24 00:00:00+0800': 'entermordor', '2012-10-02 12:00:00+0800': 'throw my precious intomount doom'} | ['home', 'riddermark']

插入一条新记录
cqlsh:test> INSERT INTO users (user_id,todo)  VALUES ('miaohan', { '2013-9-22 12:01' : 'birthday wishes to Bilbo',  '2013-10-1 18:00' : 'Check into Inn of Prancing Pony' });
cqlsh:test> select * from users;
 user_id | emails | first_name | last_name | todo                                                                                                                    | top_places
---------+--------+------------+-----------+-------------------------------------------------------------------------------------------------------------------------+------------------------
 miaohan |   null |       null |      null | {'2013-09-22 12:01:00+0800': 'birthday wishes to Bilbo', '2013-10-01 18:00:00+0800': 'Check into Inn of Prancing Pony'} |                   null
   frodo |   null |      Frodo |   Baggins |             {'2012-09-24 00:00:00+0800': 'entermordor', '2012-10-02 12:00:00+0800': 'throw my precious intomount doom'} | ['home', 'riddermark']

删除Map中的一个键值对
cqlsh:test> DELETE todo['2012-9-24'] FROM users WHERE user_id = 'frodo';
cqlsh:test> select * from users;
 user_id | emails | first_name | last_name | todo                                                                                                                    | top_places
---------+--------+------------+-----------+-------------------------------------------------------------------------------------------------------------------------+------------------------
 miaohan |   null |       null |      null | {'2013-09-22 12:01:00+0800': 'birthday wishes to Bilbo', '2013-10-01 18:00:00+0800': 'Check into Inn of Prancing Pony'} |                   null
   frodo |   null |      Frodo |   Baggins |                                                        {'2012-10-02 12:00:00+0800': 'throw my precious intomount doom'} | ['home', 'riddermark']

插入(没有存在时)或更新(已经存在时), 同时设置ttl失效时间. 在ttl时间后, 这个键值对会从Map中删除
cqlsh:test> UPDATE users USING TTL 60 SET todo['2012-10-1'] = 'find water' WHERE user_id = 'frodo';
cqlsh:test> select * from users;
 user_id | emails | first_name | last_name | todo                                                                                                                    | top_places
---------+--------+------------+-----------+-------------------------------------------------------------------------------------------------------------------------+------------------------
 miaohan |   null |       null |      null | {'2013-09-22 12:01:00+0800': 'birthday wishes to Bilbo', '2013-10-01 18:00:00+0800': 'Check into Inn of Prancing Pony'} |                   null
   frodo |   null |      Frodo |   Baggins |              {'2012-10-01 00:00:00+0800': 'find water', '2012-10-02 12:00:00+0800': 'throw my precious intomount doom'} | ['home', 'riddermark']

过了60秒后, todo['2012-10-1'] 的记录会从Map中删除掉
cqlsh:test> select * from users;
 user_id | emails | first_name | last_name | todo                                                                                                                    | top_places
---------+--------+------------+-----------+-------------------------------------------------------------------------------------------------------------------------+------------------------
 miaohan |   null |       null |      null | {'2013-09-22 12:01:00+0800': 'birthday wishes to Bilbo', '2013-10-01 18:00:00+0800': 'Check into Inn of Prancing Pony'} |                   null
   frodo |   null |      Frodo |   Baggins |                                                        {'2012-10-02 12:00:00+0800': 'throw my precious intomount doom'} | ['home', 'riddermark']

Best Practice

DataModel

拼接字符串成主键

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
CREATE TABLE metrics_1 (
  id text,
  type text,
  value text,
  PRIMARY KEY (id,type)
);

insert into metrics_1(id,type,value) values('12345_cnt', 'idNumber', '2');
insert into metrics_1(id,type,value) values('12345_max', 'idNumber', '10');
insert into metrics_1(id,type,value) values('12345_sum', 'idNumber', '15');
insert into metrics_1(id,type,value) values('12345_avg', 'idNumber', '7.5');
insert into metrics_1(id,type,value) values('12345_dist', 'idNumber', '2');

insert into metrics_1(id,type,value) values('12346_cnt', 'idNumber', '2');
insert into metrics_1(id,type,value) values('12346_max', 'idNumber', '10');
insert into metrics_1(id,type,value) values('12346_sum', 'idNumber', '15');
insert into metrics_1(id,type,value) values('12346_avg', 'idNumber', '7.5');
insert into metrics_1(id,type,value) values('12346_dist', 'idNumber', '2');

select * from metrics_1 where id = '12345_cnt' and type='idNumber';

 id        | type     | value
-----------+----------+-------
 12345_cnt | idNumber |     2

 activity                                                                                          | timestamp                  | source       | source_elapsed
---------------------------------------------------------------------------------------------------+----------------------------+--------------+----------------
                                                                                Execute CQL3 query | 2017-06-15 10:55:58.455000 | 192.168.6.70 |              0
 Parsing select * from metrics_1 where id = '12345_cnt' and type='idNumber'; [SharedPool-Worker-1] | 2017-06-15 10:55:58.455000 | 192.168.6.70 |            194
                                                         Preparing statement [SharedPool-Worker-1] | 2017-06-15 10:55:58.455000 | 192.168.6.70 |            332
                               Executing single-partition query on metrics_1 [SharedPool-Worker-2] | 2017-06-15 10:55:58.456000 | 192.168.6.70 |           1074
                                                Acquiring sstable references [SharedPool-Worker-2] | 2017-06-15 10:55:58.456000 | 192.168.6.70 |           1109
                                                 Merging memtable tombstones [SharedPool-Worker-2] | 2017-06-15 10:55:58.456000 | 192.168.6.70 |           1134
   Skipped 0/0 non-slice-intersecting sstables, included 0 due to tombstones [SharedPool-Worker-2] | 2017-06-15 10:55:58.456000 | 192.168.6.70 |           1173
                                  Merging data from memtables and 0 sstables [SharedPool-Worker-2] | 2017-06-15 10:55:58.456000 | 192.168.6.70 |           1190
                                           Read 1 live and 0 tombstone cells [SharedPool-Worker-2] | 2017-06-15 10:55:58.456000 | 192.168.6.70 |           1219
                                                                                  Request complete | 2017-06-15 10:55:58.456821 | 192.168.6.70 |           1821

select * from metrics_1 where id = '12345_sum' and type='idNumber';

查询同一个id的不同指标，使用rowkey的in

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
select * from metrics_1 where id in ('12345_cnt','12345_sum') and type='idNumber';
 id        | type     | value
-----------+----------+-------
 12345_cnt | idNumber |     2
 12345_sum | idNumber |    15

 activity                                                                                                         | timestamp                  | source       | source_elapsed
------------------------------------------------------------------------------------------------------------------+----------------------------+--------------+----------------
                                                                                               Execute CQL3 query | 2017-06-15 10:55:36.398000 | 192.168.6.70 |              0
 Parsing select * from metrics_1 where id in ('12345_cnt','12345_sum') and type='idNumber'; [SharedPool-Worker-1] | 2017-06-15 10:55:36.398000 | 192.168.6.70 |            171
                                                                        Preparing statement [SharedPool-Worker-1] | 2017-06-15 10:55:36.398000 | 192.168.6.70 |            312
                                              Executing single-partition query on metrics_1 [SharedPool-Worker-3] | 2017-06-15 10:55:36.399000 | 192.168.6.70 |           1054
                                                               Acquiring sstable references [SharedPool-Worker-3] | 2017-06-15 10:55:36.399000 | 192.168.6.70 |           1184
                                                                Merging memtable tombstones [SharedPool-Worker-3] | 2017-06-15 10:55:36.399000 | 192.168.6.70 |           1230
                  Skipped 0/0 non-slice-intersecting sstables, included 0 due to tombstones [SharedPool-Worker-3] | 2017-06-15 10:55:36.399000 | 192.168.6.70 |           1290
                                                 Merging data from memtables and 0 sstables [SharedPool-Worker-3] | 2017-06-15 10:55:36.399000 | 192.168.6.70 |           1309
                                                          Read 1 live and 0 tombstone cells [SharedPool-Worker-3] | 2017-06-15 10:55:36.399000 | 192.168.6.70 |           1338
                                              Executing single-partition query on metrics_1 [SharedPool-Worker-2] | 2017-06-15 10:55:36.400000 | 192.168.6.70 |           2086
                                                               Acquiring sstable references [SharedPool-Worker-2] | 2017-06-15 10:55:36.400000 | 192.168.6.70 |           2133
                                                                Merging memtable tombstones [SharedPool-Worker-2] | 2017-06-15 10:55:36.400000 | 192.168.6.70 |           2169
                  Skipped 0/0 non-slice-intersecting sstables, included 0 due to tombstones [SharedPool-Worker-2] | 2017-06-15 10:55:36.400000 | 192.168.6.70 |           2205
                                                 Merging data from memtables and 0 sstables [SharedPool-Worker-2] | 2017-06-15 10:55:36.400000 | 192.168.6.70 |           2223
                                                          Read 1 live and 0 tombstone cells [SharedPool-Worker-2] | 2017-06-15 10:55:36.400000 | 192.168.6.70 |           2248
                                                                                                 Request complete | 2017-06-15 10:55:36.400743 | 192.168.6.70 |           2743

查询不同id的多个指标

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
select * from metrics_1 where id in ('12345_cnt','12345_sum','12346_cnt','12346_sum') and type='idNumber';

 id        | type     | value
-----------+----------+-------
 12345_cnt | idNumber |     2
 12345_sum | idNumber |    15
 12346_cnt | idNumber |     2
 12346_sum | idNumber |    15

 activity                                                                                                                                 | timestamp                  | source       | source_elapsed
------------------------------------------------------------------------------------------------------------------------------------------+----------------------------+--------------+----------------
                                                                                                                       Execute CQL3 query | 2017-06-15 10:58:11.479000 | 192.168.6.70 |              0
 Parsing select * from metrics_1 where id in ('12345_cnt','12345_sum','12346_cnt','12346_sum') and type='idNumber'; [SharedPool-Worker-1] | 2017-06-15 10:58:11.479000 | 192.168.6.70 |            152
                                                                                                Preparing statement [SharedPool-Worker-1] | 2017-06-15 10:58:11.479000 | 192.168.6.70 |            294
                                                                      Executing single-partition query on metrics_1 [SharedPool-Worker-2] | 2017-06-15 10:58:11.480000 | 192.168.6.70 |            908
                                                                                       Acquiring sstable references [SharedPool-Worker-2] | 2017-06-15 10:58:11.480000 | 192.168.6.70 |            948
                                                                                        Merging memtable tombstones [SharedPool-Worker-2] | 2017-06-15 10:58:11.480000 | 192.168.6.70 |            973
                                          Skipped 0/0 non-slice-intersecting sstables, included 0 due to tombstones [SharedPool-Worker-2] | 2017-06-15 10:58:11.480000 | 192.168.6.70 |           1012
                                                                         Merging data from memtables and 0 sstables [SharedPool-Worker-2] | 2017-06-15 10:58:11.480000 | 192.168.6.70 |           1030
                                                                                  Read 1 live and 0 tombstone cells [SharedPool-Worker-2] | 2017-06-15 10:58:11.480000 | 192.168.6.70 |           1060
                                                                      Executing single-partition query on metrics_1 [SharedPool-Worker-3] | 2017-06-15 10:58:11.481000 | 192.168.6.70 |           1750
                                                                                       Acquiring sstable references [SharedPool-Worker-3] | 2017-06-15 10:58:11.481000 | 192.168.6.70 |           1792
                                                                                        Merging memtable tombstones [SharedPool-Worker-3] | 2017-06-15 10:58:11.481000 | 192.168.6.70 |           1805
                                          Skipped 0/0 non-slice-intersecting sstables, included 0 due to tombstones [SharedPool-Worker-3] | 2017-06-15 10:58:11.481000 | 192.168.6.70 |           1829
                                                                         Merging data from memtables and 0 sstables [SharedPool-Worker-3] | 2017-06-15 10:58:11.481000 | 192.168.6.70 |           1839
                                                                                  Read 1 live and 0 tombstone cells [SharedPool-Worker-3] | 2017-06-15 10:58:11.481000 | 192.168.6.70 |           1855
                                                                      Executing single-partition query on metrics_1 [SharedPool-Worker-3] | 2017-06-15 10:58:11.481001 | 192.168.6.70 |           2458
                                                                                       Acquiring sstable references [SharedPool-Worker-3] | 2017-06-15 10:58:11.481001 | 192.168.6.70 |           2469
                                                                                        Merging memtable tombstones [SharedPool-Worker-3] | 2017-06-15 10:58:11.481001 | 192.168.6.70 |           2477
                                          Skipped 0/0 non-slice-intersecting sstables, included 0 due to tombstones [SharedPool-Worker-3] | 2017-06-15 10:58:11.481001 | 192.168.6.70 |           2489
                                                                         Merging data from memtables and 0 sstables [SharedPool-Worker-3] | 2017-06-15 10:58:11.481001 | 192.168.6.70 |           2495
                                                                                  Read 1 live and 0 tombstone cells [SharedPool-Worker-3] | 2017-06-15 10:58:11.481001 | 192.168.6.70 |           2503
                                                                      Executing single-partition query on metrics_1 [SharedPool-Worker-3] | 2017-06-15 10:58:11.482000 | 192.168.6.70 |           3018
                                                                                       Acquiring sstable references [SharedPool-Worker-3] | 2017-06-15 10:58:11.482000 | 192.168.6.70 |           3029
                                                                                        Merging memtable tombstones [SharedPool-Worker-3] | 2017-06-15 10:58:11.482000 | 192.168.6.70 |           3036
                                          Skipped 0/0 non-slice-intersecting sstables, included 0 due to tombstones [SharedPool-Worker-3] | 2017-06-15 10:58:11.482000 | 192.168.6.70 |           3045
                                                                         Merging data from memtables and 0 sstables [SharedPool-Worker-3] | 2017-06-15 10:58:11.482000 | 192.168.6.70 |           3052
                                                                                  Read 1 live and 0 tombstone cells [SharedPool-Worker-3] | 2017-06-15 10:58:11.482000 | 192.168.6.70 |           3061
                                                                                                                         Request complete | 2017-06-15 10:58:11.482477 | 192.168.6.70 |           3477

动态列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
CREATE TABLE metrics (
  id text,
  type text,
  metric text,
  value text,
  PRIMARY KEY ((id,type), metric)
);

insert into metrics(id,type,metric,value) values('12345', 'idNumber', 'cnt', '2');
insert into metrics(id,type,metric,value) values('12345', 'idNumber', 'max', '10');
insert into metrics(id,type,metric,value) values('12345', 'idNumber', 'sum', '15');
insert into metrics(id,type,metric,value) values('12345', 'idNumber', 'avg', '7.5');
insert into metrics(id,type,metric,value) values('12345', 'idNumber', 'distinct', '2');

insert into metrics(id,type,metric,value) values('12346', 'idNumber', 'cnt', '2');
insert into metrics(id,type,metric,value) values('12346', 'idNumber', 'max', '10');
insert into metrics(id,type,metric,value) values('12346', 'idNumber', 'sum', '15');
insert into metrics(id,type,metric,value) values('12346', 'idNumber', 'avg', '7.5');
insert into metrics(id,type,metric,value) values('12346', 'idNumber', 'distinct', '2');

select * from metrics where id = '12345' and type='idNumber';

 id    | type     | metric   | value
-------+----------+----------+-------
 12345 | idNumber |      avg |   7.5
 12345 | idNumber |      cnt |     2
 12345 | idNumber | distinct |     2
 12345 | idNumber |      max |    10
 12345 | idNumber |      sum |    15

 activity                                                                                        | timestamp                  | source       | source_elapsed
-------------------------------------------------------------------------------------------------+----------------------------+--------------+----------------
                                                                              Execute CQL3 query | 2017-06-15 10:51:48.365000 | 192.168.6.70 |              0
     Parsing select * from metrics where id = '12346' and type='idNumber'; [SharedPool-Worker-2] | 2017-06-15 10:51:48.366000 | 192.168.6.70 |            180
                                                       Preparing statement [SharedPool-Worker-2] | 2017-06-15 10:51:48.366000 | 192.168.6.70 |            311
                               Executing single-partition query on metrics [SharedPool-Worker-3] | 2017-06-15 10:51:48.366000 | 192.168.6.70 |            821
                                              Acquiring sstable references [SharedPool-Worker-3] | 2017-06-15 10:51:48.366000 | 192.168.6.70 |            861
                                               Merging memtable tombstones [SharedPool-Worker-3] | 2017-06-15 10:51:48.366000 | 192.168.6.70 |            896
 Skipped 0/0 non-slice-intersecting sstables, included 0 due to tombstones [SharedPool-Worker-3] | 2017-06-15 10:51:48.366000 | 192.168.6.70 |            952
                                Merging data from memtables and 0 sstables [SharedPool-Worker-3] | 2017-06-15 10:51:48.366001 | 192.168.6.70 |            970
                                         Read 5 live and 0 tombstone cells [SharedPool-Worker-3] | 2017-06-15 10:51:48.366001 | 192.168.6.70 |           1007
                                                                                Request complete | 2017-06-15 10:51:48.366564 | 192.168.6.70 |           1564

同一个rowkey， 查询不同的字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select * from metrics where id = '12345' and type='idNumber' and metric in('cnt','max');

 id    | type     | metric | value
-------+----------+--------+-------
 12345 | idNumber |    cnt |     2
 12345 | idNumber |    max |    10

 activity                                                                                                               | timestamp                  | source       | source_elapsed
------------------------------------------------------------------------------------------------------------------------+----------------------------+--------------+----------------
                                                                                                     Execute CQL3 query | 2017-06-15 10:48:46.426000 | 192.168.6.70 |              0
 Parsing select * from metrics where id = '12345' and type='idNumber' and metric in('cnt','max'); [SharedPool-Worker-1] | 2017-06-15 10:48:46.426000 | 192.168.6.70 |            355
                                                                              Preparing statement [SharedPool-Worker-1] | 2017-06-15 10:48:46.426000 | 192.168.6.70 |            548
                                                      Executing single-partition query on metrics [SharedPool-Worker-2] | 2017-06-15 10:48:46.426000 | 192.168.6.70 |           1157
                                                                     Acquiring sstable references [SharedPool-Worker-2] | 2017-06-15 10:48:46.427000 | 192.168.6.70 |           1193
                                                                      Merging memtable tombstones [SharedPool-Worker-2] | 2017-06-15 10:48:46.427000 | 192.168.6.70 |           1216
                        Skipped 0/0 non-slice-intersecting sstables, included 0 due to tombstones [SharedPool-Worker-2] | 2017-06-15 10:48:46.427000 | 192.168.6.70 |           1341
                                                       Merging data from memtables and 0 sstables [SharedPool-Worker-2] | 2017-06-15 10:48:46.427000 | 192.168.6.70 |           1366
                                                                Read 2 live and 0 tombstone cells [SharedPool-Worker-2] | 2017-06-15 10:48:46.427000 | 192.168.6.70 |           1423
                                                                                                       Request complete | 2017-06-15 10:48:46.427941 | 192.168.6.70 |           1941

不同的rowkey， 查询不同的字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
select * from metrics where id in('12345','12346') and type='idNumber' and metric in('cnt','max');

 id    | type     | metric | value
-------+----------+--------+-------
 12345 | idNumber |    cnt |     2
 12345 | idNumber |    max |    10
 12346 | idNumber |    cnt |     2
 12346 | idNumber |    max |    10

 activity                                                                                                                         | timestamp                  | source       | source_elapsed
----------------------------------------------------------------------------------------------------------------------------------+----------------------------+--------------+----------------
                                                                                                               Execute CQL3 query | 2017-06-15 10:50:20.151000 | 192.168.6.70 |              0
 Parsing select * from metrics where id in('12345','12346') and type='idNumber' and metric in('cnt','max'); [SharedPool-Worker-1] | 2017-06-15 10:50:20.152000 | 192.168.6.70 |            200
                                                                                        Preparing statement [SharedPool-Worker-1] | 2017-06-15 10:50:20.152000 | 192.168.6.70 |            377
                                                                Executing single-partition query on metrics [SharedPool-Worker-2] | 2017-06-15 10:50:20.153000 | 192.168.6.70 |           1431
                                                                               Acquiring sstable references [SharedPool-Worker-2] | 2017-06-15 10:50:20.153000 | 192.168.6.70 |           1464
                                                                                Merging memtable tombstones [SharedPool-Worker-2] | 2017-06-15 10:50:20.153000 | 192.168.6.70 |           1488
                                  Skipped 0/0 non-slice-intersecting sstables, included 0 due to tombstones [SharedPool-Worker-2] | 2017-06-15 10:50:20.153000 | 192.168.6.70 |           1605
                                                                 Merging data from memtables and 0 sstables [SharedPool-Worker-2] | 2017-06-15 10:50:20.153000 | 192.168.6.70 |           1628
                                                                          Read 2 live and 0 tombstone cells [SharedPool-Worker-2] | 2017-06-15 10:50:20.153000 | 192.168.6.70 |           1687
                                                                Executing single-partition query on metrics [SharedPool-Worker-3] | 2017-06-15 10:50:20.154000 | 192.168.6.70 |           2481
                                                                               Acquiring sstable references [SharedPool-Worker-3] | 2017-06-15 10:50:20.154000 | 192.168.6.70 |           2599
                                                                                Merging memtable tombstones [SharedPool-Worker-3] | 2017-06-15 10:50:20.154000 | 192.168.6.70 |           2647
                                  Skipped 0/0 non-slice-intersecting sstables, included 0 due to tombstones [SharedPool-Worker-3] | 2017-06-15 10:50:20.154000 | 192.168.6.70 |           2693
                                                                 Merging data from memtables and 0 sstables [SharedPool-Worker-3] | 2017-06-15 10:50:20.154000 | 192.168.6.70 |           2711
                                                                          Read 2 live and 0 tombstone cells [SharedPool-Worker-3] | 2017-06-15 10:50:20.154000 | 192.168.6.70 |           2753
                                                                                                                 Request complete | 2017-06-15 10:50:20.154296 | 192.168.6.70 |           3296

本文标题:Apache Cassandra入门

文章作者:任何忧伤,都抵不过世界的美丽

发布时间:2015年08月25日 - 00时00分

最后更新:2019年02月14日 - 21时42分

原始链接:http://github.com/zqhxuyuan/2015/08/25/2015-08-25-Apache-Cassandra/

许可协议: "署名-非商用-相同方式共享 3.0" 转载请保留原文链接及作者。


文章目录
  1. 1. Hello Cassandra(单机模式)
    1. 1.1. 配置文件默认值
  2. 2. Cassandra Cluster
  3. 3. PrimaryKey
    1. 3.1. 单一主键
    2. 3.2. 复合主键(Composite Primary Key)
    3. 3.3. 复合分区键(Compound Partition Key,随机)
    4. 3.4. 根据时间范围组合分区键求TopN
    5. 3.5. 参考文档
    6. 3.6. twissandra : Twitter with Cassandra Backend Demo
  4. 4. CQL:TTL & Delete
  5. 5. CQL Collection
  6. 6. Best Practice
    1. 6.1. DataModel
      1. 6.1.1. 拼接字符串成主键
      2. 6.1.2. 动态列