Partager via

Indexing Azure SQL Database with Azure Search

  • Article

This post will show how to index a table in an Azure SQL Database using Azure Search.

Background

I am always trying to learn more about Azure, about things that are possible.  One thing I just learned today that I had to share was the ability to use Azure Search against an Azure SQL Database.  The reason you might do this is because you need features like results ranking and relevance to add features to your solution such as autocomplete, suggestions, and relevant links.  Instead of you trying to build a bunch of SQL queries to pull that stuff out, Azure Search provides this as a service.

This post will show how Azure Search can provide ranking and relevancy capabilities over your relational SQL data.

Create the Database

My previous post, Adventure Works for Azure SQL Database, discussed how to create the Adventure Works sample database in an Azure SQL Database using the new portal.  Rather than rehash the steps, I urge you to go there and see how to create it. 

image

Create the Search Account

Using the new Azure portal, https://portal.azure.com, create a new Search account.  Provide a name and location nearest you, and choose a pricing tier.  For this demo, I can absolutely take advantage of the Free pricing tier, but if you need dedicated resources and need to scale, then you’ll need to move to the Standard tier.

image

Create the Data Source

You’ll need to use the REST API to create the data source.  You’ll need to provide values for:

  • Your Search Service – The name of your Azure Search service
  • API Key – The key found in the Azure Search service settings
  • Your SQL Database Server – The server name for your Azure SQL Database
  • Your User ID:  The user ID for the Azure SQL Database
  • Your Password:  The password for the Azure SQL Database

This template uses a database named “AdventureWorks” and the “SalesLT.Customer” table in the Adventure Works sample database.

Code Snippet

  1. POST https://<Your Search Service>.search.windows.net/datasources?api-version=2015-02-28
  2. Content-Type: application/json
  3. api-key: <Your API Key>
  4.  
  5. {
  6.     "name" : "myazuresqldatasource",
  7.     "type" : "azuresql",
  8.     "credentials" : { "connectionString" : "Server=tcp:<Your SQL Database Server>.database.windows.net,1433;Database=AdventureWorks;User ID=<Your User ID>;Password=<Your Password>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;" },
  9.     "container" : { "name" : "SalesLT.Customer" }
  10. }

As an example, I used Fiddler to compose a new POST using the values above to my search service named “kirkesearchdemo”. 

image

A successful result returns back HTTP 201 – CREATED.

Create an Azure Search Index

Now that we have the datasource named “myazuresqldatasource”, we can create an index using either the portal or the Create Index API.  Since we’re already using Fiddler, let’s use the API to create the index.

Code Snippet

  1. POST https://<Your Search Service>.search.windows.net/indexes?api-version=2015-02-28
  2. api-key: <Your API Key>
  3. Content-Type: application/json
  4.  
  5. {
  6.     "name":"customerindex",
  7.     "fields":[
  8.         {"name":"CustomerID","type":"Edm.String","searchable":false,"filterable":false,"retrievable":true,"sortable":false,"facetable":false,"key":true},
  9.         {"name":"NameStyle","type":"Edm.String","searchable":false,"filterable":false,"retrievable":true,"sortable":false,"facetable":false,"key":false},
  10.         {"name":"Title","type":"Edm.String","searchable":true,"filterable":true,"retrievable":true,"sortable":true,"facetable":true,"key":false},
  11.         {"name":"FirstName","type":"Edm.String","searchable":true,"filterable":true,"retrievable":true,"sortable":true,"facetable":true,"key":false},
  12.         {"name":"MiddleName","type":"Edm.String","searchable":true,"filterable":true,"retrievable":true,"sortable":true,"facetable":true,"key":false},
  13.         {"name":"LastName","type":"Edm.String","searchable":true,"filterable":true,"retrievable":true,"sortable":true,"facetable":true,"key":false},
  14.         {"name":"Suffix","type":"Edm.String","searchable":true,"filterable":true,"retrievable":true,"sortable":true,"facetable":true,"key":false},
  15.         {"name":"CompanyName","type":"Edm.String","searchable":true,"filterable":true,"retrievable":true,"sortable":true,"facetable":true,"key":false},
  16.         {"name":"SalesPerson","type":"Edm.String","searchable":true,"filterable":true,"retrievable":true,"sortable":true,"facetable":true,"key":false},
  17.         {"name":"EmailAddress","type":"Edm.String","searchable":true,"filterable":true,"retrievable":true,"sortable":true,"facetable":true,"key":false},
  18.         {"name":"Phone","type":"Edm.String","searchable":true,"filterable":true,"retrievable":true,"sortable":true,"facetable":true,"key":false},
  19.         {"name":"PasswordHash","type":"Edm.String","searchable":false,"filterable":false,"retrievable":false,"sortable":false,"facetable":false,"key":false},
  20.         {"name":"PasswordSalt","type":"Edm.String","searchable":false,"filterable":false,"retrievable":false,"sortable":false,"facetable":false,"key":false},
  21.         {"name":"rowguid","type":"Edm.String","searchable":false,"filterable":false,"retrievable":false,"sortable":false,"facetable":false,"key":false},
  22.         {"name":"ModifiedDate","type":"Edm.String","searchable":false,"filterable":false,"retrievable":true,"sortable":false,"facetable":false,"key":false}
  23.     ]
  24. }

Here is a capture of the command I issued using Fiddler.

image

A successful result returns back HTTP 201 – CREATED.

Create the Indexer

The final step is to create an indexer that connects the index we just created to the data source we created earlier.  Again, we use a REST API.

Code Snippet

  1. POST https://<Your Search Service>.search.windows.net/indexers?api-version=2015-02-28 HTTP/1.1
  2. api-key: <Your API Key>
  3. Content-Type: application/json
  4.  
  5. {
  6.     "name" : "myindexer",
  7.     "dataSourceName" : "myazuresqldatasource",
  8.     "targetIndexName" : "customerindex"
  9. }

And here’s a screen shot to help provide a concrete example.

image

A successful result returns back HTTP 201 – CREATED.

Testing It Out

To test it out, I first go the new Azure portal, https://portal.azure.com, and refresh the page.  I can see that my index is listed and that there are 847 documents

image

That’s great, because there are exactly 847 rows in the SalesLT.Customer table in my Azure SQL Database.

image

I created a new .NET 4.5.1 Console application (important, this version is needed for the next step).

image

I then add the Microsoft.Azure.Search NuGet package.  You can search for “Azure Search”, including PreRelease versions.

image

I created a class, Customer.cs.

Customer.cs

  1. using Microsoft.Azure.Search.Models;
  2. using System;
  3.  
  4. namespace SearchDemo
  5. {
  6.     [SerializePropertyNamesAsCamelCase]
  7.     public class Customer
  8.     {
  9.         public string CustomerID { get; set; }
  10.         public string NameStyle { get; set; }
  11.         public string Title { get; set; }
  12.         public string FirstName { get; set; }
  13.         public string MiddleName { get; set; }
  14.         public string LastName { get; set; }
  15.         public string Suffix { get; set; }
  16.         public string CompanyName { get; set; }
  17.         public string SalesPerson { get; set; }
  18.         public string EmailAddress { get; set; }
  19.         public string Phone { get; set; }
  20.         public string ModifiedDate { get; set; }
  21.  
  22.  
  23.         public override string ToString()
  24.         {
  25.             return String.Format(
  26.                 "Company: {0}, Customer: {1} {2} {3}", CompanyName, FirstName, MiddleName, LastName);
  27.         }
  28.     }
  29. }

I then implemented the Main method to simply call the Search service and return results. 

Program.cs

  1. using Microsoft.Azure.Search;
  2. using Microsoft.Azure.Search.Models;
  3. using System;
  4.  
  5. namespace SearchDemo
  6. {
  7.     class Program
  8.     {
  9.         static void Main(string[] args)
  10.         {
  11.             string searchServiceName = "kirkesearchdemo"; // Put your search service name here.
  12.             string apiKey = "REDACTED";
  13.  
  14.             SearchServiceClient serviceClient = new SearchServiceClient(searchServiceName, new SearchCredentials(apiKey));
  15.  
  16.             SearchIndexClient indexClient = serviceClient.Indexes.GetClient("customerindex");
  17.  
  18.  
  19.             Console.WriteLine("{0}", "Searching using 'bike store'...\n");
  20.             SearchDocuments(indexClient, searchText: "bike store");
  21.  
  22.             Console.WriteLine("\n{0}", "Filter for salesperson adventure-works\\pamela0...\n");
  23.             SearchDocuments(indexClient, searchText: "*", filter: "SalesPerson eq 'adventure-works\\pamela0'");
  24.  
  25.             Console.WriteLine("{0}", "Complete.  Press any key to end application...\n");
  26.             Console.ReadKey();
  27.         }
  28.  
  29.         private static void SearchDocuments(SearchIndexClient indexClient, string searchText, string filter = null)
  30.         {
  31.             // Execute search based on search text and optional filter
  32.             var sp = new SearchParameters();
  33.  
  34.             if (!String.IsNullOrEmpty(filter))
  35.             {
  36.                 sp.Filter = filter;
  37.             }
  38.  
  39.             DocumentSearchResponse<Customer> response = indexClient.Documents.Search<Customer>(searchText, sp);
  40.             foreach (SearchResult<Customer> result in response)
  41.             {
  42.                 Console.WriteLine(result.Document);
  43.             }
  44.         }
  45.     }
  46. }

I run the program, and see results that are already ranked for me according to relevance, with “A Bike Store” being a more relevant match than “Fifth Bike Store”.

image

Let’s filter based on SalesPerson=’adventureworks\pamela0’.  We receive results, and could then go back to the database to verify that the results are accurate. 

image

Refreshing the Index

A search index is not a live representation of data, it reflects the state when the index was last updated.  To demonstrate this, let’s update record 1 in the database, “A Bike Store”, to change the owner’s name to Kirk A Evans.

image

We then run our application again.  Notice that even though we changed the value in the database, it still shows as Orlando N. Gee.

image

This indexer does not have a schedule, we’ll need to update the index either on a scheduled basis or by some event occurring.  For instance, you might do this using a scheduled job, such as a WebJob, or you might want to let Azure Search manage the schedule for you.  Another alternative is not to run on a scheduled basis, but to update it yourself when your application writes to the database.  As an example, it might also push a message to a queue signaling that a backend process needs to update the index, enabling you to update the index when data is updated.

To update manually or via a scheduled process outside of Azure Search, we need to run the following REST operation:

Run Indexer

  1. POST https://<Your Search Service>.search.windows.net/indexers/myindexer/run?api-version=2015-02-28 HTTP/1.1
  2. api-key: <Your API Key>

Once we run the command, we execute our sample again and verify that the name now reflects the update in the database.

image

You can also let Azure Search manage the schedule for you.  The documentation for Connecting Azure SQL Database to Azure Search Using Indexers shows that you can update to run on a schedule using an ISO8601 time duration format.  We can update the existing indexer:

Update Indexer with Schedule

  1. PUT https://<Your Search Service>.search.windows.net/indexers/myindexer?api-version=2015-02-28 HTTP/1.1
  2. api-key: <Your API Key>
  3. Content-Type: application/json
  4.  
  5.  
  6. {
  7.     "name" : "myindexer",
  8.     "dataSourceName" : "myazuresqldatasource",
  9.     "targetIndexName" : "customerindex",
  10.     "schedule" : { "interval" : "PT1H", "startTime" : "2015-03-06T00:00:00Z" }
  11. }

The format “PT1H” indicates that we will recur every 1 hour, and the start time will be March 6th, 2016 UTC time.   Here is a screen capture of the request and response in Fiddler.

image

I can now check the status of the indexer using an HTTP GET.

GET Indexer Status

  1. GET https://<Your Search Service>.search.windows.net/indexers/myindexer/status?api-version=2015-02-28 HTTP/1.1
  2. api-key: <Your API Key>

A screen shot of Fiddler shows the request/response pair as an example.

image

There are additional capabilities that are possible, such as using change tracking.  For more information, see the documentation at Connecting Azure SQL Database to Azure Search Using Indexers.  I think this is a great opportunity for us application developers to introduce advanced search capabilities into applications with very little work or ongoing maintenance. 

For More Information

Connecting Azure SQL Database to Azure Search Using Indexers

Create Index API

Indexer Operations REST API

Adventure Works for Azure SQL Database

Comments

  • Anonymous
    April 09, 2015
    I can't get past the "Create the Indexer" I'm getting a 400 http status code

  • Anonymous
    April 09, 2015
    The comment has been removed