Introduction to Couchbase for Oracle Developers and Experts: Part 5: Statements and Features
In this blog series, we are exploring various topics to compare Oracle and Couchbase from a developer perspective. Today, we'll talk about Statements and Features.
Join the DZone community and get the full member experience.Join For Free
SQL is the only 22nd century tool available in 21st century
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
- Definition of the framework
- Control statements: BEGIN WORK, COMMIT, ROLLBACK, SAVEPOINT
- Procedural language
- Stored procedures (PL/SQL, T-SQL, SPL)
- 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.
SQL takes one or more sets of tuples(rows), processes the set as specified by the query, and gives you another set of tuples (rows). While it's normal to think about row by row processing, SQL rules come from and apply to a set.
N1QL stands for Non-1st-normal Query Language. The goal of the N1QL language and the engine is to give developers and enterprises an expressive, powerful, and complete language for querying, transforming, and manipulating JSON data. This means it's designed to manipulate more than just numbers and strings. It can easily handle nested objects, arrays, arrays of objects, objects of arrays, arrays of arrays of objects, and so forth. Real-world data, and therefore JSON, can be nested and complex. You need a language to give that power to the developer.
Query Processing Architecture
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.
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.
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.
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.
Published at DZone with permission of Keshav Murthy, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.