Introduction to CRUD on cql 3.0 data type

In a previous article, we covered a basic data definition language,  and in this article, we are going to cover data manipulation language. With cql3, composite data type is pretty interesting compare to sql. Official documentation available here.

We covered all data type in cql 3.0 except counter and now we will create all the available data types that can coexists within a table. Let's do it.

CREATE TABLE dataType (
  id uuid,
  name ascii,
  amount bigint,
  binary blob,
  isSingle boolean,
  lamp decimal,
  salary double,
  works float,
  ip inet,
  car int,
  email set<text>,
  kidsAge map<text,int>,
  places list<text>,
  description text,
  lastUpdate timestamp,
  myTimeUUID timeuuid,
  longDescription varchar,
  spending varint,
  PRIMARY KEY (id)
);

cqlsh:jw_schema1> desc table datatype;

CREATE TABLE datatype (
  id uuid,
  amount bigint,
  binary blob,
  car int,
  description text,
  email set<text>,
  ip inet,
  issingle boolean,
  kidsage map<text, int>,
  lamp decimal,
  lastupdate timestamp,
  longdescription text,
  mytimeuuid timeuuid,
  name ascii,
  places list<text>,
  salary double,
  spending varint,
  works float,
  PRIMARY KEY (id)
) WITH
  bloom_filter_fp_chance=0.010000 AND
  caching='KEYS_ONLY' AND
  comment='' AND
  dclocal_read_repair_chance=0.000000 AND
  gc_grace_seconds=864000 AND
  index_interval=128 AND
  read_repair_chance=0.100000 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'};

looks good, table created and let's continue by insert data into this table!

insert into datatype (id, name, amount, binary, issingle, lamp, salary, works, ip, car, email, kidsage, places, description, lastUpdate, myTimeUUID, longDescription, spending) values (62c36092-82a1-3a00-93d1-46196ee77204, 'jason wee', 123, 0xff, false, 10, 100000, 1, '192.168.0.1', 3, {'a@b.com', 'c@d.com'}, {'juniorA':1, 'juniorB':2}, ['kuala lumpur', 'petaling jaya', 'kepong'], 'hello world', '2014-04-15 00:00:00', maxTimeuuid('2014-04-15 00:05+0000'), 'this is a longer hello world', 123);

cqlsh:jw_schema1> select * from datatype;

 id                                   | amount | binary | car | description | email                  | ip          | issingle | kidsage                      | lamp | lastupdate               | longdescription              | mytimeuuid                           | name      | places                                      | salary | spending | works
--------------------------------------+--------+--------+-----+-------------+------------------------+-------------+----------+------------------------------+------+--------------------------+------------------------------+--------------------------------------+-----------+---------------------------------------------+--------+----------+-------
 62c36092-82a1-3a00-93d1-46196ee77204 |    123 |   0xff |   3 | hello world | {'a@b.com', 'c@d.com'} | 192.168.0.1 |    False | {'juniorA': 1, 'juniorB': 2} |   10 | 2014-04-15 00:00:00+0800 | this is a longer hello world | 95fc050f-c431-11e3-7f7f-7f7f7f7f7f7f | jason wee | ['kuala lumpur', 'petaling jaya', 'kepong'] |  1e+05 |      123 |     1

(1 rows)

Goodies, all data were inserted. Let's try update, we start by single update to one column.

cqlsh:jw_schema1> update datatype set amount = 456 where id = 62c36092-82a1-3a00-93d1-46196ee77204;
cqlsh:jw_schema1> select amount from datatype where id = 62c36092-82a1-3a00-93d1-46196ee77204;

 amount
--------
    456

(1 rows)

Looks good too! Now we will update three fields.

cqlsh:jw_schema1> update datatype set binary = 0x68656c6c6f20776f726c64, car = 6, description = 'changed description' where id = 62c36092-82a1-3a00-93d1-46196ee77204;
cqlsh:jw_schema1> select binary, car, description from datatype where id = 62c36092-82a1-3a00-93d1-46196ee77204;

 binary                   | car | description
--------------------------+-----+---------------------
 0x68656c6c6f20776f726c64 |   6 | changed description

(1 rows)

cqlsh:jw_schema1>

Looks good! The binary data type always prefix with 0x. It is hex representation of hello world. Let's now change the composite data type.

cqlsh:jw_schema1> update datatype set email = {'e@f.com'} where id = 62c36092-82a1-3a00-93d1-46196ee77204;
cqlsh:jw_schema1> select email from datatype;

 email
-------------
 {'e@f.com'}

(1 rows)

Hmm... email field values get overridden. So how do we append it? concat them ! :-)

cqlsh:jw_schema1> update datatype set email = email + {'a@b.com', 'c@d.com'} where id = 62c36092-82a1-3a00-93d1-46196ee77204;
cqlsh:jw_schema1> select email from datatype;

 email
-----------------------------------
 {'a@b.com', 'c@d.com', 'e@f.com'}

(1 rows)

Move on, update data type boolean and IP number.

cqlsh:jw_schema1> update datatype set ip = 'a.b.c.d', issingle = True where id = 62c36092-82a1-3a00-93d1-46196ee77204;
Bad Request: unable to make inetaddress from 'a.b.c.d'
cqlsh:jw_schema1> update datatype set ip = '255.255.255.255', issingle = True where id = 62c36092-82a1-3a00-93d1-46196ee77204;
cqlsh:jw_schema1> update datatype set ip = '255.255.255.255', issingle = Trued where id = 62c36092-82a1-3a00-93d1-46196ee77204;
Bad Request: line 1:61 no viable alternative at input 'where'
cqlsh:jw_schema1> update datatype set ip = '255.255.255.255', issingle = True where id = 62c36092-82a1-3a00-93d1-46196ee77204;

cqlsh:jw_schema1> select ip,issingle from datatype;

 ip              | issingle
-----------------+----------
 255.255.255.255 |     True

(1 rows)

Simple checking on IP number and boolean data type is enforce. Let's change map now.

cqlsh:jw_schema1> update datatype set kidsage = {'juniorC':3} where  id = 62c36092-82a1-3a00-93d1-46196ee77204;
cqlsh:jw_schema1> select kidsage from datatype;

 kidsage
----------------
 {'juniorC': 3}

(1 rows)

cqlsh:jw_schema1> update datatype set kidsage = kidsage + {'juniorA':1, 'juniorB':2} where  id = 62c36092-82a1-3a00-93d1-46196ee77204;
cqlsh:jw_schema1> select kidsage from datatype;

 kidsage
--------------------------------------------
 {'juniorA': 1, 'juniorB': 2, 'juniorC': 3}

(1 rows)

Exactly like set behavior, if you need to append the data, you need to concat them using plus sign.

cqlsh:jw_schema1> update datatype set lamp = 12.34, lastupdate = '2014-04-16 20:00', longdescription = 'this is a long long long hello world', mytimeuuid = maxTimeuuid('2014-04-16'), name = 'john smith' where  id = 62c36092-82a1-3a00-93d1-46196ee77204;
cqlsh:jw_schema1> select lamp, lastupdate, longdescription, mytimeuuid, name from datatype;

 lamp  | lastupdate               | longdescription                      | mytimeuuid                           | name
-------+--------------------------+--------------------------------------+--------------------------------------+------------
 12.34 | 2014-04-16 20:00:00+0800 | this is a long long long hello world | ff72270f-c4b6-11e3-7f7f-7f7f7f7f7f7f | john smith

(1 rows)

cqlsh:jw_schema1>

Everything looks good, timestamp provided by hour and longer text and time uuid can accept only date, everything seem cool.

cqlsh:jw_schema1> update datatype set places = places + ['cheras'], salary = 985621.35, spending = 12355, works = 89.36 where  id = 62c36092-82a1-3a00-93d1-46196ee77204;
cqlsh:jw_schema1> select places, salary, spending, works from datatype;

 places                                                | salary     | spending | works
-------------------------------------------------------+------------+----------+-------
 ['kuala lumpur', 'petaling jaya', 'kepong', 'cheras'] | 9.8562e+05 |    12355 | 89.36

(1 rows)

cqlsh:jw_schema1>

Okay, we pretty all cover update all the data type. Let's remove this one row.

cqlsh:jw_schema1> delete amount,binary,car,description,email,ip,issingle,kidsage,lamp,lastupdate,longdescription,mytimeuuid from datatype where id = 62c36092-82a1-3a00-93d1-46196ee77204;
cqlsh:jw_schema1> select * from datatype;

 id                                   | amount | binary | car  | description | email | ip   | issingle | kidsage | lamp | lastupdate | longdescription | mytimeuuid | name       | places                                                | salary     | spending | works
--------------------------------------+--------+--------+------+-------------+-------+------+----------+---------+------+------------+-----------------+------------+------------+-------------------------------------------------------+------------+----------+-------
 62c36092-82a1-3a00-93d1-46196ee77204 |   null |   null | null |        null |  null | null |     null |    null | null |       null |            null |       null | john smith | ['kuala lumpur', 'petaling jaya', 'kepong', 'cheras'] | 9.8562e+05 |    12355 | 89.36

(1 rows)

Pretty interesting, we can delete columns within a row. The data is set to null.

cqlsh:jw_schema1> delete from datatype where id = 62c36092-82a1-3a00-93d1-46196ee77204;
cqlsh:jw_schema1> select * from datatype;

(0 rows)

Now we have deleted everything.

• looks like it is case insensitive, that, is when we created the table name, dataType it is stored as datatype.


• the composite datatype is definitely nice to have as we don't have to link a few tables.


• we can also delete a few columns or we can delete the entire row.