{ "cells": [ { "cell_type": "markdown", "source": [ "### Input Redshift Cluster Endpoint and User\n", "Please input your redshift cluster endpoint and existing user on that cluster." ], "metadata": {} }, { "cell_type": "code", "execution_count": 12, "source": [ "REDSHIFT_ENDPOINT = 'democluster-5bd3e16b.cr5syyfjjfc0.us-east-1.redshift.amazonaws.com:5439/dev'\n", "REDSHIFT_USER=\"demo\"\n", "REDSHIFT_IAM_ROLE='arn:aws:iam::709512860261:role/redshiftml-12-RedshiftMLIAMRole-709512860261'\n", "S3_BUCKET='redshiftml-12-redshiftmlbucket-1mxb3ll9jpxz9'" ], "outputs": [], "metadata": {} }, { "cell_type": "markdown", "source": [ "### Setup Run SQL function using Redshift Data API to get SQL query output directly into pandas dataframe\n", "In this step, we are creating function run_sql, which we will use to get SQL query output directly into pandas dataframe. We will also use this function to run DDL statements" ], "metadata": {} }, { "cell_type": "code", "execution_count": 3, "source": [ "\n", "import boto3\n", "import time\n", "import pandas as pd\n", "import numpy as np\n", "import matplotlib.pyplot as plt\n", "\n", "session = boto3.session.Session()\n", "region = session.region_name\n", "\n", "\n", "def run_sql(sql_text):\n", " client = boto3.client(\"redshift-data\")\n", " res = client.execute_statement(Database=REDSHIFT_ENDPOINT.split('/')[1], DbUser=REDSHIFT_USER, Sql=sql_text,\n", " ClusterIdentifier=REDSHIFT_ENDPOINT.split('.')[0])\n", " query_id = res[\"Id\"]\n", " done = False\n", " while not done:\n", " time.sleep(1)\n", " status_description = client.describe_statement(Id=query_id)\n", " status = status_description[\"Status\"]\n", " if status == \"FAILED\":\n", " raise Exception('SQL query failed:' + query_id + \": \" + status_description[\"Error\"])\n", " elif status == \"FINISHED\":\n", " if status_description['ResultRows']>0:\n", " results = client.get_statement_result(Id=query_id)\n", " column_labels = []\n", " for i in range(len(results[\"ColumnMetadata\"])): column_labels.append(results[\"ColumnMetadata\"][i]['label'])\n", " records = []\n", " for record in results.get('Records'):\n", " records.append([list(rec.values())[0] for rec in record])\n", " df = pd.DataFrame(np.array(records), columns=column_labels)\n", " return df\n", " else:\n", " \n", " return query_id \n", " " ], "outputs": [], "metadata": {} }, { "cell_type": "markdown", "source": [ "## Data Preparation Script\n", "Data preparation script to be run on Redshift" ], "metadata": {} }, { "cell_type": "code", "execution_count": 4, "source": [ "setup_script = \"\"\"\n", "DROP TABLE IF EXISTS ridership CASCADE;\n", "DROP TABLE IF EXISTS weather CASCADE;\n", "DROP TABLE IF EXISTS holiday CASCADE;\n", "DROP TABLE IF EXISTS trip_data CASCADE;\n", "\n", "CREATE TABLE IF NOT EXISTS ridership\n", "( trip_id INT\n", ", trip_duration_seconds INT\n", ", trip_start_time timestamp\n", ", trip_stop_time timestamp\n", ", from_station_name VARCHAR(50)\n", ", to_station_name VARCHAR(50)\n", ", from_station_id SMALLINT\n", ", to_station_id SMALLINT\n", ", user_type VARCHAR(20));\n", "\n", "CREATE TABLE IF NOT EXISTS weather\n", "( longitude_x DECIMAL(5,2)\n", ", latitude_y DECIMAL(5,2)\n", ", station_name VARCHAR(20)\n", ", climate_id BIGINT\n", ", datetime_utc TIMESTAMP\n", ", weather_year SMALLINT\n", ", weather_month SMALLINT\n", ", weather_day SMALLINT\n", ", time_utc VARCHAR(5)\n", ", temp_c DECIMAL(5,2)\n", ", temp_flag VARCHAR(1)\n", ", dew_point_temp_c DECIMAL(5,2)\n", ", dew_point_temp_flag VARCHAR(1)\n", ", rel_hum SMALLINT\n", ", rel_hum_flag VARCHAR(1)\n", ", precip_amount_mm DECIMAL(5,2)\n", ", precip_amount_flag VARCHAR(1)\n", ", wind_dir_10s_deg VARCHAR(10)\n", ", wind_dir_flag VARCHAR(1)\n", ", wind_spd_kmh VARCHAR(10)\n", ", wind_spd_flag VARCHAR(1)\n", ", visibility_km VARCHAR(10)\n", ", visibility_flag VARCHAR(1)\n", ", stn_press_kpa DECIMAL(5,2)\n", ", stn_press_flag VARCHAR(1)\n", ", hmdx SMALLINT\n", ", hmdx_flag VARCHAR(1)\n", ", wind_chill VARCHAR(10)\n", ", wind_chill_flag VARCHAR(1)\n", ", weather VARCHAR(10));\n", "\n", "CREATE TABLE IF NOT EXISTS holiday\n", "( holiday_date DATE\n", ", description VARCHAR(100));\n", "\n", "\n", "COPY ridership FROM \n", "'s3://redshift-ml-bikesharing-data/bike-sharing-data/ridership/'\n", "IAM_ROLE '{}'\n", "FORMAT csv IGNOREHEADER 1 DATEFORMAT 'auto' TIMEFORMAT 'auto' REGION 'us-west-2' gzip;\n", "\n", "COPY weather FROM\n", "'s3://redshift-ml-bikesharing-data/bike-sharing-data/weather/'\n", "IAM_ROLE '{}'\n", "FORMAT csv IGNOREHEADER 1 DATEFORMAT 'auto' TIMEFORMAT 'auto' REGION 'us-west-2' gzip;\n", "\n", "COPY holiday FROM\n", "'s3://redshift-ml-bikesharing-data/bike-sharing-data/holiday/'\n", "IAM_ROLE '{}'\n", "FORMAT csv IGNOREHEADER 1 DATEFORMAT 'auto' TIMEFORMAT 'auto' REGION 'us-west-2' gzip;\n", "\n", "CREATE OR REPLACE VIEW ridership_view AS\n", "SELECT\n", " trip_time\n", " , trip_count\n", " , TO_CHAR(trip_time,'hh24') ::INT trip_hour\n", " , TO_CHAR(trip_time, 'dd') :: INT trip_day\n", " , TO_CHAR(trip_time, 'mm') :: INT trip_month\n", " , TO_CHAR(trip_time, 'yy') :: INT trip_year\n", " , TO_CHAR(trip_time, 'q') :: INT trip_quarter\n", " , TO_CHAR(trip_time, 'w') :: INT trip_month_week\n", " , TO_CHAR(trip_time, 'd') :: INT trip_week_day\n", "FROM \n", " (SELECT \n", " CASE\n", " WHEN TRUNC(r.trip_start_time) < '2017-07-01'::DATE\n", " THEN CONVERT_TIMEZONE('US/Eastern', DATE_TRUNC('hour',r.trip_start_time))\n", " ELSE DATE_TRUNC('hour',r.trip_start_time)\n", " END trip_time\n", " , COUNT(1) trip_count\n", " FROM \n", " ridership r\n", " WHERE r.trip_duration_seconds BETWEEN 60 AND 60 * 60 * 24\n", " GROUP BY\n", " 1);\n", "\n", "CREATE OR REPLACE VIEW weather_view AS\n", "SELECT \n", " CONVERT_TIMEZONE('US/Eastern', \n", " DATE_TRUNC('hour',datetime_utc)) daytime\n", " , ROUND(AVG(temp_c)) temp_c\n", " , ROUND(AVG(precip_amount_mm)) precip_amount_mm\n", "FROM weather\n", "GROUP BY 1;\n", "\n", "DROP TABLE IF EXISTS trip_data;\n", "CREATE TABLE trip_data AS \n", "SELECT \n", " r.trip_time\n", " ,r.trip_count\n", " ,r.trip_hour\n", " ,r.trip_day\n", " ,r.trip_month\n", " ,r.trip_year\n", " ,r.trip_quarter\n", " ,r.trip_month_week\n", " ,r.trip_week_day\n", " ,w.temp_c\n", " ,w.precip_amount_mm\n", " ,CASE\n", " WHEN h.holiday_date IS NOT NULL\n", " THEN 1\n", " WHEN TO_CHAR(r.trip_time,'D')::INT IN (1,7)\n", " THEN 1\n", " ELSE 0\n", " END is_holiday\n", " , ROW_NUMBER() OVER (ORDER BY RANDOM()) serial_number\n", "FROM \n", " ridership_view r\n", "JOIN weather_view w\n", " ON ( r.trip_time = w.daytime )\n", "LEFT OUTER JOIN holiday h\n", " ON ( TRUNC(r.trip_time) = h.holiday_date );\n", "\n", "\"\"\"" ], "outputs": [], "metadata": {} }, { "cell_type": "markdown", "source": [ "### Run data preparation script in Redshift" ], "metadata": {} }, { "cell_type": "code", "execution_count": 6, "source": [ "Sql_Commands=setup_script.split(\";\")\n", "for sql_text in Sql_Commands[:-1]:\n", " print(\"Executing:\", sql_text.format(REDSHIFT_IAM_ROLE))\n", " print(\"Queryid:\",run_sql(sql_text.format(REDSHIFT_IAM_ROLE)))\n", " " ], "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ "Executing: \n", "DROP TABLE IF EXISTS ridership CASCADE\n", "Queryid: 4523a0db-39b7-4f43-b1ab-5501b1e6d000:FINISHED\n", "Executing: \n", "DROP TABLE IF EXISTS weather CASCADE\n", "Queryid: 1a6e139e-2656-4c8e-ae3f-4d6eb81699f2:FINISHED\n", "Executing: \n", "DROP TABLE IF EXISTS holiday CASCADE\n", "Queryid: c075d955-35c6-41d3-836b-84b593ccc778:FINISHED\n", "Executing: \n", "DROP TABLE IF EXISTS trip_data CASCADE\n", "Queryid: 5a9afd55-ef5d-45c1-80d1-ccb473cb7590:FINISHED\n", "Executing: \n", "\n", "CREATE TABLE IF NOT EXISTS ridership\n", "( trip_id INT\n", ", trip_duration_seconds INT\n", ", trip_start_time timestamp\n", ", trip_stop_time timestamp\n", ", from_station_name VARCHAR(50)\n", ", to_station_name VARCHAR(50)\n", ", from_station_id SMALLINT\n", ", to_station_id SMALLINT\n", ", user_type VARCHAR(20))\n", "Queryid: 3e79bf50-6c95-4f2a-b7c5-0c80e02475ef:FINISHED\n", "Executing: \n", "\n", "CREATE TABLE IF NOT EXISTS weather\n", "( longitude_x DECIMAL(5,2)\n", ", latitude_y DECIMAL(5,2)\n", ", station_name VARCHAR(20)\n", ", climate_id BIGINT\n", ", datetime_utc TIMESTAMP\n", ", weather_year SMALLINT\n", ", weather_month SMALLINT\n", ", weather_day SMALLINT\n", ", time_utc VARCHAR(5)\n", ", temp_c DECIMAL(5,2)\n", ", temp_flag VARCHAR(1)\n", ", dew_point_temp_c DECIMAL(5,2)\n", ", dew_point_temp_flag VARCHAR(1)\n", ", rel_hum SMALLINT\n", ", rel_hum_flag VARCHAR(1)\n", ", precip_amount_mm DECIMAL(5,2)\n", ", precip_amount_flag VARCHAR(1)\n", ", wind_dir_10s_deg VARCHAR(10)\n", ", wind_dir_flag VARCHAR(1)\n", ", wind_spd_kmh VARCHAR(10)\n", ", wind_spd_flag VARCHAR(1)\n", ", visibility_km VARCHAR(10)\n", ", visibility_flag VARCHAR(1)\n", ", stn_press_kpa DECIMAL(5,2)\n", ", stn_press_flag VARCHAR(1)\n", ", hmdx SMALLINT\n", ", hmdx_flag VARCHAR(1)\n", ", wind_chill VARCHAR(10)\n", ", wind_chill_flag VARCHAR(1)\n", ", weather VARCHAR(10))\n", "Queryid: 048c3a69-4b78-4aea-90a5-569fa1c9b47e:FINISHED\n", "Executing: \n", "\n", "CREATE TABLE IF NOT EXISTS holiday\n", "( holiday_date DATE\n", ", description VARCHAR(100))\n", "Queryid: 746a5b6e-cfd8-4148-8ab8-4694dfb51d20:FINISHED\n", "Executing: \n", "\n", "\n", "COPY ridership FROM \n", "'s3://redshift-ml-bikesharing-data/bike-sharing-data/ridership/'\n", "IAM_ROLE 'arn:aws:iam::709512860261:role/redshiftml-12-RedshiftMLIAMRole-709512860261'\n", "FORMAT csv IGNOREHEADER 1 DATEFORMAT 'auto' TIMEFORMAT 'auto' REGION 'us-west-2' gzip\n", "Queryid: b3d13092-fa59-4228-91c3-050d65f23c1d:FINISHED\n", "Executing: \n", "\n", "COPY weather FROM\n", "'s3://redshift-ml-bikesharing-data/bike-sharing-data/weather/'\n", "IAM_ROLE 'arn:aws:iam::709512860261:role/redshiftml-12-RedshiftMLIAMRole-709512860261'\n", "FORMAT csv IGNOREHEADER 1 DATEFORMAT 'auto' TIMEFORMAT 'auto' REGION 'us-west-2' gzip\n", "Queryid: 6c5377db-6705-428d-8e50-81d28749513f:FINISHED\n", "Executing: \n", "\n", "COPY holiday FROM\n", "'s3://redshift-ml-bikesharing-data/bike-sharing-data/holiday/'\n", "IAM_ROLE 'arn:aws:iam::709512860261:role/redshiftml-12-RedshiftMLIAMRole-709512860261'\n", "FORMAT csv IGNOREHEADER 1 DATEFORMAT 'auto' TIMEFORMAT 'auto' REGION 'us-west-2' gzip\n", "Queryid: e866cfde-8d84-4348-8e6b-560361a7502c:FINISHED\n", "Executing: \n", "\n", "CREATE OR REPLACE VIEW ridership_view AS\n", "SELECT\n", " trip_time\n", " , trip_count\n", " , TO_CHAR(trip_time,'hh24') ::INT trip_hour\n", " , TO_CHAR(trip_time, 'dd') :: INT trip_day\n", " , TO_CHAR(trip_time, 'mm') :: INT trip_month\n", " , TO_CHAR(trip_time, 'yy') :: INT trip_year\n", " , TO_CHAR(trip_time, 'q') :: INT trip_quarter\n", " , TO_CHAR(trip_time, 'w') :: INT trip_month_week\n", " , TO_CHAR(trip_time, 'd') :: INT trip_week_day\n", "FROM \n", " (SELECT \n", " CASE\n", " WHEN TRUNC(r.trip_start_time) < '2017-07-01'::DATE\n", " THEN CONVERT_TIMEZONE('US/Eastern', DATE_TRUNC('hour',r.trip_start_time))\n", " ELSE DATE_TRUNC('hour',r.trip_start_time)\n", " END trip_time\n", " , COUNT(1) trip_count\n", " FROM \n", " ridership r\n", " WHERE r.trip_duration_seconds BETWEEN 60 AND 60 * 60 * 24\n", " GROUP BY\n", " 1)\n", "Queryid: d4537666-6dc2-4dd9-8fd0-43eb52ec9d8b:FINISHED\n", "Executing: \n", "\n", "CREATE OR REPLACE VIEW weather_view AS\n", "SELECT \n", " CONVERT_TIMEZONE('US/Eastern', \n", " DATE_TRUNC('hour',datetime_utc)) daytime\n", " , ROUND(AVG(temp_c)) temp_c\n", " , ROUND(AVG(precip_amount_mm)) precip_amount_mm\n", "FROM weather\n", "GROUP BY 1\n", "Queryid: 96800014-be5a-4301-888d-5bf5e4ad876b:FINISHED\n", "Executing: \n", "\n", "DROP TABLE IF EXISTS trip_data\n", "Queryid: 59240c23-3105-4f4d-ad5b-6206be22efd8:FINISHED\n", "Executing: \n", "CREATE TABLE trip_data AS \n", "SELECT \n", " r.trip_time\n", " ,r.trip_count\n", " ,r.trip_hour\n", " ,r.trip_day\n", " ,r.trip_month\n", " ,r.trip_year\n", " ,r.trip_quarter\n", " ,r.trip_month_week\n", " ,r.trip_week_day\n", " ,w.temp_c\n", " ,w.precip_amount_mm\n", " ,CASE\n", " WHEN h.holiday_date IS NOT NULL\n", " THEN 1\n", " WHEN TO_CHAR(r.trip_time,'D')::INT IN (1,7)\n", " THEN 1\n", " ELSE 0\n", " END is_holiday\n", " , ROW_NUMBER() OVER (ORDER BY RANDOM()) serial_number\n", "FROM \n", " ridership_view r\n", "JOIN weather_view w\n", " ON ( r.trip_time = w.daytime )\n", "LEFT OUTER JOIN holiday h\n", " ON ( TRUNC(r.trip_time) = h.holiday_date )\n", "Queryid: 65e22764-40f3-4592-9273-4cccbb64c4f4:FINISHED\n" ] } ], "metadata": {} }, { "cell_type": "markdown", "source": [ "### Read SQL output with Pandas Dataframe" ], "metadata": {} }, { "cell_type": "code", "execution_count": 7, "source": [ "df = run_sql(\"select trip_count, trip_time from trip_data\");\n", "df.head(10)" ], "outputs": [ { "output_type": "execute_result", "data": { "text/plain": [ " trip_count trip_time\n", "0 449 2017-09-03 17:00:00\n", "1 15 2018-05-07 01:00:00\n", "2 606 2018-06-21 09:00:00\n", "3 346 2018-05-09 12:00:00\n", "4 8 2017-01-15 02:00:00\n", "5 95 2017-08-27 08:00:00\n", "6 95 2018-03-26 21:00:00\n", "7 88 2018-09-28 21:00:00\n", "8 466 2018-08-25 19:00:00\n", "9 17 2017-04-22 07:00:00" ], "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
trip_counttrip_time
04492017-09-03 17:00:00
1152018-05-07 01:00:00
26062018-06-21 09:00:00
33462018-05-09 12:00:00
482017-01-15 02:00:00
5952017-08-27 08:00:00
6952018-03-26 21:00:00
7882018-09-28 21:00:00
84662018-08-25 19:00:00
9172017-04-22 07:00:00
\n", "
" ] }, "metadata": {}, "execution_count": 7 } ], "metadata": {} }, { "cell_type": "code", "execution_count": 6, "source": [ "df.describe()" ], "outputs": [ { "output_type": "execute_result", "data": { "text/plain": [ " trip_count trip_time\n", "count 17306 17306\n", "unique 1066 17306\n", "top 2 2017-11-28 20:00:00.0\n", "freq 239 1" ], "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
trip_counttrip_time
count1730617306
unique106617306
top22017-11-28 20:00:00.0
freq2391
\n", "
" ] }, "metadata": {}, "execution_count": 6 } ], "metadata": {} }, { "cell_type": "markdown", "source": [ "### Visualize data using matplotlib" ], "metadata": {} }, { "cell_type": "code", "execution_count": 8, "source": [ "plt.hist(df.trip_count)\n", "plt.xlabel('trip count')\n", "plt.title('trip count histogram')" ], "outputs": [ { "output_type": "execute_result", "data": { "text/plain": [ "Text(0.5, 1.0, 'trip count histogram')" ] }, "metadata": {}, "execution_count": 8 }, { "output_type": "display_data", "data": { "text/plain": [ "
" ], "image/png": "iVBORw0KGgoAAAANSUhEUgAAAX0AAAEWCAYAAACKSkfIAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuNCwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8QVMy6AAAACXBIWXMAAAsTAAALEwEAmpwYAAAWsElEQVR4nO3df7RdZX3n8feHoEBVFIaAkMSG2vgDaIslprR2pjg4JaOuhvlBG0clddFmpEzHrjWODZ1Oa1uzpHXWjMN0oItlNcFfmGVriVocM1R0qmi4KBp+LiIgxCBJpSpQRcHv/HGe1OPN/XFucnNvkuf9Wuusvfd3P3vvZx/gczfP3uecVBWSpD4cMd8dkCTNHUNfkjpi6EtSRwx9SeqIoS9JHTH0Jakjhr7mTJLnJHk0yYL57stsSbIhyVumWP9okh+byz5JUzH0NSuS3JfkZVO1qar7q+rpVfXkXPVrfyQ5J8mO/dlHO997DvRxpFEZ+poTSY6c7z706nD6PyvtP0Nf+y3Ju4HnAB9uwxlvSrI0SSW5KMn9wN8M1Y5s292Q5K1Jtib5ZpJrkxw/xXFWJbklybeSfDnJylY/JcnmJA8n2Z7k14e2+aHhl/FX1e3/UN6Y5EutDx9IcnSSpwHXAae0c3o0ySmTdO24JB9N8kiSzyV57tD+K8mPt/mXJ7m9tftqO+6Ex0lyVJK3J9nZXm9PctTQft+U5MG27tfGHWdDkiuT/HWSx4CXJnlFki+09+6BJG8e2teefy6va+v+Psnrk7y4vS/fSPKn0/+boENCVfnytd8v4D7gZUPLS4ECrgaeBhwzVDuytbkB+CpwRmvzF8B7Jtn/CuCbwL9gcLGyCHhBW/dJ4ArgaOBMYDdwblu3AXjL0H7OAXaM6/dW4BTgeOAO4PUTtZ2kXxuAh1v/jgTeC1wztL6AH2/zDwL/tM0fB/z0ZMcB/hD4LHAisBD4DPBHbd1K4GvA6cCPAO8ed5wN7b16SXuvjm7H+Im2/JPAQ8D54/5Z/Vlr+4vAd4C/asdfBOwCfmG+/z3ztf8vr/R1oL25qh6rqm9Psv7dVXVrVT0G/FfglycZjrgIeGdVbamq71fVV6vqziRLgJ8HfruqvlNVtwDvAF47gz5eXlU7q+ph4MMM/nDMxF9W1daqeoJB6E+2/feA05IcW1V/X1Wfn2Kfrwb+sKp2VdVu4A/4wTn9MvCuqrqtqv6hrRvv2qr6dHuvvlNVN1TVtrb8JeD9wC+M2+aPWtuPA48B72/H/yrw/4AXTf9W6GBn6OtAe2AG678CPAU4YYJ2S4AvT1A/BXi4qh4Zt59FM+jj14bm/wF4+gy2ncn2/wZ4OfCVJJ9M8rNT7PMUBuexx1dabc+64fdtovf4h2pJfibJJ5LsTvJN4PXs/T4/NDT/7QmWZ/q+6CBk6Gu2TPZ1rdN9jeuSofnnMLga/rsJ2j0APHeC+k7g+CTPGLefr7b5xxgMgezx7Gn6M2xWv4K2qm6qqlUMhkz+Ctg0xXF2Aj86tPycVoPBMNHioXXD7+E/Hm7c8vuAzcCSqnomg6GczKT/OjwY+potDwH78jz6a5KcluRHGIxjf7AmfqTzz4HXJTk3yRFJFiV5QVU9wGC8+63tBuxPMhgKem/b7hbg5UmOT/Js4LdmeE7/JMkz9+G8fkiSpyZ5dZJnVtX3gG8Be85zouO8H/jdJAuTnAD8HvCetm4Tg/fihe19+70RuvAMBv9H9J0kK4B/t7/npEOToa/Z8lYGIfWNJG+cwXbvZnDj8WsMbiL+x4kaVdVW4HXA/2Bwk/KT/OBK+FUMbkbuBD4E/H5VbRna/xcZ3LD9OPCBUTtWVXcyCN972nlN9vTOqF4L3JfkWwyGV14zxXHeAowBXwK2AZ9vNarqOuBy4BPAduDGtv/Hpzj2bwB/mOQRBn8kNk3RVoexVPkjKpofSW5g8LTOO+a7L4eyJC8EbgWOajeTpUl5pS8dgpL8qzZkdBzwx8CHDXyNwtCXDk3/nsHnEb7M4N7AxfPbHR0qHN6RpI54pS9JHTnovwTrhBNOqKVLl853NyTpkHLzzTf/XVUtHF8/6EN/6dKljI2NzXc3JOmQkuQrE9Ud3pGkjhj6ktQRQ1+SOmLoS1JHDH1J6oihL0kdMfQlqSOGviR1ZKTQT/KsJB9McmeSO5L8bPtRii1J7m7T44baX5pke5K7kpw3VD8ryba27vIk/nKPJM2hUT+R+z+Bj1XVv03yVAY/P/c7wPVVdVmSdcA64LeTnAasBk5n8Fue/zfJ89qvIV0JrAU+C/w1sBK4blbPaMjSdR89ULue0n2XvWJejitJ05n2Sj/JscA/Y/BzdVTVd6vqG8AqYGNrthE4v82vAq6pqser6l4Gv+yzIsnJwLFVdWMNvtrz6qFtJElzYJThnR9j8L3d70ryhSTvSPI04KSqehCgTU9s7Rcx+BHrPXa02qI2P76+lyRrk4wlGdu9e/eMTkiSNLlRQv9I4KeBK6vqRcBjDIZyJjPROH1NUd+7WHVVVS2vquULF+71JXGSpH00SujvAHZU1efa8gcZ/BF4qA3Z0Ka7htovGdp+MYMfrN7R5sfXJUlzZNrQr6qvAQ8keX4rnQvcDmwG1rTaGuDaNr8ZWJ3kqCSnAsuArW0I6JEkZ7endi4c2kaSNAdGfXrnN4H3tid37gFex+APxqYkFwH3AxcAVNVtSTYx+MPwBHBJe3IHBr/juQE4hsFTOwfsyR1J0t5GCv2qugVYPsGqcydpvx5YP0F9DDhjBv2TJM0iP5ErSR0x9CWpI4a+JHXE0Jekjhj6ktQRQ1+SOmLoS1JHDH1J6oihL0kdMfQlqSOGviR1xNCXpI4Y+pLUEUNfkjpi6EtSRwx9SeqIoS9JHTH0Jakjhr4kdcTQl6SOGPqS1BFDX5I6YuhLUkcMfUnqiKEvSR0ZKfST3JdkW5Jbkoy12vFJtiS5u02PG2p/aZLtSe5Kct5Q/ay2n+1JLk+S2T8lSdJkZnKl/9KqOrOqlrfldcD1VbUMuL4tk+Q0YDVwOrASuCLJgrbNlcBaYFl7rdz/U5AkjWp/hndWARvb/Ebg/KH6NVX1eFXdC2wHViQ5GTi2qm6sqgKuHtpGkjQHRg39Aj6e5OYka1vtpKp6EKBNT2z1RcADQ9vuaLVFbX58fS9J1iYZSzK2e/fuEbsoSZrOkSO2e0lV7UxyIrAlyZ1TtJ1onL6mqO9drLoKuApg+fLlE7aRJM3cSFf6VbWzTXcBHwJWAA+1IRvadFdrvgNYMrT5YmBnqy+eoC5JmiPThn6SpyV5xp554BeBW4HNwJrWbA1wbZvfDKxOclSSUxncsN3ahoAeSXJ2e2rnwqFtJElzYJThnZOAD7WnK48E3ldVH0tyE7ApyUXA/cAFAFV1W5JNwO3AE8AlVfVk29fFwAbgGOC69pIkzZFpQ7+q7gF+aoL614FzJ9lmPbB+gvoYcMbMuylJmg1+IleSOmLoS1JHDH1J6oihL0kdMfQlqSOGviR1xNCXpI4Y+pLUEUNfkjpi6EtSRwx9SeqIoS9JHTH0Jakjhr4kdcTQl6SOGPqS1BFDX5I6YuhLUkcMfUnqiKEvSR0x9CWpI4a+JHXE0Jekjhj6ktQRQ1+SOjJy6CdZkOQLST7Slo9PsiXJ3W163FDbS5NsT3JXkvOG6mcl2dbWXZ4ks3s6kqSpzORK/w3AHUPL64Drq2oZcH1bJslpwGrgdGAlcEWSBW2bK4G1wLL2WrlfvZckzchIoZ9kMfAK4B1D5VXAxja/ETh/qH5NVT1eVfcC24EVSU4Gjq2qG6uqgKuHtpEkzYFRr/TfDrwJ+P5Q7aSqehCgTU9s9UXAA0PtdrTaojY/vr6XJGuTjCUZ271794hdlCRNZ9rQT/JKYFdV3TziPicap68p6nsXq66qquVVtXzhwoUjHlaSNJ0jR2jzEuCXkrwcOBo4Nsl7gIeSnFxVD7ahm12t/Q5gydD2i4Gdrb54grokaY5Me6VfVZdW1eKqWsrgBu3fVNVrgM3AmtZsDXBtm98MrE5yVJJTGdyw3dqGgB5JcnZ7aufCoW0kSXNglCv9yVwGbEpyEXA/cAFAVd2WZBNwO/AEcElVPdm2uRjYABwDXNdekqQ5MqPQr6obgBva/NeBcydptx5YP0F9DDhjpp2UJM0OP5ErSR0x9CWpI4a+JHXE0Jekjhj6ktQRQ1+SOmLoS1JHDH1J6oihL0kdMfQlqSOGviR1xNCXpI4Y+pLUEUNfkjpi6EtSRwx9SeqIoS9JHdmfn0vUJJau++i8Hfu+y14xb8eWdPDzSl+SOmLoS1JHDH1J6oihL0kdMfQlqSOGviR1xNCXpI5MG/pJjk6yNckXk9yW5A9a/fgkW5Lc3abHDW1zaZLtSe5Kct5Q/awk29q6y5PkwJyWJGkio1zpPw7886r6KeBMYGWSs4F1wPVVtQy4vi2T5DRgNXA6sBK4IsmCtq8rgbXAsvZaOXunIkmazrShXwOPtsWntFcBq4CNrb4ROL/NrwKuqarHq+peYDuwIsnJwLFVdWNVFXD10DaSpDkw0ph+kgVJbgF2AVuq6nPASVX1IECbntiaLwIeGNp8R6stavPj65KkOTJS6FfVk1V1JrCYwVX7GVM0n2icvqao772DZG2SsSRju3fvHqWLkqQRzOjpnar6BnADg7H4h9qQDW26qzXbASwZ2mwxsLPVF09Qn+g4V1XV8qpavnDhwpl0UZI0hVGe3lmY5Flt/hjgZcCdwGZgTWu2Bri2zW8GVic5KsmpDG7Ybm1DQI8kObs9tXPh0DaSpDkwylcrnwxsbE/gHAFsqqqPJLkR2JTkIuB+4AKAqrotySbgduAJ4JKqerLt62JgA3AMcF17SZLmyLShX1VfAl40Qf3rwLmTbLMeWD9BfQyY6n6AJOkA8hO5ktQRQ1+SOmLoS1JHDH1J6oihL0kdMfQlqSOGviR1xNCXpI4Y+pLUEUNfkjpi6EtSRwx9SeqIoS9JHTH0Jakjhr4kdcTQl6SOGPqS1BFDX5I6YuhLUkcMfUnqiKEvSR0x9CWpI0fOdwc0u5au++i8HPe+y14xL8eVNDNe6UtSRwx9SerItKGfZEmSTyS5I8ltSd7Q6scn2ZLk7jY9bmibS5NsT3JXkvOG6mcl2dbWXZ4kB+a0JEkTGeVK/wngP1XVC4GzgUuSnAasA66vqmXA9W2Ztm41cDqwErgiyYK2ryuBtcCy9lo5i+ciSZrGtKFfVQ9W1efb/CPAHcAiYBWwsTXbCJzf5lcB11TV41V1L7AdWJHkZODYqrqxqgq4emgbSdIcmNGYfpKlwIuAzwEnVdWDMPjDAJzYmi0CHhjabEerLWrz4+uSpDkycugneTrwF8BvVdW3pmo6Qa2mqE90rLVJxpKM7d69e9QuSpKmMVLoJ3kKg8B/b1X9ZSs/1IZsaNNdrb4DWDK0+WJgZ6svnqC+l6q6qqqWV9XyhQsXjnoukqRpjPL0ToA/B+6oqv8+tGozsKbNrwGuHaqvTnJUklMZ3LDd2oaAHklydtvnhUPbSJLmwCifyH0J8FpgW5JbWu13gMuATUkuAu4HLgCoqtuSbAJuZ/DkzyVV9WTb7mJgA3AMcF17SZLmyLShX1V/y8Tj8QDnTrLNemD9BPUx4IyZdFCSNHv8RK4kdcTQl6SO+C2bmhXz9e2e4Dd8SjPhlb4kdcTQl6SOGPqS1BFDX5I6YuhLUkcMfUnqiKEvSR0x9CWpI4a+JHXET+TqkDdfnwb2k8A6FHmlL0kdMfQlqSOGviR1xNCXpI4Y+pLUEUNfkjpi6EtSRwx9SeqIoS9JHTH0Jakjhr4kdcTQl6SOGPqS1JFpQz/JO5PsSnLrUO34JFuS3N2mxw2tuzTJ9iR3JTlvqH5Wkm1t3eVJMvunI0mayihX+huAleNq64Drq2oZcH1bJslpwGrg9LbNFUkWtG2uBNYCy9pr/D4lSQfYtKFfVZ8CHh5XXgVsbPMbgfOH6tdU1eNVdS+wHViR5GTg2Kq6saoKuHpoG0nSHNnXMf2TqupBgDY9sdUXAQ8MtdvRaova/Pj6hJKsTTKWZGz37t372EVJ0nizfSN3onH6mqI+oaq6qqqWV9XyhQsXzlrnJKl3+xr6D7UhG9p0V6vvAJYMtVsM7Gz1xRPUJUlzaF9DfzOwps2vAa4dqq9OclSSUxncsN3ahoAeSXJ2e2rnwqFtJElzZNofRk/yfuAc4IQkO4DfBy4DNiW5CLgfuACgqm5Lsgm4HXgCuKSqnmy7upjBk0DHANe1l3TImq8fZAd/lF37btrQr6pXTbLq3EnarwfWT1AfA86YUe8kSbPKT+RKUkcMfUnqiKEvSR2Zdkxf0sFnvm4iewP50OeVviR1xNCXpI4Y+pLUEUNfkjpi6EtSRwx9SeqIoS9JHTH0Jakjhr4kdcRP5EoamZ8EPvR5pS9JHTH0Jakjhr4kdcTQl6SOGPqS1BGf3pF00PNH6GePV/qS1BFDX5I6YuhLUkcc05ekKRxun0L2Sl+SOjLnoZ9kZZK7kmxPsm6ujy9JPZvT0E+yAPjfwL8ETgNeleS0ueyDJPVsrq/0VwDbq+qeqvoucA2wao77IEndmusbuYuAB4aWdwA/M75RkrXA2rb4aJK79vF4L2jTpwDfOwinB3PfnDp1Oo/T/DHfA+5k3/3oRMVU1X7sc2aSXACcV1W/1pZfC6yoqt88QMd7ss0eAXz/IJwezH1z6tTpPE+ragGz7IjZ3uE0dgBLhpYXAzvnuA+S1K25Dv2bgGVJTk3yVGA1sHmO+yBJ3ZrTMf2qeiLJfwD+D7AAeGdV3XYAD3lTm54I7DoIpwdz35w6dTr/01k3p2P6kqT5NdfDO5KkeWToS1JHDukvXEtyNPBp4EVA5rk7kjRXnmCQeXdW1Rkz2fBQv9J/vE23Aw8D3qCQdLh7kv24YD/UQ//5DL7D53c59M9Fkqay56L2CPbjAveQfnonyRbgZAbhfySD/+U5pIesJGkETzIY3rmjm+GdJK8EvgG8kMH3U+zEcX1JfdjnrDtkQx94SXsFOB04hcEHviTpcHdEez0vyQ0z2fBQH95ZCFwLXAn8CfDstmrPFxZJ0uGkGHwL51OB7wBf7mZ4pzkZOB7YwA8CHw7985KkiYRB4H8POAp4fpI3z2gHh/KVviRpZrwilqSOGPqS1BFDX5I6YuhLUkcMfUnqiKGvw1aSZyX5jWnafGau+jOKJOcnOW2++6HDl6Gvw9mzgAlDP8kCgKr6ubns0AjOZ/AlgtIBYejrcHYZ8NwktyR5W5JzknwiyfuAbQBJHm3Tc5J8KsmHktye5M+S7PXfR5IXJ/lMki8m2ZrkGUmOTvKuJNuSfCHJS1vbX03yp0PbfiTJOXuOm2R9289nk5yU5OeAXwLe1vr83AP9Bqk/fiOlDmfrgDOq6kwYBDuwotXunaD9CgZX2V8BPgb8a+CDe1YmeSrwAeBXquqmJMcC3wbeAFBVP5HkBcDHkzxvmr49DfhsVf2XJH8C/HpVvSXJZuAjVfXBabaX9olX+urN1kkCf8+6e6rqSeD9wM+PW/984MGqugmgqr5VVU+0du9utTsZ/NGYLvS/C3ykzd8MLJ3piUj7wtBXbx6bYt347yQZv5wJanvqE3mCH/5v7Oih+e/VD74DZb9+CUmaCUNfh7NHgGfMoP2KJKe2sfxfAf523Po7gVOSvBigjecfCXwKeHWrPQ94DnAXcB9wZpIjkixhMHw0232WZsTQ12Grqr4OfDrJrUneNsImNzK4+XsrcC/woXH7+y6DPwb/K8kXgS0Mrt6vABYk2cZgzP9Xq+px4NNtP9uA/wZ8foQ+XAP853ZD2Bu5mnV+y6bEP97kfWNVvXKeuyIdUF7pS1JHvNKXpI54pS9JHTH0Jakjhr4kdcTQl6SOGPqS1JH/D3FscD7qCJe1AAAAAElFTkSuQmCC" }, "metadata": { "needs_background": "light" } } ], "metadata": {} }, { "cell_type": "code", "execution_count": 10, "source": [ "df = run_sql(\"select round(trip_count/100) trip_count, trip_hour from trip_data\");\n", "df.head(10)\n" ], "outputs": [ { "output_type": "execute_result", "data": { "text/plain": [ " trip_count trip_hour\n", "0 1.0 16.0\n", "1 2.0 17.0\n", "2 3.0 18.0\n", "3 0.0 15.0\n", "4 0.0 21.0\n", "5 4.0 13.0\n", "6 0.0 1.0\n", "7 1.0 10.0\n", "8 0.0 2.0\n", "9 1.0 23.0" ], "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
trip_counttrip_hour
01.016.0
12.017.0
23.018.0
30.015.0
40.021.0
54.013.0
60.01.0
71.010.0
80.02.0
91.023.0
\n", "
" ] }, "metadata": {}, "execution_count": 10 } ], "metadata": {} }, { "cell_type": "code", "execution_count": 11, "source": [ "plt.scatter(df.trip_hour,df.trip_count)\n", "plt.ylabel('trip count (100x)')\n", "plt.xlabel('trip hour')\n", "plt.title('trip count vs hour of day scatter plot ')\n", "plt.grid(True)\n", "plt.tight_layout()" ], "outputs": [ { "output_type": "display_data", "data": { "text/plain": [ "
" ], "image/png": "iVBORw0KGgoAAAANSUhEUgAAAagAAAEYCAYAAAAJeGK1AAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuNCwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8QVMy6AAAACXBIWXMAAAsTAAALEwEAmpwYAAAo1UlEQVR4nO3de5hcVZnv8e/PEKAlgYhIa5pgEDWohMMlcplwtFvFIKLGzKgwQdFR4m0Uz0CUGB3AkUlm8qAyF50B5QEE016IAZUxcuQElOEiEKTRGBW5pQNBjIE0EyAJ7/lj704qlbrs6uqq2tX1+zxPP+lau9beb63e6bf3qlXvVkRgZmaWN89rdQBmZmalOEGZmVkuOUGZmVkuOUGZmVkuOUGZmVkuOUGZmVkuOUF1MEkHShqSNK7VseSJpF5Ja1sdRymSuiT9QNITkr6b4fm5fS15JOkySV9sdRyWcIIaoyQ9IOlNlZ4TEQ9FxISI2NasuOrhX7YA/BXQDbwwIt7V6mBGk6SQ9PKCx7n+eRfHa6PPCapDSdqt1TF0OiVq/T/4UuC3EbG1ETGNJT7H258T1Bgk6ZvAgcAP0im8T0uamv7F90FJDwE3FLTtlvZbKWmRpNvTKaRrJO1b4TjvkHS3pCcl3SfpxLR9sqRrJW2Q9HtJZxT02WkKpfiv5PTK72xJ96QxfFvSnpL2Av4LmJy+piFJk4viOVbSo4VTlpLeKeme9PujJd2Rxrte0peqjONZkh6T9IikDxS07yPpCkl/lPSgpM8NJxpJ50m6suC5pcb4Akk3A/8DvKzEcV+VPm+jpF9Jenvafj7w98B70tf/wRJ9u9Ix/rOkXwOvLdp+Tvqz2iTp15Lembbvkf68phc8d39JmyW9qMRxXi7pxvRn9Likbxdse42k69P9rZf02YLxvyV9XY9I+jdJu6fbbkq7/zJ9badT4uct6XkFr+FPkr4zfI6WOsdLxN0raa2kz6ZxPyBpbvHzCp5/RnoOb0jP6cll4n1PuX1YHSLCX2PwC3gAeFPB46lAAFcAewFdBW27pc9ZCQwCh6bPuRq4ssz+jwaeAE4g+UOnBzgk3XYj8FVgT+Bw4I/AG9NtlwFfLNhPL7C2KO7bgcnAvsBq4COlnlsmrvuAEwoefxc4J/3+FuC96fcTgGPL7KMX2Ap8ARgPnESSTF6Qbr8CuAaYmI7hb4EPptvOKxyzMmP8EPAaYDdgfNGxxwO/Bz4L7A68AdgETCu1/xKxLwZ+lo7dFODeovF9Vzq2zwPeAzwFvCTd9lXgnwqeeybwgzLHWQosTPezJ3B82j4ReAQ4K22fCByTbjsKODZ93VPTn+2nCvYZwMvLnRtp26eAW4EDgD2A/wSWljvHK/xsv5T2f306BsPjexnp+ZmO/ePAkelz/xW4qVy8/hr9L19BdZ7zIuKpiNhcZvs3I+LeiHgK+DzwbpVeRPFB4NKIuD4inouIwYj4jaQpwPHAZyLi6Yi4G/g68N4aYvyXiFgXERuAH5AkuayWAqcCSJpIklyWptu2AC+XtF9EDEXErRX2swX4QkRsiYjrgCFgWjoW7wEWRMSmiHgAuJDaXt9lEfGriNgaEVuKth1LkjwXR8SzEXED8MPh15TBu4ELImJDRDwM/Evhxoj4bjq2z0XEt4HfkfyxAXA58NfaMe34XuCbZY6zhWS6cXL6c/552n4y8GhEXJi2b4qI29Jj3xkRt6av+wGS5PL6jK9r2IeBhRGxNiKeIUnYf6Wdp/OqneMAn4+IZyLiRuBHJONWbC7JOX5XeqwFwHGSptYYs42QE1TnebiG7Q+S/EW/X4nnTSG5Wik2GdgQEZuK9tNTQ4yPFnz/PyS/sLP6FjBH0h7AHOCuiHgw3fZB4JXAbyT9QtLJFfbzp9j5fZ7hOPYjubJ5sGBbra+v0s9gMvBwRDw3wv1PZtef4XaS3qdkWnajpI0kV8v7AaSJ5Cng9ZIOAV4OXFvmOJ8GBNyeTkP+Tdpe7rxA0isl/TCdhn0S+EdKn1uVvBT4fkH8q4FtJAtHhlU7x/+c/gE27EGScSs2mYLxi4gh4E/U9rO2OjhBjV3lytRXK18/peD7A0n+Un68xPMeBg4u0b4O2De9eincz2D6/VPA8wu2vbhKPIWqlt6PiF+T/FJ5C/DXJAlreNvvIuJUYH/gn4DvKXlvqxaPs+PqYVitr6/S61gHTNHOiycK91/NI+z6MwRA0kuBS4C/JVkFOIlkClAFz78cOI3k6ul7EfF0qYNExKMRcUZETCa5qvmqkhVt5c4LgK8BvwFeERF7k0xjqsxzofQ4PQy8JSImFXztGRGDVfoVekHRz/1AknEvto6Cn3Pa54Vk/1lYnZygxq71lHgDPoPTJL1a0vNJ3oP5XpRehv4N4AOS3pi+cd0j6ZB0Wum/gUVKFjccRnLlclXa727gJEn7SnoxyXsKtbymF0rap8rzvgV8EngdyXtQAEg6TdKL0quTjWlzTUvs07H4DnCBpInpL/2/A4YXRtwNvE7JZ8z2IZkWqsXwVcynJY2X1Au8DejP2P87wAJJL5B0APCJgm17kfzy/iOAkoUfhxb1/ybwTpIkdUW5g0h6V7p/gD+n+91GMh35YkmfShdeTJR0TPq8icCTwFB6hfbRot0Wn7Olft7/QTL2L03jeJGkd5SLs4LzJe0u6X+TTEuW+kzZt0jO8cPTK/J/BG5LpydLxWujzAlq7FoEfC6dCjm7hn7fJHmj+FGSN7k/WepJEXE78AHgyySLJW5kx1+bp5K8Yb0O+D5wbkRcX7D/X5IshvgJsH31VzUR8RuS95P+kL6uUtMypM/pBW6IiMKrvxOBX0kaAi4CTil3hVDFJ0iSyB+An5P8Irs0jfF6ktd0D3AnyS/szCLiWeDtJFeAj5MsXHhf+tqzOJ/kCvJ+kvHd/h5SenV5IclikfXAdODmouOvBe4iSTg/q3Cc1wK3pWN5LXBmRNyfTu2eQJJUHyV5j6sv7XM2yVXtJpIrueKf/XnA5enP9t1lft4Xpcf7iaRNJAsmjqE2j5Ik1XUkfzh9pNT4RsRPSd6HvZrkyvRg4JRy8dYYg2WgCN+w0BKSVpKsEPt6q2Ox1pF0KbAuIj7X6lhGW3pFemVEHFDlqZYD/iCbmW2XrlCbAxzR4lDMPMVnZglJ/0CyaGJJRNzf6njMPMVnZma55CsoMzPLpbZ4D2q//faLqVOn1rWPp556ir32qvUjL53JY5WNxykbj1N2nTpWd9555+MRsUvNx7ZIUFOnTuWOO+6oax8rV66kt7d3dAIa4zxW2XicsvE4ZdepYyXpwVLtnuIzM7NccoIyM7NccoIyM7NccoIyM7NccoIyM7NcaliCknSpkttl31vU/glJa9J7yPxzo45vZp1p+apBZi6+gYPO+REzF9/A8lW+O0a7auQy88uAf6OgZL+kPuAdwGER8Yyk/Rt4fDPrMMtXDbJg2QCbtyR3URncuJkFywYAmH2E7zPYbhp2BRURNwEbipo/SnIr62fS5zzWqOObWedZsmLN9uQ0bPOWbSxZsaZFEVk9GlqLL62M/MOIODR9fDdwDcl9eZ4Gzo6IX5TpOw+YB9Dd3X1Uf3/W+7WVNjQ0xIQJtdw5vHN5rLLxOGXTzHEaGHyi7LbpPdXuc9l6nXpO9fX13RkRM4rbm11JYjfgBcCxJDc8+46kl0WJLBkRFwMXA8yYMSPq/XR1p35CeyQ8Vtl4nLJp5jgtXHwDgxs379LeM6mLT8xtTgz18Dm1s2av4lsLLIvE7cBzwH5NjsHMxqj5s6bRNX7cTm1d48cxf9a0FkVk9Wh2gloOvAFA0iuB3Ulua21mVrfZR/SwaM50eiZ1IZIrp0VzpnuBRJtq2BSfpKVAL7CfpLXAucClwKXp0vNngdNLTe+ZmY3U7CN6nJDGiIYlqIg4tcym0xp1TDMzGztcScLMzHLJCcrMzHLJCcrMzHLJCcrMzHLJCcrMzHLJCcrMzHLJCcrMzHLJCcrMzHLJCcrMzHLJCcrMzHLJCcrMzHLJCcrMzHLJCcrMzHLJCcrMzHLJCcrMzHLJCcrMzHKpkXfUvRQ4GXgsIg4t2nY2sAR4UUT4lu9mNmrmXnILN9+3YfvjmQfvy1VnHNfCiGykGnkFdRlwYnGjpCnACcBDDTy2mXWg4uQEcPN9G5h7yS0tisjq0bAEFRE3ARtKbPoy8GkgGnVsM+tMxcmpWrvlmyIalyckTQV+ODzFJ+ntwBsj4kxJDwAzyk3xSZoHzAPo7u4+qr+/v65YhoaGmDBhQl376BQeq2w8Ttk0c5wGBp8ou216zz5NiaEenXpO9fX13RkRM4rbG/YeVDFJzwcWAm/O8vyIuBi4GGDGjBnR29tb1/FXrlxJvfvoFB6rbDxO2TRznN5/zo/KbntgbnNiqIfPqZ01cxXfwcBBwC/Tq6cDgLskvbiJMZjZGDbz4H1rard8a1qCioiBiNg/IqZGxFRgLXBkRDzarBjMbGy76ozjdklGXsXXvhq5zHwp0AvsJ2ktcG5EfKNRxzMzA5yMxpCGJaiIOLXK9qmNOraZmbU/V5IwM7NccoIyM7NccoIyM7NccoIyM7NcatoHdc3MmmFqiQ/rPrD4rZn6Ll81yJIVa1i3cTOTJ3Uxf9Y0Zh/RM9ohWka+gjKzMaNUcqrUXmj5qkEWLBtgcONmAhjcuJkFywZYvmpwlKO0rJygzMyAJSvWsHnLtp3aNm/ZxpIVa1oUkTlBmZkB6zZurqndGs8JyswMmDypq6Z2azwnKDMzYP6saXSNH7dTW9f4ccyfNa1FEZkTlJmNGeVW62VZxTf7iB4WzZlOz6QuBPRM6mLRnOlexddCXmZuZmNK1iXlpcw+oscJKUd8BWVmZrnkBGVmZrnkBGVmZrnkBGVmZrnUyDvqXgqcDDwWEYembUuAtwHPAvcBH4iIjY2Kwca2E760kt899tT2x6/Yfy+u/7ve1gVkuVBPLb56uI7f6GvkFdRlwIlFbdcDh0bEYcBvgQUNPL6NYcXJCeB3jz3FCV9a2ZqALBfqqcVXD9fxa4yGJaiIuAnYUNT2k4jYmj68FTigUce3sa04OVVrN2sk1/FrDEVE43YuTQV+ODzFV7TtB8C3I+LKMn3nAfMAuru7j+rv768rlqGhISZMmFDXPjpFO4zVwOATZbdN79mnKTG0wzjlQTPHqVXnxWgdt1PPqb6+vjsjYkZxe0sSlKSFwAxgTmQIYMaMGXHHHXfUFcvKlSvp7e2tax+doh3GqtKUTTPeb4D2GKc8aOY4teq8mLn4BgZLFJXtmdTFzee8IfN+OvWcklQyQTV9FZ+k00kWT8zNkpzMSnnF/nvV1G7WSK7j1xhNTVCSTgQ+A7w9Iv6nmce2seX6v+vdJRl5FZ/VU4uvHq7j1xiNXGa+FOgF9pO0FjiXZNXeHsD1kgBujYiPNCoGG9ucjKyUZk3xFnMdv9HXsAQVEaeWaP5Go45nZmZjiytJmJlZLjlBmZlZLjlBmZlZLjlBmZlZLlVdJCFpf2AmMBnYDNwL3BERzzU4NrOKWlUU1PKtnvPCBV/zpewVlKQ+SSuAHwFvAV4CvBr4HDAg6XxJezcnTLOdtaooqOVbPeeFC77mT6UrqJOAMyLioeINknYjqQZxAnB1g2IzM2uaSgVffRXVGmUTVETMr7BtK7C8EQGZmbXCuhK19Cq1W+NVXSQh6ZuS9il4PFXSTxsblplZc02e1FVTuzVellV8Pwduk3SSpDOAnwBfaWhUZmZN5oKv+VM1QUXEfwIfAq4BvgC8LiJ+0OjAzCppVVFQy7d6zgsXfM2fLMvM3wt8HngfcBhwnaQPRMQvGx2cWSVORlZKPeeFC77mS5ZisX8JHB8RjwFLJX0fuAw4opGBmZlZZ6uaoCJidtHj2yUd07CIzMzMqJKgJM0CZgM9QADrgGsi4seND83MzDpZ2QQl6SvAK4ErgLVp8wHAJyW9JSLObHx4ZmbWqSpWkoiIVxY3Svo28FugYoKSdClJtYnHIuLQtG1f4NvAVOAB4N0R8ecRRW4dz7X4rJR6zgufU/lSaZn505KOLtH+WuDpDPu+DDixqO0c4KcR8Qrgp+ljs5q5Fp+VUs954XMqfypdQb0f+JqkieyY4psCPJluqygibpI0taj5HUBv+v3lwErgM1mDNTOzzqGIqPwE6cUkiyQErI2IRzPvPElQPyyY4tsYEZMKtv85Il5Qpu88YB5Ad3f3Uf39/VkPW9LQ0BATJkyoax+doh3GamDwibLbpvfsU3bbaGqHccqDZo5TPeeFz6nW6evruzMiZhS3V1vFJ+Cl7FjFN07S+qiW1UZBRFwMXAwwY8aM6O3trWt/K1eupN59dIp2GKv3V5h2eWBub1NiaIdxyoNmjlM954XPqfyptIrvzcBXgd8BwzdEOQB4uaSPRcRPRnC89ZJeEhGPSHoJ8NgI9mFmZh2g0iKJi4A3RcRbIuJD6deJJPeAumiEx7sWOD39/nSS+n5mNXMtPiulnvPC51T+VJri240diyMKDQLjq+1Y0lKSBRH7SVoLnAssBr4j6YPAQ8C7ag3YbJh/cVgp9ZwXPqfypVKCuhT4haR+4OG0bQpwCvCNajuOiFPLbHpjTRGamVlHqnRH3UWSlpMsDT+OdBUfMDcift2c8MzMrFNVXMUXEauB1U2KxczMbLssd9TdhaT/Gu1AzMzMClVaZn5kuU3A4Q2JxszMLFVpiu8XwI0kCanYpIZEY1YDF/bMt+WrBlmyYg2nTNnEwsU3MH/WtMx3qz3mgutZv+nZ7Y+7J+7ObQtPyNS3HYvF1jNWY1mlKb7VwIcjoq/4C3i8SfGZleTCnvm2fNUgC5YNMLhxMwCDGzezYNkAy1cNVum5a3ICWL/pWY654PqqfduxWGw9YzXWVUpQ51XY/onRD8XMxoolK9awecu2ndo2b9nGkhVrqvYtTk7V2ttdPWM11lVaZv69CtuWNyQaMxsT1qVXA1nbO5nHqryyV1CSTpNUafvBko5vTFhm1s4mT+qqqb2TeazKqzTF90JglaRLJX1c0rslvU/SFyTdCPwzsL45YZpZO5k/axpd48ft1NY1fhzzZ02r2rd74u41tbe7esZqrCuboCLiIuBIYCnwIpISRUeS1OJ7b0T8ZUT8rilRmhVxYc98m31ED4vmTKcnvQromdTFojnTM61Mu23hCbsko6yr+NqxWGw9YzXWVb1hYR7MmDEj7rjjjrr24fusZOexysbjlI3HKbtOHStJJW9YOKJKEmZmZo3mBGVmZrlUNUFJOihLm5mZ2WjKcgV1dYm2sp+RMjMzGw2VisUeArwG2EfSnIJNewN71nNQSf8H+BAQwADwgYh4up59WufptFp8w/Xa1m3czORJXTXVazvs3B/z5DM7qhXsvcc47jn/xKr9DjrnRxQuoxJwf4117c6avpX3p983oyZeq/rOveQWbr5vw/bHMw/el6vOOC5TXyut0hXUNOBkksKwbyv4OhI4Y6QHlNQDfBKYERGHAuNI7tJrllmn1eIrrNcW1FavrTg5ATz5zDYOO/fHFfsVJydI/qI8qMF17dqxb3FyArj5vg3MveSWqn2tvEqljq4BrpF0XESM9ijvBnRJ2gI8H1g3yvs3G1Mq1WurdhVVnJyqtQ8r9wGU/H8wpfmKk1O1dsum6uegJL2I5IppKgUJLSL+ZsQHlc4ELgA2Az+JiLklnjMPmAfQ3d19VH9//0gPB8DQ0BATJkyoax+doh3GamDwibLbpvfs05QYmjlO9bzekfYdrWN2d8H6grJyjYq3XfsWaof/e43Q19dX8nNQWRLUfwM/A+4Etv/JFRGlFk9UJekFJAsv3gNsBL4LfC8irizXxx/Uba52GKtK0y7Neh+qmeM0c/EN22/HUKhnUhc3n/OGin1HOlb1jHFh37Omb+XCgR2TNbX0ree47dK3UDv832uEej6o+/yI+ExEfCcirh7+qiOWNwH3R8QfI2ILsAz4izr2Zzbm1VOvbe89xtXUPqzUnUortXeymQfvW1O7ZZMlQf1Q0kmjeMyHgGMlPV+SSGr8rR7F/VsH6LRafIX12kRt9druOf/EXZJRllV89y9+6y7JKOsqvlbVxGtV36vOOG6XZORVfPXLMsW3CdgLeAbYQnKORkTsPeKDSueTTPFtBVYBH4qIZ8o931N8zeWxysbjlI3HKbtOHatyU3xlV/ENi4iJox1MRJwLnDva+zUzs7GjaoKS9LpS7RFx0+iHY2ZmlqiaoID5Bd/vCRxNsqKv8tIhMzOzOmSZ4ntb4WNJU0jupmtmZtYwI7ndxlrg0NEOxMzMrFCW96D+lR3VTZ4HHA78soExmWXSqqKgrTLSgq8w8tc7GmPcKcVi6+k7XAj4lCmbWLj4hpoKAddTRDjvslxB3UHyntOdwC3AZyLitIZGZVZFq4qCtspIC77CyF9vOxZtbce+hYWAobZCwPUUEW4HVRNURFwOLCVJUL8Ebm90UGa2s5EWfLX8q1QIuJF920GWKb5e4HLgAZIP6U6RdLqXmZuZ1W9diRqLldpHq287yDLFdyHw5oh4fUS8DpgFfLmxYZmZdYbJk7pqah+tvu0gS4IaHxHbrxcj4rfA+MaFZGbFRlrw1fKvnkLA9fRtB5kWSUj6hqTe9OsSkvejzFqmVUVBW2WkBV9h5K+3HYu2tmPfwkLAUFsh4HqKCLeDLMVi9wA+DhxP8h7UTcBXKxV3HW0uFttcHqtsPE7ZeJyy69SxGnGx2PQ5F0XEl9IdjQP2GOX4zMzMdpJliu+nQOE7bl3A/21MOGZmZoksCWrPiBgafpB+//zGhWRmZpYtQT0l6cjhB5KOAsbGInszM8utLO9BfQr4rqR16eOXkNwNd8QkTQK+TlJ0NoC/iYhb6tmndZ52rMV3yMLreHrbjoVJe44Tv7ngpEx9W/F6XYuvOX1fvuBHbI0dY7Wb4PeL8ruqtFmylDr6BXAI8FHgY8CrIqLeZeYXAT+OiEOA/wWsrnN/1mHasRZfcXICeHpbcMjC66r2bcXrbce6du3Ydzg5FdoaSXuny3IFRURsAe4djQNK2ht4HfD+dN/PAs+Oxr7N8qw4OVVrt85QnJyqtXeSqp+DGvUDSocDFwO/Jrl6uhM4MyKeKnrePGAeQHd391H9/f11HXdoaIgJEybUtY9O0Q5jNTD4RNlt03v2aVjfQrWOU6tiHmnf0TpmdxesL3jXOo+vNS99ax2rsaKvr6/k56BakaBmALcCMyPiNkkXAU9GxOfL9fEHdZurHcaq0tRJtXn/evoWqnWcWhXzSPuO1jHPmr6VCwd2TNbk8bXmpW+tYzVW1PNBXSTNIakkEcDPI+L7dcSyFlgbEbelj78HnFPH/szawp7jVHI6b89xakE0lhe7qfR03m4+LaovkpD0VeAjwADJ+1AflvTvIz1gRDwKPCxpuJrhG0mm+8wya8dafL+54KRdklHWVXyteL3tWNeuHfv+ftFbd0lGXsWXyFKL71fAoZE+UdLzgIGIeM2ID5q8D/V1YHfgD8AHIuLP5Z7vKb7m8lhl43HKxuOUXaeOVT1TfGuAA4EH08dTgHvqCSYi7gZ2CcbMzGxYlgT1QmC1pOFbvb8WuEXStQAR8fZGBWdmZp0rS4L6+4ZHYWZmVqRqgoqIG5sRiJmZWaGyCUrSzyPieEmbSJaXb98ERETs3fDozCpoVd205asGWbJiDadM2cTCxTcwf9a0zHcwbbc6ca7F19y+IxmruZfcws33bdj+eObB+3LVGcdl6pt3ZZeZR8Tx6b8TI2Lvgq+JTk7Waq2qm7Z81SALlg0wuDH5uP/gxs0sWDbA8lWDDT2ua/G5bynFyQng5vs2MPeSsVF7u+LnoCQ9T9Ko1OAzGwuWrFjD5i3bdmrbvGUbS1asaVFE1smKk1O19nZTMUFFxHPALyUd2KR4zHJt3cbSt0Ir125mI5dlFd9LgF+ly8y3F3T18nLrRJMndW2f3ituN7PRlSVBnd/wKMzaxPxZ01iwbGCnab6u8eOYP2tahV5mjTHz4H1LTufNPHjfFkQz+rLc8v2kiLix8AvIdgtQswZpVd202Uf0sGjOdHrSK6aeSV0smjM90yq+dqsT127xdmLfq844bpdkNJZW8WWpxXdXRBxZ1HZPRBzW0MgKuBZfc3mssvE4ZeNxyq5Tx6rmWnyShm/x/jJJhbX3JgI3j36IZmZmO1R6D+pbwH8Bi9j5fk2bImJsrGE0M7PcKpugIuIJ4Ang1OaFY2ZmlsiySMLMzKzpnKDMzCyXsnwOqiEkjQPuAAYj4uRWxWGt1erinPX07YQiqB6nsX1O5b3QbCuvoM4EVrfw+NZi7Vics5P6tlu87ltb33YoNNuSBCXpAOCtwNdbcXwzs07XDoVmq35QtyEHlb5Hsnx9InB2qSk+SfOAeQDd3d1H9ff313XMoaEhJkyYUNc+OkWzxmpg8Imy26b37JP7vt1dsL6gLF87xFxLX49T8/u2y1iNtr6+vpIf1G16gpJ0Mkn5pI9J6qVMgirkShLN1ayxqjQNUW0OPQ99z5q+lQsHdryN2w4x19LX49T8vu0yVqOtXCWJVkzxzQTeLukBoB94g6QrWxCHmVnHKldQNk+FZpueoCJiQUQcEBFTgVOAGyLitGbHYa3XjsU5O6lvu8XrvrX1bYdCsy15D2r7wT3Fl0seq2w8Ttl4nLLr1LGquVhsM0TESmBlK2MwM7N8ciUJMzPLJScoMzPLJScoMzPLpZa+B2XW6tpn9fTthBpzHiefU6Ucc8H1rN/07PbH3RN357aFJ2Q6Zi18BWUt0471yzqpb7vF677N6VucnADWb3qWYy64vuoxa+UEZWZmmRUnp2rt9XCCMjOzXHKCMjOzXHKCMjOzzLon7l5Tez2coKxl2rF+WSf1bbd43bc5fW9beMIuyahRq/haWosvK9fiay6PVTYep2w8Ttl16ljl6XYbZmZmVTlBmZlZLjlBmZlZLjlBmZlZLjlBmZlZLjW9WKykKcAVwIuB54CLI+KiRh3vc8sHWHrbw3zq0C18cMF1nHrMFL44e3qmvstXDbJkxRrWbdzM5EldzJ81jdlH9DQq1I7U6uKc9fRtl8Ke9fT1OPmcGu1j1qIVV1BbgbMi4lXAscDHJb26EQf63PIBrrz1IbalS+m3RXDlrQ/xueUDVfsuXzXIgmUDDG7cTACDGzezYNkAy1cNNiLUjtRuRTI7rW+7xeu+zelbzzFr1fQEFRGPRMRd6febgNVAQy5Llt72cE3thZasWMPmLdt2atu8ZRtLVqwZldjMzKyyln5QV9JU4Cbg0Ih4smjbPGAeQHd391H9/f01739g8Int33d3wfrNO7ZN79knc99i1fq2u6GhISZMmNDw49Qzxnno28xzqhV9PU7N79sOY9WI3419fX0lP6jbsgQlaQJwI3BBRCyr9NyRVpI4eMF126f3zpq+lQsHkrfcxknct+ikin1nLr6BwY2bd2nvmdTFzee8oeZY2kmzPs1eaUqg2nx2HvoWnlPNPG6z+nqcmt+3HcaqnmOWk6tKEpLGA1cDV1VLTvU49ZgpNbUXmj9rGl3jx+3U1jV+HPNnTRuV2MzMrLKmJyhJAr4BrI6ILzXyWF+cPZ3Tjj2QcRKQXDmdduyBmVbxzT6ih0VzptMzqQuRXDktmjPdq/hGUbsVyey0vu0Wr/s2p289x6xV06f4JB0P/AwYIFlmDvDZiLiuXB8Xi20uj1U2HqdsPE7ZdepYlZvia/rnoCLi54CafVwzM2svriRhZma55ARlZma55ARlZma51PT3oJqt3WpcQfvVAGx1/bJW9W2nc8q1+Nqjb7uM1Viuxdc07VbjCtqvBmA7jrH7ZuvbbvG6b3P6julafFaZawCamSWcoHJmXYnySpXazczGKieonJk8qaumdjOzscoJKmdcA9DMLDGmE1S71biC9qsB2I5j7L7Z+rZbvO7bnL5juhbfSLgWX3N5rLLxOGXjccquU8cqV7fbMDMzq8YJyszMcskJyszMcskJyszMcskJyszMcqklq/gknQhcBIwDvh4Riys9v55VfIVFGC8cSGrj5rkIYx761jpWrY63VX074ZzyOPmcGu1jlpKbVXySxgH/DrwFeDVwqqRXN+JY7VaEsR37tlu87pu9b7vF677N6TvWi8UeDfw+Iv4QEc8C/cA7WhCHmZnlWNOn+CT9FXBiRHwoffxe4JiI+Nui580D5gF0d3cf1d/fX/OxBgaf2P59dxesL6i3Or1nn8x9i431vrWMVR7ibVXfsX5OeZya37cdxqqeY5bT19dXcoqvFQnqXcCsogR1dER8olyfkb4HVXjJWTi3C9XnSytdro71vrWMVR7ibVXfsX5OeZya37cdxqqeY5aTm/eggLXAlILHBwDrWhCHmZnlWCsS1C+AV0g6SNLuwCnAtY04ULsVYWzHvu0Wr/tm79tu8bpvc/qO+WKxkk4CvkKyzPzSiLig0vNdLLa5PFbZeJyy8Thl16ljVW6Kb7dST260iLgOuK4VxzYzs/bgShJmZpZLTlBmZpZLTlBmZpZLTlBmZpZLbXHLd0l/BB6sczf7AY+PQjidwGOVjccpG49Tdp06Vi+NiBcVN7ZFghoNku4otYzRduWxysbjlI3HKTuP1c48xWdmZrnkBGVmZrnUSQnq4lYH0EY8Vtl4nLLxOGXnsSrQMe9BmZlZe+mkKygzM2sjTlBmZpZLHZGgJJ0oaY2k30s6p9Xx5JWkByQNSLpbUn3l48cYSZdKekzSvQVt+0q6XtLv0n9f0MoY86DMOJ0naTA9r+5O72bQ0SRNkfT/JK2W9CtJZ6btPqcKjPkEJWkc8O/AW4BXA6dKenVro8q1vog43J/F2MVlwIlFbecAP42IVwA/TR93usvYdZwAvpyeV4endzPodFuBsyLiVcCxwMfT30s+pwqM+QQFHA38PiL+EBHPAv3AO1ock7WZiLgJ2FDU/A7g8vT7y4HZzYwpj8qMkxWJiEci4q70+03AaqAHn1M76YQE1QM8XPB4bdpmuwrgJ5LulDSv1cG0ge6IeASSXzjA/i2OJ8/+VtI96RRgR09bFZM0FTgCuA2fUzvphASlEm1eW1/azIg4kmQ69OOSXtfqgGxM+BpwMHA48AhwYUujyRFJE4CrgU9FxJOtjidvOiFBrQWmFDw+AFjXolhyLSLWpf8+BnyfZHrUylsv6SUA6b+PtTieXIqI9RGxLSKeAy7B5xUAksaTJKerImJZ2uxzqkAnJKhfAK+QdJCk3YFTgGtbHFPuSNpL0sTh74E3A/dW7tXxrgVOT78/HbimhbHk1vAv3NQ78XmFJAHfAFZHxJcKNvmcKtARlSTSZa1fAcYBl0bEBa2NKH8kvYzkqglgN+BbHqcdJC0Fekluh7AeOBdYDnwHOBB4CHhXRHT0AoEy49RLMr0XwAPAh4ffZ+lUko4HfgYMAM+lzZ8leR/K51SqIxKUmZm1n06Y4jMzszbkBGVmZrnkBGVmZrnkBGVmZrnkBGVmZrnkBGVWB0mTJH2synP+u8Z9PiBpv/oiM2t/TlBm9ZkElExQaSV9IuIvmhlQmVgkyf/fra34hDWrz2Lg4PQ+R0sk9ab3+fkWyYcwkTSU/tsr6SZJ35f0a0n/USFpfELSXen9uQ5J++8raXladPVWSYel7edJOnu4o6R7JU1Nv1ZL+ipwFzuX/DLLPScos/qcA9yX3udoftp2NLAwIkrdd+xo4CxgOkkB1Tll9vt4Wrj3a8Bw8jkfWBURh5FUHbgiQ3zTgCsi4oiIeDDTKzLLCScos9F3e0TcX2HbHyJiG7AUOL7M84aLh94JTE2/Px74JkBE3AC8UNI+VWJ5MCJuzRy5WY44QZmNvqcqbCuuLVau1tgz6b/bSGojQvlbx2xl5//Le2aMxSzXnKDM6rMJmFjD849OK+s/D3gP8PMa+t4EzIXk/SySacAnSQqwHpm2HwkcVMM+zXLLCcqsDhHxJ+DmdGHCkgxdbiFZWHEvcD87KshncR4wQ9I96T6Gb8twNbCvpLuBjwK/rWGfZrnlauZmTZJe9ZwdESe3OBSztuArKDMzyyVfQZmZWS75CsrMzHLJCcrMzHLJCcrMzHLJCcrMzHLJCcrMzHLp/wPJnyFxHYZe3AAAAABJRU5ErkJggg==" }, "metadata": { "needs_background": "light" } } ], "metadata": {} }, { "cell_type": "markdown", "source": [ "### Run Create Model statement to create a new ML model with REdshift ML" ], "metadata": {} }, { "cell_type": "code", "execution_count": 14, "source": [ "create_model_command = \"\"\"\n", "CREATE MODEL predict_rental_count \n", "FROM (SELECT\n", " trip_count,trip_hour,trip_day,trip_month,trip_year,trip_quarter,\n", " trip_month_week, trip_week_day, temp_c, precip_amount_mm, is_holiday\n", "FROM trip_data)\n", "TARGET trip_count\n", "FUNCTION predict_rental_count\n", "IAM_ROLE '{}'\n", "PROBLEM_TYPE regression\n", "OBJECTIVE 'mse'\n", "SETTINGS (s3_bucket '{}')\n", "\"\"\"\n", "\n" ], "outputs": [], "metadata": {} }, { "cell_type": "code", "execution_count": 16, "source": [ "run_sql( create_model_command.format(REDSHIFT_IAM_ROLE,S3_BUCKET))\n" ], "outputs": [ { "output_type": "execute_result", "data": { "text/plain": [ "'b41735d3-8e7d-4c86-abb7-f0b8ef07fa1d:FINISHED'" ] }, "metadata": {}, "execution_count": 16 } ], "metadata": {} }, { "cell_type": "markdown", "source": [ "### View Model Progress " ], "metadata": {} }, { "cell_type": "code", "execution_count": 19, "source": [ "df = run_sql(\"SHOW MODEL predict_rental_count\")\n", "df" ], "outputs": [ { "output_type": "execute_result", "data": { "text/plain": [ " Key \\\n", "0 Model Name \n", "1 Schema Name \n", "2 Owner \n", "3 Creation Time \n", "4 Model State \n", "5 \n", "6 TRAINING DATA: \n", "7 Query \n", "8 \n", "9 Target Column \n", "10 \n", "11 PARAMETERS: \n", "12 Model Type \n", "13 Problem Type \n", "14 Objective \n", "15 AutoML Job Name \n", "16 Function Name \n", "17 Function Parameters \n", "18 Function Parameter Types \n", "19 IAM Role \n", "20 S3 Bucket \n", "21 Max Runtime \n", "\n", " Value \n", "0 predict_rental_count \n", "1 public \n", "2 demo \n", "3 Thu, 05.08.2021 21:50:55 \n", "4 TRAINING \n", "5 \n", "6 \n", "7 SELECT TRIP_COUNT,TRIP_HOUR,TRIP_DAY,TRIP_MONT... \n", "8 FROM TRIP_DATA \n", "9 TRIP_COUNT \n", "10 \n", "11 \n", "12 auto \n", "13 Regression \n", "14 MSE \n", "15 2021-08-05-21-50-55-798247-auto \n", "16 predict_rental_count \n", "17 trip_hour trip_day trip_month trip_year trip_q... \n", "18 int4 int4 int4 int4 int4 int4 int4 numeric num... \n", "19 arn:aws:iam::709512860261:role/redshiftml-12-R... \n", "20 redshiftml-12-redshiftmlbucket-1mxb3ll9jpxz9 \n", "21 5400 " ], "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
KeyValue
0Model Namepredict_rental_count
1Schema Namepublic
2Ownerdemo
3Creation TimeThu, 05.08.2021 21:50:55
4Model StateTRAINING
5
6TRAINING DATA:
7QuerySELECT TRIP_COUNT,TRIP_HOUR,TRIP_DAY,TRIP_MONT...
8FROM TRIP_DATA
9Target ColumnTRIP_COUNT
10
11PARAMETERS:
12Model Typeauto
13Problem TypeRegression
14ObjectiveMSE
15AutoML Job Name2021-08-05-21-50-55-798247-auto
16Function Namepredict_rental_count
17Function Parameterstrip_hour trip_day trip_month trip_year trip_q...
18Function Parameter Typesint4 int4 int4 int4 int4 int4 int4 numeric num...
19IAM Rolearn:aws:iam::709512860261:role/redshiftml-12-R...
20S3 Bucketredshiftml-12-redshiftmlbucket-1mxb3ll9jpxz9
21Max Runtime5400
\n", "
" ] }, "metadata": {}, "execution_count": 19 } ], "metadata": {} }, { "cell_type": "markdown", "source": [ "### Run Prediction and compare actual vs predicted\n" ], "metadata": {} }, { "cell_type": "code", "execution_count": 18, "source": [ "df = run_sql(\"\"\"\n", "SELECT trip_time, actual_count, predicted_count, ( actual_count - predicted_count ) difference\n", "FROM \n", "(SELECT\n", " trip_time\n", ",trip_count AS actual_count\n", ",PREDICT_RENTAL_COUNT (trip_hour, trip_day, trip_month, trip_year, trip_quarter, trip_month_week, trip_week_day, temp_c, precip_amount_mm, is_holiday) predicted_count\n", "FROM trip_data\n", ") LIMIT 5;\n", "\"\"\")\n", "\n", "df\n" ], "outputs": [ { "output_type": "error", "ename": "Exception", "evalue": "SQL query failed:f026d0e4-1544-4b5b-9658-b6c331aec601: ERROR: The ML model is not ready, check STV_ML_MODEL_INFO for its status.\n Detail: \n -----------------------------------------------\n error: The ML model is not ready, check STV_ML_MODEL_INFO for its status.\n code: 28008\n context: \n query: 21736\n location: cg_expr_fn_builder.cpp:2724\n process: padbmaster [pid=25303]\n -----------------------------------------------\n", "traceback": [ "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[0;31mException\u001b[0m Traceback (most recent call last)", "\u001b[0;32m\u001b[0m in \u001b[0;36m\u001b[0;34m\u001b[0m\n\u001b[1;32m 8\u001b[0m \u001b[0mFROM\u001b[0m \u001b[0mtrip_data\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 9\u001b[0m ) LIMIT 5;\n\u001b[0;32m---> 10\u001b[0;31m \"\"\");\n\u001b[0m\u001b[1;32m 11\u001b[0m \u001b[0mdf\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n", "\u001b[0;32m\u001b[0m in \u001b[0;36mrun_sql\u001b[0;34m(sql_text)\u001b[0m\n\u001b[1;32m 20\u001b[0m \u001b[0mstatus\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mstatus_description\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"Status\"\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 21\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mstatus\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;34m\"FAILED\"\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 22\u001b[0;31m \u001b[0;32mraise\u001b[0m \u001b[0mException\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'SQL query failed:'\u001b[0m \u001b[0;34m+\u001b[0m \u001b[0mquery_id\u001b[0m \u001b[0;34m+\u001b[0m \u001b[0;34m\": \"\u001b[0m \u001b[0;34m+\u001b[0m \u001b[0mstatus_description\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"Error\"\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 23\u001b[0m \u001b[0;32melif\u001b[0m \u001b[0mstatus\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;34m\"FINISHED\"\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 24\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mstatus_description\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'ResultRows'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m>\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n", "\u001b[0;31mException\u001b[0m: SQL query failed:f026d0e4-1544-4b5b-9658-b6c331aec601: ERROR: The ML model is not ready, check STV_ML_MODEL_INFO for its status.\n Detail: \n -----------------------------------------------\n error: The ML model is not ready, check STV_ML_MODEL_INFO for its status.\n code: 28008\n context: \n query: 21736\n location: cg_expr_fn_builder.cpp:2724\n process: padbmaster [pid=25303]\n -----------------------------------------------\n" ] } ], "metadata": {} }, { "cell_type": "code", "execution_count": null, "source": [], "outputs": [], "metadata": {} } ], "metadata": { "kernelspec": { "display_name": "conda_python3", "language": "python", "name": "conda_python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.6.13" } }, "nbformat": 4, "nbformat_minor": 4 }