{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "0fb7f3db",
   "metadata": {},
   "source": [
    "*Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved*  \n",
    "\n",
    "*SPDX-License-Identifier: MIT-0*    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b78e5991",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "81e637d6",
   "metadata": {},
   "source": [
    "## Connecting to Amazon Keyspaces using server-side credentials \n",
    "\n",
    "This code shows how to connect to Amazon Keyspaces from SageMaker using an [service-specific credentials](https://docs.aws.amazon.com/keyspaces/latest/devguide/programmatic.credentials.html) for an existing AWS Identity and Access Management (IAM) user.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a1a923c",
   "metadata": {},
   "source": [
    "Before we start we need to generate the Keyspaces credential and use SecretManager to securly store credentials. \n",
    "\n",
    "\n",
    "1. Generate [Keyspaces Service-Specific Credentials](https://docs.aws.amazon.com/keyspaces/latest/devguide/programmatic.credentials.html)\n",
    "\n",
    "\n",
    "The following is an example of a service-specific credential .\n",
    "\n",
    "```\n",
    "\"ServiceSpecificCredential\": {\n",
    "        \"CreateDate\": \"2019-10-09T16:12:04Z\",\n",
    "        \"ServiceName\": \"cassandra.amazonaws.com\",\n",
    "        \"ServiceUserName\": \"keyspace-user1-at-11122223333\",\n",
    "        \"ServicePassword\": \"wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY\",\n",
    "        \"ServiceSpecificCredentialId\": \"ACCAYFI33SINPGJEBYESF\",\n",
    "        \"UserName\": \" keyspace-user1\",\n",
    "        \"Status\": \"Active\"\n",
    "    }\n",
    "}\n",
    "```\n",
    "\n",
    "2. Store ServiceUserName and ServicePassword in the SecretManager.  As a best practice we don't want to store credential in SageMaker Notebooks in plain text. \n",
    "\n",
    "  In this example I'm using \n",
    "*Keyspaces_Server_Generated_credential* as a Secret name and _keyspaces_generated_id_ and _keyspaces_generated_pw_ fields to store Keyspaces ID and password. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a91e2ab0",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Import \n",
    "from cassandra.cluster import Cluster\n",
    "from ssl import SSLContext, PROTOCOL_TLSv1_2 , CERT_REQUIRED\n",
    "from cassandra.auth import PlainTextAuthProvider\n",
    "\n",
    "ssl_context = SSLContext(PROTOCOL_TLSv1_2 )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "68e63b3f",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Download certificate \n",
    "!curl https://certs.secureserver.net/repository/sf-class2-root.crt -O "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a714c7dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load certificate \n",
    "ssl_context.load_verify_locations('sf-class2-root.crt') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "265d59f0",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Getting Credential from Secert Manager \n",
    "#Need to have a SecretsManagerReadWrite Policy\n",
    "\n",
    "import boto3\n",
    "import base64\n",
    "from botocore.exceptions import ClientError\n",
    "import json\n",
    "\n",
    "secret_name = \"Keyspaces_Server_Generated_credential\"\n",
    "region_name = \"us-west-1\"\n",
    "\n",
    "# Create a Secrets Manager client\n",
    "session = boto3.session.Session()\n",
    "client = session.client(\n",
    "    service_name='secretsmanager',\n",
    "    region_name=region_name\n",
    ")\n",
    "\n",
    "\n",
    "get_secret_value_response = client.get_secret_value(\n",
    "    SecretId=secret_name\n",
    ")\n",
    "\n",
    "# Decrypts secret using the associated KMS CMK.\n",
    "# Depending on whether the secret is a string or binary, one of these fields will be populated.\n",
    "if 'SecretString' in get_secret_value_response:\n",
    "    secret = json.loads(get_secret_value_response['SecretString'])\n",
    "    ServiceUserName = secret['keyspaces_generated_id']\n",
    "    ServicePassword = secret['keyspaces_generated_pw']\n",
    "    print(\"id:\",ServiceUserName, \",  pw:\",ServicePassword)\n",
    "else:\n",
    "    decoded_binary_secret = base64.b64decode(get_secret_value_response['SecretBinary'])\n",
    "             \n",
    "            "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2a98e386",
   "metadata": {},
   "outputs": [],
   "source": [
    "auth_provider = PlainTextAuthProvider(username=ServiceUserName, password=ServicePassword)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bad5758b",
   "metadata": {},
   "outputs": [],
   "source": [
    "cluster = Cluster(['cassandra.us-west-1.amazonaws.com'], ssl_context=ssl_context, auth_provider=auth_provider, port=9142)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6b22f21d",
   "metadata": {},
   "outputs": [],
   "source": [
    "session = cluster.connect()\n",
    "cql = \"select * from  system_schema.keyspaces \"\n",
    "r = session.execute(cql)\n",
    "print(r.current_rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eb124f18",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load rows to Panda DataFrame \n",
    "from pandas import DataFrame\n",
    "\n",
    "df = DataFrame(r)\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c9b9169",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "conda_python3",
   "language": "python",
   "name": "conda_python3"
  },
  "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.6.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}