Creating an Amazon Q Business Application with RDS MySQL Integration

Configuring Amazon Q Business with RDS MySQL: A Step-by-Step Guide

Amazon Q Business is a powerful tool that leverages AI to provide actionable insights from your business data. To make the most of it, you can configure it with an Amazon RDS MySQL database, where your data is stored securely and efficiently. In this guide, we’ll walk through the steps to configure Amazon Q Business with RDS MySQL, including how to insert RDS dump data using AWS Cloud9 and how to add the data source from the Amazon Q Business console.

High level architecture of Amazon Q Business

Prerequisites

Before you start, ensure you have the following:

1. AWS Account: Access to an AWS account with permissions to create RDS, Cloud9, and other resources.
2. AWS Cloud9: Set up and configured in your AWS environment.
3. MySQL Dump File: The SQL dump file containing the data you want to import into your RDS MySQL database. 4.Amazon Q Business set up, please visit getting started guide
4. Complete the steps to create your Amazon Q application.
5. Complete the steps for selecting an Amazon Q retriever
6. AWS Cloud 9 environment to help running our sample data and scripts for MySQL. Please visit getting started guide

Step 1: Setting Up RDS MySQL

1. Create an RDS MySQL Instance:
   • Go to the RDS console in AWS.
   • Click on "Create database" and select "MySQL" as the database engine.
   • Choose the appropriate configuration based on your needs (e.g., instance class, storage, etc.).
   • Note down the endpoint, port, username, and password for future reference.

1. Configure Security Groups:
   • Ensure the RDS instance’s security group allows inbound traffic on port 3306 (default for MySQL) from your Cloud9 environment.

Step 2: Inserting RDS Dump Data Using Cloud9

1. Set Up AWS Cloud9:

   • Navigate to the Cloud9 console and create a new Cloud9 environment.
   • Ensure the environment has the necessary IAM roles to interact with your RDS MySQL instance.

2. Upload the MySQL Dump File:

   • In your Cloud9 environment, upload the MySQL dump file using the file upload feature or by directly cloning it from a repository if it's available online.

3. Connect to RDS MySQL Using Python:

   • Open a new terminal in Cloud9.
   • Install MySQL client libraries by running:

    sudo apt-get install mysql-client
   

• Connect to your RDS MySQL instance using the mysql command:
  

   mysql -h <RDS_ENDPOINT> -u <USERNAME> -p
  

• You’ll be prompted for your RDS MySQL password. Enter it to establish the connection.

1. Sample code and data for setting up RDS MySQL sample data:

import mysql.connector

cnx = mysql.connector.connect(user=<<username>>, password=<<password>>,host='rds-hostname.us-west-2.rds.amazonaws.com',database=<<databasename>>)

Please replace with your credentials , hostname and database name.
For the purpose of demo, I will be using public available sample data about few movies, actors and reviews. Run the following scripts

#Create the actors table
mycursor.execute('''
CREATE TABLE actors (
    actor_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birth_date DATE,
    bio TEXT
);
''')

#Create the movies table
mycursor.execute('''
CREATE TABLE movies (
    movie_id SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    release_date DATE,
    genre VARCHAR(50),
    director VARCHAR(100),
    plot TEXT,
    main_actor_id INT REFERENCES actors(actor_id)
);
''')

#Create the customer_reviews_all table (for movie reviews)
mycursor.execute('''
CREATE TABLE movie_reviews_all (
    review_id SERIAL PRIMARY KEY,
    movie_name VARCHAR(100) NOT NULL,
    review TEXT NOT NULL
);
''')

#Insert sample data into the actors table
mycursor.execute("INSERT INTO actors (first_name, last_name, bio) VALUES('Leonardo', 'DiCaprio', 'Award-winning actor known for his versatile roles and environmental activism.');")
mycursor.execute("INSERT INTO actors (first_name, last_name, bio) VALUES('Meryl', 'Streep', 'Legendary actress with a record number of Academy Award nominations.');")
mycursor.execute("INSERT INTO actors (first_name, last_name, bio) VALUES('Denzel', 'Washington', 'Acclaimed actor and director with a powerful on-screen presence.');")
mycursor.execute("INSERT INTO actors (first_name, last_name, bio) VALUES('Viola', 'Davis', 'Versatile actress known for her intense and emotional performances.');")
mycursor.execute("INSERT INTO actors (first_name, last_name, bio) VALUES('Tom', 'Hanks', 'Beloved actor known for his everyman charm and dramatic range.');")
cnx.commit()

#Insert sample data into the movies table
mycursor.execute("INSERT INTO movies (title, release_date, genre, director, plot, main_actor_id) VALUES('Inception', '2010-07-16', 'Science Fiction', 'Christopher Nolan', 'A skilled thief is offered a chance to regain his old life as payment for a task considered to be impossible.', 1);")
mycursor.execute("INSERT INTO movies (title, release_date, genre, director, plot, main_actor_id) VALUES('The Devil Wears Prada', '2006-06-30', 'Comedy-Drama', 'David Frankel', 'A naive young woman comes to New York and scores a job as the assistant to one of the city''s biggest magazine editors.', 2);")
mycursor.execute("INSERT INTO movies (title, release_date, genre, director, plot, main_actor_id) VALUES('Training Day', '2001-10-05', 'Crime Thriller', 'Antoine Fuqua', 'A rookie cop spends his first day as a Los Angeles narcotics officer with a rogue detective who isn''t what he appears to be.', 3);")
mycursor.execute("INSERT INTO movies (title, release_date, genre, director, plot, main_actor_id) VALUES('Fences', '2016-12-25', 'Drama', 'Denzel Washington', 'A working-class African-American father tries to raise his family in the 1950s, while coming to terms with the events of his life.', 4);")
mycursor.execute("INSERT INTO movies (title, release_date, genre, director, plot, main_actor_id) VALUES('Forrest Gump', '1994-07-06', 'Drama', 'Robert Zemeckis', 'The presidencies of Kennedy and Johnson, the Vietnam War, the Watergate scandal and other historical events unfold through the perspective of an Alabama man with a low IQ.', 5);")
cnx.commit()

#Insert sample data into the movie_reviews_all table (for movie reviews)
mycursor.execute("INSERT INTO movie_reviews_all (movie_name, review) VALUES ('Inception', 'Mind-bending plot with stunning visuals, a true cinematic masterpiece');")
mycursor.execute("INSERT INTO movie_reviews_all (movie_name, review) VALUES ('The Devil Wears Prada', 'Witty, fashionable, and surprisingly deep. Meryl Streep is phenomenal');")
mycursor.execute("INSERT INTO movie_reviews_all (movie_name, review) VALUES ('Inception', 'Christopher Nolan at his best, complex yet thoroughly entertaining');")
mycursor.execute("INSERT INTO movie_reviews_all (movie_name, review) VALUES ('Forrest Gump', 'A heartwarming journey through history. Tom Hanks delivers an unforgettable performance');")
mycursor.execute("INSERT INTO movie_reviews_all (movie_name, review) VALUES ('Training Day', 'Intense and gripping. Denzel Washington is absolutely electrifying');")
cnx.commit()

Once completed, you can verify your data running the following

#SELECT query joining movies and actors tables
mycursor.execute("SELECT movie_id, title, CONCAT('movie_id:', m.movie_id, ' title:', m.title, ' release_date:', m.release_date, ' genre:', m.genre, ' director:', m.director, ' plot:', m.plot, ' main_actor_id:', m.main_actor_id, ' actor_details: actor_id:', a.actor_id, ' first_name:', a.first_name, ' last_name:', a.last_name, ' bio:', a.bio) AS movie_details FROM movies m JOIN actors a ON m.main_actor_id = a.actor_id;")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)


#SELECT query for moview reviews
mycursor.execute("SELECT review_id, movie_name, CONCAT('review_id:', c.review_id, ' movie_name:', c.movie_name, ' review:', c.review) AS movie_review_details FROM movie_reviews_all c")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

with this in place, lets move to setting up Amazon Q for business connector for RDS MySQL
Amazon RDS (MySQL) (Amazon Relational Database Service) is a web service that makes it easier to set up, operate, and scale a relational database in the AWS Cloud. You can connect your Amazon RDS (MySQL) instance to Amazon Q Business – using either the AWS Management Console, CLI, or the CreateDataSource API – and create an Amazon Q web experience.
I will be following this guide to set up the connector using the console. We need to make sure we follow our prerequisites as noted before.

Step 3: Configuring Amazon Q Business with RDS MySQL

1. Access Amazon Q Business Console:
   • Navigate to the Amazon Q Business console from the AWS Management Console.

1. Add a Data Source:
   • In the Amazon Q Business console, go to the "Data Sources" section.
   • Click on "Add data source" and select "RDS MySQL" from the available options.

1. Configure Data Source Connection:

• Provide the necessary connection details such as the RDS endpoint, database name, username, and password.

• To enhance security, consider storing these credentials in AWS Secrets Manager and retrieving them programmatically within Amazon Q Business. Once you followed rest of the steps in the guide, lets take a look on sync scopes we will add. You will need to add 2-3 different data sources per your needs. For this demo, we will add two. Data source sync scope 1 - Movie details

SELECT movie_id, title, CONCAT('movie_id:', m.movie_id, ' title:', m.title, ' release_date:', m.release_date, ' genre:', m.genre, ' director:', m.director, ' plot:', m.plot, ' main_actor_id:', m.main_actor_id, ' actor_details: actor_id:', a.actor_id, ' first_name:', a.first_name, ' last_name:', a.last_name, ' bio:', a.bio) AS movie_details FROM movies m JOIN actors a ON m.main_actor_id = a.actor_id

Add movie_id as primary key column, title as title column and movie_details as Body column.

Data source sync scope 2 - Movie reviews

SELECT review_id, movie_name, CONCAT('review_id:', c.review_id, ' movie_name:', c.movie_name, ' review:', c.review) AS movie_review_details FROM movie_reviews_all c

Add review_id as primary key column, movie_name as title column and movie_review_details as Body column.

Leave everything else as default. With this set up done, lets sync each of the above data source and wait for it to complete. If there are any sync errors, you will see on this console and will be able to look into the Amazon CloudWatch under details column.

Amazon Q Business Conversations.
With everything in place lets ask few queries.

1. Test the Connection:
   • After entering the connection details, test the connection to ensure Amazon Q Business can successfully connect to your RDS MySQL database.

1. Create Datasets:

   • Once the connection is established, create datasets from your RDS MySQL tables.
   • These datasets will be used by Amazon Q Business to generate insights and visualizations.

2. Create User Inside Application:

   • Register User

• Confirm Subscription

Testing Application

To Test Application User need to login to ChatUI:

Before Data Sync:

You can see the chat bot is not able to provide the insigts

After Data Sync:

Conclusion

Configuring Amazon Q Business with RDS MySQL provides a robust foundation for gaining valuable insights from your business data. By following the steps outlined above, you can seamlessly integrate RDS MySQL with Amazon Q Business, ensuring your data is securely stored, easily accessible, and ready for analysis. Whether you’re importing data using AWS Cloud9 or managing connections via the Amazon Q Business console, this setup will empower you to make data-driven decisions with confidence.

Now that your Amazon Q Business is up and running, take advantage of its features to unlock the full potential of your business data!

Note :

For Amazon Q Business Pricing Please visit AWS Q pricing

Please don't hesitate to contact me if you have any queries or require additional help.