tutorials/001 - Connecting to Amazon Redshift.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Connecting to Amazon Redshift"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Overview\n",
    "`redshift_connector` provides multiple options when it comes to establishing a connection to an Amazon Redshift cluster. These options are discussed and shown below."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using Database credentials\n",
    "Raw database credentials can be used for establishing a connection to an Amazon Redshift cluster. While straight forward, this approach lack the strong security and user access controls provides by Identity and access management (IAM) and identity provider (IdP) plugins."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import redshift_connector\n",
    "\n",
    "# establish a connection to an Amazon Redshift cluster\n",
    "\n",
    "# here we use \"with\" statements to ensure connection\n",
    "# and cursor resources are cleaned up once we are finished\n",
    "# with them\n",
    "\n",
    "with redshift_connector.connect(\n",
    "    host='examplecluster.abc123xyz789.us-west-1.redshift.amazonaws.com',\n",
    "    database='dev',\n",
    "    user='awsuser',\n",
    "    password='my_password'\n",
    "    # port value of 5439 is specified by default\n",
    ") as conn:\n",
    "    with conn.cursor() as cursor:\n",
    "       # Please note: autocommit is disabled by default, per DB-API specification\n",
    "       # If you'd like to commit your changes, manually commit or enable autocommit\n",
    "       # on the cursor object\n",
    "\n",
    "       # conn.commit()  # manually commits\n",
    "       # conn.autocommit = True  # enables autocommit for subsequent SQL statements\n",
    "        cursor.execute(\"create table book(bookname varchar,author varchar)\")\n",
    "        cursor.executemany(\"insert into book (bookname, author) values (%s, %s)\",\n",
    "                        [\n",
    "                            ('One Hundred Years of Solitude', 'Gabriel García Márquez'),\n",
    "                            ('A Brief History of Time', 'Stephen Hawking')\n",
    "                        ]\n",
    "                      )\n",
    "        cursor.execute(\"select * from book\")\n",
    "\n",
    "        result: tuple = cursor.fetchall()\n",
    "        print(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using IAM Credentials\n",
    "IAM Credentials can be supplied directly to ``connect(...)`` using an AWS profile. This approach allows users the option of using temporary credentials and limiting the permissions the connected user has."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import redshift_connector\n",
    "\n",
    "# Connects to Redshift cluster using IAM credentials from default profile defined in ~/.aws/credentials\n",
    "conn: redshift_connector.Connection = redshift_connector.connect(\n",
    "    iam=True,\n",
    "    database='dev',\n",
    "    db_user='awsuser',\n",
    "    password='',\n",
    "    user='',\n",
    "    cluster_identifier='examplecluster',\n",
    "    profile='default'\n",
    " )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Below a sample `~/.aws/config` and `~/.aws/credentials` are shown. Please note that `redshift_connector` requires a `region` to be specified when usign IAM authentication. The region can be specified either in `~/.aws/config` or passed directly to `redshift_connector.connect(...)`. In the case where a region is specified in both `~/.aws/config` and `redshift_connector.connect(...)`, the value provided to `redshift_connector.connect(...)` will be used."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`~/.aws/credentials`\n",
    "```\n",
    "[default]\n",
    "aws_access_key_id=\"my_aws_access_key_id\"\n",
    "aws_secret_access_key=\"my_aws_secret_access_key\"\n",
    "aws_session_token=\"my_aws_session_token\"\n",
    "```\n",
    "`~/.aws/config`\n",
    "```\n",
    "[default]\n",
    "region=us-west-2\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alternatively, IAM credentials can be supplied directly to ``connect(...)`` using AWS credentials as shown below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import redshift_connector\n",
    "\n",
    "# Connects to Redshift cluster using IAM credentials from default profile defined in ~/.aws/credentials\n",
    "conn: redshift_connector.Connection = redshift_connector.connect(\n",
    "    iam=True,\n",
    "    database='dev',\n",
    "    db_user='awsuser',\n",
    "    password='',\n",
    "    user='',\n",
    "    cluster_identifier='examplecluster',\n",
    "    access_key_id=\"my_aws_access_key_id\",\n",
    "    secret_access_key=\"my_aws_secret_access_key\",\n",
    "    session_token=\"my_aws_session_token\",\n",
    "    region=\"us-east-2\"\n",
    " )\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Connecting using an Amazon Redshift Authentication Profile\n",
    "An Amazon Redshift authentication profile can be used for authentication with Amazon Redshift via ``redshift_connector``. This approach allows connection properties to be stored in the server side and retrieved by ``redshift_connector``. Any connection parameter which appears in both the authentication profile and is directly provided to ``redshift_connector.connect(...)`` will be overriden by the value provided in the authentication profile.\n",
    "\n",
    "Please see the Amazon Redshift documentation to learn how to create and delete authentication profiles.\n",
    "\n",
    "In the following example we will be creating, using, and deleting an authentication profile. For this use case we would like to connect to an Amazon Redshift cluster, but store no credential or cluster information within the Python script. This will improve the portability of our code as well as its security.\n",
    "\n",
    "Firstly, we will create the Amazon Redshift authentication profile by using ``boto3``."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import boto3\n",
    "from botocore.exceptions import ClientError\n",
    "import json\n",
    "\n",
    "\n",
    "authentication_profile_contents = {\n",
    "    'host': 'examplecluster.abc123xyz789.us-west-1.redshift.amazonaws.com',\n",
    "    'region': 'us-west-1',\n",
    "    'cluster_identifier': 'examplecluster',\n",
    "    'db_name': 'dev'\n",
    "}\n",
    "\n",
    "try:\n",
    "    client = boto3.client(\"redshift\", \"us-east-2\")\n",
    "    client.create_authentication_profile(\n",
    "        AuthenticationProfileName=\"QAProfile\",\n",
    "        AuthenticationProfileContent=json.dumps(authentication_profile_contents)\n",
    "    )\n",
    "except ClientError:\n",
    "    raise"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The Redshift authentication profile, named ``QAProfile`` has been created. This profile is intended for use by a QA team who would like to avoid hard-coded references to a specific cluster in their projects. Its contents are in JSON format and contain fields such as ``host`` and ``cluster_identifier``.\n",
    "\n",
    "Next we will establish a connection to this cluster by using this authentication profile."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import redshift_connector\n",
    "import os\n",
    "\n",
    "with redshift_connector.connect(\n",
    "    iam=True,\n",
    "    region='us-west-2',\n",
    "    access_key_id=os.environ[\"AWS_ACCESS_KEY_ID\"],\n",
    "    secret_access_key=os.environ[\"AWS_SECRET_ACCESS_KEY\"],\n",
    "    session_token=os.environ[\"AWS_SESSION_TOKEN\"],\n",
    "    auth_profile=\"QAProfile\",\n",
    "    db_user=\"bobby_tables\"\n",
    ") as conn:\n",
    "    pass"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Noting the ``region`` parameter above, we can see that while the Amazon Redshift authentication profile lives in ``us-west-2``, ``examplecluster`` lives in ``us-west-1``. When retrieving temporary IAM credentials to connect to this cluster, provide the ``region`` where the Redshift authentication profile lives and not the region of the cluster.  ``region`` provided above is the region where the authentication profile is created as shown in `` client = boto3.client(\"redshift\", \"us-east-2\")``.\n",
    "\n",
    "Please see the ``redshift_connector.RedshiftProperty`` class for guidance on how to define the key and value contents of the JSON authentication profile contents.\n",
    "\n",
    "Finally, we will delete this authentication profile for demonstration purposes.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    client = boto3.client(\"redshift\")\n",
    "    client.delete_authentication_profile(\n",
    "        AuthenticationProfileName=\"QAProfile\",\n",
    "    )\n",
    "except ClientError:\n",
    "    raise"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Connecting using Identity Provider (IdP) Plugins\n",
    "Please refer to the following [Amazon Redshift documentation](https://docs.aws.amazon.com/redshift/latest/mgmt/options-for-providing-iam-credentials.html) for instructions on configuring the desired IdP.\n",
    "Check out our blog post on [AWS Big Data Blog](https://aws.amazon.com/blogs/big-data/federated-api-access-to-amazon-redshift-using-an-amazon-redshift-connector-for-python/) to learn more about integration with Okta IdP."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Authenticating using Active Directory Federation Service (ADFS) identity provider plugin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import redshift_connector\n",
    "\n",
    "conn: redshift_connector.Connection = redshift_connector.connect(\n",
    "    iam=True,\n",
    "    database='dev',\n",
    "    cluster_identifier='my-testing-cluster',\n",
    "    credentials_provider='AdfsCredentialsProvider',\n",
    "    user='brooke@myadfshostname.com',\n",
    "    password='Hunter2',\n",
    "    idp_host='myadfshostname.com'\n",
    ")\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Authenticating using Azure identity provider plugin\n",
    "Values for `client_id`, `client_secret` can be created and found  within the Enterprise Application created with Azure."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import redshift_connector\n",
    "\n",
    "conn: redshift_connector.Connection = redshift_connector.connect(\n",
    "    iam=True,\n",
    "    database='dev',\n",
    "    region='us-east-1',\n",
    "    cluster_identifier='my-testing-cluster',\n",
    "    credentials_provider='AzureCredentialsProvider',\n",
    "    user='brooke@myazure.org',\n",
    "    password='Hunter2',\n",
    "    idp_tenant='my_idp_tenant',\n",
    "    client_id='my_client_id',\n",
    "    client_secret='my_client_secret',\n",
    "    preferred_role='arn:aws:iam:123:role/MyFirstDinnerRoll'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Authenticating using Azure Browser identity provider plugin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import redshift_connector\n",
    "\n",
    "conn: redshift_connector.Connection = redshift_connector.connect(\n",
    "    iam=True,\n",
    "    database='dev',\n",
    "    cluster_identifier='my-testing-cluster',\n",
    "    credentials_provider='BrowserAzureCredentialsProvider',\n",
    "    user='brooke@myazure.org',\n",
    "    password='',\n",
    "    idp_tenant='my_idp_tenant',\n",
    "    client_id='my_client_id',\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Authenticating using Okta identity provider plugin\n",
    "Values for `idp_host`, `app_id`, and `app_name` can be located within the Okta application created."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import redshift_connector\n",
    "\n",
    "conn: redshift_connector.Connection = redshift_connector.connect(\n",
    "    iam=True,\n",
    "    database='dev',\n",
    "    region='us-east-1',\n",
    "    cluster_identifier='my-testing-cluster',\n",
    "    credentials_provider='OktaCredentialsProvider',\n",
    "    user='brooke@myazure.org',\n",
    "    password='hunter2',\n",
    "    idp_host='my_idp_host',\n",
    "    app_id='my_first_appetizer',\n",
    "    app_name='dinner_party'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Authenticating using JumpCloud via generic Saml Browser identity provider plugin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import redshift_connector\n",
    "\n",
    "conn: redshift_connector.Connection = redshift_connector.connect(\n",
    "    iam=True,\n",
    "    database='dev',\n",
    "    cluster_identifier='my-testing-cluster',\n",
    "    credentials_provider='BrowserSamlCredentialsProvider',\n",
    "    user='brooke@myjumpcloud.org',\n",
    "    password='',\n",
    "    login_url='https://sso.jumpcloud.com/saml2/plustwo_melody'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Connecting to a Redshift Serverless Endpoint\n",
    "\n",
    "Authentication methods discussed below are supported for Redshift serverless endpoints. If connecting using a network load balancer (NLB) or Redshift-managed VPC endpoint please set ``is_serverless=True`` and specify workgroup and serverless account id information using ``serverless_acct_id`` and ``serverless_work_group``, respectively.\n",
    "\n",
    "### Using Database credentials (Native authentication)\n",
    "\n",
    "Provide the Redshift Serverless Endpoint to the `host` parameter of `redshift_connector.connect()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import redshift_connector\n",
    "with redshift_connector.connect(\n",
    "    host='mytest_workgroup.123456789012.us-west-1.redshift-serverless.amazonaws.com',\n",
    "    database='dev',\n",
    "    user='awsuser',\n",
    "    password='my_password'\n",
    "    # port value of 5439 is specified by default\n",
    ") as conn:\n",
    "    pass"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using IAM or Identity Provider (IdP) Plugins\n",
    "\n",
    "Provide the Redshift Serverless Endpoint to the `host` parameter of `redshift_connector.connect()` along with the necessary connection parameter."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import redshift_connector\n",
    "\n",
    "with redshift_connector.connect(\n",
    "        iam=True,\n",
    "        host='mytest_workgroup.123456789012.us-west-1.redshift-serverless.amazonaws.com',\n",
    "        database='dev',\n",
    "        access_key_id='my_aws_access_key_id',\n",
    "        secret_access_key='my_aws_secret_access_key',\n",
    "        session_token='my_aws_session_token',\n",
    "        region='us-east-2'\n",
    "    ) as conn:\n",
    "        pass\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Connecting using Azure Native Identity Provider Plugin (IdP)\n",
    "\n",
    "To establish a connection to Redshift using Native Azure AD, the following connection parameters may be used."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import redshift_connector\n",
    "\n",
    "with redshift_connector.connect(\n",
    "    iam=True,\n",
    "    credentials_provider='BrowserAzureOAuth2CredentialsProvider',\n",
    "    host='test-cluster.abcdefghijkl.us-east-1.redshift.amazonaws.com',\n",
    "    scope='api://123456-7891-j3jk3-9dd2-c423434f0037/jdbc_login',\n",
    "    idp_tenant='e42343b2-1234-5678-8768-5db01232131d72',\n",
    "    cluster_identifier='test-cluster',\n",
    "    region='us-east-1',\n",
    "    listen_port=7890,\n",
    "    idp_response_timeout=50,\n",
    "    client_id='343432-492d-j3jk3-9dd2-c423434f0037',\n",
    "    database='dev',\n",
    "    ssl=True,\n",
    "    sslmode='verify-ca',\n",
    "    #ssl_insecure=False\n",
    "    # port value of 5439 is specified by default\n",
    ") as conn:\n",
    "    pass"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "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.9.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}