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 TableDescription

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.

disk_usage

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

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:

  1. cqlsh> EXPAND ON ;
  2. Now Expanded output is enabled
  3. cqlsh> SELECT * FROM system_views.clients;
  5. @ Row 1
  6. ------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  7.  address          | 127.0.0.1
  8.  port             | 50687
  9.  client_options   | {'CQL_VERSION': '3.4.6', 'DRIVER_NAME': 'DataStax Python Driver', 'DRIVER_VERSION': '3.25.0'}
  10.  connection_stage | ready
  11.  driver_name      | DataStax Python Driver
  12.  driver_version   | 3.25.0
  13.  hostname         | localhost
  14.  protocol_version | 5
  15.  request_count    | 16
  16.  ssl_cipher_suite | null
  17.  ssl_enabled      | False
  18.  ssl_protocol     | null
  19.  username         | anonymous
  21. @ Row 2
  22. ------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  23.  address          | 127.0.0.1
  24.  port             | 50688
  25.  client_options   | {'CQL_VERSION': '3.4.6', 'DRIVER_NAME': 'DataStax Python Driver', 'DRIVER_VERSION': '3.25.0'}
  26.  connection_stage | ready
  27.  driver_name      | DataStax Python Driver
  28.  driver_version   | 3.25.0
  29.  hostname         | localhost
  30.  protocol_version | 5
  31.  request_count    | 4
  32.  ssl_cipher_suite | null
  33.  ssl_enabled      | False
  34.  ssl_protocol     | null
  35.  username         | anonymous
  37. @ Row 3
  38. ------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  39.  address          | 127.0.0.1
  40.  port             | 50753
  41.  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'}
  42.  connection_stage | ready
  43.  driver_name      | DataStax Java driver for Apache Cassandra(R)
  44.  driver_version   | 4.13.0
  45.  hostname         | localhost
  46.  protocol_version | 5
  47.  request_count    | 18
  48.  ssl_cipher_suite | null
  49.  ssl_enabled      | False
  50.  ssl_protocol     | null
  51.  username         | anonymous
  53. @ Row 4
  54. ------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  55.  address          | 127.0.0.1
  56.  port             | 50755
  57.  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'}
  58.  connection_stage | ready
  59.  driver_name      | DataStax Java driver for Apache Cassandra(R)
  60.  driver_version   | 4.13.0
  61.  hostname         | localhost
  62.  protocol_version | 5
  63.  request_count    | 7
  64.  ssl_cipher_suite | null
  65.  ssl_enabled      | False
  66.  ssl_protocol     | null
  67.  username         | anonymous
  69. (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.

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:

  1. cqlsh> DESCRIBE TABLE system_views.clients;
  3. /*
  4. Warning: Table system_views.clients is a virtual table and cannot be recreated with CQL.
  5. Structure, for reference:
  6. VIRTUAL TABLE system_views.clients (
  7.   address inet,
  8.   port int,
  9.   client_options frozen<map<text, text>>,
  10.   connection_stage text,
  11.   driver_name text,
  12.   driver_version text,
  13.   hostname text,
  14.   protocol_version int,
  15.   request_count bigint,
  16.   ssl_cipher_suite text,
  17.   ssl_enabled boolean,
  18.   ssl_protocol text,
  19.   username text,
  20.     PRIMARY KEY (address, port)
  21. ) WITH CLUSTERING ORDER BY (port ASC)
  22.     AND comment = 'currently connected clients';
  23. */

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:

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

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

  1. cqlsh:system_views> SELECT * FROM system_views.settings;
  3. name                                 | value
  4. -------------------------------------+--------------------
  5.   allocate_tokens_for_keyspace       | null
  6.   audit_logging_options_enabled      | false
  7.   auto_snapshot                      | true
  8.   automatic_sstable_upgrade          | false
  9.   cluster_name                       | Test Cluster
  10.   enable_transient_replication       | false
  11.   hinted_handoff_enabled             | true
  12.   hints_directory                    | /home/ec2-user/cassandra/data/hints
  13.   incremental_backups                | false
  14.   initial_token                      | null
  15.                            ...
  16.                            ...
  17.                            ...
  18.   rpc_address                        | localhost
  19.   ssl_storage_port                   | 7001
  20.   start_native_transport             | true
  21.   storage_port                       | 7000
  22.   stream_entire_sstables             | true
  23.   (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:

  1. cqlsh:system_views> select * from system_views.thread_pools;
  3. name                         | active_tasks | active_tasks_limit | blocked_tasks | blocked_tasks_all_time | completed_tasks | pending_tasks
  4. ------------------------------+--------------+--------------------+---------------+------------------------+-----------------+---------------
  5.             AntiEntropyStage |            0 |                  1 |             0 |                      0 |               0 |             0
  6.         CacheCleanupExecutor |            0 |                  1 |             0 |                      0 |               0 |             0
  7.           CompactionExecutor |            0 |                  2 |             0 |                      0 |             881 |             0
  8.         CounterMutationStage |            0 |                 32 |             0 |                      0 |               0 |             0
  9.                  GossipStage |            0 |                  1 |             0 |                      0 |               0 |             0
  10.              HintsDispatcher |            0 |                  2 |             0 |                      0 |               0 |             0
  11.        InternalResponseStage |            0 |                  2 |             0 |                      0 |               0 |             0
  12.          MemtableFlushWriter |            0 |                  2 |             0 |                      0 |               1 |             0
  13.            MemtablePostFlush |            0 |                  1 |             0 |                      0 |               2 |             0
  14.        MemtableReclaimMemory |            0 |                  1 |             0 |                      0 |               1 |             0
  15.               MigrationStage |            0 |                  1 |             0 |                      0 |               0 |             0
  16.                    MiscStage |            0 |                  1 |             0 |                      0 |               0 |             0
  17.                MutationStage |            0 |                 32 |             0 |                      0 |               0 |             0
  18.    Native-Transport-Requests |            1 |                128 |             0 |                      0 |             130 |             0
  19.       PendingRangeCalculator |            0 |                  1 |             0 |                      0 |               1 |             0
  20. PerDiskMemtableFlushWriter_0 |            0 |                  2 |             0 |                      0 |               1 |             0
  21.                    ReadStage |            0 |                 32 |             0 |                      0 |              13 |             0
  22.                  Repair-Task |            0 |         2147483647 |             0 |                      0 |               0 |             0
  23.         RequestResponseStage |            0 |                  2 |             0 |                      0 |               0 |             0
  24.                      Sampler |            0 |                  1 |             0 |                      0 |               0 |             0
  25.     SecondaryIndexManagement |            0 |                  1 |             0 |                      0 |               0 |             0
  26.           ValidationExecutor |            0 |         2147483647 |             0 |                      0 |               0 |             0
  27.            ViewBuildExecutor |            0 |                  1 |             0 |                      0 |               0 |             0
  28.            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:

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

SSTables Tasks Virtual Table

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

  1. cqlsh:system_views> SELECT * FROM sstable_tasks;
  2. keyspace_name | table_name | task_id                              | kind       | progress | total    | unit
  3. ---------------+------------+--------------------------------------+------------+----------+----------+-------
  4.        basic |      wide2 | c3909740-cdf7-11e9-a8ed-0f03de2d9ae1 | compaction | 60418761 | 70882110 | bytes
  5.        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:

  1. SELECT total - progress AS remaining
  2. FROM system_views.sstable_tasks;

Other Virtual Tables

Some examples of using other virtual tables are as follows.

Find tables with most disk usage:

  1. cqlsh> SELECT * FROM disk_usage WHERE mebibytes > 1 ALLOW FILTERING;
  3. keyspace_name | table_name | mebibytes
  4. ---------------+------------+-----------
  5.    keyspace1 |  standard1 |       288
  6.   tlp_stress |   keyvalue |      3211

Find queries on table/s with greatest read latency:

  1. cqlsh> SELECT * FROM  local_read_latency WHERE per_second > 1 ALLOW FILTERING;
  3. keyspace_name | table_name | p50th_ms | p99th_ms | count    | max_ms  | per_second
  4. ---------------+------------+----------+----------+----------+---------+------------
  5.   tlp_stress |   keyvalue |    0.043 |    0.152 | 49785158 | 186.563 |  11418.356

Example

  1. To list the keyspaces, enter cqlsh and run the CQL command DESCRIBE KEYSPACES:
  1. cqlsh> DESC KEYSPACES;
  2. system_schema  system          system_distributed  system_virtual_schema
  3. system_auth    system_traces   system_views
  1. To view the virtual table schema, run the CQL commands USE system_virtual_schema and SELECT * FROM tables:
  1. cqlsh> USE system_virtual_schema;
  2. cqlsh> SELECT * FROM tables;

results in:

  1.  keyspace_name         | table_name                | comment
  2. -----------------------+---------------------------+--------------------------------------
  3.           system_views |                    caches |                        system caches
  4.           system_views |                   clients |          currently connected clients
  5.           system_views |  coordinator_read_latency |
  6.           system_views |  coordinator_scan_latency |
  7.           system_views | coordinator_write_latency |
  8.           system_views |                disk_usage |
  9.           system_views |         internode_inbound |
  10.           system_views |        internode_outbound |
  11.           system_views |        local_read_latency |
  12.           system_views |        local_scan_latency |
  13.           system_views |       local_write_latency |
  14.           system_views |        max_partition_size |
  15.           system_views |             rows_per_read |
  16.           system_views |                  settings |                     current settings
  17.           system_views |             sstable_tasks |                current sstable tasks
  18.           system_views |         system_properties | Cassandra relevant system properties
  19.           system_views |              thread_pools |
  20.           system_views |       tombstones_per_read |
  21.  system_virtual_schema |                   columns |           virtual column definitions
  22.  system_virtual_schema |                 keyspaces |         virtual keyspace definitions
  23.  system_virtual_schema |                    tables |            virtual table definitions
  25. (21 rows)
  1. To view the virtual tables, run the CQL commands USE system_view and DESCRIBE tables:
  1. cqlsh> USE system_view;;
  2. cqlsh> DESCRIBE tables;

results in:

  1. sstable_tasks       clients                   coordinator_write_latency
  2. disk_usage          local_write_latency       tombstones_per_read
  3. thread_pools        internode_outbound        settings
  4. local_scan_latency  coordinator_scan_latency  system_properties
  5. internode_inbound   coordinator_read_latency  max_partition_size
  6. local_read_latency  rows_per_read             caches
  1. To look at any table data, run the CQL command SELECT:
  1. cqlsh> USE system_view;;
  2. cqlsh> SELECT * FROM clients LIMIT 2;
  1. results in:
  1.  address   | port  | connection_stage | driver_name            | driver_version | hostname  | protocol_version | request_count | ssl_cipher_suite | ssl_enabled | ssl_protocol | username
  2. -----------+-------+------------------+------------------------+----------------+-----------+------------------+---------------+------------------+-------------+--------------+-----------
  3.  127.0.0.1 | 37308 |            ready | DataStax Python Driver |   3.21.0.post0 | localhost |                4 |            17 |             null |       False |         null | anonymous
  4.  127.0.0.1 | 37310 |            ready | DataStax Python Driver |   3.21.0.post0 | localhost |                4 |             8 |             null |       False |         null | anonymous
  6. (2 rows)