Cassandra with Hadoop

There's a lot to consider. I'm going to narrow this down to a single problem and offer one solution, although there's many approaches.

Problem: You need AD-HOC queries on lots of rows.

First, setup a server to handle writes and a replicated slave (running some flavor of MySQL 5.5.x) to handle reads. My personal preference is Percona Server. Your mileage may vary.

Once you have replication caught up, ALTER your slave table to: engine=InnoDB, ROW_FORMAT=COMPRESSED, partition by date, and index the data to optimize your query WHERE clauses. Each of these have an optimization for your case and there is further tuning required.

Finally, setup a schedule to archive/shard the data when it falls out of scope. Say 2-3 years. Depends on your SLA for providing back reporting. You can always union it if you have to.

MySQL 5.6 will have Memcached built in which will allow you to store pre-processed results. Map/Reduce is great, as long as it's not the first run of the reduce function. NoSQL is great as long as the data is already processed. Neither is really your problem.

Additions 2012-06-13: Since it appears my recommendations have stirred another response, I'll submit my reasons:

1. Why InnoDB in this case? With the constant insertions of many rows, you can use InnoDB to avoid implicit read locks at the table level.
2. Why COMPRESSED + Barracuda? "The compression means less data is transferred between disk and memory, and takes up less space in memory. The benefits are amplified for tables with secondary indexes, because index data is compressed also."
3. Why partition by date? Reduce scan breadth.
4. Why index properly? Decrease search times by reducing cpu bottleneck through elimination of full table scans.

If you foresee that moving to Cassandra is definitely in your future, it will be easier to do while your dataset is still small and manageable. Also, as you learn and get a feel for Cassandra, a small dataset is a better one to make mistakes on (and thus, easier to correct them). That way your data model is solid by the time your dataset gets big, and that's when it really matters. And IMHO, there is no such thing as "too small" for Cassandra.

One of our applications uses Cassandra and ElasticSearch in prod. Based on those experiences, I would offer some caution about using ElasticSearch as a persistent datastore. We've seen it lose writes fairly often. Have a read through this discussion on Quora, appropriately titled: Why should I NOT use ElasticSearch as my primary datastore? That being said, it works great as a search engine.