Virtual tables for SAI indexes and SSTables

Storage Attached Indexing (SAI) provides CQL-based virtual tables that enable you to discover the current state of system metadata for SAI indices and associated SSTables. These virtual tables reside in the system_views keyspace.

For related information, see the SAI information.

system_views.indexes

The system_views.indexes virtual table contains stateful information about SAI indexes. This view provides information at the column index level, including the index name, number of indexed SSTables, disk usage, and index state. From the index state, the data reveals if the index is currently building, and whether the index can be queried.

Use CQL to view the table’s description. Example:

DESCRIBE TABLE system_views.indexes;
/*
Warning: Table system_views.indexes is a virtual table and cannot be recreated with CQL.
Structure, for reference:
VIRTUAL TABLE system_views.indexes (
    keyspace_name text,
    index_name text,
    analyzer text,
    cell_count bigint,
    column_name text,
    indexed_sstable_count int,
    is_building boolean,
    is_queryable boolean,
    is_string boolean,
    per_column_disk_size bigint,
    per_table_disk_size bigint,
    table_name text,
    PRIMARY KEY (keyspace_name, index_name)
) WITH CLUSTERING ORDER BY (index_name ASC)
    AND comment = 'Storage-attached column index metadata';
*/

To view the current data, submit a query such as:

SELECT * FROM system_views.indexes;
 keyspace_name | index_name           | analyzer                                                    | cell_count | column_name  | indexed_sstable_count | is_building | is_queryable | is_string | per_column_disk_size | per_table_disk_size | table_name
---------------+----------------------+-------------------------------------------------------------+------------+--------------+-----------------------+-------------+--------------+-----------+----------------------+---------------------+------------------
       cycling |          age_sai_idx |                                              NoOpAnalyzer{} |          0 |          age |                     0 |       False |         True |     False |                    0 |                   0 | cyclist_semi_pro
       cycling |      country_sai_idx | NonTokenizingAnalyzer{caseSensitive=false, normalized=true} |          0 |      country |                     0 |       False |         True |      True |                    0 |                   0 | cyclist_semi_pro
       cycling |     lastname_sai_idx | NonTokenizingAnalyzer{caseSensitive=false, normalized=true} |          0 |     lastname |                     0 |       False |         True |      True |                    0 |                   0 | cyclist_semi_pro
       cycling | registration_sai_idx |                                              NoOpAnalyzer{} |          0 | registration |                     0 |       False |         True |     False |                    0 |                   0 | cyclist_semi_pro

(4 rows)
system_views.indexes metadata
Column name CQL type Meaning

keyspace_name

text

The name of the keyspace to which the index belongs.

index_name

text

The name of the index.

analyzer

text

The toString representation of the analyzer used by the index.

cell_count

bigint

The number of indexed table cells, or the number of index value-key entries. This is the sum of the number of index entries in each SSTable.

column_name

text

The name of the indexed column.

indexed_sstable_count

int

The number of indexed SSTables. Note that SSTables without relevant data won’t be indexed or counted here.

is_building

boolean

Whether there is a on going build for the index.

is_queryable

boolean

Whether it is possible to query the index. It won’t be possible if the initial task build hasn’t finished yet.

is_string

boolean

Whether the index is for a text field (ascii, text, or varchar).

per_column_disk_size

bigint

The on-disk size of the index components that are exclusive to the column, in bytes.

per_table_disk_size

bigint

The on-disk size of the index components that are shared with other SAI indexes for the same table, in bytes.

table_name

text

The name of the table to which the indexed column belongs.

system_views.sstable_indexes

The system_views.sstable_indexes virtual table has a row per SAI index and SSTable. This view describes individual SSTable indexes, and includes information around disk size, min/max row ID, the min/max ring token, and the write-time version of the index.

Use CQL to view the table’s description. Example:

DESCRIBE TABLE system_views.sstable_indexes;
/*
Warning: Table system_views.sstable_indexes is a virtual table and cannot be recreated with CQL.
Structure, for reference:
VIRTUAL TABLE system_views.sstable_indexes (
    keyspace_name text,
    index_name text,
    sstable_name text,
    cell_count bigint,
    column_name text,
    end_token text,
    format_version text,
    max_row_id bigint,
    min_row_id bigint,
    per_column_disk_size bigint,
    per_table_disk_size bigint,
    start_token text,
    table_name text,
    PRIMARY KEY (keyspace_name, index_name, sstable_name)
) WITH CLUSTERING ORDER BY (index_name ASC, sstable_name ASC)
    AND comment = 'SSTable index metadata';
*/

To view the current data, submit a query such as:

SELECT * FROM system_views.sstable_indexes;
system_views.sstable_indexes metadata<
Column name CQL type Meaning

keyspace_name

text

The name of the keyspace to which the index belongs.

index_name

text

The name of the index.

sstable_name

text

The name of the SSTable.

cell_count

bigint

The number of indexed table cells, or the number of index value-key entries.

column_name

text

The name of the indexed column.

start_token

text

The start of the token range covered by the indexed SSTable.

end_token

text

The end of the token range covered by the indexed SSTable.

min_row_id

bigint

The minimum row id in the SSTable index.

max_row_id

bigint

The maximum row id in the SSTable index.

per_column_disk_size

bigint

The on-disk size of the SSTable index components that are exclusive to the column, in bytes.

per_table_disk_size

bigint

The on-disk size of the SSTable index components that are shared with other SAI indexes for the same table, in bytes.

table_name

text

The name of the table to which the indexed column belongs.

system_views.sstable_index_segments

The system_views.sstable_index_segments virtual table has a row per SAI index and SSTable segment. This view describes the segments of the SSTable indexes. It exposes the segment row ID offset and most of the information in the SSTable-level virtual table, specifically at a segment granularity.

Use CQL to view the table’s description. Example:

DESCRIBE TABLE system_views.sstable_index_segments;
/*
Warning: Table system_views.sstable_index_segments is a virtual table and cannot be recreated with CQL.
Structure, for reference:
VIRTUAL TABLE system_views.sstable_index_segments (
    keyspace_name text,
    index_name text,
    sstable_name text,
    segment_row_id_offset bigint,
    cell_count bigint,
    column_name text,
    component_metadata frozen<map<text, map<text, text>>>,
    end_token text,
    max_sstable_row_id bigint,
    max_term text,
    min_sstable_row_id bigint,
    min_term text,
    start_token text,
    table_name text,
    PRIMARY KEY (keyspace_name, index_name, sstable_name, segment_row_id_offset)
) WITH CLUSTERING ORDER BY (index_name ASC, sstable_name ASC, segment_row_id_offset ASC)
    AND comment = 'SSTable index segment metadata';
*/

To view the current data, submit a query such as:

SELECT * FROM system_views.sstable_index_segments;
system_views.sstable_index_segments metadata
Column name CQL type Meaning

keyspace_name

text

The name of the keyspace to which the index belongs.

index_name

text

The name of the index.

sstable_name

text

The name of the SSTable.

segment_row_id_offset

bigint

The row id offset for the SSTable segment.

cell_count

bigint

The number of indexed segments, or the number of index segments value-key entries.

column_name

text

The name of the indexed column.

component_metadata

frozen<map<text, map<text, text>>>

The component metadata in the SSTable segment.

end_token

text

The end of the token range covered by the SSTable segment.

max_sstable_row_id

bigint

The maximum row id in the SSTable segment.

max_term

text

The maximum term in the SSTable segment.

min_sstable_row_id

bigint

The minimum row id in the SSTable segment.

min_term

text

The minimum term in the SSTable segment.

start_token

text

The start of the token range covered by the SSTable segment.

table_name

text

The name of the table to which the SSTable segment belongs.

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000, info@datastax.com