Getting started
with cql3
Lets step through some basics of cassandra
cql3 . I am going to assume you have already install cassandra and ready to get
into cqlsh
<cassandra_home>#
bin/cqlsh
Connected to Test
Cluster at localhost:9160.
[cqlsh 4.1.1 |
Cassandra 2.0.8 | CQL spec 3.1.1 | Thrift protocol 19.39.0]
Use HELP for
help.
cqlsh>
Now we are ready
to run some queries .
Lets create a
keyspace to start with . Keyspace is the same as database if you are coming
from sql land
cqlsh> CREATE
KEYSPACE testkeyspace WITH replication = {'class': 'SimpleStrategy',
'replication_factor' : 1};
now the keyspace
has been created..
lets use the
keyspace
cqlsh> USE
testkeyspace;
cqlsh:testkeyspace>
now we have a
keyspace with replication factor 1 .
if you want to
change your keyspace rf , you can alter at any point of time
ALTER KEYSPACE
testkeyspace WITH replication = {'class': 'SimpleStrategy',
'replication_factor' : 2};
this will require
you to have a two node cluster setup
Dropping keyspace
DROP KEYSPACE
testkeyspace;
yeah , simple so
far
Now lets create a
simple table
CREATE TABLE
testtable(id text PRIMARY KEY, name text, avg varint, age int) WITH
comment='Test table' AND read_repair_chance = 1.0;
Running a basic
select query is very simple
cqlsh:testkeyspace>
select * from testtable;
(0 rows)
Lets describe the
table quickly
cqlsh:testkeyspace>
describe columnfamily testtable;
CREATE TABLE
testtable (
id text,
age int,
avg varint,
name text,
PRIMARY KEY (id)
) WITH
bloom_filter_fp_chance=0.010000 AND
caching='KEYS_ONLY' AND
comment='Test table' AND
dclocal_read_repair_chance=0.000000 AND
gc_grace_seconds=864000 AND
index_interval=128 AND
read_repair_chance=1.000000 AND
replicate_on_write='true' AND
populate_io_cache_on_flush='false' AND
default_time_to_live=0 AND
speculative_retry='99.0PERCENTILE' AND
memtable_flush_period_in_ms=0 AND
compaction={'class':
'SizeTieredCompactionStrategy'} AND
compression={'sstable_compression':
'LZ4Compressor'};
column family is
similar to a table in sql
Now , let us
insert some data into the column family
cqlsh:testkeyspace>
insert into testtable (id,age,avg,name) values('1',23,10,'Arunn');
and select
cqlsh:testkeyspace>
select * from testtable;
id | age | avg | name
----+-----+-----+-------
1 | 23
| 10 | Arunn
(1 rows)
Altering a table
and adding fields is simple
ALTER TABLE testtable ADD gender varchar;
cqlsh:testkeyspace>
ALTER TABLE testtable ADD gender varchar;
cqlsh:testkeyspace>
select * from testtable;
id | age | avg | gender | name
----+-----+-----+--------+-------
1 | 23
| 10 |
null | Arunn
(1 rows)
Updates are very
simple too
cqlsh:testkeyspace>
update testtable set gender = 'MALE' where id = '1';
cqlsh:testkeyspace>
select * from testtable;
id | age | avg | gender | name
----+-----+-----+--------+-------
1 | 23
| 10 |
MALE | Arunn
(1 rows)
Now lets try a
simple delete and we are done with basic operations
cqlsh:testkeyspace>
delete from testtable where id = '1';
cqlsh:testkeyspace>
select * from testtable;
(0 rows)
Now we know how
to create
·
Keyspace
·
Column
family / CF
·
Insert
data
·
Alter CF
·
Update
row
·
Delete
row
Immediate
interesting thing is to try batch operations , this saves network round-trips
BEGIN BATCH
insert into
testtable (id,age,avg,gender,name) values('2',30,19, 'MALE','Abc');
insert into
testtable (id,age,avg,gender,name) values('3',40,16, 'MALE','Def');
update testtable
set name = 'Ghi' where id = '3';
APPLY BATCH;
Counters are a
very important column type in Cassandra
Counters
The counter type is used to define counter columns. A counter column is a column whose value is a
64-bit signed integer and on which 2 operations are supported: incrementation
and decrementation Note the value of a counter cannot be set. A counter doesn’t
exist until first incremented/decremented, and the first
incrementation/decrementation is made as if the previous value was 0. Deletion
of counter columns is supported but have some limitations (see the Cassandra Wiki for more information).
The use of the counter
type is limited in the following way:
- It cannot be used for column that is part of the PRIMARY KEY of a table.
- A table that contains a counter can only contain
counters. In other words, either all the columns of a table outside the PRIMARY KEY have the counter type, or none of them have it.
Map column types are
interesting to store a collection of data . Cassandra also supports set and
list types
cqlsh:testkeyspace>
create table testmaptable (id text PRIMARY KEY , clus_columns
map<int,text>);
cqlsh:testkeyspace>
select * from testmaptable;
(0 rows)
cqlsh:testkeyspace>
insert into testmaptable (id,clus_columns) values ('1',{1:'number one'});
cqlsh:testkeyspace>
select * from testmaptable;
id | clus_columns
----+-------------------
1 | {1: 'number one'}
(1 rows)
cqlsh:testkeyspace>
update testmaptable set clus_columns = {2:'number two'} where id = '1';
cqlsh:testkeyspace>
select * from testmaptable;
id | clus_columns
----+-------------------
1 | {2: 'number two'}
(1 rows)
cqlsh:testkeyspace>
update testmaptable set clus_columns = clus_columns + {1:'number one'} where id
= '1';
cqlsh:testkeyspace>
select * from testmaptable;
id | clus_columns
----+------------------------------------
1 | {1: 'number one', 2: 'number two'}
(1 rows)
Lets take a look at how
it looks in the cassandra cli
<Cassandra_home>
bin/Cassandra-cli –host localhost
Use testkeyspace;
[default@testkeyspace]
list testmaptable;
Using default limit of
100
Using default cell limit
of 100
-------------------
RowKey: 1
=> (name=, value=,
timestamp=1402035929908000)
=>
(name=clus_columns:00000001, value=6e756d626572206f6e65,
timestamp=1402036016296000)
=>
(name=clus_columns:00000002, value=6e756d6265722074776f, timestamp=1402035971589000)
1 Row Returned.
Elapsed time: 429
msec(s).
No comments:
Post a Comment