How to fetch Primary Key/Clustering column names for a particular table using CQL statements?

user_2534177 0
Viewed 48

I am trying to fetch the Primary Key/Clustering Key names for a particular table/entity and implement the same query in my JPA interface (which extends CassandraRepository). I am not sure whether something like:

@Query("DESCRIBE TABLE <table_name>)
public Object describeTbl();

would work here as describe isn't a valid CQL statement and in case it would, what would be the type of the Object?

Suggestions?

cql spring-data-cassandra cassandra
2 Answers

DESCRIBE TABLE is supported only in Cassandra 4 that includes fix for CASSANDRA-14825. But it may not help you much because it just returns the text string representing the CREATE TABLE statement, and you'll need to parse text to extract primary key definition - it's doable but could be tricky, depending on the structure of the primary key.

Or you can obtain underlying Session object and via getMetadata function get access to actual metadata object that allows to obtain information about keyspaces & tables, including the information about schema.

Alex_Ott_186270

One thing you could try, would be to query the system_schema.columns table. It is keyed by keyspace_name and table_name, and might be what you're looking for here:

> SELECT column_name,kind FROM system_schema.columns
  WHERE keyspace_name='spaceflight_data'
  AND table_name='astronauts_by_group';

 column_name       | kind
-------------------+---------------
           flights |       regular
             group | partition_key
              name |    clustering
 spaceflight_hours |    clustering

(4 rows)
user_10545580
Related