A couple of simple questions
Hi,
I've been using Couchbase 1.8 for a while for session management and data caching and it works very well. I am now trying to see if I can do more with it, especially since 2.0 has been released and we got views. I watched some of the videos online and read a lot of the documentation, but somehow I cannot find the answers to my questions. Any help would be much appreciated.
Scenario:
My system has a bunch of objects defined in a model. Think of Persons, Vehicles, PersonAndVehicleAssociation and so on. There's many of these, but let's just use those 3.
Each of those objects (documents) have a bunch of properties (some of which are other types).
Think of a them like this:
Person
Id = "2187328-3221-122222"
Name
First = "Jack"
Last = "Smith"
Age = 35
Address
City = "New York"
ZipCode = "12345"
Vehicle
Id = "324333-2132-145666"
Plate
State = "NJ"
Tag = "ABC-123"
Make = "Chevrolet"
Model = "Corvette"
PersonAndVehicleAssociation
PersonId = "2187328-3221-122222"
VehicleId = "324333-2132-145666"
AssociationType = "Owner"
Pretty common stuff.
Now, I need to run queries such as the following:
"20 Persons SKIPPING 234400 WHERE Address.City = 'New York' AND Name.Last LIKE '%mit%'"
"FIRST 10 Vehicles WHERE (Plate.State in ['NJ', 'IL', 'CA'] OR Make LIKE 'Chev%') AND (...something else...)"
These queries are pretty much **always dynamic and customer-driven**. The UI allows our customers to search data any way they need. This is a pretty common need for any data-oriented system, so there has to be an answer somewhere... I just cannot seem to find it :(
QUESTION 1:
My first problem is the use of SQL LIKE functionality and the ability to mix a number of conditions.
Is this even possible thanks to the new views functionality, in a way that doesn't assume I will manually create a view for each possible combination of properties my users want to search on? In other words: I cannot know how my users will search for data. It has to be dynamic and I cannot create a Javascript code for each possible combination of things my users would want to search on. How do you do this?
QUESTION 2:
Is there any "SQL to Couchbase" set of articles or documents that cover these sort of scenarios? I assume the majority of developers looking at Couchbase for more than just a cache mechanism, would come from a SQL background. Are questions like these answered anywhere?
Thanks a lot!
Alessandro
Hi and many thanks for your answer.
Let me start saying one thing: By rereading my post below it might sound like I am a bit "hard". It's not my intention. I love the product for what I use it so far, but I wanted to see if I can use it for more. They are simple questions, no harshness meant :)
I skimmed over Elastic Search but, since it is a developer preview, I cannot really use it in production...
Regardless, the example at the very end of the page you pointed me to is not exactly what I am after. The fact it found a document with "Douchesse" somewhere in it is good for a few searches we do, but not for most. For instance, what I'd like to be able to search for is all beers where name contains "de" and abv > x and y. It didn't seem to me elastic search helps me much there. In other words, I need a *specific* property to contain a certain value, not *any* property. I also need to specify which property that is, at runtime.
"Translating SQL to Map/Reduce" was a good article but it seems to confirm what my fear was: That I need to think ahead of all possible searches users could make and have indexes built. It also makes me wonder what happens if I have 2 or more indexes made off of [string, string, number] (the last "carrot","rice",0 example) but that refer to totally different properties?
I am coming to the conclusion that the noSQL approach is good when you know what you're after (like in a web page where you can put links to other documents) but doesn't work when you don't know what you want ahead (like in a totally user driven query where anything could be used, in any combination). Am I correct assuming that what I am trying to address is something only an RDBMS can help me with, or I am missing something obvious (or not obvious, but an alternative way to look at the problem)?
Thanks,
Alessandro
Hello Allessandro,
Even if it looks like simple questions they are not in the way most of the NoSQL engine, including Couchbase are working.
When you want to do some queries with Couchbase you need to create views as documented here:
http://www.couchbase.com/docs/couchbase-devguide-2.0/indexing-querying-d...
So you have to create a view, that in fact creates/stores an index based on the values you have in your documents. But you can only do queries on a single index. (See below)
So when you need more advanced query features like the one you have described you can use our Elastic Search integration that allows you to do any type of query. You can take a look to:
http://blog.couchbase.com/couchbase-and-full-text-search-couchbase-trans...
About the views
One important part to understand is that when you are doing queries you are doing queries against this index (and only one index), so you can do things like:
state in ["CA", "NJ"] by creating a view with the state
and then query using the keys parameter keys=["CA","NJ"]
Like operation using the startkey/endkey parameter
I invite you to look at this document: Translating SQL to Map/Reduce
http://www.couchbase.com/docs/couchbase-manual-2.0/couchbase-views-writi...
Regards
Tug
Tug
@tgrall