El pasado lunes 27 de mayo se realizo en Buenos Aires el evento DataSummit 2019 organizado por nuestra comunidad de SQL Pass Argentina
En este evento que duro todo el día en las oficinas de Microsoft Argentina y que han participado mas de 60 asistentes, se han dado varias charlas de Data Platform y Analytics donde los oradores mostramos muchas de las nuevas funcionalidades que nos ofrece la plataforma de Microsoft ya sea para ambientes on-prem como azure.
Por mi lado he dado estas dos conferencias de 90 minutos cada una:
Modernizando BI en azure
En esta charla mostré con ejemplos reales como se pueden usar los servicios de azure para poder implementar una solución completa de BI y analytics. Observamos el uso de Datalake Storage, Blob Storage, SQL datawarehouse, Analysis Services, Powerbi, Big Data (HdInisight y Databricks) y Datafactory para los ETL.
En el siguiente link comparto el material que use en mi charla como así también varios enlaces de interés
SQL Machine Learning y Big Data cluster SQL 2019
En esta charla he mostrado como usar las características de Machine Learning en SQL usando lenguajes como R y Python como así también las nuevas características de Big data cluster de SQL 2019
En el siguiente linkcomparto el material de dicha charla
SQL Server Machine Learning: Trabajando con modelos.
Autor: Maximiliano Accotto (MVP Data Platform).
www.triggerdb.com TriggerDB Consulting SRL
Trabajar con modelos es lo más habitual para las tareas de Machine Learning.
En este Lab usaremos el store procedure sp_execute_external_script el cual vimos en el LAB01 para poder crear y guardar modelos.
Paso 1: Creación de tablas
En este paso lo que haremos es crear una tabla en SQL Server en la cual luego guardaremos los modelos de R o Python, como así también una segunda tabla en la cual guardaremos datos de velocidades y distancias de frenados de autos usando el Dataset Cars de R.
Para ello usaremos el siguiente código desde el SSMS
-- CREACION DE BASE DE DATOS
DROP DATABASE IF EXISTS MLTRIGGERDB
CREATE DATABASE MLTRIGGERDB
USE MLTRIGGERDB
GO
DROP TABLE IF EXISTS DBO.TRIGGERDB_MODEL
CREATE TABLE DBO.TRIGGERDB_MODEL
(
ID INT IDENTITY PRIMARY KEY,
MODELNAME VARCHAR(255),
MODEL VARBINARY(MAX)
)
DROP TABLE IF EXISTS DBO.CARSPEED
CREATE TABLE DBO.CARSPEED ([SPEED] INT NOT NULL,
[DISTANCE] INT NOT NULL);
Paso 2: Cargar la tabla CarSpeed
En este paso cargaremos la tabla CarSpeed usando un dataset de R (Cars)
En este paso crearemos un Store Procedure el cual invocará a sp_execute_external_script pero para la creación de un modelo de regresión lineal, este SP que crearemos nos retornara como resultado un binario el cual contiene la representación de dicho modelo.
DROP PROCEDURE IF EXISTS generate_linear_model;
GO
CREATE PROCEDURE generate_linear_model
AS
BEGIN
EXECUTE sp_execute_external_script
@language = N'R'
, @script = N'lrmodel <- rxLinMod(formula = distance ~ speed, data = CarsData);
trained_model <- data.frame(payload = as.raw(serialize(lrmodel, connection=NULL)));'
, @input_data_1 = N'SELECT [speed], [distance] FROM CarSpeed'
, @input_data_1_name = N'CarsData'
, @output_data_1_name = N'trained_model'
WITH RESULT SETS ((model varbinary(max)));
END;
GO
Paso 4: Guardar el modelo en una tabla
En este paso guardaremos el modelo en la tabla que hemos creado en el paso 1
INSERT INTO DBO.TRIGGERDB_MODEL (model)
EXECUTE generate_linear_model
UPDATE DBO.TRIGGERDB_MODEL SET MODELNAME = 'ModeloCars'
WHERE ID = SCOPE_IDENTITY()
SELECT * FROM DBO.TRIGGERDB_MODEL
Paso 5: Usar el modelo para predicciones
En este paso usaremos al modelo creado con R y guardado en una tabla SQL Server para poder hacer predicciones, para ello crearemos una nueva tabla con velocidades para que nos determine la distancia de frenado en base al modelo.
DROP TABLE IF EXISTS [dbo].[NewSpeed]
CREATE TABLE [dbo].[NewSpeed] (
[speed] [int] NOT NULL,
[distance] [int] NULL) ON [PRIMARY]
GO
INSERT [dbo].[NewSpeed] (speed)
VALUES (40), (50), (60), (70), (80), (90), (100),(110),(133),(200)
-- predecimos con el modelo guardado anteriormente en la tabla
DECLARE @speedmodel varbinary(max) =
(SELECT model FROM [dbo].[TRIGGERDB_MODEL] WHERE MODELNAME = 'ModeloCars');
EXECUTE sp_execute_external_script
@language = N'R'
, @script = N'
current_model <- unserialize(as.raw(speedmodel));
new <- data.frame(NewData);
predicted.distance <- rxPredict(current_model, new);
str(predicted.distance);
OutputDataSet <- cbind(new, ceiling(predicted.distance));
'
, @input_data_1 = N' SELECT speed FROM [dbo].[NewSpeed] '
, @input_data_1_name = N'NewData'
, @params = N'@speedmodel varbinary(max)'
, @speedmodel = @speedmodel
WITH RESULT SETS (([new_speed] INT, [predicted_distance] INT))
Creando un SP para predicciones
Creamos un Store Procedure el cual recibe como parámetro la velocidad y en base al modelo nos predice la distancia. Para esto usamos la posibilidad de pasarle parametros a sp_execute_external_script
-- CREAMOS UN SP QUE RECIBE UNA VELOCIDAD
DROP PROCEDURE IF EXISTS DBO.USP_PREDICT_VELOCIDAD;
CREATE PROCEDURE DBO.USP_PREDICT_VELOCIDAD
@VELOCIDAD INT
AS
DECLARE @speedmodel varbinary(max) =
(SELECT model FROM [dbo].[TRIGGERDB_MODEL] WHERE MODELNAME = 'ModeloCars');
EXECUTE sp_execute_external_script
@language = N'R'
, @script = N'
current_model <- unserialize(as.raw(speedmodel));
new <- data.frame(NewData);
predicted.distance <- rxPredict(current_model, new);
str(predicted.distance);
OutputDataSet <- cbind(new, ceiling(predicted.distance));
'
, @input_data_1 = N'select @vSpeed as speed '
, @input_data_1_name = N'NewData'
, @params = N'@speedmodel varbinary(max),@vSpeed int'
, @speedmodel = @speedmodel
,@vSpeed = @VELOCIDAD
WITH RESULT SETS (([new_speed] INT, [predicted_distance] INT))
go
-- PRUEBAS DE PREDICCION
EXECUTE DBO.USP_PREDICT_VELOCIDAD 300
EXECUTE DBO.USP_PREDICT_VELOCIDAD 77
LAB 02 – SQL Server Machine Learning: sp_execute_external_script
La integración de los servicios de Machine Learning con SQL Server se hacen por medio del store procedure sp_execute_external_script el cual permite la ejecución de código R o Python.
Este SP tiene 3 tipos de salidas.
Resultset
Model
Plot
Ejemplo 1: Hola Mundo
Este primer ejemplo ejecuta un codigo con la instruccion print.
Para poder ejecutar el código usaremos el SQL Server management studio conectándonos a la instancia de SQL Server.
EXEC sp_execute_external_script
@language = N'R',
@script = N'print(''Hola Mundo'')'
GO
EXEC sp_execute_external_script
@language = N'Python',
@script = N'print(''Hola Mundo'')'
GO
Ejemplo 2: Usar imput_data_1 con una consulta TSQL
En este ejemplo vamos a usar los parámetros de entrada que nos ofrece el store procedure para poderle pasar una consulta TSQL.
-- usando R
EXECUTE sp_execute_external_script
@language = N'R',
@script = N' OutputDataSet <- InputDataSet;',
@input_data_1 = N' SELECT name FROM sys.databases order by 1;',
@input_data_1_name = N''
WITH RESULT SETS (([NombreBase] nvarchar(max) NOT NULL));
EXECUTE sp_execute_external_script
@language = N'R',
@script = N' OutputDataSet <- Datos;',
@input_data_1 = N' SELECT name FROM sys.databases order by 1;',
@input_data_1_name = N'Datos'
WITH RESULT SETS (([NombreBase] nvarchar(max) NOT NULL));
--- con python
EXECUTE sp_execute_external_script
@language =N'Python',
@script=N'OutputDataSet=InputDataSet',
@input_data_1 = N'SELECT name FROM sys.databases order by 1;'
WITH RESULT SETS (([NombreBase] nvarchar(max) NOT NULL))
EXECUTE sp_execute_external_script
@language =N'Python',
@script=N'OutputDataSet=Datos',
@input_data_1 = N'SELECT name FROM sys.databases order by 1;',
@input_data_1_name = N'Datos'
WITH RESULT SETS (([NombreBase] nvarchar(max) NOT NULL))
Ejemplo 3: Usar el dataset Iris de R
Este ejemplo muestra un código R el cual se usara el famoso dataset Iris y su resultado se retorna como dataset de SQL Server
En nuestra primer parte lo que haremos es la instalación de los distintos componentes que necesitaremos para este HOL.
1. Instalar SQL Server 2017 Developers
Lo primero que vamos a instalar son los componentes de SQL Server (Engine y servicios de Machine Learning).
Luego de haber bajado el medio de instalación bajaremos el .iso completo para poder empezar con la instalación.
Al abrir el medio iniciamos el setup de SQL Server para asi instalar en modo Standalone los componentes.
Seleccionamos los servicios a instalar, en nuestro caso:
SQL Server Engine
Machine Learning Services (R & Pyhon)
Machine Learning Server
[!WARNING] Es recomendable instalar Machine Learning Server en un servidor independiente al motor de base de datos, para este LAB lo instalaremos en el mismo servidor pero es solo a los efectos de demos.
También elegimos la ruta donde alojamos el SQL Server. Se recomienda que los binarios esten en un disco separado de las bases de datos.
Instalamos como default instance o bien con un nombre si ya tenemos una instancia en ese servidor.
Cambiamos el inicio del agente a modo automático y seleccionamos la opción de Grant Perform Volume.
Seleccionamos el tipo de autentificación, en nuestro caso hemos elegido mixta a la cual le hemos ingresado la clave del SA como así también el usuario de AD sysadmin.
En la solapa “Data Directory” seleccionamos la ubicación de nuestros archivos de base de datos, si bien para una prueba pueden estar en el mismo disco C , lo recomendable es separar las bases en discos distintos al C para luego continuar con el instalador Revisamos si esta todo ok y procedemos con la instalación.
2. Instalar herramientas administrativas
En este paso vamos a instalar el SQL Server Management Studio , el cual usaremos para conectarnos a nuestro servidor de SQL Server con Machine Learning. Luego de haber bajado el medio de instalación referenciado en los links anteriores vamos a iniciar el proceso de setup.
3. Configurar la instancia de SQL Server
Luego de tener instalado nuestro motor con sus servicios y herramientas correspondientes vamos a continuar con la habilitación de ML en la instancia, por defecto viene en modo disable. Para ello usaremos el comando SP_configure conectados desde nuestro SSMS.
EXEC sp_configure 'external scripts enabled', 1
RECONFIGURE WITH OVERRIDE
Reiniciamos la instancia de SQL Server (servicios) y verificamos que este habilitado el ML.
EXEC sp_configure 'external scripts enabled'
Podemos ahora hacer un testeo simple de funcionamiento
EXEC sp_execute_external_script @language =N'R',
@script=N'
OutputDataSet <- InputDataSet;
',
@input_data_1 =N'SELECT ''Triggerdb Consulting SRL'' AS empresa'
WITH RESULT SETS (([empresa] varchar(50) not null));
GO
En el mes de octubre de 2018 participe como orador en el evento NetConfAr en el cual hable sobre el uso de Machine Learning sobre SQL Server. Comparto aquí la entrevista que me hicieron post charla
Comparto con ustedes el video de mi charla completa que di en el evento Net Conf Ar edición 2018 en el cual hable de como hacer Machine Learning con los servicios de SQL Server.