The integration of AI-powered search with robust database management systems like Azure SQL is a game-changer for organizations that need to optimize their data retrieval processes. With Azure AI Search and Azure SQL, companies can improve how users search for and retrieve data, resulting in a faster, more intuitive experience. This blog aims to demystify the process of integrating Azure AI Search with Azure SQL, covering both the conceptual and practical aspects along with code-level implementation.

What is Azure AI Search?

Azure AI Search is a powerful, AI-driven search-as-a-service solution from Microsoft. It allows developers to incorporate sophisticated search capabilities into their applications, making it easier for users to find relevant information from vast amounts of data. Key features include natural language processing (NLP), cognitive search (which uses AI to extract insights from structured and unstructured data), filters, and facets.

Why Integrate Azure AI Search with Azure SQL?

Azure SQL Database is a fully managed relational database service in the cloud. When integrated with Azure AI Search, it enables the following:

• Enhanced Search Capabilities: Users can search data stored in Azure SQL using advanced search techniques like keyword search, fuzzy matching, NLP, and faceting.
• Cognitive Skills Integration: Extract deeper insights from text, images, or documents stored in Azure SQL using AI models.
• Improved User Experience: Create search-driven applications that are more intuitive and responsive, allowing users to find data faster.

Architecture Overview

The integration follows a simple architecture:

1. Azure SQL Database: Stores structured or semi-structured data.
2. Azure Cognitive Search: Indexes the data from Azure SQL and provides the search functionality.
3. Azure Function or Logic App: Optionally, acts as a connector or orchestrator between Azure SQL and Azure Cognitive Search for tasks like triggering reindexing.
4. Application: A frontend application that interacts with Azure AI Search to present results to users.

Step-by-Step Integration Process

1. Setting Up the Azure SQL Database

Ensure that you have an Azure SQL Database running. For the purposes of this tutorial, we will use a table with sample data.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    ProductDescription NVARCHAR(MAX),
    Price DECIMAL(10, 2),
    Category NVARCHAR(50),
    AvailableStock INT
);

INSERT INTO Products (ProductID, ProductName, ProductDescription, Price, Category, AvailableStock)
VALUES (1, 'Laptop', 'A high-performance laptop', 1299.99, 'Electronics', 50),
       (2, 'Headphones', 'Noise-cancelling headphones', 199.99, 'Accessories', 150),
       (3, 'Coffee Maker', '12-cup coffee maker', 49.99, 'Home Appliances', 75);

2. Provisioning Azure Cognitive Search

1. In the Azure Portal, create an Azure Cognitive Search instance.
2. Set the pricing tier based on your needs (for testing purposes, the Free tier is sufficient).
3. Once created, note down the Service Name and API Key.

3. Defining the Index

Azure Cognitive Search requires an index that defines the structure of the data you want to search.

Create an Index Using the Azure Portal

1. Navigate to your Azure Cognitive Search resource.
2. In the left pane, under “Data,” click on “Indexes” and create a new index.
3. Define fields that match the columns of your Azure SQL table:

{
  "name": "product-index",
  "fields": [
    { "name": "ProductID", "type": "Edm.Int32", "key": true, "filterable": true },
    { "name": "ProductName", "type": "Edm.String", "searchable": true, "sortable": true },
    { "name": "ProductDescription", "type": "Edm.String", "searchable": true },
    { "name": "Price", "type": "Edm.Double", "filterable": true, "sortable": true },
    { "name": "Category", "type": "Edm.String", "filterable": true },
    { "name": "AvailableStock", "type": "Edm.Int32", "filterable": true, "sortable": true }
  ]
}

4. Save the index. You’ll use this to map the data from Azure SQL to Azure Cognitive Search.

4. Connecting Azure SQL to Cognitive Search

Azure Cognitive Search can pull data from an Azure SQL Database using a Data Source object.

Create a Data Source Using the Azure Portal

1. In your Azure Cognitive Search resource, click on “Data sources” and create a new data source.
2. Choose Azure SQL as the data source type.
3. Provide your Azure SQL Database connection details, including the connection string.
4. Choose the table from which to pull data (e.g., Products).
5. Define a schedule for data refresh if you need automatic updates.

Create Data Source Using REST API

Alternatively, you can create a data source using the REST API:

POST https://<search-service-name>.search.windows.net/datasources?api-version=2020-06-30
Content-Type: application/json
api-key: <api-key>

{
  "name": "azure-sql-datasource",
  "type": "azuresql",
  "credentials": {
    "connectionString": "Server=tcp:<server-name>.database.windows.net;Database=<db-name>;User ID=<username>;Password=<password>;"
  },
  "container": {
    "name": "Products"
  }
}

5. Indexer to Sync Data from Azure SQL

Once the data source is ready, an Indexer is needed to extract the data from Azure SQL and index it in Azure Cognitive Search.

Create an Indexer in the Portal

1. Go to the “Indexers” section of the Azure Cognitive Search resource.
2. Click on “Add Indexer.”
3. Select the data source and index you created earlier.
4. Define the indexer frequency (how often it should sync the data).

Create Indexer Using REST API

POST https://<search-service-name>.search.windows.net/indexers?api-version=2020-06-30
Content-Type: application/json
api-key: <api-key>

{
  "name": "product-indexer",
  "dataSourceName": "azure-sql-datasource",
  "targetIndexName": "product-index",
  "schedule": { "interval": "PT1H" }
}

6. Querying the Index

Once data is indexed, you can query the Azure Cognitive Search index through an API.

Example: Query Using REST API

GET https://<search-service-name>.search.windows.net/indexes/product-index/docs?search=laptop&api-version=2020-06-30
Content-Type: application/json
api-key: <api-key>

The response would contain search results matching the query, ranked by relevance.

Code-Level Implementation in C#

You can use the Azure Search SDK to interact programmatically with Azure Cognitive Search. Below is a simple example in C# to create the index, indexer, and query data.

using Azure;
using Azure.Search.Documents;
using Azure.Search.Documents.Indexes;
using Azure.Search.Documents.Indexes.Models;

public class AzureSearchIntegration
{
    private static readonly string serviceName = "<search-service-name>";
    private static readonly string apiKey = "<api-key>";
    private static readonly Uri serviceEndpoint = new Uri($"https://{serviceName}.search.windows.net");

    // Create a Search Index Client
    SearchIndexClient indexClient = new SearchIndexClient(serviceEndpoint, new AzureKeyCredential(apiKey));

    // Create Search Client
    SearchClient searchClient = new SearchClient(serviceEndpoint, "product-index", new AzureKeyCredential(apiKey));

    public async Task CreateIndex()
    {
        var index = new SearchIndex("product-index")
        {
            Fields =
            {
                new SimpleField("ProductID", SearchFieldDataType.Int32) { IsKey = true, IsFilterable = true },
                new SearchableField("ProductName") { IsSortable = true },
                new SearchableField("ProductDescription"),
                new SimpleField("Price", SearchFieldDataType.Double) { IsFilterable = true, IsSortable = true },
                new SimpleField("Category", SearchFieldDataType.String) { IsFilterable = true },
                new SimpleField("AvailableStock", SearchFieldDataType.Int32) { IsFilterable = true, IsSortable = true }
            }
        };

        await indexClient.CreateOrUpdateIndexAsync(index);
    }

    public async Task SearchProducts(string searchText)
    {
        var options = new SearchOptions { IncludeTotalCount = true };
        SearchResults<Product> results = await searchClient.SearchAsync<Product>(searchText, options);

        await foreach (SearchResult<Product> result in results.GetResultsAsync())
        {
            Console.WriteLine(result.Document.ProductName);
        }
    }
}

public class Product
{
    public int ProductID { get; set; }
    public string ProductName { get; set; }
    public string ProductDescription { get; set; }
    public double Price { get; set; }
    public string Category { get; set; }
    public int AvailableStock { get; set; }
}

Conclusion

Integrating Azure AI Search with Azure SQL transforms the search experience by leveraging the advanced capabilities of AI to enhance data retrieval. By following the steps outlined in this blog, you can create a robust search solution that provides users with faster, more relevant results. Whether you use the Azure Portal or REST APIs, the integration process is flexible and can be tailored to meet your specific needs. With Azure Cognitive Search, you can unlock new levels of efficiency and user satisfaction in your applications.

Categories: Azure

Tags: AIIntegration, AzureAI, AzureSQL, DataRetrieval, TechInnovation