{"id":25954,"date":"2019-01-09T09:00:03","date_gmt":"2019-01-09T17:00:03","guid":{"rendered":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/?p=25954"},"modified":"2021-09-17T14:12:45","modified_gmt":"2021-09-17T21:12:45","slug":"how-to-automate-machine-learning-on-sql-server-2019-big-data-clusters","status":"publish","type":"post","link":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2019\/01\/09\/how-to-automate-machine-learning-on-sql-server-2019-big-data-clusters\/","title":{"rendered":"How to automate machine learning on SQL Server 2019 big data clusters"},"content":{"rendered":"

In this post, we will explore how to use automated machine learning (AutoML) to create new machine learning models over your data in SQL Server 2019 big data clusters.<\/p>\n

SQL Server 2019<\/a> big data clusters<\/a> make it possible to use the software of your choice to fit machine learning models on big data and use those models to perform scoring. In fact, Apache Spark<\/a>TM<\/sup>, the popular open source big data framework, is now built in! Apache SparkTM<\/sup> includes the MLlib Machine Learning Library, and the open source community has developed a wealth of additional packages that integrate with and extend Apache SparkTM<\/sup> and MLlib.<\/p>\n

Automated machine learning<\/h2>\n

Manually selecting and tuning machine learning models requires familiarity with a variety of model types and can be laborious and time-consuming. Software for automating this process has recently become available, relieving both novice and expert Data Scientists and ML Engineers of much of the burden that comes with manual model selection and tuning.<\/p>\n

H2O\u2019s open source AutoML APIs<\/h3>\n

H2O provides popular open source software for data science and machine learning on big data, including Apache SparkTM<\/sup> integration. It provides two open source python AutoML classes: h2o.automl.H2OAutoML and pysparkling.ml.H2OAutoML. Both APIs use the same underlying algorithm implementations, however, the latter follows the conventions of Apache Spark\u2019s MLlib library<\/a> and allows you to build machine learning pipelines that include MLlib transformers. We will focus on the latter API in this post.<\/p>\n

H2OAutoML supports classification and regression. The ML models built and tuned by H2OAutoML include Random Forests, Gradient Boosting Machines, Deep Neural Nets, Generalized Linear Models, and Stacked Ensembles.<\/p>\n

H2OAutoML can automatically split training data into training, validation, and leaderboard frames. The h2o.automl.H2OAutoML API also allows these frames to be specified manually, which is useful when the task is to predict the future using a model trained on historical data.<\/p>\n

Models produced by H2OAutoML can be persisted to disk, used for prediction\/scoring in an Apache SparkTM<\/sup> cluster, used in local mode in\u00a0Apache SparkTM<\/sup> running on a single node, or used in a Java Virtual Machine (JVM) with the necessary libraries on the CLASSPATH. These options will allow batch and real-time scoring in a SQL Server 2019 big data cluster within Apache SparkTM<\/sup>, within a Transact-SQL stored procedure, or deployed as an application.<\/p>\n

Running PySpark3 notebooks in Azure Data Studio<\/h3>\n

The code discussed in this blog is available as a Jupyter notebook<\/a>\u00a0written for the PySpark3 kernel. You can now run Apache SparkTM<\/sup> notebooks in Azure Data Studio connected to a SQL Server 2019 big data cluster as described in this\u00a0notebook<\/a> how-to.<\/p>\n

Power plant output prediction<\/h2>\n

Let\u2019s take a tour through our example Jupyter notebook<\/a>\u00a0that shows how a customer running a power plant would take advantage of H20 and AutoML in Apache SparkTM<\/sup> to predict power plant output. This example is based on an H20 blog post<\/a>.<\/p>\n

The first cells of the notebook set\u00a0Apache SparkTM<\/sup> parameters and install the H2O PySparkling package if it\u2019s not already installed; this package provides the pysparkling.ml.H2OAutoML class.<\/p>\n

Next, the notebook code downloads the CSV file containing the data and copies the file to HDFS, if it\u2019s not already present.<\/p>\n

Running H2OContext.getOrCreate starts the H20 engine.<\/p>\n

Next, the notebook uses Apache SparkTM\u00a0<\/sup>to read the data from HDFS and randomly split it into training and prediction\/test sets.<\/p>\n

The following screenshot shows how easy it is to invoke automated machine learning:<\/p>\n

\"\"<\/p>\n

Here, you are defining a modeling pipeline, fitting it on the training data, and using it to generate predictions on the test data. In our example, we set maxModels=2, which results in two tree-based models and two (identical) stacked ensemble models. This is sufficient for demonstration purposes, but in practice, you should allow H2OAutoML to explore more models to achieve the best possible prediction metrics. If you simply omit the maxModels argument, then H2OAutoML will explore models for a maximum of maxRuntimeSecs, which defaults to 3600 seconds (1 hour).<\/p>\n

Our code follows the standard pattern for using the Apache SparkTM<\/sup> MLlib library because the pysparkling.ml.H2OAutoML class inherits from pyspark.ml.wrapper.JavaEstimator.<\/p>\n

Notice that we included an Apache SparkTM<\/sup> SQLTransformer in our pipeline, showing that a standard Spark MLlib transformer<\/a> can be used with a pysparkling.ml.H2OAutoML estimator<\/a> in an Apache SparkTM<\/sup>\u00a0MLlib pipeline<\/a>. During both training and scoring, this transformer will skip any rows that have a Celsius temperature value of less than or equal to 10.<\/p>\n

You can see the generalization performance of our model by looking at the leaderboard. The generalization performance we get for predictions on held-out data should be similar to the leaderboard performance. You can use Apache Spark’s\u00a0RegressionEvaluator class to compute metrics such as the mean absolute error (MAE). As expected, the MAE for predictions on held-out data is similar to the leaderboard MAE, with both typically between 2.3 and 2.5.<\/p>\n

Scale and monitor big data in SQL Server 2019 big data clusters<\/h2>\n

With SQL Server 2019, not only can you automatically select and tune machine learning models, you can also easily scale and monitor your big data cluster.<\/p>\n

Scaling to big data<\/h3>\n

Using SQL Server 2019 big data clusters, large amounts of computing and memory resources can be leveraged to process data at scale quickly and efficiently. To scale to big data, you have the ability to configure the following parameters:<\/p>\n