System keyspaces and tables

The database uses a set of internal keyspaces to store metadata about the cluster, its schema, security, and operational state. The tables in these keyspaces are managed by the database; do not modify them directly.

The system keyspace

Tables in the system keyspace store metadata about the local node.

Table name Column names Comment

Table name	Column names	Comment
`available_ranges`	`keyspace_name`, `ranges`
`batches`	`id`, `mutations`, `version`
`batchlog`	`id`, `data`, `version`, `written_at`
`built_views`	`keyspace_name`, `view_name`	Information on materialized views.
`compaction_history`	`id`, `bytes_in`, `bytes_out`, `columnfamily_name`, `compacted_at`, `keyspace_name`, `rows_merged`	Information on compaction history.
`"IndexInfo"`	`table_name`, `index_name`	Information on indexes.
`local`	`key`, `bootstrapped`, `broadcast_address`, `cluster_name`, `cql_version`, `data_center`, `dse_version`, `gossip_generation`, `graph`, `host_id`, `jmx_port`, `listen_address`, `native_protocol_version`, `native_transport_address`, `native_transport_port`, `native_transport_port_ssl`, `partitioner`, `rack`, `release_version`, `rpc_address`, `schema_version`, `server_id`, `storage_port`, `storage_port_ssl`, `tokens`, `truncated_at`, `workload`, `workloads`	Information that a node has about itself and a superset of gossip. Gossip is a peer-to-peer communication protocol for exchanging location and state information between nodes.
`paxos`	`row_key`, `cf_id`, `in_progress_ballot`, `most_recent_commit`, `most_recent_commit_at`, `most_recent_commit_version`, `proposal`, `proposal_ballot`, `proposal_version`	Information on lightweight Paxos transactions.
`peers`	`peer`, `data_center`, `dse_version`, `graph`, `host_id`, `jmx_port`, `native_transport_address`, `native_transport_port`, `native_transport_port_ssl`, `preferred_ip`, `rack`, `release_version`, `rpc_address`, `schema_version`, `server_id`, `storage_port`, `storage_port_ssl`, `tokens`, `workload`, `workloads`	Each node records what other nodes tell it about themselves over the gossip.
`peer_events`	`peer`, `hints_dropped`
`prepared_statements`	`prepared_id`, `logged_keyspace`, `query_string`	Prepared statements used by drivers for queries.
`range_xfers`	`token_bytes`, `requested_at`
`size_estimates`	`keyspace_name`, `table_name`, `range_start`, `range_end`, `mean_partition_size`, `partitions_count`	Information on partitions.
`sstable_activity`	`keyspace_name`, `columnfamily_name`, `generation`, `rate_120m`, `rate_15m`
`views_builds_in_progress`	`keyspace_name`, `view_name`, `generation_number`, `last_token`

available_ranges

keyspace_name, ranges

batches

id, mutations, version

batchlog

id, data, version, written_at

built_views

keyspace_name, view_name

Information on materialized views.

compaction_history

id, bytes_in, bytes_out, columnfamily_name, compacted_at, keyspace_name, rows_merged

Information on compaction history.

"IndexInfo"

table_name, index_name

Information on indexes.

local

key, bootstrapped, broadcast_address, cluster_name, cql_version, data_center, dse_version, gossip_generation, graph, host_id, jmx_port, listen_address, native_protocol_version, native_transport_address, native_transport_port, native_transport_port_ssl, partitioner, rack, release_version, rpc_address, schema_version, server_id, storage_port, storage_port_ssl, tokens, truncated_at, workload, workloads

Information that a node has about itself and a superset of gossip. Gossip is a peer-to-peer communication protocol for exchanging location and state information between nodes.

paxos

row_key, cf_id, in_progress_ballot, most_recent_commit, most_recent_commit_at, most_recent_commit_version, proposal, proposal_ballot, proposal_version

Information on lightweight Paxos transactions.

peers

peer, data_center, dse_version, graph, host_id, jmx_port, native_transport_address, native_transport_port, native_transport_port_ssl, preferred_ip, rack, release_version, rpc_address, schema_version, server_id, storage_port, storage_port_ssl, tokens, workload, workloads

Each node records what other nodes tell it about themselves over the gossip.

peer_events

peer, hints_dropped

prepared_statements

prepared_id, logged_keyspace, query_string

Prepared statements used by drivers for queries.

range_xfers

token_bytes, requested_at

size_estimates

keyspace_name, table_name, range_start, range_end, mean_partition_size, partitions_count

Information on partitions.

sstable_activity

keyspace_name, columnfamily_name, generation, rate_120m, rate_15m

views_builds_in_progress

keyspace_name, view_name, generation_number, last_token

The system_schema keyspace

Use CQL to query system_schema tables to view keyspace, table, and column information. The same information is also available using the CQL shell DESCRIBE commands, which is the preferred method. The system_schema tables does not show search index or row-level access control settings.

Enable system_keyspaces_filtering to restrict schema visibility. Users will only see schema information for objects they have been granted permission to access.

Table name Column names Comment

Table name	Column names	Comment
`aggregates`	`keyspace_name`, `aggregate_name`, `argument_types`, `final_func`, `initcond`, `return_type`, `state_func`, `state_type`	Information about user-defined aggregates.
`columns`	`keyspace_name`, `table_name`, `column_name`, `clustering_order`, `column_name_bytes`, `kind`, `position`, `type`	Information about table columns.
`dropped_columns`	`keyspace_name`, `table_name`, `column_name`, `dropped_time`, `type`	Information about dropped columns.
`functions`	`keyspace_name`, `function_name`, `argument_types`, `argument_names`, `body`, `called_on_null_input`, `language`, `return_type`	Information on user-defined functions.
`indexes`	`keyspace_name`, `table_name`, `index_name`, `kind`, `options`	Information about indexes.
`keyspaces`	`keyspace_name`, `durable_writes`, `replication`	Information on keyspace durable writes and replication.
`tables`	`keyspace_name`, `table_name`, `bloom_filter_fp_chance`, `caching`, `comment`, `compaction`, `compression`, `crc_check_chance`, `default_time_to_live`, `extensions`, `flags`, `gc_grace_seconds`, `id`, `max_index_interval`, `memtable_flush_period_in_ms`, `min_index_interval`, `speculative_retry`	Information on columns and column indexes. Used internally for compound primary keys.
`types`	`keyspace_name`, `type_name`, `field_names`, `field_types`	Information about user-defined types.
`views`	`keyspace_name`, `view_name`, `base_table_id`, `base_table_name`, `bloom_filter_fp_chance`, `caching`, `comment`, `compaction`, `compression`, `crc_check_chance`, `default_time_to_live`, `extensions`, `flags`, `gc_grace_seconds`, `include_all_columns`, `max_index_interval`, `memtable_flush_period_in_ms`, `min_index_interval`, `speculative_retry`, `where_clause`	Information about materialized views.

aggregates

keyspace_name, aggregate_name, argument_types, final_func, initcond, return_type, state_func, state_type

Information about user-defined aggregates.

columns

keyspace_name, table_name, column_name, clustering_order, column_name_bytes, kind, position, type

Information about table columns.

dropped_columns

keyspace_name, table_name, column_name, dropped_time, type

Information about dropped columns.

functions

keyspace_name, function_name, argument_types, argument_names, body, called_on_null_input, language, return_type

Information on user-defined functions.

indexes

keyspace_name, table_name, index_name, kind, options

Information about indexes.

keyspaces

keyspace_name, durable_writes, replication

Information on keyspace durable writes and replication.

tables

keyspace_name, table_name, bloom_filter_fp_chance, caching, comment, compaction, compression, crc_check_chance, default_time_to_live, extensions, flags, gc_grace_seconds, id, max_index_interval, memtable_flush_period_in_ms, min_index_interval, speculative_retry

Information on columns and column indexes. Used internally for compound primary keys.

types

keyspace_name, type_name, field_names, field_types

Information about user-defined types.

views

keyspace_name, view_name, base_table_id, base_table_name, bloom_filter_fp_chance, caching, comment, compaction, compression, crc_check_chance, default_time_to_live, extensions, flags, gc_grace_seconds, include_all_columns, max_index_interval, memtable_flush_period_in_ms, min_index_interval, speculative_retry, where_clause

Information about materialized views.

The system_distributed keyspace

Tables in system_distributed keyspace store metadata that is shared across nodes.

Table name Column names Comment

Table name	Column names	Comment
`repair_history`	`keyspace_name`, `columnfamily_name`, `id`, `coordinator`, `exception_message`, `exception_stacktrace`, `finished_at`, `parent_id`, `participants`, `range_begin`, `range_end`, `started_at`, `status`	Contains detailed information on keyspace nodetool repair history.
`parent_repair_history`	`parent_id`, `columnfamily_names`, `exception_message`, `exception_stacktrace`, `finished_at`, `keyspace_name`, `options`, `requested_ranges`, `started_at`, `successful_ranges`
`view_build_status`	`keyspace_name`, `view_name`, `host_id`, `status`

repair_history

keyspace_name, columnfamily_name, id, coordinator, exception_message, exception_stacktrace, finished_at, parent_id, participants, range_begin, range_end, started_at, status

Contains detailed information on keyspace nodetool repair history.

parent_repair_history

parent_id, columnfamily_names, exception_message, exception_stacktrace, finished_at, keyspace_name, options, requested_ranges, started_at, successful_ranges

view_build_status

keyspace_name, view_name, host_id, status

The system_auth keyspace

Tables in the system_auth keyspace store role-based access control data, including role definitions, memberships, and resource permissions.

Table Columns Description

Table	Columns	Description
`role_permissions`	`role` (PK), `resource`, `permissions`	Stores the role, resource (for example keyspace_name/table_name), and the permission that the role has to access the resource.
`role_members`	`role` (PK), `member`	Stores the roles and role members.
`roles`	`role` (PK), `can_login`, `is_superuser`, `member_of`, `salted_hash`	Stores the role name, whether the role can be used for login, whether the role is a superuser, what other roles the role may be a member of, and a bcrypt salted hash password for the role.

role_permissions

role (PK), resource, permissions

Stores the role, resource (for example keyspace_name/table_name), and the permission that the role has to access the resource.

role_members

role (PK), member

Stores the roles and role members.

roles

role (PK), can_login, is_superuser, member_of, salted_hash

Stores the role name, whether the role can be used for login, whether the role is a superuser, what other roles the role may be a member of, and a bcrypt salted hash password for the role.

The dse_security keyspace

Tables in the dse_security` keyspace stores DSE-specific security data for role options, Kerberos digest authentication, and Spark shared secrets.

Table Columns Description

Table	Columns	Description
`role_options`	`role`, `options`	Role options.
`digest_tokens`	`id`, `password`	Kerberos digest tokens when enabled.
`spark_security`	`dc`, `shared_secret`	Shared secret for Spark.

role_options

role, options

Role options.

digest_tokens

id, password

Kerberos digest tokens when enabled.

spark_security

dc, shared_secret

Shared secret for Spark.

System keyspaces and tables

The system keyspace

The system_schema keyspace

The system_distributed keyspace

The system_auth keyspace

The dse_security keyspace

Was this helpful?

Give Feedback