How to automate machine learning on SQL Server 2019 big data clusters

In this post, we will explore use automated (AutoML) to create new models over your data in SQL big data clusters.

SQL Server 2019 big data clusters make it possible to use the software of your choice to fit models on big data and use those models to perform scoring. In fact, Apache SparkTM, the popular open source big data framework, is now built in! Apache SparkTM 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 and MLlib.

Automated machine learning

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.

H2Os open source AutoML APIs

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

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.

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.

Models produced by H2OAutoML can be persisted to disk, used for prediction/scoring in an Apache SparkTM cluster, used in local mode inApache SparkTM 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 big data cluster within Apache SparkTM, within a Transact-SQL stored procedure, or deployed as an application.

Running PySpark3 notebooks in Azure Data Studio

The code discussed in this blog is available as a Jupyter notebookwritten for the PySpark3 kernel. You can now run Apache SparkTM notebooks in Azure Data Studio connected to a SQL big data cluster as described in thisnotebook how-to.

Power plant output prediction

Lets take a tour through our example Jupyter notebookthat shows how a customer running a power plant would take advantage of H20 and AutoML in Apache SparkTM to predict power plant output. This example is based on an H20 blog post.

The first cells of the notebook setApache SparkTM parameters and install the H2O PySparkling package if its not already installed; this package provides the class.

Next, the notebook code downloads the CSV file containing the data and copies the file to HDFS, if its not already present.

Running H2OContext.getOrCreate starts the H20 engine.

Next, the notebook uses Apache SparkTMto read the data from HDFS and randomly split it into training and prediction/test sets.

The following screenshot shows how easy it is to invoke automated machine learning:

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).

Our code follows the standard pattern for using the Apache SparkTM MLlib library because the class inherits from

Notice that we included an Apache SparkTM SQLTransformer in our pipeline, showing that a standard Spark MLlib transformer can be used with a estimator in an Apache SparkTMMLlib pipeline. During both training and scoring, this transformer will skip any rows that have a Celsius temperature value of less than or equal to 10.

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'sRegressionEvaluator 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.

Scale and monitor big data in SQL Server 2019 big data clusters

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.

Scaling to big data

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:

  • The number and size of nodes in the cluster
  • The number of Apache SparkTM
  • YARN scheduler memory and cores
  • Apache SparkTM Driver and Executor memory, cores, and the number of executors per pod
  • Livy timeout

Details on setting these parameters are included in the sample notebook.

Monitoring and diagnostics

SQL Server 2019 big data clusters include powerful tools for monitoring and diagnostics. The sample notebook includes instructions for accessing the following graphical user interfaces for monitoring, controlling, and runs in Apache SparkTM:


  • Shows the available and used memory and virtual cores in the Apache SparkTM cluster
  • Lists running and completed Apache SparkTM applications
  • Provides links to the Apache SparkTM UI for running applications and Spark History for completed applications
  • Allows running applications to be terminated

Apache SparkTM UI

  • Provides detailed information on running Apache SparkTM applications

Apache SparkTM History

  • Provides details on completed Apache SparkTM applications
  • Includes newly available Microsoft diagnostics for Apache SparkTM applications

H2O Flow UI

  • Monitors H2O job progress and engine status


In this blog post, youve learned that SQL Server has gained a powerful new capability in the 2019 preview and learned how to run machine learning workloads on big data using built-in Apache SparkTM, with the ability to leverage additional packages of your choosing such as H2Os automated machine learning software. We have taken a tour through a sample Apache SparkTM notebook for automated machine learning that can be run in Azure Data Studio against a SQL Server 2019 big data cluster. And youve seen how you can scale resources such as nodes, cores, and memory, and monitor Apache SparkTM applications using built-in graphical user interfaces.

Getting started


The class is part of H2Os Spark integration, Sparkling Water, which is documented here. Unfortunately, this site currently lacks detailed documentation of Instead, you can find help on attributes and methods by running the following python commands:

from import H2OAutoML


Since and h2o.automl.H2OAutoML share underlying code, it is also helpful to refer to the latters documentation.

The post How to automate machine learning on SQL Server 2019 big data clusters appeared first on SQL Server Blog.


This article was originally published by Microsoft's PowerShell Blog. You can find the original article here.