Is it possible to construct complex queries agains

Question

I have been researching noSQL DB and have not been able to satisfactorily answer this for myself: Is it possible to construct complex queries against noSQL DB?

The type of query I'm wondering about would be something like this:

select * from DB where
vara > x AND
varb = 2 AND
varc < x AND
vard in (x,y,z) AND
vare like '%texthere%' AND
varf = 2 AND
varg = 3 and
etc...

NOTE: I'm aware that I can't use SQL, as above, what I'm asking is how would I query using the psuedo logic above, in other words a whole bunch of different conditions. So far the best answer I have found is that you have an RDBMS to query and then grab data based on key from cloud. That doesn't necessarily seem more efficient to me.

So as a follow up. If just trying to solve a 'search 4 million rows' problem as opposed to a 'we have billions of rows of data' problem, should I even bother looking at a noSQL DB?

Answer 1

It depends on the data store you are using.

I frequently use AppEngine and their data store only allows inequality on one column (and that column must be the first element in the sort order. So you would not be able to run the query you posted, but you could do a similar one:

select * from DB where
vara > x AND
varb = 2 AND
varc in (t,u,v,w)
vard in (x,y,z) AND
varf = 2 AND
varg = 3

Also, you can do things like have a column that contains a list of strings and select rows that have a value in the list.

So, the official answer is "maybe, sorta, sometimes, but not really, except when yes"

Answer 2

In my opinion, you can do this in a document data store, such as MongoDB, but not easily in a key-value data store such as Cassandra. If you were to do it in a key-value data store, the composite key would have to identify all the queried data elements (columns). In other words, one instance would have to have all the query columns. This is possible. In a vanilla key-value store, there is one data element (column) per key but you can do it to support many. A key value store permits this because the value is just a string value and can contain what you want. I recommend multiple data elements (columns) per key but you would have to program for this. If instead you have the columns in different column instances, it would be very slow to search through the data vertically, even though the columns are ordered. The sample in your question has only one table. If you have multiple tables in relational, in key-value stores you would have to create a new column family to store the joined data and you would still have to have multiple data elements per key. However, it would have to be pre-loaded horizontally in one instance in one family by an ETL-like process. In other words, join the data before it is loaded into the key value store, and design the Cassandra data store to store the already-combined data in a column with multiple values. Or use data mining. I believe "big data analytics" are currently addressing this problem in key-value stores. Another example, less sophisticated than yours, is how in key-value stores can you produce a simple report of sales revenue and volume by customer by product by week?

Answer 3

In mongodb, you would just do something like db.mytbl.find({"vara": { $gt: 10}, "varb": 2, "varc": {$lt: 100 }})

See here, and here for examples

Answer 4

I'not a NoSQL expert, but as the name says, they don't rely necessary on SQL. You can probably do whatever you want, but will need to code map/reduce function or other non-SQL way to query the data.

Maybe this blog provide useful information to you: Query processing for NoSQL database