nosql's biggest lie: sql never went away - martin esmann

NoSQL's biggest lie: SQL never went away!Martin EsmannDeveloper Advocate, Couchbase

Let’s do something unspectacular

A query

SELECT * FROM `travel-sample` WHERE type = ‘airline’ AND country = ‘United Kingdom’;

A query

Did I say unspectacular?

That was JSON

So, what did we do?

We put the SQL back into NoSQL

But first, let’s take a step back

NoSQL?

Polyglot persistence is “…using multiple data

storage technologies, chosen based upon the

way data is being used by individual

applications. Why store binary images in

relational database, when there are better

storage systems?”

Martin Fowler and Pramod Sadalage

Types of NoSQL database

By Schumi4ever (Own work) [CC BY-‐SA 3.0 (http://creativecommons.org/licenses/by-‐sa/3.0)], via Wikimedia Commons

Key value

Email: martin@couchbase.comemail:email: {

“personal”: “martin.esmann@hotmail.com”,“work”: “martin@couchbase.com”

London

matthew@couchbase.comjames@couchbase.comlaura@couchbase.comtom@couchbase.comdavid@couchbase.comgreg@couchbase.com

Document

Developer Advocacy

matthew@couchbase.comjames@couchbase.comlaura@couchbase.comlaurent@couchbase.commartin@couchbase.commatt@couchbase.comnic@couchbase.comwill@couchbase.com

martin@couchbase.com:

{"city": ”Copenhagen","glasses": false,"team": "Developer Advocacy","music": ”Electronic!"

Document

London

matthew@couchbase.comjames@couchbase.comlaura@couchbase.com

Developer Advocacy

London and Developer Advocacy

And the others

Context is all

There's always a trade-‐off

• Offload from some other data store (i.e. caching)• Computation offload• Speed• Scalability• Availability• Flexibility in what you store• Query flexibility

Where Couchbase comes in

Couchbase Server 4.0

High availability cache

Key-‐value store

Document database N1QL SQL-‐like query

for JSON

N1QL: SQL for JSON

A user profile as JSON

How do we query that?

Querying the JSON profile

• Look-‐up documents: i.e. manual secondary indexing (2i)• Couchbase views: i.e. automated secondary indexing (2i)• N1QL

Manual 2i

Automatic 2i: views

SELECT

SELECT ...

SELECT

SELECT 1 + 1;

{ "requestID": "3ccebac7-‐341a-‐4c31-‐a2c5-‐b46aaed54356", "signature": {

"$1": "number" }, "results": [

{ "$1": 2 }

], "status": "success", "metrics": {

"elapsedTime": "31.826219ms", "executionTime": "29.800616ms", "resultCount": 1, "resultSize": 31

SELECT COUNT

SELECT COUNT(*) FROM `default`WHERE office = "London";

{"requestID": "6e733000-ac83-44ba-95a7-9b012e9c553d","signature": {

"$1": "number"},"results": [

{"$1": 6

}],"status": "success","metrics": {

"elapsedTime": "18.603124ms","executionTime": "18.327696ms","resultCount": 1,"resultSize": 31

SELECT email FROM `default`WHERE office = "London";

SELECT

SELECT email FROM `default`WHERE office = "London"AND team = "Developer Advocacy";

SELECT

SELECT email FROM `default`WHERE office = "London"AND team != "Developer Advocacy";

SELECT

But this is JSON

SELECT conferences[0].nameAS event_name FROM `default`;

ARRAY ELEMENTS

SELECT DISTINCT conferences[0].nameAS event_name FROM `default`;

DISTINCT ARRAY ELEMENTS

SELECT DISTINCT conferences[0].nameAS event_name FROM `default`WHERE conferencesIS NOT MISSING;

REMOVE MISSING ITEMS

SELECT email AS person,conferences[0].name AS event FROM `default`WHERE ANY event in conferences SATISFIES event.name = "Droidcon Sweden" END;

WHO IS GOING TO DROIDCON SWEDEN?

What's going on underneath?

EXPLAIN SELECT email AS person,conferences[0].name AS event FROM `default`WHERE ANY event in conferences SATISFIES event.name = "Droidcon Sweden" END;

EXPLAIN

Is N1QL read-‐only?

Updating and deleting

• DELETE: provide the key to delete the document• INSERT: provide a key and some JSON to create a new document• UPSERT: as INSERT but will overwrite existing docs• UPDATE: change individual values inside existing docs

UPDATE

DELETE

A larger data-‐set: travel-‐sample

TRAVEL SAMPLE DATA

travel-‐sample

CREATE PRIMARY INDEX

CREATE PRIMARY INDEXON `travel-sample`USING GSI;

SELECT

SELECT * FROM `travel-sample`WHERE type = "airline";

SELECT

SELECT * FROM `travel-sample`WHERE type = "airline"AND country = "United States";

SELECT

Indexes

CREATE INDEX

CREATE INDEX airline ON `travel-sample`(type)WHERE type = "airline"USING GSI;

• Retrieve data from two documents in a single SELECT• Join within a keyspace/bucket• Join across keyspaces/buckets

A SIMPLE JOIN

SELECT * FROM `travel-sample` r JOIN `travel-sample` a ON KEYS r.airlineidWHERE r.sourceairport="LHR" AND r.destinationairport = "SFO";

WHO FLIES LHR-‐>SFO?

SELECT DISTINCT a.name FROM `travel-sample` r JOIN `travel-sample` a ON KEYS r.airlineidWHERE r.sourceairport="LHR" AND r.destinationairport = "SFO";

UNNEST

• Breaks out nested JSON from the results

SOMETHING USEFUL

SELECT a.name, s.flight, s.utc, r.sourceairport, r.destinationairport, r.equipmentFROM `travel-sample` r UNNEST r.schedule sJOIN `travel-sample` a ON KEYS r.airlineidWHERE r.sourceairport="LHR" AND r.destinationairport = "SFO"AND s.day=1ORDER BY s.utc;

N1QL vs View’s

N1QL and views

N1QL Views

Ad-hoc querying Predictable queries

JSON in and JSON out Number crunching

Large growth clusters Multi-dimensional/geospatialqueries

Multi-‐dimensional scaling

Scaling out

Horizontal scaling▪ Partitions a dataset onto one or more homogenous nodes▪ Each node runs the same mixed workloads▪ Re-‐partition dataset with additional hardware capacity

What is Multi-‐Dimensional Scalability? MDS is the architecture that enables independent scaling of data, query, and indexing workloads.

Isolated Service for minimized interference▪ Independent “zones” for query, index, and data services

Minimize indexing and query overhead on core key-‐value operations.

Independent scalability for the best computational capacity per service

Heavier indexing (index more fields): scale up index service nodes.

More RAM for query processing:scale up query service nodes.

Geospatial querying

Geospatial views

• Experimental in 3.0, now GA in 4.0• Performance and stability improvements• GeoJSON output• Bounding-‐box and range queries on

multiple dimensions

What else is in Couchbase Server 4.0?

Other new things in Couchbase Server 4.0

• ForestDB: GSIs are stored with a new storage engine• Filtered XDCR: more efficient cross-‐data centre replication• Security: LDAP, admin auditing

Developers?SDK support for N1QL!

.NET SDK: Connection

// Option 1: Create Cluster and open bucket var cluster = new Cluster(config);var bucket = cluster.OpenBucket(bucketName);

// Option 2: ClusterHelper and open bucket (Singleton, thread safe bucket instanceClusterHelper.Initialize(config);var cluster = ClusterHelper.Get();var bucket = ClusterHelper.GetBucket(bucketName);

// Close connectioncluster.CloseBucket(bucket);cluster.Dispose();

.NET SDK: Client Configuration

// Cluster Configurationvar config = new ClientConfiguration

{Servers = new List<Uri> { new Uri("http://10.211.55.2:8091/pools"),},UseSsl = false,BucketConfigs = new Dictionary<string, BucketConfiguration>

"travel-‐sample", new BucketConfiguration{

BucketName = "travel-‐sample",UseSsl = false,Password = "",PoolConfiguration = new PoolConfiguration

{MaxSize = 10,MinSize = 5

.NET SDK: Basic Operations

// Create document (dynamic)var doc = new Document<dynamic>{

Id = "doc1",Content = new{

Id = "doc1",Title = "My Document",Type = "basic",Pages = 3

// update or create documentvar upsertResult = await bucket.UpsertAsync<dynamic>(doc);

// Get documentvar getResult = bucket.GetDocument<dynamic>("key1");

// Delete documentvar deleteResult = await bucket.RemoveAsync<dynamic>(doc);

// Check if doc/key exsistsvar exsists = await bucket.ExistsAsync("key1");

.NET SDK: N1QL Query (raw)

// Create Query Request. Raw API requestvar query = QueryRequest.Create("SELECT COUNT(*) FROM `travel-‐sample` WHERE type = 'airline'");

// Execute Query. var response = await bucket.QueryAsync<dynamic>(query);

// Convert result to string for easy console print out.var result = JsonConvert.SerializeObject(response, Formatting.Indented);

.NET SDK: N1QL Query (Linq2Couchbase)

// Lambda syntax

var result = ClusterHelper.GetBucket("beer-‐sample").Queryable<Beer>().Where(a => a.Type == "beer").Select(a => a).Join(

ClusterHelper.GetBucket("beer-‐sample").Queryable<Brewery>().Where(airline => airline.Type == "brewery"),innerKey => innerKey.BreweryId,outerKey => N1Ql.Key( outerKey), (inner,outer) => new { Inner = inner, Outer = outer})

.Take(1)

.ToList();

.NET SDK: N1QL Query (Linq2Couchbase)

// Query syntax

var query = from beer in QueryFactory.Queryable<Beer>(mockBucket.Object)join breweryGroup in QueryFactory.Queryable<Brewery>(mockBucket.Object)on beer.BreweryId equals N1Ql.Key(breweryGroup) into bgfrom brewery in bg.DefaultIfEmpty()select new { beer.Name, beer.Abv, BreweryName = brewery.Name };

https://github.com/couchbaselabs/N1QL-‐Intro-‐dotNET-‐Supplement-‐Demo-‐code

Next Steps

Couchbase Developer Portal

developer.couchbase.com

Forums

http://forums.couchbase.com

Thanks for listening!

nosql's biggest lie: sql never went away - martin esmann

Data & Analytics

matrix lie groups - university of toronto · 2014-01-25 ·...

lie groups, lie algebras, and their...

the church's biggest lie - the missing link in christianity

the biggest lie ever told by david prentice, m.ed., m.a.s.t....

the biggest lie ever told by david prentice, m.ed., m.a.s.t....

innovative drug development approaches · companies...

the biggest lie in marketing - amazon web services · lie...

kpmg in poland report, entitled: business partnership...

lie groups and lie algebra

virtual voodoo -- the lie detector lie

transverse lie and unstable lie

woeih - 001 - truth, deception, solipsism (the biggest lie),...

lie algebroids are curved lie algebras

continuous groups - lunds...

112/ñio 48 588mm lie —l) i-lie (fit)...112/ñio 48 588mm...

2013 beya seminar: men lie women lie

lie groups and lie algebras in robotics

chapter 7 lie groups, lie algebras and the exponential...

to lie or not to lie

irstaxfix ripoff scam report is a lie, lie, lie. this works!