Sunday, July 17, 2016

KEEP CALM and QUERY JSON


[reposted from my dzone article: http://dzone.com/articles/keep-calm-and-query-json]


Trying to break from SQL? Here's how JSON and the platforms that use it can lend a hand organizing and querying data.

Keep Calm and Query JSON

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. 
Image title
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.
Image title
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.
Image title

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. CassandraMongoDB, 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.
Image title
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
MERGE INTO CUSTOMER USING
      (SELECT CID FROM NEWCUST WHERE STATE = 'CA') as NC
ON KEY NC.CID  
     WHEN MATCHED THEN UPDATE SET CUSTOMER.STATE= 'CA';
Here's the comparison between SQL based RDBMS and N1QL:  
Query Features
SQL on RDBMS
N1QL: SQL on JSON
ObjectsTables, tuples, columnsKeyspaces, JSON Documents, key-value
ReferencesFlat 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
  • Select, Join, Project, Subqueries
  • Strict Schema 
  • Strict Type checking
  • Select, Join, Project, Subqueries
  • Nest & Unnest
  • Look Ma! No Type Mismatch Errors!
  • JSON keys act as columns
Schema
Predetermined Columns
  • Fully addressable JSON
  • Flexible document structure
Data Types
SQL Data types
Conversion Functions
  • JSON Data types: Strings, Number Boolean,  Objects, Arrays, null
  • 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!
Image title

A Deep Dive Into Couchbase N1QL Query Optimization

[Reposting of the article published with Sitaram Vemulapalli on DZone.  https://dzone.com/articles/a-deep-dive-into-couchbase-n1ql-query-op...