How to use Azure Cognitive Search for local database?

Question

We are trying to utilize Azure Cognitive Search to enable search functionality on our local database. We have a solution created in .Net to establish the connection with local DataSource. Local DataSource may be SQL Server, NOSQL, Access or Excel data.

For example, we have 2 million rows in excel. we want to read the data and search certain value in those records.

How to utilize Azure Cognitive Search to implement the same?

Answer 1

AFAIK, Azure Cognitive Search cannot search local data sources. It can only search the data that is there in an Index created inside a Cognitive Search account.

You would need to somehow bring this data in the Index. One solution would be to upload your Excel files in Azure Blob Storage and create a Data Source using that. Then you would create an Index and an Indexer which will connect this Data Source to the Index and populate the Index with the data in this Data Source.

To elaborate further, let's say that your Excel file has 4 columns - Id, FirstName, LastName and EmailAddress. Here's what you would do:

1. Create an Index - First you would create an Index. Index is an entity in Azure Cognitive Search that contains the data which you want to search. In your case, you will create an Index with 4 fields that map to the columns in your Excel File.
2. Upload Files in Blob Storage - Next you would upload this Excel file in Azure Blob Storage. You may need to convert that file into CSV format before uploading and upload that CSV file.
3. Create a Data Source - Next you would create a Data Source in your Cognitive Search account. As the name suggests, a Data Source is an entity that holds the original data. You would create a Blob Storage type Data Source.
4. Create an Indexer - Considering the objective is to read the data from a Data Source and bring it into an Index, you would create an Indexer. Think of an Indexer as a bridge between your Index and Data Source which reads the data from the Data Source and populate the Index.
5. Run Indexer - Last step would be to run the Indexer. If everything is configured properly, once an Indexer runs you should see data in an Index shortly.

If the data in your Excel file does not change, other option could be to just read the data from Excel file and populate the Index manually. You can use Cognitive Search SDK for that purpose.