About system keyspace tables
The system keyspace includes a number of tables that contain details about your database objects and cluster configuration.
Columns in system tables
| 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. |
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 |
| 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. |
| 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. |
nodesync_status |
keyspace_name, table_name, range_group, start_token, end_token, last_successful_validation, last_unsuccessful_validation, locked_by |
Contains NodeSync status for segments on the local system. See NodeSync Validation process and status. |
nodesync_user_validations |
id, node, keyspace_name, table_name, ended_at, metrics, outcomes, segments_to_validate, segments_validated, started_at, status, validated_ranges |
Contains details of NodeSyncs operations that were manually started. |
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 |
Security keyspaces and tables
Read access to these system tables is implicitly given to every authenticated user because the tables are used by most DSE tools:
system_auth keyspace
Contains authorization and internal authentication data.
| 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. |
dse_security keyspace
Contains DSE Spark, Kerberos digest data, and role options.
| Table | Columns | Description |
|---|---|---|
role_options |
role, options |
Role options. |
digest_tokens |
id, password |
Kerberos digest tokens when enabled. |
spark_security |
dc, shared_secret |
Share secret for Spark. |
- Keyspace, table, and column information
-
Use CQL to query system_schema tables to view keyspace, table, and column information.
- Querying system tables for cluster topology information
-
Query system tables to get cluster topology information.
- Functions, aggregates, and user types
-
Query system_schema functions, aggregates, and types tables to get information about user-defined functions, user-defined aggregates, and user-defined types.
