[[{“value”:”

Managing cloud costs and understanding resource usage can be a daunting task, especially for organizations with complex AWS deployments. AWS Cost and Usage Reports (AWS CUR) provides valuable data insights, but interpreting and querying the raw data can be challenging.

In this post, we explore a solution that uses generative artificial intelligence (AI) to generate a SQL query from a user’s question in natural language. This solution can simplify the process of querying CUR data stored in an Amazon Athena database using SQL query generation, running the query on Athena, and representing it on a web portal for ease of understanding.

The solution uses Amazon Bedrock, a fully managed service that offers a choice of high-performing foundation models (FMs) from leading AI companies like AI21 Labs, Anthropic, Cohere, Meta, Mistral AI, Stability AI, and Amazon through a single API, along with a broad set of capabilities to build generative AI applications with security, privacy, and responsible AI.

Challenges addressed

The following challenges can hinder organizations from effectively analyzing their CUR data, leading to potential inefficiencies, overspending, and missed opportunities for cost-optimization. We aim to target and simplify them using generative AI with Amazon Bedrock.

Complexity of SQL queries – Writing SQL queries to extract insights from CUR data can be complex, especially for non-technical users or those unfamiliar with the CUR data structure (unless you’re a seasoned database administrator)
Data accessibility – To gain insights from structured data in databases, users need to get access to databases, which can be a potential threat to overall data protection
User-friendliness – Traditional methods of analyzing CUR data often lack a user-friendly interface, making it challenging for non-technical users to take advantage of the valuable insights hidden within the data

Solution overview

The solution that we discuss is a web application (chatbot) that allows you to ask questions related to your AWS costs and usage in natural language. The application generates SQL queries based on the user’s input, runs them against an Athena database containing CUR data, and presents the results in a user-friendly format. The solution combines the power of generative AI, SQL generation, database querying, and an intuitive web interface to provide a seamless experience for analyzing CUR data.

The solution uses the following AWS services:

Amazon Athena
Amazon Bedrock
AWS Billing and Cost Management for cost and usage reports
Amazon Simple Storage Service (Amazon S3)
The compute service of your choice on AWS to call Amazon Bedrock APIs. This could be Amazon Elastic Compute Cloud (Amazon EC2), AWS Lambda, AWS SDK, Amazon SageMaker notebooks, or your workstation if you are doing a quick proof of concept. For the purpose of this post, this code is running on a t3a.micro EC2 instance with Amazon Linux 2023.

The following diagram illustrates the solution architecture.

Figure 1. Architecture of Solution

The data flow consists of the following steps:

The CUR data is stored in Amazon S3.
Athena is configured to access and query the CUR data stored in Amazon S3.
The user interacts with the Streamlit web application and submits a natural language question related to AWS costs and usage.

Figure 2. Shows the Chatbot Dashboard to ask question

The Streamlit application sends the user’s input to Amazon Bedrock, and the LangChain application facilitates the overall orchestration.
The LangChain code uses the BedrockChat class from LangChain to invoke the FM and interact with Amazon Bedrock to generate a SQL query based on the user’s input.

Figure 3. Shows initialization of SQL chain

The generated SQL query is run against the Athena database using the FM on Amazon Bedrock, which queries the CUR data stored in Amazon S3.
The query results are returned to the LangChain application.

Figure 4. Shows generated Query in the application output logs

LangChain sends the SQL query and query results back to the Streamlit application.
The Streamlit application displays the SQL query and query results to the user in a formatted and user-friendly manner.

Figure 5. Shows final output presented on the chat bot webapp including SQL Query and the Query results

Prerequisites

To set up this solution, you should have the following prerequisites:

An AWS account with access to AWS Cost Explorer, Athena, and Amazon S3. (This is a proof of concept setup. You should follow the least privilege model when using AWS Identity and Access Management (IAM), refer to the IAM security best practices documentation, and conduct your own due diligence when setting this up.)
CUR data stored in an S3 bucket. For instructions, see Creating Cost and Usage Reports.
Athena set up to analyze the data from your S3 bucket holding CUR data. For instructions, see Querying Cost and Usage Reports using Amazon Athena.
An AWS compute environment created to host the code and call the Amazon Bedrock APIs.
An AWS Identity and Access Management (IAM) role with permission to access Amazon Bedrock and access to FMs in Amazon Bedrock.

Configure the solution

Complete the following steps to set up the solution:

Create an Athena database and table to store your CUR data. Make sure the necessary permissions and configurations are in place for Athena to access the CUR data stored in Amazon S3.
Set up your compute environment to call Amazon Bedrock APIs. Make sure you associate an IAM role with this environment that has IAM policies that grant access to Amazon Bedrock.
When your instance is up and running, install the following libraries that are used for working within the environment:

pip install langchain==0.2.0 langchain-experimental==0.0.59 langchain-community==0.2.0 langchain-aws==0.1.4 pyathena==3.8.2 sqlalchemy==2.0.30 streamlit==1.34.0

Use the following code to establish a connection to the Athena database using the langchain library and the pyathena Configure the language model to generate SQL queries based on user input using Amazon Bedrock. You can save this file as cur_lib.py.

from langchain_experimental.sql import SQLDatabaseChain
from langchain_community.utilities import SQLDatabase
from sqlalchemy import create_engine, URL
from langchain_aws import ChatBedrock as BedrockChat
from pyathena.sqlalchemy.rest import AthenaRestDialect

class CustomAthenaRestDialect(AthenaRestDialect):
def import_dbapi(self):
import pyathena
return pyathena

# DB Variables
connathena = “athena.us-west-2.amazonaws.com”
portathena = ‘443’
schemaathena = ‘mycur’
s3stagingathena = ‘s3://cur-data-test01/athena-query-result/’
wkgrpathena = ‘primary’
connection_string = f”awsathena+rest://@{connathena}:{portathena}/{schemaathena}?s3_staging_dir={s3stagingathena}/&work_group={wkgrpathena}”
url = URL.create(“awsathena+rest”, query={“s3_staging_dir”: s3stagingathena, “work_group”: wkgrpathena})
engine_athena = create_engine(url, dialect=CustomAthenaRestDialect(), echo=False)
db = SQLDatabase(engine_athena)

# Setup LLM
model_kwargs = {“temperature”: 0, “top_k”: 250, “top_p”: 1, “stop_sequences”: [“nnHuman:”]}
llm = BedrockChat(model_id=”anthropic.claude-3-sonnet-20240229-v1:0″, model_kwargs=model_kwargs)

# Create the prompt
QUERY = “””
Create a syntactically correct athena query for AWS Cost and Usage report to run on the my_c_u_r table in mycur database based on the question, then look at the results of the query and return the answer as SQLResult like a human
{question}
“””
db_chain = SQLDatabaseChain.from_llm(llm, db, verbose=True)

def get_response(user_input):
question = QUERY.format(question=user_input)
result = db_chain.invoke(question)
query = result[“result”].split(“SQLQuery:”)[1].strip()
rows = db.run(query)
return f”SQLQuery: {query}nSQLResult: {rows}”

Create a Streamlit web application to provide a UI for interacting with the LangChain application. Include the input fields for users to enter their natural language questions and display the generated SQL queries and query results. You can name this file cur_app.py.

import streamlit as st
from cur_lib import get_response
import os

st.set_page_config(page_title=”AWS Cost and Usage Chatbot”, page_icon=”chart_with_upwards_trend”, layout=”centered”, initial_sidebar_state=”auto”,
menu_items={
‘Get Help’: ‘https://docs.aws.amazon.com/cur/latest/userguide/cur-create.html’,
#’Report a bug’:,
‘About’: “# The purpose of this app is to help you get better understanding of your AWS Cost and Usage report!”
})#HTML title
st.title(“_:orange[Simplify] CUR data_ :sunglasses:”)

def format_result(result):
parts = result.split(“nSQLResult: “)
if len(parts) > 1:
sql_query = parts[0].replace(“SQLQuery: “, “”)
sql_result = parts[1].strip(“[]”).split(“), (“)
formatted_result = []
for row in sql_result:
formatted_result.append(tuple(item.strip(“(),'”) for item in row.split(“, “)))
return sql_query, formatted_result
else:
return result, []

def main():
# Get the current directory
current_dir = os.path.dirname(os.path.abspath(__file__))
st.markdown(“<div class=’main’>”, unsafe_allow_html=True)
st.title(“AWS Cost and Usage chatbot”)
st.write(“Ask a question about your AWS Cost and Usage Report:”)

Connect the LangChain application and Streamlit web application by calling the get_response Format and display the SQL query and result in the Streamlit web application. Append the following code with the preceding application code:

# Create a session state variable to store the chat history
if “chat_history” not in st.session_state:
st.session_state.chat_history = []

user_input = st.text_input(“You:”, key=”user_input”)

if user_input:
try:
result = get_response(user_input)
sql_query, sql_result = format_result(result)
st.code(sql_query, language=”sql”)
if sql_result:
st.write(“SQLResult:”)
st.table(sql_result)
else:
st.write(result)
st.session_state.chat_history.append({“user”: user_input, “bot”: result})
st.text_area(“Conversation:”, value=”n”.join([f”You: {chat[‘user’]}nBot: {chat[‘bot’]}” for chat in st.session_state.chat_history]), height=300)
except Exception as e:
st.error(str(e))

st.markdown(“</div>”, unsafe_allow_html=True)

if __name__ == “__main__”:
main()

Deploy the Streamlit application and LangChain application to your hosting environment, such as Amazon EC2, or a Lambda function.

Clean up

Unless you invoke Amazon Bedrock with this solution, you won’t incur charges for it. To avoid ongoing charges for Amazon S3 storage for saving the CUR reports, you can remove the CUR data and S3 bucket. If you set up the solution using Amazon EC2, make sure you stop or delete the instance when you’re done.

Benefits

This solution offers the following benefits:

Simplified data analysis – You can analyze CUR data using natural language using generative AI, eliminating the need for advanced SQL knowledge
Increased accessibility – The web-based interface makes it efficient for non-technical users to access and gain insights from CUR data without needing credentials for the database
Time-saving – You can quickly get answers to your cost and usage questions without manually writing complex SQL queries
Enhanced visibility – The solution provides visibility into AWS costs and usage, enabling better cost-optimization and resource management decisions

Summary

The AWS CUR chatbot solution uses Anthropic Claude on Amazon Bedrock to generate SQL queries, database querying, and a user-friendly web interface to simplify the analysis of CUR data. By allowing you to ask natural language questions, the solution removes barriers and empowers both technical and non-technical users to gain valuable insights into AWS costs and resource usage. With this solution, organizations can make more informed decisions, optimize their cloud spending, and improve overall resource utilization. We recommend that you do due diligence while setting this up, especially for production; you can choose other programming languages and frameworks to set it up according to your preference and needs.

Amazon Bedrock enables you to build powerful generative AI applications with ease. Accelerate your journey by following the quick start guide on GitHub and using Amazon Bedrock Knowledge Bases to rapidly develop cutting-edge Retrieval Augmented Generation (RAG) solutions or enable generative AI applications to run multistep tasks across company systems and data sources using Amazon Bedrock Agents.

About the Author

Anutosh is a Solutions Architect at AWS India. He loves to dive deep into his customers’ use cases to help them navigate through their journey on AWS. He enjoys building solutions in the cloud to help customers. He is passionate about migration and modernization, data analytics, resilience, cybersecurity, and machine learning.

“}]] In this post, we explore a solution that uses generative artificial intelligence (AI) to generate a SQL query from a user’s question in natural language. This solution can simplify the process of querying CUR data stored in an Amazon Athena database using SQL query generation, running the query on Athena, and representing it on a web portal for ease of understanding. Read More Advanced (300), Amazon Bedrock, Artificial Intelligence, Generative AI, Technical How-to

Unlock AWS Cost and Usage insights with generative AI powered by Amazon Bedrock Anutosh AWS Machine Learning Blog