Examples

Creating a 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
);

Creating 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 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.

Creating a legacy table with COMPACT STORAGE (5.1 only)

Use WITH COMPACT STORAGE to create a table that is compatible with clients written to work with the legacy (Thrift) storage engine format.

CREATE TABLE sblocks (
  block_id uuid,
  subblock_id uuid,
  data blob,
  PRIMARY KEY (block_id, subblock_id)
)
WITH COMPACT STORAGE;

Using the WITH COMPACT STORAGE directive prevents you from defining more than one column that is not part of a compound primary key. A compact table with a primary key that is not compound can have multiple columns that are not part of the primary key.

A compact table that uses a compound primary key must define at least one clustering column. Columns cannot be added nor removed after creation of a compact table. Unless you specify WITH COMPACT STORAGE, CQL creates a table with non-compact storage.

Collections and static columns cannot be used with COMPACT STORAGE tables.

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000, info@datastax.com