Have you heard about the Seasons of Serverless challenge? Join the festive virtual potluck by creating solutions to challenges leveraging Azure serverless computing. Throughout the next seven weeks we’ll be sharing a solution to the week’s challenge that integrates Azure SQL Database serverless with Azure serverless compute.
Challenge 3: The Longest Kebab 📏
The third week features the traditional “kebab” from Turkey. The challenge calls for quickly calculating a recipe for the kebab based on an input weight of meat. In addition to returning the recipe, we’re also interested in how many people the kebab will feed as well as how long it will be. Below is a high level view of the solution we created, utilizing an Azure Function and Azure SQL Database serverless. While the components are similar to Challenge 1 where Azure Functions and Azure SQL Database serverless was leveraged to solve the challenge, we decided to write the Azure Functions for Challenge 3 in TypeScript. The completed example can be found on GitHub.
Azure SQL Database and Indexes for Performance
In challenge 1, we created an Azure SQL database with the serverless compute tier. In this challenge we are going to create another database in the same manner, providing us with a serverless SQL database hosted in Azure that automatically starts, pauses and scales with our workload. Through the SQL Database projects extension in Azure Data Studio we are able to create a new SQL database project and the handful of database structures we need to store recipes and return the maximum length recorded.
The table dbo.Recipes will store each recipe generated and our Azure Functions will call two stored procedures: dbo.saveRecipe and dbo.getRecord. In dbo.getRecord, we will retrieve only a single record from the table dbo.Recipes after ordering the recipes by kebab lengths.
CREATE PROCEDURE [dbo].[getRecord]
AS
BEGIN
SELECT TOP 1 LambKilos, Onions, GarlicCloves, CuminTeaspoons, SumacTeaspoons, SaltTeaspoons,
BlackPepperTeaspoons, RedPepperTeaspoons, LengthCm, FeedsPeople
FROM Recipes
ORDER BY LengthCm DESC, Id ASC
END
One way to improve performance of sorting the kebab recipes and returning the longest one is to store a presorted copy of all the recipe Id’s and their lengths in our database. This is an INDEX, and we add it to the definition of our table and specify which column(s) should be used to look up a record in the index. There is a great depth of information on indexes in T-SQL available, one overview of the functionality is the documentation.
CREATE TABLE [dbo].[Recipes] ( [Id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY, LambKilos DECIMAL(14,2) NOT NULL, Onions DECIMAL(14,2) NOT NULL, GarlicCloves DECIMAL(14,2) NOT NULL, CuminTeaspoons DECIMAL(14,2) NOT NULL, SumacTeaspoons DECIMAL(14,2) NOT NULL, SaltTeaspoons DECIMAL(14,2) NOT NULL, BlackPepperTeaspoons DECIMAL(14,2) NOT NULL, RedPepperTeaspoons DECIMAL(14,2) NOT NULL, LengthCm INT NOT NULL, FeedsPeople INT NOT NULL, INDEX IX_Length (LengthCm DESC) )
After deploying our database project in Azure Data Studio, we can check that our index is being used in the dbo.getRecord procedure by using the command “Run Query with Actual Plan” instead of “Execute Query” on a query editor with the statement EXEC dbo.getRecord. The query plan shows our index, IX_Length, reading a single row with an “Index Scan” and doing an inner join between the Id of that row and a key lookup (Id) of our primary key. We could have two, 200, or 2,000,000 rows in our Recipes table and the query would be reading a single row from the index and a single row from the table. Without the index our query would have to read every row in the Recipes table to determine which has the longest length.
We manually created an index in our database project for this week’s challenge, but with Azure SQL database, the database engine can make and apply index recommendations for you. The serverless tier of Azure SQL database includes intelligent performance features, including Query Performance Insight and Automatic tuning. With Query Performance Insight you can quickly identify and drill into the details of long-running or resource-intensive queries on your database. With Automatic tuning your database can have performance changes automatically applied, including index creation.
Creating TypeScript Azure Functions
Using the VS Code Azure Functions extension and the “Azure Functions: Create New Project” command, we’re creating a new Azure Function locally in VS Code in TypeScript with the HTTP trigger template. This function will receive HTTP GET requests with an amount of lamb in kilograms (kg) through a query string parameter. The body of the function is defined in the httpTrigger(context: Context, req: HttpRequest) function in the file index.ts. Dependencies for our function that need to be installed to run the function are detailed in the file package.json, where we want to begin by adding a line for mssql under dependencies by running npm install mssql from the terminal. mssql is the package name for the open source Nodejs driver for MSSQL that enables connecting our TypeScript functions to Azure SQL Database serverless.
We want to create 2 functions in our folder, 1 for receiving a querystring for an amount of lamb meat and returning the recipe and another (2) for checking the current longest recipe recorded. In both functions, we are going to leverage TypeScript’s namesake – types – in defining classes to assist in working with the kebab recipes and length record.
For function #1, the recipe calculator, we want to return a response to the user as fast as possible. We’ll do this in 3 steps: by creating a new instance of a KebabRecipe, returning this to the user in the HTTP Response, and finally saving the KebabRecipe to the database with the RecordRecipe function. By declaring a custom constructor function for the KebabRecipe class, we are able to create an instance of the recipe with a single line of code in index.ts and leave all the calculation of the other attributes in the class declaration in recipe.ts. The following is the custom constructor for our KebabRecipe class:
constructor(lambKilos: number) {
this.lambKilos = lambKilos;
this.onions = lambKilos / 2;
this.garlicCloves = lambKilos * 2;
this.cuminTeaspons = lambKilos * (3 / 4);
this.sumacTeaspoons = lambKilos * (3 / 4);
this.saltTeaspoons = lambKilos / 4;
this.blackPepperTeaspoons = lambKilos / 8;
this.redPepperTeaspoons = lambKilos / 8;
this.lengthCm = Math.round(lambKilos * (2.2/4) * 12);
this.feedsPeople = Math.round(lambKilos * (2.2/4));
}
We are able to set the HTTP response and call the function that sends the recipe to our serverless Azure SQL database. The key to ensuring that our user experience is a fast response on the recipe every time, regardless of how long the database access takes, is not waiting for the RecordRecipe() function to complete before reaching the end of the httpTrigger() function. Although Azure SQL is fast – there may be times when our serverless database is unpausing and we want to return the recipe to the user while the function retries the connection after the database is ready. In the screenshot of our application logs below, we see the log call for “End of function” on the 2nd line and the function’s service reports that the execution took 53ms. On the last line, timestamped 5ms later, we see the log call from the RecordRecipe() function. ⚡
Note: Should the storage of a generated recipe be critical information, we would consider connecting our Azure Function to a messaging service layer as a producer, allowing data storage requests to be safely queued as subscribers.
The full code for the Azure Function, including index.ts and recipe.ts, are available here.
await: Retrieving the recipe for the longest kebab
While the Recipe function needed to return a recipe as fast as possible, we also want a user to be able to find out what is the longest Kebab Recipe generated via our application. Using the “Azure Functions: Create Function” command we can add another function to our project, allowing the functions to share the same Function host and application settings (like database connection details). The 2nd function is also a TypeScript function with an HTTP request trigger but the program flow in index.ts is more straight forward. The function cannot return results to the user until it hears back from the database what the longest kebab recipe is. Enter await, which suspends subsequent execution until the called function fulfills the Promise and returns. (async/await is functionality from JavaScript and is available in TypeScript as well)
The root level function httpTrigger is an async function, permitting utilization of the await keyword within it. We can apply the await keyword to the KebabRecord.getRecord() function, where we will connect to the serverless Azure SQL database and update the KebabRecord object. In doing so, we force the function to wait for the response from the database before returning an HTTP response to the user. The getRecord() function is a public function in the KebabRecord class, exposing it to other modules (such as index.ts) via an import statement. There is a private function within the KebabRecord class, a function named connectToSQL() that assists our class in connecting to the Azure SQL database.
private async connectToSQL(retryCount) {
if (retryCount < 10) {
try {
let pool = await mssql.connect(sqlConfig);
return pool;
} catch (err) {
console.log('retrying sql connection');
console.log(retryCount);
retryCount = retryCount + 1;
console.error(err);
await sleep(10000);
return await this.connectToSQL(retryCount);
}
}
}
After connecting to the Azure SQL database, we can access the first (and only) row returned with the first item in the array recordset. this refers to the calling KebabRecord object, allowing us to update its attributes (such as number of onions). When the function completes, it resolves the Promise and returns execution to the primary function in index.ts.
public async getRecord() {
try {
let pool = await this.connectToSQL(0);
let resultSet = await pool.request().execute('getRecord');
let outputRecord = resultSet.recordset[0];
this.lambKilos = outputRecord.LambKilos;
this.onions = outputRecord.Onions;
this.garlicCloves = outputRecord.GarlicCloves;
this.cuminTeaspons = outputRecord.CuminTeaspoons;
this.sumacTeaspoons = outputRecord.SumacTeaspoons;
this.saltTeaspoons = outputRecord.SaltTeaspoons;
this.blackPepperTeaspoons = outputRecord.BlackPepperTeaspoons;
this.redPepperTeaspoons = outputRecord.RedPepperTeaspoons;
this.lengthCm = outputRecord.LengthCm;
this.feedsPeople = outputRecord.FeedsPeople;
} catch (err) {
console.error('Failed to retrieve record from SQL database.');
console.error(err);
}
}
Wrapping Up
We will deploy our Azure Functions into a single serverless Function App. This can be accomplished by the same GitHub action for Azure Functions utilized in Challenge 1. One last thing – application settings. The connection details for our Azure SQL database were stored in the local.settings.json file in our Azure Function project and need to be input in the application settings under Configuration on our function app. We have 4 individual settings, the server address, the database name, the SQL username, and the SQL password.
With the secrets in place and the functions deployed to Azure, we are ready to test our functions from public URLs. The full source code from this example is available here: https://github.com/dzsquared/seasons-of-serverless-week3
For a limited time, the longest kebab API test URL is: https://kebab.azurewebsites.net/api/RecordAPI?code=3LwapFdF8mCNGOg0yXAIhvpiyqnNCdIYPN8Au28fkHTzMMnLcJ8F1Q==
For a limited time, the URL to generate your own kebab recipe is: https://kebab.azurewebsites.net/api/RecipeAPI?code=3LwapFdF8mCNGOg0yXAIhvpiyqnNCdIYPN8Au28fkHTzMMnLcJ8F1Q==&lamb=2 (don’t forget to update the query string to the amount of lamb you have available in kilograms)
Just a reminder… Since both our compute and database are serverless, our costs are minimized when there is no workload. For the database, we are only charged for storage when the database is paused. Next time you need a database for your serverless architecture, consider Azure SQL database serverless.
See you next week for our solution to Challenge 4: A Big Barbecue!
0 comments