3

I have a column family that store articles. I'll need to get those articles from the most recent to the oldest, getting them from Country, and of course the ability to limit the number of fetched articles.

I though about another ColumnFamily "ArticlesByDateAndCountry" with dynamic columns

The Key would a mix from the 2 Char country Code (ISO 3166-1), and the articles day's date so something like : US-20150118 or FR-20141230 -- (XX-YYYYMMDD)

In those Row, the column name would be the timeuuid of the article, and the value is the article's ID.

It would probably get a thousand of articles per day for each country.

Let's say I want to show only 100 of the newer articles, I'll get the today's articles, and if it does not fill the request (too few articles), I'll check the day before that, etc...

Is that the best practice, or does someone has a better idea for this purpose ?

1

I thought about another ColumnFamily "ArticlesByDateAndCountry" dynamic columns.

You're on the right track with this thought, but I'd stay away from dynamic columns. Currently, there isn't a way to really manage column families with dynamic columns in CQL3. So, the only real solution is to go the route of creating it in the cassandra-cli, which is being deprecated. Sticking with CQL tables gives you a much easier path to data access, and that in itself (IMO) makes it worth it. Besides, all the latest drivers work with CQL, so you're really backing yourself into a corner by choosing a path that can't be managed by it.

While it may not be readily apparent, there is a way to solve your current problem to adequately serve your queries. I would build a (CQL) table, like this:

CREATE TABLE ArticlesByDateAndCountry (
 countrycode text,
 articledate timestamp,
 field1 text,
 field2 text,
 PRIMARY KEY ((countrycode),articledate))
WITH CLUSTERING ORDER BY (articledate DESC);

Note: I have created two sample payload fields, field1 and field2. I'm sure your payload fields will vary. Also, I have opted to use a timestamp instead of a timeuuid, as it makes the example easier.

Essentially, this will group your data by countrycode. And within each countrycode, your data will be sorted by articledate.

SELECT articledate, field1, field2
FROM ArticlesByDateAndCountry 
WHERE countrycode='US' 
AND articledate >= '2015-01-23 00:00:00' AND articledate < '2015-01-24 00:00:00';

You should read Patrick McFadin's article Getting started with Cassandra time series data modeling. It has several examples that are quite similar to what you are doing here. While I have demonstrated this with the timestamp type, you could very easily make this work with time UUIDs instead. Here is a link to DataStax's documentation on Cassandra's timeuuid functions that I am sure you will find useful.

  • Thanks for the answer. I was thinking of this model due to performances querying, I though of course of the composite Key, but what if there is billions of rows, is querying will be as fast as the wide column model ? Of course the issue won't hit right away, but I would like to avoid remodeling the database due to performance issues – TheSquad Jan 23 '15 at 15:52
  • Cassandra can handle 2 billion columns in a row, so with articles 1000 per day and 3 cols per clustering key, you'll be storing 1.095 million columns per country each year. If that's not going to be enough, you could always make the articledate (like you were planning to, or even just part of it) part of the partitioning key, as well. Take a look at Pattern #2 in the Time Series article for more info on that. – Aaron Jan 23 '15 at 16:26
  • Actually the model I though of, was to create on a day basis row per country, not year, If I want to get only 100 articles, I don't want to transfer 1 million rows to from the cluster to the webservice, and take only 100 from a million... – TheSquad Jan 24 '15 at 1:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.