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

    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

    Why Should Databases Go Natural?

    From search to CRM, applications are adopting natural language and intuitive interactions. Should databases follow? This article provides a strategic perspective. Amid the many technological evolutions in software and hardware (CISC/RISC, Internet, Cloud, and AI), one technology has endured:  Relational Database Systems   (RDBMS), aka SQL databases. For over 50 years, RDBMS has survived and thrived, overcoming many challenges. It has evolved and adopted beneficial features from emerging technologies like object-relational databases and now competes robustly with   NoSQL databases .  Today, RDBMS dominates the market, with four of the top five databases and seven of the top ten being relational. RDBMS has smartly borrowed ideas, like JSON support, from NoSQL, while NoSQL has also borrowed from RDBMS. NoSQL no longer rejects SQL. From a user perspective, all modern databases have SQL-inspired query language and a set of APIs. All applications manage the respective data...
    Post a Comment
    Read more

    iQ Interactive: Cool Things for Developers on Couchbase Capella iQ

      The landscape of software development is ever-evolving with the advent of new technologies. As we venture into 2023, natural language processing ( NLP ) is rapidly emerging as a pivotal aspect of programming. Unlike previous generations of tools that primarily aimed at enhancing coding productivity and code quality, the new generation of Artificial Intelligence ( GenAI ) tools, like iQ, is set to revolutionize every facet of a developer's workflow. This encompasses a wide range of activities: Reading, writing, and rewriting specifications Designing, prototyping, and coding Reviewing, refactoring, and verifying software Going through the iterative cycle of deploying, debugging, and improving the software Create a draft schema and sample data for any use case Natural language queries. Generate sample queries on a given dataset Fix the syntax error for a query Don't stop here. Let your imagination fly. Although the insights garnered from iQ are preliminary and should be treated ...
    Post a Comment
    Read more