Democratizing Data Access and Machine Learning with Amazon Redshift ML - AWS Online Tech Talks

Key Takeaways

hello everyone and welcome to our webinar today we're going to be talking about transforming your business with machine learning through amazon redshiftml i'm chris celeski i'm in charge of product marketing for amazon redshift and i'm co-presenting with my colleague dilip kikla who's a global analytics sales specialist today we're going to briefly cover amazon redshift but we're mostly going to talk about machine learning and how it can transform your business we'll get into some of the use cases and a demo that most of you will recognize as as a common use case before we get into the actual content i wanted to give a brief customer anecdote about a fascinating company called magellan they have a vision of bridging the gap between scientists in manufacturing in the area of pharmaceuticals and biotechnology their mission is to deliver the most effective inefficient pharmaceutical solutions to their customers and they rely very heavily on machine learning predictive analytics it's central to their business because they needed to do things like forecast where drug costs are going and prices are going uh to identify where the market is going and what what drugs will be in demand and also to forecast how patients will respond they faced a very common problem that we see which is their machine learning process was very iterative it was time consuming it was costly and also prone to human error their data scientists had to do a lot of manual operations they had to move the data they had to transform it they had to build custom integrations they had to do things like grant permissions uh controls and magellan had had a goal of improving their operational costs while not sacrificing the clinical accuracy which is all important to their their business redshift ml turned out to be the ideal solution to them because uh through our simplified model flow in automation they were able to avoid all of those manual processes and actually achieve their goals by reducing up operational costs by 20 percent without sacrificing any of the clinical accuracy this example is very inspirational to me because uh you know magellan and companies like it are developing developing products that improve all of our lives and in the lives of our families as well amazon redshift is our cloud data warehouse and it's foundational to machine learning for a couple of reasons number one you can query data really fast which is required for real-time predictive analytics in number two you you avoid moving and transforming your data through automation you avoid a lot of the manual tests such as those that impacted magellan amazon redshift integrates with other aws services for an end-to-end machine learning solution you'll learn more about that in dilla's presentation as well as the demo amazon redshift is the price performance leader and in fact it's up to 3x better than other cloud data warehouses in that all-important metric and using our advanced query accelerator you can accelerate your queries budget 10x and as your data sets grow from terabytes to petabytes performance is consistent consistency is a tenet of performance to us amazon redshift has many features that just work out of the box including automation so we have automated workload management and table optimization so no tuning is needed to get the best performance amazon redshift is very friendly to developers as well because you can integrate semi-structured data such as web data and iot data and you can easily access the data through our data api there are many personas across the organization that use aws analytics there's the data scientists in the case of of of magellan but also developers um data analysts bi analysts business intelligence analytics you use it for their reports to forecast things like profits revenue costs there are a lot of use cases for machine learning in a data warehouse we're going to cover one of them into today in detail in the demo which is very common which is predicting customer returns something every business deals with uh predicting sales cycles and whether a deal will close also customer lifetime value predictions this is something that leading companies increasingly are doing um to forecast how their their solutions are going to perform and product recommendations based on a number of variables there's market conditions there's um customer demand there's the competitive environment and and so on and of course revenue and pr price predictions and there will be new use cases as it becomes common in the the industry some of the benefits of machine learning are most obviously in the case of magellan to improve the the business operations to improve operational costs also to transform the customer experience by having more personalization of web experiences web ads and so on uh there's faster decision making and also innovating on a product or or a service so the problem with the traditional machine learning workflow is that they're very complex and iterative um and you know as we saw in the the the example um it takes a lot of time to prepare the models um to actually build choose an algorithm and build the models then you have to train and tune them there's management involved you have to debug the the the models and manage different environments and then there's the deployment phase where you have to deploy the models in in production validate all of your predictions monitor them and and scale them to to to production environments we sought simply to simplify this through a simplified model flow and also to make it accessible to the various organizational personas not just the data scientists not just the i.t administrator but to the other personas as well so some of the requirements are from these various users that the data scientists obviously need to perform machine learning training and prediction with within their their data warehouses um and they need to use pretend uh pre-trained models the business intelligence professionals rely on it for their predictions for their dashboards for their business reports and also the data analysts and developers who also need to train the models to make predictions if they don't want to learn a lot of new processes rely on external models they want something that works out of the box that's integrated with with other services and with that i'll hand it over to my colleague dilla who will do a little bit of a deep dive into amazon redshift ml thank you chris our mission is to take our rich experience and expertise with machine learning and work with tens of thousands of customers to address these challenges and do that by putting this powerful technology in the hands of every developer data scientist researcher basically anyone who knows how to write sql we want to make it easy for all builders to easily build intelligent applications in other words we want to simplify machine learning let me introduce you to machine learning that just simplifies ml using sql statements a user just interfaces with amazon redshift using their favorite tool they would run the sql command to create the model behind the scenes amazon redshift ml securely exports the specified data from amazon redshift to amazon s3 and calls sagemaker autopilot to automatically prepare the data select the appropriate pre-built algorithms and apply the algorithm for the training model amazon redshift ml handles all the interactions between the amazon redshift amazon s3 and sagemaker abstracting the steps involved in training and compilation after the model is trained it's deployed in amazon redshift amazon reshift ml makes this model available as a sql function in amazon reshift data warehouse the user would then use this function in the sql queries and perform predictions now that you know about how amazon reshift ml works let me talk to you a little bit about its capabilities that help our customers in easily adopting this particular feature the first and the foremost it's simple to use users can create models train models make predictions using simple sql queries users do not have to learn external tools or any of the machine learning languages and we'll see that in a demo too why simple to use it does not compromise on flexibility advanced users who know about machine learning can extend that same sql query to influence the training they can specify a problem type an algorithm such as xg boost specified tuning hyper parameters and preprocessors they prefer to be used in either case the execution of the steps is fully automatic without any user input the preprocessing of the data creation of the model training tuning the model and deployment of the model are carried out automatically the inference or prediction algorithms run inside redshift as a result the algorithms leverage the power of redshift massively parallel processing architecture to run machine learning in the data warehouse in the moment manner now throughout the model creation training and deployment processes the customer data remains inside their own vpc this enables customers to securely generate predictions equal using sql without having to ship their data outside of their vpc and this is one of the points that our customers were really concerned about the other fact uh factor they were concerned about was cost which is again a big factor for our customers now breaking down our customers machine learning costs typically predictions or inferences drive up the cost in production environments now this makes sense where customers train a model maybe once or twice a week they could be running inferences on it thousands if not millions of times in the same period to minimize overall machine learning costs with amazon redshift customers would not pay anything extra for predictions or inferences as predictions happen locally in the data warehouse only the model training costs which are about 10 percent of the overall machine learning costs are additional and there too cost controls can be set through parameters like max sales and max runtimes during training moving ahead now let's dive deep into the workings of the redshift machine learning there are three different ways users can leverage reshift and sagemaker for machine learning and i'll cover them in the next few slides the first way to create a machine learning model as a data analyst you use a simple sql create model statement in that you would specify the data in amazon redshift you want to use as the data input to train your model and the output you want to predict as you can see from the example here you need to specify the name of the model customer churn in this case you can specify the training data use a table name or a select query in the query a column that we are trying to predict here it is marked as target and the column is c active or active underscore cost the final thing is specifying the name of the function through which we can then use this model to make predictions when this sql statement is run reshif ml behind the scenes will interact with sagemaker autopilot to train your model once ready it should deployed in redshift and the function will be available for predictions in this example to create the model that predicts customer churn you can query columns in one or more tables in amazon redshift that include the customer profile information and historical account activity as the inputs and the column showing whether the customer is active or inactive as the output which is what we want to predict now let's look at the second way the customers can interact with redshift and sagemaker for machine learning we also heard from our customer data scientists that they have already trained their machine learning models with xg boost mlp or autopilot and do not want to retrain these models using reshift create model statement they are instead looking to leverage these models directly to run inference on the data in redshift the benefit with this approach is they get to use the powerful multi-processing capability of redshift while not paying extra for inference or predictions they also do not need to export that data to s3 for inference now we provide this capability to import models from sagemaker into redshift in the create model statement that you see on your slide users specify the name of the sagemaker model and the function name as it's to be referred to in redshift once the statement is complete and executed users can now use the functions to run local predictions inside redshift the third way this is also what customers requested us is the capability to run machine learning models created in sage maker to be run in sage maker but use redshift data this would allow customers to leverage algorithms that reshift does not support as yet on the data which is residing in redshift we provide this capability as well by remotely invoking sagemaker endpoint from redshift ml so in this scenario that you're seeing on the slide the training and the predictions both are performed in sagemaker let's look at a customer reference here with a job case so job case which you probably know is a job search website it provides job recommendations to the users the job seekers with redshift ml they have reduced their time and cost for making job predictions while providing a better experience for their job seekers they were earlier using an external machine learning platform with data pipelines built to export their data daily that used to take them hours to get inferences or predictions with redshift ml now they are getting their inferences in a few minutes without building any data pipelines are already seeing results with a five to ten percent revenue improvement and also five to ten percent improvements in member engagement rates we already heard chris talk about other customers who have benefited from reshift ml across many other industries and use cases with that i'll hand it over to phil for the demonstration of reshef ml in identifying customer chun hi my name is phil bates and my teammate and i bono pittanpali our redshift analytics specialist essays we're going to take a persona based approach to show you how to solve a common business challenge that challenge that we're looking at today is customer churn the challenge is we've been experiencing higher than normal rates of customer churn our current methods of doing analysis of charged customers to create customer retainment campaigns are not effective enough and not targeting the correct customers kim the data scientist has been tasked to create a machine learning model to predict customer churn after meeting with miguel the data analyst they come up with a plan to use redshift ml since the data needed is already available in their redshift cluster they will also enlist the help of marie the bi analyst to help with creating the visualizations here are the steps that we will be going through today first we will create a model we will then validate the model run prediction queries then analyze and visualize the results then we will also show how the data scientists can use sagemaker and jupiter notebooks with xgboost and be able to provide other detail parameters in the create model statement now let's take a look at the demo and see how mcgill creates his model and validates it and then does some predictions first of all let's take a quick look at the table that contains the data that miguel will be using he's got a customer activity table which contains many important attributes that should be able to help him create a good model to predict churn things such as account length which is how many months a customer has been active and the various plans that they have subscribed to and what their recent charges have been so now let's take a look at our create model statement as you can see the create model statement in this example it's very simple in this one we're doing automl we will not provide any parameters such as the problem type or the objective or any hyperparameters or preprocessors miguel has chosen the attributes that he wants to use to try to predict the customer churn so as you can see we are going to use state area code how many months the customer has been active how many minutes they're currently been using and what is their average daily spend and their average daily cases all of these values here are the inputs the last one you see here churn that is what we are predicting you also see down in our target that we have that value referenced as well in this case we're looking for all the record dates which are less than 2020901 this is going to be our training set that the model will be trained on later when we are doing our predictions we'll be filtering on a different date so that we can do predictions on a test data set as you can see here the other things that we provide is a function we're going to call this predict customer churn and then it's also important you need to provide an iam role for a role which has permissions to execute and create a model and you also provide an s3 bucket this s3 bucket is where all the data is collected for the shake the handshake between redshift ml and sagemaker we have also an s3 garbage collect parameter you see we have set that off the reason we have done that in this example is so that all the artifacts that are created during the create model statement are saved off and the data scientists can analyze those for more usage later so as you can see here it's very simple we are just wrapping our select statement inside the create model statement now this model has already been ran it and we did it ahead of time because it does take about an hour to an hour and a half to run but this is how you do it and whenever you execute this create model statement everything is done in the background redshiftml coordinates a handoff to sagemaker autopilot and at that point sagemaker autopilot does everything behind the scenes and this runs asynchronously the create model statement itself will just take a few seconds to run and then everything is running in sagemaker and you can do a show model command it'll tell you the status of the model and if it's not ready it'll say that's in training or if it's had some other issue or an error it will show that as well so when the model shows that it's ready you are then able to use that to do your predictions so let's take a look at this show model command from this model that we previously created as you can see if you look at the model state it shows that it's ready and it shows that the validation in this case was a f1 binary because we did a binary classification there is our target column that we call churn and if you look at the parameters this is where you see the model type is auto that's because we didn't specify any parameters so sagemaker autopilot automatically detected that determined that the model type was binary classification because we can only have one of two values and it also has and creates an automl job name and then there is our function name and parameters this could be useful later you say you have an analyst or someone that you want to say run the predict customer churn you can point them to this show model command and they can go find the function name and the parameters that they need to run so now that we know that our model is ready let's do some validation and see how accurate it looks so as you see here this first query up here we are doing an accuracy rate calculation we're doing that by comparing the actual value the churn with what our predict customer churn function will return from our test data set that we used that was from the customer activity table where their record date was less than 20 20 00 901. so let's and you see here that what we're comparing it we're simply calling that predict customer churn function and passing in the parameters that we gave it in our create model statement so let's see how accurate our model was against the training data set as you can see it's over 90 it's 0.923 percent so we have a a pretty good accuracy rate there so now that miguel feels comfortable with the accuracy rate he is ready to go and run a prediction so what he's got now he's going to check some things of interest that he thinks he wants to analyze such as the account length uh the total charge etc and he's calling predict customer churn function again passing in those same parameters that we saw before this time he's changed the date he's looking for everything that's greater than or equal to 2020 0901 and he wants to see where the predicted active is false in other words these customers are predicted to churn as you see the results that there are 380 customers that are predicted to be churned one of the things he can do some simple charts as well as you see we're using redshift query editor version 2 and we have a few nice things that you can do in here miguel can do a simple chart so he decides that maybe he'll look at a bar chart you can change that here and we'll look to see if if we'll look at state for an example to see if anything unusual stands out as you can see some states appear to have more churn than others you see missouri here and vermont over here so we can also save that chart if we like and give it a name now that miguel has done that and done identified that there are 380 customers predicted to be churned he wants to turn this over to marie who is the the bi analyst and let her do some further investigations marie will be using quicksite and we'll see that in a little bit before he gives that to her he wants to make it easy for her so he's going to create he's created a view for her here and we call he calls this predict customer churn view so it's got the same query that we saw previously now he will just tell marie to pull the data from this view and we'll see what kind of insights that marie has in just a moment now that mcgill has created the data set of customers likely to churn marie is going to go into quicksite and show how easy it is to do some quick analysis and be able to share information with the customer service team as they develop programs to try to prevent these customers from churning she's going to choose new analysis and new data set we'll go to redshift auto discovered she just has to give in a few details to get logged in so validate the connection and choose create data source in this case she's going to use custom sql she decides to call this churn analysis and she's going to select the data from the view that miguel has created she chooses confirm query she's then going to choose to directly query the data and visualize it first thing that marie decides to look at is the state and get a count of customers predicted to churn by state as you can see there are some interesting things here she can scroll down and easily identify which states have higher incidence of customers likely to churn while that's interesting marie wants to also look at the total charge to see the impact of these customers again she is easily able to identify by state which ones are most impacted in terms of the value of the customer's monthly spend now she wants to look at it at the account id level to see if anything stands out there she quickly is able to tell that there are a few customers with great much greater monthly spend that are predicted likely to churn as you can see these average monthly spin values are much higher than the next closest customers as you can see marie has now been able to easily identify high impact customers and they can now put together a program to reach out to these customers this has illustrated how quick and easy it is to gain insights using amazon quicksite now we're going to demonstrate how a data scientist can use sagemaker and notebook tools that they're familiar with to do machine learning leveraging the redshift data api so now kim is going to go into amazon sage maker she's going to look at the model that was trained earlier by miguel she can go to hyper parameter tuning jobs and then select the name of the job that was ran she can then click on the best training job and she can see similar metrics that you might see from the show model command she's most interesting and looking at the best training job hyper parameters now that she's looked at that she decides that she wants to go and create a model using some of her own parameters using xgboost she's going to use a jupiter notebook the per jupiter notebook that she's created for this first she's going to do some setup so that she can use the data api next step is data preparation since we're going to be using xg boost we need to have all the inputs converted to integer values this step has already been done but as you can see she created a table called customer activity transform and she converted the state codes the alphanumerics to an id and she converted the churn values to be a one or a zero true being one false being zero and she's ran the data preparation step already now let's take it to create model statement that she ran as you can see the first part is the same as before with the exception that we're using the new transform table with numeric values and as you can see she's set the auto off and the model type to xgboost she specified the objective as binary logistic and she said for the hyper parameters to use all default except for the num round setting and again as we did before you have to specify the bucket and she's using s3 garbage collect off now we're going to check the status of the model through the notebook here so her show model command as you can see the model is ready now she can run predictions inside the notebook and as you can see she came up with almost the same number of records as before the previous model this one came up with 396 records or customers predicted to churn and the other one was 380 that we did previously in the visual editor that we did so you can see how easy it is to use the tools of your choice as a data scientist you can use notebooks that you're familiar with and you can put in the various parameters and objectives and model types as you create your models thank you phil summarizing what we spoke about and saw today with reshif ml customers enjoy a simplified way to do machine learning it helps i mean faster decision making innovate on their next intelligent application and transform their customers experience while gaining a competitive edge for more information about reshift ml and to take it for a spin go to aws.com redshift and get started thanks folks for watching have a nice day