A look at BDC architecture

Big data clusters are built on Kubernetes, and implemented as a cluster of containers. The nodes in the cluster are divided into three logical planes: the control plane, the compute plane, and the data plane. Each of these planes serve a different set of functionalities in the cluster as shown in Figure 20-1.

HDFS tiering

HDFS tiering is similar to PolyBase in that it allows you to access external data. HDFS tiering allows you to mount file systems that are compatible with HDFS. This includes Azure Data Lake Storage Gen2 and Amazon S3. This feature is only available in a big data cluster.

The benefit of tiering is that it allows seamless access to the data as though it was mounted locally. Only the metadata is copied over to your local database. The data is copied on demand when accessed. This allows Spark jobs and SQL queries to be executed as though they are being run on local HDFS systems.

The file mounts are read-only, and directories or files cannot be added or removed through the mount. If the location of a mount changes, the mount must be deleted and recreated. The mount location is not dynamic.

The Spark connector

One of the more common ways of cleaning, analyzing, and processing data is in Spark. To make this process easier, two new connectors have been introduced in SQL Server 2019. The Spark to SQL connector uses JDBC to write data to the SQL Server. A sample of how this connector is used can be found at https://github.com/microsoft/sql-server-samples/blob/master/samples/features/sql-big-data-cluster/spark/data-virtualization/spark_to_sql_jdbc.ipynb

The second connector is the MSSQL Spark connector. This connector uses SQL Server Bulk copy APIS to write data to SQL Server. A sample of the connector code in use can be found at https://github.com/microsoft/sql-server-samples/blob/master/samples/features/sql-big-data-cluster/spark/data-virtualization/mssql_spark_connector_non_ad_pyspark.ipynb.

Shared access signatures

As the name suggests, a shared access signature (SAS) is a way to share an object in your storage account with others without exposing your account key. This gives you granular control over the access you grant. This can be done at the account, service, or user level.

• You can set a start time and expiry time

• Blob containers, file shares, queues and tables are all resources that accept SAS policies

• You can set an optional IP address or range from which access will be accepted

• You can restrict access from HTTPS clients by specifying the accepted protocol

Shared access signatures (SAS) are used to allow clients to read, write, or delete data in your storage account without access to your account key. This is typically needed when a client wants to upload large amounts of data or high-volume transactions to your storage account, and creating a service to scale and match demand is too difficult or expensive. The SAS is a signed URI that points to resources and includes a token that contains a set of query parameters (token) that indicate how the resource can be accessed. Azure Storage checks both the storage piece and the provided token piece of the SAS URI to verify the request. If for any reason the SAS is not valid, it receives an error code 403 (Forbidden) and access is denied.

There are two different types of SAS:

1. The Service SAS delegates access to a resource in a single storage service (blob, queue, table, or file service).

2. The Account-level SAS delegates access to resources in one or more storage services within a storage account. This also includes the option to apply access to services such as Get/Set Service Properties and Get Service Stats.

For details on all the parameters that can be set with Shared access signatures at https://docs.microsoft.com/azure/storage/common/storage-dotnet-shared-access-signature-part-1#shared-access-signature-parameters.

An ad hoc SAS can be created either as an account SAS or service SAS, and the start time, expiry time, and permissions are all specified in the SAS URI. An SAS with a stored access policy is defined on a resource container and can be used to manage constraints on one or more SAS. When the SAS is associated with a stored access policy, it inherits the constraints defined for the stored access policy.

Any SAS access attributes egress costs to your Azure Subscription. The data that is uploaded with a SAS is not validated in any way, so a middle tier that performs rule validation, authentication and auditing may still be the better option. Regardless of what you choose, monitor your storage for spikes in authentication failures.

Examples of how to create account and service SAS can be found at https://docs.microsoft.com/azure/storage/common/storage-dotnet-shared-access-signature-part-1#sas-examples.

Installation

With SQL Server 2019, PolyBase is available on Linux as well as Windows. The virtues and architecture of big data clusters were discussed earlier in this chapter. In this section we look at the installation and application of the PolyBase concepts.

Installation instructions for Windows can be found at https://docs.microsoft.com/sql/relational-databases/polybase/polybase-installation.

When installing on a Windows machine keep these items top of mind:

1. PolyBase can be installed on only one instance per machine

2. To use PolyBase you need to be assigned the sysadmin role or Control Server permission

3. All nodes in a scale-out group need to run under the same domain account

4. You must enable the PolyBase feature after installation

5. If the Windows firewall service is running during installation, the necessary firewall rules will be set up automatically

For step-by-step instructions on how to install PolyBase on Linux (Red Hat, Ubuntu, or SUSE) at https://docs.microsoft.com/sql/relational-databases/polybase/polybase-linux-setup.

As with Windows, you must enable the PolyBase feature after installation on Linux.

If you are connecting to Hadoop specifically, there are a few additional steps needed for configuration and security. Please follow the steps listed at https://docs.microsoft.com/sql/relational-databases/polybase/polybase-configure-sql-server.

With the installation complete the next steps are:

• Configure sources

• Enable access

• Create External Tables

Configure and enable

A master key is a symmetric key used to protect other keys and certificates in the database. It is encrypted with both an algorithm and password. A master key is needed for protecting the credentials of the external tables. Details on how to create and update a master key, and the instructions to set it up, can be found in this chapter in the section called External Tables.

Along with the master key you will need to configure PolyBase globally using sp_configure. The configvalue can range from 0 to 7 with 1 through 6 dealing with the older versions of Hortonworks. Setting 7 allows for connectivity with more recent versions on both Windows and Linux as well as Azure Blob Storage. As you usually do after running the sp_configure command, RECONFIGURE needs to be run and a restart of SQL Server service needs to be completed. The PolyBase services will both have to be started manually as they are automatically turned off during this process and do not start automatically.

This can be done with these commands:

Click here to view code image

exec sp_configure @configname = 'polybase enabled', @configvalue = 1;

exec sp_configure @configname = 'hadoop connectivity', @configvalue = 7;

RECONFIGURE [ WITH OVERRIDE ]  ;

You can find details on the command on Microsoft Docs at https://docs.microsoft.com/sql/database-engine/configure-windows/polybase-connectivity-configuration-transact-sql.

Database scoped credential

Scoped credentials are used to access non-public Azure blob storage accounts from SQL Server or Azure Synapse with PolyBase. Hadoop clusters secured with Kerberos that are accessed from SQL Server with PolyBase also require scoped credentials.

Syntax for creation of scoped credentials can be found in the Microsoft Docs at https://docs.microsoft.com/sql/t-sql/statements/create-database-scoped-credential-transact-sql.

External data source

External data sources are used to establish connectivity to systems outside of SQL Server for data virtualization or loading data using PolyBase. The most common use cases are loading data with bulk insert or openrowset activities or access to data that would otherwise not be available in SQL Server because it resides on another system such as Hadoop.

The following example shows how to load data from a CSV file in an Azure blob storage location, which has been configured as an external data source. This requires a database-scoped credential using a shared access signature.

Click here to view code image

-- Create the External Data Source

-- Remove the ? from the beginning of the SAS token

-- Do not put a trailing /, file name, or shared access signature parameters at the end
of the LOCATION URL when configuring an external data sources for bulk operations.

CREATE DATABASE SCOPED CREDENTIAL AccessPurchaseOrder
WITH
     IDENTITY = 'SHARED ACCESS SIGNATURE'
, SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-
29T16:55:34Z***************'
;
CREATE EXTERNAL DATA SOURCE ExternalPurchaseOrder
WITH
(LOCATION   = 'https://newinvoices.blob.core.windows.net/week3'
,CREDENTIAL = AccessPurchaseOrder, TYPE = BLOB_STORAGE)
;
--Insert into

BULK INSERT Sales.Orders
FROM 'order-2019-11-04.csv'
WITH (DATA_SOURCE = ' ExternalPurchaseOrder');

External file format

The external file format is required before you can create the external table. As suggested, the file format specifies the layout of the data to be referenced by the external table. Hadoop, Azure Blob Storage, and Azure Data Lake Storage all need an external file format object defined for PolyBase. Delimited text files, Parquet, and both RCFile and ORC Hive files are supported.

An example is:

Click here to view code image

CREATE EXTERNAL FILE FORMAT skipHeader_CSV
WITH (FORMAT_TYPE = DELIMITEDTEXT,
      FORMAT_OPTIONS(
          FIELD_TERMINATOR = ',',
          STRING_DELIMITER = '"',
          FIRST_ROW = 2, 
          USE_TYPE_DEFAULT = True)
)

Syntax for this command can be found at https://docs.microsoft.com/sql/t-sql/statements/create-external-file-format-transact-sql.

External table

External tables are used to read specific external data and to import data into SQL Server. No actual data is moved to SQL Server during the creation of an external table, only the metadata along with basic statistics about the folder and file are stored. The intent of the external table is to be the link connecting the external data to SQL Server to create the data virtualization. The external table looks much like a regular SQL Server table in format and has a similar syntax as you see here with this external Oracle table.

Click here to view code image

-- Create a Master Key

   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';
-- LOCATION: Location string for data
   CREATE EXTERNAL DATA SOURCE external_data_source_name
   WITH ( LOCATION = 'oracle://<server address>[:<port>]',   CREDENTIAL =
 credential_name)
--Create table
   CREATE EXTERNAL TABLE customers(
   [O_ORDERKEY] DECIMAL(38) NOT NULL,
   [O_CUSTKEY] DECIMAL(38) NOT NULL,
   [O_ORDERSTATUS] CHAR COLLATE Latin1_General_BIN NOT NULL,
   [O_TOTALPRICE] DECIMAL(15,2) NOT NULL,
   [O_ORDERDATE] DATETIME2(0) NOT NULL,
   [O_ORDERPRIORITY] CHAR(15) COLLATE Latin1_General_BIN NOT NULL,
   [O_CLERK] CHAR(15) COLLATE Latin1_General_BIN NOT NULL,
   [O_SHIPPRIORITY] DECIMAL(38) NOT NULL,
   [O_COMMENT] VARCHAR(79) COLLATE Latin1_General_BIN NOT NULL
   )
   WITH ( LOCATION='customer', DATA_SOURCE= external_data_source_name   );

In ad-hoc query scenarios, such as querying Hadoop data, PolyBase stores the rows that are retrieved from the external data source in a temporary table. After the query completes, PolyBase removes and deletes the temporary table. No permanent data is stored in SQL tables. Connect to an employee.tbl file in blob storage on a Hadoop cluster. The query looks similar to a standard T-SQL query. Once the external table is defined the data is retrieved from Hadoop and displayed.

Click here to view code image

CREATE EXTERNAL DATA SOURCE mydatasource
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'
)
CREATE EXTERNAL FILE FORMAT myfileformat
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (FIELD_TERMINATOR ='|')
);
CREATE EXTERNAL TABLE ClickStream (
    url varchar(50),
    event_date date,
    user_IP varchar(50)
)
WITH (
        LOCATION='/webdata/employee.tbl',
        DATA_SOURCE = mydatasource,
        FILE_FORMAT = myfileformat
    )
;
SELECT TOP 10 (url) FROM ClickStream WHERE user_ip = 'xxx.xxx.xxx.xxx' ;

In an import scenario, such as select into from external table, PolyBase stores the rows that are returned as permanent data in the SQL table. The new table is created during query execution when PolyBase retrieves the external data.

PolyBase can push some of the query computation to Hadoop to improve query performance. This action is called predicate pushdown. It is used by specifying the Hadoop resource manager location option when creating the external data source, and turning the pushdown on using these parameters:

Click here to view code image

PUSHDOWN                  = [ON | OFF]
 , RESOURCE_MANAGER_LOCATION = '<resource_manager>[:<port>]'

You can create many external tables that reference the same or different external data sources.

Statistics

Statistics on external tables is done the same as other tables.

Syntax for this command can be found at https://docs.microsoft.com/sql/t-sql/statements/create-statistics-transact-sql.

SELECT SERVERPROPERTY ('IsPolyBaseInstalled') AS IsPolyBaseInstalled;

Catalog views

There are catalog views for the installation and running of PolyBase.

Dynamic management views

There are a number of dynamic management views that can be used with PolyBase. The list of relevant views can be found in the Microsoft Docs at https://docs.microsoft.com/sql/relational-databases/polybase/polybase-troubleshooting#dynamic-management-views.

They can be used to troubleshoot issues such as finding long running queries and to monitor nodes in a PolyBase group.

Configure ML Server for operationalization of models and analytics

All deployments must consist of at least a single web node, single compute node, and a database.

Web nodes are HTTP REST endpoints that users interact directly with to make API calls. They are used to access the data and send requests to the compute node. One web node can route multiple requests at the same time.

Commute nodes are used to execute the code. Each node needs its own pool of R or Python shells and can therefore execute multiple requests at the same time.

Security for operationalizing models

Machine Learning Server allows for seamless integration with Active Directory LDAP or Azure Active Directory. For more details on how to do this see the Microsoft Docs at https://docs.microsoft.com/machine-learning-server/operationalize/configure-authentication.

Configuring the server to use a trust relationship between the users and the operational engine will allow the use of username/password combination for verification. Once the user is verified, a token is issued to the authenticated user. More details on how to manage tokens can be found on Microsoft Docs at https://docs.microsoft.com/machine-learning-server/operationalize/how-to-manage-access-tokens.

In addition to authenticating the user, it is important to secure the tokens from interception during transmission and storage. This should be done using transport layer security (HTTPS), as shown in Figure 20-6. More details on how to enable SSL or TLS can be found on Microsoft Docs at https://docs.microsoft.com/machine-learning-server/operationalize/configure-https.

Role-based access control for web services is also recommended. Role-based access control (RBAC) is used to grant minimum security required for a user’s role. More details on how to set up the roles can be found in MS Docs at https://docs.microsoft.com/machine-learning-server/operationalize/configure-roles.

Cross-Origin Resource Sharing

Cross-Origin Resource sharing (CORS) is used to request resources from a domain different than the one your web node is in. This is typical when using a government or third-party data source.

CORS is a mechanism that uses additional HTTP headers to tell a browser to let a web application running at one origin (your domain) have permission to access selected resources from a server at a different origin (a government site with data on how many people voted in the last election). A web application executes a cross-origin HTTP request when it requests a resource that has a different origin (domain, protocol, or port) than its own origin.

Here is an example of a cross-origin request: The frontend JavaScript code for a web application served from http://mydomain.com uses XMLHttpRequest to make a request for http://api.stats.gov.ca/data.json.

For security reasons, browsers restrict cross-origin HTTP requests initiated from within scripts. For example, XMLHttpRequest and the Fetch API follow the same-origin policy. This means that a web application using those APIs can only request HTTP resources from the same origin the application was loaded from, unless the response from the other origin includes the right CORS headers.

By default, Cross-Origin Resource sharing (CORS) is disabled and can be enabled by following the instructions in MS Docs at https://docs.microsoft.com/machine-learning-server/operationalize/configure-cors.

Packages for R and Python

When R or Python are installed with either SQL Server or with Machine Learning Services, a distribution of open source libraries are included. These libraries may vary with each version and can only be upgraded by upgrading your installation, regular service pack, or cumulative upgrades. An example would be upgrading from SQL Server 2016 to SQL Server 2019. The libraries should never be manually upgraded and may destabilize your installation should you do so.

Operationalizing and deploying code may often mean including additional packages in addition to the default ones included in an installation. These packages are publicly available, but due to enterprise restrictions on Internet access, it can sometimes be viable to have a repository inside the enterprise firewall. Note that all dependent packages need to be available to all nodes at runtime.

R package management options are available at https://docs.microsoft.com/machine-learning-server/operationalize/configure-manage-r-packages.

The Python package list can be found at https://docs.microsoft.com/sql/advanced-analytics/python/install-additional-python-packages-on-sql-server?view=sql-server-ver15#add-a-new-python-package.

Deploy model as a web service

Both R and Python models can be deployed as a web service. Using the mrsdeploy or azureml-model-management-sdk package that ships with Machine Learning Server, you can develop, test, and ultimately deploy these analytics as web services in your production environment.

Python uses the azureml-model-management-sdk Python package that ships with the Machine Learning Server to deploy the web services and manage the web service from a Python script. A set of RESTful APIs are also available for direct access. Details on how to deploy the service can be found at https://docs.microsoft.com/machine-learning-server/operationalize/python/quickstart-deploy-python-web-service.

R uses the functions in the mrsdeploy package to gain access to the web service from an R script, and then uses a set of RESTful APIs to provide access to the web service directly. Publish the model as a web service to Machine Learning Server using the publishService() function from the mrsdeploy package.

Details on how to deploy the service can be found at https://docs.microsoft.com/machine-learning-server/operationalize/quickstart-publish-r-web-service.

API libraries

Machine Learning Server uses Swagger for the creation of both Python and R APIs. Swagger is an open source tool used to build and enable APIs. Using core APIs, R, and Python code can be published as a Machine Learning Server hosted analytics Web service. Swagger offers templates to simplify this process, which are available at https://swagger.io. Swagger-based JSON files define the list of calls and resources available in the REST APIs. These are defined as either Core APIs or Service Consumption APIs. More details on the different APIs can be found at https://docs.microsoft.com/machine-learning-server/operationalize/concept-api. An API library is needed to access the RESTful APIs outside of R/Python. This library can be built with Swagger and will simplify the calls, the encoding of data, and a markup response handling on the API. Programming language options for the library are: C#, Java, JS, Python, or Node.js. Details on the swagger template files for APIs can be found at https://docs.microsoft.com/machine-learning-server/operationalize/how-to-build-api-clients-from-swagger-for-app-integration#get-the-swagger-file.

Steps for Deployment

Depending on the type of deployment you are doing and to what configuration, the steps are very different, however a simple practice example that follows the basic steps in a Python notebook can be found in the accompanying sample. This script and all scripts for this book are available for download at https://www.MicrosoftPressStore.com/SQLServer2019InsideOut/downloads.

1. Configuration

   1. Workspace

   2. Notebook

   3. Access

2. Ensure you have data and a trained model

3. Save the package

4. Deploy

5. Run and test the deployed model