Cassandra: get column names of a columnfamily?

Question

I'm wondering if there is a query in CQL3 that allows you to get column names of a specific columnfamily that has a static schema?
Thanks in advance :)

Pye

Answer 1

You could use the system keyspace to do this:

SELECT column_name FROM system.schema_columnfamilies 
  WHERE keyspace_name = 'testks' AND columnfamily_name = 'testcf';

Output in cqlsh (using cql3):

 column_name
-------------
    password

You can work out the key for the column by using:

SELECT key_aliases FROM system.schema_columnfamilies WHERE keyspace_name='testks' 
AND columnfamily_name='testcf';

Output:

 key_aliases
--------------
 ["username"]

Rabb

@Lyuben Can I do a range on column names similar to column's Slice ? Eg Getting column names between two dates if date is the column name – Eury

Answer 2

If you want to get column names of a specific table with CQL3 then i guess try this

select * from system.schema_columns WHERE keyspace_name='#KS' AND columnfamily_name='#CF' allow filtering;

Note: keyspace_name in the where clause is optional. Its mainly used for better filtration purpose (say, table with the same name in multiple keyspace)

Yellowtail

Thank you so much! Actually I didn't know witch answer to validate both were correct and helpful, thanks again ;) – Pye

Actually this works for Cassandra 2, CQL3 but not the above, to get all column names... – Gem

Change * to column_name and you'll get just the name of all the columns in the columnfamily – Latakia

Answer 3

From my latest test, we should use schema_columns, rather than schema_columnfamilies to get all the column names. schema_columnfamilies could be used for getting table names.

1. Get column names:

   SELECT column_name FROM system.schema_columns WHERE keyspace_name = 'KeySpaceName' AND columnfamily_name = 'TableName';
   

2. Get column family names, i.e., table names:

   select columnfamily_name from system.schema_columnfamilies where keyspace_name='KeySpaceName';
   

Vlada

Answer 4

As per the latest Documentation of Cassandra 3.x, all above answers won't work and now the query to show columns would be like this.

SELECT * FROM system_schema.columns WHERE keyspace_name = 'xxxx' AND table_name = 'xxx';

Aseity