Cassandra create table won't keep column order

7
votes

I am creating a column family in Cassandra and I expect the column order to match the one I am specifying in the create clause.

This

CREATE TABLE cf.mycf (
    timestamp timestamp,
    id text,
    score int,
    type text,
    publisher_id text,
    embed_url text,
    PRIMARY KEY (timestamp, id, score)
) WITH bloom_filter_fp_chance = 0.01
AND comment = ''
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 = '99.0PERCENTILE'
AND caching = {
    'keys' : 'ALL',
    'rows_per_partition' : 'NONE'
}
AND compression = {
    'chunk_length_kb' : 64,
    'crc_check_chance' : 1.0,
    'sstable_compression' : 'LZ4Compressor'
}
AND compaction = {
    'base_time_seconds' : 60,
    'class' : 'DateTieredCompactionStrategy',
    'enabled' : true,
    'max_sstable_age_days' : 365,
    'max_threshold' : 32,
    'min_threshold' : 4,
    'timestamp_resolution' : 'MICROSECONDS',
    'tombstone_compaction_interval' : 86400,
    'tombstone_threshold' : 0.2,
    'unchecked_tombstone_compaction' : false
};

Should create a table like : timestamp ,id ,score , type, id ,embed_url

Instead I am getting this:

timestamp timestamp,
    id text,
    score int,
    embed_url text,
    publisher_id text,
    type text,

I've created quite a few tables in the same way and this never happened so any help would be appreciated.

I put the id and score as keys to show that these keep their respective position. while the actual scheme I am looking for is only the timestamp to be the primary key.

3
cassandra
Are you using using SELECT *? If you add the fields to the SELECT query, you can force the ordering. Also, using timestamp and type as column names is likely to cause issues at some point in the future. – Jeff Jirsa
This happens on insert. – raam86
I'm getting the same result, this is the only post on that matter - might not bother anyone although it's annoying on select * - jeff-jirsa has a point - select with specified fields name do the job.. But this is still annoying – lastboy
Just as bad on writes – raam86
It also shows on DESCRIBE TABLE apester.best_current_interaction. – astrojuanlu

3 Answers

7
votes

Looks like there is no such thing as fields order in cassandra.

The others columns are displayed in alphabetical order by Cassandra.

http://docs.datastax.com/en/cql/3.1/cql/ddl/ddl_compound_keys_c.html

5
votes

You should make a clear distinction on how you want the data to be presented and how it is effectively presented to you. Moreover, you should not rely on the ordinal position of the fields but only on their names.

In order to be efficient, and against your will (you specified an order to the columns when you modeled your schema), Cassandra needs to store the columns in a particular order, and for simplicity this reflects on how it (the CQL interface or the driver) will give back your data.

I suggest you to have a deep insight on how Cassandra stores data (column names included!) in Understanding How CQL3 Maps to Cassandra’s Internal Data Structure.

By the way, if you absolutely need to keep your order at application level (and are too lazy to specify all the fields in the SELECT instead of using SELECT *), you need to create an abstraction interface on your own, something like creating an ordered "field names" array (your order):

String myorder[] = { "timestamp", "id", "score", "type", "publisher_id", "embed_url"};

and then use this as a map in loops using ordinal values.

2
votes

Keep in mind that the rendering of the CQL string in DESCRIBE in cqlsh is just a function call in the python driver iterating over the metadata. It has nothing to do with how C* stores or sends its results.

If it matters you can set the order. When you Insert you can define the order explicitly

INSERT INTO keyspace_name.table_name
  ( identifier, column_name, whatever, order)
  VALUES ( value, value ... )

When you do a select you can define the order explicitly.

SELECT identifier, whatever, order, column_name FROM keyspace_name.table_name