CREATE TABLE

Creates a new table in the specified (or current) keyspace. If the table already exists, no table is created and the statement returns an error. Use IF NOT EXISTS to suppress the error message.

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 ] 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 bracke ts ( < > ) 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

keyspace_name

Optional. Name of the keyspace that contains the table. .Keyspace and table name conventions

The name of a keyspace or a table is a string of alphanumeric characters and underscores, but it must begin with a letter. If case must be maintained, the name must be encased in double quotes, such as "MyTable".

Since tables are defined within a keyspace, you can either use the keyspace as part of the table creation command, or create a table in the current keyspace. To specify the keyspace as part of a table name, use the keyspace name, a period (.), and table name, such as cycling.cyclist-stats.

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

table_name

Name of the table.

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

Options

Specify table options using the WITH keyword. Each option can have a single value. Some options have sub-properties 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

Creates 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 sub-properties for more information.

COMPRESSION

Configures the compression algorithm. See Compression sub-properties 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). Beginning in 2018, the expiration timestamp can exceed the maximum value supported by the storage engine.

The database storage engine can only encode TTL timestamps through January 19 2038 03:14:07 UTC due to the Year 2038 problem.

The TTL date overflow policy (-Dcassandra.expiration_date_overflow_policy) determines whether requests with expiration timestamps later than the maximum date are rejected or inserted.

If the value is greater than zero, 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.

0

EXTENSIONS

Specifies the table extensions.

{}

GC_GRACE_SECONDS

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

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 sub-properties

Configure by specifying the compression algorithm class followed by the sub-properties 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 sub-properties

Configure by specifying the compaction algorithm class followed by the sub-properties 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.

UnifiedCompactionStrategy

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 does not reclaim disk space, and zombies can propagate. Although compaction consumes I/O resources, it should remain enabled in most cases.

true

log_all

Activates advanced 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, DSE checks an SSTable to make sure it is safe to drop tombstones.

false

UnifiedCompactionStrategy

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),
    'scaling_parameters' : <foo>,
    'overlap_inclusion_method': <foo>,
    'min_sstable_size' : <int>,
    'base_shard_count' : <foo>,
    'sstable_growth' : <foo>,
    'target_sstable_size' : <foo>,
    '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

scaling_parameters

A list of one or more per-level scaling parameters, like Lf, Tf or N. Alternatively, a single integer value (ω).

Examples:

scaling_parameters = 'L1,T2,N'
scaling_parameters = 'L4'
scaling_parameters = 3

See Configure compaction for more details.

'T4' equivalent to 2 (ω = 2)

static_scaling_factors

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

static_scaling_factors is provided for backward compatibility when migrating from DataStax Enterprise (DSE) to HCD. Don’t use static_scaling_factors for new HCD tables.

2

overlap_inclusion_method

Specifies how to handle overlapping SSTables during compaction.

Valid values are:

  • TRANSITIVE - compact table and all overlapping SSTables, including those that overlap with the overlapping SSTables.

  • SINGLE - compact table and only the SSTables that directly overlap with it.

  • NONE - compact table only, ignoring all overlapping SSTables.

DataStax does not recommend using NONE in production systems.

TRANSITIVE

min_sstable_size

Minimum size of an SSTable in bytes. Applies when the base_shard_count property would create SSTables smaller than this value. A value of 0 disables this feature.

100MiB

base_shard_count

The minimum number of shards used for levels with the smallest density.

4 (1 for system tables, or when multiple data locationsa are defined.)

sstable_growth

Value between 0 and 1 used to calculate SSTable size. Higher values result in fewer, larger SSTables. Lower values result in more, smaller SSTables.

0.333

target_sstable_size

The target SSTable size in bytes.

1GiB

num_shards

Setting this property enables legacy UCS V1, which is for backwards compatibility when migrating from DSE 6.8 or 6.9 to HCD.

Specifying a value for the num_shards property with the base_shard_count, sstable_growth, or target_sstable_size properties will result in an error.

Undefined, no default value.

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

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

The cold_reads_to_omit property for SizeTieredCompactionStrategy (STCS) is no longer supported.

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

All of the properties for STCS are also valid for TWCS.

The following additional properties apply to TimeWindowCompactionStrategy:

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

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 DSE 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

base_time_seconds

The size of the first time window.

3600

max_sstable_age_days (deprecated)

DSE 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 lower the value, 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 hints) that are queued for that node while it was unresponsive. The database does not replay hints older than gc_grace_seconds after creation. The max_hint_window_in_ms 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 older than gc_grace_seconds after creation. If your application uses batch operations, consider the possibility that decreasing gc_grace_seconds increases the chance that a batched write operation may restore deleted data. The batchlog_replay_throttle_in_kb property 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.

Examples

Was this helpful?

Give Feedback

How can we improve the documentation?

© Copyright IBM Corporation 2026 | Privacy policy | Terms of use |  Manage Privacy Choices

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: Contact IBM