Create_,_Update_,_Delete_Athena_Prepared_Statement_using_boto3.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyOldsBynHv6Pz97bdTKjPP2",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/SatadruMukherjee/Data-Preprocessing-Models/blob/main/Create_%2C_Update_%2C_Delete_Athena_Prepared_Statement_using_boto3.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Prerequisite:\n",
        "https://github.com/SatadruMukherjee/Data-Preprocessing-Models/blob/main/Athena_parameterized_queries_using_Python.ipynb"
      ],
      "metadata": {
        "id": "999exryIKhvN"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "OqDN6-_N1yNb"
      },
      "outputs": [],
      "source": [
        "!pip install boto3"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import boto3\n",
        "access_key=''\n",
        "secret_key=''\n",
        "session = boto3.Session(\n",
        "    aws_access_key_id=access_key,\n",
        "    aws_secret_access_key=secret_key,region_name='us-east-1'\n",
        ")\n",
        "athena_client = session.client('athena')\n"
      ],
      "metadata": {
        "id": "v_Ao18Cc116f"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Create prepared statements using boto3"
      ],
      "metadata": {
        "id": "6Bxn7tJl2kFb"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "resp = athena_client.create_prepared_statement(\n",
        "        StatementName = \"iris_fulla\",\n",
        "        WorkGroup = \"primary\",\n",
        "        QueryStatement = \"\"\"\n",
        "        SELECT sum(sepal_length) FROM irisdemo WHERE variety = ? \n",
        "        \"\"\"\n",
        "    )"
      ],
      "metadata": {
        "id": "z-vW5iQs17HT"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Update prepared statements using boto3"
      ],
      "metadata": {
        "id": "FfniHtX924WU"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "response = athena_client.update_prepared_statement(\n",
        "    StatementName = \"iris_fulla\",\n",
        "        WorkGroup = \"primary\",\n",
        "        QueryStatement = \"\"\"\n",
        "        SELECT sum(petal_length) FROM irisdemo WHERE variety = ? ;\n",
        "        \"\"\"\n",
        ")"
      ],
      "metadata": {
        "id": "2k0vZkzP2zZd"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Delete prepared statements\n"
      ],
      "metadata": {
        "id": "VWq2ha3a3jt9"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "--to remove the prepared statement\n",
        "DEALLOCATE PREPARE iris_fulla;"
      ],
      "metadata": {
        "id": "YtU9ZqcY3mUg"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "response = athena_client.delete_prepared_statement(\n",
        "    StatementName='iris_fulla',\n",
        "    WorkGroup='primary'\n",
        ")"
      ],
      "metadata": {
        "id": "140SfLvO6Tp1"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# *security **using** Amazon Athena parameterized queries*"
      ],
      "metadata": {
        "id": "pCzKTd1B7PRt"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "https://aws.amazon.com/blogs/big-data/improve-reusability-and-security-using-amazon-athena-parameterized-queries/"
      ],
      "metadata": {
        "id": "tQ2DAFoJKq1d"
      }
    }
  ]
}