Sunday, February 8, 2015

SQL is dead. Long live SQL.

SQL and relational database systems (RDBMS) have been used and have evolved so closely that RDBMS is sometimes referred to as the SQL database, much to the chagrin of the theorists. Relational calculus and theory provided foundation for data modeling and manipulation. SQL provided applications and ad-hoc users, an easy way to define schema and manipulate the data.  Its declarative nature, drivers, language bindings, and integration with multitude tools has made SQL indispensable.
  
Even with the absence of SQL, NoSQL systems have become successful.
Lack of SQL implies joins and advanced aggregations are absent. These are not the operations you can crank out with an all-nighter or two.

The issues of scalability (#ScaleOutNotScaleUp), lack of hierarchical and flexible data model are severe in RDBMS. NoSQL applications work around this issue using various techniques like denormalization (to avoid joins), duplication or summarization of data to support variety of queries and expressions.  As enterprises move more mission critical applications to NoSQL databases, cost of working around the limitations will increase.

NoSQL provides flexible schema. That means, each data set is self-describing, changing from tuple to tuple. Data can be flat or hierarchical. Using JSON is the most popular way to represent flexible and hierarchical schema for operational applications.

Facebook broke the barrier with Hive project providing SQL for analytics on top of the data sets on Hadoop. Since then, there has been explosion of SQL on top of NoSQL. Spark to Drill, CQL to N1QL, Tajo to Presto – all aim to create SQL on top of NoSQL data store.  

These new SQL engines are making SQL richer and relevant. N1QL is one of the premier SQL systems to aid operational applications to exploit JSON data model.

N1QL basics 

N1QL (pronounced nickel) is created from the ground up to support this self describing hierarchical data model for data in Couchbase. It is modeled on SQL -- provides the select-join-project-aggregation-sorting-pagination operations that SQL does.  You can find the details of the latest release here.

Unlike other SQL engines, N1QL is a JSON-in and JSON-out query engine. Applications can store & manipulate large datasets of JSON just like SQL manipulates large sets of regular data.  Underlying relational model and the language has been extended to suite model. The SQL++ paper explains many of the underlying concepts.

Here are some tips and observations for SQL folks using N1QL.

Let's look at examples:








  • It’s all JSON.


  • JSON-in and JSON-out. Whether you project simple constant,   key-value pairs, aggregates or any combination, the result set is a JSON document.

    cbq> select "Hello, World!" as HiWorld, "Hello, " || "N1QL!" as HiNickel;

    {
        "requestID": "c306b029-8af4-4b06-8079-96aa6e81c1da",
        "signature": {
            "HiNickel": "string",
            "HiWorld": "string"
        },
        "results": [
            {
                "HiNickel": "Hello, N1QL!",
                "HiWorld": "Hello, World!"
            }
        ],
        "status": "success",
        "metrics": {
            "elapsedTime": "2.671362ms",
            "executionTime": "1.405229ms",
            "resultCount": 1,
            "resultSize": 90
        }
    }


    The "describe" information, resultset and query run statistics are all sent as a resultset.   The "signature" is the describe information, with the best possible guess of each projected expression. When the output is uncertain, type will be "json". In addition, the key-value pairs are alphabetically ordered and hence will not follow the exact order in the projection list.  In this case, "HiNickel" is the first key-value pair even though it's the second expression in the projection list.  That's a mindset change from SQL!

    "results" is an array of all the documents with resultset.

    • It's SQL!

    Here is a sample document from the `beer-sample` data set shipped with product.

    cbq> select * from `beer-sample` limit 1;
    {
        "requestID": "4cd67ffd-62f8-455e-bf10-44f61a12a3c9",
        "signature": {
            "*": "*"
        },
        "results": [
            {
                "beer-sample": {
                    "abv": 0,
                    "brewery_id": "bibiana_brewing",
                    "category": "North American Ale",
                    "description": "",
                    "ibu": 0,
                    "name": "Hop-Stuffed Pale Ale",
                    "srm": 0,
                    "style": "American-Style Pale Ale",
                    "type": "beer",
                    "upc": 0,
                    "updated": "2010-07-22 20:00:20"
                }
            }
        ],
        "status": "success",
        "metrics": {
            "elapsedTime": "184.208765ms",
            "executionTime": "184.086738ms",
            "resultCount": 1,
            "resultSize": 473
        }
    }


    Here's a query to get a query grouping the information by its type and number of such documents.  It's as simple as 1-2-3!

    cbq> select type, count(type) as type_count 
         from `beer-sample` 
         group by type 
         order by type_count  desc;
    {
        "requestID": "afb2b1fb-5fcc-46a6-8a77-cbd29b440159",
        "signature": {
            "type": "json",
            "type_count": "number"
        },
        "results": [
            {
                "type": "beer",
                "type_count": 5891
            },
            {
                "type": "brewery",
                "type_count": 1412
            },
            {
                "type_count": 0
            }
        ],
        "status": "success",
        "metrics": {
            "elapsedTime": "623.210565ms",
            "executionTime": "623.08442ms",
            "resultCount": 3,
            "resultSize": 182
        }
    }

     Ready to learn more on N1QL?  Checkout the current documentation, try out the tutorial and stay tuned to this blog!

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