Virtual keyspaces and tables

Virtual keyspaces and tables expose internal server data — such as configuration settings and metrics — as queryable tables. These virtual tables are not associated with physical files on disk, have no memtables or SSTables. Use CQL SELECT statements to retrieve this data without needing external monitoring tools or Java Management Extensions (JMX).

The system_virtual_schema keyspace

The system_virtual_schema keyspace exposes metadata virtual keyspace and table. The CQL shell DESCRIBE KEYSPACE and DESCRIBE TABLE commands also .

Virtual keyspaces and tables are managed internally by the database. You cannot create, alter, or drop them using DDL commands.

keyspaces
Column name	CQL type	Column type
keyspace_name	text	partition key

tables
Column name	CQL type	Column type
keyspace_name	text	partition key
table_name	text	clustering key
comment	text	regular

columns
Column Name	CQL Type	Column type
keyspace_name	text	partition key
table_name	text	clustering key
column_name	text	clustering key
clustering_order	text	regular
column_name_bytes	blob	regular
position	int	regular
type	text	regular

The system_views keyspace

The system_views keyspace provides a CQL interface to runtime data about the local node. You can query its virtual tables to inspect caches, connected clients, SSTable metadata, node and peer status, pending hints, and configuration settings.

caches
Column name	CQL type	Column type
`name`	`text`	primary key
`capacity_bytes`	`bigint`	regular
`entry_count`	`int`	regular
`hit_count`	`bigint`	regular
`hit_ratio`	`double`	regular
`recent_hit_rate_per_second`	`bigint`	regular
`recent_request_rate_per_second`	`bigint`	regular
`request_count`	`bigint`	regular
`size_bytes`	`bigint`	regular

clients
Column name	CQL type	Column type
`address`	`inet`	primary key
`port`	`int`	clustering key
`connection_stage`	`text`	regular
`driver_name`	`text`	regular
`driver_version`	`text`	regular
`hostname`	`text`	regular
`protocol_version`	`int`	regular
`request_count`	`bigint`	regular
`ssl_cipher_suite`	`text`	regular
`ssl_enabled`	`boolean`	regular
`ssl_protocol`	`text`	regular
`username`	`text`	regular

local_node
Column name	CQL type	Column type
`key`	`text`	primary key
`bootstrapped`	`text`	regular
`broadcast_address`	`inet`	regular
`cluster_name`	`text`	regular
`cql_version`	`text`	regular
`data_center`	`text`	regular
`dse_version`	`text`	regular
`gossip_generation`	`int`	regular
`graph`	`boolean`	regular
`host_id`	`uuid`	regular
`jmx_port`	`int`	regular
`last_nodesync_checkpoint_time`	`bigint`	regular
`listen_address`	`inet`	regular
`native_protocol_version`	`text`	regular
`native_transport_address`	`inet`	regular
`native_transport_port`	`int`	regular
`native_transport_port_ssl`	`int`	regular
`partitioner`	`text`	regular
`rack`	`text`	regular
`release_version`	`text`	regular
`rpc_address`	`inet`	regular
`schema_version`	`uuid`	regular
`server_id`	`text`	regular
`storage_port`	`int`	regular
`storage_port_ssl`	`int`	regular
`tokens`	`frozen<set<text>>`	regular
`truncated_at`	`frozen<map<uuid, blob>>`	regular
`workload`	`text`	regular
`workloads`	`frozen<set<text>>`	regular

node_status
Column name	CQL type	Column type
`id`	`text`	primary key
`gossip_active`	`boolean`	regular
`native_transport_active`	`boolean`	regular
`stop_node_exception`	`text`	regular
`stop_node_reason`	`text`	regular

peer_nodes
Column name	CQL type	Column type
`peer`	`inet`	primary key
`data_center`	`text`	regular
`dse_version`	`text`	regular
`graph`	`boolean`	regular
`host_id`	`uuid`	regular
`jmx_port`	`int`	regular
`native_transport_address`	`inet`	regular
`native_transport_port`	`int`	regular
`native_transport_port_ssl`	`int`	regular
`preferred_ip`	`inet`	regular
`rack`	`text`	regular
`release_version`	`text`	regular
`rpc_address`	`inet`	regular
`schema_version`	`uuid`	regular
`server_id`	`text`	regular
`storage_port`	`int`	regular
`storage_port_ssl`	`int`	regular
`tokens`	`frozen<set<text>>`	regular
`workload`	`text`	regular
`workloads`	`frozen<set<text>>`	regular

pending_hints
Column name	CQL type	Column type
`host_id`	`uuid`	primary key
`address`	`inet`	regular
`dc`	`text`	regular
`files`	`int`	regular
`keyspace_name`	`text`	regular
`newest`	`timestamp`	regular
`oldest`	`timestamp`	regular
`port`	`int`	regular
`rack`	`text`	regular
`status`	`text`	regular

settings
Column name	CQL type	Column type
`name`	`text`	primary key
`value`	`text`	regular
`writeable`	`boolean`	regular

sstable task
Column name	CQL type	Column type
keyspace_name	text	partition key
table_name	text	clustering key
task_id	uuid	clustering key
kind	text	regular
progress	bigint	regular
total	bigint	regular
unit	text	regular

The remaining amount of work can be computer using total - progress AS remaining in a SELECT clause.

Virtual tables and CQL shell

Virtual tables are exposed as local tables, so CQL shell will display data for the currently connected host. To displace schema for virtual tables, use DESCRIBE FULL SCHEMA to return schema for both the real keyspaces and the virtual ones.

Users are not allowed to create virtual keyspaces or virtual tables.

To see the schema for the virtual table sstable_tasks, use:

DESCRIBE TABLE system_views.sstable_tasks;

For example:

/*
Warning: Table system_views.sstable_tasks is a virtual table and cannot be re-created with CQL.
Structure, for reference:
VIRTUAL TABLE system_views.sstable_tasks (
    keyspace_name text,
    table_name text,
    task_id uuid,
    kind text,
    progress bigint,
    total bigint,
    unit text,
    PRIMARY KEY (keyspace_name, table_name, task_id)
) WITH CLUSTERING ORDER BY (table_name ASC, task_id ASC)
    AND comment = 'current sstable tasks';
*/

To discover how much time is remaining for SSTable tasks, use the following query:

SELECT total - progress AS remaining FROM system_views.sstable_tasks;

 remaining
-----------


(0 rows)

Virtual keyspaces and tables

The system_virtual_schema keyspace

The system_views keyspace

Virtual tables and CQL shell

Was this helpful?

Give Feedback