<jupyter_start><jupyter_text>Input Redshift Cluster Endpoint and UserPlease input your Amazon Redshift Cluster endpoint and existing database user<jupyter_code>REDSHIFT_ENDPOINT = 'redshift-cluster.xxxxxxxxxx.us-east-1.redshift.amazonaws.com:5439/dev'
REDSHIFT_USER="awsuser"<jupyter_output><empty_output><jupyter_text>Setup Run SQL function using Redshift Data API to get SQL query output directly into pandas dataframeIn 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<jupyter_code>import boto3
import time
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

session = boto3.session.Session()
region = session.region_name


def run_sql(sql_text):
    client = boto3.client("redshift-data")
    res = client.execute_statement(Database=REDSHIFT_ENDPOINT.split('/')[1], DbUser=REDSHIFT_USER, Sql=sql_text,
                                   ClusterIdentifier=REDSHIFT_ENDPOINT.split('.')[0])
    query_id = res["Id"]
    while True:
        time.sleep(1)
        status_description = client.describe_statement(Id=query_id)
        status = status_description["Status"]
        if status == "FAILED":
            raise Exception('SQL query failed:' + query_id + ": " + status_description["Error"])
        elif status == "FINISHED":
            if status_description['ResultRows']>0:
                results = client.get_statement_result(Id=query_id)
                column_labels = []
                for i in range(len(results["ColumnMetadata"])): column_labels.append(results["ColumnMetadata"][i]['label'])
                records = []
                for record in results.get('Records'):
                    records.append([list(rec.values())[0] for rec in record])
                df = pd.DataFrame(np.array(records), columns=column_labels)
                return df
            else:
                return query_id<jupyter_output><empty_output><jupyter_text>Create User and Grant Permissions - OptionalAs the database adminstrator, you may skip the permissions section. Otherwise, you can create users and grant them permissions with the Principle of Least Privilege in mind.  If demouser exists with privilege, please revoke before dropping the user:```sqlrevoke all on schema demo_ml from demouser;```<jupyter_code>permissions_one_sql = """

DROP USER IF EXISTS demouser;

create user demouser with password '<password>';

GRANT CREATE MODEL TO demouser;

"""

for sql_text in permissions_one_sql.split(";")[:-1]:
    run_sql(sql_text);<jupyter_output><empty_output><jupyter_text>Data Preparation Data preparation script to be ran on Amazon Redshift**Note**: Please change `` to your AWS Account Id down in the script below<jupyter_code>setup_script="""

DROP SCHEMA IF EXISTS DEMO_ML CASCADE;

CREATE SCHEMA DEMO_ML;

DROP TABLE IF EXISTS demo_ml.customer_activity;

CREATE TABLE demo_ml.customer_activity (
state varchar(2), 
account_length int, 
area_code int,
phone varchar(8), 
intl_plan varchar(3), 
vMail_plan varchar(3),
vMail_message int, 
day_mins float, 
day_calls int, 
day_charge float,
total_charge float,
eve_mins float, 
eve_calls int, 
eve_charge float, 
night_mins float,
night_calls int, 
night_charge float, 
intl_mins float, 
intl_calls int,
intl_charge float, 
cust_serv_calls int, 
churn varchar(6),
record_date date);

COPY DEMO_ML.customer_activity 
FROM 's3://redshift-downloads/redshift-ml/customer_activity/' 
IAM_ROLE 'arn:aws:iam::<accountId>:role/RedshiftML' IGNOREHEADER 1 CSV
region 'us-east-1';
"""<jupyter_output><empty_output><jupyter_text>Run data preparation script in Amazon Redshift<jupyter_code>for sql_text in setup_script.strip().split(";")[:-1]:
    run_sql(sql_text);<jupyter_output><empty_output><jupyter_text>Granting Permissions - OptionalCreate demo userGrant create model permissions to `demouser`<jupyter_code>permissions_two_sql = """

GRANT SELECT on demo_ml.customer_activity TO demouser;

GRANT CREATE, USAGE ON SCHEMA demo_ml TO demouser;


"""

for sql_text in permissions_two_sql.split(";")[:-1]:
    run_sql(sql_text);<jupyter_output><empty_output><jupyter_text>Read SQL output from Pandas Dataframe<jupyter_code>df = run_sql("SELECT * FROM demo_ml.customer_activity;");
df.head(10)
df.describe()<jupyter_output><empty_output><jupyter_text>Run Create Model statement to create a new ML model with Redshift MLPlease replace `` with your AWS account Id<jupyter_code>resp = run_sql("""
CREATE MODEL demo_ml.customer_churn_model
  FROM (SELECT state,
               area_code,
               total_charge/account_length AS average_daily_spend, 
               cust_serv_calls/account_length AS average_daily_cases,
               churn 
          FROM demo_ml.customer_activity
         WHERE record_date < '2020-01-01'
     )
  TARGET churn
FUNCTION predict_customer_churn
IAM_ROLE 'arn:aws:iam::<accountId>:role/RedshiftML'
SETTINGS (
  S3_BUCKET 'redshiftml-<accountId>'
)
;
""")

resp<jupyter_output><empty_output><jupyter_text>Check the status on your ML model You can check the status of your models by running the `SHOW MODEL` command from your SQL prompt.Continuously check `Model State` and once it has been set to `Ready`, continue to the next step.<jupyter_code>df = run_sql('SHOW MODEL demo_ml.customer_churn_model;')

df.head(10)<jupyter_output><empty_output><jupyter_text>Evaluate your model performance<jupyter_code>df = run_sql("""
WITH infer_data AS (
  SELECT area_code || phone accountid, churn,
    demo_ml.predict_customer_churn( 
          state,
          area_code, 
          total_charge/account_length , 
          cust_serv_calls/account_length ) AS predicted
  FROM demo_ml.customer_activity
 WHERE record_date <  '2020-01-01'
)
SELECT * FROM infer_data where churn!=predicted;
""")
df<jupyter_output><empty_output><jupyter_text>EvaluationYou can see the F1 value for the example model customer_churn_model in the output of the `SHOW MODEL` command. The F1 amount signifies the statistical measure of the precision and recall of all the classes in the model. The value ranges between 0–1; the higher the score, the better the accuracy of the model.<jupyter_code>df = run_sql('SHOW MODEL demo_ml.customer_churn_model;')
df.head(10)<jupyter_output><empty_output><jupyter_text>Invoke your ML model for inferenceYou can use your SQL function to apply the ML model to your data in queries, reports, and dashboards. For example, you can run the predict_customer_churn SQL function on new customer data in Amazon Redshift regularly to predict customers at risk of churning and feed this information to sales and marketing teams so they can take preemptive actions, such as sending these customers an offer designed to retain them.For example, you can run the following query to predict which customers in area code 408 might churn and the output shows the account ID and whether the account is predicted to remain active:<jupyter_code>df = run_sql("""
SELECT area_code || phone accountid, 
       demo_ml.predict_customer_churn( 
          state,
          area_code, 
          total_charge/account_length , 
          cust_serv_calls/account_length )
          AS "predictedActive"
  FROM demo_ml.customer_activity
 WHERE area_code='408' and record_date > '2020-01-01';
""")

df<jupyter_output><empty_output><jupyter_text>Granting Permissions - OptionalThe following code grants the EXECUTE privilege to users such as your marketing_analyst_grp<jupyter_code>df = run_sql('GRANT EXECUTE demo_ml.predict_customer_churn TO marketing_analyst_grp')<jupyter_output><empty_output><jupyter_text>Cost Control If the `SELECT` query of `CREATE MODEL` produces 10,000 records for training and each record has five columns, the number of cells in the training data is 50,000. You can control the training cost by setting the `MAX_CELLS`.Please replace `` with your AWS account Id<jupyter_code>df = run_sql("""
CREATE MODEL demo_ml.customer_churn_model
FROM (SELECT state,
             area_code,
             total_charge/account_length AS average_daily_spend, 
             cust_serv_calls/account_length AS average_daily_cases,
             churn 
      FROM demo_ml.customer_activity
      WHERE account_length > 120 
     )
TARGET churn
FUNCTION predict_customer_churn
IAM_ROLE 'arn:aws:iam::<acountId>:role/RedshiftML'
SETTINGS (
  S3_BUCKET 'redshiftml_<accountId>',
   MAX_CELLS 10000
)
;
""")<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup