Virtual Tables

Apache Cassandra 4.0 implements virtual tables (CASSANDRA-7622). Virtual tables are tables backed by an API instead of data explicitly managed and stored as SSTables. Apache Cassandra 4.0 implements a virtual keyspace interface for virtual tables. Virtual tables are specific to each node.

Some of the features of virtual tables are the ability to:

expose metrics through CQL
expose YAML configuration information

Virtual keyspaces and tables are quite different from regular tables and keyspaces:

Virtual tables are created in special keyspaces and not just any keyspace.
Virtual tables are managed by Cassandra. Users cannot run DDL to create new virtual tables or DML to modify existing virtual tables.
Virtual tables are currently read-only, although that may change in a later version.
Virtual tables are local only, non-distributed, and thus not replicated.
Virtual tables have no associated SSTables.
Consistency level of the queries sent to virtual tables are ignored.
All existing virtual tables use LocalPartitioner. Since a virtual table is not replicated the partitioner sorts in order of partition keys instead of by their hash.
Making advanced queries using ALLOW FILTERING and aggregation functions can be executed in virtual tables, even though in normal tables we dont recommend it.

Virtual Keyspaces

Apache Cassandra 4.0 has added two new keyspaces for virtual tables:

system_virtual_schema
system_views.

The system_virtual_schema keyspace has three tables: keyspaces, columns and tables for the virtual keyspace, table, and column definitions, respectively. These tables contain schema information for the virtual tables. It is used by Cassandra internally and a user should not access it directly.

The system_views keyspace contains the actual virtual tables.

Virtual Table Limitations

Before disccusing virtual keyspaces and tables, note that virtual keyspaces and tables have some limitations. These limitations are subject to change. Virtual keyspaces cannot be altered or dropped. In fact, no operations can be performed against virtual keyspaces.

Virtual tables cannot be created in virtual keyspaces. Virtual tables cannot be altered, dropped, or truncated. Secondary indexes, types, functions, aggregates, materialized views, and triggers cannot be created for virtual tables. Expiring time-to-live (TTL) columns cannot be created. Virtual tables do not support conditional updates or deletes. Aggregates may be run in SELECT statements.

Conditional batch statements cannot include mutations for virtual tables, nor can a virtual table statement be included in a logged batch. In fact, mutations for virtual and regular tables cannot occur in the same batch table.

Virtual Tables

Each of the virtual tables in the system_views virtual keyspace contain different information.

The following table describes the virtual tables:

Virtual Table	Description
caches	Displays the general cache information including cache name, capacity_bytes, entry_count, hit_count, hit_ratio double, recent_hit_rate_per_second, recent_request_rate_per_second, request_count, and size_bytes.
clients	Lists information about all connected clients.
coordinator_read_latency	Records counts, keyspace_name, table_name, max, median, and per_second for coordinator reads.
coordinator_scan	Records counts, keyspace_name, table_name, max, median, and per_second for coordinator scans.
coordinator_write_latency	Records counts, keyspace_name, table_name, max, median, and per_second for coordinator writes.
cql_metrics	Metrics specific to CQL prepared statement caching.
disk_usage	Records disk usage including disk_space, keyspace_name, and table_name, sorted by system keyspaces.
gossip_info	Lists the gossip information for the cluster.
internode_inbound	Lists information about the inbound internode messaging.
internode_outbound	Information about the outbound internode messaging.
local_read_latency	Records counts, keyspace_name, table_name, max, median, and per_second for local reads.
local_scan	Records counts, keyspace_name, table_name, max, median, and per_second for local scans.
local_write_latency	Records counts, keyspace_name, table_name, max, median, and per_second for local writes.
max_partition_size	A table metric for maximum partition size.
rows_per_read	Records counts, keyspace_name, tablek_name, max, and median for rows read.
settings	Displays configuration settings in cassandra.yaml.
sstable_tasks	Lists currently running tasks and progress on SSTables, for operations like compaction and upgrade.
system_properties	Displays environmental system properties set on the node.
thread_pools	Lists metrics for each thread pool.
tombstones_per_read	Records counts, keyspace_name, tablek_name, max, and median for tombstones.

Virtual Table

Description

caches

Displays the general cache information including cache name, capacity_bytes, entry_count, hit_count, hit_ratio double, recent_hit_rate_per_second, recent_request_rate_per_second, request_count, and size_bytes.

clients

Lists information about all connected clients.

coordinator_read_latency

Records counts, keyspace_name, table_name, max, median, and per_second for coordinator reads.

coordinator_scan

Records counts, keyspace_name, table_name, max, median, and per_second for coordinator scans.

coordinator_write_latency

Records counts, keyspace_name, table_name, max, median, and per_second for coordinator writes.

cql_metrics

Metrics specific to CQL prepared statement caching.

disk_usage

Records disk usage including disk_space, keyspace_name, and table_name, sorted by system keyspaces.

gossip_info

Lists the gossip information for the cluster.

internode_inbound

Lists information about the inbound internode messaging.

internode_outbound

Information about the outbound internode messaging.

local_read_latency

Records counts, keyspace_name, table_name, max, median, and per_second for local reads.

local_scan

Records counts, keyspace_name, table_name, max, median, and per_second for local scans.

local_write_latency

Records counts, keyspace_name, table_name, max, median, and per_second for local writes.

max_partition_size

A table metric for maximum partition size.

rows_per_read

Records counts, keyspace_name, tablek_name, max, and median for rows read.

settings

Displays configuration settings in cassandra.yaml.

sstable_tasks

Lists currently running tasks and progress on SSTables, for operations like compaction and upgrade.

system_properties

Displays environmental system properties set on the node.

thread_pools

Lists metrics for each thread pool.

tombstones_per_read

Records counts, keyspace_name, tablek_name, max, and median for tombstones.

We shall discuss some of the virtual tables in more detail next.

Clients Virtual Table

The clients virtual table lists all active connections (connected clients) including their ip address, port, client_options, connection stage, driver name, driver version, hostname, protocol version, request count, ssl enabled, ssl protocol and user name:

cqlsh> EXPAND ON ;
Now Expanded output is enabled
cqlsh> SELECT * FROM system_views.clients;

@ Row 1
------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 address          | 127.0.0.1
 port             | 50687
 client_options   | {'CQL_VERSION': '3.4.6', 'DRIVER_NAME': 'DataStax Python Driver', 'DRIVER_VERSION': '3.25.0'}
 connection_stage | ready
 driver_name      | DataStax Python Driver
 driver_version   | 3.25.0
 hostname         | localhost
 protocol_version | 5
 request_count    | 16
 ssl_cipher_suite | null
 ssl_enabled      | False
 ssl_protocol     | null
 username         | anonymous

@ Row 2
------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 address          | 127.0.0.1
 port             | 50688
 client_options   | {'CQL_VERSION': '3.4.6', 'DRIVER_NAME': 'DataStax Python Driver', 'DRIVER_VERSION': '3.25.0'}
 connection_stage | ready
 driver_name      | DataStax Python Driver
 driver_version   | 3.25.0
 hostname         | localhost
 protocol_version | 5
 request_count    | 4
 ssl_cipher_suite | null
 ssl_enabled      | False
 ssl_protocol     | null
 username         | anonymous

@ Row 3
------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 address          | 127.0.0.1
 port             | 50753
 client_options   | {'APPLICATION_NAME': 'TestApp', 'APPLICATION_VERSION': '1.0.0', 'CLIENT_ID': '55b3efbd-c56b-469d-8cca-016b860b2f03', 'CQL_VERSION': '3.0.0', 'DRIVER_NAME': 'DataStax Java driver for Apache Cassandra(R)', 'DRIVER_VERSION': '4.13.0'}
 connection_stage | ready
 driver_name      | DataStax Java driver for Apache Cassandra(R)
 driver_version   | 4.13.0
 hostname         | localhost
 protocol_version | 5
 request_count    | 18
 ssl_cipher_suite | null
 ssl_enabled      | False
 ssl_protocol     | null
 username         | anonymous

@ Row 4
------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 address          | 127.0.0.1
 port             | 50755
 client_options   | {'APPLICATION_NAME': 'TestApp', 'APPLICATION_VERSION': '1.0.0', 'CLIENT_ID': '55b3efbd-c56b-469d-8cca-016b860b2f03', 'CQL_VERSION': '3.0.0', 'DRIVER_NAME': 'DataStax Java driver for Apache Cassandra(R)', 'DRIVER_VERSION': '4.13.0'}
 connection_stage | ready
 driver_name      | DataStax Java driver for Apache Cassandra(R)
 driver_version   | 4.13.0
 hostname         | localhost
 protocol_version | 5
 request_count    | 7
 ssl_cipher_suite | null
 ssl_enabled      | False
 ssl_protocol     | null
 username         | anonymous

(4 rows)

Some examples of how clients can be used are:

To find applications using old incompatible versions of drivers before upgrading and with nodetool enableoldprotocolversions and nodetool disableoldprotocolversions during upgrades.
To identify clients sending too many requests.
To find if SSL is enabled during the migration to and from ssl.
To identify all options the client is sending, e.g. APPLICATION_NAME and APPLICATION_VERSION

The virtual tables may be described with DESCRIBE statement. The DDL listed however cannot be run to create a virtual table. As an example describe the system_views.clients virtual table:

cqlsh> DESCRIBE TABLE system_views.clients;

/*
Warning: Table system_views.clients is a virtual table and cannot be recreated with CQL.
Structure, for reference:
VIRTUAL TABLE system_views.clients (
    address inet,
    port int,
    client_options frozen<map<text, text>>,
    connection_stage text,
    driver_name text,
    driver_version text,
    hostname text,
    protocol_version int,
    request_count bigint,
    ssl_cipher_suite text,
    ssl_enabled boolean,
    ssl_protocol text,
    username text,
    PRIMARY KEY (address, port)
) WITH CLUSTERING ORDER BY (port ASC)
    AND comment = 'currently connected clients';
*/

Caches Virtual Table

The caches virtual table lists information about the caches. The four caches presently created are chunks, counters, keys and rows. A query on the caches virtual table returns the following details:

cqlsh:system_views> SELECT * FROM system_views.caches;
name     | capacity_bytes | entry_count | hit_count | hit_ratio | recent_hit_rate_per_second | recent_request_rate_per_second | request_count | size_bytes
---------+----------------+-------------+-----------+-----------+----------------------------+--------------------------------+---------------+------------
  chunks |      229638144 |          29 |       166 |      0.83 |                          5 |                              6 |           200 |     475136
counters |       26214400 |           0 |         0 |       NaN |                          0 |                              0 |             0 |          0
    keys |       52428800 |          14 |       124 |  0.873239 |                          4 |                              4 |           142 |       1248
    rows |              0 |           0 |         0 |       NaN |                          0 |                              0 |             0 |          0

(4 rows)

CQL metrics Virtual Table

The cql_metrics virtual table lists metrics specific to CQL prepared statement caching. A query on cql_metrics virtual table lists below metrics.

cqlsh> select * from system_views.cql_metrics ;

 name                         | value
------------------------------+-------
    prepared_statements_count |     0
  prepared_statements_evicted |     0
 prepared_statements_executed |     0
    prepared_statements_ratio |     0
  regular_statements_executed |    17

Settings Virtual Table

The settings table is rather useful and lists all the current configuration settings from the cassandra.yaml. The encryption options are overridden to hide the sensitive truststore information or passwords. The configuration settings however cannot be set using DML on the virtual table presently: :

cqlsh:system_views> SELECT * FROM system_views.settings;

name                                 | value
-------------------------------------+--------------------
  allocate_tokens_for_keyspace       | null
  audit_logging_options_enabled      | false
  auto_snapshot                      | true
  automatic_sstable_upgrade          | false
  cluster_name                       | Test Cluster
  transient_replication_enabled      | false
  hinted_handoff_enabled             | true
  hints_directory                    | /home/ec2-user/cassandra/data/hints
  incremental_backups                | false
  initial_token                      | null
                           ...
                           ...
                           ...
  rpc_address                        | localhost
  ssl_storage_port                   | 7001
  start_native_transport             | true
  storage_port                       | 7000
  stream_entire_sstables             | true
  (224 rows)

The settings table can be really useful if yaml file has been changed since startup and dont know running configuration, or to find if they have been modified via jmx/nodetool or virtual tables.

Thread Pools Virtual Table

The thread_pools table lists information about all thread pools. Thread pool information includes active tasks, active tasks limit, blocked tasks, blocked tasks all time, completed tasks, and pending tasks. A query on the thread_pools returns following details:

cqlsh:system_views> select * from system_views.thread_pools;

name                         | active_tasks | active_tasks_limit | blocked_tasks | blocked_tasks_all_time | completed_tasks | pending_tasks
------------------------------+--------------+--------------------+---------------+------------------------+-----------------+---------------
            AntiEntropyStage |            0 |                  1 |             0 |                      0 |               0 |             0
        CacheCleanupExecutor |            0 |                  1 |             0 |                      0 |               0 |             0
          CompactionExecutor |            0 |                  2 |             0 |                      0 |             881 |             0
        CounterMutationStage |            0 |                 32 |             0 |                      0 |               0 |             0
                 GossipStage |            0 |                  1 |             0 |                      0 |               0 |             0
             HintsDispatcher |            0 |                  2 |             0 |                      0 |               0 |             0
       InternalResponseStage |            0 |                  2 |             0 |                      0 |               0 |             0
         MemtableFlushWriter |            0 |                  2 |             0 |                      0 |               1 |             0
           MemtablePostFlush |            0 |                  1 |             0 |                      0 |               2 |             0
       MemtableReclaimMemory |            0 |                  1 |             0 |                      0 |               1 |             0
              MigrationStage |            0 |                  1 |             0 |                      0 |               0 |             0
                   MiscStage |            0 |                  1 |             0 |                      0 |               0 |             0
               MutationStage |            0 |                 32 |             0 |                      0 |               0 |             0
   Native-Transport-Requests |            1 |                128 |             0 |                      0 |             130 |             0
      PendingRangeCalculator |            0 |                  1 |             0 |                      0 |               1 |             0
PerDiskMemtableFlushWriter_0 |            0 |                  2 |             0 |                      0 |               1 |             0
                   ReadStage |            0 |                 32 |             0 |                      0 |              13 |             0
                 Repair-Task |            0 |         2147483647 |             0 |                      0 |               0 |             0
        RequestResponseStage |            0 |                  2 |             0 |                      0 |               0 |             0
                     Sampler |            0 |                  1 |             0 |                      0 |               0 |             0
    SecondaryIndexManagement |            0 |                  1 |             0 |                      0 |               0 |             0
          ValidationExecutor |            0 |         2147483647 |             0 |                      0 |               0 |             0
           ViewBuildExecutor |            0 |                  1 |             0 |                      0 |               0 |             0
           ViewMutationStage |            0 |                 32 |             0 |                      0 |               0 |             0

(24 rows)

Internode Inbound Messaging Virtual Table

The internode_inbound virtual table is for the internode inbound messaging. Initially no internode inbound messaging may get listed. In addition to the address, port, datacenter and rack information includes corrupt frames recovered, corrupt frames unrecovered, error bytes, error count, expired bytes, expired count, processed bytes, processed count, received bytes, received count, scheduled bytes, scheduled count, throttled count, throttled nanos, using bytes, using reserve bytes. A query on the internode_inbound returns following details:

cqlsh:system_views> SELECT * FROM system_views.internode_inbound;
address | port | dc | rack | corrupt_frames_recovered | corrupt_frames_unrecovered |
error_bytes | error_count | expired_bytes | expired_count | processed_bytes |
processed_count | received_bytes | received_count | scheduled_bytes | scheduled_count | throttled_count | throttled_nanos | using_bytes | using_reserve_bytes
---------+------+----+------+--------------------------+----------------------------+-
----------
(0 rows)

SSTables Tasks Virtual Table

The sstable_tasks could be used to get information about running tasks. It lists following columns:

cqlsh:system_views> SELECT * FROM sstable_tasks;
keyspace_name | table_name | task_id                              | kind       | progress | total    | unit
---------------+------------+--------------------------------------+------------+----------+----------+-------
       basic |      wide2 | c3909740-cdf7-11e9-a8ed-0f03de2d9ae1 | compaction | 60418761 | 70882110 | bytes
       basic |      wide2 | c7556770-cdf7-11e9-a8ed-0f03de2d9ae1 | compaction |  2995623 | 40314679 | bytes

As another example, to find how much time is remaining for SSTable tasks, use the following query:

SELECT total - progress AS remaining
FROM system_views.sstable_tasks;

Gossip Information Virtual Table

The gossip_info virtual table lists the Gossip information for the cluster. An example query is as follows:

cqlsh> select address, port, generation, heartbeat, load, dc, rack from system_views.gossip_info;

 address   | port | generation | heartbeat | load    | dc          | rack
-----------+------+------------+-----------+---------+-------------+-------
 127.0.0.1 | 7000 | 1645575140 |       312 | 70542.0 | datacenter1 | rack1
 127.0.0.2 | 7000 | 1645575135 |       318 | 70499.0 | datacenter1 | rack1
 127.0.0.3 | 7000 | 1645575140 |       312 | 70504.0 | datacenter1 | rack1
 127.0.0.4 | 7000 | 1645575141 |       311 | 70502.0 | datacenter1 | rack1
 127.0.0.5 | 7000 | 1645575136 |       315 | 70500.0 | datacenter1 | rack1

(5 rows)

Other Virtual Tables

Some examples of using other virtual tables are as follows.

Find tables with most disk usage:

cqlsh> SELECT * FROM disk_usage WHERE mebibytes > 1 ALLOW FILTERING;

keyspace_name | table_name | mebibytes
---------------+------------+-----------
   keyspace1 |  standard1 |       288
  tlp_stress |   keyvalue |      3211

Find queries on table/s with greatest read latency:

cqlsh> SELECT * FROM  local_read_latency WHERE per_second > 1 ALLOW FILTERING;

keyspace_name | table_name | p50th_ms | p99th_ms | count    | max_ms  | per_second
---------------+------------+----------+----------+----------+---------+------------
  tlp_stress |   keyvalue |    0.043 |    0.152 | 49785158 | 186.563 |  11418.356

Example

To list the keyspaces, enter cqlsh and run the CQL command DESCRIBE KEYSPACES:

cqlsh> DESC KEYSPACES;
system_schema  system          system_distributed  system_virtual_schema
system_auth    system_traces   system_views

To view the virtual table schema, run the CQL commands USE system_virtual_schema and SELECT * FROM tables:

cqlsh> USE system_virtual_schema;
cqlsh> SELECT * FROM tables;

results in:

 keyspace_name         | table_name                | comment
-----------------------+---------------------------+--------------------------------------
          system_views |                    caches |                        system caches
          system_views |                   clients |          currently connected clients
          system_views |  coordinator_read_latency |
          system_views |  coordinator_scan_latency |
          system_views | coordinator_write_latency |
          system_views |                disk_usage |
          system_views |         internode_inbound |
          system_views |        internode_outbound |
          system_views |        local_read_latency |
          system_views |        local_scan_latency |
          system_views |       local_write_latency |
          system_views |        max_partition_size |
          system_views |             rows_per_read |
          system_views |                  settings |                     current settings
          system_views |             sstable_tasks |                current sstable tasks
          system_views |         system_properties | Cassandra relevant system properties
          system_views |              thread_pools |
          system_views |       tombstones_per_read |
 system_virtual_schema |                   columns |           virtual column definitions
 system_virtual_schema |                 keyspaces |         virtual keyspace definitions
 system_virtual_schema |                    tables |            virtual table definitions

(21 rows)

To view the virtual tables, run the CQL commands USE system_view and DESCRIBE tables:

cqlsh> USE system_view;;
cqlsh> DESCRIBE tables;

results in:

sstable_tasks       clients                   coordinator_write_latency
disk_usage          local_write_latency       tombstones_per_read
thread_pools        internode_outbound        settings
local_scan_latency  coordinator_scan_latency  system_properties
internode_inbound   coordinator_read_latency  max_partition_size
local_read_latency  rows_per_read             caches

To look at any table data, run the CQL command SELECT:

cqlsh> USE system_view;;
cqlsh> SELECT * FROM clients LIMIT 2;

results in:

 address   | port  | connection_stage | driver_name            | driver_version | hostname  | protocol_version | request_count | ssl_cipher_suite | ssl_enabled | ssl_protocol | username
-----------+-------+------------------+------------------------+----------------+-----------+------------------+---------------+------------------+-------------+--------------+-----------
 127.0.0.1 | 37308 |            ready | DataStax Python Driver |   3.21.0.post0 | localhost |                4 |            17 |             null |       False |         null | anonymous
 127.0.0.1 | 37310 |            ready | DataStax Python Driver |   3.21.0.post0 | localhost |                4 |             8 |             null |       False |         null | anonymous

(2 rows)

Cassandra Documentation

Version:

Virtual Tables

Virtual Keyspaces

Virtual Table Limitations

Virtual Tables

Clients Virtual Table

Caches Virtual Table

CQL metrics Virtual Table

Settings Virtual Table

Thread Pools Virtual Table

Internode Inbound Messaging Virtual Table

SSTables Tasks Virtual Table

Gossip Information Virtual Table

Other Virtual Tables

Example