Workshop: Kubernetes - From Bare Metal to SQL Server Big Data Clusters

A Microsoft Course from the SQL Server team

05 - Using the SQL Server big data cluster on Kubernetes for Data Science

In this workshop you have covered concepts and processes for using Kubernetes, and how to set up a SQL Server big data cluster on a Kubernetes Cluster. The end of this Module contains several helpful references you can use in these exercises and in production.

This module covers a complete workflow for this environment, focusing on a Data Science application.

4.0 End-To-End Solution with BDC

Wide World Importers (WWI) is a traditional brick and mortar business with a long track record of success, generating profits through strong retail store sales of their unique offering of affordable products from around the world. They have a traditional N-tier application that uses a front-end (mobile, web and installed) that interacts with a scale-out middle-tier software product, which in turn stores data in a large SQL Server database that has been scaled-up to meet demand.

WWI has now added web and mobile commerce to their platform, which has generated a significant amount of additional data, and data formats. These new platforms were added without integrating into the OLTP system data or Business Intelligence infrastructures. As a result, "silos" of data stores have developed, and ingesting all of this data exceeds the scale of their current RDBMS server:

This presented the following four challenges - the IT team at WWI needs to:

Scale data systems to reach more consumers
Unlock business insights from multiple sources of structured and unstructured data
Apply deep analytics with high-performance responses
Enable AI into apps to actively engage with customers

Solution - Challenge 1: Scale Data System

To meet these challenges, the following solution is proposed. Using the BDC platform you learned about in the 02 - BDC Components Module, the solution allows the company to keep it's current codebase, while enabling a flexible scale-out architecture. This answers the first challenge of working with a scale-out system for larger data environments.

The following diagram illustrates the complete solution that you can use to brief your audience with:

In the following sections you'll dive deeper into how this scale is used to solve the rest of the challenges.

4.1 Data Virtualization - Challenge 2: Multiple Data Sources

The next challenge the IT team must solve is to enable a single data query to work across multiple disparate systems, optionally joining to internal SQL Server Tables, and also at scale.

Using the Data Virtualization capability you saw in the 02 - SQL Server BDC Components Module, the IT team creates External Tables using the PolyBase feature. 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.

Activity: Load and query data in an External Table

In this activity, you will load the sample data into your big data cluster environment, and then create and use an External table to query the data in HDFS. This process is similar to connecting to any PolyBase target.

Steps

Open this reference, and perform all of the instructions you see there. This loads your data in preparation for the next Activity.

Open this reference, and perform all of the instructions you see there. This step shows you how to create and query an External table.

(Optional) Open this reference, and review the instructions you see there. (You You must have an Oracle server that your BDC can reach to perform these steps, although you can review them if you do not)

4.2 Creating a Distributed Data solution using big data cluster - Challenge 3: Deep Analytics

Ad-hoc queries 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 you saw in the 02 - BDC Components Module, 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.

Activity: Load and query data into the Data Pool

In this activity, you will load the sample data into your big data cluster environment, and then create and use an External table to load data into the Data Pool.

Steps

Open this reference, and perform the instructions you see there. This loads data into the Data Pool.

4.3 Querying HDFS Data using big data cluster - Challenge 4: Enable AI

There are three primary uses for a large cluster of data processing systems for Machine Learning and AI applications. The first is that the users will involved in the creation of the Features used in various ML and AI algorithms, and are often tasked to Label the data. These users can access the Data Pool and Data Storage data stores directly to query and assist with this task.

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 as you learned about in the 02 - SQL Server BDC Components Module. 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: Load data with Spark, run a Spark Notebook

In this activity, you will load the sample data into your big data cluster environment using Spark, and use a Notebook in Azure Data Studio to work with it.

Steps

Open this reference, and follow the instructions you see there. This loads the data in preparation for the Notebook operations.

Open this reference, and follow the instructions you see there. This simple example shows you how to work with the data you ingested into the Storage Pool using Spark.

For Further Study

Official Documentation for this section
Use curl to load data into HDFS on SQL Server 2019 big data clusters
Train and Create machine learning models with Spark
How to deploy an app on BDC
How to use notebooks in SQL Server 2019 preview

Next Steps

Congratulations! You have completed this course. Review the "For Further Study" links in each Module to go deeper into each topic.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!