Hi ! I tried to make an index with multiple data sources following this tutorial https://learn.microsoft.com/en-us/azure/search/tutorial-multiple-data-sources The data source I will use are Azure SQL Database and File Share Index making for file share was mentioned in this link https://learn.microsoft.com/en-us/azure/search/search-file-storage-integration The problem here is, how to use both of them (Azure SQL Database and File Share) as the data source ? Firstly, they don't have key column with same data, but data from SQL has filename column and the file share will have metadata_storage_name, but both are not key column will this work ? The idea is to combine content data from file share indexer, to the data from SQL. I tried to run indexer twice from Azure portal both targeting same index, first from SQL indexer, second from File indexer. But the content is still empty I already use field mapping from file indexer for the filename and metadata_storage_name mapping**. ** Unfortunately, the index still showing no data in content column Is there any extra steps needed or is this not possible yet ?

@Lyncheese In order to combine data from these two sources, you need to have a common field between them. As you mentioned, the filename column from SQL and metadata_storage_name from File Share are not key columns. However, you can still use them as a common field to merge data from both sources. To do this, you need to create an indexer that pulls data from both sources and populates a single search index. You can configure an indexer that pulls from both sources to populate a single search index. The two data sets must have a value in common to support the merge. In this sample, that field is an ID. As long as there's a field in common to support the mapping, an indexer can merge data from disparate resources: structured data from Azure SQL, unstructured data from Blob storage, or any combination of supported data sources on Azure. You can follow the tutorial you mentioned earlier to create an indexer that pulls data from both sources and populates a single search index. You can also use the field mapping feature to map the filename column from SQL and metadata_storage_name from File Share to a common field in the search index. If you have already created an indexer that pulls data from both sources and populates a single search index, but the content is still empty, there might be some issues with the configuration. You can check the logs to see if there are any errors or warnings. If you are still facing issues, please let me know and I will try to help you further.

How to index multiple data source when one of the source is file share ?

Accepted answer

brtrach-MSFT 17,476 Reputation points Microsoft Employee

2024-01-24T23:18:33.6233333+00:00

@Lyncheese In order to combine data from these two sources, you need to have a common field between them. As you mentioned, the filename column from SQL and metadata_storage_name from File Share are not key columns. However, you can still use them as a common field to merge data from both sources. To do this, you need to create an indexer that pulls data from both sources and populates a single search index. You can configure an indexer that pulls from both sources to populate a single search index. The two data sets must have a value in common to support the merge. In this sample, that field is an ID. As long as there's a field in common to support the mapping, an indexer can merge data from disparate resources: structured data from Azure SQL, unstructured data from Blob storage, or any combination of supported data sources on Azure. You can follow the tutorial you mentioned earlier to create an indexer that pulls data from both sources and populates a single search index. You can also use the field mapping feature to map the filename column from SQL and metadata_storage_name from File Share to a common field in the search index. If you have already created an indexer that pulls data from both sources and populates a single search index, but the content is still empty, there might be some issues with the configuration. You can check the logs to see if there are any errors or warnings. If you are still facing issues, please let me know and I will try to help you further.
Please sign in to rate this answer.
Lyncheese 105 Reputation points

2024-01-26T07:01:54.48+00:00

@brtrach-MSFT
Thank you for your response.

Unfortunately, my problem still persisted.
I have tried several times and the result still not as expected.
I hope you can direct me further in this case.
I will try to explain what I've done so far below.

Originally, I was going to use SDK to achieve this. But I realized for creating data source using the SDK, azurefile type is not supported.
Therefore, I'm using Azure Portal directly.

Create Index A with column
xxx | ... | zzz | filenm_only | metadata_storage_name | content

Create Azure SQL Indexer and Run it

Data :

xxx | ... | zzz | filenm_only | metadata_storage_name | content

data | ... | data | data | empty | empty

Create File Share Indexer with field mapping

"fieldMappings": [ { "sourceFieldName": "metadata_storage_name", "targetFieldName": "filenm_only", "mappingFunction": null } ]

Run File Share Indexer

Data :

xxx | ... | zzz | filenm_only | metadata_storage_name | content

data | ... | data | data | empty | empty

As you can see the result still the same.

But here are several things need to be noted, in case it might help you to help me in debug this problem.

I did this using Azure Portal (not sure if this is actually allowed using Portal directly)

As I said before the filenm_only and metadata_storage_name are not Key column, but you said this is no problem. Just want to remind you again.

My Azure SQL Indexer has 50k++ documents, while my File Share Indexer has 40k++ documents only. They are not the same counts, because not all rows in Azure SQL has filenm_only column. There are rows with empty data. Is this a problem ?

After run both Indexer, I realized the Index A documents has become 90k++. If both indexer data is merged, it is supposed to be 50k++ following the biggest count from both indexer. But it looks like instead of merge, it add the documents without merging.

Please let me know if you have any further question.
Thank you

brtrach-MSFT 17,476 Reputation points Microsoft Employee

2024-01-26T21:10:41.0533333+00:00

@Lyncheese Thank you for providing more information. Based on the information you provided, it seems like there might be some issues with the configuration of the indexers. Here are some suggestions that might help you to debug the problem:

Review the logs: Take a look at the logs of the indexers to see if there are any errors or warnings. This might give you some clues about what went wrong.

Verify the field mapping: Ensure that the field mapping is correct. In your case, you are mapping the metadata_storage_name field from the file share to the filenm_only field in the search index. Make sure that the mapping is correct and that the field names are spelled correctly.

Check the data types: Ensure that the data types of the fields are compatible. For example, if the filenm_only field is a string and the metadata_storage_name field is a number, the mapping might not work correctly.

Verify the merge policy: Ensure that the merge policy is set correctly. By default, the merge policy is set to "mergeOrUpload", which means that if a document with the same key already exists in the index, it will be merged with the new document. If you want to overwrite the existing document, you can set the merge policy to "upload".

Regarding your questions:

Using the Azure portal to create the indexers is allowed and should work fine.

The filenm_only and metadata_storage_name fields don't have to be key columns, but they should have a value in common to support the merge.

The fact that the counts of the documents in the two indexers are different should not be a problem, as long as there is a common field to support the merge.

The fact that the number of documents in the search index is larger than the sum of the documents in the two indexers might indicate that the merge policy is not set correctly.

Lyncheese 105 Reputation points

2024-01-29T04:18:13.69+00:00

@brtrach-MSFT
Thank you very much for your additional comments.

I tried to check what you had told me. I also simplified my data so I can try it faster with many parameters.

Review the logs : No errors or warnings.

Verify the field mapping : metadata_storage_name and filenm_only are both string. I made sure there is no typo here.

Verify the merge policy : this is left default.

I have one thing that I have doubt after several trials.
It is about this point :
"The filenm_only and metadata_storage_name fields don't have to be key columns, but they should have a value in common to support the merge."

Every trials I tried, ending up both indexer adding data to the index without any merging happened.

I confirmed this using Azure Ticket Support and this link https://learn.microsoft.com/en-us/azure/search/tutorial-multiple-data-sources (Map Key fields Seciton).

Both the support and the link said, Keys column needed as field mapping. Without this, instead of merging. The second indexer will add more data to the index.

Please correct me if I'm wrong.
If you have another information about this, please let me know.

Thanks

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Lyncheese 105 Reputation points

2024-01-31T10:30:00.35+00:00

@brtrach-MSFT
Hi

Looks like you added some comments, which is deleted by the platform.
Just want to let you know.

brtrach-MSFT 17,476 Reputation points Microsoft Employee

2024-02-05T01:45:10.61+00:00

@Lyncheese My apologies for that. We were having a plaftorm update while I was trying to reply to you so there was some issues. You are correct that having a common key field between the two data sources is necessary for merging the data. I apologize for the confusion in my previous response. The key field is used to identify the documents that need to be merged. Without a key field, the indexers will add new documents to the index instead of merging them. In your case, you can use the filename column from Azure SQL and the metadata_storage_name column from the file share as the key field. You can map these two fields to a common key field in the search index using the field mapping feature. Once you have set up the key field and the field mapping, you can run the indexers again and they should merge the data from both sources into a single search index.

petermcnally 25 Reputation points

2025-03-06T13:45:52.7766667+00:00

I am reading some ambiguity in your answer. If I understand correctly, the two sources need a common column, but not a common value for that column, correct? In other words, it doesn't function like a join in SQL, but as an append/insert. Does this make sense?

Also, in keeping with the SQL analogy, you're implying that the schema for the two sources does not need to be identical (just share a single common field). Does this mean that the fields in the index will have null values if they are not present in one of the sources?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to index multiple data source when one of the source is file share ?

1 additional answer

Your answer