[MUSIC]
Hey, I'm Scott Hanselman, and it's Azure Friday.
And here we're talking about DocumentDB stored procedures with
Andrew Liu.
How's it going?
>> Yep, it's great, thanks for having me here.
>> So stored procs, so I think stored procs,
I kinda think old school SQL.
What does that bring to a world of NoSQL databases at a world scale?
>> So this is actually pretty cool.
A few nuances that are different from SQL is we made a bet
on JavaScripts so
you have a full blown programming language to implement these.
>> That's cool.
>> Part of this reason why is because well, when you're dealing
with JSON Data, JSON is JavaScript's Object Notation.
JavaScript is very natural to interact with this data.
>> Yeah.
>> And the other thing that I think is really cool is a lot of NoSQL
databases, they relax transactions a lot.
Right, they give you transactions as a transactions around a single
document, and what we've found is,
this is really hard to program against, right?
And so if you wanna get ACID transactions across
multiple documents,
well this is what we intended stored procedures to give you.
>> Mm-hm.
>> And so I think this is really cool.
>> It makes me feel a little bit like there's NoSQL databases,
without mentioning any names.
>> Mm-hm.
>> That are nice for medium to small applications, but
when you need to get serious, when enterprise work needs to get done.
The reality of transactions happens and those tend to fall down.
>> Yeah, yeah, I can even show you a use case.
So what I have here is I have a sample dataset in my DocumentDB
account.
>> Mm-hm.
>> And what it is it's a product catalog.
>> Okay.
>> And let me load this real quick.
>> Okay.
And so basically what I have done here is I've got a dataset that is
a product catalog containing things like bike frames, helmets, jerseys.
>> Sure.
>> And what I wanna do with this product catalog is I wanna add the
functionality of let's say revealing the items, setting our rating.
>> Right.
>> And in order to do this, what I wanna do is I wanna create
a review document with a comment as well as update let's say,
what is the rating from zero to ten?
How many reviews does this particular product has?
>> Mm-hm.
>> And so what happens is I'm updating one document, and
I am writing a new document.
And what I wanna do is I wanna make this happen transactionally so
that I don't just update the rating without having a review underneath.
>> Right. >> Or create a dangling
review comment that doesn't actually effect the product.
>> And it might sound like a small thing, but
we've all had that experience.
We've gone to a website and we've seen, well,
there's three comments here and the count says five.
What's going on, inconsistency, it's bad for a number of reasons.
>> Yep, and so let me jump into the stored procedure for doing this.
So here I'm in the Azure portal.
I'm going to our Script Explorer.
>> Mm-hm.
>> And I've written a quick stored procedure for applying this review.
>> Okay.
>> Now, a couple things to really know about stored procedures is
number one,
because we've optimized around the use case for transactions,
these work best when you have some kind of write operation.
If you have strictly let's say a sequence of reads-
>> Mm-hm.
>> This is actually best implemented on the client SDKs.
>> Hm.
>> Now within the stored procedure,
what we have here is I've taken a set of parameters.
So what product I wanna update, who the reviewer is,
what kind of rating and what the reviewer's comment is, right?
>> Okay.
>> I do some validation and basically I'm gonna do two things.
I'm gonna create the review document, and
I'm gonna update the product document.
Within this, these helper functions, it's pretty simple, right?
>> Are those helper functions also stored procedures or they literally
just helper functions within the larger name stored procedure?
>> There was just JavaScript functions that I have embedded into
this stored procedure.
>> Okay. >> And this is actually a really
cool thing about also using JavaScript is,
the Java ecosystem is humongous.
>> Yeah.
>> So the number of JavaScript libraries out there that you can
embed into a stored procedure, let's say if you wanna bring in a popular
JavaScript machine learning library,
you can actually then embed that into the stored procedure.
Cuz we have a full blown JavaScript runtime running
behind the scenes, so- >> Then it's then enlisted into
the transaction and either worked or
it didn't and you'll take care of stuff.
>> That's correct. >> That's great.
So I wanna bring Moment.js or
whatever makes me happy in there to do work where it can do-
>> Yeah, yeah.
And so the way our API works is
we have a bunch of crowd and query operations.
These methods create a document, just createDocument,
pretty straightforward.
But one thing to know about the JavaScript Runtimes we host,
is DocumentDB is a multi-tenant service.
We do need to govern the resources to make sure one tenant doesn't
cause problems for other tenants.
>> Right.
>> And so what we do is we actually bound the execution of the stored
procedures to a set number of CPU and time.
And in order to do,
let's say long running transactions, if you want to go beyond,
we gave you plenty of budget for most day-to-day operations.
>> Mm-hm. >> But
if you wanna do something very, very sizeable,
what we do is we ask you to build a continuation model.
>> Mm-hm.
>> An example of a continuation model is let's say I want to delete
a very, very large set of documents transactionally.
>> Right. >> What I might do here is I
will delete a set,
I will then return a Boolean back to the client side application.
And at the client side application sees that that Boolean is true and
knows hey, that's a signal that I didn't finish,
I'm going to run this again.
Now if the Boolean returns false,
that means there's actually more time and CPU budgeted for
this execution so you can continue running.
>> So you're chunking those big operations?
Is that what you do?
You're chunking them? >> Yeah.
>> Let's say I have a million to do, and
we'll do them 10,000 at a time or 100,000 at a time.
>> That's correct.
>> And if the whole thing failed on the last page,
would it roll back everything?
>> No, so that is something to build into your continuation model.
And there are techniques for doing this but basically each run of
the stored procedure, that gets applied transactionally.
>> Okay. >> And
just to kinda make this as a signal for the user to know when did
I need to kick out and return a message to the clients.
>> Mm-hm.
>> Each of these crowd operations return this true/false Boolean as
accepted.
And what I do here is if a crowd operation was not accepted
then if I throw an exception, this is the signal to roll back.
Whenever an exception is thrown,
all of the logic within the stored procedures is rolled back.
>> Mm-hm.
>> And this can be really nice for doing some validation.
So an example is if I've already created that review document in
the function above, let's say now I wanna update the product document
and I couldn't find it.
Rather than having this dangling review document, I can now just roll
this back and say, hey, ignore everything I've done in here, I'm
gonna fail this, I wanna roll back because of this validation check.
>> And to make sure I get the language right, and
people understand as well.
When we say roll back, it's as if it never happened.
These aren't compensating transactions, right?
>> Right, right.
>> If I'm adding something like if I'm doing finance,
if I'm transferring money- >> Mm-hm.
>> And that fails, it's like it never happened at all.
It's not that it failed and
then I transferred it back with a compensating transaction.
>> That's correct.
So at the beginning of the execution in the stored procedure,
what we do is we actually take a snapshot of your database.
>> Mm.
>> And then at the end of, when it completes,
let's say you don't wanna fail it or roll it back,
you don't throw an exception- >> Mm-hm.
>> What we do is we apply all of those mutations all at once at
the end of the execution.
So that's the equivalent to a commit.
>> Very cool.
>> Yeah.
Now, some things to also note about this in terms of just best
practices.
I have seen a lot of folks if you're coming from let's say
a Java background like I came from or .NET background.
You might not be used to the control flow of JavaScript, and
JavaScript, I mean really embraces async operations, right?
And so one of the main things to point out, I think
if you're new to JavaScript and new to writing stored procedures,
is make sure that when you do the control flow for
the script, make sure that you line it up in call backs.
Don't fire a bunch of async operations all at once.
>> Mm-hm.
If you're bounding your CPU, then those async operations will
just fly away and then eventually you'll just slap them down and
you won't know whether they happened or not?
>> Right. So it'd be very hard to control
the IO and understand what's going on.
So in this case if you wanna perform this IO sequentially,
you'll want to make sure that you follow the call back convention.
>> Mm-hm.
>> As a matter of chance,
since this is implemented actually on ChakraCore.
>> ChakraCore, the open source JavaScript library that we use.
>> Yes.
The JavaScript runtime that we use.
We have full ES6 compatibility so should you wanna use premises or
something else to alter the control for
any of the other ES6 functionality.
Which I think is really awesome in the JavaScript world.
We do have that available, and one of the other cool things
about this is our JavaScript runtime isn't just for stored procedures,
we gave you transaction semantics around triggers.
So if you wanna apply let's say a JavaScript script attached to
a crowd operation, and this might be updating let's say a metadata
document and you want to have that happen transactionally.
>> Mm-hm.
>> Or if you wanna do user defined functions and
augment our query language, we also let you use the JS Runtime for that.
So, an example is in the product catalog I had a bunch of documents
which had a weight.
Those weights were in grams.
And let's say I wanna present back to the user in a product
listing page.
>> Mm-hm.
>> I want to show them this in pounds.
I can do this conversion and then embed this into a SQL query.
>> And now anyone who's in that collection can use that
in their stored procedures and build on it, and whatever.
>> Yes. >> So your developers could build
a whole library of user defined functions that they could all then
exploit within their stored procedures.
>> Yes.
So that's all getting embedded in the SQL query.
And then as part of the stored procedure,
you can run these SQL queries, and so you can include the UDF if
you choose to, as part of the select clause or the where clause, yeah.
>> This seems such a nice compromise.
And I don't mean compromise in an everyone loses way, but
literally everyone wins, cuz you've got enterprise developers with
a familiarity for things like stored procs, UDFs, SQL.
>> Mm-hm.
>> And you've got the new generation of people that wanna push
JSON around, they want it on structured database, and
they want world scale and they want it for free.
>> Mm.
>> You're giving, for lack of a better world,
both the young people and the older people,
what they want to make applications that are gonna run at huge scale.
>> Yes.
And speaking of world scale,
I mean one of the things that DocumentDB has really cool is all of
the partitioning is built into the server side itself.
All of the partitions are transparent, right?
>> Mm-hm.
>> All you need to define is a partition key.
One thing to note about stored procedures is we do need to
have some scope on where that transaction runs.
And so we scope these two with the partition key.
So when you make a request from the SDK,
what you wanna do is put into the request options what partition key
you wanna run this transaction on.
>> Mm-hm. >> And that is the scope for
the transaction.
Something that then call out from that is as a user, when you're
thinking about how you wanna partition your data to really kinda
leverage this scale out capability- >> Mm-hm.
>> Is your choice of partition key should reflect what kinda
transactions you wanna do.
So in the context of, let's say, a product catalog,
I know that I can scale out
perhaps by choosing a partition on maybe the product category.
I know that my transaction scope will fit within
that product category.
Let's say I'm just reviewing a single product and
creating review documents, that'll fit well.
But this is a design consideration you'll wanna think about up front.
So I guess I'll walk you through this stored procedure.
Run it and kinda just call some things out.
>> Yeah, well let's see it run as we end here.
>> So I basically created two functions.
One to create a doc, one to update a doc.
>> Mm-hm. >> Fairly straightforward.
>> You're using different objects like collection of those objects
just intrinsic and just available always?
>> Say that one more time?
>> For example, scroll down this page.
>> Yes. >> You said,
collection.createDocument.
Where is collection coming from?
>> Yes.
So all the crowd operations come from a context object.
>> There it is.
>> And so the way the API is, is you can get the current collection and
this will reveal all of the crowd and query operators.
>> Mm-hm. >> You can also get the response and
this is where you can change the response and say hey, this is what I
want returned at the end of the execution of this stored proc.
>> Cool and we can run it and test it right here?
>> Yes, so let me grab a product real quick from our product catalog
and let's add a review.
>> Yeah.
>> So let's say I wanna review this helmet.
And the product number HL-U509,
this is what my stored procedure is expecting in the body.
>> All right.
>> Or in the parameter.
I go to reviewProduct.
I will go down here to- >> Little
challenging on a small screen.
>> Yes.
>> Looks fine on a big screen.
>> Looks good on a higher resolution.
[LAUGH] >> Yep, all right, so
there's your inputs.
>> So our inputs, what we're expecting here is a product number.
The reviewer, so we're gonna say Scott really loves this helmet.
He's gonna give it a 10 out of 10, and he's saying this thing rocks.
Oops.
Hit Execute.
And assuming that I got all the inputs right,
what we'll see here below.
There is the response.
So what we've done is we've created a review object,
as well as we've updated that product.
>> Yeah.
>> And so in the product itself, what you'll see here-
>> There it is.
>> As part of it is the new score.
So we've added a review.
We calculated the new score.
And this is kind of the beauty of JavaScript, right?
It's a full blown programming language.
You can have fairly complex logic within this.
>> Very cool.
>> Yeah. >> Well,
we're learning all about stored procedures at world scale.
With Azure DocumentDB, here on Azure Friday.
[MUSIC]