CQL for Cassandra 2.x

Querying a system table

The system keyspace includes a number of tables that contain details about your Cassandra database objects and cluster configuration.

Cassandra populates these tables and others in the system keyspace.
Columns in System Tables
Table name Column name Comment
schema_keyspaces keyspace_name, durable_writes, strategy_class, strategy_options None
local "key", bootstrapped, cluster_name, cql_version, data_center, gossip_generation, native_protocol_version,partitioner, rack, release_version, ring_id, schema_version, thrift_version, tokens set, truncated at map Information a node has about itself and a superset of gossip.
peers peer, data_center, rack, release_version, ring_id, rpc_address, schema_version, tokens set Each node records what other nodes tell it about themselves over the gossip.
schema_columns keyspace_name, columnfamily_name, column_name, component_index, index_name, index_options, index_type, validator Used internally with compound primary keys.
schema_columnfamilies See comment. Inspect schema_columnfamilies to get detailed information about specific tables.

Keyspace, table, and column information

An alternative to the Thrift API describe_keyspaces function is querying system.schema_keyspaces directly. You can also retrieve information about tables by querying system.schema_columnfamilies and about column metadata by querying system.schema_columns.

Procedure

Query the defined keyspaces using the SELECT statement.
SELECT * from system.schema_keyspaces;
The cqlsh output includes information about defined keyspaces.
 keyspace | durable_writes | name    | strategy_class | strategy_options
----------+----------------+---------+----------------+----------------------------
  history |           True | history | SimpleStrategy | {"replication_factor":"1"}
  ks_info |           True | ks_info | SimpleStrategy | {"replication_factor":"1"}
  
(2 rows)

Cluster information

You can query system tables to get cluster topology information. You can get the IP address of peer nodes, data center and rack names, token values, and other information. "The Data Dictionary" article describes querying system tables in detail.

Procedure

After setting up a 3-node cluster using ccm on the Mac OSX, query the peers and local tables.
USE system;
SELECT * FROM peers;
Output from querying the peers table looks something like this:
   peer    | data_center | host_id     | preferred_ip | rack  | release_version | rpc_address | schema_version | tokens
-----------+-------------+-------------+--------------+-------+-----------------+-------------+----------------+-----------
 127.0.0.3 | datacenter1 | edda8d72... |         null | rack1 |           2.1.0 | 127.0.0.3   | 59adb24e-f3... |  {3074...
 127.0.0.2 | datacenter1 | ef863afa... |         null | rack1 |           2.1.0 | 127.0.0.2   | 3d19cd8f-c9... | {-3074...}
                        
(2 rows)
Show/hide