Examples
Create table with UUID as the primary key
Create the cyclist_name
table with UUID as the primary key:
CREATE TABLE IF NOT EXISTS cycling.cyclist_name (
id UUID PRIMARY KEY,
lastname text,
firstname text
);
Create a compound primary key
Create the cyclist_category
table and store the data in reverse order:
CREATE TABLE IF NOT EXISTS cycling.cyclist_category (
category text,
points int,
id UUID,
lastname text,
PRIMARY KEY (category, points)
)
WITH CLUSTERING ORDER BY (points DESC);
Creating a composite partition key
Create a table that is optimized for query by cyclist rank by year:
CREATE TABLE IF NOT EXISTS cycling.rank_by_year_and_name (
race_year int,
race_name text,
cyclist_name text,
rank int,
PRIMARY KEY ((race_year, race_name), rank)
);
Creating a table with a vertex label or an edge label
Create the person
table with a vertex label person_label
as graph data:
CREATE TABLE IF NOT EXISTS food_cql.person (
person_id UUID,
name text,
gender text,
nickname set<text>,
cal_goal int,
macro_goal list<int>,
badge map<text, date>,
PRIMARY KEY (name, person_id)
) WITH CLUSTERING ORDER BY (person_id ASC) AND VERTEX LABEL person_label;
Create the book
table with a vertex label book_label
as graph data:
CREATE TABLE IF NOT EXISTS food_cql.book (
book_id int,
name text,
authors list<frozen<fullname>>,
publish_year int,
isbn text,
category set<text>,
PRIMARY KEY (name, book_id)
) WITH CLUSTERING ORDER BY (book_id ASC) AND VERTEX LABEL;
Create the person_authored_book
table with an edge label authored
as graph data:
CREATE TABLE IF NOT EXISTS food_cql.person_authored_book (
person_id UUID,
person_name text,
book_id int,
book_name text,
PRIMARY KEY ( (person_name, person_id) , book_name, book_id)
) WITH EDGE LABEL person_authored_book
FROM person(name, person_id)
TO book(name, book_id);
See Managing Graphs for information on creating graphs.
Creating a table with a frozen UDT
Create the race_winners
table that has a frozen user-defined type (UDT):
CREATE TABLE IF NOT EXISTS cycling.race_winners (
cyclist_name FROZEN<fullname>,
race_name text,
race_position int,
PRIMARY KEY (race_name, race_position)
);
See Creating a user-defined type for information on creating UDTs. UDTs can be created unfrozen if only non-collection fields are used in the user-defined type creation. If the table is created with an unfrozen UDT, then individual field values can be updated and deleted.
Creating a table with a geospatial type
CREATE TABLE cycling.geospatial (
id text PRIMARY KEY,
point 'PointType',
linestring 'LineStringType'
);
Creating a table with a CDC log
Create a change data capture log for the cyclist_id
table:
CREATE TABLE IF NOT EXISTS cycling.cyclist_id (
lastname text,
firstname text,
age int,
id UUID,
PRIMARY KEY ((lastname, firstname), age)
);
CDC logging must be enabled in cassandra.yaml.
Before enabling CDC logging, have a plan for moving and consuming the log information. After the disk space limit is reached, writes to CDC-enabled tables are rejected until more space is freed. See Change-data-capture (CDC) space settings for information about available CDC settings. |
Storing data in descending order
The following example shows a table definition that stores the categories with the highest points first.
CREATE TABLE IF NOT EXISTS cycling.cyclist_category (
category text,
points int,
id UUID,
lastname text,
PRIMARY KEY (category, points)
)
WITH CLUSTERING ORDER BY (points DESC);
Restoring from the table ID for commit log replay
Recreate a table with its original ID to facilitate restoring table data by replaying commit logs:
CREATE TABLE IF NOT EXISTS cycling.cyclist_emails (
userid text PRIMARY KEY,
id UUID,
emails set<text>
)
WITH ID = '1bb7516e-b140-11e8-96f8-529269fb1459';
To retrieve a table’s ID, query the id
column of system_schema.tables
.
For example:
SELECT id FROM system_schema.tables
WHERE keyspace_name = 'cycling'
AND table_name = 'cyclist_emails';
To perform a point-in-time restoration of the table, see Restoring a backup to a specific point-in-time.