Introduction to Couchbase for Oracle Developers and Experts: Part 5: Statements and Features

SQL is the only 22nd century tool available in 21st century

Here are the previous articles comparing architecture, database objects, data types, and data modeling of Oracle with Couchbase. This will focus on SQL support.  

Oracle was the first and staunch supporter of SQL. Oracle's SQL implementation beat IBM to market by two years.  That changed the fortune of one or two people. :-) All of the modern relational databases implement SQL. So much so, the relational databases are sometimes called SQL databases, much to the chagrin of C. J. Date.  Nations are known by their languages... English, French, and American(!). It's not a stretch for a class of database systems to be known by their languages as well.  SQL has been so effective, many big data and non-relational systems have picked up SQL as the lingua franca. SQL is here to stay, even for NoSQL systems. 

SQL > SQL

SQL may have started as a structured english query language, but it has grown to be so much more.  Roughly, SQL now consists of:

• Data Definition Language (DDLs): 
  • To create the physical objects (indexes, tables, views, triggers) to represent the model
  • Various data types and their exact definitions
• Declarative Query Language
  • SELECT, INSERT, UPDATE, DELETE, MERGE, etc.
  • Arithmetic, logical, set operators
• Transactions
  • Definition of the framework
  • Control statements: BEGIN WORK, COMMIT, ROLLBACK, SAVEPOINT
• Procedural language
  • Stored procedures (PL/SQL, T-SQL, SPL)
• Optimizer
  • The query optimizer, especially the cost-based optimizer, 
• Modern data types, structures, and features
  • Spatial,  text search, queues

There's ANSI SQL Standard, defined in 14 volumes. It's safe to say no one vendor implements all of the standards. Sometimes a feature is implemented in a product ahead of the standard.  E.g., Hierarchical queries in Oracle are implemented with CONNECT BY extension, but the standard syntax is via recursive Common Table Expressions.  Oracle supports both for backward compatibility. 

SQL in Couchbase

SQL, the language and the underlying principles, has been unreasonably effective even in NoSQL databases. NoSQL databases went from "absolutely no SQL" to "Not Only SQL". Whatever works. :-). In reality, SQL itself wasn't the problem, it was the solution. When the operational databases went from relational to NoSQL model, it was difficult to implement on a distributed NoSQL database and still meet the performance and high-availability objectives. It took time, but now a number of NoSQL systems are doing that. Couchbase follows SQL closely and others to various degrees. 

Couchbase has designed N1QL (SQL for JSON). Couchbase has two N1QL query engines, one for OLTP, another for OLAP.  The use cases for each are straightforward.

High-Level Comparison                   

Query Processing Architecture 

Query Service

All databases have query, index, data layers. Couchbase has abstracted these into different processes interacting via APIs. Hence, you can multiple instances of these in multiple nodes of the cluster and can be combined in any combination on each node to meet specific and elastic workloads.  The diagram below shows the logical flow of the query processing.  Each query is run on a single node of a query service using one or more index and data nodes.

Analytics Service

Couchbase analytics service supports N1QL, targeted for queries analyzing large amounts of data on its shared-nothing MPP engine. It can analyze data from one or more Couchbase clusters as well datasets in CSV, TSV, JSON formats. More formats are in the roadmap.  It has DDLs to map data from the data engine and create external data sets. It has extensive DML (SELECT) to run reports on the data, including joins, window functions, and user-defined functions.   See the details in the talk below. The rest of the article mainly focuses on the statements and features in the query service targeted for OLTP (operational) workload. 

function calccircle() {  
  const pi = 3.141592653589793;  
  var radius, area;  
  rq = select  r as rad from [1, 2, 3, 4, 5] as r;  
  var acc = [];  
  for (const row of rq) {    
    radius = row["rad"];    a
    rea = pi * (radius ** 2);    
    var qi = INSERT INTO b VALUES(UUID(), {"radius": $radius, "area": $area}) RETURNING * ;    
    for (const r1 of qi) {acc.push(r1);}  }  
  return acc; 
}

create or replace function ps1() language javascript as "pselect1" at "p1" ; 
EXECUTE FUNCTION calccircle();  
SELECT cinfo FROM calccircle() as cinfo; 

Data Manipulation Language Statements 

In Oracle, SELECT, INSERT, UPDATE, DELETE, UPSERT, MERGE are the main data manipulation statements. The same is true for Couchbase N1QL language. SELECT is the most used statement - In Oracle, just the SELECT syntax diagram itself runs into 24 pages. It has the most features and complexity. Other statements, while typically simple and straightforward, can use SELECT as a subquery and things can get complicated. Especially when the SQL is generated by tools. Let's compare the common features, things that are common, and things that are different.

Recommended prereq:

1. Couchbase N1QL boolean logic: here

2. Couchbase N1QL Data types: here

    -- Comparison to Oracle Datatypes

3. Couchbase N1QL Literals: here

4. Couchbase N1QL Identifiers: here

This should give you an overview of commonly used SQL and N1QL statements in Oracle and Couchbase. Each product is too extensive to cover comprehensively.  The official documentation will give you further details. In the coming posts, we'll go deeper into DML. 

Next up are indexes, optimizers, and transactions.