How to index selected tables from azure sqldatabase using azure cognitive search

Question

I have Azure sqlDatabase it contains 90 tables, i want to create cognitive search on only 11 tables programmatically in js, i am using free version, is there possible way to index selected tables from sqldatabase, for azure sqldatabase in datasource configuration we have to mention table name or view, here i want to index 11 tables each table contains different data, if i create view by combining columns from 11 tables, and in index configuration i have to define schema based on the columns present in the table (11 tables) it would be complex, is there any possible way to do this, let me know.

Answer 1

Hi,

Indexes on cognitive search are meant to be an indexed selection of similar data. As explained here: https://learn.microsoft.com/en-us/azure/search/search-what-is-an-index#content-of-a-search-index an index is like a table and should map to a concept like "article for a news organisation." Due to the nature of SQL I think it is expected that you will either build a view to bring data and any extra metadata together as one entity or just use the table directly. This is the recommendation likely leading you to need to upgrade to the basic tier with 15 indexes available.

However, you do mention being on the free plan hence only 3 indexes are available to you. If that is a limit and you can not get a basic tier instance then I would suggest splitting the tables into 3 groups which will be joined into a view and then a single index. These will still be big indexes and it is gonna take some work to set them up but it will make your queries more narrow and utilise as much of the service as you can.

By the sounds of it you have figured out the Azure SQL datasource setup but more info here: https://learn.microsoft.com/en-us/azure/search/search-howto-connecting-azure-sql-database-to-azure-search-using-indexers.

Hope this is helpful.

TL;DR there is no way round this. SQL imports a table/view at a time so the best option is to split the data into as small as possible logical groupings.