Up next in 10
Build serverless, full stack applications in Azure with Azure SQL || Women Data Summit 2021
7K views
Nov 9, 2023
Learn how to create, build, and deploy modern full stack applications in Azure leveraging the language of your choice (Python, Node.js, or .NET) and with a Vue.js frontend. Topics covered include modern database capabilities, CI/CD and DevOps, backend API development, REST, and more. Using a real-world scenario of trying to catch the bus, you will learn how to build a solution that leverages Azure SQL Database, Azure Functions, Azure Static Web Apps, Logic Apps, Visual Studio Code and GitHub Actions. Conference Website: https://globaltechconferences.com/event/women-data-summit-2021/ C# Corner - Community of Software and Data Developers: https://www.c-sharpcorner.com C# Live - Dev Streaming Destination: https://csharp.live #Azure #Data #WomenDataSummit #AzureSQL #FullStack
View Video Transcript
0:00
I am stoked to be here with all of you. Thanks for joining me on a Friday, no less. We have such a great lineup for today. I've been watching some of the sessions and I'm going to keep watching them. I know my colleague and friend Mara is on at the end of the day, so I'll be on pretty much all day
0:18
So feel free to during the session or after the session, add your comments from wherever you're streaming in from
0:25
I would love to help with any questions if they're related to this or even if they're not
0:30
With that, you might be wondering what I'm going to talk about today. I'm going to be talking about serverless full stack applications in Azure
0:38
but I'm going to put a specific focus on Azure SQL today
0:43
But what I want to comfort you all with is that Everything that I do today
0:48
everything that you see today is actually part of a new learning path that
0:53
myself and another colleague launched last month. What this means, this new learning path
0:59
if I just switch to it real fast. What this means is it's all on Microsoft Learn and each module
1:06
there are four modules, but each module has a built-in Azure Sandbox
1:10
which means for free in four-hour chunks, you can actually go deploy all this stuff without having to
1:15
have credits or money or whatever to spend, you can just go play around with it and you can do it like 20 times a day
1:21
So you're never going to run out of blocks. But this learning path was really meant to be built for
1:28
either new developers or students or older developers that are trying to learn some new technology or
1:35
some different technology or even database administrators trying to get into the space of Azure
1:41
So I think hopefully there's something for everyone in what you're going to see
1:45
in the next 40-45 minutes or so. Hopefully that's good. We're going to talk about really
1:51
the full process of building a new application starting with the back-end
1:56
which typically involves some sort of database going into the APIs that are required
2:02
the application that's required, and then of course, how do you automate deployment or CICD
2:06
That's a big topic these days and we'll show you how to do that at least with GitHub today
2:11
So this is great, but for every project, you know, I like to have some sort of scenario
2:18
So for this scenario, it actually came from a real world scenario of a colleague of mine
2:24
Davide Mowry, who helped me with this, trying to catch the bus
2:28
So, you know, I think this is something we're all familiar with, maybe less familiar during
2:32
COVID times, but typically, you know, you are trying to catch a bus usually to get to
2:36
the office, let's say. Now, what happens when you try to catch the bus
2:41
So one of two things typically happens. You get to the bus stop. You wait for some unknown amount of time
2:47
You catch the bus, and then you head to the office. Or you get to the bus stop
2:51
You see the bus because you're just a little bit too late. You have to run to the bus
2:56
And then you have to wait some time because you actually missed the bus before you can
3:00
actually take the next bus and get to the office. So this is a less than ideal scenario
3:05
and maybe this is something that we want to try to optimize
3:10
given the modern world we live in. So we said, hey, everyone has a cell phone
3:15
or pretty much everyone has a cell phone at this point. What if we could have some sort of mobile application
3:21
or website that had a map on it? And with this map, you could see the buses you want to take
3:28
So the buses and maybe a few routes that you might wanna take. and also you'd be able to see a geofence of where you're trying to catch the bus so like for example
3:38
maybe I want to catch the bus within like three blocks of where I live so I can have a geofence
3:42
there and then even better than that what would be great is if one of my buses in my routes
3:49
entered that geofence then I would get a notification so that's kind of where all this
3:55
came from and and another thing just to to show you all kind of the motivation we found for the
4:01
Seattle metro area and actually a lot of relatively large cities provide this information, but they
4:08
actually release a real-time JSON file that has bus information in it. So real-time live updates
4:15
of where buses are. So they're leveraging some sort of IoT here and they're just surfacing it
4:19
on the web for us for free. So we know we have this and we also have a flat file that's like a
4:24
text file that contains all the different routes and that's going to help us kind of coordinate
4:29
which routes we want to monitor and then match it with this real-time data. So that's just an overview of kind of the motivation that went into this
4:37
And so what we did, like you kind of saw a preview of, is we built a whole learning path based on this idea
4:44
So we're going to go through each of it today. Again, we're going to go kind of fast
4:48
but I know that y'all will be able to follow along step-by-step in the learning path afterwards
4:53
That's my biggest call to action is get hands-on, try this stuff yourself
4:57
It's free. got no excuses. Let's get right into it. We're going to start in module one. We're going to be
5:05
starting by talking about creating a foundation for modern apps. To me, that involves selecting
5:11
a database service. This is where we're going to spend a lot of time today because I want to make sure we understand some of the requirements and also what we have available. For each step of this
5:21
you can see the breadcrumbs down in the bottom. We're going to track the database, the API
5:25
the application, and then ultimately the solution. And for each step, what we're going to try to do
5:31
is understand the overall solution requirement, and then what necessary information specific to
5:37
what we're trying to do, do we have? So if we think about our solution, there's three main things
5:43
We want a website to monitor real-time bus locations. We also want notifications when a
5:48
bus is close by, so we know when to leave our office or our apartment or house or whatever to
5:54
cache the bus and then we want deployment automation. So we're just getting started
5:58
but as we enhance this application, enhance this database, we want to be able to automate that deployment in a way that's
6:05
scalable, reproducible, and also able for us to monitor if anything went wrong
6:11
All right. So when we think about the database service, we can first start by thinking about the data that we have
6:16
So we have some real-time bus data, which is stored in JSON. You just saw an example of that file
6:21
Then we also have the route data in a flat file. That's what we have going into it
6:28
but I want to step back and think about in general, what are some modern database requirements for
6:34
applications like this one or other applications in general? Now, I think a big one is this idea of serverless
6:41
When we talk about serverless, that means that we want auto-scaling, meaning we only want to pay for what we're using
6:49
We don't want to pay for provision stuff that we're not using at the time
6:53
but we don't want to under provision and then not be able to perform when our app or workloads gets heavy
6:59
Now, we also want to support development workflows. CICD, DevOps are all huge and very important topics these days
7:06
We want to make sure that whatever database we use is going to help us in
7:11
this development workflow that we're trying to organize. Ideally, especially for this scenario
7:18
but many scenarios as well, you need JSON support. A lot of information is coming in in the form of JSON You going to need JSON support For our scenario and a lot of scenarios are using locations for various things but for our scenario specifically we want to be able to support geospatial queries
7:38
geospatial data points. We'd love it if whatever database service we select
7:44
is going to have built-in functionality. Because if it doesn't, then what we're going to have to do
7:48
is we're going to have to, in whatever language we're programming in, we're going to have to do some extra work
7:53
find some libraries, figure out how to do the geospatial thing ourselves
7:57
This isn't a big deal, but if it was built into the database service that we pick
8:02
just make our lives a little bit easier. Then finally, you want to future-proof your application
8:08
We want to know not only is this database service going to be
8:12
around for the next 5, 10, 15, etc. years, but is it going to keep improving
8:18
and advancing as technology advances? For that, my recommendation, maybe you all saw this coming, is Azure SQL
8:27
Specifically, I think Azure SQL Database is going to be great for this
8:31
There's a few reasons for this. Big reason is that Azure SQL Database today is
8:36
the only Azure SQL deployment option that supports both serverless and hyperscale
8:41
This means if you start out with a small application or you're just developing your application
8:45
you can start in serverless and only pay for what you use, and I'll show you what that means
8:50
Then if your application gets really popular, which maybe my bus application will
8:56
then you can switch to Hyperscale, which is going to allow you to support databases up to 100 terabytes in size
9:04
Really in theory, that's just a number. There's nothing physically or architecturally stopping us
9:10
from going bigger than 100 terabytes. That's one way you can future proof your application
9:15
Also Azure SQL Database has native JSON and geospatial support, which is going to make our lives easier in this scenario
9:22
but also a lot of modern applications are using both JSON and geospatial or
9:27
graph or key value, even XML. These are all things supported natively in Azure SQL database
9:33
Versionless is also another big one. This means that you never have to update SQL Server or
9:40
whatever SQL you're using again. All of that, making sure we don't break you
9:45
that sort of thing now falls on Microsoft. You're just going to get the latest and greatest updates and keep your application running and fast all the time
9:53
including high availability like an SLA and other things. For anyone watching that already has T-SQL skills or SQL server skills
10:01
I know that's a lot of our data community. You can just leverage your existing skills and move to this more modern type of database
10:09
Lots of other things here, but I think a lot of people see themselves moving towards
10:14
non-relational databases or purely NoSQL databases. I think that's fine, but I also think
10:21
that the relational engine is still the backbone of everything. To be able to guarantee ACID
10:29
to be able to do CRUD operations, be able to have tables and indexes to increase your performance
10:34
all these things aren't going anywhere. It's nice that Azure SQL database is built on
10:40
this SQL engine that's been around for almost 30 years, as well as is enabling you to touch some of these NoSQL topics
10:48
that previously might not have been possible with a relational database. So all that being said
10:53
let's talk a little bit about the JSON support that we have today
10:58
One of the things that we have with JSON support today is some built-in functions
11:03
so things like isJSON, JSON value, JSON queries. You can also basically take in JSON and transform it into a table
11:12
or vice versa, take a table and transform it into JSON. This can be nice for both ways
11:18
Taking in JSON becomes really easy, but also if you want to push JSON to an application
11:24
it also becomes very easy because you can actually specify how that JSON is going to be returned
11:28
That's just going to make it a little bit easier for you to process once you're back in your application
11:34
Now, we're going to talk about some of the geospatial support. I'm going to show you in a demo actually
11:40
so I'm not going to talk too much about it now. It is important to note that we have geospatial support by default
11:46
so you don't have to install anything else. You don't have to pay for anything else. You just kind of get these capabilities
11:52
And with that, let's take a look at Azure SQL Database. So the first thing that you're going to do in the learning path
12:01
is focus on the database. And you're going to configure your development environment
12:05
So I'll show you all the tips and tricks I use for when I'm developing with Azure
12:09
with all the things you're going to use today. and then you'll deploy and configure your Azure SQL database and automate updates
12:15
So just for time's sake, I went ahead and deployed this database
12:19
In the exercise, we're using scripts to do that. You could also use the portal
12:24
But what I want to point out is this pricing tier. So what we have here is we have a serverless database, like I was mentioning
12:32
And when it loads, what we're going to see is we're using serverless compute
12:36
So that means our compute resources are auto scaled and we're built on a per second basis for what we actually use, including the max of CPU or memory at any given point
12:47
And what we're going to do is we're going to scale. They're going to scale us on a per second basis between our min and max so we can set those
12:54
And optionally, what you can do is you can set an auto pause delay
12:58
So here I enabled auto pause. You don't have to with an auto pause delay of one hour
13:03
So if after one hour, no one touches this database, then what we're actually going to do is we're going to separate the compute from the storage
13:12
We're going to shut down essentially SQL Server running on the compute side
13:16
And during that time, you're only going to pay for the storage. So this is drastically going to save you money
13:22
Now, when you ping that database again, the first time it will fail and it'll take about 30 seconds to wake back up again
13:29
Basically, we reconnect the compute and the storage. and then you start paying at the minimum level
13:35
and we'll start back to auto-scaling you between what you need. One thing to keep in mind with this
13:41
for all you data people, after a pause date, when it comes back online
13:45
you are starting with a cold cash. So that's something to keep in mind if you're looking at using this
13:50
But we're seeing a huge amount of adoption of this serverless capability
13:54
So I really wanted to make sure that I showed it to you all. All right
13:58
So the other thing that I wanted to show you before I X out of it is this idea of the app CPU build. So I told you when the database is paused
14:06
you're not going to be billed for CPU. So you can see earlier this week, I wasn't using the
14:10
database because I wasn't, you know, working on this application and I wasn't billed for CPU at all
14:16
Wednesday, I hit it a few times. So you can see I was built a little bit same with yesterday
14:20
And then today as well. So you can see how it's going to give me cost saving because if I was
14:26
using the provision compute model, I'd be built at this rate up here
14:29
whatever I provision the whole time, even though I'm not even using the database a lot
14:34
So this is really useful. All right, so the second thing I wanted to show you
14:38
is the geospatial support. So if you go through the exercises, you're going to get access to this SQL notebook
14:46
which you can open in Azure Data Studio like you see here. Hopefully you're all familiar with this
14:51
It's an awesome tool. I talk about it all the time. Basically it allows you to mix text cells with code cells and you can pick the language you want If you a data scientist maybe you familiar with using Python or R notebooks but now you can use PowerShell notebooks or SQL notebooks
15:06
Super powerful. We're going to stick to SQL for today, and we're going to take a look at some of the JSON support
15:12
Now, what I did here is I just brought in some of that JSON data
15:16
just a sample of the JSON data that contains the real-time bus information
15:21
Just want to bring in a sample of it and start to work with it. I'm calling it a payload
15:25
It's just straight JSON data. We can even see there's some nesting going on with the position
15:30
Now what I'm going to do is I'm going to use this geography point nomenclature
15:35
to basically bring in the latitude and longitude as a location, and I can even specify the standard that I want to use
15:43
That's really nice. It's just out of the box. I'm going to bring in this JSON data using the open JSON T-SQL function
15:53
And this is going to allow me to bring in those things and specify how I want that data store
15:58
Now, if we take a look at it, we can see the direction, the route, the vehicle
16:03
and the location is now taking the latitude and longitude and store it as this value that doesn't
16:08
mean a ton to us, but it allows us to store it in one place. Now, what I can also do is I can
16:13
take that location. So take this weird, funky number binary and convert it to a string. And
16:19
that's going to give us this point value. Now what I can do is I can take this point value and go into a tool like this
16:29
and I can actually plot the point on a map. If we zoom out a little
16:35
we can see this is somewhere on the east side of Seattle
16:39
kind of close to the Microsoft Office, which is over here. If we go back to Aggredata Studio, this is cool
16:45
We can plot points, but we want to do more things than that. Another thing you can do just to show you a few things out of the box is this ST distance
16:53
I'm basically saying what's the distance between bus one and bus two
16:57
Sure enough, I'm able to get that in meters. For certain applications, this could be useful
17:02
Another thing that you can do, which is pretty cool, is you can also plot polygons
17:07
Here you can see I'm using a polygon. We can actually bring that in and store it in this case, again, with the standard as a geofence
17:17
So this is the geofence I was talking about, and we can even take a look at that in a minute
17:22
So we can now have this geofence and we have points. Now, what do we want to do for our application
17:26
Well, we want to know when a point or a bus enters or exits a geofence
17:31
So fortunately for us, there is a way for us to put the points and the polygons together
17:39
basically using a geometry collection. And then we can use this st within function, which is going to say
17:45
hey, for this location, is it within the geofence? We can basically select that as a yes, no
17:52
so a one or a zero. If we take a look at this one, for example, we can see that
18:00
this bus is within the geofence here. That's my geofence, that's my bus point
18:05
So then the geofence. So in Azure Data Studio, we see is the in geofence result is one
18:11
Similarly, just to do a sanity check, we can do it for this one. and we can see the buses down here
18:17
So it's not in the geofence, and we can see back in Azure Data Studio as well
18:21
that it's picking that up as well. So this is pretty powerful. All right, so now that we've seen this
18:27
what we're going to do is we're going to add some stored procedures. I'm not going to walk through all that
18:30
We're going to add some stored procedures so that our database is capable of taking in the data
18:37
storing it accordingly, processing it, and letting us know which buses are in or out of the geofence
18:44
at that given moment. Now, what we can do is since we already configured this on
18:49
another database is we can leverage something called a DACPAC, which you can generate using something like SQL package
18:56
Now, what I've done is I stored the DACPAC in my GitHub repository
19:02
but I've also included a YAML file, which is basically instructions on how to deploy or
19:07
redeploy the schema on my database. So what you can see here is every time a push or
19:12
a pull request is made on this repository, we're going to run this deploy database job
19:17
which is using the Azure SQL action. It's going to connect to my Azure SQL database server
19:22
We have the secret or the connection strings stored securely within the repository
19:28
We also have a reference for, hey, where is the DACPAC within the repository
19:33
If we go over to some of the actions, you can see I did this one just before this session
19:40
We can see what it's doing is it's It's finding the DACPAC file
19:44
it's publishing it to that database server, it's updating the database and then letting us know
19:51
that it was published successfully. So this is a way we can automate the deployment and updates to our databases
19:57
Or if I start a new dev environment and I want to be up and running with the right schema
20:01
then I can just leverage this. So really cool, powerful stuff. We saw a lot in that one demo
20:07
But hopefully this was useful for you. Now, one thing we didn't take a look at is
20:12
importing the route data into Azure SQL database. This is very similar to what you would do in SQL Server if you're familiar
20:19
except instead of importing from a file, we're going to be importing from Azure Blob Storage
20:24
There's an exercise that goes through how to do that as well. If we take a look at our architecture so far, what do we have
20:32
We have buses which have IoT devices on them. Now, this isn't something we had to configure
20:37
because ultimately they're producing the Seattle County Metro in this case is producing a JSON
20:42
document that contains all that real-time bus information. We know that we can process that
20:48
bus information in here and store that bus information here and identify if something
20:53
is or is not in a geofence. We know that we can do that because of the example you just saw
20:57
We also created some stored procedures with that DACPAC file that can do that for us
21:01
without me clicking manually each time. Now, we talked about how we could also get the route
21:07
reference data into Azure Storage and then use T-SQL to get it into Azure SQL Database
21:12
The next thing we need to understand is, how are we going to regularly keep
21:17
the data in Azure SQL Database up-to-date? For that, we're going to need some API to basically
21:23
call this new JSON document and bring in the new data. That brings us to module two
21:29
where we're going to talk about serverless APIs. We're going to talk about things like Azure Functions and
21:34
Azure Logic Apps. And just checking the questions, keep your questions in coming in
21:41
I see one comment about Clippy. I did get to meet Clippy a few years ago when we were back in person
21:48
It was pretty cool. All right. So we talked about selecting a database service
21:53
The next thing we need to think about is selecting an API service. So remember what I said
21:58
I wanted to start with the solution requirements and then dive into each component of that
22:03
So down at the bottom, you can see what the breadcrumbs. We talked about the database. Now we're going to talk about the API
22:07
Again, overall, we're still looking for a website. We want notifications and we want deployment automation
22:14
When it comes to the API itself, we want the API to go get the latest data from that JSON document
22:21
identify buses in the monitor routes, and also identify geofence activation Again we don want to monitor all the routes only the routes that are relevant to me as a user that I want to monitor Then we want to send an email notification for each activated bus
22:37
I'm calling a bus activated if it enters or exits a geofence
22:42
For this, I think we can use Azure Functions. If you've never played with this before
22:46
I highly recommend it. It supports a lot of different languages. It also supports different triggers
22:51
So for example, you can have a queue trigger, you have a timer trigger
22:55
you could just have an HTTP trigger, so it just is triggered when you call an HTTP request
23:03
But in this case, we might want a timer trigger, right? Because we want to keep our database up to date
23:08
The great thing about functions is that it's super easy to configure because you just deploy them and they are serverless
23:14
and they will scale on demand. So they'll scale up, they'll scale down based on what you need
23:18
and you don't really have to worry about configuring it. makes it super easy
23:22
So this Azure function, what do we need to do? So I'm trying to understand the requirements
23:26
a little bit further. So let's say every 15 seconds, I want it to do the following things
23:32
Determine what the monitor routes are. Because again, we don't monitor all the routes
23:36
just the routes that we care about or our users care about
23:39
Then we want to get the latest bus data and want to store it in Azure SQL Database
23:44
Then maybe with Azure SQL Database, with those stored procedures, we want to trigger some of those sort of procedures in
23:50
Azure SQL database to identify if any of those new buses or routes are activating a geofence
23:58
Then once we identify, hey, maybe route two is activating a geofence near me
24:05
then I want to send an e-mail notification saying, hey, go catch the bus
24:09
Your bus is almost here. It's entered the geofence. For that, we haven't talked about a good solution for that yet
24:16
Now, I know that there's a lot of great developers watching this probably
24:20
So you could go in and write this from scratch. More power to you
24:25
But what if I told you there was a way that you could do this without writing any code, right
24:30
Just connect to Outlook or Gmail or SSMS and just configure your connectors and go
24:36
Well, that's what Azure Logic Apps can do for you. And this is a really powerful tool, especially for folks who I wouldn't say are lazy like me
24:45
but don't necessarily want to reinvent the wheel if you don't have to. That's what Azure Logic Apps is great for
24:51
It is also serverless and scalable just like Azure Functions, except it is a low-code or no-code development platform
24:58
I'll show you a look at the Logic App Designer and how easy it is to use
25:02
They have hundreds of connectors. Sure, they have connectors to SQL Server, Azure SQL, Outlook
25:06
SMS, Salesforce, Gmail, all those sorts of stuff, Twitter. and you can just connect up and it just makes integrating things really simple
25:16
So with that, let's take a look at our solution so far
25:22
So where do I want to start? So let's start by looking locally
25:25
Now, the great thing about Azure Functions, you can run everything locally before you deploy it
25:30
And that's really nice because it just helps you debug and test things easier without even paying any money
25:36
So what I have here is I'm in Visual Studio Code and I'm in the repository for this application that we're using
25:44
What you're going to see is for every step, we've actually coded it up in .NET, Node, and Python
25:49
So you can pick whichever language you're more comfortable with or since we wrote them all side by side
25:55
I found it was a good learning exercise to learn a new language. For example, I helped write the .NET in the Python section
26:02
but my colleague wrote the Node section. And by working together and seeing, okay, this is how you write it in Python
26:07
this is how it translates to Node. It was really useful for just learning more about Node
26:12
For local, what I'm going to do is I'm going to use the dotnet version of the Azure function
26:18
Down here, I'm actually just going to use func start. There's a lot of built-in libraries and
26:22
packages and extensions that you can use locally for developing. While this is kicking off
26:27
I just want to show you a glimpse at what the code is doing. This is a timer trigger
26:32
so it's running every 15 seconds. It's basically going to process the bus data
26:37
What does that mean? Just really fast, I'm going to show you what that means
26:41
What that means is first, we're going to get the real-time feed
26:47
We're going to use that JSON real-time document you saw earlier. Like this, we're going to pull this in
26:54
Then we're going to get that data as a feed. We're going to return the feed
26:59
From the feed, we're going to make a connection to Azure SQL Database
27:04
and we're going to get the monitor route. Actually, before we do something with the feed
27:08
we're going to figure out what routes we want to monitor, and we're going to take note of that
27:13
Now, what we're going to do with the process geofences method is we're going to take in that JSON data
27:20
This should look pretty familiar. It's very similar to what you saw in the first demo
27:24
We're going to take in the JSON data and add it to our Azure SQL database
27:28
using this stored procedure called add bus data. and the result of add bus data is going to tell us for those monitor routes that we've picked up
27:38
and the new data coming in, how many buses are activating a geofence and maybe it's none
27:45
or how many are on our monitored route. So for example, we can see this is running locally down
27:49
here and here, for example, we can see there's 443 bus positions, but five buses in our monitor
27:54
route and zero buses activating a geofence. If a bus was activating a geofence
28:00
then we want to trigger a Logic App, which I haven't shown you yet. We want to trigger a Logic App and we want to send
28:06
the Logic App two things. We want to send it the vehicle ID
28:09
and we want to send it the geofence status. Basically, what vehicle should I look for
28:14
and is it entering or exiting the geofence? That's basically what we're going to do
28:19
and then we're going to call the Logic App. So you can see here that's a logic app URL
28:25
So I've actually gone ahead and published this. So I'm going to cancel it here
28:29
but I published the Python version. So the way I was able to do that
28:32
again, we're using these YAML files, and you'll see there's one for each language
28:36
But the Python one simply uses the Azure Function package path, which is basically saying, okay, where's the code
28:45
And it's also using the function app published profile, which is basically like a connection
28:50
string to your Azure function app. Then it's basically going to deploy
28:56
all this to Azure functions for us. Now, it's pretty similar for dotnet or node
29:01
it's just slightly different in basically which action you're using. Here we're using the dotnet one as opposed
29:07
to a Python one as you see here. We've used that to deploy this function
29:14
In the portal, what you're going to see is, I've got this function up here
29:19
You can see the execution counts, what's going in. We can also see here in monitoring that we're monitoring the same data
29:28
This is a little bit, it's been running this whole time, so it's a little off
29:32
but you can see we're getting the same logs in Azure as we were getting locally
29:36
which is nice. Now you're probably wondering, I cut off around the Logic App portion
29:42
If a bus is activating a Logic App, remember we're going to call the HTTP request that sends it to
29:48
the Azure Logic App and we're going to call it with those two values and then we're going to send an email
29:52
Let's look at how easy that was to do with Logic Apps. This is my Logic Apps
29:57
You can see this is the Logic App Designer. I can drop. It's very easy to find things
30:02
If you want to add a new step, you can see there's lots of connectors that you can leverage
30:10
But in this case, we're just going to look at what we have. So we have this HTTP request that our Azure Function calls
30:16
and it has to give two values. Remember, we talked about those two values it's going to bring
30:20
And it's going to take those two values, and you can see I'm using the Outlook connector here
30:24
and it's going to send an email. It's going to say, hey, if you want to catch the bus, you head to the bus stop now, your bus
30:29
and it's going to supply those values of the vehicle ID and then whether or not it's entering or exiting the geofence
30:35
And then I could even parameterize this to send it to specific users, but I'm just keeping it really simple here
30:41
So that's it, just this two-step logic app. And now I'm able to automatically send emails
30:47
And you can see I've gotten a few since this session started. So, for example, I can see this bus is entering the geofence
30:54
so I can see that I should go head to it now. You can see they've been coming in all hours
31:00
This is how we can take a look at the Azure Logic Apps and
31:05
using Azure Functions together. It was very easy to make this happen
31:10
I see one question here that I'm going to address now. It says, does a Logic App have multiple triggers
31:17
Yes, there are multiple triggers that you can leverage for Logic Apps. There's literally like 250 or 300 different connectors and
31:24
ways that you can trigger this thing or make it work. So something to look into
31:29
Definitely a really cool technology. Let me see if there are any other questions that are related to this
31:36
What are the prerequisites for this learn module? You don't really need anything
31:41
We will walk you through in the module. So if you happen to take on, we do say there are some things that are useful
31:50
so being able to navigate the Azure portal, familiarity with different things
31:54
but ultimately we're going to walk you through step-by-step, whatever it is that you need
31:59
especially configuring your environment. Here I'm going to take you through all the extensions I think you should have
32:04
as well as how to get the GitHub code, and how to get set up in Azure Data Studio
32:10
Hopefully everything you have regardless of prereqs is there for you. Hopefully that helps
32:18
Let me see. I think that's good for now, but I can take some of these questions that are a little tangential
32:25
a little bit later if we have time at the end. All right. We took a look at a lot of things
32:32
We've done so much so far. We talked about how to run functions locally
32:36
We published them using GitHub actions just like we did for Azure SQL database
32:41
We looked at Azure Logic apps and we were able to monitor those results
32:45
Our architecture so far is, of course, we're familiar with this. Now we've filled in this question mark
32:52
We're going to use Azure functions to sit to trigger the logic out and send an email
32:57
We're also going to use Azure functions to get the data into Azure SQL database and
33:02
process it to identify what should be triggered in an email. Now the next part is around this application
33:09
We still don't have an application. That's what we're going to get into now is
33:13
how to build the application in this case with Azure Static Web Apps
33:20
We remember our requirements. We've checked out most of these except the website for
33:25
monitoring real-time bus locations. For that website, we want to basically have
33:30
a map with real-time buses and the geofence, and we want to be able to filter what's displayed on that map
33:36
based on the monitored bus routes and geofences that are provided as an input or by a user
33:43
Now, some of you might be wondering a little bit about what static web apps are
33:48
Now, a big thing about static web apps is that server-side rendering is not required
33:53
We're seeing a lot of these being built in Angular, React, Blazor, Vue
33:58
For this we used Vue to do this front end purely because we wanted to learn how to use Vue We been hearing a lot about it This is JavaScript language If you want to learn about it this is a good place to start as well
34:12
Now, for our solution, instead of using Azure App Service for hosting
34:17
we're going to use Azure Static Web Apps. I really love Azure Static Web Apps because what it's doing is it's
34:22
combining the power of static web application hosting with Azure Functions. Some of the stuff you already saw
34:28
we're now combining it all in one. What that means is we can take in our code changes
34:34
and with GitHub Actions or Azure DevOps, it's supported in both. I'm just only using GitHub Actions today
34:40
You can make it so that your static content, so your web page is displayed and deployed
34:46
At the same time, your API or APIs using Azure functions are displayed
34:51
Now, this is a relatively new service. It just became generally available maybe mid-May
34:58
So it's relatively new. But what's so great about it is how tight these integrations with, say, GitHub Actions are
35:04
So, for example, if I take a look, when I deploy an Azure Static Web App
35:11
it's going to create that GitHub Action workflow file. Those YAML files I've been showing you for Azure SQL Database and Azure Functions
35:18
you have to create those. But with GitHub Actions and Azure Static Web Apps, it creates all this for you
35:24
It's even going to create a connection to your Azure Static Web Apps and to your GitHub and kind of integrate your Azure and GitHub together so they can do a lot of the handshakes and pass offs and you don't have to be involved, which is really nice
35:39
Now, there are two things that you are responsible for providing during deployment or post deployment in this YAML file
35:46
And that's where your front end application is located and where your API or APIs are located
35:52
So where in your repository? Azure Static Web Apps wants you to tell it basically
35:57
don't let me guess, tell me where your front-end is and tell me where your APIs are
36:02
Just because it takes a minute to deploy, I wanted to take a pause and go ahead and deploy this Static Web Apps
36:08
What you're seeing is here in this example, there are three different languages that you can use
36:14
and we're going to use the portal, but there's also an exercise if you want to do this all using scripts
36:18
you can do that as well. So what I'm going to do is I'm going to search for static web apps
36:28
And in the marketplace, I'm going to go ahead and select it. That's going to allow me to create a new one
36:33
And then I'm going to be brought to this familiar workflow. I'm going to put it in my bus resource group
36:39
We'll just call it bus app. And the great thing here is you can pick two tiers
36:44
Free, which is what I'm going to use. or you can pick standard, which is going to be able to auto scale and is more meant for your production workloads
36:51
I'm going to pick a region. You want to put this region as close to your database as possible
36:55
So I think my database is in East US too. And then I'm going to use GitHub. Sorry, this always gets me
37:01
Being a Microsoft employee, I automatically get put in the preview Azure portal
37:05
And sometimes I forget that I need to hop into the actual portal
37:10
So just bear with me one minute. I'm going to go through that again
37:14
but now you'll be a pro at it because you've seen it twice, right? That's how that works
37:18
So we're going to go into the Bust Research Group. I'm going to call it Bust App. Again, we're going to use free
37:22
I'm going to put it near my database, and now I'm going to sign in with GitHub. So remember I said this is going to help me do all of those handshakes for me
37:30
because I'm basically allowing GitHub and Azure to communicate without me having to facilitate that
37:38
So now that I'm in, I can specify my organization, my repository
37:43
So I've forked the main GitHub repository. That's what you'll do as well
37:48
And then even the branch and I can tell it what language this is. So those are the different frameworks I was talking about
37:53
They also have static site generators or you can bring your own. I'm going to go ahead and select view and then I'm going to specify where my app and
38:04
database are. So that where my app is in my GitHub repository and this is where my API or my function is which we talk a little bit more about later I just going to go ahead and spin this up
38:17
and then we're going to actually talk about what's happening in this application, because we don't know yet and how things are working
38:22
Now that's deploying, let's talk about what's happening in the Adderstatic Web Apps
38:29
and how we're going to display that data. Now, the first thing to consider is
38:36
how is the front end going to be getting data? How is the client UI
38:41
that's what I'm referring to as the front end, going to get the data? Well, it's basically going to call the back-end API
38:47
so that Azure function that's built in, and just return whatever that back-end API returns
38:53
We'll talk a little bit about what that back-end is doing, but let's first talk about the front-end
38:58
This is just a quick look at the code. What it's first doing is it's going to look in the URL
39:04
and it's going to look for the route ID and the geofence ID
39:09
Again, one of the requirements remember was to only display certain routes and geofences, not all of them at once
39:15
We're going to add some parameters to our URL. Maybe we can make this a little fancier in the UI later
39:20
but for this initial deployment, that's what we got. Then it's going to take that route ID and geofence ID
39:26
and basically call the API. We're going to call the API with that information
39:35
and then we're going to return whatever that API returns. What is that API doing
39:41
Now we're looking at the backend API, which is going to call Azure SQL
39:45
which essentially has all the data we want to pull forward and ultimately show in the application
39:54
Now, all this data is in Azure SQL database, we're just going to use a stored procedure to call it
40:00
we're just going to use, I think it's called get monitor bus data
40:04
But the tricky thing, or one thing to be careful of when you're working with different languages
40:09
is there are different libraries you're gonna want or packages you're gonna want when you're interacting
40:13
with Azure SQL database. And even trickier, your connection string required is gonna change slightly based on what language you're using
40:21
So make sure that for whatever language you use, you select the right connection string
40:26
And you might say, well, duh, but I can't tell you how many times I was using
40:31
the SQL Server connection string when I should have been using the Python connection string and then nothing worked
40:37
Once you get that, which is pretty simple, then you can configure that connection
40:43
from your Azure Static Web App. Basically, we're going to call the stored procedure like I was telling
40:48
and it's going to return all the different points that are in our monitor bus data
40:53
and it's also going to return the actual geofence. This is going to make it super easy for us to surface
40:59
Remember how I was saying that JSON support and Azure SQL database allows us to
41:03
specify how we want to return the data, and so that's what we're able to do here
41:07
All right, so let's take a look at how to put this all together
41:11
I'm going to go back, hopefully our application is deployed, so it has
41:17
We're going to go to the application, and we can see a few things
41:23
Again, since it's tightly integrated with GitHub Actions, we can go straight into the main GitHub repository
41:31
We can edit that YAML file. If we take a look at that, remember I told you it was going to
41:36
automatically deploy this for us. We can see it's gone ahead and deployed this for us
41:40
It's added these secrets to our repository, and it's taken in our application and API location
41:47
Now, the other thing we can do is we can see the GitHub actions runs
41:52
Just like we saw them for Azure SQL database, They also exist for Azure Functions
41:56
and they also exist for Azure Static Web Apps. You can see, for example, here what we're doing
42:03
This one's actually pretty smart because it's able to detect the language of your application
42:10
Here it saying let me see let me find it It detects that my API was written using Python in this case Then it going to basically download the right version of Python and all the requirements
42:27
that I have for my application in order to deploy this thing and build it correctly
42:31
That's pretty cool, it just takes care of that for me. The last thing is I can take a look at the actual application
42:36
It gives us some funky name, which you can change later. We can see it's just a map
42:41
Now it's not displaying our Azure SQL database data yet. But why is that
42:46
Well, that's because we haven't configured the connection string. Remember I told you, you have to tell the Azure SQL
42:55
sorry, you have to tell the Azure Static Web Apps how to connect. I'm going to create a connection string
43:01
Azure SQL connection string. We're going to make sure we use the Python connection string
43:07
because that's what I use this time. But again, this is written in node and dotnet as well
43:15
Now, we should be able to save it and go back to our application and
43:20
refresh and see that information. Now, we still aren't seeing it and that's because remember
43:26
we said we want to specify the route and the geofence ID as well
43:32
Now, when I specify that and refresh, cool, what do we see
43:36
So down here, we see our geofence. I'll zoom in a little. We see our geofence
43:39
We see there actually are a few buses in the geofence right now, so I could go catch the bus
43:44
And we can also see buses that are nearby data points that are close by
43:49
It's only displaying the most recent 50. But we can see there are some near Microsoft West Campus
43:54
and some probably getting pretty close. Probably a lot of chances for me to catch the bus pretty soon
43:59
So that's kind of what we took a look at. Again, all this is also, of course
44:03
you could go through the code for this. It's all in here for the Azure Static Web App
44:07
You see the API is written in three different languages, and you have the client application
44:11
All right. We've seen a lot. Let me come back and let's take a look
44:20
at the final architecture we came up with. You've seen all this before
44:25
the IoT device, Azure Functions is sending the notifications, and also putting data into Azure SQL database
44:31
What we added was this Azure Static Web App, which combines the web application with Azure Functions
44:37
That Azure Function is simply calling Azure SQL Database to return the data to
44:41
surface in our Azure Static Web App, which is then producing that website
44:45
for monitoring the active buses. Hopefully, that was cool. We've seen a lot of things and not a lot of time
44:52
You've really learned how in different languages, you can see the integration points between things like Azure SQL Database
45:00
Azure Static Web Apps, Azure Functions, Azure Logic Apps. Hopefully, you're seeing how Azure itself is really a big puzzle
45:08
and it's very interesting and useful to know how to piece these things together
45:13
Now, resources, you're probably wondering, where can I get hands-on? This first URL is going to take you to the learning path where
45:21
again, you can try all these exercises for free, you can play this application, play around with it
45:25
I challenge someone to go get the JSON data for the city they live in
45:30
and update the application and submit a PR. That would be a cool update
45:35
We have a show on YouTube called Data Exposed. I stream every Wednesday at 9 a.m. Pacific
45:40
and we also release a bunch of on-demand episodes with people from the Azure SQL and SQL Server product group
45:46
Definitely want to subscribe to that. You should follow us on Twitter
45:51
As Simon mentioned, myself, Davide, who wrote this sample, and a few other colleagues from our team
45:59
We actually wrote a book and Simon's so nice to show his book, the book on screen and read the bios from it
46:05
But definitely check out the book. I hope this is really useful for you all
46:10
Thanks so much for having me
#Education
#Scripting Languages
#Windows & .NET