The Blue Owls > Navigating The AI Landscape > Blog > Integrating Azure AI Search with Your Azure MySQL Database

Integrating Azure AI Search with Your Azure MySQL Database

10 July 2024 Blog, Intelligent Apps

Integrating Azure AI Search (formerly known as Cognitive Search) with your Azure MySQL Database can significantly enhance the search capabilities of your applications, making them more efficient and user-friendly.
Given that this feature is currently in public preview and can only be accessed through APIs, this guide aims to provide you with a comprehensive, step-by-step tutorial.

1. Prerequisites

Azure Subscription

Ensure that you have an active Azure subscription with the necessary credits or billing set up.

Permissions

You’ll need specific permissions to access Azure resources. Typically, these include rights to create and manage instances of Azure MySQL Database and Azure AI Search.

Installed Tools

Before starting, make sure you’ve installed the Azure CLI and a REST client like Postman to perform API calls.

2. Setting Up Azure Services

Create an Azure MySQL Database Instance

Navigate to the Azure Portal

Go to the Azure portal and sign in with your credentials.

Create a MySQL Database

Select “Create a resource” and search for “Azure Database for MySQL”. Follow the prompt to create a new instance. Specify parameters such as database name, server name, location, and performance tier.

Connection Configuration

Once created, obtain the connection string for your database. This will be needed when setting up the data source for Azure AI Search.

3. Configuring API Access

Create Azure AI Search Service

In the Azure portal, search for “Azure AI Search” and create a new search service. Assign a unique name to your search service and choose your pricing tier based on your requirements.

Obtain API Keys and Endpoint

After creating the search service, navigate to the “Keys” section under your search service to obtain your admin API key and endpoint URL. You will need these for API authentication.

4. Creating a Data Source

Connecting Azure AI Search to the MySQL database is a three-step process: creating a data source, an index, and an indexer.

Create Data Source

The data source definition specifies the data to index, including credentials and policies.

{
    "name": "hotel-mysql-ds",
    "description": "Description of MySQL data source",
    "type": "mysql",
    "credentials": {
        "connectionString": "Server=[MySQLServerName].MySQL.database.azure.com; Port=3306; Database=[DatabaseName]; Uid=[UserName]; Pwd=[Password]; SslMode=Preferred;"
    },
    "container": {
        "name": "[TableName]"
    },
    "dataChangeDetectionPolicy": {
        "@odata.type": "#Microsoft.Azure.Search.HighWaterMarkChangeDetectionPolicy",
        "highWaterMarkColumnName": "[HighWaterMarkColumn]"
    }
}

NOTE: For Azure Database for MySQL indexers, the only supported policy is the HighWaterMarkChangeDetectionPolicy.

API Call to Create Data Source

Use a REST client like Postman to send a POST request to create the data source.

  • Endpoint: https://[YourSearchService].search.windows.net/datasources?api-version=2020-06-30-preview
  • Headers:
    • Content-Type: application/json
    • api-key: [Your AI_SEARCH_ADMIN_KEY]
  • Body: The JSON payload as defined above.

5. Creating Indexes

Indexes in Azure AI Search are schemas that define the structure of the searchable data.

Define Index Schema

Create or update an index schema to define the searchable fields.

{
    "name": "hotels-mysql-ix",
    "fields": [
        { "name": "ID", "type": "Edm.String", "key": true, "searchable": false },
        { "name": "HotelName", "type": "Edm.String", "searchable": true, "filterable": false },
        { "name": "Category", "type": "Edm.String", "searchable": false, "filterable": true, "sortable": true },
        { "name": "City", "type": "Edm.String", "searchable": false, "filterable": true, "sortable": true },
        { "name": "Description", "type": "Edm.String", "searchable": false, "filterable": false, "sortable": false }
    ]
}

API Call to Create Index

Send a POST request to create the index schema.

  • Endpoint: https://[YourSearchService].search.windows.net/indexes?api-version=2020-06-30-preview
  • Headers:
    • Content-Type: application/json
    • api-key: [Your AI_SEARCH_ADMIN_KEY]
  • Body: The JSON payload as defined above.

6. Indexers Configuration

Indexers automate the data import and indexing process. They connect to the MySQL database, read data, and index it.

Define Indexer Configuration

{
    "name": "hotels-mysql-idxr",
    "dataSourceName": "hotel-mysql-ds",
    "targetIndexName": "hotels-mysql-ix",
    "disabled": null,
    "schedule": null,
    "parameters": {
        "batchSize": null,
        "maxFailedItems": null,
        "maxFailedItemsPerBatch": null,
        "base64EncodeKeys": null,
        "configuration": { }
    },
    "fieldMappings": [],
    "encryptionKey": null
}

API Call to Create Indexer

Send a POST request to create the indexer.

  • Endpoint: https://[YourSearchService].search.windows.net/indexers?api-version=2020-06-30-preview
  • Headers:
    • Content-Type: application/json
    • api-key: [Your AI_SEARCH_ADMIN_KEY]
  • Body: The JSON payload as defined above.

Run the Indexer

The indexer will run automatically immediately after creation unless disabled is set to true. You can also schedule indexer runs for real-time data synchronization.

7. Querying the Search Index

Once the data is indexed, you can perform queries to validate and optimize search results.

Example API Queries

GET https://[YourSearchService].search.windows.net/indexes/hotels-mysql-ix/docs?search=*&api-version=2020-06-30-preview

Customize and Optimize Queries

Experiment with different queries and filters to improve the search results. Utilize features like scoring profiles, filters, and facets to enhance the search experience.

8. Handling Errors and Troubleshooting

Common issues may arise during setup or execution. Use Azure monitoring tools and logs for troubleshooting.

Common Issues

  • API Errors: Check for API key validity and endpoint correctness.
  • Data Mapping Errors: Ensure field names and types match between the MySQL database and the search index.
  • Indexer Failures: Monitor indexer status using the Get Indexer Status API.
GET https://[YourSearchService].search.windows.net/indexers/myindexer/status?api-version=2020-06-30-preview

Optimize Indexing

Regularly update indexes and manage them efficiently.

Performance Tuning

Utilize Azure’s query optimizations and performance settings.

Effective Use of Features

Leverage suggesters, analyzers, and AI enrichments to improve search relevance and user experience.

10. Advanced Features

Azure AI Search offers advanced search capabilities that can enhance the user experience further.

AI Enrichments

Use built-in or custom skills for advanced data analysis.

Suggestions and Analyzers

Implement suggesters for autocomplete and analyzers for language-specific tokenization.

Conclusion

Creating your MySQL instance to running queries, each step is crucial for ensuring seamless integration and optimal performance. As you get familiar with these steps, you are encouraged to explore additional Azure features to further leverage the full potential of your applications.

References and Additional Resources

For further reading and support, refer to these resources:

Leave a Reply

Your email address will not be published. Required fields are marked *