Duplicate rows/columns for the same primary key in Cassandra

Question

I have a table/columnfamily in Cassandra 3.7 with sensordata.

CREATE TABLE test.sensor_data (
    house_id int,
    sensor_id int,
    time_bucket int,
    sensor_time timestamp,
    sensor_reading map<int, float>,
    PRIMARY KEY ((house_id, sensor_id, time_bucket), sensor_time)
) 

Now when I select from this table I find duplicates for the same primary key, something I thought was impossible.

cqlsh:test> select * from sensor_data;

 house_id | sensor_id | time_bucket | sensor_time                     | sensor_reading
----------+-----------+-------------+---------------------------------+----------------
        1 |         2 |           3 | 2016-01-02 03:04:05.000000+0000 |       {1: 101}
        1 |         2 |           3 | 2016-01-02 03:04:05.000000+0000 |       {1: 101}

I think part of the problem is that this data has both been written "live" using java and Datastax java driver, and it has been loaded together with historic data from another source using sstableloader.

Regardless, this shouldn't be possible. I have no way of connecting with the legacy cassandra-cli to this cluster, perhaps that would have told me something that I can't see using cqlsh.

So, the questions are:
* Is there anyway this could happen under known circumstances?
* Can I read more raw data using cqlsh? Specifically write time of these two rows. the writetime()-function can't operate on primary keys or collections, and that is all I have.

Thanks.

Update:

This is what I've tried, from comments, answers and other sources
* selecting using blobAsBigInt gives the same big integer for all identical rows
* connecting using cassandra-cli, after enabling thrift, is possible but reading the table isn't. It's not supported after 3.x
* dumping out using sstabledump is ongoing but expected to take another week or two ;)

Answer 1

I don't expect to see nanoseconds in a timestamp field and additionally i'm of the impression they're fully not supported? Try this:

SELECT house_id, sensor_id, time_bucket, blobAsBigint(sensor_time) FROM test.sensor_data;

I WAS able to replicate it doing by inserting the rows via an integer:

INSERT INTO sensor_data(house_id, sensor_id, time_bucket, sensor_time) VALUES (1,2,4,1451692800000); INSERT INTO sensor_data(house_id, sensor_id, time_bucket, sensor_time) VALUES (1,2,4,1451692800001);

This makes sense because I would suspect one of your drivers is using a bigint to insert the timestamp, and one is likely actually using the datetime.

Tried playing with both timezones and bigints to reproduce this... seems like only bigint is reproducable

 house_id | sensor_id | time_bucket | sensor_time              | sensor_reading
----------+-----------+-------------+--------------------------+----------------
        1 |         2 |           3 | 2016-01-02 00:00:00+0000 |           null
        1 |         2 |           4 | 2016-01-01 23:00:00+0000 |           null
        1 |         2 |           4 | 2016-01-02 00:00:00+0000 |           null
        1 |         2 |           4 | 2016-01-02 00:00:00+0000 |           null
        1 |         2 |           4 | 2016-01-02 01:01:00+0000 |           null

edit: Tried some shenanigans using bigint in place of datetime insert, managed to reproduce...

Answer 2

"sensor_time" is part of the primary key. It is not in "Partition Key", but is "Clustering Column". this is why you get two "rows".

However, in the disk table, both "visual rows" are stored on single Cassandra row. In reality, they are just different columns and CQL just pretend they are two "visual rows".

Clarification - I did not worked with Cassandra for a while so I might not use correct terms. When i say "visual rows", I mean what CQL result shows.

Update

You can create following experiment (please ignore and fix any syntax errors I will do).

This suppose to do table with composite primary key:

• "state" is "Partition Key" and

• "city" is "Clustering Column".

  create table cities( state int, city int, name text, primary key((state), city) );

  insert into cities(state, city, name)values(1, 1, 'New York'); insert into cities(state, city, name)values(1, 2, 'Corona');

  select * from cities where state = 1;

this will return something like:

1, 1, New York
1, 2, Corona

But on the disk this will be stored on single row like this:

+-------+-----------------+-----------------+
| state | city = 1        | city = 2        |
|       +-----------------+-----------------+
|       | city | name     | city | name     |
+-------+------+----------+------+----------+
| 1     | 1    | New York | 2    | Corona   |
+-------+------+----------+------+----------+

When you have such composite primary key you can select or delete on it, e.g.

select * from cities where state = 1;
delete from cities where state = 1;

In the question, primary key is defined as:

PRIMARY KEY ((house_id, sensor_id, time_bucket), sensor_time)

this means

• "house_id", "sensor_id", "time_bucket" is "Partition Key" and
• "sensor_time" is the "Clustering Column".

So when you select, the real row is spitted and show as if there are several rows.

Update

http://www.planetcassandra.org/blog/primary-keys-in-cql/

The PRIMARY KEY definition is made up of two parts: the Partition Key and the Clustering Columns. The first part maps to the storage engine row key, while the second is used to group columns in a row. In the storage engine the columns are grouped by prefixing their name with the value of the clustering columns. This is a standard design pattern when using the Thrift API. But now CQL takes care of transposing the clustering column values to and from the non key fields in the table.

Then read the explanations in "The Composite Enchilada".