In this Module of the Workshop you'll cover using SQL Server on-premises and in-cloud configurations, as well as hybrid applications as a solution for data processing. In each section you'll get more references, which you should follow up on to learn more. Also watch for links within the text - click on each one to explore that topic. The end of this module contains several helpful references you can use in this course and in production.
(Make sure you check out the prerequisite page before you start. You'll need all of the items loaded there before you can proceed with the workshop.)
In this module you'll cover working with Data Science workloads with a focus on larger sets of data. Starting in SQL Server 2019, big data clusters allows for large-scale, near real-time processing of data over the HDFS file system and other data sources. It also leverages the Apache Spark framework which is integrated into one environment for management, monitoring, and security of your environment. This means that organizations can implement everything from queries to analysis to Machine Learning and Artificial Intelligence within SQL Server, over large-scale, heterogeneous data. SQL Server big data clusters can be implemented fully on-premises, in the cloud using a Kubernetes service such as Azure's AKS, and in a hybrid fashion. This allows for full, partial, and mixed security and control as desired.
Note: This is a complex topic, so you can find a complete workshop on this topic here.
Businesses require near real-time insights from ever-larger sets of data from a variety of sources. Large-scale data ingestion requires scale-out storage and processing in ways that allow fast response times. In addition to simply querying this data, organizations want full analysis and even predictive capabilities over their data. Machine Learning, Artificial Intelligence, and Deep Learning techniques all require large sets of data for training their models to be effective. Two technologies have emerged as the primary methods for processing large sets of data, using a scale-out paradigm - Hadoop and Spark.
Hadoop uses a set of computing nodes that position the workload over distributed data nodes:
Spark is a technology that uses various libraries to make processing over distributed storage more efficient and faster:
Both of these technologies assume many nodes (computers), and since you only need the computation elements (not all the drivers and other components of a full computer or Virtual Machine), Container technologies work well for this solution.
To control containers, a technology called Kubernetes is used for deployment, management and storage of a grouping of containers, called a Cluster.
Using the technologies described above, SQL Server uses a Kubernetes Cluster to deploy multiple components for SQL Server processing, distributed queries using PolyBase, Spark, and Storage on HDFS for a complete environment to work with large sets of data, and includes Machine Learning, Artificial Intelligence and Deep Learning capabilities.
A SQL Server Big Data Cluster (BDC) can be deployed to multiple environments, in-cloud and on-premises:
- In a Cloud Service (Such as the Azure Kubernetes Service or AKS)
- On premises (using KubeADM)
These architectures are not mutually exclusive - you can install some components on-premises, and others as a service. Your connections can interconnect across these environments.
In addition to traditional OLTP workloads, the SQL Server Big Data Cluster has three other uses that work with data at scale.
Using the Data Virtualization capability of PolyBase in SQL Server Big Data Clusters you create External Tables for data outside the Cluster. These External Table definitions are stored in the database on the SQL Server Master Instance within the cluster. When queried by the user, the queries are engaged from the SQL Server Master Instance through the Compute Pool in the SQL Server BDC, which holds Kubernetes Nodes containing the Pods running SQL Server Instances. These Instances send the query to the PolyBase Connector at the target data system, which processes the query based on the type of target system. The results are processed and returned through the PolyBase Connector to the Compute Pool and then on to the Master Instance, and then on to the user.
This process allows not only a query to disparate systems, but also those remote systems can hold extremely large sets of data. Normally you are querying a subset of that data, so the results are all that are sent back over the network. These results can be joined with internal tables for a single view, and all from within the same Transact-SQL statements.
Ad-hoc queries over larges sets of normalized data are very useful for many scenarios. There are times when you would like to bring the data into storage, so that you can create denormalized representations of datasets, aggregated data, and other purpose-specific data tasks.
Using the Data Virtualization capability (PolyBase), the IT team creates External Tables using PolyBase statements. These External Table definitions are stored in the database on the SQL Server Master Instance within the cluster. When queried by the user, the queries are engaged from the SQL Server Master Instance through the Compute Pool in the SQL Server BDC, which holds Kubernetes Nodes containing the Pods running SQL Server Instances. These Instances send the query to the PolyBase Connector at the target data system, which processes the query based on the type of target system. The results are processed and returned through the PolyBase Connector to the Compute Pool and then on to the Master Instance, and the PolyBase statements can specify the target of the Data Pool. The SQL Server Instances in the Data Pool store the data in a distributed fashion across multiple databases, called Shards.
There are various uses for a large cluster of data processing systems for Machine Learning and AI applications. One primary use-case is the creation of the Features and Labels used in various ML and AI algorithms. Large sets of data stored in the Data Pool allow Python, R and Spark calls and libraries are available for this task in BDC. This process is often called Data Engineering.
The SQL Server Master Instance in the BDC installs with Machine Learning Services, which allow creation, training, evaluation and persisting of Machine Learning Models. Data from all parts of the BDC are available, and Data Science oriented languages and libraries in R, Python and Java are enabled. In this scenario, the Data Scientist creates the R or Python code, and the Transact-SQL Developer wraps that code in a Stored Procedure. This code can be used to train, evaluate and create Machine Learning Models. The Models can be stored in the Master Instance for scoring, or sent on to the App Pool where the Machine Learning Server is running, waiting to accept REST-based calls from applications.
The Data Scientist has another option to create and train ML and AI models. The Spark platform within the Storage Pool is accessible through the Knox gateway, using Livy to send Spark Jobs. This gives access to the full Spark platform, using Jupyter Notebooks (included in Azure Data Studio) or any other standard tools that can access Spark through REST calls.
Activity: Big Data Clusters for SQL Server Lab Review
In this lab, you will review a series of Jupyter Notebooks you can open in Azure Data Studio to work with a Big Data Cluster. It demonstrates loading data, querying SQL Server databases, accessing HDFS using PolyBase, loading data into the Data Pool, working with Spark, and implementing a Data Science Machine Learning Model in Python.
Note: This lab assumes a completely installed and function SQL Server Big Data Cluster if you wish to run the Notebooks, otherwise review the completed Notebooks at the reference.
Steps
Open this reference and review the notebooks you find there.
For Further Study
- Understanding the Big Data Landscape
- Linux for the Windows Admin
- Docker Guide
- Video introduction to Kubernetes
- Complete course on Azure Kubernetes Service (AKS)
- Working with Spark
- Full tutorial on Jupyter Notebooks
Next Steps
Next, Continue to 04 - SQL Server on the Microsoft Azure Platform.