Querying NoSQL with SQL: HAVING Your JSON Cake and SELECTing it too

Editor's Notes

• #3: This is a screenshot from the documentation of a popular document database It's a query to find inventory items that meet a certain criteria
• #4: If you're like most developers, though, you probably already know how to write SQL And so wouldn't it be nice if we could get all the benefits of NoSQL databases While still being able to write declarative SQL queries on it?s
• #6: My employer Couchbase is sponsoring, so please come by the booth later Say hi, ask questions, get some stickers We also have a live demo, which isn't difficult at all, but after this session It should be even easier
• #9: What’s also interesting is that we’re seeing the use of NoSQL expand inside many of these companies. Orbitz, the online travel company, is a great example – they started using Couchbase to store their hotel rate data, and now they use Couchbase in many other ways. Same with ebay, they recently presented at the Couchbase conference with a chart tracking how many instances of various nosql databases are in use, and we see growth in Cassandra, mongo, and couchbase has actually surpassed them within ebay Marriott is using Couchbase, so you may be interacting with it right now
• #11: Let’s talk about what NoSQL is, first. NoSQL generally refers to databases which lack SQL or don’t use a relational model Once the SQL language, transaction became optional, flurry of databases were created using distinct approaches for common use-cases. KEY-Value simply provided quick access to data for a given KEY. Wide Column databases can store large number of arbitrary columns in each row Graph databases store data and relationships as first class concepts Document databases aggregate data into a hierarchical structure. With JSON is a means to the end. Document databases provide flexible schema,built-in data types, rich structure, implicit relationships using JSON.
• #12: When we look at document databases, they originally came with a Minimal set of APIs and features But as they continue to mature, we’re seeing more features being added And generally I’m seeing a convergent trend between SQL and NoSQL But anyway, this set of minimal features, lacking a SQL language and tables gives us the buzzword “nosql”
• #13: Elastic scaling Size your cluster for today Scale out on demand Cost effective scaling Commodity hardware On premise or on cloud Scale OUT instead of Scale UP [example: changing the channel to a soccer game or Game of Thrones, everyone makes the same API request in the same 5 minutes] [example: TV show lets watchers vote during some period of the week, so you can scale up during that period of time] [example: black Friday]
• #14: Elastic scaling Size your cluster for today Scale out on demand Cost effective scaling Commodity hardware On premise or on cloud Scale OUT instead of Scale UP [example: changing the channel to a soccer game or Game of Thrones, everyone makes the same API request in the same 5 minutes] [example: TV show lets watchers vote during some period of the week, so you can scale up during that period of time] [example: black Friday]
• #15: Schema flexibility Easier management of change in the business requirements Easier management of change in the structure of the data Sometimes you're pulling together data, integrating from different sources (e.g. ELT) and that flexibility helps Document database means that you have no rigid schema. You can do whatever the heck you want. That being said, you SHOULDN’T. You should still have discipline about your data.
• #16: NoSQL systems are optimized for specific access patterns Low response time for web & mobile user experience Millisecond latency Consistently high throughput to handle growth [perf measures can be subjective – talk about architecture, integrated cache, maybe mention MDS too]
• #17: If one tap for coke goes out, there’s others available. If one db node goes down, the others will compensate. This is related to scaling Built-in replication and fail-over No application downtime when hardware fails Online maintenance & upgrade No application downtime
• #18: Let’s talk about data modeling a bit, because storing data in JSON Is different that storing in tables.
• #19: So I want to compare the approaches over 4 key areas. I’m going to fill in this table, traditional SQL on the left and JSON on the right
• #20: Let’s look at modeling Customer data. This is an example of what a customer might look like There is a rich structure: attributes, potentially sub-attributes (first name and last name) Relationships: to other data (other customers, to products perhaps) Value evolution: Maybe we’d start with one purchase, add more as Helen makes more purchases Structure evolution: Maybe we start will billing information being properties of Helen, then evolve later to be multiple billing options
• #21: Let’s look at modeling Customer data. This is an example of what a customer might look like There is a rich structure: attributes, potentially sub-attributes (first name and last name) Relationships: to other data (other customers, to products perhaps) Value evolution: Maybe we’d start with one purchase, add more as Helen makes more purchases Structure evolution: Maybe we start will billing information being properties of Helen, then evolve later to be multiple billing options
• #22: Let’s see how to represent customer data in JSON. The primary (CustomerID) becomes the DocumentKey Column name-Column value becomes KEY-VALUE pair.
• #23: We aren’t normal form anymore Rich Structure & Relationships Billing information is stored as a sub-document There could be more than a single credit card. So, use an array.
• #24: Value evolution Simply add additional array element or update a value.
• #25: Structure evolution Simply add new key-value pairs No downtime to add new KV pairs Applications can validate data Structure evolution over time. Relations via Reference
• #26: I'm going to skip ahead a bit in the interest of time So, finally, you have a JSON document that represents a CUSTOMER. In a single JSON document, relationship between the data is implicit by use of sub-structures and arrays and arrays of sub-structures.
• #27: So, finally, you have a JSON document that represents a CUSTOMER. In a single JSON document, relationship between the data is implicit by use of sub-structures and arrays and arrays of sub-structures.
• #28: So I want to compare the approaches over 4 key areas. I’m going to fill in this table, traditional SQL on the left and JSON on the right
• #30: I think there's a piece that's missing in many NoSQL databases: robust query capabilities
• #31: Key value is super simple Extremely fast, with couchbase it operates at memory speeds But you have to know the keys. If you don't, you'll need to comport the data some other way to accomplish this
• #32: Map reduce is where you write a couple of pure functions These functions are run against every piece of data to compute results ahead of time Kinda like a materialized view Great for aggregations, better than K/V when you don't know the keys Great performance – they can run in parallel Somewhat limited. Adhoc is difficult Unions/joins are typically not possible Not as natural as SQL
• #33: Many document databases have some other type of querying syntax/language available Here's an example in Mongodb Again, these are somewhat limited when compared to SQL many devs abstract this behind ODMs like mongoose And mongo, for instance, keeps improving the language Beyond the raw technical story: Ultimately JavaScript to query data doesn't feel as natural as SQL to me And for many developers who have been using SQL their whole career, this could be a tough transition, a tough sell
• #34: Result: 80/20 rule applies. You can use these operations to get maybe 80% data interaction with no trouble, but that last 20% is tough So, querying is inadequate This is how you might have to do it WITHOUT a query language like SQL Complex code and logic Inefficient processing on client side I can do this with k/v calls, map/reduce calls sure, but it’s more complex, and maybe inefficient Less complex, more natural to do this in SQL, more readable
• #35: SQL is meant for normalized data so what do we do when we have denormalized data, like JSON documents?
• #36: So let’s look at how SQL for JSON should work I’m going to use a different model as an example: Travel data. Specifically airlines and routes. I want users to be able to find flights between given destinations On a specific day, etc The data I’m looking at is actually a sample data set that ships with Couchbase Called the “travel sample” bucket It has airlines, routes, airports, and landmarks, but we’re only going to look at airlines and routes today.
• #37: Here is a sample route document Notice it has a source and destination It also has a “type”, which isn't a required field, magic field or reserved word, it could just as easily be "_type" or something else Notice it has an airlineid field, which is kinda like a foreign key It has a schedule field, which is an array. In relational, this would typically Be 3 rows in another table, but it’s denormalized into a single document
• #38: Here’s an airline sample document It’s flat data, but it contains the name of the airline It also has a “type” field
• #39: The only part of this sql query that might look odd to you is line 4 Travel-sample is not a table, it's a collection of JSON documents (couchbase calls this a 'bucket') It contains route documents, but also airline, landmark, and airport So I'm filtering to just route documents with line 5 We've got airlineid, but not the name of the airline How can we get the airline name?
• #40: Joining two fields with an "ON" we refer to as an "ANSI join" and is a relatively new feature of N1QL Previously any join had to have the document key on one side of the join Now it can be anything (with some indexing caveats) I'm showing an early build of Couchbase Server 5.next here. CosmosDb does not yet have an inter-document join.
• #41: Let's add some aggregation COUNT GROUP BY HAVING And ORDER BY I want to find which airlines have more than 2000 routes
• #42: This query is going to give me a list of all the airport codes I'm pulling from destination in the top one And source in the bottom one (I don't expect there to be any airports that are ONLY destinations or sources, but there might be, and this UNION has that covered)
• #43: Some screenshot gymnastics here to get this to fit on a slide This is a plain select, no join but one of the fields being selected is an array
• #44: UNNEST is an “intra-document” join Like a CROSS JOIN between a document and an array it contains It will join the array and decompose the JSON hierarchy There's also a NEST, which is another type of inter-document JOIN but it encapsulates the JSON It's "aka JOIN" because this is what a CosmosDb calls it
• #45: SQL language operates on set of relations (table) and then projects another relation (table) Resultset, needs to be mapped to something in application (could be complex, or maybe not), and then you have to map that to complex objects, and then to json Not as natural
• #46: With SQL for JSON, you query JSON documents. There will likely be fewer types of documents because of denormalization And "aggregate oriented" data (Fowler) But you still might want to join. The end result is also JSON, but it's not flattened, so there is no need for an OR/M Couchbase'a implementation is called N1QL CosmosDb just calls it SQL Also notice that the arrows went away when I switched to CosmosDb
• #48: DocumentDb is Microsoft’s document database azure offering It has a SQL syntax available, and it’s pretty good (side note: It has JOIN in its syntax but it’s an intra-document join so it’s more like UNNEST)
• #49: N1QL is a superset of SQL The extra "stuff" (see next page)
• #51: Skip the demo! Come to the booth!
• #52: Insert, update, delete, merge all supported
• #53: Indexes are just as important if not more so With tables, if it's small enough, you can usually get away with not creating an index since a table scan won't take that long Since buckets are heterogeneous collections, you want to avoid scanning the entire bucket So that requires careful indexing Couchbase requires you to opt-in to primary scan (equivalent of table scan) Point out CREATE INDEX and mention DROP INDEX
• #54: Use the EXPLAIN keyword to output the query plan This can show you whether you're hitting the correct index Help you diagnose performance problems
• #55: This is currently an Enterprise only feature
• #56: Instead of cobbling together k/v, map reduce, some other declarative syntax Which might be inefficient, which might be tough to train, read, understand ,etc We could just write 6 lines of SQL
• #57: Reference/summary slide
• #58: This chart is not exhaustive, but I think it identifies key points of difference Sprocs on CosmosDb are in Javascript, not SQL The response to intra-document joins on uservoice is "use gremlin, not SQL, we aren't going to add joins" Things like GROUP BY, DISTINCT, LIKE are probably coming to cosmosdb
• #59: UC San Diego: SQL++ Couchbase: N1QL SQL++ is a paper that UC San Diego produced N1QL is pretty much the first implementation of SQL++, validating their paper in a real database
• #61: JSON support in SQL IBM Informix Oracle PostgreSQL MySQL All the sql vendors are introducing JSON features
• #62: SQL Server 2016 introduced JSON_VALUE and some other JSON functions It’s not quite a SQL for JSON implementation, but it’s a step in that direction Here’s a table with two columns, one is an nvarchar with a JSON string JSON_VALUE can be applied to it to parse the JSON and get specific values
• #64: All of these tools are using JDBC/ODBC driver provided by Simba to connect to Couchbase ODBC opens the door to a ton of integrations, these are just a few Maybe mention Crystal Reports too Cosmosdb also has an odbc connector from microsoft that you can download and install
• #65: Couchbase can integrate directly with big data tools using DCP/XDCR instead of a query These don't rely on ODBC but are built specifically to connect to Couchbase For mongo to couchbase, we have ottoman, which has a very close API to mongoose
• #66: No ODBC driver afaik (yet) But there are some interesting APIs being provided by CosmosDb and now by CosmosDb DocumentDb provides a mongo API, so you can switch seamlessly With CosmosDb, it's multi-model, so it can work as a graph database using the Gremlin language It can work as a Table database using the Azure Table Storage API So there may be some integrations out there that use those It's important to remember that compared to Couchbase and many other document databases, DocumentDb is very new, so features will come in time. Similarly, compared to Oracle and SQL Server, document databases in general are relatively new, so there are new features And innovations happening every year
• #68: All I ask is that you give Couchbase a chance Free download You can also take it for a free test drive on the major cloud providers Also, this is something new for me this year, please go to this URL to enter to win a $100 gift card. It is literally a 1 question survey and it helps me out a lot.
• #69: This is my family My enormous head barely fits in the picture
• #71: Not yet. We've been talking about it at least as long as I've been with Couchbase. It's partly a technical problem, may need additional features for multi-tenant. It's partly (mostly) a business problem. Would this be worth it? Couchbase IS in the Azure and AWS marketplaces, and there are some wizards to make config easy, but it runs on your VMs.
• #72: Memory first: integrated cache, you don't need to put redis on top of couchbase Master-master: easier scaling, better scaling Auto-sharding: we call vBuckets, you don't have to come up with a sharding scheme, it's done by crc32 N1QL: SQL, mongo has a more limited query language and it's not SQL-like Full Text Search: Using the bleve search engine, language aware FTS capabilities built in Mobile & sync: Mongo has nothing like the offline-first and sync capabilities couchbase offers Mongo DOES have a DbaaS cloud provider
• #73: Everything I've shown you today is available in Community edition The only N1QL feature I can think of not in Community is INFER and Query Plan Visualizer The Enterprise features you probably don't need unless you are Enterprise developer.