Carlos says:

Will composite keys be efficient on large data sets in Cassandra 1.1?

E.g.

CREATE TABLE actions(
user_id varchar,
time_uuid uuid,
…
PRIMARY KEY (user_id, time_uuid)
);

Tyler Hobbs says:

Carlos, I’m not sure what you mean. There’s no reason that I can think of that would make composites inefficient now or in Cassandra 1.1. If you are using CQL, what you’re describing is only possible in CQL 3.0, which you can choose to use in Cassandra 1.1.

Can you be more specific with your question?

Carlos says:

I was reading “Schema in Cassandra 1.1″. In the section “Clustering, composite keys, and more”, it describes defining CFs with composite primary keys. Will composite primary keys scale well with large data sets for time series?

Tyler Hobbs says:

Yes, they shouldn’t have any problems. The first component of the composite will be used as the row key; the remaining components become part of a composite column name. Queries for a slice of these will only read a portion of a single row, so it will be very efficient. You should have no problems scaling it.

Charlie 木匠 (@mujiang) says:

Howdy,

Can I ask a data model question here?

We have a book table with 20 columns, 300 million rows.

create table book(
book_id,
isbn,
price,
author,
titile,
col_n1,
col_n2,
…
col_nm,
);

Data usage:

We need to query data by each column, do pagination as below,

select * from book where isbn < "XYZ" order by ISBN descending;
select * from book where price < 992 order by price descending;
select * from book where col_n1 < 992 order by col_n1 descending;
select * from book where col_n2 < 992 order by col_n2 descending;
…
select * from book where col_nm < 992 order by col_nm descending;

We update book table in a high rate, at about 100 millions updates a day.

If we choose Materialized Views approach, we have to update 20 column(s) in each Materialized View
column family, for each base row update.
Will the Cassandra write performance acceptable?

Redis recommend building an index for the query on each column, that is your 1st strategy:
http://redis.io/topics/data-types-intro
(see section [ Pushing IDs instead of the actual data in Redis lists ]

Should we just normalize the data, create base book table with book_id as primary key, and then
build 20 index column family(s), use wide row column slicing approach, with index column values as column name and book_id as value?
This way, we only need to update fewer column family that column value changed, but not all 20 Materialized Views CF(s).

What do you think of it?

Thanks,
Charlie | DBA developer

Bhaskar says:

Can the 2 approaches, i.e. high-throughput timelines and varying buckt-size , be somehow combined , or used together.

Or are they orthogonal, in that they achieve the same goal, to avoid hot-spots by distributing the data across nodes, but go about in different ways of achieving that goal.

Secondly If the entire data is stored i.e. Materialized View approach, doesn’t that make the data difficult to search on, on account of not being able to create secondary index. i.e. How will you index some of the keys that are part of the data ?

Those are great questions!

The two approaches can definitely be used in conjunction; they are not mutually exclusive. They have different goals: the variable bucket size approach helps to control the total width of rows, while the high-throughput approach minimizes the hotspot effect by distributing each time bucket in the timeline across multiple nodes. If you have concerns about both of these, you can combine the two approaches.

Regarding the materialized view approach, you are correct, you can’t use the built-in secondary indexes directly on this data. Typically, what you do instead is create a separate materialized view column family for each type of query that you need to support on that data and denormalize so that the data exists separately in each of those column families. So, if you need to be able to query by time and also query by some second attribute, you will use one column family for the timelines and a second column family that will support queries based on the second attribute efficiently.

@Charlie
Redis is different planet. It pay no price for the disk I/O lookups.
With Cassandra you should de-normalize, in order to do less reads as possible, without having too long rows.

In the materialized table it appears you are using super columns. Is this correct or am I misinterpreting the diagram?

I understand super columns will be replaced ultimately with composites.

If my interpretation of the diagram is correct, how will using composite types change the materialized table?

will it result in something like this?

timeline_ids = [32232,45545,76566]
columns_names = [‘foo’, ‘bar’, ‘baz’]
columns = [(id,name) for id in timeline_ids for name in columns_names]

column_family_name.get(user, columns=columns)

Is this the most appropriate means?

@rikAtee I am not using super columns in the materialized view example, just storing a JSON blob. If you need to be able to update individual fields in those columns frequently *and* it’s not easy or efficient to overwrite the entire thing, you could use either supercolumns (not recommended) or composites (recommended), especially through CQL 3.

For the composites-with-cql3 approach, I suggest checking out some of the recent blog posts on CQL 3: http://www.datastax.com/dev/blog/whats-new-in-cql-3-0, http://www.datastax.com/dev/blog/thrift-to-cql3, http://www.datastax.com/dev/blog/cql3_collections, and http://www.datastax.com/dev/blog/cql3-for-cassandra-experts.

Whatever you end up doing, I don’t suggest trying to fetch a lot of columns by name, like your example code. Always try to use slices if you’re fetching a lot of columns.

We are planning a major time series database with Cassandra and wanted to thank you for this article. We made a few modifications, but one thing I would note is that using JSON for your data will take up a lot of space. There are alternatives out there but I won’t name them, a simple Google search should suffice though.

while using counter column family we can maually update values of counter say c1, c2 using cli

how to achieve this using hector client,i want counter column to accept only 20(e.g.) records and than after new column c2 (with 20 rec) and than c3 and so on

select * from timecontentcounter;

key | column1 | value
————+———+——-
2013053015 | c1 | 20
2013053015 | c2 | 20
In above example new column c3 should be generated to accept counter increments upto 20 records ,hector solution is needed

I am trying to store data with following schema:

CREATE TABLE temp_humidity_data (
asset_id text,
date text,
event_time timestamp,
temprature int,
humidity int,
PRIMARY KEY((asset_id, date),event_time)
)

I have followed datastax article ‘Getting Started with Time Series Modeling’ – http://planetcassandra.org/blog/post/getting-started-with-time-series-data-modeling/

however with this data model one thing that is not working is query that returns me data between two dates. How do I do that?

If I do this:

select * from temp_humidity_data
where asset_id=’1234′ AND date >= ‘2010-04-02′ AND date <= '2011-04-03';

It gives me following error:

code=2200 [Invalid query] message="Only EQ and IN relation are supported on the partition key (unless you use the token() function)"

In understand there is a way to do IN operator but I don't want to put all those dates in a 'IN' operator. Is there a way to query when using the above table definition data between two dates?

@Tyler
In my specific case it’s about stock data (timeseries). And I’m using the latest Cassandra version.

CREATE TABLE ats.raw_ts (
listings_id uuid, // Indirect the Symbol (eg. APPLE), it can handle symbol changes
insertion_time timeuuid,
type text, // Eg. ‘tick’
price int,
ask int,
bid int,
bid_size bigint,
ask_size bigint,
high int,
low int,
volume bigint,
PRIMARY KEY(listings_id, insertion_time, type)
)
WITH CLUSTERING ORDER BY(insertion_time desc);

Now is my question: Is the symbol as partition key enough? Will Cassandra spread the data around the cluster efficiently, by using the WITH CLUSTERING ORDER BY?

Or you I need to setup a SECOND partition key? Like this:

CREATE TABLE ats.raw_ts_2 (
listings_id uuid,
date text,
insertion_time timeuuid,
type text,
price int,
ask int,
bid int,
bid_size bigint,
ask_size bigint,
high int,
low int,
volume bigint,
PRIMARY KEY((listings_id, date), insertion_time, type)
)
WITH CLUSTERING ORDER BY(insertion_time desc);

As you can see, the partition key is now a combination of the symbol & year.

The pro is that the data will be spread on the year. Let’s say you need 2 nodes to store the data. 1 node could be 2014 data & another 2015 of the same symbol (eg. AAPL). The big con is here; if you want to get ALL the data over 2 years or more, you need to get the data from several nodes. As you said, using IN.

Another performance issue could be around old/new year. Where you definitely want some data from the months of previous year, together with the data from the next year.

So, waht is the best solution? Is Cassandra smart enough with CLUSTERING ORDER BY? Do I really need this second partition key?

Hello Tyler – if I stored the data in JSON lob, how would I query them ( to get the individual values ) using a cql. or is there a better way to query them. please suggest.

Hi

This is really very helpful. can you please explain this with an example, like take a table where we need to store the time series data and suggest the DDL and best partition keys. you may like to assume that we need to select values based on timestamp field for the sake of simplicity.

What is the meaning of “Variable Time Bucket Sizes” , can you please give an example, how to actually implement it.

Hi Tyler, This is a great post. very helpful.

I am modeling to store time series data, i am new to cassandra, have following questions

1. I want to partition my table on timestamp is a such a way that one partition will always keep latest 36 hours of data and then other partition should be partioned for 1 day of data.

Can you please give an example on how to do this.

2. My model will keep the parameter data from different machines – machine_id is the key . what should be the best primary key

an example would be very helpful to understand. if you can please take a sample table structure and show us how to model it , that would be very helpful.

Ok, try it again. Why the composition key is ‘timeline:bucket:partition’ but not ‘bucket:timeline:partition’?

Either one would work fine. If you’re using RandomPartitioner or Murmur3Partitioner (which you should be using) then keys aren’t sorted anyway.

Hi Tyler, This is a great post. very helpful.

I am modeling to store time series data, i am new to cassandra, have following questions

1. I want to partition my table on timestamp is a such a way that one partition will always keep latest 36 hours of data and then other partition should be partioned for 1 day of data.

Can you please give an example on how to do this.

2. My model will keep the parameter data from different machines – machine_id is the key . what should be the best primary key

an example would be very helpful to understand. if you can please take a sample table structure and show us how to model it , that would be very helpful.