COPY-TO-REDSHIFT-FOR-ANALYSIS.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# I run this notebook on my local machine, so you need to pip install all necessary packages\n",
    "import boto3\n",
    "import pandas as pd\n",
    "import json\n",
    "import matplotlib.pyplot as plt \n",
    "from time import time\n",
    "import psycopg2\n",
    "import configparser\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\" I ll need to configparse ARN the next time after creating ARN, not the first time \\nARN                    = config.get('IAM_ROLE','ARN')\\nARN2                   = config.get('IAM_ROLE','ARN2')\""
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# \n",
    "config = configparser.ConfigParser()\n",
    "config.read_file(open('redshift-config.cfg'))\n",
    "\n",
    "KEY                    = config.get('IAM_USER', 'KEY')\n",
    "SECRET                 = config.get('IAM_USER', 'SECRET')\n",
    "\n",
    "\n",
    "IAM_ROLE_NAME          = config.get('IAM_ROLE', 'IAM_ROLE_NAME')\n",
    "\n",
    "\n",
    "CLUSTER_IDENTIFIER     = config.get('AWS', 'CLUSTER_IDENTIFIER')\n",
    "CLUSTER_TYPE           = config.get('AWS', 'CLUSTER_TYPE')\n",
    "NUM_NODES              = config.get('AWS', 'NUM_NODES')\n",
    "NODE_TYPE              = config.get('AWS', 'NODE_TYPE')\n",
    "\n",
    "\n",
    "DB_NAME                = config.get('CLUSTER', 'DB_NAME')\n",
    "DB_USER                = config.get('CLUSTER', 'DB_USER')\n",
    "DB_PASSWORD            = config.get('CLUSTER', 'DB_PASSWORD')\n",
    "DB_PORT                = config.get('CLUSTER', 'DB_PORT')\n",
    "\n",
    "\n",
    "#DWH_IAM_ROLE_NAME      = config.get('DWH', 'DWH_IAM_ROLE_NAME' )\n",
    "\n",
    "\"\"\" I ll need to configparse ARN the next time after creating ARN, not the first time \n",
    "ARN                    = config.get('IAM_ROLE','ARN')\n",
    "ARN2                   = config.get('IAM_ROLE','ARN2')\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Params</th>\n",
       "      <th>Values</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CLUSTER_IDENTIFIER</td>\n",
       "      <td>capstone-project-2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CLUSTER_TYPE</td>\n",
       "      <td>multi-node</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NUM_NODES</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NODE_TYPE</td>\n",
       "      <td>dc2.large</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DB_NAME</td>\n",
       "      <td>my_database</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>DB_USER</td>\n",
       "      <td>database_user-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>DB_PASSWORD</td>\n",
       "      <td>projectPassw0rd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>DB_PORT</td>\n",
       "      <td>5439</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>IAM_ROLE_NAME</td>\n",
       "      <td>iamRoleName</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Params                 Values\n",
       "0  CLUSTER_IDENTIFIER  capstone-project-2021\n",
       "1        CLUSTER_TYPE             multi-node\n",
       "2           NUM_NODES                      4\n",
       "3           NODE_TYPE              dc2.large\n",
       "4             DB_NAME            my_database\n",
       "5             DB_USER       database_user-12\n",
       "6         DB_PASSWORD        projectPassw0rd\n",
       "7             DB_PORT                   5439\n",
       "8       IAM_ROLE_NAME            iamRoleName"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame({\"Params\":\n",
    "              [ \"CLUSTER_IDENTIFIER\",\"CLUSTER_TYPE\", \"NUM_NODES\",\"NODE_TYPE\",\"DB_NAME\",\"DB_USER\",\\\n",
    "               \"DB_PASSWORD\",\"DB_PORT\", \"IAM_ROLE_NAME\"  ],\n",
    "             \"Values\":\n",
    "             [ CLUSTER_IDENTIFIER,CLUSTER_TYPE, NUM_NODES,NODE_TYPE,DB_NAME,DB_USER,\\\n",
    "               DB_PASSWORD,DB_PORT, IAM_ROLE_NAME]})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Client for IAM, EC2, S3, and RedShift "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "ec2 = boto3.resource('ec2', \n",
    "                    region_name=\"us-west-2\",\n",
    "                    aws_access_key_id     = KEY,\n",
    "                    aws_secret_access_key = SECRET\n",
    "                    )\n",
    "\n",
    "s3 = boto3.resource('s3',\n",
    "                   region_name='us-west-2',\n",
    "                   aws_access_key_id      = KEY,\n",
    "                   aws_secret_access_key  = SECRET\n",
    "                   )\n",
    "\n",
    "iam = boto3.client('iam', \n",
    "                region_name='us-west-2',\n",
    "                aws_access_key_id         = KEY,\n",
    "                aws_secret_access_key     = SECRET\n",
    "                )\n",
    "\n",
    "redshift = boto3.client('redshift',\n",
    "                      region_name='us-west-2',\n",
    "                      aws_access_key_id    = KEY,\n",
    "                      aws_secret_access_key= SECRET\n",
    "                      )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## STEP1 : IAM ROLE\n",
    "##### Create an IAM Role that enables RedShift to access S3 bucket ( ReadOnly) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.3 Get the IAM role ARN\n",
      "arn:aws:iam::185187464837:role/iamRoleName\n"
     ]
    }
   ],
   "source": [
    "\"\"\"from botocore.exceptions import ClientError\n",
    "\n",
    "#1.1 Create the role\n",
    "try:\n",
    "    print(\"1.1 Creating a new IAM Role called Omaar-IAM-FOR-AIRFLOW\") \n",
    "    dwhRole = iam.create_role(\n",
    "        Path='/',\n",
    "        RoleName=IAM_ROLE_NAME,\n",
    "        Description = \"Allows Redshift clusters to call AWS services on your behalf.\",\n",
    "        AssumeRolePolicyDocument=json.dumps(\n",
    "            {'Statement': [{'Action': 'sts:AssumeRole','Effect': 'Allow','Principal': {'Service': 'redshift.amazonaws.com'}}],\n",
    "             'Version': '2012-10-17'})\n",
    "    )    \n",
    "except Exception as e:\n",
    "    print(e)\n",
    "    \n",
    "\n",
    "print(\"1.2 Attaching Policy\")\n",
    "\n",
    "iam.attach_role_policy(RoleName=IAM_ROLE_NAME,PolicyArn=\"arn:aws:iam::aws:policy/AmazonS3ReadOnlyAccess\")['ResponseMetadata']['HTTPStatusCode']\n",
    "\"\"\"\n",
    "print(\"1.3 Get the IAM role ARN\")\n",
    "roleArn = iam.get_role(RoleName=IAM_ROLE_NAME)['Role']['Arn']\n",
    "\n",
    "\n",
    "print(roleArn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## STEP2: Redshift Cluster\n",
    "##### Create a RedShift Cluster"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    response= redshift.create_cluster( ClusterType   = CLUSTER_TYPE,\n",
    "                                  NodeType           = NODE_TYPE,\n",
    "                                  NumberOfNodes      = int(NUM_NODES),\n",
    "                                  \n",
    "                                  #identifiers & credentials\n",
    "                                  DBName             = DB_NAME,\n",
    "                                  ClusterIdentifier  = CLUSTER_IDENTIFIER,\n",
    "                                  MasterUsername     = DB_USER,\n",
    "                                  MasterUserPassword = DB_PASSWORD,\n",
    "                                  \n",
    "                                  #Roles ( for s3 access)\n",
    "                                  IamRoles = [roleArn] \n",
    "                                 )\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Describe the cluster to see its status "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Key</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ClusterIdentifier</td>\n",
       "      <td>capstone-project-2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NodeType</td>\n",
       "      <td>dc2.large</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ClusterStatus</td>\n",
       "      <td>creating</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MasterUsername</td>\n",
       "      <td>database_user-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DBName</td>\n",
       "      <td>my_database</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>VpcId</td>\n",
       "      <td>vpc-61eca419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>NumberOfNodes</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Key                  Value\n",
       "0  ClusterIdentifier  capstone-project-2021\n",
       "1           NodeType              dc2.large\n",
       "2      ClusterStatus               creating\n",
       "3     MasterUsername       database_user-12\n",
       "4             DBName            my_database\n",
       "5              VpcId           vpc-61eca419\n",
       "6      NumberOfNodes                      4"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def description (props):\n",
    "    pd.set_option('display.max_colwidth',None)\n",
    "    keyToShow= [\"ClusterIdentifier\", \"NodeType\", \"ClusterStatus\", \"MasterUsername\", \"DBName\", \"Endpoint\", \"NumberOfNodes\", 'VpcId']\n",
    "    x = [(k,v) for k,v in props.items() if k in keyToShow]\n",
    "    return pd.DataFrame(data=x,columns=['Key','Value'])\n",
    "myClusterProps = redshift.describe_clusters(ClusterIdentifier=CLUSTER_IDENTIFIER)['Clusters'][0]\n",
    "description( myClusterProps)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# do not run this for the first time \n",
    "#if a psycopg2 execution fails please run the this cell to close the connection and reconnect\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# this one is functional ( les parametre dans cfg sont dans l'order pour CLUSTER ) \n",
    "conn = psycopg2.connect(\"host={} dbname={} user={} password={} port={}\".format(*config['CLUSTER'].values()))\n",
    "cur=conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## STEP 3 : Import data, check it and do analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Drop tables if they exist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "date_time_drop=\"DROP TABLE IF EXISTS date_time CASCADE\"\n",
    "calendar_drop=\"DROP TABLE IF EXISTS calendar CASCADE\"\n",
    "location_drop=\"DROP TABLE IF EXISTS location CASCADE\"\n",
    "hosts_drop=\"DROP TABLE IF EXISTS hosts CASCADE\"\n",
    "listings_drop=\"DROP TABLE IF EXISTS listings CASCADE\"\n",
    "reviewers_drop=\"DROP TABLE IF EXISTS reviewers CASCADE\"\n",
    "reviews_drop=\"DROP TABLE IF EXISTS reviews CASCADE\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "drop_all_tables=[date_time_drop,calendar_drop, location_drop,hosts_drop,reviewers_drop,listings_drop ,reviews_drop]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "for query in drop_all_tables:\n",
    "    cur.execute(query)\n",
    "    conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create tables "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "date_time_create=\"\"\"CREATE TABLE IF NOT EXISTS date_time(\n",
    "date date PRIMARY KEY ,\n",
    "day_of_month integer,\n",
    "week integer,\n",
    "month integer,\n",
    "year integer,\n",
    "day varchar)\n",
    "\"\"\"\n",
    "calendar_create=\"\"\"\n",
    "CREATE TABLE IF NOT EXISTS calendar(\n",
    "calendar_id integer PRIMARY KEY,\n",
    "listing_id integer references listings (id),\n",
    "date date references date_time(date),\n",
    "available boolean,\n",
    "price float)\n",
    "\"\"\"\n",
    "location_create=\"\"\"\n",
    "CREATE TABLE IF NOT EXISTS location(\n",
    "location_id integer PRIMARY KEY, \n",
    "latitude float,\n",
    "longitude float,\n",
    "street VARCHAR ,\n",
    "zipcode VARCHAR,\n",
    "city VARCHAR,\n",
    "country VARCHAR\n",
    ")\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "hosts_create=\"\"\"CREATE TABLE IF NOT EXISTS hosts (\n",
    "host_id integer PRIMARY KEY, \n",
    "host_name varchar  , \n",
    "host_about varchar (10000), \n",
    "host_response_time varchar, \n",
    "host_response_rate float,\n",
    "host_is_superhost boolean,\n",
    "host_location varchar,\n",
    "host_identity_verified boolean\n",
    ")\n",
    "\"\"\"\n",
    "listings_create=\"\"\"\n",
    "CREATE TABLE IF NOT EXISTS listings (\n",
    "id integer PRIMARY KEY,\n",
    "host_id integer references hosts(host_id),\n",
    "location_id integer references location (location_id),\n",
    "price float,\n",
    "weekly_price float,\n",
    "monthly_price float,\n",
    "security_deposit float ,\n",
    "cleaning_fee float ,\n",
    "guests_included float,\n",
    "extra_people float,\n",
    "listing_url varchar,\n",
    "property_type varchar ,\n",
    "accommodates integer,\n",
    "room_type varchar ,\n",
    "bathrooms float,\n",
    "bedrooms float,\n",
    "beds float,\n",
    "bed_type varchar,\n",
    "amenities varchar (9000),\n",
    "review_scores_rating float,\n",
    "review_scores_accuracy float,\n",
    "review_scores_cleanliness float,\n",
    "review_scores_location float,\n",
    "review_scores_value float,\n",
    "cancellation_policy varchar,\n",
    "require_guest_profile_picture boolean,\n",
    "require_guest_phone_verification boolean)\n",
    "\"\"\"\n",
    "reviewers_create=\"\"\"CREATE TABLE IF NOT EXISTS reviewers (\n",
    "reviewer_id integer PRIMARY KEY,\n",
    "reviewer_name varchar )\"\"\"\n",
    "\n",
    "reviews_create=\"\"\"CREATE TABLE IF NOT EXISTS reviews(\n",
    "id integer PRIMARY KEY,\n",
    "listing_id integer references listings (id),\n",
    "date date references date_time(date),\n",
    "reviewer_id integer references reviewers (reviewer_id),\n",
    "comments varchar (10000)\n",
    ")\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "create_table_queries=[date_time_create, location_create,\\\n",
    "                      hosts_create, listings_create, calendar_create,reviewers_create, reviews_create]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "for query in create_table_queries:\n",
    "    cur.execute(query)\n",
    "    conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Copy from S3 into Redshift"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "date_time_copy=(\"\"\" copy date_time  from {} iam_role '{}' format as parquet ;\"\"\").\\\n",
    "format(config['S3']['date_time'],config['IAM_ROLE']['ARN'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "calendar_copy=(\"\"\" \n",
    "copy calendar from {} \n",
    "iam_role '{}'\n",
    "format as parquet ;\"\"\").format(config['S3']['calendar'],config['IAM_ROLE']['ARN'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "location_copy=(\"\"\" \n",
    "copy location from {} \n",
    "iam_role '{}'\n",
    "format as parquet  ;\"\"\").format(config['S3']['location'],config['IAM_ROLE']['ARN'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "hosts_copy=(\"\"\" \n",
    "copy hosts from {} \n",
    "iam_role '{}'\n",
    "format as parquet  ;\"\"\").format(config['S3']['hosts'],config['IAM_ROLE']['ARN'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "listings_copy=(\"\"\" \n",
    "copy listings from {} \n",
    "iam_role '{}'\n",
    "format as parquet ;\"\"\").format(config['S3']['listings'],config['IAM_ROLE']['ARN'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "reviewers_copy=(\"\"\" \n",
    "copy reviewers from {} \n",
    "iam_role '{}'\n",
    "format as parquet ;\"\"\").format(config['S3']['reviewers'],config['IAM_ROLE']['ARN'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "reviews_copy=(\"\"\" \n",
    "copy reviews from {} \n",
    "iam_role '{}'\n",
    "format as parquet  ;\"\"\").format(config['S3']['reviews'],config['IAM_ROLE']['ARN'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "copy_all_tables={'date_time':date_time_copy,'calendar':calendar_copy,\\\n",
    "                 'location':location_copy,'hosts':hosts_copy,\\\n",
    "                 'listings':listings_copy, 'reviewers':reviewers_copy, 'reviews':reviews_copy}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=== COPYING TABLE __date_time__ IS DONE IN : 14.369196891784668 sec\n",
      " \n",
      "=== COPYING TABLE __calendar__ IS DONE IN : 9.001993179321289 sec\n",
      " \n",
      "=== COPYING TABLE __location__ IS DONE IN : 3.066822052001953 sec\n",
      " \n",
      "=== COPYING TABLE __hosts__ IS DONE IN : 8.107087850570679 sec\n",
      " \n",
      "=== COPYING TABLE __listings__ IS DONE IN : 8.379455089569092 sec\n",
      " \n",
      "=== COPYING TABLE __reviewers__ IS DONE IN : 5.819473743438721 sec\n",
      " \n",
      "=== COPYING TABLE __reviews__ IS DONE IN : 5.169198036193848 sec\n",
      " \n"
     ]
    }
   ],
   "source": [
    "for query in copy_all_tables:\n",
    "    t0=time()\n",
    "    cur.execute(copy_all_tables[query])\n",
    "    conn.commit()\n",
    "    copyTime=time()-t0\n",
    "    print(\"=== COPYING TABLE __{}__ IS DONE IN : {} sec\\n \". format(query,copyTime))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Run Quality Check"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "query1=\"SELECT count(*) FROM date_time;\"\n",
    "query2=\"SELECT count(*) FROM calendar;\"\n",
    "query3=\"SELECT count(*) FROM location;\"\n",
    "query4=\"SELECT count(*) FROM hosts;\"\n",
    "query5=\"SELECT count(*) FROM listings;\"\n",
    "query6=\"SELECT count(*) FROM reviewers;\"\n",
    "query7=\"SELECT count(*) FROM reviews;\"\n",
    "count_queries={\"date_time_records_count\":query1, \"calendar_records_count\":query2, \"location_records_count\":query3,\\\n",
    "             \"hosts_records_count\":query4, \"listings_records_count\":query5,\"reviewers_records_count\":query6,\\\n",
    "             \"reviews_records_count\":query7}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### record count per table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "date_time_records_count : 3240\n",
      "calendar_records_count : 7310950\n",
      "location_records_count : 20029\n",
      "hosts_records_count : 17263\n",
      "listings_records_count : 20006\n",
      "reviewers_records_count : 411813\n",
      "reviews_records_count : 431830\n"
     ]
    }
   ],
   "source": [
    "for query in count_queries:\n",
    "    cur.execute(count_queries[query])\n",
    "    rows=cur.fetchall()\n",
    "    conn.commit()\n",
    "    print(query,\":\",rows[0][0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### min, max, and average price of all the available listings "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The minimum price of a listing is $9.0, maximum price is $8500.0 , and average price is $207.655591210853\n"
     ]
    }
   ],
   "source": [
    "query=\"\"\"SELECT min (price), max (price), avg (price) FROM calendar; \"\"\"\n",
    "cur.execute(query)\n",
    "rows=cur.fetchall()\n",
    "conn.commit()\n",
    "print(\"The minimum price of a listing is ${}, maximum price is ${} , and average price is ${}\"\\\n",
    "      .format(rows[0][0], rows[0][1], rows[0][2]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### average price per listing: top10 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "query=\"\"\"SELECT listing_id, AVG ( price) as average_price FROM calendar \\\n",
    "GROUP BY (listing_id) HAVING average_price is not null\n",
    "ORDER BY ( average_price) DESC LIMIT 10;\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The average price of the listing __12213707__ is $8500.0\n",
      "The average price of the listing __258273__ is $8000.0\n",
      "The average price of the listing __5587500__ is $4782.96703296703\n",
      "The average price of the listing __25349319__ is $3900.0\n",
      "The average price of the listing __15626614__ is $3142.0\n",
      "The average price of the listing __28651212__ is $3000.0\n",
      "The average price of the listing __11040717__ is $2500.0\n",
      "The average price of the listing __19559616__ is $2500.0\n",
      "The average price of the listing __28973126__ is $2257.50699300699\n",
      "The average price of the listing __17525867__ is $2200.0\n"
     ]
    }
   ],
   "source": [
    "cur.execute(query)\n",
    "rows=cur.fetchall()\n",
    "conn.commit()\n",
    "for row in rows:\n",
    "    print( \"The average price of the listing __{}__ is ${}\".format(row[0], row[1]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The listings '12213707' & '258273' must be luxurious "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### top recurrent prices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "query=\"\"\"SELECT average_price, count(average_price) as counts FROM (SELECT listing_id, AVG ( price) as average_price FROM calendar \\\n",
    "GROUP BY (listing_id) HAVING average_price is not null) GROUP BY average_price ORDER BY counts DESC LIMIT 10  ;\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(query)\n",
    "rows=cur.fetchall()\n",
    "conn.commit()\n",
    "data=pd.DataFrame(rows, columns=['average_price', 'counts'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>average_price</th>\n",
       "      <th>counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>150.0</td>\n",
       "      <td>255</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100.0</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>120.0</td>\n",
       "      <td>175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>200.0</td>\n",
       "      <td>145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>125.0</td>\n",
       "      <td>131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>250.0</td>\n",
       "      <td>123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>140.0</td>\n",
       "      <td>121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>175.0</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>110.0</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>180.0</td>\n",
       "      <td>91</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   average_price  counts\n",
       "0          150.0     255\n",
       "1          100.0     180\n",
       "2          120.0     175\n",
       "3          200.0     145\n",
       "4          125.0     131\n",
       "5          250.0     123\n",
       "6          140.0     121\n",
       "7          175.0      99\n",
       "8          110.0      95\n",
       "9          180.0      91"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The majority of the listings' prices are between 100 & 150 dollars"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Number of available listings per month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "query=\"\"\" SELECT MIN (date), MAX (date) FROM calendar;\"\"\"\n",
    "cur.execute(query)\n",
    "rows=cur.fetchall()\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(datetime.date(2018, 12, 6), datetime.date(2019, 12, 6))]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "query=\"\"\"SELECT dt.month, COUNT(DISTINCT ((listing_id))) \n",
    "         FROM calendar as cal\n",
    "         JOIN date_time as dt on dt.date=cal.date\n",
    "         WHERE cal.available = true\n",
    "         GROUP BY  dt.month \n",
    "         ORDER BY  dt.month\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(query)\n",
    "rows=cur.fetchall()\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 7269)\n",
      "(2, 5867)\n",
      "(3, 5557)\n",
      "(4, 4089)\n",
      "(5, 4188)\n",
      "(6, 4178)\n",
      "(7, 3282)\n",
      "(8, 3330)\n",
      "(9, 3317)\n",
      "(10, 3157)\n",
      "(11, 3156)\n",
      "(12, 8750)\n"
     ]
    }
   ],
   "source": [
    "for row in rows :\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "data=pd.DataFrame(rows, columns=['month', 'listings_per_month'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAnEAAAFNCAYAAABv3TlzAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4yLjIsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+WH4yJAAAgAElEQVR4nO3dd5xU1fnH8c8DS+9dqiCW2BuxYa/YS9RgLERNTIwxppmoKcZU0/z9YvLTxFjASoy9K4uCiR0EK5ahiAhSpCud5/fHOSvDsDM7u+zsnbv7fb9e9zUz57bn3pmdefacc881d0dERERE0qVZ0gGIiIiISO0piRMRERFJISVxIiIiIimkJE5EREQkhZTEiYiIiKSQkjgRERGRFFISJ02amY00s18ntG8zs1vMbJGZvVzN/K+a2X+zXi83s63qsJ8rzOzGzY1Xgtz3pYjltzOzSWa2zMy+U8rYRKRpURInZcXMZpjZXDNrl1X2NTMbl2BYpbI/cATQz933qmlhd2/v7tMKLWNmB5vZrJz1fuvuX9u8UJsmMxtoZm5mFZuxmR8B49y9g7tfW1+xFSsewzNm9pmZvWNmh+fM72Fmd5rZ4vgPxR0581ua2QIza29mp5vZ83Fb43KW29bMHjSz+Wa20MyeNLPtqonnKTM70sx2isssMLNNBiyNcT8WY/rYzP6W732obWKdZxvfi/tZYmY3m1mrnPnDzWyKmX1qZlPN7ICc+VeY2W/j+bonfpe5mR2cs9ylZvZmTOqnm9ml1cSyn5k9H5//yszeMLO1ZvaLnOXMzH5iZjPNbKmZjTazjptzHiRdlMRJOaoALkk6iNoys+a1XGVLYIa7f1qKeBqLOpzXcrMl8Fa+mQ1wfHcBk4BuwE+Ae8ysR9b8+4CPCXH2BP6Us/6BwGR3Xw4sBP4XuLqa/XQGHgK2A3oBLwMPZi8Q/znbExgPrAHuBs7PE/d1wDygN7AbcBDwrRqPtg7M7CjgMuAwYCCwFXBV1vwjgN8D5wIdCOck9x+qY4DH4vP/AmcRzusmuwPOAboAw4Bvm9nwAtvKEP4ReLSabZ0DnA0MBfoAbYC/FjpWaWTcXZOmspmAGYQv04VA51j2NUJNBoQvWAcqstYZB3wtPv8q8BzwP8BiwhftfrH8Q8KPwoisdUcCfwfGAMsIPy5bZs3/Qpy3EHgXOD1n3esJX7afAodXczx9CD9sCwlfxl+P5ecDK4F1wHLgqmrW/Srw36zXDmwdnx8DvB1j/gj4IdAOWAGsj9tcHvf/C+D2nPM3ApgJLAB+krWPNsAoYBEwhfDjMStr/o/j/pbF83FYnvex1Od1HPBr4Pl4nA8TkpQ7gKXAK8DArOX3i2VL4uN+Odv6FeFzswx4Cuge582M56vqfO5b9b4Qkp1FwHTg6Dzn4en4Hq+M629b3fEB28c4FhMSvhNyzsd1wONxG88BWxCSqUXAO8Duefa/LbAK6JBV9h/gm/H5kYS/ueYF/iavAb6fU/b532SB9brGc9ctq+wE4KGc5bYGvJr1pwDHZL3+I/CPapbbno3/lhbH8k7ArcB84APgp0CzPLHeCfw26/VhwMdZr58Hzi9wrF0I3y3Nc8pnAQfXcJ6uBf6aU/YqsEdO2e3AL3LK7gEuzfmcrwTaFtqnpsYzqSZOytEEwg/aD+u4/t7A64Qf9TuB0cAXCT8WZwF/M7P2WcufSfgR7w5MJiQCVbUGY+I2egJnANeZ2Y5Z634F+A3hv/PqmnPuInyR9wFOBX5rZoe5+03AN4EXPDSTXlnLY7wJ+Ia7dwB2Ap72UKN3NDA7brO9u8/Os/7+hBqTw4Cfm9n2sfxKNtREHEE4X0Do2wV8G/hi3O9RhAQgn1KeV4DhhFqIvsBg4AXgFkLyMCUeC2bWlVCLcS3hM3EN8KiZdcvZ37kxnpZs+OwdGB87x/P5Qny9NyH57A78AbjJzCw3QHc/lJA0fTuu/141x/cSIQl9Ku7/YuCOnKbI0wlJSHdCUvYC4Ye+O+GH/Jo852hHYJq7L8sqey2WA+wTj2OUmX1iZq+Y2UE52ziG6muBanIgIRH6pI7b+gsw3Mzamllfwmf7idyF3H0KG/8tdY6z/kpI5LYi1OKdQ3iPq7Mj4bxUeQ3oZWbdYk3pEKCHmWXMbFZs2m2TtfxRwFh3X1fksQGhORQ4gKyaWjPrTajJnFTMJuKU/boVsE1t4pD0UhIn5ernwMU5zT7Fmu7ut8Qv1H8B/YFfuvsqd38KWE1I6Ko86u7PuvsqQnPTvmbWHziO0Nx5i7uvdfdXgXsJyViVB939OXdf7+4rs4OI29gf+LG7r3T3ycCNhMRjc60BdjCzju6+KMZWG1e5+wp3f43wg7VrLD+dUCOxyN1nERKfKusIPxA7mFkLd5/h7lML7KMk5zXLLe4+1d2XEGqpprp7pbuvBf4N7B6XOxZ4391vi/u7i1B7dXzOtt5z9xWEJr7dChwXwAfu/s/4GRtFaPLrVcM62T4/vriv9sDV7r7a3Z8GHiEkt1Xud/eJ8VzcD6x091uzPuO75+4gak+ofcy2hJA8AvQj1MY9Q6jd+zPwoJl1B7BwIU0Ld3+3FseGmfUD/g/4fs6so9nQTFiT8YTkainhH6EJwANF7r858GXgcndf5u4zCMeW728v9zxVPe9AeF9bED6fBxDer90JSXWVYyn+uLL9gvA7fEtW2THAE+5ezI3NHwe+FvsPdiLUlAO0rUMskkJK4qQsufubhB+yy+qw+tys5yvi9nLLsmviPszab1W/nz6EPkJ7xw7fi81sMaF2aYvq1q1GH2BhTi3IB4Sao831JcKX/QdmNt7M9q3l+tl9dT5jw/now8bHlH1uMsB3CT8882In6j4F9lGq81ol9z3N9x73IZz3bLnvQ77zkc/ny7v7Z/FpTetkyz6+PsCHMaHLF1+xx5prOZDb0b0jodm4at0Z7n6Tu69x99ExtqFxfq2Tk/iP11PAdTFhrirfGVjq7jW+t2bWDHiS0F+vHaHGsQuhX1oxuhNqVLPf90J/e7nnqer5MuJ3CKHJc467LyDUfB6TFesRVFNLWIiZfZtQO3hs/EenSnZ/uJrcTKjtH0eozXsmls/Kt4I0LkripJxdCXydjb94qy4CyP5PM/vHvy76Vz2JzaxdgdmEH7Px7t45a2rv7hdmrVvov+XZQFcz65BVNoDQp2yzuPsr7n4iofntAULtUU3xFGMOoXamSv/sme5+p7vvT0jEnMI/qqU6r7U1O8abrdj3oT7jyLfd2UD/mAxUqZfPCeGHfaucz+CubGi+e53Cx1irplQz60JI4B5y999sxra6Ej4/f4s16J8QaquOybN87jEsINRWZ7/vhc7pW2yojSY+n+vun7j7IkJSlO88fZGQCM/PdzC5zOw84oUUsca7qrwFoel3TDHbiTXVV7r7QHfvF4/jI+rnsyMpoCROylas+fkX8J2ssvmEL6izzKx5/DIcvJm7OsbM9jezloQ+XC/F2oJHgG3N7GwzaxGnL2b1H6sp/g8JHaJ/Z2atzWwXwgUNdxRes7A4hMGZZtbJ3dcQmpuq+uLMBbrFppW6uBu43My6xH5I387a73ZmdmgcemEloYaiUB+gkpzXOngs7u8rZlZhZl8Gdohx1GQ+4UKRWo/PVwsvEf45+VE8FwcTmnpHb+6GYx+8ycCV8TN4MrALofkaQtNsFzMbEf+eTiX80/Rc7PO1F6GWBwjNlGbWmnAFebO4zRZxXkdC7dlz7l5dDfpGtXpxeIzWhBoz4rZaxbgXEC4YuTC+Z50JF+O8tulmgfC57xc/a8Rm5ruB35hZBzPbktC0e3ue9W8FzjezHWIi+lPCBSVVbiF07+gZ53+XDZ+fTWorzaxVPDaAlvHYLM47E/gtcIRvOmTQAcDr7r40a1st4raaARVxW83jvK5mNjieyx0INYS/zKnVlUZMSZyUu18SmlOyfR24FPiE0Gfm+c3cx52EWr+FhOEPzgSIzaBHEjrQzyY0of2e0C+sWGcQLhSYTfjBvNLdi/ovuwZnAzPMbCmhU/dZMeZ3CM0r02JTZaHmzur8klDrMB2oJHSar2rqaUUYWmIB4Vz0BK4osK1SnteixVqc44AfED4zPwKOi4lCTet+RrgA4bl4PvcpQXyrCVdtHk04t9cB58T3sj4MJ3TMX0R4/06tqjVy94Vx3z8k9AO7DDgxnpvDCBcLZPdJPJuQvF9PSDhWAP+M804m1Eqda2Fg6qppQPynYns2/lvdMq5fVSu4gnCRRZVTCENwzCdc2b0W+F6eY3w6budjM6t6Xy8mJMfTCBfH3EloftyEuz9BuEDlGUKz6wfEC2OiXxGuan6PcNHMJMLnAqpv/nw3Hk9fQmK7gg21gr8mXGDzStY5+nuBbf0zrn8GoW/pCjb07evOhqucHwdudvcbqjtGaZysuL6TItIUmdmFwHB3z71isab1RhKGJvlpTctKeTKz64A33f26etjW6YTk8fTNj6x8mFkvQk1nnyIvRKhpe28TztPbmx2cNAmqiRORz5lZbzMbambNLAxx8QNCDaI0PZOpv/d+MWHsxsamE2EMvfpI4FoCtyqBk9pQTZyIfC72HXoUGET44R1NGKZhdS23MxLVxImIlJSSOBEREZEUUnOqiIiISAopiRMRERFJoYqkAyiV7t27+8CBA5MOQ0RERKRGEydOXODutbrVZKNN4gYOHMiECROSDkNERESkRmaWe3vAGqk5VURERCSFlMSJiIiIpJCSOBEREZEUUhInIiIikkJK4kRERERSSEmciIiISAopiRMRERFJISVxIiIiIimkJE5EREQkhZTEiYiISOMxexK8/m9YuyrpSEpOSZyIiIg0Hq/eBg9fknQUDUJJnIiIiDQO7pAZA4MOhIpWSUdTckriREREpHH4ZCosnglbH5Z0JA1CSZyIiIg0DpnK8Lj14cnG0UCUxImIiEjjkKmEbltD10FJR9IglMSJiIhI+q1ZATP+22Rq4UBJnIiIiDQGHzwPa1coiRMRERFJlcxYaN4KthyadCQNRkmciIiIpF+mEgYOhZZtk46kwSiJExERkXRbPBMWvNukmlJBSZyIiIikXWZseFQSJyIiIpIimUro1B+6b5t0JA1KSZyIiIik17o1MG18uEuDWdLRNCglcSIiIpJeH74Mq5c1uaZUUBInIiIiaZaphGYV4ab3TYySOBEREUmvTCX03xtad0o6kganJE5ERETSadlc+Pj10B+uCVISJyIiIuk09enw2AT7w0GJkzgz+56ZvWVmb5rZXWbW2sy6mtkYM3s/PnbJWv5yM8uY2btmdlRW+Z5m9kacd61ZE7v8RERERDaVqYR2PaHXzklHkoiSJXFm1hf4DjDE3XcCmgPDgcuAse6+DTA2vsbMdojzdwSGAdeZWfO4ueuBC4Bt4jSsVHGLiIhICqxfB1PHhqbUZk2zYbHUR10BtDGzCqAtMBs4ERgV548CTorPTwRGu/sqd58OZIC9zKw30NHdX3B3B27NWkdERESaotmTYMWiJtuUCiVM4tz9I+BPwExgDrDE3Z8Cern7nLjMHKBnXKUv8GHWJmbFsr7xeW65iIiINFWZSsBgq0OSjiQxpWxO7UKoXRsE9AHamdlZhVappswLlFe3zwvMbIKZTZg/f35tQxYREZG0yFRC3z2gXbekI0lMKZtTDwemu/t8d18D3AfsB8yNTaTEx3lx+VlA/6z1+xGaX2fF57nlm3D3G9x9iLsP6dGjR70ejIiIiJSJzxbCRxObdFMqlDaJmwnsY2Zt49WkhwFTgIeAEXGZEcCD8flDwHAza2VmgwgXMLwcm1yXmdk+cTvnZK0jIiIiTc20Z8DXw9ZHJB1JoipKtWF3f8nM7gFeBdYCk4AbgPbA3WZ2PiHROy0u/5aZ3Q28HZe/yN3Xxc1dCIwE2gCPx0lERESaosxYaN05NKc2YRYu+Gx8hgwZ4hMmTEg6DBEREalP7vDn7WDLoXDaLUlHU2/MbKK7D6nNOk1zYBURERFJp7lvwvK5Tb4/HCiJExERkTTJVIbHJnq/1GxK4kRERCQ9MmPDbbY6bJF0JIlTEiciIiLpsGoZzHxBtXCRkjgRERFJh+nPwvq16g8XKYkTERGRdMhUQsv20H/vpCMpC0riREREpPy5hyRu0EFQ0TLpaMqCkjgREREpf59kYPFM9YfLoiROREREyp+GFtmEkjgREREpf5lK6LYNdBmYdCRlQ0mciIiIlLc1K2DGf3VVag4lcSIiIlLePngO1q5UEpdDSZyIiIiUt8xYqGgNA4cmHUlZURInIiIi5e39MbDlUGjRJulIyoqSuLqY+jQ8/ZswZo2IiIiUzqIZ8Mn7akqthpK4unjvKXj2D/DAt2DdmqSjERERabwyY8OjkrhNVCQdQCoN+x206QzjfgfL58Lpo6BVh6SjEhERaXwyY6HzAOi+TdKRlB3VxNWFGRx8GRx/LUwbByOPg+Xzko5KRESkcVm7GqaPD7VwZklHU3aUxG2OPUfAGXfBgvfgxsNhQSbpiERERBqPD1+C1cvVlJqHkrjNte1RMOKR8CG7+UiYNSHpiERERBqHTCU0q4BBByYdSVlSElcf+u0J548J/eJGHgfvPpF0RCIiIumXGQsD9lW/8zyUxNWXboNDItfzCzD6DJg4MumIRERE0mvpHJj7hm54X4CSuPrUvmdoWh18GDx8CTzzW40lJyIiUhdTnw6P6g+Xl5K4+taqfbjYYbezYPzv4aGLYd3apKMSERFJl0wltO8FvXZKOpKypXHiSqF5Czjxb9CxTxgUePlcOG0ktGyXdGQiIiLlb/26UBP3hWM1tEgBqokrFTM49Cdw3P+E/yZGHgfL5ycdlYiISPn76FVYuVj94WqgJK7UhpwHX74D5k0JQ5AsnJZ0RCIiIuUtUwnWDLY6JOlIypqSuIbwhWNgxEOwYjHceAR8NDHpiERERMpXphL67gltuyYdSVlTEtdQ+u8F5z8FLduGptX3xyQdkYiISPn5bGGo7NBVqTVSEteQum8D51dCt63hzi/DpNuTjkhERKS8TH0acCVxRVAS19A69IJzHwu3EHnwIhj/B40lJyIiUiUzFtp0gT67Jx1J2VMSl4RWHeArd8Muw+GZ38Aj39NYciIiIuvXh/5wgw+FZs2TjqbsaZy4pFS0hJP/HsaS++81YSy5L90U+syJiIg0RXPfhE/nqSm1SKqJS5IZHH4lHPMnePdxuPUE+PSTpKMSERFJRqYyPA4+NNk4UkJJXDnY6+vw5dtgzuthLLlFM5KOSEREpOFlKmGLnaHDFklHkgpK4srF9sfDOQ/CpwvCWHKzJycdkYiISMNZuQQ+fElNqbWgJK6cbLlvGEuuohWMPDZcoSMiItIUTH8W1q+FrY9IOpLUUBJXbnpsB+ePgS4D4c7TYfJdSUckIiJSeplKaNkhDI4vRVESV4469g5jyW25HzzwTfjPNRpLTkREGi/30Pq01UHQvEXS0aSGkrhy1boTnHkv7HwajL0KHrsU1q9LOioREZH6t+A9WPKh+sPVksaJK2cVLeHkG6BDb3j+Wlg2B750I7Rok3RkIiIi9adqaJGtD0s2jpRRTVy5a9YMjvwVDLsa3nkUbj0p3BxYRESkschUQvftoPOApCNJFSVxabHPhXDaLTD7Vbj5KFg8M+mIRERENt/qz2DGc2pKrQMlcWmy48lw9v3hFl03HgEfv5F0RCIiIpvng+dg3So1pdaBkri0Gbg/nPdkuDHwzUfDtHFJRyQiIlJ3mUqoaANbDk06ktRREpdGPbcPY8l17g+3nwqv/zvpiEREROomUxkqKFq0TjqS1FESl1ad+sK5j8OAfeC+r8Fz12osORERSZeF0+GTjPrD1VFJkzgz62xm95jZO2Y2xcz2NbOuZjbGzN6Pj12ylr/czDJm9q6ZHZVVvqeZvRHnXWtmVsq4U6NNZzjr3tBXbszP4InLYf36pKMSEREpztR4e0klcXVS6pq4vwBPuPsXgF2BKcBlwFh33wYYG19jZjsAw4EdgWHAdWbWPG7neuACYJs4DStx3OlR0Qq+dDPscxG8dD3ccy6sWZl0VCIiIjXLjIXOW0K3wUlHkkolS+LMrCNwIHATgLuvdvfFwInAqLjYKOCk+PxEYLS7r3L36UAG2MvMegMd3f0Fd3fg1qx1BMJYcsN+C0f+Bt5+AG4/BVYsSjoqERGR/NauhmnjQy2cGtjqpJQ1cVsB84FbzGySmd1oZu2AXu4+ByA+9ozL9wU+zFp/VizrG5/nlkuu/b4NX7oJZr0SrlxdMqvmdURERJLw4Yuw5lM1pW6GUiZxFcAewPXuvjvwKbHpNI/q0nAvUL7pBswuMLMJZjZh/vz5tY23cdj51NBPbulHYSy5uW8lHZGIiMimMpXQrAUMOiDpSFKrVkmcmTWLzaTFmAXMcveX4ut7CEnd3NhESnycl7V8/6z1+wGzY3m/aso34e43uPsQdx/So0ePIsNshAYdGK5cxUON3PT/JB2RiIjIxjJjwwgLrTokHUlq1ZjEmdmdZtYxNoW+DbxrZpfWtJ67fwx8aGbbxaLD4voPASNi2Qjgwfj8IWC4mbUys0GECxhejk2uy8xsn3hV6jlZ60g+W+wUxpLr2Dv0kXvzvqQjEhERCZbOgblvqil1M1UUscwO7r7UzM4EHgN+DEwE/ljEuhcDd5hZS2AacC4hcbzbzM4HZgKnAbj7W2Z2NyHRWwtc5O7r4nYuBEYCbYDH4yQ16dw/1MiNPjNctfqfP0Pv3aD3rtBnN+i1E7Rsm3SUIiLS1GhokXpRTBLXwsxaEK4I/Zu7rzGzokaVdffJwJBqZlV7gzR3/w3wm2rKJwA7FbNPydG2a7jf6vN/DZ1I33sCJt8e5lkz6L5dSOqqErstdlbVtoiIlNb7Y6BDb+i1Y9KRpFoxSdw/gBnAa8CzZrYlsLSUQUk9a9EaDoot4O6wbA7MngxzXoM5k2H6eHh9dFzYoNvWOYndLmFgYRERkc21bi1Mewa2P15Di2ymGpM4d78WuDar6AMzO6R0IUlJmUHHPmH6wjEbypfNjUldTOw+fAnevGfD/C6DNk7seu8WavlERERq46OJsHKJmlLrQY1JnJl9v5riJWY2MTaXSmPQoRd0OBK2PXJD2acLNk7s5kwOgwlX6TQAeu8SEro+sa9d+56bbltERKRKpjJ059nq4KQjSb1imlOHxOnh+PpY4BXgm2b2b3f/Q6mCk4S16w5bHxamKisWwZzXsxK71+CdRzbM79Anp8Zu19DvQVXmIiICIYnr90Vo06XmZaWgYpK4bsAe7r4cwMyuJIz5diDhKlUlcU1Jmy6w1UFhqrJyKXz8xoakbvbkcAFF1ZjM7XpunNT13hU69VdiJyLS1Hy6AGZPgkOuSDqSRqGYJG4AsDrr9RpgS3dfYWarShOWpErrjjBwaJiqrP4UPn5z48Ru6tNQNWpMm66bJnZdBimxExFpzKY+A/jGLTxSZ8UkcXcCL5pZ1QC7xwN3ZQ3+K7Kplu1gwN5hqrJmBcx9G+ZM2pDYPf83WL8mzG/VKfSxq7pwYuABoa+eiIg0DplKaNsNeu+edCSNQjFXp/7KzB4HhhLuY/rNOG4bwJmlDE4amRZtoN+eYaqydhXMe3vDBRSzJ8NLN8C6VdC6c7gPbL/qhhoUEZFUWb8+DPI7+FBoVspbtzcdxdTEAUwi3K+0AsDMBrj7zJJFJU1HRSvos3uYqqxbEy6euPd8GHUCDL8DBmtUGxGRVPv4dfh0voYWqUfF3Dv1YmAuMAZ4BHg0PoqURvMWobbuvCegy0C483SY8nCNq4mISBnLVIbHwYcmG0cjUkx95iXAdu6+o7vv4u47u/supQ5MhA5bwFcfCRc93H0OTLoj6YhERKSuMmM1nmg9KyaJ+xBYUupARKrVtiuc8yAMOgge/Ba8cF3SEYmISG2tXBLuBKSm1HpVTJ+4acA4M3sU+HxIEXe/pmRRiWRr2Q6+8i+492vw5OVhwOFDrtBwJCIiaTFtfBhiSklcvSomiZsZp5ZxEml4Fa3g1FvgkUvg2T+E/+qGXa0rnERE0iBTCa06hjs1SL0pZoiRqxoiEJEaNa+AE/4Whh554W+wcjGc+H/hQggRESlP7qE/3FYH6fu6nuVN4szsf939u2b2MJ/fP2kDdz+hpJGJVMcMjvw1tOkMT/8aVi0LNXQtWicdmYiIVGf+u7B0Fhx0adKRNDqFauJui49/aohARIpmBgdeGmrkHvsh3HEqnHEXtOqQdGQiIpLr86FFdKut+pa3Q5G7T4xPd3P38dkTsFvDhCdSwF5fh1P+CR88D6OOh08/SToiERHJlamEHl+Azv2TjqTRKaZX+Ihqyr5az3GI1M0up8PwO2HeFLjlaFg6O+mIRESkyupP4YPndFVqieRN4szsjNgfbpCZPZQ1jQNU5SHlY7th4R6rS2fDTUfBJ1OTjkhERABmPAfrViuJK5FCfeKeB+YA3YE/Z5UvA14vZVAitTZwf/jqw3DbKXDzMDj7Pthi56SjEhFp2jJjoEVbGLBv0pE0SoX6xH3g7uOAw4H/xL5wc4B+gEZZlfLTZ/dwv9XmLWDksTDzpaQjEhFp2jKVMPAAjSBQIsX0iXsWaG1mfYGxwLnAyFIGJVJnPbYLiVzbbnDbSRuuihIRkYb1yVRYOE1NqSVUTBJn7v4ZcArwV3c/GdihtGGJbIbOA+C8J6HrYLhzOLz1QNIRiYg0PVOfDo9ba2iRUikqiTOzfYEzgUdjWTG36xJJTvue8NVHoO+ecM+58OqtSUckItK0ZCqhyyDoNjjpSBqtYpK47wKXA/e7+1tmthXwTGnDEqkHbTrD2ffD4EPhoYvhuWuTjkhEpGlYuwqmP6um1BIr5t6p44HxWa+nAd8pZVAi9aZlWxh+F9x/AYz5Wbjf6qE/C3d9EBGR0pj5Aqz5TElcieneqdL4VbSEL90ErTrCf/4MKxbDMX+CZsVURIuISK1lKqF5yzD8k5SM7p0qTUOz5nD8X0IT63N/gVVL4aTrw3AkIiJSvzJjw9hwrdonHUmjljeJq7p3amxOFUk/Mzjil9C6M4y9ClYuhdNHQYs2SUcmItJ4LPkI5r0NR7iUIUEAABrcSURBVPwq6UgavULNqW9QTTNqFXffpSQRiZTaAd8PNXKPfB9u/xKccRe07pR0VCIijcPUseFR/eFKrlBz6nENFoVIQxtyXugjd/83YNTxcNZ90K570lGJiKRfphI69IGe2ycdSaNXqDn1g4YMRKTB7XxqSOTuPjvcb/WcB6BTv6SjEhFJr3VrYeo42OEEjQLQAHR5njRt2x4ZxpJbPhduOgoWZJKOSEQkvT6aAKuWqCm1gSiJE9lyv3B3h7Ur4eajYM5rSUckIpJOmUqw5rDVwUlH0iTkTeLMbGx8/H3DhSOSkN67wnlPQEVrGHkcfPBC0hGJiKRPphL6fTFcPCYlV6gmrreZHQScYGa7m9ke2VNDBSjSYLpvExK59j3htpPh/TFJRyQikh7L58PsSWpKbUCFrk79OXAZ0A+4JmeeA4eWKiiRxHTuD+c+AbefAncNh1NugJ2+lHRUIiLlb1q8rfrWhyUbRxNS6OrUe4B7zOxn7q4R+6TpaN8j9JG7czjcc34YFHjIuUlHJSJS3jKV0LY79N4t6UiajEI1cQC4+6/M7ATgwFg0zt0fKW1YIglr3QnOuhf+PQIe+S6sXAz7fy/pqEREytP69eFWW1sfpvtSN6Aaz7SZ/Q64BHg7TpfEMpHGrWVbGH4n7HQqVP4CxvwcPO9NTEREmq6PX4PPFqg/XAOrsSYOOBbYzd3XA5jZKGAScHkpAxMpC81bhH5xrTvCc3+BlUvg2GugWfOkIxMRKR+ZSsBgsLrLN6RikjiAzsDC+Fw3mZSmpVnzkLi17gz/vSYkciffABUtk45MRKQ8vF8JfXbT7QsbWDFJ3O+ASWb2DGCEvnGqhZOmxQwOvzKMfTTm57BqGZx+W2hyFRFpylYsglkvwwE/SDqSJqeYCxvuMrNxwBcJSdyP3f3jUgcmUpaGXhJq5B6+JIwl95V/aVBLEWnapo0HX6/+cAkoqjnV3ecAD5U4FpF02HNE6CN379dh1HFw1n1hgOBys3Z1aPr9fFocp6yytatDLaMZWLMwUfU8X5nllFW3bCm22QxatoP+e+vqN5FykqmEVp2g75CkI2lyiu0TJyLZdjwZWnWA0WfBzcPgnAeg84D63ce6NTkJ2JL804pq5q9dUXj7zSrCbcbcw3/RxEdfv3FZuemzOxz1O9hy36QjERH3MLTI4IOhuVKKhqYzLlJXWx8ekrc7Tg+J3NkPQI9tN8xftxZWLa0+Aasu6cqd1nxaeP/WPDTltu60YerYe+PXrTvHqdOmU4s2obarJlUJnWclefkSvoLL5pZ5NeuvL7Csw/x34Jnfwi3DQiJ9+FXQZcu6voMisrnmTYFls9WUmpCCSZyZNQNed/ed6roDM2sOTAA+cvfjzKwr8C9gIDADON3dF8VlLwfOB9YB33H3J2P5nsBIoA3wGHCJuwbskjIwYB8491G47RS46XDo2HdDErZ6eeF1rdmmCVf3bXISsGqSr9adQvLWom1xSdjmMgsJYzno/0XY6RR47tow5Ms7j8G+F8EB3w81oyLSsDKV4XGwbrWVhIJJnLuvN7PXzGyAu8+s4z4uAaYAHePry4Cx7n61mV0WX//YzHYAhgM7An2ASjPb1t3XAdcDFwAvEpK4YcDjdYxHpH5tsTOc90QYENjXb5x85daUZU8t2zdMEtbYtGwHh1wOe5wDY68Kw75Muh0O+xnsdqbG8BNpSJlK6LkDdOqbdCRNUjHNqb2Bt8zsZeDz9h13P6GmFc2sH2Gw4N8A34/FJwIHx+ejgHHAj2P5aHdfBUw3swywl5nNADq6+wtxm7cCJ6EkTspJt8Hw5duSjqJp6dQ3DMS81zfgicvgoYvh5Rtg2NUwcP+koxNp/FYth5kvwN7fSDqSJquYJO6qzdj+/wI/ArLbOXrFq11x9zlmVnVZX19CTVuVWbFsTXyeWy4iAv32hPOfgjfvDbWhI4+F7Y+HI34JXbdKOjqRxmvGf2HdavWHS1CN1+m7+3hC37UW8fkrwKs1rWdmxwHz3H1ikbFU167kBcqr2+cFZjbBzCbMnz+/yN2KSOqZwc6nwrdfgUN/Cpmn4f/2hqd+Fvonikj9y1SGvrkDdKV4UmpM4szs68A9wD9iUV/ggSK2PRQ4ITaHjgYONbPbgblm1jtuuzcwLy4/C+iftX4/YHYs71dN+Sbc/QZ3H+LuQ3r06FFEiCLSqLRoAwdeChdPhJ1Pg+evhWv3gAk3w/p1SUcn0rhkKmHQgVDRKulImqxiRsy8iJCQLQVw9/eBGkc2dffL3b2fuw8kXLDwtLufRRg0eERcbATwYHz+EDDczFqZ2SBgG+Dl2PS6zMz2MTMDzslaR0RkUx17w0nXwQXjoPu28Mj34O8HwLRxCQcm0kh8MhUWTVdTasKKSeJWufvqqhdmVsHmjQB6NXCEmb0PHBFf4+5vAXcDbwNPABfFK1MBLgRuBDLAVHRRg4gUo8/ucO5jcNooWL0Mbj0R7joj/ACJSN1lxobHrTW0SJKspuHWzOwPwGJCDdjFwLeAt939J6UPr+6GDBniEyZMSDoMESkXa1bCS9fDs3+GtSthrwvgoEuhTZekIxNJnztOh0/eh+9MSjqSRsPMJrp7re5dVkxN3GXAfOAN4BuEcdp+WvvwREQS1KI17P+90F9utzPgxetCf7mX/xnuriEixVmzEmb8R02pZaCYq1PXE8Zz+xVhuJFRuluCiKRWh15wwl/hG89Crx3hsR/C34duGHleRAqb+QKs+Qy2PiLpSJq8Yq5OPZbQD+1a4G9AxsyOLnVgIiIl1XsXGPEwfPkOWLsKbv8S3HEazH8v6chEylumEpq3goFDk46kySumOfXPwCHufrC7HwQcAvxPacMSEWkAZrD9cXDRS3Dkr2Hmi3DdPvDYj+CzhUlHJ1KeMmNhy/3CLfAkUcUkcfPcPZP1ehobxnYTEUm/ilaw38Whk/aeX4VX/gnX7g4v/h3WrUk6OpHysWQWzJ+i/nBlIm8SZ2anmNkphPumPmZmXzWzEcDDhLs2iIg0Lu26w3HXwDefgz67wRM/huv2hfeeBHUFFskaWkRJXDkoVBN3fJxaA3OBgwg3rp8P6Jp8EWm8eu0AZz8AZ/wLcLjzdLj9FJg3JenIRJKVGQMd+0GP7ZKORICKfDPc/dyGDEREpKyYwXbDYPChMOEmGPc7uH4/2PNcOOSKUGsn0pSsWwPTxsOOJ4e/D0lc3iSuSrwF1sXAwOzl3f2E0oUlIlImKlrCPhfCLl+GcVfDKzfCG/fAQT8KAwZXtEw6QpGGMesVWLVUTallpMYkjnCz+5sIfeHWlzYcEZEy1bYrHPMH+OL58ORP4KmfhBq6I38N2x2jmglp/DKVYM1hq4OSjkSiYpK4le5+bckjERFJgx7bwVn3wPtjQjI3+isw6EA46newxU5JRydSOplK6L83tO6UdCQSFTPEyF/M7Eoz29fM9qiaSh6ZiEg52+YIuPA5OOZP8PEb8I8D4KHvwHKNwCSN0PJ5MOc13fC+zBRTE7czcDZwKBuaUz2+FhFpupq3gL2+DjufCuP/CC//A968Dw78YehHV9Eq6QgD99Apfe3KcHeKqsd1q6opWw3WrJrJ4mPzPPPjMs1qmF9wG7bhed7tqNk6EVOfDo/qD1dWikniTga2cvfVpQ5GRCSV2nSBYb+FIefBUz+Fyith4i1wxC9h+xNg/bqcZCk7icpNpFZvvMzalSGxyk22Nlq2hsRs7cqkz1D9+jyhi4le607QYQvo0Bs69g6PVa+rHtt0hWbFND5JtTKV0K4HbLFL0pFIlmKSuNeAzuguDSIihXXfGr4yGqY+A09eAXefE5IMr4drwipah5q9qsfmrTYua90pPm+VZ9lqynPLmrcMEx5i/nyKr9evyynPme81zS/FNtbBikWw7GNY8iHMehk++2TT89esRUzottg0wct+bN1ZtX251q8Lg/xuc6QS4TJTTBLXC3jHzF4BVlUVaogREZE8Bh8C3/gPvHE3LJxWfdL1eRKVm0hVV9ZCiUVtrF0Fy+eGxG7ZnE0fF7wP05+FlUs2XbeideEkr+qxVYeGP66kzJkMKxaqKbUMFZPEXVnyKEREGpvmFbDbV5KOommqaAWdB4SpkNWfwfKP8yR7H4cLVt57CtZ8uum6LdvnJHd5Er4WbUpzjA0pMxaw8M+JlJUakzh3H98QgYiIiDSolm2h61ZhKmTVsvy1ess+DoPgLvu4+r6HrTttmty17R6a2auTt8a1QE1sbdepba3um/dCn911l5IyVMwdG5YRrkYFaAm0AD51946lDExERKQstOoQpu7b5F/GHVYuLpzszfhveL5+bcPFXl8O/0XSEUg1iqmJ26jh38xOAvYqWUQiIiJpYxauUm7TBXpun3+59eth9bKQ9G2iurKqWQXm1Xr5PPPyrWMGbbvVbv/SIIrpE7cRd3/AzC4rRTAiIiKNWrNmuuOB1JtimlNPyXrZDBhCwX8XRERERKTUiqmJOz7r+VpgBnBiSaIRERERkaIU0yfu3IYIRERERESKlzeJM7OfF1jP3f1XJYhHRERERIpQqCaumtENaQecD3QDlMSJiIiIJCRvEufuf656bmYdgEuAc4HRwJ/zrSciIiIipVewT5yZdQW+D5wJjAL2cPdFDRGYiIiIiORXqE/cH4FTgBuAnd19eYNFJSIiIiIF5bl5GwA/APoAPwVmm9nSOC0zs6UNE56IiIiIVKdQn7hCCZ6IiIiIJEiJmoiIiEgKKYkTERERSSElcSIiIiIppCROREREJIWUxImIiIikkJI4ERERkRRSEiciIiKSQkriRERERFJISZyIiIhICimJExEREUkhJXEiIiIiKaQkTkRERCSFlMSJiIiIpJCSOBEREZEUUhInIiIikkJK4kRERERSqGRJnJn1N7NnzGyKmb1lZpfE8q5mNsbM3o+PXbLWudzMMmb2rpkdlVW+p5m9Eedda2ZWqrhFRERE0qCUNXFrgR+4+/bAPsBFZrYDcBkw1t23AcbG18R5w4EdgWHAdWbWPG7reuACYJs4DSth3CIiIiJlr2RJnLvPcfdX4/NlwBSgL3AiMCouNgo4KT4/ERjt7qvcfTqQAfYys95AR3d/wd0duDVrHREREZEmqUH6xJnZQGB34CWgl7vPgZDoAT3jYn2BD7NWmxXL+sbnueUiIiIiTVbJkzgzaw/cC3zX3ZcWWrSaMi9QXt2+LjCzCWY2Yf78+bUPVkRERCQlSprEmVkLQgJ3h7vfF4vnxiZS4uO8WD4L6J+1ej9gdizvV035Jtz9Bncf4u5DevToUX8HIiIiIlJmSnl1qgE3AVPc/ZqsWQ8BI+LzEcCDWeXDzayVmQ0iXMDwcmxyXWZm+8RtnpO1joiIiEiTVFHCbQ8FzgbeMLPJsewK4GrgbjM7H5gJnAbg7m+Z2d3A24QrWy9y93VxvQuBkUAb4PE4iYiIiDRZFi74bHyGDBniEyZMSDoMERERkRqZ2UR3H1KbdXTHBhEREZEUUhInIiIikkJK4kRERERSSEmciIiISAopiRMRERFJISVxIiIiIimkJE5EREQkhZTEiYiIiKSQkjgRERGRFFISJyIiIpJCSuJEREREUkhJnIiIiEgKKYkTERERSSElcSIiIiIppCROREREJIWUxImIiIikkJI4ERERkRRSEiciIiKSQkriRERERFJISZyIiIhICimJExEREUkhJXEiIiIiKaQkTkRERCSFlMSJiIiIpJCSOBEREZEUUhInIiIikkJK4kRERERSSEmciIiISAopiRMRERFJISVxIiIiIimkJE5EREQkhZTEiYiIiKSQkjgRERGRFFISJyIiIpJCSuJEREREUkhJnIiIiEgKKYkTERERSSElcSIiIiIppCROREREJIWUxImIiIikkJI4ERERkRRSEiciIiKSQkriRERERFJISZyIiIhICimJExEREUkhJXEiIiIiKaQkTkRERCSFlMSJiIiIpFBqkjgzG2Zm75pZxswuSzoeERERkSSlIokzs+bA/wFHAzsAZ5jZDslGJSIiIpKciqQDKNJeQMbdpwGY2WjgRODtJIJ5e/ZSZi36bJNyL7CO552Zf61869RlP55nrfxx5d+PF1op7zr59lG746/NOckXZ7WltYivtu9L4XNcu/el4JmvzfHWtJ/63Fbe5ZP7HIV1ard8of3U9lDynt/qPvN5t1Hdspu/3Xr9HNXr907t9lFoY3X5TJbj5yisk9z7Vbtjr+W2N/PzbQZ/OHXX6jfeiKQliesLfJj1ehawd+5CZnYBcAHAgAEDShbMbS9+wF0vzyzZ9kVEkmSWp7zgOtXPzbdOvn2EdWoXQOG4arePwnHl20ftjr3QzDqd40Tfr+Jjqu22q3uPij3WfMfX2KQliavu3dgk93b3G4AbAIYMGVKH/2mK8+1Dt+bMvatPEuvyxVRwnVp+ARVep3bLF1qrob7oqv8iqH1MRW23FvE1xA9D/i/5+nvvwzoJ/vgm+DkK62z+Z6m+91H9560ePptN5EdNpClJSxI3C+if9bofMDuhWOjbuQ19O7dJavciIiIi6biwAXgF2MbMBplZS2A48FDCMYmIiIgkJhU1ce6+1sy+DTwJNAdudve3Eg5LREREJDGpSOIA3P0x4LGk4xAREREpB2lpThURERGRLEriRERERFJISZyIiIhICimJExEREUkhJXEiIiIiKaQkTkRERCSFlMSJiIiIpJC5l+wWo4kys/nAB0nHUWa6AwuSDqIW0hYvpC9mxVt6aYs5bfFC+mJOW7yQvpjTFi/Adu7eoTYrpGaw39py9x5Jx1BuzGyCuw9JOo5ipS1eSF/Mirf00hZz2uKF9MWctnghfTGnLV4IMdd2HTWnioiIiKSQkjgRERGRFFIS17TckHQAtZS2eCF9MSve0ktbzGmLF9IXc9rihfTFnLZ4oQ4xN9oLG0REREQaM9XEiYiIiKSQkrgmwMxuNrN5ZvZm0rEUw8z6m9kzZjbFzN4ys0uSjqkQM2ttZi+b2Wsx3quSjqkYZtbczCaZ2SNJx1IMM5thZm+Y2eS6XMXV0Myss5ndY2bvxM/yvknHVIiZbRfPbdW01My+m3RchZjZ9+Lf3JtmdpeZtU46ppqY2SUx3rfK9fxW95thZl3NbIyZvR8fuyQZY7Y88Z4Wz/F6Myurq1TzxPvH+F3xupndb2adi9mWkrimYSQwLOkgamEt8AN33x7YB7jIzHZIOKZCVgGHuvuuwG7AMDPbJ+GYinEJMCXpIGrpEHffLSVDB/wFeMLdvwDsSpmfa3d/N57b3YA9gc+A+xMOKy8z6wt8Bxji7jsBzYHhyUZVmJntBHwd2IvwmTjOzLZJNqpqjWTT34zLgLHuvg0wNr4uFyPZNN43gVOAZxs8mpqNZNN4xwA7ufsuwHvA5cVsSElcE+DuzwILk46jWO4+x91fjc+XEX78+iYbVX4eLI8vW8SprDubmlk/4FjgxqRjaYzMrCNwIHATgLuvdvfFyUZVK4cBU9293AdMrwDamFkF0BaYnXA8NdkeeNHdP3P3tcB44OSEY9pEnt+ME4FR8fko4KQGDaqA6uJ19ynu/m5CIRWUJ96n4mcC4EWgXzHbUhInZc3MBgK7Ay8lG0lhsWlyMjAPGOPuZR0v8L/Aj4D1SQdSCw48ZWYTzeyCpIOpwVbAfOCW2GR9o5m1SzqoWhgO3JV0EIW4+0fAn4CZwBxgibs/lWxUNXoTONDMuplZW+AYoH/CMRWrl7vPgfCPNtAz4Xgas/OAx4tZUEmclC0zaw/cC3zX3ZcmHU8h7r4uNkP1A/aKzSZlycyOA+a5+8SkY6mloe6+B3A0oYn9wKQDKqAC2AO43t13Bz6lvJqf8jKzlsAJwL+TjqWQ2CfrRGAQ0AdoZ2ZnJRtVYe4+Bfg9oensCeA1QvcREQDM7CeEz8QdxSyvJE7Kkpm1ICRwd7j7fUnHU6zYZDaO8u6DOBQ4wcxmAKOBQ83s9mRDqpm7z46P8wh9tfZKNqKCZgGzsmpk7yEkdWlwNPCqu89NOpAaHA5Md/f57r4GuA/YL+GYauTuN7n7Hu5+IKFJ7f2kYyrSXDPrDRAf5yUcT6NjZiOA44Azvcjx35TESdkxMyP0JZri7tckHU9NzKxH1ZVEZtaG8OPyTrJR5eful7t7P3cfSGg2e9rdy7oGw8zamVmHqufAkYSmqbLk7h8DH5rZdrHoMODtBEOqjTMo86bUaCawj5m1jd8Zh1HmF48AmFnP+DiA0PE+Deca4CFgRHw+AngwwVgaHTMbBvwYOMHdPyt2vYrShSTlwszuAg4GupvZLOBKd78p2agKGgqcDbwR+5kBXOHujyUYUyG9gVFm1pzwj9Hd7p6KYTtSpBdwf/itpgK4092fSDakGl0M3BGbJ6cB5yYcT41iP60jgG8kHUtN3P0lM7sHeJXQ/DSJdIzSf6+ZdQPWABe5+6KkA8pV3W8GcDVwt5mdT0igT0suwo3liXch8FegB/ComU1296OSi3KDPPFeDrQCxsTvuRfd/Zs1bkt3bBARERFJHzWnioiIiKSQkjgRERGRFFISJyIiIpJCSuJEREREUkhJnIiIiEgKKYkTkSbHzNzMbst6XWFm882sTkPDmFlnM/tW1uuD67otEZFiKYkTkaboU2CnODgzhLHRPtqM7XUGvlXjUiIi9UhJnIg0VY8Dx8bnG92lwMy6mtkDZva6mb1oZrvE8l+Y2c1mNs7MppnZd+IqVwODzWyymf0xlrU3s3vM7B0zuyPeVUBEpN4oiRORpmo0MNzMWgO7AC9lzbsKmOTuuwBXALdmzfsCcBTh3q1Xxvv8XgZMdffd3P3SuNzuwHeBHYCtCHciERGpN0riRKRJcvfXgYGEWrjcW7rtD9wWl3sa6GZmneK8R919lbsvINwEvFeeXbzs7rPcfT0wOe5LRKTe6N6pItKUPQT8iXAfw25Z5dU1fVbdo3BVVtk68n+PFruciEidqCZORJqym4FfuvsbOeXPAmdCuNIUWODuSwtsZxnQoSQRiojkof8MRaTJcvdZwF+qmfUL4BYzex34DBhRw3Y+MbPnzOxNwgUTj9Z3rCIiuczda15KRERERMqKmlNFREREUkhJnIiIiEgKKYkTERERSSElcSIiIiIppCROREREJIWUxImIiIikkJI4ERERkRRSEiciIiKSQv8PMJhnTpvc7gcAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 720x360 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "plt.figure(figsize=(10,5))\n",
    "plt.plot(data)\n",
    "plt.xticks(np.arange(1, 13, step=1))\n",
    "plt.ylabel('Number of listings')\n",
    "plt.xlabel('Month')\n",
    "plt.title('Number of listings per month from 06/12/18 to 06/12/19')\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The diagram above shows that availability descreases in the summer to october ==> it reflects that people tends to travel to Amsterdam in summer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"SELECT room_type, count(id) counts FROM listings GROUP BY room_type ORDER BY counts DESC \"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(query)\n",
    "rows=cur.fetchall()\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Entire home/apt', 15874), ('Private room', 4067), ('Shared room', 65)]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### number of listings per host "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "query=\"\"\"SELECT listings.host_id, host_name, COUNT(listings.id) as counts FROM listings \n",
    "         JOIN hosts ON listings.host_id=hosts.host_id\n",
    "         GROUP BY listings.host_id, host_name ORDER BY counts DESC LIMIT 10\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(query)\n",
    "rows=cur.fetchall()\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(65859990, 'Martijn', 208),\n",
       " (1464510, 'Michiel And Jane', 105),\n",
       " (76104209, 'Rated', 83),\n",
       " (113977564, 'Willem', 38),\n",
       " (517215, 'Niels En Viv (Welcome Back)', 30),\n",
       " (107745142, 'Oscar', 27),\n",
       " (14183886, 'Luis', 25),\n",
       " (11969034, 'Raul', 24),\n",
       " (44168250, 'Ruben', 21),\n",
       " (7594884, 'Ruben, Sarah & Lindy', 21)]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As for these hosts having more than 20 listings, it is obvious that this it about professional hosting activities which is forbidden within AirBnB"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "to know more about these hosts (I pick one 'Martijn') , below I issued an sql query to know more about him "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "query=\"\"\"SELECT host_id, host_about from hosts where host_id='65859990'  ;\"\"\"\n",
    "cur.execute(query)\n",
    "rows=cur.fetchall()\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(65859990,\n",
       "  \"Hi, I'm Martijn, \\r\\n\\r\\nWe are a team of professional hosts established by travelers just like you. We believe in sharing homes. Wherever you are, it is delightful to explore and discover an unknown city from an authentic home. \\r\\n\\r\\nOn behalf of home owners we advertise their properties through our airbnb profile and support them by offering you a pleasant stay. \\r\\n\\r\\nWhen you book your holiday with us, you can be confident of a memorable stay and an experience through the eyes of a local. We are more than happy to share our knowledge, give you the best insider tips and recommendations about Amsterdam.\\r\\n\\r\\nIf you have any questions, holiday wishes or simply want to have a chat, then feel free to contact us any time. We always reply within an hour!\\r\\n\\r\\nWe look forward to being your host! :)\")]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Most expensive strest in Amsterdam"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "query=\"\"\"SELECT AVG (calendar.price) average_price, street \n",
    "         from calendar JOIN listings ON calendar.listing_id=listings.id \n",
    "                       JOIN location ON listings.location_id= location.location_id\n",
    "         WHERE calendar.available is true\n",
    "         GROUP BY street  ORDER BY average_price DESC LIMIT 10;\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(query)\n",
    "rows=cur.fetchall()\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(597.578796561605, 'Amsterdam, GD, Netherlands'),\n",
       " (494.092024539877, 'Amsterdam, Emmy andriessestraat, Netherlands'),\n",
       " (431.01677852349, 'Amsterdam , Amsterdam, Netherlands'),\n",
       " (380.578512396694, 'Amsterdam, Nassaukade, Netherlands'),\n",
       " (265.0, 'Zaandam, Noord-Holland, Netherlands'),\n",
       " (239.0, 'Amsterdam, GE, Netherlands'),\n",
       " (231.0663507109, 'Amsterdam, Amsterdam, Netherlands'),\n",
       " (228.62466901158, 'Amsterdam, Netherlands'),\n",
       " (215.307090061015, 'Amsterdam, Noord-Holland, Netherlands'),\n",
       " (213.705882352941, 'Amsterdam, CE, Netherlands')]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "below a list of the 10 most expensive streets in Amsterdam"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### most interactive reviewers "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "query =\"\"\" SELECT reviewers.reviewer_id, reviewer_name, count(reviews.id) as counts from reviews\n",
    "JOIN reviewers ON reviews.reviewer_id=reviewers.reviewer_id \n",
    "GROUP BY reviewers.reviewer_id, reviewer_name \n",
    "ORDER BY counts DESC LIMIt 10\"\"\"\n",
    "cur.execute(query)\n",
    "rows=cur.fetchall()\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(12574897, 'Michel', 25),\n",
       " (44092711, 'Markus', 20),\n",
       " (11318929, 'Philippe', 18),\n",
       " (2644225, 'Joeran', 14),\n",
       " (24554860, 'Sidney', 13),\n",
       " (8749900, 'Joshua', 12),\n",
       " (1291027, 'Andrea', 11),\n",
       " (9426660, 'Thomas', 11),\n",
       " (35708120, 'Jim', 11),\n",
       " (7647712, 'Bernard', 11)]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Number of reviews per listing and their corresponding average review rating "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "query =\"\"\" SELECT reviews.listing_id, count(reviews.id) as counts,AVG(listings.review_scores_rating)  from reviews\n",
    "JOIN listings ON reviews.listing_id=listings.id \n",
    "GROUP BY reviews.listing_id\n",
    "ORDER BY counts DESC LIMIt 10\"\"\"\n",
    "cur.execute(query)\n",
    "rows=cur.fetchall()\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(82482, 695, 88.0),\n",
       " (1469096, 631, 94.0),\n",
       " (1814121, 606, 94.0),\n",
       " (785432, 602, 95.0),\n",
       " (152280, 580, 88.0),\n",
       " (694850, 576, 96.0),\n",
       " (68290, 574, 96.0),\n",
       " (654931, 541, 96.0),\n",
       " (239647, 540, 96.0),\n",
       " (68873, 527, 96.0)]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "listings '82482', '1469096' have more than 631 reviews but '1469096' seems to have more positive feedbacks"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## STEP 4 : Clean up AWS resources "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Cluster': {'ClusterIdentifier': 'capstone-project-2021',\n",
       "  'NodeType': 'dc2.large',\n",
       "  'ClusterStatus': 'deleting',\n",
       "  'ClusterAvailabilityStatus': 'Modifying',\n",
       "  'MasterUsername': 'database_user-12',\n",
       "  'DBName': 'my_database',\n",
       "  'Endpoint': {'Address': 'capstone-project-2021.cahjwy6ny05j.us-west-2.redshift.amazonaws.com',\n",
       "   'Port': 5439},\n",
       "  'ClusterCreateTime': datetime.datetime(2021, 1, 18, 14, 20, 0, 279000, tzinfo=tzutc()),\n",
       "  'AutomatedSnapshotRetentionPeriod': 1,\n",
       "  'ManualSnapshotRetentionPeriod': -1,\n",
       "  'ClusterSecurityGroups': [],\n",
       "  'VpcSecurityGroups': [{'VpcSecurityGroupId': 'sg-1d670a33',\n",
       "    'Status': 'active'}],\n",
       "  'ClusterParameterGroups': [{'ParameterGroupName': 'default.redshift-1.0',\n",
       "    'ParameterApplyStatus': 'in-sync'}],\n",
       "  'ClusterSubnetGroupName': 'default',\n",
       "  'VpcId': 'vpc-61eca419',\n",
       "  'AvailabilityZone': 'us-west-2b',\n",
       "  'PreferredMaintenanceWindow': 'sat:11:00-sat:11:30',\n",
       "  'PendingModifiedValues': {},\n",
       "  'ClusterVersion': '1.0',\n",
       "  'AllowVersionUpgrade': True,\n",
       "  'NumberOfNodes': 4,\n",
       "  'PubliclyAccessible': True,\n",
       "  'Encrypted': False,\n",
       "  'Tags': [],\n",
       "  'EnhancedVpcRouting': False,\n",
       "  'IamRoles': [{'IamRoleArn': 'arn:aws:iam::185187464837:role/iamRoleName',\n",
       "    'ApplyStatus': 'in-sync'}],\n",
       "  'MaintenanceTrackName': 'current',\n",
       "  'DeferredMaintenanceWindows': [],\n",
       "  'NextMaintenanceWindowStartTime': datetime.datetime(2021, 1, 23, 11, 0, tzinfo=tzutc())},\n",
       " 'ResponseMetadata': {'RequestId': 'e930b073-4184-4440-ad72-20bbd9cc864e',\n",
       "  'HTTPStatusCode': 200,\n",
       "  'HTTPHeaders': {'x-amzn-requestid': 'e930b073-4184-4440-ad72-20bbd9cc864e',\n",
       "   'content-type': 'text/xml',\n",
       "   'content-length': '2584',\n",
       "   'vary': 'accept-encoding',\n",
       "   'date': 'Mon, 18 Jan 2021 14:21:33 GMT'},\n",
       "  'RetryAttempts': 0}}"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "redshift.delete_cluster(ClusterIdentifier= CLUSTER_IDENTIFIER, SkipFinalClusterSnapshot= True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Key</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ClusterIdentifier</td>\n",
       "      <td>capstone-project-2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NodeType</td>\n",
       "      <td>dc2.large</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ClusterStatus</td>\n",
       "      <td>deleting</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MasterUsername</td>\n",
       "      <td>database_user-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DBName</td>\n",
       "      <td>my_database</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Endpoint</td>\n",
       "      <td>{'Address': 'capstone-project-2021.cahjwy6ny05j.us-west-2.redshift.amazonaws.com', 'Port': 5439}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>VpcId</td>\n",
       "      <td>vpc-61eca419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NumberOfNodes</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Key  \\\n",
       "0  ClusterIdentifier   \n",
       "1           NodeType   \n",
       "2      ClusterStatus   \n",
       "3     MasterUsername   \n",
       "4             DBName   \n",
       "5           Endpoint   \n",
       "6              VpcId   \n",
       "7      NumberOfNodes   \n",
       "\n",
       "                                                                                              Value  \n",
       "0                                                                             capstone-project-2021  \n",
       "1                                                                                         dc2.large  \n",
       "2                                                                                          deleting  \n",
       "3                                                                                  database_user-12  \n",
       "4                                                                                       my_database  \n",
       "5  {'Address': 'capstone-project-2021.cahjwy6ny05j.us-west-2.redshift.amazonaws.com', 'Port': 5439}  \n",
       "6                                                                                      vpc-61eca419  \n",
       "7                                                                                                 4  "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "myClusterProps=redshift.describe_clusters(ClusterIdentifier= CLUSTER_IDENTIFIER)['Clusters'][0]\n",
    "description( myClusterProps)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}