>> Hey, friends. Did you know that you can query data
modeled in JSON documents using SQL?
Tim is here to show updates on how the rich query and
indexing functionality of Azure Cosmos DB's SQL API,
it can give you the best of both worlds;
NoSQL and relational databases,
today on Azure Friday.
[MUSIC].
>> Hey, friends. It's Azure Friday and our good friends
from the Azure Cosmos DB team are here.
Tim, I love that Cosmos DB comes on Azure Friday all the time.
Kirill comes on all time and what's great
is that you're going to tell me what's new
right now in Cosmos DB because you-all add
features and make things better so fast, it's so cool.
What is new right now, querying and indexing?
>> We do, and I think I want to start.
I think Kirill from my team was on
the show a little while ago and he showed
aggregate path improvements in Azure Cosmos DB.
I'm pleased to say that these have been rolled out to
all new accounts in Cosmos DB.
So definitely check these out, they're really awesome.
>> Make sure that you go back on Azure Friday and
look at all the great Azure Cosmos DB content,
there's a lot of stuff going on.
>> Yeah. So today I want to focus on some awesome new query
features that we've released in our SQL API.
The first thing I'm going to talk about are how you can use
composite indexes to optimize ORDER BY queries in Azure Cosmos DB.
So I have a quick demo prepared that I
want to show to talk about that.
>> Let's do it.
>> If you look at this query here,
it's doing a filter on a nutrition dataset that I have.
This is a dataset of many different food items and
has things like tags for the name of the food items,
a categories for them food groups,
and then more information about the nutrition content.
>> Is that group there that says this comma, this comma.
Is that true it has something special with
the commas in the end or is that
a string query or what's happening there in that group?
>> Yeah. So right here in this query here I'm doing
a filter where the food group is equal to soups,
sauces, and gravies, and then I'm doing an order by the timestamp.
Underscored ts is just a system property
in Cosmos DB for the timestamp.
Cool. Now, this collection that I have right here,
just does the default Cosmos DB index,
except it has a range index on every single property.
When I run this query, it's pretty efficient.
It uses about 21 RUs.
But we can optimize this.
If we add on a composite index,
we can have the query.
I'll go and use a composite index for the ORDER BY,
instead of using the range indexes.
>> RUs are important to remember for people who may not be
familiar because it speaks to capacity and what you paid for.
So the lower that is the more you can get done.
>> Absolutely. So this query used about 21 RUs.
If I come here and add on a composite index,
when I scroll down to the bottom of the indexing policy and
just add a composite index on those two properties.
If I "Save" that and then come back to
the original query and just do a small modification on
the query text to tell it to
use a composite index to filter on
the food group and then do an ORDER BY timestamp.
When I run that,
I go all the way down to four RUs.
So this is a lot more cost effective because I
got about five or six X perf improvement.
So for every one time I ran that earlier query,
I could run the same query
five times and the cost would be the same,
and the performance will also be much faster.
>> You did that index addition pretty quickly.
Would you mind going back and
showing the JSON at the bottom there?
>> Yeah. Absolutely. So what I've
done here is added on a, zoom out a bit.
>> Here we go, line 14.
So you've got composite indexes and you
added it within that JSON expression,
that declarative expression of what the indexings are.
That's exactly right. I chose to add it through the portal
here but this is supported in our SDKs as well,
you could add them in through that.
>> Now you say that that just made that
five times effectively more efficient.
>> Exactly right. The crazy thing is this
was only a 64 megabyte dataset.
So this is a tiny tiny scale.
Of course, if you did this on a much larger scale,
the perf improvements would be incredibly significant.
>> Wow. So you could get, with a smart composite index,
you can get an order of magnitude more perf.
>> That's exactly right. So the idea is for most queries,
your range indexes that are
created by default, are going to be awesome.
But if you want even better performance
for queries that have ORDER BY's,
composite indexes are a really great fit.
Okay. Then my question is going to be,
"How do I know to do this?"
I'm going to guess I know the answer is,
"Test it, try it out measure.".
>> Absolutely. So it's all about your query patterns.
We've put this in our indexing docs.
So take a look there. We've highlighted
the key use cases where you want to use composite indexes.
>> Okay. So that's a great demo on how to use composite indexes to
make a lower Ru charge. What's our next demo?
>> Yeah. So the next thing I want to talk about
is sub-queries in Azure Cosmos DB.
Sub-queries make it a lot easier to work with arrays in Cosmos DB.
In Cosmos DB, you can model your data in JSON,
and a great advantage of JSON
is you can have arrays in your data and
this gives you tons of data modeling flexibility.
>> That's definitely something that when the context
of a relational data,
of an entity, an ER diagram,
that's kind of hard to express.
It's effectively like a sub-table with a join in the back.
But within the context of JSON,
it's just really just an array of stuff.
>> Absolutely right. You've got tons of
flexibility with modeling data and it makes
use cases like that really simple to model in Cosmos DB.
>> Cool.
>> So sub-queries make it really simple to
work with arrays when you're querying data.
So I'm going to show off another demo where I'm running
a sub-query that uses the same dataset and basically,
it does a self join between the different arrays in my dataset.
In this nutrition dataset,
I have tags for different names of food items,
nutrients, and servings arrays.
Essentially what this query does is it does
a join between these different arrays
within the same document and then applies a filter logic on them.
Now, when I run the query this way,
it's doing the filters after
I've done the joints between the arrays.
So the RU charge is actually pretty high, it's over 100 RUs.
>> Okay. So that was a big deal.
I mean, you got three self joins and
a fairly sophisticated WHERE clause and by default,
it just does with the best it can. Yes exactly right.
Before we had sub-queries,
this was really the best way to have a filter
like this with properties within arrays.
Now, if I rewrite this query using a sub-query,
I can do a sub-query logic
before I actually enjoined the different arrays.
>> Okay. So you're still doing the three joins but
you're filtering before the join happens.
>> Exactly right.
>> As opposed to after the fact.
>> So if there were, let's say,
10 elements in each array,
in the absence of doing a sub-query,
I'd be doing 10 times 10 times 10 different combinations.
>> Three orders of magnitude.
It's a really big number and this really doesn't scale very well.
When I use a sub-query here,
we're only joining the array elements
that match this sub-query filter.
>> Well, and frankly, it's actually
a cleaner looking query as well.
>> Really easy to understand and when I run it,
RU charge is much lower.
It's only about 16 RUs.
>> So almost 10x.
>> Exactly.
>> So here you are with something that's three orders
of magnitude of complexity.
You just got a three order of magnitude right there and now you
can do this query 10 times for the same price.
>> Exactly right, and another key thing to
emphasize is that these were pretty small arrays,
this would be much more powerful if I had very large arrays
and maybe a filter that only
matched a few different elements within it.
So this simple, this simple in quotes,
thing if someone's watching this and they do Cosmos DB, hopefully,
they're going to just be thrown back in
their seat and say, "Oh my goodness.
I can be a hero at work right now.
I'm just going to go and factor that one query
that everyone hates into using
sub-queries and I'm going to be a hero for at least an hour.".
>> That's exactly right. It's going to be simpler,
latency is going to be lower,
and the costs will ultimately be lower as well.
You can get a promotion with Cosmos DB today my friends.
Okay, cool.
So what other cool sub-queries can we do?
>> Yeah. So one other cool use case of sub-queries that I want to
show off are how you can
rewrite array contains to use a sub-query.
>> Okay.
>> So right here I have a query that I'm going to run that
uses the array contains system function.
What array contains does is
this query is going to return all documents where
the tags array had at least one name that was orange.
So when I run this query,
it's returning documents whose tags array
contains orange essentially as a name. I can rewrite this.
>> How long RUs does that use?
>> Yeah. So the RU charge was pretty low.
It's about, run that again,
it's just about three RUs.
>> Okay, that's not bad.
>> Yeah.
>> Very simple.
>> Very very simply, yes. So when I do
this query using a sub-query and I run it,
I've rewritten the query to use a sub-query.
Pretty similar RU charge, still about three RUs.
But the key is since I've written it with the exist expression,
instead of being restricted to an equality filter here,
I can put in any sub-query I want here.
So it's much more expressive if I wanted to
have let's say a greater than or less than comparison.
This is ideal for that.
>> That's a really great point because
I was thinking about RUs but in this example,
we've got a situation where for the exact same price,
we get a much more expressive and easier to read query and
it opens up a whole new series of filters
that we could go and put within that sub-query.
>> That's exactly right, it's just much more expressive.
>> Costs us nothing different.
>> Exactly. That's cool.
So are these just things that I just have,
did I just wake up this morning and I have these features?
>> Yeah. So we released both of these features at Build.
So they'd been actually available for quite a few months now.
What's new is a lot of the new use cases
for composite indexes in Cosmos DB.
But both of these features are available
in all our SDKs and actually
for sub-queries or you don't
even need to do anything special with indexing.
>> Okay. So I don't have to go and update
my indexes like you did at
the beginning for the sub-queries stuff.
Then for the composite index once I can do that,
I do some measuring and some testing,
and some measuring and some testing.
I'm going to be able to tune the heck out of my databases.
>> Exactly right.
>> Hey, that's pretty sweet.
>> So in addition to all those awesome new query functions
we have in your query functionality indexing functionality,
we also have a ton of additional features
that we're releasing now in Azure Cosmos DB as well.
We're releasing support for GROUP BY,
which was a really awesome way to again,
improve our SQL query functionality within the product and give
you the best of both worlds between
relational and NoSQL databases.
We also definitely want you to explore our Cosmos DB Notebooks,
which I believe you had a show a few weeks ago on that.
So I definitely recommend reviewing
that show to learn more about that.
We're also pleased to announce support for MongoDB's
3.6 Wire Protocol available and Cosmos DB Collections as well.
>> Fantastic. The hits keep on coming with
great new features in Azure Cosmos DB.
I've been learning all about what's new with
querying and indexing today on Azure Friday.
[MUSIC]