Assume that a Cassandra table stores the user information (email address and password) and user status. The user information remains constant while the user status changes frequently. If the user information is updated along with each status update, a large amount of storage space will be used.

Cassandra provides the static column feature to solve this problem. The values of a static column in a partition are consistent and only one copy is stored.

Define a static column

Add STATIC after a column name to define the column as a static column. The example is as follows:

CREATE TABLE "iteblog_users_with_status_updates" (
  "username" text,
  "id" timeuuid,
  "email" text STATIC,
  "encrypted_password" blob STATIC,
  "body" text,
  PRIMARY KEY ("username", "id")
);

The preceding statements set email and encrypted_password to STATIC, which means there is only one email value and one encrypted_password value corresponding to a username.

Limits

You cannot define static columns in the following situations:

  • The table is not configured with clustering columns (also known as clustering keys). For example:
cqlsh:iteblog_keyspace> CREATE TABLE "iteblog_users_with_status_updates_invalid" (
                    ...   "username" text,
                    ...   "id" timeuuid,
                    ...   "email" text STATIC,
                    ...   "encrypted_password" blob STATIC,
                    ...   "body" text,
                    ...   PRIMARY KEY ("username")
                    ... );
InvalidRequest: Error from server: code=2200 [Invalid query] message="Static columns are only useful (and thus allowed) if the table has at least one clustering column"

You cannot define static columns in the iteblog_users_with_status_updates_invalid table because the table has only a primary key and does not have clustering columns. The more rows of data exist in a partition, the better the static column performance will be. If no clustering columns are defined, a primary key value identifies a unique row in a partition and no static columns are required.

  • The table is set to a COMPACT STORAGE table. For example:
cqlsh:iteblog_keyspace> CREATE TABLE "iteblog_users_with_status_updates_invalid" (
                    ...   "username" text,
                    ...   "id" timeuuid,
                    ...   "email" text STATIC,
                    ...   "encrypted_password" blob STATIC,
                    ...   "body" text,
                    ...   PRIMARY KEY ("username", "id")
                    ... )WITH COMPACT STORAGE;
InvalidRequest: Error from server: code=2200 [Invalid query] message="Static columns are not supported in COMPACT STORAGE tables"
  • Columns used as partition keys or clustering columns cannot be set to STATIC. For example:
cqlsh:iteblog_keyspace> CREATE TABLE "iteblog_users_with_status_updates_invalid" (
                    ...   "username" text,
                    ...   "id" timeuuid STATIC,
                    ...   "email" text STATIC,
                    ...   "encrypted_password" blob STATIC,
                    ...   "body" text,
                    ...   PRIMARY KEY ("username", "id")
                    ... );
InvalidRequest: Error from server: code=2200 [Invalid query] message="Static column id cannot be part of the PRIMARY KEY"
cqlsh:iteblog_keyspace> CREATE TABLE "iteblog_users_with_status_updates_invalid" (
                    ...   "username" text,
                    ...   "id" timeuuid,
                    ...   "email" text STATIC,
                    ...   "encrypted_password" blob STATIC,
                    ...   "body" text,
                    ...   PRIMARY KEY (("username", "id"), email)
                    ... );
InvalidRequest: Error from server: code=2200 [Invalid query] message="Static column email cannot be part of the PRIMARY KEY"

Insert data into a table that contains static columns

Inserting data into a table that contains static columns is similar to inserting data into a standard table. For example, you can execute the following statements to insert data into the iteblog_users_with_status_updates table:

cqlsh:iteblog_keyspace> INSERT INTO "iteblog_users_with_status_updates"
                    ... ("username", "id", "email", "encrypted_password", "body")
                    ... VALUES (
                    ...   'iteblog',
                    ...   NOW(),
                    ...   'iteblog_hadoop@iteblog.com',
                    ...   0x877E8C36EFA827DBD4CAFBC92DD90D76,
                    ...   'Learning Cassandra!'
                    ... );
cqlsh:iteblog_keyspace> select username, email, encrypted_password, body from iteblog_users_with_status_updates;
 
 username | email                      | encrypted_password                 | body
----------+----------------------------+------------------------------------+---------------------
  iteblog | iteblog_hadoop@iteblog.com | 0x877e8c36efa827dbd4cafbc92dd90d76 | Learning Cassandra!
 
(1 rows)

A row of data is inserted into the table. The preceding statements have performed the following operations:

  • All data in the email and encrypted_password columns corresponding to the iteblog username is set to iteblog_hadoop@iteblog.com and 0x877e8c36efa827dbd4cafbc92dd90d76 because the email and encrypted_password columns are static.
  • A row with the Learning Cassandra! body is added to the iteblog partition. Execute the following statements to insert another row into the table:
cqlsh:iteblog_keyspace> INSERT INTO "iteblog_users_with_status_updates"
                    ... ("username", "id", "body")
                    ... VALUES ('iteblog', NOW(), 'I love Cassandra!') ;
cqlsh:iteblog_keyspace> select username, email, encrypted_password, body from iteblog_users_with_status_updates;
 
 username | email                      | encrypted_password                 | body
----------+----------------------------+------------------------------------+---------------------
  iteblog | iteblog_hadoop@iteblog.com | 0x877e8c36efa827dbd4cafbc92dd90d76 | Learning Cassandra!
  iteblog | iteblog_hadoop@iteblog.com | 0x877e8c36efa827dbd4cafbc92dd90d76 |   I love Cassandra!
 
(2 rows)
cqlsh:iteblog_keyspace>

The email and encrypted_password columns are not specified. However, the query result shows that the values of the two columns in the new row are consistent with the values specified in the preceding statements.

The user modifies the email address as follows:

cqlsh:iteblog_keyspace> UPDATE iteblog_users_with_status_updates SET email = 'iteblog@iteblog.com'
                    ... WHERE username = 'iteblog';
cqlsh:iteblog_keyspace> select username, email, encrypted_password, body from iteblog_users_with_status_updates;
 
 username | email               | encrypted_password                 | body
----------+---------------------+------------------------------------+---------------------
  iteblog | iteblog@iteblog.com | 0x877e8c36efa827dbd4cafbc92dd90d76 | Learning Cassandra!
  iteblog | iteblog@iteblog.com | 0x877e8c36efa827dbd4cafbc92dd90d76 |   I love Cassandra!
 
(2 rows)

The query result shows that the emails of all iteblog username rows are synchronized as the new email because the email column is static.

The table stores user information such as the email address and password. If a user modifies the email address and password on the frontend page, the backend system must obtain the current email address and password by executing the following statements:

cqlsh:iteblog_keyspace> SELECT "username", "email", "encrypted_password"
                    ... FROM "iteblog_users_with_status_updates"
                    ... WHERE "username" = 'iteblog';
 
 username | email               | encrypted_password
----------+---------------------+------------------------------------
  iteblog | iteblog@iteblog.com | 0x877e8c36efa827dbd4cafbc92dd90d76
  iteblog | iteblog@iteblog.com | 0x877e8c36efa827dbd4cafbc92dd90d76
 
(2 rows)

All rows with the iteblog username are displayed. You can use the SELECT DISTINCT statement to eliminate duplicate rows:

cqlsh:iteblog_keyspace> SELECT DISTINCT "username", "email", "encrypted_password"
                    ... FROM "iteblog_users_with_status_updates"
                    ... WHERE "username" = 'iteblog';
 
 username | email               | encrypted_password
----------+---------------------+------------------------------------
  iteblog | iteblog@iteblog.com | 0x877e8c36efa827dbd4cafbc92dd90d76
 
(1 rows)

All duplicate rows with the iteblog username are eliminated and a unique row is displayed.

Cassandra does not perform the DISTINCT operation for all data. A copy of the static column data is already stored in the underlying system.