{"id":4894,"date":"2019-01-16T09:31:57","date_gmt":"2019-01-16T09:31:57","guid":{"rendered":"http:\/\/www.odbms.org\/blog\/?p=4894"},"modified":"2019-01-16T09:31:57","modified_gmt":"2019-01-16T09:31:57","slug":"on-sql-and-couchbase-n1ql-for-analytics-interview-with-mike-carey","status":"publish","type":"post","link":"https:\/\/www.odbms.org\/blog\/2019\/01\/on-sql-and-couchbase-n1ql-for-analytics-interview-with-mike-carey\/","title":{"rendered":"On SQL++ and Couchbase N1QL for Analytics. Interview with Mike Carey."},"content":{"rendered":"

“N1QL for Analytics is the first commercial implementation of SQL++.” –Mike Carey<\/strong><\/p><\/blockquote>\n

I have interviewed\u00a0Michael Carey<\/a>,<\/strong>\u00a0<\/em>Bren Professor of Information and Computer Sciences and Distinguished Professor of Computer Science at UC Irvine, where he leads the AsterixDB project, as well as a Consulting Architect at Couchbase<\/a>. We talked about SQL++, the AsterixDB project, and the Couchbase N1QL for Analytics.<\/p>\n

RVZ<\/p>\n

Q1. You are Couchbase\u2019s Consulting Chief Architect. What are your main tasks in such a role?<\/strong><\/p>\n

Mike Carey:\u00a0<\/strong>This came about when Couchbase began working on the effort that led to the recently released Couchbase Analytics Service, a service that was born when Ravi Mayuram<\/a> (Couchbase\u2019s Senior VP of Engineering and CTO) and I realized that Couchbase<\/a> and the AsterixDB project<\/a> shared a common vision regarding what future data management systems ought to look like. Rather than making me quit my day job, I was given the opportunity to participate in a consulting role and build a team within Couchbase to make the Analytics Service happen — using AsterixDB as a starting point. I guess now I\u2019m kind of a mini-CTO for database-related issues; I primarily focus on the Analytics Service, but I also pay attention to the Query Service and the Couchbase Data Platform<\/a> as a whole, especially when it comes to things like its query capabilities. I spend one day a week up at Couchbase HQ, at least most weeks. It\u2019s really fun, and this keeps me connected to what\u2019s happening in the \u201creal world\u201d outside academia.<\/p>\n

Q2. What is SQL++ ? And what is special about it?<\/strong><\/p>\n

Mike Carey:\u00a0<\/strong>SQL++<\/a> is a language that came out of work done by Prof. Yannis Papakonstantinou<\/a> and his group at UC San Diego. Prior to SQL++, in the AsterixDB project, we had invented and implemented a full query language for semi-structured data called AQL<\/a> (short for Asterix Query Language) based on a data model called ADM<\/a> (short for Asterix Data Model). ADM was the result of realizing back in 2010 that JSON<\/a> was coming in a pretty big way — we looked at JSON from a database data modeling perspective and added some things inspired by object databases that were missing. Most notable were the option to specify schemas, at least partially, if desired, and the ability to have multisets as well as arrays as multi-valued fields. AQL was the result of looking at XQuery<\/a>, since it had been designed by a group of world experts to deal with semi-structured data, and then throwing out its \u201cXML cruft\u201d in order to gain a nice query language for ADM. To make AQL a bit more natural for SQL users, we also allowed some optional keyword substitutions (such as SELECT for RETURN and FROM for FOR). We had a pretty reasonable technical explanation for users as to why AQL was what it was — why it wasn\u2019t just a SQL extension. Users listened and learned AQL, but they always seemed to wistfully sigh and continue to wish that AQL was more directly like SQL (in its syntax and not just its query power).<\/p>\n

More or less in parallel, Yannis and friends were building a data integration system called FORWARD<\/a> to integrate data of varied shapes and sizes from heterogeneous data stores. The FORWARD view of data was based on a semi-structured data model, and SQL++ was the SQL-based language framework that Yannis developed to classify the query capabilities of the stores. It also served as the integration language for FORWARD\u2019s end users. At some point he approached us with a draft of his SQL++ framework paper, getting our attention by saying nice things about AQL relative to the other JSON query languages (:-)), and we took a look. Pretty quickly we realized that SQL++ was very much like AQL, but with a SQL-based syntax that would make those wistful AQL users much happier. Yannis did a very nice job of extending and generalizing SQL, allowing for a few differences where needed, such as where SQL had made \u201cflat-world\u201d or schema-based assumptions that no longer hold for JSON, and exploiting the generality of the nested data model, like adding richer support for grouping and de-mystifying grouped aggregation.<\/p>\n

We have since \u201cre-skinned\u201d Apache AsterixDB to use SQL++ as the end-user query language for the system. This was actually relatively easy to do since all of the same algebra and physical operators work for both. We recently deprecated AQL altogether as an end-user language.<\/p>\n

Q3. What is N1QL for Analytics?<\/strong><\/p>\n

Mike Carey:\u00a0<\/strong>The Couchbase Analytics service<\/a> is a component of the Couchbase Data Platform<\/a> that allows users to run analytical-sized queries over their Couchbase JSON data<\/a>. N1QL for Analytics<\/a> is the product name for the end-user query language of Couchbase Analytics. It\u2019s a dialect of SQL++, which itself is a language framework; the framework includes a number of choices that a SQL++ implementer gets to pin down about details like data types, missing information, supported functions, and so on. N1QL<\/a> for Analytics could have been called \u201cCouchbase SQL++\u201d, but N1QL (non-1NF query language) is what Couchbase originally called the SQL-inspired query language for its Query service. A decision was made to keep the N1QL brand name, while adding \u201cfor Query\u201d or \u201cfor Analytics\u201d to more specifically identify the target service. Over time both N1QLs will be converging to the same dialect of SQL++. The bottom line is that N1QL for Analytics is the first commercial implementation of SQL++.
\nBy the way, there\u2019s a terrific new book available on Amazon called \u201cSQL++ for SQL Users: A Tutorial.<\/a>\u201d It was written by Don Chamberlin<\/a>, of SQL fame, for folks who want to learn more about SQL++ (from one of the world\u2019s leading query language experts).<\/p>\n

Q4. Is N1QL for Analytics based entirely on the SQL++ framework?<\/strong><\/p>\n

Mike Carey:\u00a0<\/strong>Indeed it is. As I mentioned, N1QL for Analytics is really a dialect of SQL++, having chosen a particular combination of detailed settings that the framework provides options for. In the future it may gain other extensions, e.g., support for window queries, but right now, N1QL for Analytics is based entirely on the SQL++ framework.<\/p>\n

Q5. How is new Couchbase Analytics influenced by the open-source Apache AsterixDB project?<\/strong><\/p>\n

Mike Carey:\u00a0<\/strong>You\u2019ve probably seen those computer ads in magazines that say \u201cIntel Inside,\u201d yes? In this case, the ad would say \u201cApache AsterixDB Inside\u201d… \ud83d\ude42<\/p>\n

Q6. Specifically, did you re-use the Apache AsterixDB query engine? Or else?<\/strong><\/p>\n

Mike Carey:\u00a0<\/strong>Specifically, yes. The Couchbase Data Platform, internally, is based on a software bus that the Data service (the Key\/Value store<\/a> service) broadcasts all data events on — and components like the Index service<\/a>, Full Text service<\/a>, Cross Datacenter Replication service<\/a>, and others are all bus listeners. The Analytics service is a listener as well, and it manages a real-time replica of the KV data in order to make that data immediately available for analysis in a performance-isolated manner. Performance isolation is needed so that analytical queries don\u2019t interfere with the front-end applications. Under the hood, the Analytics service is based on Apache AsterixDB<\/a> — its storage facilities are used to store and manage the data, and its query engine powers the parallel query processing. The developers at Couchbase contribute their work on those components back to the Apache AsterixDB open source, and these days they\u2019re among its most prolific committers. Couchbase Analytics also has some extensions that are only available from Couchbase — including integrated system management, cluster resizing, and a nice integrated query console — but the core plumbing is the same.<\/p>\n

Q7. SQL does not provide an efficient solution for querying JSON or semi-structured data in JSON form. Can you explain how Couchbase Analytics analyzes data in JSON format? What is that capability useful for?<\/strong><\/p>\n

Mike Carey:\u00a0<\/strong>Couchbase Analytics supports a JSON-based \u201ccome as you are\u201d data model rather than requiring data to be normalized and schematized for analysis. We like to say that this gives users \u201cNoETL for NoSQL.\u201d You can perhaps think of it as being a data mart<\/a> for Couchbase application data. The application folks think about their data naturally; if it\u2019s nested, it\u2019s allowed to be nested (e.g., an order object can contain a nested set of line items and a nested shipping address), and if it\u2019s heterogeneous, it\u2019s allowed to be heterogeneous (e.g., an electronic product can have different descriptive data than a clothing product or a furniture product). Couchbase Analytics allows data analysis on data that looks like that — data can \u201ccome as it is\u201d and SQL++ is ready to query it in that \u201cas is\u201d form. You can do all the same analyses that you could do if you first designed a relational schema and wrote a collection of ETL<\/a> scripts to move the data into a parallel SQL DBMS — but without having to do all that. Instead, you can now \u201chave your data and query it too\u201d in its original, natural, front-end JSON structure.<\/p>\n

Q8. Can you please explain the architecture behind Couchbase`s MPP engine for JSON data?<\/strong><\/p>\n

Mike Carey:\u00a0<\/strong>Sure, that\u2019s easy — I can pretty much just refer you to the body of literature on parallel relational data management. (For an overview, see the classic DeWitt and Gray CACM paper on parallel database systems<\/a>.)
\nUnder the hood, the query engine for Couchbase Analytics and Apache AsterixDB looks like a best-practices parallel relational query engine. It uses hash partitioning to scale out horizontally in an MPP fashion, and it using best-practices physical operators (e.g., dynamic hash join, broadcast join, index join, parallel sort, sort-based and hash-based grouped aggregation, …) to deal gracefully with very large volumes of data. The operator set and the optimizer rules have just been extended where needed to accommodate nesting and schema optionality. Data is hash-partitioned on its primary key (the Couchbase key), with optional local secondary indexes on other fields, and queries run in parallel on all nodes in order to support linear speed-up and\/or scale-up.<\/p>\n

Q9. Do you think other database vendors will implement their own version\/dialect of SQL++ ?<\/strong><\/p>\n

Mike Carey:\u00a0<\/strong>Indeed I do. It\u2019s a really nice language, and it makes a ton of sense as the \u201cright\u201d answer to querying the more general data models that one gets when one lets down their relational guard. It\u2019s a whole lot cleaner than the \u201cJSON as a column type\u201d approach to adding JSON support to traditional RDBMSs in my opinion.<\/p>\n

Qx. Anything else you wish to add?<\/strong><\/p>\n

Mike Carey:\u00a0<\/strong>I teach the \u201cIntroduction to Data Management\u201d class at UC Irvine as part of my day job.\u00a0Our class sizes these days are exceeding 400 students per quarter — database systems are clearly not dead in students\u2019 eyes! For the past few years I\u2019ve been spending the last bit of the class on \u201cNoSQL technology\u201d — which to me means \u201cno schema required\u201d — and I\u2019ve used SQL++ for the associated hands-on homework assignment. It\u2019s been great to see how quickly and easily (relatively new!) SQL users can get their heads around the more relaxed data model and the query power of SQL++. Some faculty friends at the University of Washington have done this as well, and their experience there has been similar. I would like to encourage others to do the same! With SQL++, richer data no longer has to mean writing get\/put programs or effectively hand-writing query plans, so it\u2019s a very nice platform for teaching future generations about the emerging NoSQL world and its concepts and benefits.<\/p>\n

———————<\/p>\n

\"MIke\"<\/a><\/p>\n

Michael Carey<\/strong> received his B.S. and M.S. degrees from Carnegie-Mellon University and his Ph.D. from the University of California, Berkeley. He is currently a Bren Professor of Information and Computer Sciences and Distinguished Professor of Computer Science at UC Irvine, where he leads the AsterixDB project, as well as a Consulting Architect at Couchbase, Inc. Before joining UCI in 2008, he worked at BEA Systems for seven years and led the development of their AquaLogic Data Services Platform product for virtual data integration. He also spent a dozen years at the University of Wisconsin-Madison, five years at the IBM Almaden Research Center working on object-relational databases, and a year and a half at e-commerce platform startup Propel Software during the infamous 2000-2001 Internet bubble. He is an ACM Fellow, an IEEE Fellow, a member of the National Academy of Engineering, and a recipient of the ACM SIGMOD E.F. Codd Innovations Award. His current interests center around data-intensive computing and scalable data management (a.k.a. Big Data).<\/em><\/p>\n

Resources<\/strong><\/p>\n

SQL++ For SQL Users: A Tutorial, Don Chamberlin, September 2018<\/a>\u00a0(Free Book 143 pages)<\/p>\n

AsterixDB Overview (Link to .PDF)<\/a><\/p>\n

Related Posts<\/strong><\/p>\n

–\u00a0On Engagement Database. Interview with Ravi Mayuram, ODBMS Industry Watch,\u00a0September 24, 2018<\/a><\/p>\n

Follow us on Twitter: @odbmsorg<\/a><\/strong><\/p>\n

##<\/p>\n","protected":false},"excerpt":{"rendered":"

“N1QL for Analytics is the first commercial implementation of SQL++.” –Mike Carey I have interviewed\u00a0Michael Carey,\u00a0Bren Professor of Information and Computer Sciences and Distinguished Professor of Computer Science at UC Irvine, where he leads the AsterixDB project, as well as a Consulting Architect at Couchbase. We talked about SQL++, the AsterixDB project, and the Couchbase […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[35,1372,66,67,115,1300,914,327,734,1371,412,446,1299,549,1375,1374,1373],"_links":{"self":[{"href":"https:\/\/www.odbms.org\/blog\/wp-json\/wp\/v2\/posts\/4894"}],"collection":[{"href":"https:\/\/www.odbms.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.odbms.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.odbms.org\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.odbms.org\/blog\/wp-json\/wp\/v2\/comments?post=4894"}],"version-history":[{"count":10,"href":"https:\/\/www.odbms.org\/blog\/wp-json\/wp\/v2\/posts\/4894\/revisions"}],"predecessor-version":[{"id":4905,"href":"https:\/\/www.odbms.org\/blog\/wp-json\/wp\/v2\/posts\/4894\/revisions\/4905"}],"wp:attachment":[{"href":"https:\/\/www.odbms.org\/blog\/wp-json\/wp\/v2\/media?parent=4894"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.odbms.org\/blog\/wp-json\/wp\/v2\/categories?post=4894"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.odbms.org\/blog\/wp-json\/wp\/v2\/tags?post=4894"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}