>> Learn how to call a rest endpoint
from Azure SQL database super easily,
including some new announcements.
This week on Data Exposed.
[MUSIC].
Hi, I'm Anna Hoffman and welcome to this episode of Data Exposed.
Today we're going to be talking about
an exciting new topic with Davide from the SQL database team.
Davide, thanks so much for joining us today.
>> Hey, Anna. Good morning girl. Good evening.
Good afternoon. Thanks all for having me again.
>> It's always great to have you on the show,
you're always showing us the latest and
greatest cool things for developers.
Today is no exception,
we're going to be talking about calling
a rest service from Azure SQL database.
Before we get into it,
can you tell us why would you want to do that?
>> To a $100 million question.
Now it's that's a great question and very common
so I'm glad that you asked me.
Well, the point is that today
integrating with other services,
especially naturally super-important.
The ability to take the data that you already
have and send it to an Azure function or call
an Azure Cognitive Services for further processing to apply maybe
some machine learning techniques or send it to
Power BI by pushing it instead of pulling it,
just make everything easier.
Now, is this something you should do every time,
every day of the year?
Of course not but it's a tool that
you have in your tool set so that when you need,
for example, push some data,
let's say to update a simple internal website that
shows how many orders coming in or how many open ticket there are.
You can just use Azure SQL and you don't have to
over-complicate your architecture by adding more stuff to it,
like specialized service to move data around or writing
a lot of code to basically obtain what
you now can do with just one line of code.
For me, the point is giving developers
the opportunity to choose what is the best architecture for them.
That's my pitch for this feature which I really love.
>> Got it, I feel like this is pretty good pitch
because a lot of folks have seen interests and have
started using is already even though it's just in public preview.
My next question would be,
as someone who might be just getting started,
what are some of the things that you can do
with this or how do you get started?
>> I prepared this picture that hopefully is clear
enough to explain that from Azure SQL now,
you can pretty much reach any service that we have in Azure,
and that somehow needs to be with data.
Actually, we already are running
SP invoke external rest endpoint.
This is the name of the feature is a little bit long,
but we had to stay within the naming convention we already have.
With SP invoke external rest endpoint
you can call or you can send a request to
any other service that we expose in Azure and
even outside the Azure or we get to that in just a minute.
For example, you can send your data
to a function to be processed and then get the results.
Again, just one line of code or you can send
a message to Evan Taber
for further processing with other services.
Or you can query data,
save it, or stored it in Power BI.
For example, let's say you have a DAX query and you
need the result into your database for any reason.
Well, you don't have to do anything special other than just call
a stored procedure and call the rest PowerBI endpoint.
Everything is super simple,
and I have some demo,
if you want to that should simplify
the explanation and give you a sense of what you can do.
>> Definitely let's take a look.
>> Let me go immediately here where I'm
starting with the most common use case
that we are seeing customers using.
You have an Azure function that does something,
it could manipulate data,
could crunch the data,
or you just can take
some payload and do something with that, who knows?
But the point is that you need some data in
your database to be
sent to that function to do whatever you need to do.
Today's super easy to do that with
SP invoke external rest endpoint.
You basically just specify
the URL that you want to call in this case,
it's a tester function that I added territories
information about some persona and some order.
You can send any payload as long as it's JSON.
But I have news that I want to share with the people who are
watching this data expose episode.
I'll give you the announcement
at the end of the session so stay tuned.
But basically, for now,
let's say you just send a JSON payload.
You can specify a customer header,
so pretty regular what you would normally do in
an order or.Net to call HTTP endpoint.
Then you execute the stored procedure and
the result will be returned to you in this response out.
Let me just show you how to work, it's pretty easy.
You just execute the stored procedure,
the function take few seconds or a few milliseconds.
It really depend what is doing and then you have the result.
The result is split into sections.
Response, where you have
all the metadata about the result that you receive so
basically the HTTP status and the headers
that has been sent back to you from the service you have called.
The result is actually the payload that
is being sent back to you from the function.
It's the result of the function if you want.
In this case, as you can see,
we have JSON that contains information about
something that needs to be shifted to
this persona related to an order that she made.
Super easy and now if you think
also that we are just announced there's
some new nice supporter to
JSON payloads up to JSON data in SQL Server 2022,
it's even easier to manipulate this result because it's JSON.
Now you have native functioning in Azure SQL to manipulate JSON,
so it fits very well together.
It's super easy for a developer to manipulate or
even generate a payload.
Function is definitely the most common service
to create a serverless rest endpoint.
But let's say you about Azure,
Static Web App that also contain functions.
You can even call a Static Web App if you
want and just work the same way.
Or in steady, if many companies are
using containers and for example,
Kubernetes to Auster their APIs.
You can even call that with zero problem.
For example, here I'm executing a function,
the queries my album data on a container.
The container probably was sleeping,
so it needs to wake up,
and now Hugo, here's my container.
As you can see it, super-simple,
literally it's one line of code.
It just makes everything easier and integration now,
I like to say one line of code
away because with that you can do pretty much everything.
>> Thanks Davide, this is super cool.
One question I have is that in your demo,
you were showing us a lot of ways that you can read data.
Is this primarily for reading applications and the data in them?
>> Well that's probably the most common thing
because you just want to consume data coming from a service,
but you can actually push data.
For example, here I think I have an example somewhere.
Let me open a function.
Let's say you want to send some data to our function.
You want to push data out.
Let's say that the function doesn't return anything
because it doesn't really process the data,
it just act on the data it has received.
Like for example, send an e-mail,
send them a warning, whatever.
As long as you ship JSON data,
you can put your payload as
something that is also center when the function is called.
I would say, in and out so definitely.
>> Nice, awesome. Cool. My next question is you
have a lot of exciting things
on the left hand side of your screen.
Event Hubs, Power BI.
Like What's going on here?
>> Well, it's just what I was
saying before you can integrate with pretty much everything.
A very common pattern today
is event-driven applications or architectures,
where actually you have a harbor like Kafka Hub or even hub in
Azure that basically listen
to messages that are coming in and axons those messages.
Let's say that you want to build something that
is reactive or even pays also with Azure SQL,
well, today it's super easy.
You just have to set up our inevitable.
I have an application here that can start.
These application, just listen to
messages coming into my event hub.
Now let's say that I receive an order
or whatever and I want to make sure that
my message is sent about that order
is also sent to my event tab an integration pattern.
As you can see here,
I can just do SP book external rest point.
I point to my Event tab,
but in this case I'm even specifying
the partition and I specify my payload.
The payload will be the user ID and some other data.
Oh my bad, I am and on the database.
It will just organize.
Let me go here again, let's do it.
Here we go. The statuses.
I would expect here to have a message receiver that's
pretty nice and also pretty faster.
Now, imagine that now everything is
happened on my machine and being sent to Azure and back.
But something happened here,
that's just my simple application that where I don't
really manage everything correctly but who cares?
Because the only thing I want to show is
that I am sending messages.
You see super fast, it's almost immediate.
We optimize the code
as much as possible to make everything super faster.
Also it's super secular because as you probably noticing here,
I'm also specifying some credential.
That's because we don't want to have, for example,
the shared access signature or
other sensitive data available
in clear text in the script that you may want to share.
We also support the credentials so you can
safely store the credential needed
to call any set of safely in Azure SQL.
Therefore you can call anything securely.
You can manage who can access which URL or http endpoint,
because we added the permission to
manage everything from a security standpoint.
For example, let's say I want to call
Power BI or I want you to be able to call my Power BI,
but I don't want to give you my credential.
I can just safely store my credential somewhere.
The only thing is that I have to give
you the permission to use my credential.
I created this credential to specifically allow it
to only call a specific dataset.
You can go as specific as you want.
Then let me just execute this.
We will get the result.
This is an Dax query executed in Azure SQL right now.
How can you trust me about that since you see just a table here.
Well, let me show you the JSON that we received.
This is the JSON that is coming from Power BI and as you can see,
is formatted in its own spirit a specific way.
But since it's JSON,
it's very easy for me to turn it into into a table.
Now I can, for example,
join is data with other table that I haven't.
Everything is just easier for what concerns integrations.
That's pretty interesting my opinion.
>> Wow, that's awesome Davide.
One of my last questions as we start to wrap up
is you've shown us some great samples.
Is there somewhere folks can go today to try this out?
>> Yeah, absolutely.
Here, if you go to the sp invoke
external rest endpoint documentation page,
you have all the details about also how to use manage that entity,
which is something that especially enterprises asked us to
implement and you have a list of all the endpoints you can call.
Now why we limit the endpoints to
altos that are within the Azure domain.
Because we need to make sure that you are
calling really the service that you think you are calling.
We want to avoid the DNS poisoning attacks
or other nastier attacks.
We need to make sure that we own the DNS.
But if you need to call a completely external services,
let's say a service hosted in your friends House.
Well, just use API management
in sp invoke external less than endpoint plus
API management service that you can use in
Azure allows you to reach any service anywhere in the world.
The last thing I want to mention is that here at the end,
if you go just down the documentation page,
you have a link to our GitHub repository that is full of examples,
so on for example,
how to use API management,
cognitive services event tab and
basically pretty much I shown today.
I just have one last new I wanted to
share with you something really new.
Of all the services we are supporting here,
probably you'll have noticed that
some big services is missing. Azure storage.
Now as we speak,
Azure is being added to the supported service.
You will be able from sp invoke
rest endpoint to query an Azure blob or
an Azure file or
an Azure storage queue and in interact with those entities.
For example, create a file,
read a file, send a message.
Don't you read message from a queue so
that the support for the Azure services,
the most common Azure services complete.
You can have really full integration from Azure SQL
with everything in just one line of code
which make developer super efficient.
>> Awesome, that is super-excited.
You heard it here first on Davide's first.
>> Yes.
>> Davide thanks so much for coming on the show I learned a lot.
I think our viewers probably learned a lot as well.
To our viewers, check the description.
We'll put some links for you to learn more,
especially the things that Davide reference,
leave as a comment and let us know what you're
doing with this new capability.
We hope to see you next time on Data Exposed