AWS Database Blog

Analyzing PL/SQL and T-SQL code using Amazon Bedrock

As applications grow in complexity, understanding and modifying your existing code base can become a daunting task, especially when dealing with legacy systems or unfamiliar code bases. This challenge is particularly widespread in the realm of database programming, where languages like Oracle’s PL/SQL and SQL Server’s T-SQL are widely used.

PL/SQL and T-SQL are powerful languages that enable developers to write complex logic and procedures within the database environment. However, their syntax and structure can sometimes be dense, complex, and puzzling, making it difficult for database administrators, database developers, or even experienced developers to quickly grasp the intent and functionality of a given block of code.

Amazon Bedrock is a fully managed service that offers a choice of high-performing foundation models (FMs) from leading AI companies like AI21 Labs, Anthropic, Cohere, Meta, Stability AI, and Amazon with a single API, along with a broad set of capabilities to build generative AI applications, simplifying development while maintaining privacy and security. With the comprehensive capabilities of Amazon Bedrock, you can experiment with a variety of top FMs, privately customize them with your data using techniques such as fine-tuning and Retrieval Augmented Generation (RAG), and create managed agents that run complex business tasks—from booking travel and processing insurance claims to creating ad campaigns and managing inventory—all without writing any code. Because Amazon Bedrock is serverless, you don’t have to manage any infrastructure, and you can securely integrate and deploy generative AI capabilities into your applications using the AWS services you’re already familiar with.

In this post, we use the Anthropic Claude3 Sonnet large language model (LLM) on Amazon Bedrock to provide a detailed breakdown of the complex PL/SQL and T-SQL code, making it more understandable and comprehensible for developers who are new to a code base or working with unfamiliar code, because it helps them understand the logic and flow of the code more effectively.

At AWS our top priority is the security and confidentiality of your workloads. AWS Artificial Intelligence (AI) infrastructure and services have security and privacy features built-in to give you control over your data. You can rest assured that your data is being handled securely across the AI lifecycle including for data preparation, training, and inferencing. Refer to Secure approach to generative AI

Solution overview

The solution uses natural language processing (NLP) techniques to break down complex code into more comprehensible summaries and explanations. The following architecture diagram demonstrates how the solution uses AWS Lambda and Amazon Bedrock.

In this architecture, we use Streamlit as our frontend interface. Streamlit is a popular open-source Python library that allows you to build interactive web applications with minimal coding effort. You can input your PL/SQL or T-SQL code snippet through the Streamlit interface.

The code snippet is then passed to a Lambda function, which acts as an intermediary layer between the frontend and Amazon Bedrock. The Lambda function is responsible for making API calls to Amazon Bedrock, passing the code snippet as input, and receiving the generated summaries and explanations as output.

Amazon Bedrock analyzes the provided code using its machine learning (ML) models and NLP capabilities. It generates a high-level summary of the code’s functionality, as well as a detailed breakdown of each line or block of code, explaining its purpose and role within the larger context.

The generated summaries and explanations are then passed back to the Lambda function, which relays the information to the web interface frontend. You can view the code summaries and explanations in a user-friendly format, helping you understand the functionality of the PL/SQL or T-SQL code.

Prerequisites

This post assumes familiarity with navigating the AWS Management Console. For this example, you also need the following resources and services enabled in your AWS account:

  • AWS account – You need an AWS account to create and deploy the necessary AWS resources, such as Lambda functions and Amazon Bedrock.
  • AWS CLI (Optional) – You use the AWS Command Line Interface (AWS CLI) to interact with AWS services from your local machine or development environment. For installation instructions, refer to Install or update to the latest version of the AWS CLI.
  • Amazon Bedrock – You may need to request access to use specific FMs in Amazon Bedrock. In this post, we use Anthropic Claude 3 Sonnet on Amazon Bedrock.
  • Streamlit – You need to install Streamlit on your local machine using the following command with administrative access: 
    $ pip install streamlit
  • AWS SDK for Python (Boto3) – You use the AWS SDK for Python (Boto3) to create, configure, and manage AWS services, such as Amazon Elastic Compute Cloud (Amazon EC2) and Amazon Simple Storage Service (Amazon S3). The SDK provides an object-oriented API as well as low-level access to AWS services. Assuming you are on a Linux machine and want to install boto3, run the following command: 
    $ pip install boto3

Set up the Streamlit frontend

Create a new Python file (for example, app.py) and import the necessary Streamlit libraries and provide executable permission on the file. Assuming you are on a Linux machine, you can use the following commands.

$ cd /home/ec2-user/
$ vi app.py
$ chmod +x app2.py

Write the following code to the file:

import streamlit as st
import json
import boto3

# Initialize boto3 client
lambda_client = boto3.client('lambda', region_name='us-west-2') 

# Set page config
st.set_page_config(
    page_title=" Analyzing SQL Code",
    page_icon="🔄",
    layout="wide",
    initial_sidebar_state="expanded",
)

# Main content
with st.container():
    st.title("Analyzing SQL Code")
    st.subheader("This app will provide a summary and breakdown of the code in simple language.")
    
    # Middle pane
    with st.expander("Input SQL Code", expanded=True):
        language = st.radio("Select Database Engine:", ("Oracle", "SQL Server"), horizontal=True)
        #st.write("Enter the SQL code that you want to comprehend:")
        code_input = st.text_area("Enter the SQL code that you want to comprehend:", height=300,max_chars=10000)
       # if st.button("Submit"):
            #add condition that checks if the code input is more than 100 characters and empty
        if len(code_input) < 10 or code_input == "":
            code_input = ""
            st.stop()
        else:
            tokens = len(code_input.split())
            payload = json.dumps({"language":language, "sqltext": code_input})
            print(payload)

    # Bottom pane
    with st.expander("Explanation"):
        result = lambda_client.invoke(
                FunctionName='InvokeLambdaCall',
                Payload=payload
            )
        result = result['Payload'].read().decode("utf-8")
        result = json.loads(result)
        st.write(result)
        #tokens = len(result.split())

In this example, we create a text area for users to input their code, and a button to start the code analysis process. When you press Ctrl+Enter in the webpage, the Lambda function is called, which interacts with the LLM model to analyze the code using Amazon Bedrock.

Create the Lambda function

Next, you create the Lambda function responsible for interacting with Amazon Bedrock. You can create a new Lambda function from the Lambda console (as shown in the following screenshot) or the AWS CLI.

Make sure to provide the required permissions to the Lambda function to invoke the Amazon Bedrock model.

Copy and paste the provided code in the Code source section and update the AWS Region as required in your Lambda function code, then choose Deploy.

import json
import hashlib
import boto3
import random
import string
import re
import base64


def generate_prompt(lang, sqlstmt):
    prompt = ""
    prompt = f"""The given code is in "{lang}". You are Database Administrator. Your task is to understand the logic of the provided "{lang}" PL/SQL code and help me understand the logic of the "{lang}" code. Skip the preamble.
            Provide 3-6 lines of summary in the "Summary" section and detailed explanation of the code by breaking down the logic into the "Description" section. 
            {sqlstmt}

            Summary:
            Description of the SQL code:
            """
    print(prompt)
    return prompt
 
 
def invoke_claude(prompt):

    # Initialize the Amazon Bedrock runtime client
    client = boto3.client(service_name="bedrock-runtime", region_name="us-west-2")

    # Invoke Claude 3 with the text prompt
    model_id = "anthropic.claude-3-sonnet-20240229-v1:0"

    response = client.invoke_model(
        modelId=model_id,
        body=json.dumps(
            {
                "anthropic_version": "bedrock-2023-05-31",
                "max_tokens": 2200,
                "messages": [
                    {
                        "role": "user",
                        "content": [{"type": "text", "text": prompt}],
                    }
                ],
            }
        ),
    )

    # Process and print the response
    result = json.loads(response.get("body").read())
    input_tokens = result["usage"]["input_tokens"]
    output_tokens = result["usage"]["output_tokens"]
    output_list = result.get("content", [])

    #print("Invocation details:")
    print(f"- The input length is {input_tokens} tokens.")
    print(f"- The output length is {output_tokens} tokens.")
    return output_list



def lambda_handler(event, context):
    lang = event["language"]
    sqlstmt = event["sqltext"]
    
    #call claude 3
    promptdata = generate_prompt(lang,sqlstmt)
    response = invoke_claude(promptdata)
    output_data = ""
    for item in response:
        if item["type"] == "text":
            output_data += item["text"]
    	 return output_data

This creates a new Lambda function named InvokeLambdaCall in your account. In this example, the Lambda function receives the code and language as input from the Streamlit frontend. It then calls the Amazon Bedrock API to analyze the code, requesting both summaries and explanations. The function extracts the summary and explanations from the response and returns them as a dictionary. The AWS Identity and Access Management (IAM) role associated with the Lambda function should have permissions to call the Amazon Bedrock API.

Run the Streamlit application

With the Streamlit frontend and the Lambda function in place, you can now run the Streamlit application locally or deploy it to a hosting platform like AWS Elastic Beanstalk. To run the application locally, navigate to the directory containing your app.py file and run the following command:

   ```bash
   streamlit run app.py
   ```

This starts the Streamlit application. You can access it by opening the provided URL in your web browser. Make sure port 8501 is allowed for inbound connections.

Test the application

After the application is launched on the web browser, you can input your PL/SQL or T-SQL code snippets into the text area and choose Ctrl+Enter.

The application sends the code to the Lambda function, which interacts with Amazon Bedrock to generate summaries and explanations. The generated summary and code explanations are displayed at the bottom interface, allowing you to better understand the functionality and intent of the provided code.

The following video shows a short demonstration of the solution.

Clean up

If you no longer want to keep the Lambda function and associated resources, you can delete the function using the Lambda console. You can also remove access to the Anthropic Claude 3 model and delete other resources that you may have provisioned such as the EC2 instance.

Summary

In this post, we explored how you can use Amazon Bedrock to analyze PL/SQL and T-SQL code, making it more accessible and comprehensible for developers. By integrating Amazon Bedrock with a Streamlit frontend and a Lambda function, we created an application that allows users to input their code snippets and receive human-readable summaries and explanations.

Whether you’re working on legacy systems, onboarding new developers, or simply trying to understand unfamiliar code, the approach outlined in this post can be helpful. By taking advantage of the latest advancements in ML and NLP, you can unlock the potential of your code base and streamline the development process. We encourage you to try this solution and take advantage of AWS generative AI services such as Amazon Bedrock. Feel free to provide feedback on your experience or raise any questions you may have by posting a comment.

About the authors

Sagar Patel is a Principal Database Specialty Architect with the Professional Services team at Amazon Web Services. He works as a database migration specialist to provide technical guidance and help Amazon customers migrate their on-premises databases to AWS.

Vishal Srivastava is a Senior Partner Solutions Architect with the AMER GenAI Partner team at AWS. In his role, Vishal works with AWS Partners to provide guidance and technical assistance on generative AI projects, helping them improve the value of their solutions when using AWS.