July 12th, 2016
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 setuser_idusing cqlcreate 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 byPKand Where inIndex Value.ORDER BY is only supported when the partition key is restricted by an EQ or an IN.
Reference: