You've seen why and how to model data in JSON. Once you have the data in JSON, you can store and retrieve the JSON documents in any simple key-value databases. If your application only has to set and get the documents based on the key, use any key-value store. you're all set.
That's seldom the case.
You don't use a database just to store and retrieve JSON. You're trying to solve a real business problem. You're developing a business application. For a business application, the questions, operations, and reports don't change just because your database doesn't support query. Business applications have to do many things requiring multi-object operation:
- Process Order Checkout.
- Search stores data for the shoe customer is looking for.
- How many new customers did we get last month?
- Generate the outstanding list of shipments due for today.
- Retrieve the customer order using case insensitive customer name.
- Load the new inventory data.
- Merge customer lists.
For doing each of these tasks, you need to search your database efficiently. Do the select-join-group-project-aggregate-order processing of the data to produce a report. Similarly, you'll have to insert, update, delete, and merge to represent the real-world business processes.
Now, the question is, how would you go about implementing all these for a NoSQL key-value systems with GET and SET APIs? You do this by writing a program to do each task. Of course, writing the application is expensive. Scaling, debugging, and maintaining it makes it even more expensive.
Imagine a use case: Find high-value customers with orders > $10,000. With a NoSQL API approach, you write a program to retrieve the objects and do the filtering, grouping, aggregation in your program.
Now, the question is, how would you go about implementing all these in a NoSQL key-value systems with GET and SET APIs? You do this by writing a program to do each task. Of course, writing the application is expensive, scaling, debugging and maintaining it makes it even more expensive.
This is the reason RDBMS and SQL were invented. SQL does set processing naturally. On the relational model, each of these tasks can be done via a series of statements. Each SQL statement takes set of tuples, does the processing, and produces another set of tuples.
SELECT c.CustID, Customers.Name, SUM(o.Amount)
FROM Orders o INNER JOIN LineItems l ON (o.CustID = l.CustID)
INNER JOIN Customers c (o.CustID = c.CustID)
GROUP BY c.CustID, c.NameHAVING SUM(o.Amount) > 10000
ORDER BY SUM(o.Amount) DESC
As the NoSQL key-value databases evolved, they added the map-reduce frameworks so developers can use and cascade them to do complex work. Cassandra, MongoDB, and Couchbase introduced SQL-like languages on top distributed NoSQL database to make the query easier. MongoDB API looks different, but you'll still see something like SQL.
Couchbase N1QL (Non-First Normal Form Query Language) provides all of the select-join-project operations in SQL on the flexible JSON data model. N1QL defines a four-valued boolean logic for boolean expressions that include the new MISSING value in a flexible schema and the collation sequence for data types. In addition, N1QL extends SQL to access and manipulate all parts of nested JSON structure.
N1QL takes sets of JSON documents as input, processes them and gives you a set of JSON documents. Just replace tuples with JSON in SQL definition, you get N1QL.
Let's see how we can write the previous query in N1QL. Pretty close to SQL, but it has access to nested data and does the UNNESTING of arrays within ORDERS document.
SELECT Customers.ID, Customers.Name, SUM(OrderLine.Amount)
FROM Orders UNNEST Orders.LineItems AS OrderLine
INNER JOIN Customers ON KEYS Orders.CustID
GROUP BY Customers.ID, Customers.Name
HAVING SUM(OrderLine.Amount) > 10000
ORDER BY SUM(OrderLine.Amount) DESC
In addition to SELECT, Couchbase N1QL supports the traditional SQL statements: INSERT, UPDATE, DELETE, and MERGE. Here are some examples:
INSERT INTO ORDERS (KEY, VALUE)
VALUES ("1.ABC.X382", {"O_ID":482, "O_D_ID":3, "O_W_ID":4});
UPDATE ORDERS SET O_CARRIER_ID = ”ABC987” WHERE O_ID = 482 AND O_D_ID = 3 AND O_W_ID = 4
DELETE FROM NEW_ORDER WHERE NO_D_ID = 291 AND NO_W_ID = 3482 AND NO_O_ID = 2483
Here's the comparison between SQL based RDBMS and N1QL:
Query Features | SQL on RDBMS | N1QL: SQL on JSON |
| Objects | Tables, tuples, columns | Keyspaces, JSON Documents, key-value |
| References | Flat references:Table.column E.g. customer.name, customer.zip | Flat & Nested: keyspace.keyvalue E.g. customer.name, customer.address.zip, Customer.contact[0].phone.mobile |
Statements | SELECT, INSERT, UPDATE, DELETE, MERGE | SELECT, INSERT, UPDATE, DELETE, MERGE |
Query Operations |
|
|
Schema | Predetermined Columns |
|
Data Types | SQL Data types Conversion Functions |
|
Query Processing | INPUT: Sets of Tuples OUPUT: Set of Tuples | INPUT: Sets of JSON OUTPUT: Set of JSON |
Summary:
NoSQL databases give you the ability to read and write massive amounts of data at a substantially low latency and substantially low cost compared to RDBMS.Not having a query is like having a great engine without a steering wheel. With query on JSON from Couchbase, you can ask important questions like -- where can I find my favorite beer?.
SELECT {"Mybeer": "My Beer is " || beer.name || "."},
Array_agg({"name":brewery.name}) brewery,
Array_agg({"name":brewery.name, "state":brewery.state, "city":brewery.city, "location":brewery.geo}) locations,
Array_count(Array_agg(brewery.NAME)) AS brewery_count
FROM `beer-sample` beer
LEFT OUTER JOIN `beer-sample` brewery
ON keys beer.brewery_id
WHERE beer.type = 'beer'
AND brewery.type = 'brewery'
AND brewery.state = 'California'
GROUP BY beer.NAME
ORDER BY array_count(array_agg(brewery.NAME)) DESC,
beer.NAME ASC limit 5 ;
You have your steering wheel back, now!
Comments
Post a Comment