This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.
Introduction
Vector databases are gaining quite a lot of interest lately. Using text embeddings which is a bunch of numbers representing that text and vector operations makes it extremely easy to find similar "things". Things can be articles, photos, products…everything. As one can easily imagine, this ability is great for implementing suggestions in applications easily. The applications are many from providing suggestions on similar articles or other products that may be of interest to quickly finding and grouping similar items.
Scenario
Imagine you want to build a cool recommendation system that uses LLMs to analyze the user's input and recommends the most similar items from your database. How can you do such a thing and give your users valuable recommendations? You may use Azure OpenAI to create a tool that can help people find the most relevant things for their interests. Before you do that you'll need to convert the items into embeddings using Azure OpenAI's natural language processing capabilities. Then, use vector search to compare the embeddings with a query topic and rank the items by similarity.
You want to quickly and easily discover the items that match your goals and preferences, without having to read all that data manually using simple, scalable, and fast technologies.
What will you learn?
In this blog, you'll learn to:
- Fork a GitHub repository.
- Create an Azure OpenAI cloud resource.
- Create a deployment for an embedding LLM.
- Create an Azure SQL server and database cloud resource.
- Create database user credentials using T-SQL in the Azure SQL database Query editor.
- Clone the repository locally.
- Execute a SQL script using sqlcmd locally to set up your database.
- Create an Azure function cloud resource.
- Configure Azure function Application settings.
- Configure a GitHub workflow to run successfully.
- Create an Azure Static Web App cloud resource.
- Configure Database connection for the Azure Static Web App.
- Insert data into the database using the Azure database Query editor.
What is the main objective?
Build a recommender full stack application using OpenAI and Azure SQL.
Prerequisites
- An Azure subscription.
- If you don’t already have one, you can sign up for an Azure free account.
- For students, you can use the free Azure for Students offer which doesn’t require a credit card only your school email.
- Access to Azure OpenAI in the desired Azure subscription.
- Azure OpenAI requires registration and is currently only available to approved enterprise customers and partners. See Limited access to Azure OpenAI Service for more information.
- You can apply for access to Azure OpenAI by completing the form at https://aka.ms/oai/access.
- Code sample from Azure-Samples/azure-sql-db-session-recommender . (github.com).
- Download and install Git.
- Download and install sqlcmd. If your terminal doesn't recognize it, make sure to add it to your environment variables.
Summary of the steps:
Step 1: Fork the sample Repository on GitHub
Step 2: Open the Azure portal and create a new Resource group
Step 3: Create an Azure OpenAI resource and Deploy an embedding Model
Step 4: Create an Azure SQL server and database
Step 5: Create an Azure Function App
Step 6: Create an Azure Static Web App
Step 1: Fork the sample Repository on GitHub
GitHub is a cloud platform for hosting and sharing Git Projects. It enables collaboration between developers online.
In this step, you create a copy from the source code on your GitHub account to be able to edit it and use it later.
Complete the following steps to fork a copy from the sample repository on GitHub:
1. Visit the sample github.com/Azure-Samples/azure-sql-db-session-recommender in your browser and sign in.
2. Select Fork from the top of the sample page.
3. Select an owner for the fork then, select Create fork.
Congratulations! You successfully created a fork. In the next step, you'll create a resource group to logically group your resources.
Step 2: Open the Azure portal and create a new Resource group
A resource group is a container that holds related resources for an Azure solution. The resource group can include all the resources for the solution or only those resources that you want to manage as a group.
In this step, you'll:
- Open the Azure Portal.
- Create a new Resource group in the Azure Portal.
Open the Azure Portal
1. Visit the Azure Portal https://portal.azure.com in your browser and sign in.
Now you are inside the Azure portal!
Create a new Resource group in the Azure Portal
In this step, you create a resource group that enables you to group your cloud resources.
Complete the following steps to provision a Resource group in the Azure portal:
1. Type resource group in the search bar at the top of the portal page and select Resource groups from the available options.
2. Select Create from the toolbar to create a new resource group.
3. Add the following information to create a resource:
| What | Value |
| Subscription | Use the same subscription you used to apply for Azure OpenAI access. |
| Name | Enter a unique name. |
| Region | Select a region close to you for best response times. For example, Select East US. Note that Azure OpenAI is not available across all regions, you can check the regional availability here. |
6. Select the name of the resource group to open it. Here, you can create new resources and manage existing ones.
Congratulations! You successfully created a resource group that enables you to create a logical grouping in the cloud for your resources. In the next step, you'll create an Azure OpenAI resource to be able to interact with different large language models.
Step 3: Create an Azure OpenAI resource and Deploy an embedding Model
Azure OpenAI Service provides REST API access to OpenAI's powerful language models including the GPT-4, GPT-35-Turbo, and Embeddings model series.
In this step, you'll:
- Create an Azure OpenAI resource in the Azure portal.
- Deploy an embedding Model in Azure OpenAI studio.
Create an Azure OpenAI resource in the Azure portal
In this step, you create an Azure OpenAI Service resource that enables you to interact with different large language models (LLMs).
Complete the following steps to provision an Azure OpenAI resource in the Azure portal:
1. Inside your resource group, select Create.
2. Type openai in the search bar of the Marketplace page and select Create Azure OpenAI from the options that appear.
3. Add the following information to create a resource:
| What | Value |
| Subscription | Use the same subscription you used to apply for Azure OpenAI access. |
| Resource group | Use the resource group you created in the previous step. |
| Region | Select a region close to you for best response times. For example, Select East US. |
| Name | The name must be globally unique. |
| Pricing tier |
Currently, S0 is the only available tier.
|
5. Now that the basic information is added, select Next to confirm your details and proceed to the next page.
6. Select Next to confirm your network details.
7. Select Next to confirm your tag details.
8. Confirm your configuration settings and select Create to start provisioning the resource. Wait for the deployment to finish.
9. After the deployment finishes, select Go to resource to inspect your created resource. Here, you can manage your resource and find important information like the endpoint URL and API keys.
Congratulations! You successfully provisioned an Azure OpenAI resource that enables you to interact with OpenAI models. In the next step, you'll deploy an embedding model that creates the vectors from the data.
Deploy an embedding Model in Azure OpenAI studio
In this step, you create an Azure OpenAI embedding model deployment. Creating a deployment on your previously provisioned resource allows you to generate text embeddings (i.e. numerical representation for text).
Complete the following steps to deploy an embedding model in the Azure OpenAI studio:
1. Select Go to Azure OpenAI Studio from the toolbar to open the studio.
2. Select Create new deployment to go to the deployments tab.
3. Select + Create new deployment from the toolbar. A Deploy model window opens.
4. Add the following information to create a resource:
| What | Value |
| Select a model | Select text-embedding-ada-002. |
| Model version | Select 2. |
| Deployment name | Add a name that's unique for this cloud instance. For example, embedding-model because this model type is optimized for creating embeddings. |
5. Select Create.
Congratulations! You successfully created a model deployment that enables you to create embeddings. In the next step, you'll provision your Azure SQL server and database that enables you to store and retrieve your data.
Step 4: Create an Azure SQL server and database
Azure SQL is a family of managed, secure, and intelligent products that use the SQL Server database engine in the Azure cloud.
In this step, you'll:
- Create an Azure SQL server and database in the Azure portal.
- Create database user credentials using the Azure SQL Database Query editor.
- Clone the sample code locally.
- Execute a SQL script using sqlcmd locally to set up the database.
Create an Azure SQL server and database in the Azure portal
In this step, you create an Azure SQL server with both Microsoft Entra and SQL authentication then, you create a database with public access enabled which allows you to access it.
Complete the following steps to create an Azure SQL server and database in the Azure portal:
1. Inside your resource group, select Create.
2. Type sql database in the search bar of the Marketplace page and select Create SQL Database from the options that appear.
3. Perform the following tasks:
| What | Value |
| Subscription | Use the same subscription you used to apply for Azure OpenAI access. |
| Resource group | Use the same resource group you created before. |
| Database name | Enter a unique name for your database. For example, sqldb. |
| Server | Select Create new to create a new SQL server. Further details are provided below. |
| Want to use SQL elastic pool? | Select No. This option configures a scalable cost-effective pool that manages multiple databases with varying and unpredictable usage demands. |
| Workload environment | Select Development. This option configures a cheaper compute and storage for you during the "development" phase. Once you want to move to production you can scale it up. |
| Compute + storage | Select General Purpose - Serverless. This option uses a cheap serverless compute that has a one-hour auto-pause delay. The auto-pause delay option defines the period of time the database must be inactive before it is automatically paused. |
| Backup storage redundancy | Select Locally-redundant backup storage. This option incurs less cost and is appropriate for pre-production environments that do not require the redundance of zone- or geo-replicated storage. |
4. In the Create SQL Database Server, perform the following tasks to create a server:
| What | Value |
| Server name | Enter a globally unique name. |
| Location | Select a region close to you for best response times. For example, Select East US. |
| Authentication method | Select Use both SQL and Microsoft Entra authentication. This option enables you to log into your database using your email and normal SQL authentication with a username and a password. |
| Set Microsoft Entra admin | Select Set admin, search for your email, select your email and confirm by selecting the Select button. |
| Server admin login | Enter a username for your database. For example, enter sqlAdmin. |
| Password | Enter a strong password and save it in a secure place. |
| Confirm password | Enter the same password to confirm it. |
5. Select OK to confirm your selections and text entries.
6. Select Next : Networking > to move to the networking configuration parameters.
7. In the Networking section, perform the following tasks to configure the server's network:
| What | Comment |
| Connectivity method | Select Public endpoint. A private endpoint will only work inside a virtual network. |
|
Allow Azure services and resources to access this server
|
Select Yes. This enables other cloud resources to access this resource. |
| Add current IP address | Select Yes. This adds your IP address to the firewall allowed list. |
| Connection policy | Select Default - Uses Redirect policy for all client connections originating inside of Azure (except Private Endpoint connections) and Proxy for all client connections originating outside Azure. |
| Minimum TLS version | Select TLS 1.2. |
8. Select Review + create.
9. Confirm your configuration settings and select Create to start provisioning the resource. Wait for the deployment to finish.
10. After the deployment finishes, select Go to resource to inspect your created resource. Here, you can manage your resource and find important information like the connection strings for your database.
Congratulations! You successfully created an Azure SQL server and database. In the next step, you will create a database user to use it in allowing access from other cloud resources.
Create database user credentials using the Azure SQL Database Query editor
In this step, you create a database user in the Query editor to use it in allowing access from other cloud resources.
Complete the following steps to create a new database username and password in the Azure SQL database Query editor:
1. Inside your database, select Query editor from the left-side navigation menu.
2. Select your preferred authentication method to log into the database and execute SQL commands.
3. In the query editor, execute the following command to make sure the user doesn't exit.
| drop user if exists "appUser" go |
5. In the query editor, execute the following command to create a new user and password. Make sure to change the values and save them in a secure place.
| create user appUser with password = 'veryStrongPassword123' go |
7. In the query editor, execute the following command to give administration permission to the user.
| alter role db_owner add member appUser go |
Clone the sample code locally
In this step, you clone a copy locally from the GitHub sample you forked to be able to execute commands locally.
Complete the following steps to create a local copy from the repository:
1. Visit the repository you forked on your account.
2. Select Code, then Local and copy the URL.
3. Open git bash locally, and execute the following command to clone the repository. Make sure to use your URL not the one in the command below.
| git clone https://github.com/john0isaac/azure-sql-db-session-recommender.git |
Execute a SQL script using sqlcmd locally to set up the database
4. Copy your endpoint URL and one of the two keys to your clipboard.
| What | Value |
| OpenAIUrl | Add your Azure OpenAI endpoint URL and remove the forward slash from the end of the URL. |
| OpenAIKey | Add one of the two keys you copied from the Azure OpenAI Keys and Endpoint page. |
| APPUSERNAME | Add the username you created in a previous step from the Query Editor. |
| APPUSERPASSWORD | Add the password you created in a previous step with the username in the Query Editor. |
| OpenAIDeploymentName | Add the name of your embedding model deployment. For example, enter embedding-model. |
| sqlcmd --server session-recommender-demo.database.windows.net --database-name sqldb --user-name appUser --password veryStrongPassword123 --input-file ./database/setup-database.sql |
Congratulation! You successfully executed an SQL script to set up your database with the required tables and procedures. In the next step, you'll create a function that gets the embeddings for your data from OpenAI.
Step 5: Create an Azure Function App
Azure Functions is a serverless solution that allows you to write less code, maintain less infrastructure, and save on costs. Instead of worrying about deploying and maintaining servers.
In this step, you'll:
- Create an Azure Function App in the Azure portal.
- Configure Azure Function Application settings in the Azure portal.
- Configure the Workflow to deploy your function in GitHub.
Create an Azure Function App in the Azure portal
In this step, you create an Azure Function which enables you to interact with serverless functions that trigger based on a specific time or event like a database insert operation.
Complete the following steps to create an Azure Function App in the Azure portal:
1. Inside your resource group, select Create.
2. Type function app in the search bar of the Marketplace page and select Create Function App from the options that appear.
3. Perform the following tasks:
| What | Value |
| Subscription | Use the same subscription you used to apply for Azure OpenAI access. |
| Resource group | Use the same resource group you created before. |
| Function App name | Enter a unique name for your database. For example, session-recommender-function. |
| Server | Select Create new to create a new SQL server. Further details are provided below. |
| Do you want to deploy code or container image? | Select Code. This option specifies whether your deployment consists of code or a container. |
| Runtime stack | Select .NET. |
| Version | Select 6. |
| Region | Select East US. This is the region where the rest of the resources you created reside. |
| Operating System | Select Linux. |
| Hosting options and plans | Select Consumption. |
4. Select Next : Storage > to move to the storage settings.
5. Create a new storage account or proceed with a preexisting one.
6. Select Next : Networking > to move to the networking settings.
7. Make sure that Enable public access is On.
8. Select Next : Monitoring > to move to the monitoring settings.
9. Enable Application insights or disable it.
10. Select Next : Deployment > to move to the deployment settings.
11. Perform the following tasks to enable continuous deployment from GitHub:
| What | Value |
| Continuous deployment | Select Enable. |
| GitHub account | Select your GitHub account. |
| Organization | Select your organization. If you are using your personal account then select it. |
| Repository | Select azure-sql-db-session-recommender. |
| Branch | Select main. |
12. Select Review + create.
13. Confirm your configuration settings and select Create to start provisioning the resource. Wait for the deployment to finish.
14. After the deployment finishes, select Go to resource to inspect your created resource. Here, you can manage your resource and find important information like the function URL and application settings.
Congratulation! You successfully created an Azure Function App. In the next step, you will configure the Function Application settings and add your environment variables.
Configure Azure Function Application settings in the Azure portal
In this step, you configure the Application settings to make the function able to communicate with other cloud resources.
Complete the following steps to configure the Application settings of an Azure Function App in the Azure portal:
1. In the Azure Function App, select Configuration from the left side menu.
2. Select + New application setting to add new environment variables to the function configuration.
3. Add the following names and values one by one and select Ok. Make sure to add your own values.
| Name | Value |
| AzureSQL.ConnectionString | Server=session-recommender-demo.database.windows.net; Database=sqldb; User=appUser; Password=veryStrongPassword123 |
| AzureOpenAI.Endpoint | https://session-recommender-openai-demo.openai.azure.com/ |
| AzureOpenAI.Key | ADD-YOUR-OPENAI-KEY |
| AzureOpenAI.DeploymentName | embedding-model |
4. Once you add the four variables, select Save to confirm your changes.
Congratulations! You successfully configured your application settings. In the next step, you will configure the GitHub deployment workflow to make it work successfully.
Configure the Workflow to deploy your function in GitHub
In this step, you modify the GitHub deployment workflow to point to the folder that contains your Function configuration.
Complete the following steps to configure the workflow to deploy your function in GitHub:
1. Visit your forked repository on GitHub and notice the failing workflow.
2. Open the function workflow file.github/workflows/main_session-recommender-demo.yml and select the pen icon to edit the file.
3. Modify the AZURE_FUNCTIONAPP_PACKAGE_PATH to point to the function folder "./func".
4. Select Commit changes, and review your commit message and description. Select Commit changes.
5. Select actions to review the workflow run status.
Congratulations! You successfully configured your function. In the next step, you will create a static web app to host your React frontend application.
Step 6: Create an Azure Static Web App
Azure Static Web Apps is a service that automatically builds and deploys full stack web apps to Azure from a code repository.
In this step, you'll:
- Create an Azure static web app in the Azure portal.
- Configure the Azure static web app database connection in the Azure portal.
- Insert a test sample in the database using the Azure SQL database Query editor.
Create an Azure static web app in the Azure portal
In this step, you create an Azure Static Web App and connect it with your GitHub account to deploy a React application.
Complete the following steps to configure the workflow to deploy your function in the Azure portal:
1. Inside your resource group, select Create.
2. Type function app in the search bar of the Marketplace page and select Create Function App from the options that appear.
3. Perform the following steps to configure your static web app.
| What | Comment |
| Subscription | Use the same subscription you used to apply for Azure OpenAI access. |
| Resource group | Use the same resource group you created before. |
| Name | Enter a unique name for your application. |
| Plan type | Select Free. You can scale it up later. |
| Region | Select a region close to you for best response times. For example, select East US 2. |
4. Scroll down to the GitHub settings.
5. Perform the following steps to set up the GitHub continuous deployment workflow.
| What | Comment |
| Source | Select Enable. |
| GitHub account | Select your GitHub account. |
| Organization | Select your organization. If you are using your personal account then select it. |
| Repository | Select azure-sql-db-session-recommender. |
| Branch | Select main. |
| Build Presets | Select React. |
| App location | Enter "/client". |
| Api location | Leave empty. |
| Output location | Enter "dist". |
6. Select Review + create.
7. Confirm your configuration settings and select Create to start provisioning the resource. Wait for the deployment to finish.
8. After the deployment finishes, select Go to resource to inspect your created resource. Here, you can manage your resource and find important information like the website URL and database connection settings.
Congratulations! You successfully created a static web app and deployed your website. In the next step, you will connect the static web app with the database to be able to access your data.
Configure the Azure static web app database connection in the Azure portal.
In this step, you enable communication between your static web app and database using the databases link.
1. In the static web app, select Database connection from the left side navigation menu.
2. Select Link existing database to start the database linking process.
3. Perform the following tasks to configure the connection between the website and database:
| What | Comment |
| Database type | Select Azure SQL Database. |
| Subscription | Use the same subscription you used to apply for Azure OpenAI access. |
| Resource group | Add the name of the resource group that contains your SQL server. |
| Resource name | Add the name of your SQL server. |
| Database name | Add the name of your database. For example, enter sqldb. |
| Username | Add the username you created before. Enter appUser. |
| Password | Add the password you used while creating the new user appUser. |
4. Select the acknowledgement then, select Link to confirm your selections. Note that the connection configuration file exists on the repository you cloned staticwebapp.database.config.json.
Congratulations! You successfully established a link between your website and database. In the next step, you will insert some data to trigger the function and get the embedding vector.
Insert a test sample in the database using the Azure SQL database Query editor
In this step, you insert some data into the web.sessions table which will trigger the Azure function to get the embeddings from OpenAI.
Complete the following steps to create a new record inside your database using the Azure SQL database Query editor:
1. Inside your database, select Query editor from the left-side navigation menu.
2. Select your preferred authentication method to log into the database and execute SQL commands.
3. In the query editor, execute the following command to insert a new record in the database.
| insert into web.sessions (title, abstract) values ('Building a session recommender using OpenAI and Azure SQL', 'In this fun and demo-driven session you’ll learn how to integrate Azure SQL with OpenAI to generate text embeddings, store them in the database, index them and calculate cosine distance to build a session recommender. And once that is done, you’ll publish it as a REST and GraphQL API to be consumed by a modern JavaScript frontend. Sounds pretty cool, uh? Well, it is!') |
6. Note that there is only one session available for you to query the one you inserted before.
7. Enter any related word to the data you have inserted and notice the results.
Congratulations!! You successfully built the full application.
Clean Up
Once you finish experimenting on Microsoft Azure you might want to delete the resources to not consume any more money from your subscription.
You can delete the resource group and it will delete everything inside it or delete the resources one by one that's totally up to you.
Conclusion
Congratulations! You've learned how to create an Azure OpenAI resource, how to deploy an embedding model from the Azure OpenAI studio, how to create an Azure SQL database and server, how to use the query editor to create a database user, how to execute SQL scripts locally using sqlcmd to setup the database, how to create an Azure Function and configure continuous deployment with GitHub, and how to create an Azure Static Web App and establish a database connection. By using these technologies you can explore the power of OpenAI and Azure SQL in generating text embeddings and finding the similarity between "things".
Next steps
Documentation
- Subscriptions, licenses, accounts, and tenants for Microsoft's cloud offerings
- Manage Azure resource groups by using the Azure portal
- What is Azure OpenAI Service? - Azure AI services
- Azure OpenAI Service embeddings - Azure OpenAI - embeddings and cosine similarity
- What is Azure SQL? - Azure SQL
- Query SQL Database with Query editor in the Azure portal - Azure SQL Database
- Azure Functions Overview
- What is Azure Static Web Apps?
- Connecting to a database with Azure Static Web Apps
- Overview to Azure data API builder
Training Content
- Develop Generative AI solutions with Azure OpenAI Service
- Azure SQL fundamentals
- Create serverless applications
- Azure Static Web Apps
Found this useful? Share it with others and follow me to get updates on:
- Twitter (twitter.com/john00isaac)
- LinkedIn (linkedin.com/in/john0isaac)
Feel free to share your comments and/or inquiries in the comment section below..See you in future demos!