{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Sagemaker/Jupyter to work with Redshift\n", "\n", "## Pre-requisites\n", "\n", "You will need:\n", "\n", "* A redshift instance in a VPC. \n", "* A sagemaker notebook instance running this jupyter notebook in the same VPC as redshift.\n", "* A security group on the redshift instance that will allow access from the sagemaker notebook instance" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Install libraries" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Requirement already satisfied: psycopg2-binary in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (2.8.5)\n", "\u001b[33mYou are using pip version 10.0.1, however version 20.1 is available.\n", "You should consider upgrading via the 'pip install --upgrade pip' command.\u001b[0m\n", "Requirement already satisfied: numpy in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (1.14.3)\n", "\u001b[33mYou are using pip version 10.0.1, however version 20.1 is available.\n", "You should consider upgrading via the 'pip install --upgrade pip' command.\u001b[0m\n", "Requirement already satisfied: matplotlib in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (3.0.3)\n", "Requirement already satisfied: python-dateutil>=2.1 in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (from matplotlib) (2.7.3)\n", "Requirement already satisfied: kiwisolver>=1.0.1 in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (from matplotlib) (1.0.1)\n", "Requirement already satisfied: pyparsing!=2.0.4,!=2.1.2,!=2.1.6,>=2.0.1 in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (from matplotlib) (2.2.0)\n", "Requirement already satisfied: cycler>=0.10 in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (from matplotlib) (0.10.0)\n", "Requirement already satisfied: numpy>=1.10.0 in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (from matplotlib) (1.14.3)\n", "Requirement already satisfied: six>=1.5 in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (from python-dateutil>=2.1->matplotlib) (1.11.0)\n", "Requirement already satisfied: setuptools in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (from kiwisolver>=1.0.1->matplotlib) (39.1.0)\n", "\u001b[33mYou are using pip version 10.0.1, however version 20.1 is available.\n", "You should consider upgrading via the 'pip install --upgrade pip' command.\u001b[0m\n", "Requirement already satisfied: boto3 in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (1.12.39)\n", "Requirement already satisfied: jmespath<1.0.0,>=0.7.1 in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (from boto3) (0.9.4)\n", "Requirement already satisfied: s3transfer<0.4.0,>=0.3.0 in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (from boto3) (0.3.3)\n", "Requirement already satisfied: botocore<1.16.0,>=1.15.39 in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (from boto3) (1.15.39)\n", "Requirement already satisfied: python-dateutil<3.0.0,>=2.1 in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (from botocore<1.16.0,>=1.15.39->boto3) (2.7.3)\n", "Requirement already satisfied: urllib3<1.26,>=1.20; python_version != \"3.4\" in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (from botocore<1.16.0,>=1.15.39->boto3) (1.23)\n", "Requirement already satisfied: docutils<0.16,>=0.10 in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (from botocore<1.16.0,>=1.15.39->boto3) (0.14)\n", "Requirement already satisfied: six>=1.5 in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (from python-dateutil<3.0.0,>=2.1->botocore<1.16.0,>=1.15.39->boto3) (1.11.0)\n", "\u001b[33mYou are using pip version 10.0.1, however version 20.1 is available.\n", "You should consider upgrading via the 'pip install --upgrade pip' command.\u001b[0m\n" ] } ], "source": [ "!pip install psycopg2-binary\n", "!pip install numpy\n", "!pip install matplotlib\n", "!pip install boto3" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Configure redshift connection\n", "\n", "### The secure way\n", "\n", "If you are planning on sharing this notebook or storing this notebook in version control you should never add any explicit credentials. In order to achieve this we will be using [AWS Secrets Manager](https://console.aws.amazon.com/secretsmanager/) to store our redshift credentials. \n", "\n", "#### Create new secret\n", "\n", "Once on the [AWS Secrets Manager](https://console.aws.amazon.com/secretsmanager/) console:\n", "\n", "* select: Store a new secret\n", "* select: Credentials for Redshift cluster\n", "* Enter username and password\n", "* Select a Redshift cluster\n", "* Click next\n", "\n", "On the next page:\n", "\n", "* Enter a human friendly name and description\n", "* Click next\n", "\n", "On the next page:\n", "\n", "* Click next\n", "\n", "On the next page:\n", "\n", "* Review your selections\n", "* Store secret\n", "\n", "#### Get machine readable name of secret\n", "\n", "\n", "#### Granting your sagemaker instance permission to use the secret\n", "\n", "Obviously we wouldn't want anyone to be able to read the secret. Nor would we want your sagemaker notebook instance to be able to read all the secrets. To achieve both objectives, let's grant our notebook instance IAM permissions to access the secret:\n", "\n", "On the [AWS Secrets Manager](https://console.aws.amazon.com/secretsmanager/) console click on your secret and find the Secret ARN. Replace the ARN information in the JSON below:\n", "\n", "```\n", "{\n", " \"Version\": \"2012-10-17\",\n", " \"Statement\": [\n", " {\n", " \"Effect\": \"Allow\",\n", " \"Action\": [\n", " \"secretsmanager:GetResourcePolicy\",\n", " \"secretsmanager:GetSecretValue\",\n", " \"secretsmanager:DescribeSecret\",\n", " \"secretsmanager:ListSecretVersionIds\"\n", " ],\n", " \"Resource\": [\n", " \"arn:aws:secretsmanager:us-west-2:111122223333:secret:aes128-1a2b3c\"\n", " ]\n", " }\n", " ]\n", "}\n", "```\n", "\n", "Now attach this policy as an inline policy to the execution role for your Sagemaker notebook instance:\n", "\n", "* Navigate to the [Amazon Sagemaker](https://us-west-2.console.aws.amazon.com/sagemaker/) console\n", "* Select Notebook Instances\n", "* Click on your notebook instance (the one running this notebook, most likely)\n", "* Under \"Permissions and Encryption\" click on the IAM role link\n", "* You should now be on an IAM console that allows you to \"Add inline policy\". Click on the link\n", "* On the \"Create Policy\" page that opens, click JSON, and replace the JSON lines that show up with the block above. \n", "* Click review policy\n", "* On the next page select a human friendly name for the policy and click \"Create policy\"\n", "\n", "Finally paste the ARN for you secret in the code block below" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "# Put the ARN of your AWS Secrets Manager secret for your redshift cluster here:\n", "secret_name=\"arn:aws:secretsmanager:us-west-2:111122223333:secret:aes128-1a2b3c\"\n", "\n", "# This will get the secret from AWS Secrets Manager. \n", "import boto3\n", "import json\n", "\n", "session = boto3.session.Session()\n", "client = session.client(\n", " service_name='secretsmanager'\n", ")\n", "\n", "get_secret_value_response = client.get_secret_value(\n", " SecretId=secret_name\n", ")\n", "\n", "if 'SecretString' in get_secret_value_response:\n", " connection_info = json.loads(get_secret_value_response['SecretString'])\n", "else:\n", " print(\"ERROR: no secret data found\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### The insecure way\n", "\n", "Don't do this unless you are absolutely sure that nobody will ever see this notebook!\n", "\n", "On the [redshift console page](https://console.aws.amazon.com/redshiftv2/home) select clusters, then your cluster, then properties and look a connection string from which to extract the information required below. Presumably you know your database credentials. " ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [], "source": [ "# Please do not use this approach as it can expose credentials\n", "\n", "# connection_info = {\n", "# \"database\" : '', \n", "# \"host\" : '', \n", "# \"port\" : '5439', \n", "# \"user\" : '', \n", "# \"password\" : '' \n", "# }" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Establish the connection" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "# Sanity check for credentials\n", "expected_keys = set(['user', 'password', 'host', 'database', 'port'])\n", "if not expected_keys.issubset(connection_info.keys()):\n", " print(\"Expected values for \",expected_keys)\n", " print(\"Received values for \",set(connection_info.keys()))\n", " print(\"Please adjust query or assignment as required!\")" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": [ "# jdbc:redshift://HOST:PORT/DBNAME\n", "import time\n", "import psycopg2\n", "\n", "con=psycopg2.connect(\n", " dbname = connection_info[\"database\"], \n", " host = connection_info[\"host\"], \n", " port = connection_info[\"port\"], \n", " user = connection_info[\"user\"], \n", " password = connection_info[\"password\"]\n", ")\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Run basic queries against the database\n", "\n", "These queries make use of the [cursor class](https://www.psycopg.org/docs/cursor.html?highlight=arraysize). Also note that all of these queries do not persist to redshift unless you also call COMMIT.\n", "\n", "### Creating tables etc." ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [], "source": [ "# Set this to true to persist major changes to redshift. \n", "run_commits = False\n", "\n", "# Set this to false if set run_commits to true and want to have the created schema/table persist\n", "run_cleanup = True" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "This will query current timestamp from redshift. \n", "The beauty of this query is that it doesn't require any tables to be set up. " ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "[(datetime.datetime(2020, 5, 4, 22, 52, 10, 286561),)]\n" ] } ], "source": [ "cur = con.cursor()\n", "cur.execute(\"SELECT sysdate\")\n", "res = cur.fetchall()\n", "print(res)\n", "cur.close()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Create a schema. " ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [], "source": [ "cur = con.cursor()\n", "cur.execute(\"\"\"\n", " CREATE SCHEMA IF NOT EXISTS pytest2\n", " \"\"\")\n", "cur.close()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Check if we successfully created the schema" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Schema pytest2 successfully created? True\n", "All schemas:\n", "('aws_oracle_ext', 117417, 'rudpot')\n", "('dummy', 125265, 'rudpot')\n", "('information_schema', 17131, 'rdsdb')\n", "('octank', 117022, 'rudpot')\n", "('pg_catalog', 11, 'rdsdb')\n", "('pg_internal', 100, 'rdsdb')\n", "('pg_temp_1', 16924, 'rdsdb')\n", "('pg_toast', 99, 'rdsdb')\n", "('public', 2200, 'rdsdb')\n", "('pytest1', 125273, 'rudpot')\n", "('pytest2', 125356, 'rudpot')\n", "('random', 158043, 'rudpot')\n", "('wh', 158072, 'rudpot')\n" ] } ], "source": [ "cur = con.cursor()\n", "cur.execute(\"\"\"\n", " select s.nspname as table_schema,\n", " s.oid as schema_id, \n", " u.usename as owner\n", " from pg_catalog.pg_namespace s\n", " join pg_catalog.pg_user u on u.usesysid = s.nspowner\n", " order by table_schema;\"\"\")\n", "res = cur.fetchall()\n", "print(\"Schema pytest2 successfully created? \",'pytest2' in [x[0] for x in res])\n", "print(\"All schemas:\")\n", "for ii in res:\n", " print(ii)\n", "cur.close()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Create a table in new schema" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [], "source": [ "cur = con.cursor()\n", "cur.execute(\"\"\"\n", " CREATE TABLE IF NOT EXISTS pytest2.pytest2 ( \n", " id INTEGER primary key, \n", " name CHARACTER VARYING, \n", " created_at TIMESTAMP without time zone DEFAULT (getdate() at time zone 'utc')\n", " )\n", " \"\"\")\n", "cur.close()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Insert some data - note that the timestamp is autogenerated based on default" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [], "source": [ "cur = con.cursor()\n", "cur.execute(\"\"\"\n", " INSERT INTO pytest2.pytest2 VALUES ( \n", " 1, 'User1'\n", " )\n", " \"\"\")\n", "time.sleep(5)\n", "cur.execute(\"\"\"\n", " INSERT INTO pytest2.pytest2 VALUES ( \n", " 2, 'User2'\n", " )\n", " \"\"\")\n", "cur.close()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Check the results - note the difference of 5s in the final output value" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Data retrieved:\n", "(1, 'User1', datetime.datetime(2020, 4, 2, 2, 6, 20))\n", "(2, 'User2', datetime.datetime(2020, 4, 2, 2, 6, 25))\n" ] } ], "source": [ "cur = con.cursor()\n", "cur.execute(\"SELECT * FROM pytest2.pytest2 LIMIT 2\")\n", "res = cur.fetchall()\n", "print(\"Data retrieved:\")\n", "for ii in res:\n", " print(ii)\n", "cur.close()\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Do some basic plotting\n", "\n", "To see how to get data from the cursor. There's likely way better python libraries for this.\n", "\n", "Purely for demonstration purposes we will insert a timestamp to use as x value and a number to use as y value" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [], "source": [ "cur = con.cursor()\n", "cur.execute(\"\"\"\n", " CREATE TABLE IF NOT EXISTS pytest2.pytest3 ( \n", " data_time TIMESTAMP without time zone DEFAULT (getdate() at time zone 'utc'),\n", " data_value DOUBLE PRECISION\n", " )\n", " \"\"\")\n", "cur.execute(\"INSERT INTO pytest2.pytest3 VALUES ( 'Jan 1, 2018 10:00:00', 1 )\")\n", "cur.execute(\"INSERT INTO pytest2.pytest3 VALUES ( 'Jan 1, 2018 10:15:00', 4 )\")\n", "cur.execute(\"INSERT INTO pytest2.pytest3 VALUES ( 'Jan 1, 2018 10:30:00', 9 )\")\n", "cur.execute(\"INSERT INTO pytest2.pytest3 VALUES ( 'Jan 1, 2018 10:45:00', 16 )\")\n", "cur.execute(\"INSERT INTO pytest2.pytest3 VALUES ( 'Jan 1, 2018 11:00:00', 25 )\")\n", "cur.close()\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The we read it back and use numpy and pyplot to help us with some plotting" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "data": { "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXcAAAD8CAYAAACMwORRAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4zLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvnQurowAAIABJREFUeJzt3Xl8VOXd/vHPFwiEfU1IWELY9z1gpO4oglqpWhWwFDfQH2LV1lZr+zz62PpUW61W0VZARCuLu+ISkIKKqKxhSYCw7xCSsCZA1rl/f2Tah1KWkMzkzEyu9+uVVybnzJlz3VkuDmfO3GPOOUREJLJU8zqAiIgEnspdRCQCqdxFRCKQyl1EJAKp3EVEIpDKXUQkAqncRUQikMpdRCQCqdxFRCJQjcrcWbNmzVxiYmJl7lJEJOytWLEixzkXcz7bVGq5JyYmsnz58srcpYhI2DOzHee7jU7LiIhEIJW7iEgEUrmLiEQglbuISARSuYuIRKBzlruZtTazL81snZmtNbMH/MufMLM9ZrbK/3FN8OOKiEhZlOVSyGLgF865VDOrD6wws3n+dc87554NXjwRESmPcx65O+f2OedS/bdzgfVAy2AHExGJBMcLi3li9lqOnCiq1P2e1zl3M0sE+gJL/IsmmNkaM5tqZo3PsM04M1tuZsuzs7MrFFZEJJzkFRRz+9RlvPn9dlbsOFip+y5zuZtZPeB94EHn3FHgr0B7oA+wD3judNs55yY555Kcc0kxMef16lkRkbB15EQRo19bwoqdh3hxZF+u6NK8UvdfpukHzCyK0mKf7pz7AMA5t/+k9ZOBT4OSUEQkzBw+XshPpy5l/b6jvDyqH0N7xFV6hrJcLWPAa8B659yfT1oef9LdbgDSAx9PRCS8HDxWyKjJS8jYl8vfftLfk2KHsh25/wAYDaSZ2Sr/sseAkWbWB3DAduCeoCQUEQkT2bkF3DZlMTsOHGfKmCQu6eTdqehzlrtzbhFgp1n1eeDjiIiEp/1H8xk1eTF7D+fz+u0DGNShmad5KnXKXxGRSLT38AlGTV5Mdm4Bb9w5kIFtm3gdSeUuIlIRuw4eZ+TkxRw5XsSbd11A/zanvSq80qncRUTKaXvOMUZNXsyxwhKmj72AXq0aeR3pX1TuIiLlsDkrj9umLKaoxDFj7AV0b9HQ60j/RuUuInKeNmTmctuUJYBj5thkOsfV9zrSf1C5i4ich3V7j/KT15ZQo5oxY+yFdIit53Wk09J87iIiZZS2+wgjJy+mVo1qvH1P6BY76MhdRKRMUnceYszUpTSsHcXMscm0blLH60hnpSN3EZFzWLb9IKOnLKFJ3Zq8fc+FIV/soCN3EZGz+m5LDndNW058o2hm3J1MXMNoryOViY7cRUTOYOHGbO54fRmtGtdm1rjwKXbQkbuIyGktyNjPvX9PpX1sPd66ayBN69XyOtJ5UbmLiJxi7tpMJsxIpUtcA/5+10Aa1anpdaTzpnIXETnJZ2v28cCslfRs1ZBpdwykYe0oryOVi865i4j4fbRyD/fPTKVvQiPevDN8ix105C4iAsA7y3fxyPtrSG7blCljkqhbK7zrMbzTi4gEwIwlO3nswzQu7tiMSaOTqF2zuteRKkzlLiJV2hvfbefx2Wu5okssr9zWj+io8C92ULmLSBU2eeFWnvp8PUO6NWfiqH7UrBE5T0Oq3EWkSnr5y838ae4Gru0Zzwsj+hBVPXKKHVTuIlLFOOd44R+b+Mv8TfyoTwuevbk3NSKs2EHlLiJViHOOP83dwCtfbeHH/VvxzE29qF7NvI4VFCp3EakSnHM89dl6pizaxqgLEvj98B5Ui9BiB5W7iFQBPp/jfz5Zyxvf7+D2QYk8/sNumEVusYPKXUQinM/n+M1HacxcuouxF7flsWu6Rnyxg8pdRCJYic/xyPtreG/Fbu67vD0PD+lcJYodVO4iEqGKS3z84t3VfLxqLw9d2YmfDe5QZYodVO4iEoGKSnw8OGsVn6Xt41dDOzP+sg5eR6p0KncRiSgFxSVMmLGSeev289tru3L3xe28juQJlbuIRIz8ohL+31sr+HJDNv9zfXfGDEr0OpJnVO4iEhFOFJYw7u/LWbQ5h/+9oSejLkjwOpKnVO4iEvaOFRRz1xvLWLLtIH+8qRc3J7X2OpLnzjmhgpm1NrMvzWydma01swf8y5uY2Twz2+T/3Dj4cUVE/l1ufhFjpi5l2fZDvHBrHxW7X1lmyykGfuGc6wYkA/eZWTfgUWC+c64jMN//tYhIpTlyoojRry1l1a7DvDiiL8P7tPQ6Usg4Z7k75/Y551L9t3OB9UBLYDjwhv9ubwA/ClZIEZFTHTpWyG1TFrN27xFeua0f1/aK9zpSSDmveS7NLBHoCywBmjvn9vlXZQLNz7DNODNbbmbLs7OzKxBVRKTUgbwCRk5ezMb9eUwancSQ7nFeRwo5ZS53M6sHvA886Jw7evI655wD3Om2c85Ncs4lOeeSYmJiKhRWRCQrN58Rkxaz/cAxXhuTxOVdYr2OFJLKVO5mFkVpsU93zn3gX7zfzOL96+OBrOBEFBEplXkknxGvLmbP4RO8fvtALu6oA8YzKcvVMga8Bqx3zv35pFWzgTH+22OAjwMfT0Sk1J7DJ7h10vdk5Rbw5p0DubB9U68jhbSyXOf+A2A0kGZmq/zLHgOeBt4xs7uAHcAtwYkoIlXdzgPHGTl5MUfzi/j7XQPpm6Arr8/lnOXunFsEnGkqtcGBjSMi8u+25Rxj1OTFnCgqYcbdyfRs1dDrSGFBr1AVkZC1OSuXUZOXUOxzzLg7mW4tGngdKWyo3EUkJG3IzOW2KYsBY9a4ZDo1r+91pLByXte5i4hUhvQ9Rxgx6XuqVzPevkfFXh4qdxEJKat3HWbU5MXUjqrO2+MupH1MPa8jhSWdlhGRkLFixyFun7qURnWjmHF3Mq2b1PE6UthSuYtISFiy9QB3TltGbINopt99AS0a1fY6UlhTuYuI577dnMPdbyynRaNoZo5NJrZBtNeRwp7OuYuIp77emM2d05aR0KQOs8ZdqGIPEB25i4hn/rFuP+Onp9Ihth5v3X0BTerW9DpSxNCRu4h4Yk76Pu59awVd4uszY6yKPdB05C4ile6T1Xt58O1V9G7VkGl3DqRBdJTXkSKOyl1EKtUHqbt5+N3VJLVpwtQ7BlCvlmooGPRdFZFK886yXTzywRoubNeUKWOSqFNTFRQs+s6KSKX4++Id/NdH6VzSKYZJo/sTHVXd60gRTeUuIkE3ddE2nvx0HYO7xPLybf1U7JVA5S4iQfXq11v4Q0oGQ7vH8eLIvtSsoYv0KoPKXUSC5qX5m3hu3kau6xXP87f2Iaq6ir2yqNxFJOCcczw/byMvLtjMjX1b8scf96KGir1SqdxFJKCcczwzZwN/+3oLtyS14g839qJ6tTO9U6cEi8pdRALGOcfvPl3P1G+38ZPkBJ68vgfVVOyeULmLSED4fI7HZ6/l74t3cMcPEvnv67phpmL3ispdRCrM53M89mEas5bt4p5L2vHosC4qdo+p3EWkQkp8jl++t5oPUvdw/xUd+PlVnVTsIUDlLiLlVlzi46F3VvPJ6r38/KpO/GxwR68jiZ/KXUTKpbDYxwOzVpKSnsmjw7pw76XtvY4kJ1G5i8h5Kygu4b7pqfxjfRb/dV037rqordeR5BQqdxE5LycKS7j3rRV8vTGb3w3vzugLE72OJKehcheRMtuclcd901PZmJXL0zf2ZMTABK8jyRmo3EWkTD5etYdff5BGdFR1pt0xkEs7xXgdSc5C5S4iZ5VfVMLvPl3H9CU7SWrTmJdG9SW+YW2vY8k5qNxF5Ix2HDjG+OmprN17lHsubcfDQzprZscwoXIXkdOak76PX767BjOY/NMkrurW3OtIch7O+U+wmU01sywzSz9p2RNmtsfMVvk/rgluTBGpLIXFPp78ZB33vpVKu5i6fPazi1XsYagsR+7TgInAm6csf94592zAE4mIZ/YcPsF901NZteswtw9K5NfXdKFWDb0lXjg6Z7k75xaaWWLwo4iIlxZk7Ofn76ymuMTx8qh+XNsr3utIUgEVeWZkgpmt8Z+2aRywRCJSqYpLfDwzJ4M7py0nvmFtPrn/IhV7BChvuf8VaA/0AfYBz53pjmY2zsyWm9ny7Ozscu5ORIJh/9F8Rk1Zwl+/2sLIgQl8OH4QbZvV9TqWBEC5rpZxzu3/520zmwx8epb7TgImASQlJbny7E9EAm/RphwemLWS44UlPH9rb27o28rrSBJA5Sp3M4t3zu3zf3kDkH62+4tI6CjxOV5asIm/zN9Eh5h6zBrXj47N63sdSwLsnOVuZjOBy4BmZrYbeBy4zMz6AA7YDtwTxIwiEiA5eQU8OGsVizbncGPflvz+hh7UqamXu0SislwtM/I0i18LQhYRCaIlWw9w/8yVHDlRxNM39uTWAa31jkkRTP9ki0Q4n8/x6sKtPPvFBhKa1GHaHQPp1qKB17EkyFTuIhHs0LFCfvHuahZkZHFtz3ievqkn9aOjvI4llUDlLhKhVu48xIQZK8nKzefJ4d0ZndxGp2GqEJW7SIRxzvH6t9v5Q8p6mjeI5r17B9G7dSOvY0klU7mLRJCj+UX86t01zFmbyZVdY3nu5j40rKPTMFWRyl0kQqTvOcL46ansOXyC31zTlbsvbqvTMFWYyl0kzDnnmL5kJ09+uo4mdWry9rhkkhKbeB1LPKZyFwljxwqKeezDND5etZdLOsXw/C29aVqvltexJASo3EXC1IbMXMZPX8G2nGM8PKQT4y/rQLVqOg0jpVTuImHovRW7+e1HadSrFcVbd1/AoPbNvI4kIUblLhJGThSW8PjsdN5Zvpvkdk14cWRfYutHex1LQpDKXSRMbMnO477pqWRk5nL/FR14YHBHalSvyPvtSCRTuYuEgdmr9/Lr99dQs0Y1pt0xgMs6x3odSUKcyl0khOUXlfD7z9bx1uKd9G/TmJdG9qVFo9pex5IwoHIXCVE7Dxxn/IwVpO85yrhL2vHLqzsTpdMwUkYqd5EQNHdtJg+/uxoDJo3uz5DucV5HkjCjchcJIUUlPp5JyWDKom30atWQl0f1o3WTOl7HkjCkchcJEXsOn2DCjFRW7jzMmAvb8Ni1XalVo7rXsSRMqdxFQsCXGVk89M4qikscE0f15bpeLbyOJGFO5S7ioeISH3+et5FXvtpCl7j6vHJbP9rF1PM6lkQAlbuIR/Yfzef+mStZuu0gIwa05onruxMdpdMwEhgqdxEPfLs5hwdmreRYQQnP3dybm/q38jqSRBiVu0glKvE5Ji7YzAvzN9I+ph4zxvajU/P6XseSCKRyF6kkOXkFPPT2Kr7ZlMOP+rTgqRt6UreW/gQlOPSbJVIJlm47yP0zUzl0vIg/3NiTEQNa6y3wJKhU7iJB5PM5Jn2zlT/N3UDrxrWZOn4A3Vs09DqWVAEqd5EgOXy8kF+8s5r5GVlc0zOOp2/qRYPoKK9jSRWhchcJglW7DnPf9FSycvN54ofdGDMoUadhpFKp3EUCyDnHtO+287+frye2fjTv3juIPq0beR1LqiCVu0iAHM0v4pH31pCSnsngLrE8d0tvGtWp6XUsqaJU7iIBkL7nCPfNSGX3oRP8elgXxl7cjmrVdBpGvKNyF6kA5xwzl+7iiU/W0rhOFLPGJTMgsYnXsUTOXe5mNhW4DshyzvXwL2sCvA0kAtuBW5xzh4IXUyT0HCso5jcfpvHRqr1c3LEZL9zah6b1ankdSwSAsrxn1zRg6CnLHgXmO+c6AvP9X4tUGRv353L9xEXMXr2Xn1/ViWl3DFSxS0g555G7c26hmSWesng4cJn/9hvAV8AjAcwlErLeX7Gb33yURr1aUbx11wUM6tDM60gi/6G859ybO+f2+W9nAs0DlEckZOUXlfD4x2t5e/kuLmjbhJdG9iW2QbTXsUROq8JPqDrnnJm5M603s3HAOICEhISK7k7EE1uz8xg/PZWMzFzuu7w9D13ZiRrVy3JWU8Qb5S33/WYW75zbZ2bxQNaZ7uicmwRMAkhKSjrjPwIioeqT1Xt59P01RNWoxut3DODyzrFeRxI5p/KW+2xgDPC0//PHAUskEiIKikt46rP1vPn9DvolNGLiqH60aFTb61giZVKWSyFnUvrkaTMz2w08Tmmpv2NmdwE7gFuCGVKksu06eJzx01NJ23OEsRe35VdDuxCl0zASRspytczIM6waHOAsIiHhi7WZ/OLd1QC8Oro/V3eP8ziRyPnTK1RF/IpKfDyTksGURdvo2bIhL4/qR0LTOl7HEikXlbsIsPfwCSbMSCV152FGJ7fht9d1pVaN6l7HEik3lbtUaSU+x4cr9/DUZ+soLPbx4si+XN+7hdexRCpM5S5VknOOrzZk88ycDDIyc+ndqiF/vrUP7WPqeR1NJCBU7lLlrNp1mKdT1rN460HaNK3DxFF9ubZnvN4pSSKKyl2qjG05x3h27gY+S9tH07o1eXJ4d0YMSKBmDV3iKJFH5S4RLzu3gBfnb2Lm0p3UrFGNBwZ3ZOwl7ahXS7/+Ern02y0RK6+gmMkLtzL5m60UFvsYOTCB+wd3ILa+JvuSyKdyl4hTVOJj5tKdvDh/Ezl5hVzbM56Hr+5M22Z1vY4mUmlU7hIxnHN8lraPZ+duYPuB41zQtglTxnSlT+tGXkcTqXQqd4kI323J4ZmUDFbvPkLn5vV5/fYBXNY5RlfASJWlcpewtn7fUZ6Zk8FXG7Jp0TCaZ2/uzQ19W1K9mkpdqjaVu4SlPYdP8NwXG/hw5R4aREfx2DVd+OmFiURHacoAEVC5S5g5fLyQl7/czBvf7wBg3CXtGH9pBxrWifI4mUhoUblLWMgvKuH1b7fzylebySso5sf9WvHQVZ305hkiZ6Byl5BW4nO8v2I3f563kcyj+QzuEsuvhnahc1x9r6OJhDSVu4Qk5xzz12fxx7kZbNyfR5/WjXhhRB+S2zX1OppIWFC5S8hJ3XmIpz/PYOn2g7RtVpe/3taPoT3idFmjyHlQuUvI2JKdx5/mbGDO2kya1avF73/Ug1sHtNZ7l4qUg8pdPJd1NJ8X5m/i7WW7iK5RjZ9f1Ym7LmpLXU3sJVJu+usRz+TmFzFp4VamfLONohIfo5PbMOGKDjSrV8vraCJhT+Uula6w2Mf0JTt4acFmDh4r5Lpe8fzy6s60aaqJvUQCReUulcbnc3zqn9hr58HjDGrflEeHdaFXK03sJRJoKnepFN9uzuHplAzS9hyha3wD3rhzIJd0bKYrYESCROUuQbV27xGeTsngm005tGxUm+dv7c3w3i2ppom9RIJK5S5BsevgcZ77YgMfrdpLozpR/PbarvwkuY0m9hKpJCp3CaiDxwqZuGAzby3egRn8v8vac++l7WlYWxN7iVQmlbsExInCEqZ+u42/fbWFY4XF3Ny/NQ9d1Ym4hnq/UhEvqNylQopLfLy3YjfP/2Mj+48WcGXX5jwytDMdm2tiLxEvqdylXJxzzFu3nz/O3cDmrDz6JTRi4qh+DEhs4nU0EUHlLuWwfPtBnk7JYPmOQ7SLqcuro/szpFtzXdYoEkJU7lJmm7NyeWbOBuat209s/Vr87w09uSWpFTU0sZdIyKlQuZvZdiAXKAGKnXNJgQgloWX/0Xxe+MdG3l62izo1a/DwkE7ceVFb6tTUsYFIqArEX+flzrmcADyOhJij+UW8+vUWXlu0jRKfY8ygRO6/oiNN6tb0OpqInIMOveQ/FBSX8NbinUxcsIlDx4sY3qcFDw/pTOsmdbyOJiJlVNFyd8AXZuaAV51zkwKQSTzi8zlmr97Ls19sYPehE1zcsRmPDO1Cj5YNvY4mIuepouV+kXNuj5nFAvPMLMM5t/DkO5jZOGAcQEJCQgV3J8GycGM2T6dksG7fUbq3aMAfbuzJxR1jvI4lIuVUoXJ3zu3xf84ysw+BgcDCU+4zCZgEkJSU5CqyPwm8tN1HeGZOBos259CqcW3+MqIPP+zVQhN7iYS5cpe7mdUFqjnncv23hwBPBiyZBNXOA8f50xcb+GT1XhrXieK/r+vGbckJ1Kqhib1EIkFFjtybAx/6X7hSA5jhnJsTkFQSNAfyCnhpwWamL9lB9WrGhMs7MO7SdjSI1sReIpGk3OXunNsK9A5gFgmi44XFTPlmG5MWbuVEUQm3JLXmwSs70ryBJvYSiUS6FDLCFZX4eHvZLv4yfxPZuQUM6dacXw3tQofYel5HE5EgUrlHKOccc9Iz+dPcDWzNOUZSm8b87Sf96N9GE3uJVAUq9whzrKCYBRlZTP12Gyt3HqZDbD0m/zSJK7vGamIvkSpE5R4BjpwoYv76/XyelsnCTdkUFvuIbxjNMzf15KZ+mthLpCpSuYepA3kFzFu3n5T0TL7bkkNRiSOuQTSjBiYwrEccSYlNqK5r1UWqLJV7GNl/NJ+5azNJSctkybYD+By0blKbO37QlqE94ujTqpFefCQigMo95O0+dJw56ZnMSc9kxc5DOAftY+oy/rIODO0RR/cWDXQuXUT+g8o9BG3LOUZK+j7mpGeyZvcRALrE1efBwZ24pmec3p9URM5J5R4CnHNsysrj87TSQs/IzAWgd6uGPDK0C8N6xJHYrK7HKUUknKjcPeKcY+3eo6Sk7yMlPZOt2ccwg6Q2jfmv67oxtEccLRvV9jqmiIQplXsl8vkcq3YfJiVtH3PWZrLr4AmqGSS3a8odgxK5unscsZoOQEQCQOUeZCU+x7LtB//1pGjm0Xyiqhs/6NCMCZd34KpucXrbOhEJOJV7EBSV+Ph+ywFS0jOZty6TnLxCatWoxiWdYvhVj84M7tqchrU1C6OIBI/KPUDyi0pYtCmHlPRM/rF+P0dOFFGnZnUu7xLLsB5xXN45lrq19O0WkcqhtqmA44XFfL0hm5T0TBZkZJFXUEz96Bpc2bU5w3rEcUmnGKKj9OYXIlL5VO7nKTe/iAUZWaSkZfLVxizyi3w0rhPFtT3jGdozjh+0b0bNGprLRUS8pXIvg8PHC5m3bj9z0jP5ZlMOhSU+YurX4ub+rRnWI46BbZtoci4RCSkq9zPIzi3gi3WlV7h8v+UAxT5Hy0a1+UlyG4b1jKN/QmPN4yIiIUvlfpJ9R04wJz2TlPRMlm8/iM9BYtM63H1xO4b1iKNXq4aax0VEwkKVL/ddB4//61WiK3ceBqBjbD0mXN6BYT3j6RJXX4UuImGnSpb75qw85vgLfe3eowB0b9GAh4d0YmiPeL2/qIiEvSpR7s45MjJzSUnPJCVtH5uy8gDom9CIx67pwtDu8SQ0reNxShGRwInYcnfOsWb3EVLSM5mTvo/tB45jBgMSm/D4D0sn5opvqIm5RCQyRVS5+3yO1J2H+Dwtk7lrM9lz+ATVqxmD2jdl7CXtGNItjpj6tbyOKSISdGFf7sUlPpZuO0hKemmhZ+UWULN6NS7q2IwHr+zIVd2a06iOJuYSkaolLMu9sNjHt1tymJOWybz1+zl4rJDoqGpc1imWYT3juKJLLPWjNTGXiFRdYVPu+UUlLNyY/a+JuXLzi6lXqwZX+CfmurRzDHVqhs1wRESCKiza8MX5m/jb11s4XlhCw9pRDOkWx7AecVzUsZkm5hIROY2wKPe4htEM79OSYT3iuLB9U6I0j4uIyFmFRbnfktSaW5Jaex1DRCRs6BBYRCQCqdxFRCJQhcrdzIaa2QYz22xmjwYqlIiIVEy5y93MqgMvA8OAbsBIM+sWqGAiIlJ+FTlyHwhsds5tdc4VArOA4YGJJSIiFVGRcm8J7Drp693+ZSIi4rGgP6FqZuPMbLmZLc/Ozg727kREhIqV+x7g5IvPW/mX/Rvn3CTnXJJzLikmJqYCuxMRkbIy51z5NjSrAWwEBlNa6suAUc65tWfZJhvYUa4dQjMgp5zbhhqNJfREyjhAYwlVFRlLG+fceR0dl/sVqs65YjObAMwFqgNTz1bs/m3KfehuZsudc0nl3T6UaCyhJ1LGARpLqKrssVRo+gHn3OfA5wHKIiIiAaJXqIqIRKBwKvdJXgcIII0l9ETKOEBjCVWVOpZyP6EqIiKhK5yO3EVEpIyCUu5nmlDMzCb4lzkza3aGbZua2ZdmlmdmE09Z19/M0vyP8aKZ2Wm2N/+6zWa2xsz6nbRujJlt8n+MCYOxdDGz782swMweLkuuEB3Hbf6fRZqZfWdmvcs7jhAYy3D/WFb5X5x30Unrwur366T7DjCzYjP7cbiOxcwuM7Mj/p/LKjP773Pl8mAcT5nZLjPLO8f+f+3fzwYzu7q848A5F9APSi+L3AK0A2oCq4Fu/nV9gURgO9DsDNvXBS4C7gUmnrJuKZAMGJACDDvN9tf415n/vkv8y5sAW/2fG/tvNw7xscQCA4CngIfLkitExzHon99rSieaW1KecYTIWOrxf6czewEZ4fr7dVKGBZRe9fbjcB0LcBnw6fnk8mAcyUA8kHeW/Xfz77MW0Nafpfr5jsM5F5Qj9zNOKOacW+mc2362jZ1zx5xzi4D8k5ebWTzQwDm32JV+F94EfnSahxgOvOlKLQYa+be9GpjnnDvonDsEzAOGhvJYnHNZzrllQFFZc4XoOL7zf88BFlP6aubyjCMUxpLnXw+lf8j/vB12v19+9wPvA1knLQvXsZxXrsoch3/dYufcvnPkHQ7Mcs4VOOe2AZv9mc77byUY5R6sCcVa+h/rPx7XzO41s3vPsf/y5PJ6LIHKFUrjuIvSo6/y5vJ8LGZ2g5llAJ8Bd1Ygl6djMbOWwA3AXwOQy/OfC3Chma02sxQz617OXJU+IaKZXW9mT55j/+edKyzeQ/VcnHN/8zpDoETKWE43DjO7nNJyv+g/twhdp47FOfch8KGZXQL8DrjSk2DlcMpYXgAecc75znJKPmSdMpZUSl+in2dm1wAfAR29SXZ+nHOzgdmBftxgHLmXaUKxcj5uq5O+PtPjnmn/5cnl9VgClcvzcZhZL2AKMNw5d6ACuTwfyz855xYC7fxProXjWJKAWWa2Hfgx8IqZ/aicuTwdi3PuqHMuz3/7cyCqnD+XYI2jrALWX8Eo92VARzNra2Y1gREE4F8l/7mqo2aW7H/YEE+5AAABXElEQVS2/KfAx6e562zgp1YqGTji33YuMMTMGptZY2CIf1kojyVQuTwdh5klAB8Ao51zGyuYy+uxdPjn1RpWeiVWLeAAYfj75Zxr65xLdM4lAu8B451zH4XjWMws7qSfy0BKu+1AOXIFZRznYTYwwsxqmVlbSv/3sbRcuc72bGt5Pyi9YmUjpc/u/uak5T+j9FxRMbAXmHKG7bcDB4E8//3/+Wx1EpDuf9yJ/N9VC/cC9/pvG6Vv/7cFSAOSTnrcOyl9gmIzcEcYjCXOv81R4LD/doOz5QrRcUwBDgGr/B/Lz5UrhMfyCLDWP47vgYvC9ffrlMeZhv9qmXAcCzDB/3NZTemT9oPK+zsWxHH80f+1z//5Cf/y64EnT9r+N/59b+CkK4POdxx6haqISATSK1RFRCKQyl1EJAKp3EVEIpDKXUQkAqncRUQikMpdRCQCqdxFRCKQyl1EJAL9f+X+xiat0ZJZAAAAAElFTkSuQmCC\n", "text/plain": [ "
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "import numpy as np\n", "import matplotlib.pyplot as plt\n", "%matplotlib inline\n", "\n", "cur = con.cursor()\n", "cur.execute(\"SELECT * FROM pytest2.pytest3\")\n", "res = cur.fetchall()\n", "cur.close()\n", "data=np.array(res)\n", "\n", "plt.plot(data[:,0],data[:,1])\n", "plt.show()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Persist or delete\n", "\n", "Persist changes to redshift if run_commits was set to true. If commit is called then any other tool querying your redshift database will now see your changes. Otherwise these changes are stuck in an open transaction.\n", "\n", "Warning: if you keep the kernel running then any other tool trying to make changes to this schema will be blocking until the transaction is explicitly committed or until it is aborted when the connections is closed. This can become confusing if you keep re-creating connections during testing because then the old connections will not get closed/cleaned up until you restart the kernel." ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [], "source": [ "if run_commits:\n", " cur = con.cursor()\n", " cur.execute(\"\"\"\n", " COMMIT\n", " \"\"\")\n", " cur.close()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Clean up. If you didn't select `run_commits` then this is essentially a no-op because all changes were temporary anyway. Note that we are shortcutting table deletion here by using the CASCADE keyword on the schema drop." ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Schema pytest2 successfully deleted? True\n", "All schemas:\n", "('aws_oracle_ext', 117417, 'rudpot')\n", "('dummy', 125265, 'rudpot')\n", "('information_schema', 17131, 'rdsdb')\n", "('octank', 117022, 'rudpot')\n", "('pg_catalog', 11, 'rdsdb')\n", "('pg_internal', 100, 'rdsdb')\n", "('pg_temp_1', 16924, 'rdsdb')\n", "('pg_toast', 99, 'rdsdb')\n", "('public', 2200, 'rdsdb')\n", "('pytest1', 125273, 'rudpot')\n", "('random', 158043, 'rudpot')\n", "('wh', 158072, 'rudpot')\n" ] } ], "source": [ "if run_cleanup:\n", " cur = con.cursor()\n", " cur.execute(\"\"\"\n", " DROP SCHEMA pytest2 CASCADE\n", " \"\"\")\n", " cur.execute(\"\"\"\n", " select s.nspname as table_schema,\n", " s.oid as schema_id, \n", " u.usename as owner\n", " from pg_catalog.pg_namespace s\n", " join pg_catalog.pg_user u on u.usesysid = s.nspowner\n", " order by table_schema;\"\"\")\n", " res = cur.fetchall()\n", " print(\"Schema pytest2 successfully deleted? \",not 'pytest2' in [x[0] for x in res])\n", " print(\"All schemas:\")\n", " for ii in res:\n", " print(ii)\n", " cur.close()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Ensure all changes remain accessible in redshift for later review by setting `run_commits` to true and `run_cleanup` to false. " ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [], "source": [ "if run_commits and run_cleanup:\n", " cur = con.cursor()\n", " cur.execute(\"\"\"\n", " COMMIT\n", " \"\"\")\n", " cur.close()\n", " " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Cleanup\n", "\n", "Close cursor to release any pending transactions " ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [], "source": [ "con.close()" ] }, { "cell_type": "code", "execution_count": null, "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.5" } }, "nbformat": 4, "nbformat_minor": 4 }