CREATE TABLE

Creates a table in the specified or current keyspace.

To create a table, you must define the table name, column names, column data types, and a primary key. Optionally, you can set table properties, like compaction and compression. Omitted options use default values.

For more information about table architecture and designing performant tables, see Data modeling for Apache Cassandra-based databases and CQL data types.

Dynamic schema generation and schema collision

DSE doesn’t support dynamic schema generation. Schema collisions can occur if multiple clients attempt to generate tables simultaneously.

If a schema collision occurs, do the following:

Perform a rolling restart on all nodes to update the schemas.
Run nodetool describecluster on all nodes to verify that each node has only one schema version.
On each node, examine the data directory for the table to fix.

The default location for the data directory is /var/lib/cassandra/data. If there is only one directory for the table, go to the next node and repeat this step. If there are two or more directories for the table, continue to the next step.

If there are two directories, the older directory contains the old table data before the update. The newer directory contains the new data after the update.

Use CQL to find the table’s newest id in system_schema.tables:

SELECT id, keyspace_name, table_name FROM system_schema.tables
  WHERE keyspace_name = 'cycling'
  AND table_name = 'cyclist_name';

 id                                   | keyspace_name | table_name
--------------------------------------+---------------+--------------
 c2784ad1-065d-11ea-bd8d-9f9b8a53b5f5 |       cycling | cyclist_name

(1 rows)

Move the data from the older table to the newer table’s directory, and then remove the older directory. Repeat this step as needed.
Run nodetool refresh.

Syntax

CREATE TABLE [ IF NOT EXISTS ] [<keyspace_name>.]<table_name>
( <column_definition> [ , ... ]
  [ , PRIMARY KEY ( ( <partition_key_column_name> [ , ... ] )
                  [ , ( <clustering_column_name> [ ... ] ) ] ) ] )
  [ [ WITH | AND ] <table_options> [ AND ...] ]
  [ [ WITH | AND ] CLUSTERING ORDER BY ( <clustering_column_name> [ ASC | DESC ] [ , ... ] ) ]
  [ [ WITH | AND ] ( VERTEX LABEL <vl_name> | EDGE LABEL ) <el_name> FROM <vl_name> TO <vl_name> ]
  [ [ WITH | AND ] ID = '<table_hash_tag>' ] ;

Legend
Syntax conventions	Description
UPPERCASE	Literal keyword.
Lowercase	Not literal.
`< >`	Variable value. Replace with a user-defined value.
`[]`	Optional. Square brackets (`[]`) surround optional command arguments. Do not type the square brackets.
`( )`	Group. Parentheses ( `( )` ) identify a group to choose from. Do not type the parentheses.
`\|`	Or. A vertical bar (`\|`) separates alternative elements. Type any one of the elements. Do not type the vertical bar.
`...`	Repeatable. An ellipsis ( `...` ) indicates that you can repeat the syntax element as often as required.
`'<Literal string>'`	Single quotation (`'`) marks must surround literal strings in CQL statements. Use single quotation marks to preserve upper case.
`{ <key> : <value> }`	Map collection. Braces (`{ }`) enclose map collections or key value pairs. A colon separates the key and the value.
`<<datatype1>,<datatype2>>`	Set, list, map, or tuple. Angle brackets ( `< >` ) enclose data types in a set, list, map, or tuple. Separate the data types with a comma.
`<cql_statement>;`	End CQL statement. A semicolon (`;`) terminates all CQL statements.
`[--]`	Separate the command line options from the command arguments with two hyphens ( `--` ). This syntax is useful when arguments might be mistaken for command line options.
`' <<schema\> ... </schema\>> '`	Search CQL only: Single quotation marks (`'`) surround an entire XML schema declaration.
`@<xml_entity>='<xml_entity_type>'`	Search CQL only: Identify the entity and literal value to overwrite the XML element in the schema and solrConfig files.

Parameters

Parameter Description Default

Parameter	Description	Default
`keyspace_name`	Optional. Name of the keyspace that contains the table. Separated from the table name with a period, such as `cycling_keyspace.cycling_table`. Enclose in double quotes if the keyspace name contains uppercase letters.	If no name is specified, the current keyspace is used.
`table_name`	Name of the table. Enclose in double quotes if the table name contains uppercase letters.
`column_definition`	List of `column_names` and their CQL data types. The optional `PRIMARY KEY` clause specifies the primary key column if the table has only a single partition key. The optional `STATIC` keyword is used to define a static column, which has a single value shared by all rows in a partition.
`column_name`	Name of the column.
`cql_type`	CQL data type
`column_list`	List of columns that make up the primary key. The first column is the partition key. If there is more than one column in the partition key, enclose the columns in parentheses. Additional columns are clustering columns.
`partition_key_column_name`	Name of the partition key column. One or more partition key columns are required.
`cluster_column_name`	Name of the clustering column. Optional. One or more clustering columns can be defined. Also define `ASC` or `DESC` for the clustering order.	`ASC`
`vl_name`	DSE Graph vertex label name.
`el_name`	DSE Graph edge label name.
`IF NOT EXISTS`	Allows silent skip by checking for existing tables with the same name in the same keyspace. If a match is found, the operation silently does nothing (no error is thrown). If no match is found, the operation creates the table. This option is set immediately after `CREATE TABLE`. It isn’t part of the `WITH` clause.	Not set. If a table with the same name already exists in the same keyspace, an error occurs and the operation fails.

keyspace_name

Optional. Name of the keyspace that contains the table. Separated from the table name with a period, such as cycling_keyspace.cycling_table.

Enclose in double quotes if the keyspace name contains uppercase letters.

If no name is specified, the current keyspace is used.

table_name

Name of the table. Enclose in double quotes if the table name contains uppercase letters.

column_definition

List of column_names and their CQL data types. The optional PRIMARY KEY clause specifies the primary key column if the table has only a single partition key. The optional STATIC keyword is used to define a static column, which has a single value shared by all rows in a partition.

column_name

Name of the column.

cql_type

CQL data type

column_list

List of columns that make up the primary key. The first column is the partition key. If there is more than one column in the partition key, enclose the columns in parentheses. Additional columns are clustering columns.

partition_key_column_name

Name of the partition key column. One or more partition key columns are required.

cluster_column_name

Name of the clustering column. Optional. One or more clustering columns can be defined. Also define ASC or DESC for the clustering order.

ASC

vl_name

DSE Graph vertex label name.

el_name

DSE Graph edge label name.

IF NOT EXISTS

Allows silent skip by checking for existing tables with the same name in the same keyspace. If a match is found, the operation silently does nothing (no error is thrown). If no match is found, the operation creates the table.

This option is set immediately after CREATE TABLE. It isn’t part of the WITH clause.

Not set. If a table with the same name already exists in the same keyspace, an error occurs and the operation fails.

Options

Specify table options using the WITH keyword. Each option can have a single value. Some options have subproperties that are specified using a simple JSON format, with key-value pairs in a comma-separated list enclosed by curly braces.

Option Description Default

ADDITIONAL_WRITE_POLICY

Same as speculative_retry but for writes.

99p

BLOOM_FILTER_FP_CHANCE

False-positive probability for SSTable bloom filter.

When a client requests data, the bloom filter checks if the row exists before executing disk I/O. Values range from 0 to 1.0, where: 0 is the minimum value use to enable the largest possible bloom filter (uses the most memory) and 1.0 is the maximum value disabling the bloom filter.

The recommended and default setting is 0.1. Higher values yield diminishing returns.

0.01

CACHING

Optimizes the use of cache memory without manual tuning. Weighs the cached data by size and access frequency. Coordinate this setting with the global caching properties in the cassandra.yaml file. Valid values:

ALL-- all primary keys or rows
NONE-- no primary keys or rows
<N>: (rows per partition only) — specify a whole number Default: { 'keys': 'ALL', 'rows_per_partition': 'NONE' }

{'keys': 'ALL', 'rows_per_partition': 'NONE'}

CDC

Whether to create a change data capture (CDC) log on the table. Valid values:

TRUE- create CDC log
FALSE- do not create CDC log

false

COMMENT

Specify comments for the table.

''

COMPACTION

Configures the compaction strategy. See Compaction subproperties for more information.

COMPRESSION

Configures the compression algorithm. See Compression subproperties for more information.

CRC_CHECK_CHANCE

Probability that a read request will check the CRC of a row.

1.0

DEFAULT_TIME_TO_LIVE

TTL (Time To Live) in seconds, where zero is disabled. The maximum configurable value is 630720000 (20 years).

If the value is greater than 0, TTL is enabled for the entire table and an expiration timestamp is added to each column. A new TTL timestamp is calculated each time the data is updated and the row is removed after all the data expires.

For more information, see Data with time-to-live (TTL).

0

EXTENSIONS

Specifies the table extensions.

{}

GC_GRACE

The number of seconds after data is marked with a tombstone (deletion marker) until it is eligible for garbage collection.

In a single-node cluster, this property can safely be set to zero. You can also reduce this value for tables whose data is not explicitly deleted; for example, tables containing only data with TTL set, or tables with default_time_to_live set. Tombstone records within the grace period are excluded from hint or batched mutations. There are consideration to consider if you lower the value, such as the potential for data loss. See the usage notes for more information.

864000 (10 days) The default value allows time for the database to maximize consistency prior to deletion.

MAX_INDEX_INTERVAL

If the total memory usage of all index summaries reaches this value, CQL decreases the index summaries for the coldest SSTables to the maximum set by max_index_interval. The max_index_interval is the sparsest possible sampling in relation to memory pressure.

2048

MEMTABLE_FLUSH_PERIOD_IN_MS

A millisecond interval at which the table’s memtables are flushed.

The default is 0 (disabled). DataStax recommends leaving this disabled except for edge cases where specific tables require a fixed memtable flush cadence. For example, a table that is rarely modified doesn’t need to retain commit logs on disk for the extended amount of time that could elapse between naturally triggered flushes.

Regardless of memtable_flush_period_in_ms, the database flushes memtable data to disk in the following situations:

When memtable flush threshold is met.
On shutdown.
When you run nodetool flush or nodetool drain.
When commit logs are full.

You can also tune memtable flush behavior with the memtable parameters in cassandra.yaml.

0

MIN_INDEX_INTERVAL

Minimum gap between index entries in the index summary. A lower min_index_interval means the index summary contains more entries from the index, which allows the database to search fewer index entries to execute a read. A larger index summary may also use more memory. The value for min_index_interval is the densest possible sampling of the index.

128

NODESYNC

Manages the table repair settings in the format { 'enabled' : <value>, 'deadline_target_sec' : <seconds> }.

Set enabled to true (default) or false to enable/disable NodeSync on the table.

Set deadline_target_sec to the maximum number of seconds to validate all segments of the table. Make sure it is less than gc_grace_seconds to avoid resurrecting deleted data. DataStax recommends using nodetool nodesyncservice ratesimulator to calculate the appropriate setting.

enabled: true

deadline_target_sec: The larger of gc_grace_seconds or 4 days.

READ_REPAIR

Sets read repair behavior. Choices are BLOCKING or NONE.

BLOCKING

SPECULATIVE_RETRY

Configures rapid read protection at the table level.

Cassandra drivers also support client-level speculative retry. This is useful for query-specific retries and other use cases where a single table-level retry trigger isn’t ideal. For more information, see the documentation for your driver.

The database sends read requests to the minimum number of replica nodes to satisfy the consistency level. speculative_retry allows the database to send additional read requests to other replicas if a selected replica is slow to respond. The value of speculative_retry defines the retry trigger for a table:

ALWAYS: The coordinator node sends extra read requests to all other replicas (beyond the consistency level) for every read on the table. This is extremely resource intensive.
nPERCENTILE (default): n is an integer specifying a percentile tail read latency for retry, such as 99PERCENTILE or 95PERCENTILE. The database tracks each table’s typical read latency in milliseconds, which the coordinator node uses to calculate the nPERCENTILE latency. If a node fails to respond within the calculated latency, the coordinator node retries the request on another node.

For example, if speculative_retry is set to 80PERCENTILE, and a table’s typical latency is 60 milliseconds, then the coordinator node sends retries if a selected node doesn’t respond within 48 ms, which is 80 percent of 60 ms.

nms: n is an integer specifying a number of milliseconds, such as 50ms. The coordinator node sends retries if a node doesn’t respond within n milliseconds.
NONE: The coordinator node never retries read requests.

In most cases, the default value is acceptable. Avoid editing this property until you understand your read latencies and how speculative retries could impact them.

This property is irrelevant when the consistency level is ALL because all nodes are contacted for every read by default.

99p or 99PERCENTILE

Compression subproperties

Configure by specifying the compression algorithm class followed by the subproperties in simple JSON format. Custom compression classes can be implemented using the org.apache.cassandra.io.compress.ICompressor interface. Most applications should use the compression algorithms bundled with the database.

compression = {
   ['class' : '<compression_algorithm_name>',
     'chunk_length_in_kb' : '<value>',
     'crc_check_chance' : '<value>',]
   | 'sstable_compression' : '']
}

Property

Description

Default

class

Sets the compressor class name. The database provides the following built-in classes:

LZ4Compressor
SnappyCompressor
DeflateCompressor

Choosing the right compressor depends on your requirements for space savings over read performance. LZ4 is fastest to decompress, followed by Snappy, then by Deflate. Compression effectiveness is inversely correlated with decompression speed. The extra compression from DeflateCompressor or SnappyCompressor is not enough to make up for the decreased performance for general-purpose workloads, but for archival data they may be worth considering.

LZ4Compressor

chunk_length_in_kb

Size (in KB) of the block. On disk, SSTables are compressed by block to allow random reads. Values larger than the default value might improve the compression rate, but increases the minimum size of data to be read from disk when a read occurs. The default value is a good middle ground for compressing tables. Adjust compression size to account for read/write access patterns (how much data is typically requested at once) and the average size of rows in the table.

64

crc_check_chance

When compression is enabled, each compressed block includes a checksum of that block for the purpose of detecting disk bit rot and avoiding the propagation of corruption to other replica. This option defines the probability with which those checksums are checked during read. By default they are always checked. Set to 0 to disable checksum checking and to 0.5, for instance, to check them on every other read.

1.0

sstable_compression

Disables compression.

Specify a null value.

Compaction subproperties

Configure by specifying the compaction algorithm class followed by the subproperties in simple JSON format. See Data maintenance through compaction for more details.

compaction = {
     'class' : '<compaction_strategy_name>',
     '<property_name>' : <value> [, ...] }

where the compaction_strategy_name is UnifiedCompactionStrategy, SizeTieredCompactionStrategy, TimeWindowCompactionStrategy, or LeveledCompactionStrategy.

Common properties

The following properties apply to all compaction strategies.

compaction = {
    'class' : 'compaction_strategy_name',
    'enabled' : (true | false),
    'log_all' : (true | false),
    'only_purge_repaired_tombstone' : (true | false),
    'tombstone_threshold' : <ratio>,
    'tombstone_compaction_interval' : <sec>,
    'unchecked_tombstone_compaction' : (true | false) }

Property Description Default

class

Sets the compaction strategy class name. The database provides the following built-in classes:

UnifiedCompactionStrategy
SizeTieredCompactionStrategy
LeveledCompactionStrategy
TimeWindowCompactionStrategy

See Data maintenance through compaction to help choose the appropriate compaction strategy for your workload.

ifdef::env-hcd[UnifiedCompactionStrategy] SizeTieredCompactionStrategy

enabled

Enable or disable background compaction.

true enables minor compactions.
false disables minor compactions.

Use nodetool enableautocompaction to resume minor compactions.

Disabling background compaction can be harmful. Without compaction, the database cannot reclaim disk space, and zombies can be replicated. Although compaction consumes I/O resources, leave it enabled in almost all cases.

true

log_all

Activates extended compaction logging for the entire cluster.

false

only_purge_repaired_tombstone

Enabling this property prevents data from resurrecting when repair is not run within the gc_grace_seconds. When its been a long time between repairs, the database keeps all tombstones.

true - Only allow tombstone purges on repaired SSTables.
false - Purge tombstones on SSTables during compaction even if the table has not been repaired.

false

tombstone_threshold

The ratio of garbage-collectable tombstones to all contained columns. If the ratio exceeds this limit, compactions starts only on that table to purge the tombstones.

0.2

tombstone_compaction_interval

Number of seconds before compaction can run on an SSTable after it is created. An SSTable is eligible for compaction when it exceeds the tombstone_threshold. Because it might not be possible to drop tombstones when doing a single SSTable compaction, and since the compaction is triggered base on an estimated tombstone ratio, this setting makes the minimum interval between two single SSTable compactions tunable to prevent an SSTable from being constantly re-compacted.

86400 (1 day)

unchecked_tombstone_compaction

Setting to true allows tombstone compaction to run without pre-checking which tables are eligible for the operation. Even without this pre-check, the database checks an SSTable to make sure it is safe to drop tombstones.

false

provide_overlapping_tombstones

Compaction can consider overlapping tables that aren’t part of a compaction operation when checking for deleted or overwritten data in compacted tables. If such data is found, it can be safely discarded, which enables removal of tombstones over that data. Set to one of the following:

ROW: Overlapping tables are considered, and the operation discards only entire deleted rows or sets of rows.
CELL: Overlapping tables are considered, and the operation discards any cell (column within a row) with a deleted/overwritten value.
NONE: Overlapping tables aren’t considered when checking for deletes or overwrites.

NONE

UnifiedCompactionStrategy

The UnifiedCompactionStrategy (UCS) is disabled by default in DSE 6.8 and 6.9. To enable UCS, set -Ddse.ucs.enabled=true in the jvm-server.options file.

The compaction class UnifiedCompactionStrategy (UCS) is a hybrid compaction strategy that combines the benefits of STCS and LCS. UCS uses a tiered compaction strategy for small SSTables and a leveled compaction strategy for large SSTables. See UnifiedCompactionStrategy (UCS).

The following additional properties apply to UnifiedCompactionStrategy:

compaction = {
    'class' : 'UnifiedCompactionStrategy',
    'expired_sstable_check_frequency_seconds' : <sec>,
    'max_sstables_to_compact' : <max>,
    'unsafe_aggressive_sstable_expiration' : (true | false),
    'min_sstable_size_in_mb' : <int>,
    'max_space_overhead' : <int>,
    'dataset_size_in_gb' : <int>,
    'static_scaling_factors' : <int>,
    'num_shards' : <int> }

Property Description Default

expired_sstable_check_frequency_seconds

The frequency in seconds at which SSTables are checked for expiration.

600 (10 minutes )

max_sstables_to_compact

The maximum number of SSTables to include in a single compaction.

no maximum

unsafe_aggressive_sstable_expiration

Expired SSTables are dropped without checking if their data is shadowing other SSTables.

Setting this property to true can result in data correctness issues or data loss.

false

static_scaling_factors

An integer scaling factor (ω). See Configure compaction for more details.

2

min_sstable_size_in_mb

Set the minimum SSTable size in MB for shards. SSTables smaller than the minimum aren’t split on shard boundaries. A higher value results in fewer SSTables and larger compaction operations at the lowest levels of the hierarchy.

100

num_shards

Sets the number of shards. More shards means more parallelism and smaller SSTables at the higher levels at the expense of somewhat higher CPU usage.

10 x the number of disk volumes defined in cassandra.yaml.

SizeTieredCompactionStrategy

The compaction class SizeTieredCompactionStrategy (STCS) is the default compaction strategy. The compaction class SizeTieredCompactionStrategy (STCS) triggers a minor compaction when table meets the min_threshold. Minor compactions do not involve all the tables in a keyspace. See SizeTieredCompactionStrategy (STCS).

The following additional properties apply to SizeTieredCompactionStrategy:

compaction = {
     'class' : 'SizeTieredCompactionStrategy',
     'bucket_high' : <factor>,
     'bucket_low' : <factor>,
     'min_sstable_size' : <int> }

Property Description Default

Property	Description	Default
`bucket_high`	Size-tiered compaction merges sets of SSTables that are approximately the same size. The database compares each SSTable size to the average of all SSTable sizes for this table on the node. It merges SSTables whose size in KB are within [average-size * bucket_low] and [average-size * bucket_high].	`1.5`
`bucket_low`	Size-tiered compaction merges sets of SSTables that are approximately the same size. The database compares each SSTable size to the average of all SSTable sizes for this table on the node. It merges SSTables whose size in KB are within [average-size * bucket_low] and [average-size * bucket_high].	`0.5`
`min_sstable_size`	STCS groups SSTables into buckets. The bucketing process groups SSTables that differ in size by less than 50%. This bucketing process is too fine-grained for small SSTables. If your SSTables are small, use this option to define a size threshold in MB below which all SSTables belong to one unique bucket.	`50` (MB)
`min_threshold`	The minimum number of SSTables to trigger a minor compaction.	`4`
`max_threshold`	The maximum number of SSTables before a minor compaction is triggered.	`32`
`cold_reads_to_omit`	Deprecated

bucket_high

Size-tiered compaction merges sets of SSTables that are approximately the same size. The database compares each SSTable size to the average of all SSTable sizes for this table on the node. It merges SSTables whose size in KB are within [average-size * bucket_low] and [average-size * bucket_high].

1.5

bucket_low

0.5

min_sstable_size

STCS groups SSTables into buckets. The bucketing process groups SSTables that differ in size by less than 50%. This bucketing process is too fine-grained for small SSTables. If your SSTables are small, use this option to define a size threshold in MB below which all SSTables belong to one unique bucket.

50 (MB)

min_threshold

The minimum number of SSTables to trigger a minor compaction.

4

max_threshold

The maximum number of SSTables before a minor compaction is triggered.

32

cold_reads_to_omit

Deprecated

TimeWindowCompactionStrategy

The compaction class TimeWindowCompactionStrategy (TWCS) compacts SSTables using a series of time windows or buckets. TWCS creates a new time window within each successive time period. During the active time window, TWCS compacts all SSTables flushed from memory into larger SSTables using STCS. At the end of the time period, all of these SSTables are compacted into a single SSTable. Then the next time window starts and the process repeats. See TimeWindowCompactionStrategy (TWCS).

Because TWCS uses STCS for minor compactions in the active time window, you can use all STCS properties with the TimeWindowCompactionStrategy class to configure the active window’s compaction behavior.

Supported properties for TWCS include all STCS properties and the following additional TWCS-specific properties:

compaction = {
     'class' : 'TimeWindowCompactionStrategy',
     'compaction_window_unit' : <days>,
     'compaction_window_size' : <int>,
     'split_during_flush' : (true | false),
     'min_threshold' : <int>,
     'max_threshold' : <int> }

Property Description Default

Property	Description	Default
`compaction_window_unit`	Time unit used to define the bucket size. For accepted values, see Java `TimeUnit`.	`days`
`compaction_window_size`	Units per bucket.	`1`
`split_during_flush`	Prevents mixing older data from repairs and hints with newer data from the current time window. During a flush operation, determines whether data partitions are split based on the configured time window. `false` - the data partitions are not split based on the configured time window. `true` - ensure that data repaired by NodeSync is placed in the correct TWCS window. Enable `split_during_flush` when using NodeSync with TWCS or when running node repairs. During the flush operation, the data is split into a maximum of 12 windows. Each window holds the data in a separate SSTable. If the current time is <t0> and each window has a time duration of <w>, the data is split in the SSTables as follows: SSTable 0 contains data for the time period < <t0> - 10 * <w> SSTables 1 to 10 contain data for the 10 equal time periods from (<t0> - 10 * <w>) through to (<t0> - 1 * <w>) SSTable 11, the 12th table, contains data for the time period > <t0>	`false`
`min_threshold`	The minimum number of SSTables to trigger a minor compaction.	`4`
`max_threshold`	The maximum number of SSTables before a minor compaction is triggered.	`32`

compaction_window_unit

Time unit used to define the bucket size. For accepted values, see Java TimeUnit.

days

compaction_window_size

Units per bucket.

1

split_during_flush

Prevents mixing older data from repairs and hints with newer data from the current time window. During a flush operation, determines whether data partitions are split based on the configured time window.

false - the data partitions are not split based on the configured time window.
true - ensure that data repaired by NodeSync is placed in the correct TWCS window. Enable split_during_flush when using NodeSync with TWCS or when running node repairs.

During the flush operation, the data is split into a maximum of 12 windows. Each window holds the data in a separate SSTable. If the current time is <t0> and each window has a time duration of <w>, the data is split in the SSTables as follows:

SSTable 0 contains data for the time period < <t0> - 10 * <w>
SSTables 1 to 10 contain data for the 10 equal time periods from (<t0> - 10 * <w>) through to (<t0> - 1 * <w>)
SSTable 11, the 12th table, contains data for the time period > <t0>

false

min_threshold

The minimum number of SSTables to trigger a minor compaction.

4

max_threshold

The maximum number of SSTables before a minor compaction is triggered.

32

LeveledCompactionStrategy

The compaction class LeveledCompactionStrategy (LCS) creates SSTables of a fixed, relatively small size (160 MB by default) that are grouped into levels. Within each level, SSTables are guaranteed to be non-overlapping. Each level (L0, L1, L2 and so on) is 10 times as large as the previous. Disk I/O is more uniform and predictable on higher than on lower levels as SSTables are continuously being compacted into progressively larger levels. At each level, row keys are merged into non-overlapping SSTables in the next level. See LeveledCompactionStrategy (LCS).

The following additional properties apply to LeveledCompactionStrategy:

compaction = {
     'class' : 'LeveledCompactionStrategy',
     'sstable_size_in_mb' : <int>,
     'min_threshold' : <int>,
     'max_threshold' : <int> }

Property Description Default

sstable_size_in_mb

The target size for SSTables that use the LeveledCompactionStrategy. Although SSTable sizes should be less or equal to sstable_size_in_mb, it is possible that compaction could produce a larger SSTable during compaction. This occurs when data for a given partition key is exceptionally large. The database does not split the data into two SSTables.

The default value, 160 MB, may be inefficient and negatively impact database indexing and the queries that rely on indexes. For example, consider the benefit of using higher values for sstable_size_in_mb in tables that use (SAI) indexes. For related information, see Compaction strategies.

160

min_threshold

The minimum number of SSTables to trigger a minor compaction.

4

max_threshold

The maximum number of SSTables before a minor compaction is triggered.

32

DateTieredCompactionStrategy (deprecated)

Use TimeWindowCompactionStrategy instead.

Stores data written within a certain period of time in the same SSTable.

Property Description Default

Property	Description	Default
`base_time_seconds`	The size of the first time window.	`3600`
`max_sstable_age_days` (deprecated)	The database does not compact SSTables if its most recent data is older than this property. Fractional days can be set.	`1000`
`max_window_size_seconds`	The maximum window size in seconds.	`86400`
`timestamp_resolution`	Units, <MICROSECONDS> or <MILLISECONDS>, to match the timestamp of inserted data.	`MICROSECONDS`

base_time_seconds

The size of the first time window.

3600

max_sstable_age_days (deprecated)

The database does not compact SSTables if its most recent data is older than this property. Fractional days can be set.

1000

max_window_size_seconds

The maximum window size in seconds.

86400

timestamp_resolution

Units, <MICROSECONDS> or <MILLISECONDS>, to match the timestamp of inserted data.

MICROSECONDS

Usage Notes

All tables must have a primary key, made up of a partition key and zero or more clustering columns. A partition key can be a single column or a composite partition key composed of multiple columns. A static column is shared by all rows in a partition and is stored only once per partition. A static column cannot be part of the primary key. Primary keys can include frozen collections and UDTs.

GC Grace Period Considerations

If you reduce the GC grace period setting, consider its interaction with these operations:

hint replays: When a node goes down and then comes back up, other nodes replay the write operations (called hinted handoff) that are queued for that node while it was unresponsive. The database does not replay hints that are older than the GC grace period. The maximum hint window setting in the cassandra.yaml file sets the time limit (3 hours by default) for collecting hints for the unresponsive node.
batch replays: Like hint queues, batch operations store database mutations that are replayed in sequence. As with hints, the database does not replay a batched mutation that is older than the GC grace period after creation. If your application uses batch operations, be aware that decreasing the GC grace period increases the chance that a batched write operation might restore deleted data. The batchlog replay throttle setting in the cassandra.yaml file give some control of the batch replay process. The most important factors, however, are the size and scope of the batches you use.

Primary key examples

Table with one partition key (single-column primary key)

In this example, the table is created with three columns. The id column is specified as a single partition key in the PRIMARY KEY clause.

CREATE TABLE cycling.cyclist_name (
  id UUID,
  lastname text,
  firstname text,
  PRIMARY KEY (id)
);

For simple primary key definitions, you can also append the PRIMARY KEY keyword to the column definiton:

CREATE TABLE cycling.cyclist_name (
  id UUID PRIMARY KEY,
  lastname text,
  firstname text
);

Multi-column (composite) partition key

To define a composite partition key, specify all relevant columns in the PRIMARY KEY clause, enclosed in parentheses. Nested parenthesis are required when there are clustering columns.

In this example, race_year and race_name are the two columns that comprise the composite partition key. They are encased in parentheses, inside the PRIMARY KEY definition. The column rank is an additional column that is a clustering column, as the next section describes.

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

Clustering columns

Within the primary key definition, the clustering columns are the columns that aren’t part of the partition key. As shown in the previous example, the partition key is either the first column defined in the primary key, or a composite partition key wrapped in parentheses.

In the following example, the category column is the partition key, and the points column is a clustering column:

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

Remember that the primary key must uniquely identify a row within the table. In this example, more than one row with the same category can exist, as long as that row contains a different points value.

All the rows with the same category will sort by the points values in descending order based on the CLUSTER ORDER BY option. By default, columns are sorted in ascending alphabetical order.

Column definition examples

The following examples demonstrate how to define columns with different data types in a table schema.

Create a table with a frozen UDT column

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

Create a table with a geospatial type column

CREATE TABLE cycling.geospatial (
  id text PRIMARY KEY,
  point 'PointType',
  linestring 'LineStringType'
);

The resulting table definition shows the class names for the geospatial types used in the table columns. In the following example, the WITH clause is truncated to focus on the column definitions.

CREATE TABLE cycling.geospatial (
    id text PRIMARY KEY,
    linestring 'org.apache.cassandra.db.marshal.LineStringType',
    point 'org.apache.cassandra.db.marshal.PointType'
) WITH ... ;

Table options examples

WITH CDC

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. For more information, see Change Data Capture (CDC) logging.

Create a table named cyclist_id with change data capture logging:

CREATE TABLE IF NOT EXISTS cycling.cyclist_id (
  lastname text,
  firstname text,
  age int,
  id UUID,
  PRIMARY KEY ((lastname, firstname), age)
) WITH CDC = TRUE;

You can also use ALTER TABLE to enable or disable CDC logging after creating a table.

WITH VERTEX LABEL (Graph)

Create a 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;

WITH EDGE LABEL (Graph)

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

Additional CREATE TABLE examples

Create a table in the current keyspace

When creating a table in the current keyspace (selected with the USE command), you can omit the keyspace name:

CREATE TABLE cyclist_name (
  id UUID PRIMARY KEY,
  lastname text,
  firstname text
);

Create a table with name of mixed case

If the table name has both uppercase and lowercase letters, use double quotes to preserve the casing:

CREATE TABLE cycling."addThis" (
  a int PRIMARY KEY
);

Unless you need keyspaces or tables with uppercase or mixed case names, DataStax recommends using lowercase only. If your keyspace or table creation commands use uppercase without double quotes, the name is normalized to lowercase.

Re-create a table with the original table ID and use commit log replay to restore data

If you need to restore data, you can re-create the table with its original ID and then use commit log replay to restore the data. To do this, append WITH ID to the CREATE TABLE statement:

CREATE TABLE IF NOT EXISTS cycling.cyclist_emails (
  userid text PRIMARY KEY,
  id UUID,
  emails set<text>
)
WITH ID = '1bb7516e-b140-11e8-96f8-529269fb1459';

The value of ID must be the original table ID. The response from DESCRIBE TABLE doesn’t include the table ID. 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';

 id
--------------------------------------
 1bb7516e-b140-11e8-96f8-529269fb1459

(1 rows)

To perform a point-in-time restoration of a table, see the documentation for DSE.

Read extended compaction logs

If you enable extended compaction logging for any table on any node, it is enabled for all tables on all nodes in the cluster.

Set the log_all subproperty to true to collect in-depth information about compaction activity on a node in a dedicated log file. When extended compaction is enabled, the database creates a file named compaction-%d.log (where %d is a sequential number) in home/logs.

The compaction logging service logs detailed information about the following types of compaction events:

type:enable

Lists SSTables that have been flushed previously.

{"type":"enable","keyspace":"test","table":"t","time":1470071098866,"strategies":
  [    {"strategyId":"0","type":"LeveledCompactionStrategy","tables":[],"repaired":true,"folders":
      ["/home/carl/oss/cassandra/bin/../data/data"]},
    {"strategyId":"1","type":"LeveledCompactionStrategy","tables":[],"repaired":false,"folders":
      ["/home/carl/oss/cassandra/bin/../data/data"]
    }
 ]
}

type: flush

Logs a flush event from a memtable to an SSTable on disk, including the CompactionStrategy for each table.

{"type":"flush","keyspace":"test","table":"t","time":1470083335639,"tables":
  [    {"strategyId":"1","table":
      {"generation":1,"version":"mb","size":106846362,"details":
        {"level":0,"min_token":"-9221834874718566760","max_token":"9221396997139245178"}
      }
    }
 ]
}

type: compaction

Logs a compaction event.

{"type":"compaction","keyspace":"test","table":"t","time":1470083660267,
 "start":"1470083660188","end":"1470083660267","input":
  [    {"strategyId":"1","table":
      {"generation":1372,"version":"mb","size":1064979,"details":
        {"level":1,"min_token":"7199305267944662291","max_token":"7323434447996777057"}
      }
    }
 ],"output":
  [    {"strategyId":"1","table":
      {"generation":1404,"version":"mb","size":1064306,"details":
        {"level":2,"min_token":"7199305267944662291","max_token":"7323434447996777057"}
      }
    }
 ]
}

type: pending

Lists the number of pending tasks for a compaction strategy.

{"type":"pending","keyspace":"test","table":"t",
 "time":1470083447967,"strategyId":"1","pending":200}

CREATE TABLE

Dynamic schema generation and schema collision

Syntax

Parameters

Options

Compression subproperties

Compaction subproperties

Usage Notes

GC Grace Period Considerations

Primary key examples

Column definition examples

Create a table with a frozen UDT column

Create a table with a geospatial type column

Table options examples

Additional CREATE TABLE examples

Re-create a table with the original table ID and use commit log replay to restore data

Read extended compaction logs

Was this helpful?

Give Feedback