Skip to main content

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!
    Labels:

    Comments

    Post a Comment

    Popular posts from this blog

    MongoDB to Couchbase for Developers: Part 2: Database Objects

        MongoDB developers and DBAs work with physical clusters, machines, instances, storage systems, disks, etc. All MongoDB users, developers, and their applications work with logical entities: databases, collections, documents, fields, shards, users, data types.  There are a lot of similarities with Couchbase since both are document (JSON) oriented databases.  Let’s compare and contrast the two with respect to database objects.  Here’s the first part of this series comparing the architecture.   MongoDB Organization A full list of MongoDB schema objects is listed here and here . A database instance can have many databases; A database can have many collections; Each collection can have many indexes.  Each collection can be sharded based into multiple chunks on multiple nodes of a cluster using hash-sharding strategy or index sharding strategy.  The MongoDB indexes are local to their data.  Therefore, the indexes use the same strategy as ...
    3 comments
    Read more

    Swami Vivekananda: The Monk That Nobody Sent to Chicago

      There’s a saying in Chicago: “We don’t want nobody that nobody sent.” This was the cold reception Swami Vivekananda faced when he arrived in the windy city in July 1893, determined to attend the World Parliament of Religions that September. He belonged to no organization, carried no letter of recommendation, his countrymen were nobody, and represented an alien religion to the Western world. As the days passed, his hope of attending the parliament dwindled. With money running out and the odds stacked against him, he left the Windy City and went to Boston, praying for a glimmer of opportunity.  Swamiji came to America to share India’s most profound gift: the wisdom of the Hindu sages, preserved through centuries of oral tradition and embodied by its monks. This was 1893, not 1993—India was under the British grip, its resources drained, and its spirit subdued. Swamiji’s mission was not just a cultural exchange; it was a bold step toward envisioning a future where India could re...
    Post a Comment
    Read more

    MongoDB to Couchbase for Developers: Part 1: Architecture

      Introduction By any measure, MongoDB is a popular document-oriented JSON database. In the last dozen years, it has grown from its humble beginnings of a single lock per database to a modern multi-document transaction with snapshot isolation. MongoDB University has trained a large number of developers to develop the MongoDB database. There are many JSON databases now. While it's easy to start with MongoDB to learn NoSQL and flexible JSON schema, many customers choose Couchbase for performance, scale, and SQL. As you progress in your database evaluation and evolution, it's easier to start from what you know now. That's the idea for this series. Use your MongoDB knowledge to easily learn and eventually master Couchbase.  Don't despair if you don't know MongoDB do know any one of the RDBMS. This Oracle to Couchbase series will help you learn by comparison.  Many nations are known by their language: French, German, Spanish). Many databases are known for their language...
    1 comment
    Read more