12_bedrock/19_generate_sql_query_claude.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "dc40c48b-0c95-4757-a067-563cfccd51a5",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Invoke Bedrock model for SQL Query Generation\n",
    "\n",
    "> *If you see errors, you may need to be allow-listed for the Bedrock models used by this notebook*\n",
    "\n",
    "> *This notebook should work well with the **`Data Science 3.0`** kernel in SageMaker Studio*"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c9a413e2-3c34-4073-9000-d8556537bb6a",
   "metadata": {},
   "source": [
    "## Introduction\n",
    "\n",
    "In this notebook we show you how to use a LLM to generate SQL Query to analyze Sales data.\n",
    "\n",
    "We will use Bedrock's Claude V2 model using the Boto3 API. \n",
    "\n",
    "The prompt used in this example is called a zero-shot prompt because we are not providing any examples of text other than the prompt.\n",
    "\n",
    "**Note:** *This notebook can be run within or outside of AWS environment.*\n",
    "\n",
    "#### Context\n",
    "To demonstrate the SQL code generation capability of Amazon Bedrock, we will explore the use of Boto3 client to communicate with Amazon Bedrock API. We will demonstrate different configurations available as well as how simple input can lead to desired outputs.\n",
    "\n",
    "#### Pattern\n",
    "We will simply provide the Amazon Bedrock API with an input consisting of a task, an instruction and an input for the model under the hood to generate an output without providing any additional example. The purpose here is to demonstrate how the powerful LLMs easily understand the task at hand and generate compelling outputs.\n",
    "\n",
    "![](./images/sql-query-generation.png)\n",
    "\n",
    "#### Use case\n",
    "Let's take the use case to generate SQL queries to analyze sales data, focusing on trends, top products and average sales.\n",
    "\n",
    "#### Persona\n",
    "Maya is a business analyst, at AnyCompany primarily focusing on sales and inventory data. She is transitioning from Speadsheet analysis to data-driven analysis and want to use SQL to fetch specific data points effectively. She wants to use LLMs to generate SQL queries for her analysis. \n",
    "\n",
    "#### Implementation\n",
    "To fulfill this use case, in this notebook we will show how to generate SQL queries. We will use the Anthropic Claude v2 model using the Amazon Bedrock API with Boto3 client. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "64baae27-2660-4a1e-b2e5-3de49d069362",
   "metadata": {},
   "source": [
    "## Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "38b791ad-e6c5-4da5-96af-5c356a36e19d",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Looking in indexes: https://pypi.org/simple, https://pypi.ngc.nvidia.com\n",
      "Collecting boto3>=1.28.57\n",
      "  Obtaining dependency information for boto3>=1.28.57 from https://files.pythonhosted.org/packages/45/78/e95de33d8850429f6740763eb2d5350c02fd39e916f2ddb69fe649d25125/boto3-1.29.6-py3-none-any.whl.metadata\n",
      "  Downloading boto3-1.29.6-py3-none-any.whl.metadata (6.7 kB)\n",
      "Collecting awscli>=1.29.57\n",
      "  Obtaining dependency information for awscli>=1.29.57 from https://files.pythonhosted.org/packages/c9/fa/708f561f2ec441b9e9f6e9780a0519a7922a8e1f02c92671454983ff44c3/awscli-1.30.6-py3-none-any.whl.metadata\n",
      "  Downloading awscli-1.30.6-py3-none-any.whl.metadata (11 kB)\n",
      "Collecting botocore>=1.31.57\n",
      "  Obtaining dependency information for botocore>=1.31.57 from https://files.pythonhosted.org/packages/e4/dd/a6c7154d4f045599ce2df9199c01a8c2650c45ae74ad63f69547a500cc1c/botocore-1.32.6-py3-none-any.whl.metadata\n",
      "  Downloading botocore-1.32.6-py3-none-any.whl.metadata (6.1 kB)\n",
      "Collecting jmespath<2.0.0,>=0.7.1 (from boto3>=1.28.57)\n",
      "  Downloading jmespath-1.0.1-py3-none-any.whl (20 kB)\n",
      "Collecting s3transfer<0.8.0,>=0.7.0 (from boto3>=1.28.57)\n",
      "  Obtaining dependency information for s3transfer<0.8.0,>=0.7.0 from https://files.pythonhosted.org/packages/5a/4b/fec9ce18f8874a96c5061422625ba86c3ee1e6587ccd92ff9f5bf7bd91b2/s3transfer-0.7.0-py3-none-any.whl.metadata\n",
      "  Downloading s3transfer-0.7.0-py3-none-any.whl.metadata (1.8 kB)\n",
      "Collecting docutils<0.17,>=0.10 (from awscli>=1.29.57)\n",
      "  Downloading docutils-0.16-py2.py3-none-any.whl (548 kB)\n",
      "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m548.2/548.2 kB\u001b[0m \u001b[31m81.1 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
      "\u001b[?25hCollecting PyYAML<6.1,>=3.10 (from awscli>=1.29.57)\n",
      "  Obtaining dependency information for PyYAML<6.1,>=3.10 from https://files.pythonhosted.org/packages/29/61/bf33c6c85c55bc45a29eee3195848ff2d518d84735eb0e2d8cb42e0d285e/PyYAML-6.0.1-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl.metadata\n",
      "  Downloading PyYAML-6.0.1-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl.metadata (2.1 kB)\n",
      "Collecting colorama<0.4.5,>=0.2.5 (from awscli>=1.29.57)\n",
      "  Downloading colorama-0.4.4-py2.py3-none-any.whl (16 kB)\n",
      "Collecting rsa<4.8,>=3.1.2 (from awscli>=1.29.57)\n",
      "  Downloading rsa-4.7.2-py3-none-any.whl (34 kB)\n",
      "Collecting python-dateutil<3.0.0,>=2.1 (from botocore>=1.31.57)\n",
      "  Downloading python_dateutil-2.8.2-py2.py3-none-any.whl (247 kB)\n",
      "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m247.7/247.7 kB\u001b[0m \u001b[31m205.3 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
      "\u001b[?25hCollecting urllib3<2.1,>=1.25.4 (from botocore>=1.31.57)\n",
      "  Obtaining dependency information for urllib3<2.1,>=1.25.4 from https://files.pythonhosted.org/packages/d2/b2/b157855192a68541a91ba7b2bbcb91f1b4faa51f8bae38d8005c034be524/urllib3-2.0.7-py3-none-any.whl.metadata\n",
      "  Downloading urllib3-2.0.7-py3-none-any.whl.metadata (6.6 kB)\n",
      "Collecting six>=1.5 (from python-dateutil<3.0.0,>=2.1->botocore>=1.31.57)\n",
      "  Downloading six-1.16.0-py2.py3-none-any.whl (11 kB)\n",
      "Collecting pyasn1>=0.1.3 (from rsa<4.8,>=3.1.2->awscli>=1.29.57)\n",
      "  Obtaining dependency information for pyasn1>=0.1.3 from https://files.pythonhosted.org/packages/d1/75/4686d2872bf2fc0b37917cbc8bbf0dd3a5cdb0990799be1b9cbf1e1eb733/pyasn1-0.5.1-py2.py3-none-any.whl.metadata\n",
      "  Downloading pyasn1-0.5.1-py2.py3-none-any.whl.metadata (8.6 kB)\n",
      "Downloading boto3-1.29.6-py3-none-any.whl (135 kB)\n",
      "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m135.8/135.8 kB\u001b[0m \u001b[31m138.9 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
      "\u001b[?25hDownloading awscli-1.30.6-py3-none-any.whl (4.3 MB)\n",
      "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m4.3/4.3 MB\u001b[0m \u001b[31m51.1 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0ma \u001b[36m0:00:01\u001b[0m\n",
      "\u001b[?25hDownloading botocore-1.32.6-py3-none-any.whl (11.5 MB)\n",
      "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m11.5/11.5 MB\u001b[0m \u001b[31m91.3 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m \u001b[36m0:00:01\u001b[0m\n",
      "\u001b[?25hDownloading PyYAML-6.0.1-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (705 kB)\n",
      "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m705.5/705.5 kB\u001b[0m \u001b[31m214.0 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
      "\u001b[?25hDownloading s3transfer-0.7.0-py3-none-any.whl (79 kB)\n",
      "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m79.8/79.8 kB\u001b[0m \u001b[31m193.1 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
      "\u001b[?25hDownloading urllib3-2.0.7-py3-none-any.whl (124 kB)\n",
      "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m124.2/124.2 kB\u001b[0m \u001b[31m230.0 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
      "\u001b[?25hDownloading pyasn1-0.5.1-py2.py3-none-any.whl (84 kB)\n",
      "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m84.9/84.9 kB\u001b[0m \u001b[31m179.3 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
      "\u001b[?25hInstalling collected packages: urllib3, six, PyYAML, pyasn1, jmespath, docutils, colorama, rsa, python-dateutil, botocore, s3transfer, boto3, awscli\n",
      "  Attempting uninstall: urllib3\n",
      "    Found existing installation: urllib3 2.1.0\n",
      "    Uninstalling urllib3-2.1.0:\n",
      "      Successfully uninstalled urllib3-2.1.0\n",
      "  Attempting uninstall: six\n",
      "    Found existing installation: six 1.16.0\n",
      "    Uninstalling six-1.16.0:\n",
      "      Successfully uninstalled six-1.16.0\n",
      "  Attempting uninstall: PyYAML\n",
      "    Found existing installation: PyYAML 6.0.1\n",
      "    Uninstalling PyYAML-6.0.1:\n",
      "      Successfully uninstalled PyYAML-6.0.1\n",
      "  Attempting uninstall: pyasn1\n",
      "    Found existing installation: pyasn1 0.5.1\n",
      "    Uninstalling pyasn1-0.5.1:\n",
      "      Successfully uninstalled pyasn1-0.5.1\n",
      "  Attempting uninstall: jmespath\n",
      "    Found existing installation: jmespath 1.0.1\n",
      "    Uninstalling jmespath-1.0.1:\n",
      "      Successfully uninstalled jmespath-1.0.1\n",
      "  Attempting uninstall: docutils\n",
      "    Found existing installation: docutils 0.16\n",
      "    Uninstalling docutils-0.16:\n",
      "      Successfully uninstalled docutils-0.16\n",
      "  Attempting uninstall: colorama\n",
      "    Found existing installation: colorama 0.4.4\n",
      "    Uninstalling colorama-0.4.4:\n",
      "      Successfully uninstalled colorama-0.4.4\n",
      "  Attempting uninstall: rsa\n",
      "    Found existing installation: rsa 4.7.2\n",
      "    Uninstalling rsa-4.7.2:\n",
      "      Successfully uninstalled rsa-4.7.2\n",
      "  Attempting uninstall: python-dateutil\n",
      "    Found existing installation: python-dateutil 2.8.2\n",
      "    Uninstalling python-dateutil-2.8.2:\n",
      "      Successfully uninstalled python-dateutil-2.8.2\n",
      "  Attempting uninstall: botocore\n",
      "    Found existing installation: botocore 1.32.6\n",
      "    Uninstalling botocore-1.32.6:\n",
      "      Successfully uninstalled botocore-1.32.6\n",
      "  Attempting uninstall: s3transfer\n",
      "    Found existing installation: s3transfer 0.7.0\n",
      "    Uninstalling s3transfer-0.7.0:\n",
      "      Successfully uninstalled s3transfer-0.7.0\n",
      "  Attempting uninstall: boto3\n",
      "    Found existing installation: boto3 1.29.6\n",
      "    Uninstalling boto3-1.29.6:\n",
      "      Successfully uninstalled boto3-1.29.6\n",
      "  Attempting uninstall: awscli\n",
      "    Found existing installation: awscli 1.30.6\n",
      "    Uninstalling awscli-1.30.6:\n",
      "      Successfully uninstalled awscli-1.30.6\n",
      "\u001b[31mERROR: pip's dependency resolver does not currently take into account all the packages that are installed. This behaviour is the source of the following dependency conflicts.\n",
      "spyder 5.3.3 requires pyqt5<5.16, which is not installed.\n",
      "spyder 5.3.3 requires pyqtwebengine<5.16, which is not installed.\n",
      "distributed 2022.7.0 requires tornado<6.2,>=6.0.3, but you have tornado 6.3.3 which is incompatible.\n",
      "jupyterlab 3.4.4 requires jupyter-server~=1.16, but you have jupyter-server 2.7.3 which is incompatible.\n",
      "jupyterlab-server 2.10.3 requires jupyter-server~=1.4, but you have jupyter-server 2.7.3 which is incompatible.\n",
      "panel 0.13.1 requires bokeh<2.5.0,>=2.4.0, but you have bokeh 3.3.0 which is incompatible.\n",
      "pyasn1-modules 0.2.8 requires pyasn1<0.5.0,>=0.4.6, but you have pyasn1 0.5.1 which is incompatible.\n",
      "sagemaker-datawrangler 0.4.3 requires sagemaker-data-insights==0.4.0, but you have sagemaker-data-insights 0.3.3 which is incompatible.\n",
      "spyder 5.3.3 requires ipython<8.0.0,>=7.31.1, but you have ipython 8.16.1 which is incompatible.\n",
      "spyder 5.3.3 requires pylint<3.0,>=2.5.0, but you have pylint 3.0.1 which is incompatible.\n",
      "spyder-kernels 2.3.3 requires ipython<8,>=7.31.1; python_version >= \"3\", but you have ipython 8.16.1 which is incompatible.\u001b[0m\u001b[31m\n",
      "\u001b[0mSuccessfully installed PyYAML-6.0.1 awscli-1.30.6 boto3-1.29.6 botocore-1.32.6 colorama-0.4.4 docutils-0.16 jmespath-1.0.1 pyasn1-0.5.1 python-dateutil-2.8.2 rsa-4.7.2 s3transfer-0.7.0 six-1.16.0 urllib3-2.0.7\n",
      "\u001b[33mWARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv\u001b[0m\u001b[33m\n",
      "\u001b[0m\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m A new release of pip is available: \u001b[0m\u001b[31;49m23.2.1\u001b[0m\u001b[39;49m -> \u001b[0m\u001b[32;49m23.3.1\u001b[0m\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m To update, run: \u001b[0m\u001b[32;49mpip install --upgrade pip\u001b[0m\n",
      "Note: you may need to restart the kernel to use updated packages.\n",
      "\u001b[33mWARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv\u001b[0m\u001b[33m\n",
      "\u001b[0m\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m A new release of pip is available: \u001b[0m\u001b[31;49m23.2.1\u001b[0m\u001b[39;49m -> \u001b[0m\u001b[32;49m23.3.1\u001b[0m\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m To update, run: \u001b[0m\u001b[32;49mpip install --upgrade pip\u001b[0m\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "%pip install --no-build-isolation --force-reinstall \\\n",
    "    \"boto3>=1.28.57\" \\\n",
    "    \"awscli>=1.29.57\" \\\n",
    "    \"botocore>=1.31.57\"\n",
    "\n",
    "%pip install --quiet langchain==0.0.309"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "daee5311",
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "f79948ca-7fe9-4289-9937-f1be2004ae7a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "#### Un comment the following lines to run from your local environment outside of the AWS account with Bedrock access\n",
    "\n",
    "#import os\n",
    "#os.environ['BEDROCK_ASSUME_ROLE'] = '<YOUR_VALUES>'\n",
    "#os.environ['AWS_PROFILE'] = '<YOUR_VALUES>'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "a2226949-b933-4287-b075-7c88da439ac9",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import boto3\n",
    "import json \n",
    "\n",
    "bedrock = boto3.client(service_name=\"bedrock\")\n",
    "bedrock_runtime = boto3.client(service_name=\"bedrock-runtime\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f634211-3de1-4390-8c3f-367af5554c39",
   "metadata": {},
   "source": [
    "## Generate SQL Query\n",
    "\n",
    "Following on the use case explained above, let's prepare an input for  the Amazon Bedrock service to generate SQL query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "45ee2bae-6415-4dba-af98-a19028305c98",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# create the prompt to generate SQL query\n",
    "prompt_data = \"\"\"\n",
    "\n",
    "Human: AnyCompany has a database with a table named sales_data containing sales records. The table has following columns:\n",
    "- date (YYYY-MM-DD)\n",
    "- product_id\n",
    "- price\n",
    "- units_sold\n",
    "\n",
    "Can you generate SQL queries for the below: \n",
    "- Identify the top 5 best selling products by total sales for the year 2023\n",
    "- Calculate the monthly average sales for the year 2023\n",
    "\n",
    "Assistant:\n",
    "\"\"\"\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cc9784e5-5e9d-472d-8ef1-34108ee4968b",
   "metadata": {},
   "source": [
    "Let's start by using the Anthorpic Claude v2 model. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "8af670eb-ad02-40df-a19c-3ed835fac8d9",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Claude - Body Syntex\n",
    "body = json.dumps({\n",
    "                    \"prompt\": prompt_data,\n",
    "                    \"max_tokens_to_sample\":4096,\n",
    "                    \"temperature\":0.5,\n",
    "                    \"top_k\":250,\n",
    "                    \"top_p\":0.5,\n",
    "                    \"stop_sequences\": [\"\\n\\nHuman:\"]\n",
    "                  }) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "088cf6bf-dd73-4710-a0cc-6c11d220c431",
   "metadata": {},
   "source": [
    "#### Invoke the Bedrock's Claude Large Large language model"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "379498f2",
   "metadata": {},
   "source": [
    "First, we explore how the model generates an output based on the prompt created earlier.\n",
    "\n",
    "##### Complete Output Generation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ecaceef1-0f7f-4ae5-8007-ff7c25335251",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " Here are the SQL queries to answer the questions:\n",
      "\n",
      "1. Identify the top 5 best selling products by total sales for the year 2023:\n",
      "\n",
      "```sql\n",
      "SELECT product_id, SUM(price * units_sold) AS total_sales\n",
      "FROM sales_data\n",
      "WHERE date BETWEEN '2023-01-01' AND '2023-12-31'\n",
      "GROUP BY product_id\n",
      "ORDER BY total_sales DESC\n",
      "LIMIT 5;\n",
      "```\n",
      "\n",
      "2. Calculate the monthly average sales for the year 2023:\n",
      "\n",
      "```sql \n",
      "SELECT \n",
      "  DATE_FORMAT(date, '%Y-%m') AS month,\n",
      "  AVG(price * units_sold) AS avg_monthly_sales\n",
      "FROM sales_data\n",
      "WHERE date BETWEEN '2023-01-01' AND '2023-12-31'  \n",
      "GROUP BY month\n",
      "ORDER BY month;\n",
      "```\n",
      "\n",
      "The first query groups the sales data by product_id and sums the total sales for each product in 2023. It then orders by the total_sales column descending and limits to the top 5 results. \n",
      "\n",
      "The second query extracts the month from the date column, groups the data by month, calculates the average monthly sales using AVG() and orders the results chronologically.\n"
     ]
    }
   ],
   "source": [
    "modelId = 'anthropic.claude-v2' # change this to use a different version from the model provider\n",
    "accept = 'application/json'\n",
    "contentType = 'application/json'\n",
    "\n",
    "response = bedrock_runtime.invoke_model(body=body, modelId=modelId, accept=accept, contentType=contentType)\n",
    "response_body = json.loads(response.get('body').read())\n",
    "\n",
    "print(response_body.get('completion'))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "078b9db4",
   "metadata": {},
   "source": [
    "### Advanced Example\n",
    "#### Understanding Hospital's Patient Management System through SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "d439b90c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create the prompt\n",
    "prompt_sql_data = \"\"\"\n",
    "\n",
    "Human: You're provided with a database schema representing any hospital's patient management system.\n",
    "The system holds records about patients, their prescriptions, doctors, and the medications prescribed.\n",
    "\n",
    "Here's the schema:\n",
    "\n",
    "```sql\n",
    "CREATE TABLE Patients (\n",
    "    PatientID int,\n",
    "    FirstName varchar(50),\n",
    "    LastName varchar(50),\n",
    "    DateOfBirth datetime,\n",
    "    Gender varchar(10),\n",
    "    PRIMARY KEY (PatientID)\n",
    ");\n",
    "\n",
    "CREATE TABLE Doctors (\n",
    "    DoctorID int,\n",
    "    FirstName varchar(50),\n",
    "    LastName varchar(50),\n",
    "    Specialization varchar(50),\n",
    "    PRIMARY KEY (DoctorID)\n",
    ");\n",
    "\n",
    "CREATE TABLE Prescriptions (\n",
    "    PrescriptionID int,\n",
    "    PatientID int,\n",
    "    DoctorID int,\n",
    "    DateIssued datetime,\n",
    "    PRIMARY KEY (PrescriptionID)\n",
    ");\n",
    "\n",
    "CREATE TABLE Medications (\n",
    "    MedicationID int,\n",
    "    MedicationName varchar(50),\n",
    "    Dosage varchar(50),\n",
    "    PRIMARY KEY (MedicationID)\n",
    ");\n",
    "\n",
    "CREATE TABLE PrescriptionDetails (\n",
    "    PrescriptionDetailID int,\n",
    "    PrescriptionID int,\n",
    "    MedicationID int,\n",
    "    Quantity int,\n",
    "    PRIMARY KEY (PrescriptionDetailID)\n",
    ");\n",
    "```\n",
    "\n",
    "Write a SQL query that fetches all the patients who were prescribed more than 5 different medications on 2023-04-01.\n",
    "\n",
    "Assistant:\n",
    "\"\"\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "9afa3431",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Claude - Body Syntex\n",
    "body = json.dumps({\n",
    "                    \"prompt\": prompt_sql_data,\n",
    "                    \"max_tokens_to_sample\":4096,\n",
    "                    \"temperature\":0.5,\n",
    "                    \"top_k\":250,\n",
    "                    \"top_p\":0.5,\n",
    "                    \"stop_sequences\": [\"\\n\\nHuman:\"]\n",
    "                  }) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "5c45f4fc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " Here is a SQL query to fetch patients who were prescribed more than 5 different medications on 2023-04-01:\n",
      "\n",
      "```sql\n",
      "SELECT p.FirstName, p.LastName\n",
      "FROM Patients p\n",
      "JOIN Prescriptions pre ON p.PatientID = pre.PatientID\n",
      "JOIN PrescriptionDetails pd ON pre.PrescriptionID = pd.PrescriptionID\n",
      "WHERE pre.DateIssued = '2023-04-01'\n",
      "GROUP BY p.PatientID\n",
      "HAVING COUNT(DISTINCT pd.MedicationID) > 5;\n",
      "```\n",
      "\n",
      "The key steps are:\n",
      "\n",
      "1. Join the Patients, Prescriptions and PrescriptionDetails tables to connect patients with their prescriptions and medication details. \n",
      "\n",
      "2. Filter to only prescriptions issued on 2023-04-01.\n",
      "\n",
      "3. Group by PatientID and count the distinct MedicationIDs per patient. \n",
      "\n",
      "4. Use HAVING clause to only keep patients with more than 5 distinct medications.\n",
      "\n",
      "5. Select the first and last name columns for the matching patients.\n"
     ]
    }
   ],
   "source": [
    "modelId = 'anthropic.claude-v2' # change this to use a different version from the model provider\n",
    "accept = 'application/json'\n",
    "contentType = 'application/json'\n",
    "\n",
    "response = bedrock_runtime.invoke_model(body=body, modelId=modelId, accept=accept, contentType=contentType)\n",
    "response_body = json.loads(response.get('body').read())\n",
    "\n",
    "print(response_body.get('completion'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "217d44fd-2e3b-4e07-9fb3-65bf0ef47c8a",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "availableInstances": [
   {
    "_defaultOrder": 0,
    "_isFastLaunch": true,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 4,
    "name": "ml.t3.medium",
    "vcpuNum": 2
   },
   {
    "_defaultOrder": 1,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 8,
    "name": "ml.t3.large",
    "vcpuNum": 2
   },
   {
    "_defaultOrder": 2,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 16,
    "name": "ml.t3.xlarge",
    "vcpuNum": 4
   },
   {
    "_defaultOrder": 3,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 32,
    "name": "ml.t3.2xlarge",
    "vcpuNum": 8
   },
   {
    "_defaultOrder": 4,
    "_isFastLaunch": true,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 8,
    "name": "ml.m5.large",
    "vcpuNum": 2
   },
   {
    "_defaultOrder": 5,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 16,
    "name": "ml.m5.xlarge",
    "vcpuNum": 4
   },
   {
    "_defaultOrder": 6,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 32,
    "name": "ml.m5.2xlarge",
    "vcpuNum": 8
   },
   {
    "_defaultOrder": 7,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 64,
    "name": "ml.m5.4xlarge",
    "vcpuNum": 16
   },
   {
    "_defaultOrder": 8,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 128,
    "name": "ml.m5.8xlarge",
    "vcpuNum": 32
   },
   {
    "_defaultOrder": 9,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 192,
    "name": "ml.m5.12xlarge",
    "vcpuNum": 48
   },
   {
    "_defaultOrder": 10,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 256,
    "name": "ml.m5.16xlarge",
    "vcpuNum": 64
   },
   {
    "_defaultOrder": 11,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 384,
    "name": "ml.m5.24xlarge",
    "vcpuNum": 96
   },
   {
    "_defaultOrder": 12,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 8,
    "name": "ml.m5d.large",
    "vcpuNum": 2
   },
   {
    "_defaultOrder": 13,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 16,
    "name": "ml.m5d.xlarge",
    "vcpuNum": 4
   },
   {
    "_defaultOrder": 14,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 32,
    "name": "ml.m5d.2xlarge",
    "vcpuNum": 8
   },
   {
    "_defaultOrder": 15,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 64,
    "name": "ml.m5d.4xlarge",
    "vcpuNum": 16
   },
   {
    "_defaultOrder": 16,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 128,
    "name": "ml.m5d.8xlarge",
    "vcpuNum": 32
   },
   {
    "_defaultOrder": 17,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 192,
    "name": "ml.m5d.12xlarge",
    "vcpuNum": 48
   },
   {
    "_defaultOrder": 18,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 256,
    "name": "ml.m5d.16xlarge",
    "vcpuNum": 64
   },
   {
    "_defaultOrder": 19,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 384,
    "name": "ml.m5d.24xlarge",
    "vcpuNum": 96
   },
   {
    "_defaultOrder": 20,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": true,
    "memoryGiB": 0,
    "name": "ml.geospatial.interactive",
    "supportedImageNames": [
     "sagemaker-geospatial-v1-0"
    ],
    "vcpuNum": 0
   },
   {
    "_defaultOrder": 21,
    "_isFastLaunch": true,
    "category": "Compute optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 4,
    "name": "ml.c5.large",
    "vcpuNum": 2
   },
   {
    "_defaultOrder": 22,
    "_isFastLaunch": false,
    "category": "Compute optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 8,
    "name": "ml.c5.xlarge",
    "vcpuNum": 4
   },
   {
    "_defaultOrder": 23,
    "_isFastLaunch": false,
    "category": "Compute optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 16,
    "name": "ml.c5.2xlarge",
    "vcpuNum": 8
   },
   {
    "_defaultOrder": 24,
    "_isFastLaunch": false,
    "category": "Compute optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 32,
    "name": "ml.c5.4xlarge",
    "vcpuNum": 16
   },
   {
    "_defaultOrder": 25,
    "_isFastLaunch": false,
    "category": "Compute optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 72,
    "name": "ml.c5.9xlarge",
    "vcpuNum": 36
   },
   {
    "_defaultOrder": 26,
    "_isFastLaunch": false,
    "category": "Compute optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 96,
    "name": "ml.c5.12xlarge",
    "vcpuNum": 48
   },
   {
    "_defaultOrder": 27,
    "_isFastLaunch": false,
    "category": "Compute optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 144,
    "name": "ml.c5.18xlarge",
    "vcpuNum": 72
   },
   {
    "_defaultOrder": 28,
    "_isFastLaunch": false,
    "category": "Compute optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 192,
    "name": "ml.c5.24xlarge",
    "vcpuNum": 96
   },
   {
    "_defaultOrder": 29,
    "_isFastLaunch": true,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 16,
    "name": "ml.g4dn.xlarge",
    "vcpuNum": 4
   },
   {
    "_defaultOrder": 30,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 32,
    "name": "ml.g4dn.2xlarge",
    "vcpuNum": 8
   },
   {
    "_defaultOrder": 31,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 64,
    "name": "ml.g4dn.4xlarge",
    "vcpuNum": 16
   },
   {
    "_defaultOrder": 32,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 128,
    "name": "ml.g4dn.8xlarge",
    "vcpuNum": 32
   },
   {
    "_defaultOrder": 33,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 4,
    "hideHardwareSpecs": false,
    "memoryGiB": 192,
    "name": "ml.g4dn.12xlarge",
    "vcpuNum": 48
   },
   {
    "_defaultOrder": 34,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 256,
    "name": "ml.g4dn.16xlarge",
    "vcpuNum": 64
   },
   {
    "_defaultOrder": 35,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 61,
    "name": "ml.p3.2xlarge",
    "vcpuNum": 8
   },
   {
    "_defaultOrder": 36,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 4,
    "hideHardwareSpecs": false,
    "memoryGiB": 244,
    "name": "ml.p3.8xlarge",
    "vcpuNum": 32
   },
   {
    "_defaultOrder": 37,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 8,
    "hideHardwareSpecs": false,
    "memoryGiB": 488,
    "name": "ml.p3.16xlarge",
    "vcpuNum": 64
   },
   {
    "_defaultOrder": 38,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 8,
    "hideHardwareSpecs": false,
    "memoryGiB": 768,
    "name": "ml.p3dn.24xlarge",
    "vcpuNum": 96
   },
   {
    "_defaultOrder": 39,
    "_isFastLaunch": false,
    "category": "Memory Optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 16,
    "name": "ml.r5.large",
    "vcpuNum": 2
   },
   {
    "_defaultOrder": 40,
    "_isFastLaunch": false,
    "category": "Memory Optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 32,
    "name": "ml.r5.xlarge",
    "vcpuNum": 4
   },
   {
    "_defaultOrder": 41,
    "_isFastLaunch": false,
    "category": "Memory Optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 64,
    "name": "ml.r5.2xlarge",
    "vcpuNum": 8
   },
   {
    "_defaultOrder": 42,
    "_isFastLaunch": false,
    "category": "Memory Optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 128,
    "name": "ml.r5.4xlarge",
    "vcpuNum": 16
   },
   {
    "_defaultOrder": 43,
    "_isFastLaunch": false,
    "category": "Memory Optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 256,
    "name": "ml.r5.8xlarge",
    "vcpuNum": 32
   },
   {
    "_defaultOrder": 44,
    "_isFastLaunch": false,
    "category": "Memory Optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 384,
    "name": "ml.r5.12xlarge",
    "vcpuNum": 48
   },
   {
    "_defaultOrder": 45,
    "_isFastLaunch": false,
    "category": "Memory Optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 512,
    "name": "ml.r5.16xlarge",
    "vcpuNum": 64
   },
   {
    "_defaultOrder": 46,
    "_isFastLaunch": false,
    "category": "Memory Optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 768,
    "name": "ml.r5.24xlarge",
    "vcpuNum": 96
   },
   {
    "_defaultOrder": 47,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 16,
    "name": "ml.g5.xlarge",
    "vcpuNum": 4
   },
   {
    "_defaultOrder": 48,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 32,
    "name": "ml.g5.2xlarge",
    "vcpuNum": 8
   },
   {
    "_defaultOrder": 49,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 64,
    "name": "ml.g5.4xlarge",
    "vcpuNum": 16
   },
   {
    "_defaultOrder": 50,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 128,
    "name": "ml.g5.8xlarge",
    "vcpuNum": 32
   },
   {
    "_defaultOrder": 51,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 256,
    "name": "ml.g5.16xlarge",
    "vcpuNum": 64
   },
   {
    "_defaultOrder": 52,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 4,
    "hideHardwareSpecs": false,
    "memoryGiB": 192,
    "name": "ml.g5.12xlarge",
    "vcpuNum": 48
   },
   {
    "_defaultOrder": 53,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 4,
    "hideHardwareSpecs": false,
    "memoryGiB": 384,
    "name": "ml.g5.24xlarge",
    "vcpuNum": 96
   },
   {
    "_defaultOrder": 54,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 8,
    "hideHardwareSpecs": false,
    "memoryGiB": 768,
    "name": "ml.g5.48xlarge",
    "vcpuNum": 192
   },
   {
    "_defaultOrder": 55,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 8,
    "hideHardwareSpecs": false,
    "memoryGiB": 1152,
    "name": "ml.p4d.24xlarge",
    "vcpuNum": 96
   },
   {
    "_defaultOrder": 56,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 8,
    "hideHardwareSpecs": false,
    "memoryGiB": 1152,
    "name": "ml.p4de.24xlarge",
    "vcpuNum": 96
   },
   {
    "_defaultOrder": 57,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 32,
    "name": "ml.trn1.2xlarge",
    "vcpuNum": 8
   },
   {
    "_defaultOrder": 58,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 512,
    "name": "ml.trn1.32xlarge",
    "vcpuNum": 128
   },
   {
    "_defaultOrder": 59,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 512,
    "name": "ml.trn1n.32xlarge",
    "vcpuNum": 128
   }
  ],
  "instance_type": "ml.t3.medium",
  "kernelspec": {
   "display_name": "Python 3 (Data Science 3.0)",
   "language": "python",
   "name": "python3__SAGEMAKER_INTERNAL__arn:aws:sagemaker:us-east-1:081325390199:image/sagemaker-data-science-310-v1"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}