Check column existence

Using CQL shell (cqlsh), you can either get the description of all columns and their data types, or discover whether a particular column exists in a table by querying the column’s data.

These commands require a keyspace and a table with defined columns.

All columns

The DESCRIBE TABLE command shows all the table information, including the column definitions.

For example, if a cycling.comments table is defined, the following command returns the table definition:

DESCRIBE TABLE cycling.comments;

Result

CREATE TABLE cycling.comments (
    id uuid,
    created_at timestamp,
    comment text,
    commenter text,
    record_id timeuuid,
    PRIMARY KEY (id, created_at)
) WITH CLUSTERING ORDER BY (created_at DESC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

If a column exists, a SELECT command will return any data stored in the column. If your table has many rows, DataStax recommends that you limit the results with WHERE clauses and other filters. Unrestricted SELECT statements can be resource intensive and time consuming for all but the smallest tables.

For example, the following query only returns rows where the year column is 2015 and the month column is 6:

SELECT * FROM cycling.upcoming_calendar WHERE year = 2015 AND month = 6;

The matching rows are returned in the output:

Result

 year | month | description                                                                  | events
------+-------+------------------------------------------------------------------------------+----------------------------------------------------------------------------
 2015 |     6 | {'Criterium du Dauphine': 'Easy race', 'Tour du Suisse': 'Hard uphill race'} | ['Tour de France', 'Criterium du Dauphine', 'Vuelta Ciclista a Venezuela']

(1 rows)

The format of the returned values depends on the column type. For example, map collection columns are returned in JSON format, such as the description column in the preceding example.

Check column existence

All columns

One column

Was this helpful?

Give Feedback