in cassandra-cli how to get all column names in a table and how to get it using hector in java?

N D Thokare picture N D Thokare · Aug 22, 2013 · Viewed 16k times · Source

I'm trying to get column names but could not get way to get only column names.

In cli I executed command describe table nodes, it returned the result:

CREATE TABLE nodes (
  key text PRIMARY KEY,
  id text,
  scores text,
  topic1 text,
  topic2 text,
  topic3 text,
  topic4 text,
  topics text
) WITH COMPACT STORAGE AND
  bloom_filter_fp_chance=0.010000 AND
  caching='KEYS_ONLY' AND
  comment='' AND
  dclocal_read_repair_chance=0.000000 AND
  gc_grace_seconds=864000 AND
  read_repair_chance=0.100000 AND
  replicate_on_write='true' AND
  populate_io_cache_on_flush='false' AND
  compaction={'class': 'SizeTieredCompactionStrategy'} AND
  compression={'sstable_compression': 'SnappyCompressor'};

CREATE INDEX idx_nodes_id ON nodes (id);

As given in this question, I tries using following command in cli:

SELECT column_name FROM system.schema_columnfamilies WHERE keyspace_name = 'ianew' AND columnfamily_name = 'nodes';

but it gave the error:

Bad Request: Undefined name column_name in selection clause
Perhaps you meant to use CQL 2? Try using the -2 option when starting cqlsh.

Then I tried with:

SELECT * FROM system.schema_columnfamilies WHERE keyspace_name = 'ianew' AND columnfamily_name = 'nodes';

It returned all following things:

    keyspace_name | columnfamily_name | bloom_filter_fp_chance | caching   | column_aliases | comment | compaction_strategy_class                                       | compaction_strategy_options | comparator                               | compression_parameters                                                      | default_read_consistency | default_validator                        | default_write_consistency | gc_grace_seconds | id   | key_alias | key_aliases | key_validator                            | local_read_repair_chance | max_compaction_threshold | min_compaction_threshold | populate_io_cache_on_flush | read_repair_chance | replicate_on_write | subcomparator | type     | value_alias
---------------+-------------------+------------------------+-----------+----------------+---------+-----------------------------------------------------------------+-----------------------------+------------------------------------------+-----------------------------------------------------------------------------+--------------------------+------------------------------------------+---------------------------+------------------+------+-----------+-------------+------------------------------------------+--------------------------+--------------------------+--------------------------+----------------------------+--------------------+--------------------+---------------+----------+-------------
         ianew |             nodes |                   null | KEYS_ONLY |             [] |         | org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy |                          {} | org.apache.cassandra.db.marshal.UTF8Type | {"sstable_compression":"org.apache.cassandra.io.compress.SnappyCompressor"} |                     null | org.apache.cassandra.db.marshal.UTF8Type |                      null |           864000 | null |      null |          [] | org.apache.cassandra.db.marshal.UTF8Type |                        0 |                       32 |                        4 |                      False |                0.1 |               True |          null | Standard |        null

As given in this post, I tried using hector in java:

SliceQuery<String, String, String> query = HFactory.createSliceQuery(keyspace, StringSerializer.get(), StringSerializer.get(),  StringSerializer.get());
query.setColumnFamily(columnFamilyName);
query.setKey("key");
query.setRange(null, null, false, Integer.MAX_VALUE);

ColumnSliceIterator<String, String, String> iterator = new ColumnSliceIterator<String, String, String>(query, null, "\uFFFF", false);

while (iterator.hasNext()) {
    HColumnImpl<String, String> column = (HColumnImpl<String, String>) iterator.next();
    System.out.println("Column name = " + column.getName() + "; Column value = " + column.getValue());
    colNames.add(column.getName());
}

but it returned with no results.

I want output to be something like:

TABLE nodes:
Columns: key text PRIMARY KEY, id text, scores text, topic1 text, topic2 text, topic3 text, topic4 text, topics text

and similar result through Hector.

Versions I'm using:

[cqlsh 2.3.0 | Cassandra 1.2.4 | CQL spec 3.0.0 | Thrift protocol 19.35.0]

Answer

Lyuben Todorov picture Lyuben Todorov · Aug 22, 2013

In cassandra 1.1 that would indeed work, however the schema_columnfamilies column family has been modified since then.

Bad Request: Undefined name column_name in selection clause

In Cassandra 1.2.x information about the columns lives in a separate keyspace called schema_columns with the following schema:

CREATE TABLE schema_columns (
  keyspace_name text,
  columnfamily_name text,
  column_name text,
  component_index int,
  index_name text,
  index_options text,
  index_type text,
  validator text,
  PRIMARY KEY (keyspace_name, columnfamily_name, column_name)
);

Try something along these lines:

 SELECT * FROM system.schema_columns 
 WHERE keyspace_name = 'ianew' AND columnfamily_name = 'nodes';

Documentation on what the contents of the system keyspace.