Keyspace

  • keyspace == database in SQL
CREATE KEYSPACE KEYSPACE_NAME
  WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 3 };

then use KEYSPACE_NAME; in cqlsh before you create/alter/drop/select tables.

Special Data Operation in Cassandra (Set/List/Map/Nested Type/Frozen/Tuple/JSON)

Set data

Table schema for set.

CREATE TABLE users (
  user_id text PRIMARY KEY,
  first_name text,
  last_name text,
  emails set<text>
);

Insert data into table

INSERT INTO users (user_id, first_name, last_name, emails)
  VALUES('frodo', 'Frodo', 'Baggins', {'[email protected]', '[email protected]'});

List data

Table schema

CREATE TABLE users (
  user_id text PRIMARY KEY,
  first_name text,
  last_name text,
  top_places list<text>
);

Data Insertion.

INSERT INTO users (user_id, first_name, last_name, emails)
  VALUES('frodo', 'Frodo', 'Baggins', [ 'rivendell', 'rohan' ]);

Map Data

Table schema

CREATE TABLE users (
  user_id text PRIMARY KEY,
  first_name text,
  last_name text,
  todo map<timestamp, text>;
);

Data Insertion.

INSERT INTO users (user_id, first_name, last_name, emails)
  VALUES('frodo', 'Frodo', 'Baggins', { '2012-9-24' : 'enter mordor',
  '2014-10-2 12:00' : 'throw ring into mount doom' });

Nested Type

At first create a type of Address2.

CREATE TYPE address2 (
      street text,
      city text
  );

Using Address2 as a type and create Profile. For user defined type need to use frozen<address2>.

CREATE TYPE profile (
      mail set<text>,
      phone set<int>,
      address frozen<address2>
  );

Create User_Data using Profile, the same using frozen<profile> for user defined type.

CREATE TYPE user_data (
      username text,
      userage int,
      userprofile frozen<profile>
  );

Finally, create another User_Profile2.

CREATE TABLE user_profiles2 (
      id int PRIMARY KEY,
      data frozen<user_data>
  );

Insert data with JSON format.

INSERT INTO user_profiles2(id, data)
  VALUES (1,
         { 
            username: 'user', 
            userage: 20,
            userprofile: {
                mail: {'[email protected]', '[email protected]'},
                phone: {1234567, 9876543},
                address: {      
                    street : 'Wu fu Rd.',
                    city : 'KAOHSIUNG CITY'
                }    
            }
         }
  );    

Select it:

 id | data
----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  1 | {username: 'user', userage: 20, userprofile: {mail: {'[email protected]', '[email protected]'}, phone: {1234567, 9876543}, address: {street: 'Wu fu Rd.', city: 'KAOHSIUNG CITY'}}}

Only get partial data:

select data.userprofile.address from user_profiles2 where id = 1;


 data.userprofile.address
-----------------------------------------------
 {street: 'Wu fu Rd.', city: 'KAOHSIUNG CITY'}

Frozen

For User-Defined type need to use frozen when you need specific in table column.

CREATE TABLE mykeyspace.users (
  id uuid PRIMARY KEY,
  name frozen <fullname>,
  direct_reports set<frozen <fullname>>,     // a collection set
  addresses map<text, frozen <address>>     // a collection map
);

Note:: Non-Frozen data could not be PK.

Note: User-Defined type, so please check your field or you will get error:

"Non-frozen User-Defined types are not supported, please use frozen<>"

Tuple data (Cassandra 2.1 Supproted)

CREATE TABLE collect_things (
  k int PRIMARY KEY,
  v <tuple<int, text, float>>
);

INSERT INTO collect_things (k, v) VALUES(0, (3, 'bar', 2.1));

SELECT * FROM collect_things;

 k | v
---+-----------------
 0 | (3, 'bar', 2.1)
 

JSON operation (Cassandra 2.2 Supproted)

Table schema

CREATE TABLE users (
    id text PRIMARY KEY,
    age int,
    state text
);

Insert data as normal CQL

INSERT INTO users (id, age, state) VALUES ('user123', 42, 'TX');

Insert data as JSON.

INSERT INTO users JSON '{"id": "user123", "age": 42, "state": "TX"}';

Gotchas

  • If you want to SELECT * FROM users WHERE user_id =?, you must set user_id using cql create index on users(user_id); as indexing or your will get error "No secondary indexes on the restricted columns support the provided operators:"
  • There is no way to change PK, just drop original table and re-create a new one.
  • Primary key could not over length: 65535.
  • We could not update PK columns, could not search only one of composit PK (must be all).
  • ORDER BY with 2ndary indexes is not supported, So you cannot order by PK and Where in Index Value.
  • ORDER BY is only supported when the partition key is restricted by an EQ or an IN.

Reference:


Buy Me A Coffee

Evan

Attitude is everything