Machine Learning archivos - Blog SQL Server, Power BI y BI

DataSummit 2019

by Maxi Accotto | May 29, 2019 | Azure, BI, Machine Learning, PowerBI, TSQL

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 link comparto el material de dicha charla

Algunas imágenes del evento

SQL Machine Learning: Modelos

by Maxi Accotto | Dec 24, 2018 | Machine Learning

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)

INSERT INTO CARSPEED
EXEC SP_EXECUTE_EXTERNAL_SCRIPT
        @LANGUAGE = N'R'
        , @SCRIPT = N'CAR_SPEED <- cars;'
        , @INPUT_DATA_1 = N''
        , @OUTPUT_DATA_1_NAME = N'CAR_SPEED'

SELECT * FROM CARSPEED

Paso 3: Crear un modelo de regresión lineal

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

SQL Machine Learning: SP_execute_external_script

by Maxi Accotto | Dec 24, 2018 | Machine Learning

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

EXECUTE sp_execute_external_script
   @language = N'R',
   @script = N'OutputDataSet <- head(iris)',
   @input_data_1 = N''
WITH RESULT SETS (([Sepal.Length] float, [Sepal.Width] float,
                   [Petal.Length] float, [Petal.Width] float,
                   [Species] varchar(25)));

SQL Machine Learning: Instalación y configuración

by Maxi Accotto | Dec 23, 2018 | Machine Learning

Instalación y configuración del ambiente.

Requerimientos

SQL Server 2017 developer edition https://go.microsoft.com/fwlink/?linkid=853016
Management Studio
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017

Instalación de componentes

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

EXEC sp_execute_external_script  @language =N'Python',
@script=N'OutputDataSet=InputDataSet',
@input_data_1 = N'SELECT ''Triggerdb Consulting SRL'' AS empresa'

Entrevista Net Ar Conf 2018

by Maxi Accotto | Dec 18, 2018 | Machine Learning

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

Video charla Machine Learning con SQL Server

by Maxi Accotto | Dec 6, 2018 | Machine Learning

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.

« Older Entries