Indexing Amazon RDS for MySQL databases with Amazon Q Business
Query your Amazon RDS for MySQL databases using Natural Language
Bharath Chekuri
Amazon Employee
Published Sep 15, 2024
This blog demonstrates connecting an Amazon Q Business application to an Amazon RDS for MySQL database using the AWS Management console.
Amazon Q Business is a generative AI–powered assistant that can answer questions, provide summaries, generate content, and securely complete tasks based on data and information in your enterprises.
Amazon RDS for MySQL frees you up to focus on application development by managing time-consuming database administration tasks, including backups, upgrades, software patching, performance improvements, monitoring, scaling, and replication.
You can use the AWS Management Console, CLI or the CreateDataSource API to connect your Amazon Q Business application to your existing MySQL 8.0.21 databases in a few simple steps.
For more information about the connector, refer to this page.
Before you begin connecting the Amazon RDS MySQL database to Amazon Q Business, you should have the following:
- An AWS account with the privileges to create an Amazon RDS for MySQL database, Amazon Q Business application and AWS Identity and Access Management(IAM) roles and policies.
- An Amazon RDS for MySQL database. The MySQL database used for this blog is a publicly available sample database that consists of employees information including their hire dates and salary data.
- An AWS IAM Identity center instance and a test user. Refer to this page for more information.
1. In the AWS Management console search bar, type in Amazon Q Business and Click on Get Started. On the Applications page, choose Create Application.
2. On the Create application page, type in the Application name, select a Service access, Access Management method. The recommended access management method for Amazon Q Business is AWS IAM Identity center. Choose Create.
4. On the Connect data sources page, add Amazon RDS(MYSQL) as a data source connector. Amazon Q Business supports over 40 built in data source connectors and you can configure up to 50 data source connectors per application.
5. On the Connect data sources page type in a descriptive Data source name. Enter the AWS RDS host, port (3308 usually for MySQL) and the Instance. The host name and port can be found within the Connectivity & Security tab of the Amazon RDS MySQL database in Amazon RDS console. Review this page to find this information for your database. Note that the Instance field refers to the name of the actual database instance running within MySQL and not the DB Identifier that appears in the AWS RDS Console under Databases.
6. For authentication, choose an existing AWS Secrets manager secret or create one if it does not exist.
7. For the example in this blog, I have created the Amazon MySQL database in a private subnet in the default VPC. Review this page for instructions on how to configure the VPC, private subnet and a security group for Amazon Q Business to access a MySQL database. Once you have identified and/or created the VPC, Subnets and Security groups, fill in the respective information in the Configure VPC and security group section as shown below.
Ensure that the data source connector in Amazon Q Business is created in supported regions and availability zones.
8. In the IAM role section, choose Create a new service role(recommended). This role is used by Amazon Q Business to access repository credentials and the application content.
9. The Sync scope for an Amazon RDS MySQL data source in Amazon Q Business refers to the specific data that will be synchronized and indexed from your RDS MySQL database. It allows you to control which data gets synced and is made searchable in your Amazon Q Business experience.
- For this blog, I limited the sync scope to employees and their salary details in the employees and salaries tables in the employees database. This scope is represented as a SQL query (as shown below) that JOINS these two tables using the primary and foreign keys of emp.no.
- The Primary key column refers to the primary key for the database table – I chose emp_no in the employees table as the Primary key column.
- The Title column refers to the specific column in the database table that you want to designate as the title or the name of each document indexed in Amazon Q Business – I chose to concatenate the first_name and last_name fields of the employees table into one column; emp_name.
- The Body column refers to the specific column in the database table that contains the main textual content that you want to make searchable. For the example in this blog, I want to make employees’ names, their salaries and hire dates as searchable. I concatenate these respective columns from the employees and salaries table into one column; emp_details.
10. For Sync mode, since this is a new sync, choose Full sync and Run on demand for the Sync run schedule. Optionally, you can add tags to this data source. Leave the Field mappings section as-is and click the Add data source button.
Click here to learn more about Field Mappings.
11. Click Next to move to the Manage Access page. Click Add Groups and Users to add users to this application.
12. In the Add or assign users and groups pop-up, select Assign existing users and groups to add existing users configured in your connected IAM Identity center. Optionally, if you have permissions to add users, you can select Add new users. Choose Next.
13. Choose the users or groups you want to add and choose Assign.
14. Once assigned, the user appears in the Users tab of the Groups and users section of the application. Each user or a group needs to have an active subscription to use the application. Review this page for user subscription tiers.
15. In the Web experience settings section, choose Create and use a new service role and click Done. An Amazon Q Business web experience is an easy-to-use generative artificial intelligence (generative AI) assistant. You can use the Amazon Q Business web experience to ask questions and to accomplish your tasks.
16. You will now see the application created in the Applications section. Click on the newly created application.
17. In the Application details page, scroll down to the Data sources section and click Sync now(crawl and index) to start syncing the database.
18. You can view more details about the data source and the sync status by clicking on the data source. The Current sync state field shows the crawling and indexing status. This process can take from minutes to hours to complete depending on the size of the data source.
19. The Sync history section displays additional details about the sync job including job status, start and end times, total items scanned, failed items and the ability to view Amazon CloudWatch logs related to the sync job. Refer to this blog for deeper insights into the sync process and Document level reports. Review this documentation to troubleshoot any issues.
20. You will see your application’s Web experience URL in the Applications page. Use this URL to login to the Amazon Q Business generative AI assistant UI to query our MySQL database.
21. We can now ask Amazon Q Business, questions about employees in our Amazon RDS for MySQL database using natural language. For each of the responses you can provide feedback about the accuracy of the response, copy the response as well as the debug response information.
Any opinions in this post are those of the individual author and may not reflect the opinions of AWS.