Friday, June 17, 2016

Is Oracle's Larry Ellison Wrong on Object Stores?

It's All About the DATA

Here's Larry Ellison's critique of Workday. "Workday does not use a database, they use an object store. So, they can't really do reporting. They use flash. So, they can't run on iPhones or iPads. Besides that, they're great!"


 Here's the overview Workday application architecture (from Workday).
Workday uses object model and stores the objects MySQL table as a blob instead of normalizing and storing the data in multiple tables, tuples, and columns. Object store makes object-relational mapping, object disassembly/assembly unnecessary. Because you store objects, you lose some benefits of RDBMS: query processing -- select, join, project, schema, optimizer to rewrite complex query, and help the query to perform better.  Complex query processing is essential for canned or ad hoc reporting.  Each task will be have to be written as a program that retrieves all the objects, explode them into runtime objects, and then do the analysis.  It's slow. It's expensive.
Larry Ellison may have been right back in 2012.  BLOB or TEXT were the options to store objects in most RDBMS. DB2 and others had XML store with XQuery never really took off in any serious enterprise application. Back in 2012, MongoDB, provided object store with its flexible JSON data model, basic query processing, was focusing on developers, rather than enterprise.  Any reporting or complex query processing was not possible.
Workday would have to support very limited reporting on their object store or provide ways to extract the data into a normalized form to enable reporting on them. Additional setup, maintenance, work and cost.
Document oriented databases like MongoDB, Couchbase, DynamoDB, DocumentDB have all provided a way to store in JSON.  JSON stores hierarchical data as nested basic types, objects, and arrays.  So, we can store hierarchical objects in JSON, limiting the object to store mapping work. Here’s an example of customer object in JSON.
Now that we have object and its persistence defined, you can develop application on this object store.  Couchbase SDKs and MongoDB SDKs help you develop applications. So far, so good.
The criticism from Larry Ellison back in 2012 was that while you can develop applications, trying to reports on the data these applications created is impossible.
While document databases started out with with the NoSQL approach of basic GET, SET APIs, they’ve seen the need for complex query and provided them to various degrees. Mongo’s query API and Couchbase N1QL is inspired by SQL.  Some more than others :-)
MongoDB provides SQL inspired basic version of SELECT, INSERT, UPDATE, DELETE, UPSERT.  For reporting, MongoDB’s find() provides basic select-join-project operation,  You can even get the plan using the explain() on find().  Aggregation and joins are provided via the aggregate() method.  The aggregation method provides all of the basic filtering, grouping, aggregation, sorting functionality.  Pipelining of the aggregation helps you with multi-level filtering/ordering/grouping.  The aggregate comparison page shows you how the simple SQL statements can be written in MongoDB form.
On the other hand, Couchbase N1QL stays close to SQL, extending it to access & manipulate JSON. In Couchbase N1QL, you can issue queries like this on JSON data.  Select, join, project, nest, unnest, subqueries, aggregation, pagination, sorting are all available just like SQL. You can also generate reports using Couchbase N1QL.  Look at the example below. 
So simple and so SQL. 
Checkout more examples at querycouchbase.com.  Couchbase N1QL also has ODBC, JDBC drivers from Simba to enable BI tools, Excel to directly access and create complex reporting and visualization. Checkout the talk and the blog on using this on your data.
Is Larry Ellison wrong for saying: “you can’t do reporting on object store” in 2012?  Fortunately, you’re not in 2012 anymore.

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