¿Python en SQL Server?

Sí, desde la versión de SQL Server 2016 se introdujo la idea de ejecutar scripts en lenguaje R dentro de SQL Server, en la versión 2017 ésta capacidad pasó a llamarse Machine Learning Services y se mejoró para incluir la capacidad de ejecutar código python. Este artículo ofrece una guía simple sobre cómo se puede ejecutar código Python dentro de SQL Server y transformar datos de nuevas formas.

La puesta en marcha

La capacidad de ejecutar código Python no está permitida de forma predeterminada en SQL Server. Hay un par de etapas que debe completar antes de poder ejecutar el código. Primero, debe instalar Machine Learning Services (MLS) como parte de la configuración de SQL Server. Si no lo has hecho sigue los pasos de la guía oficial de Microsoft en el siguiente enlace instalar MLS.

Una vez instalado el MLS, ahora debemos habilitar la instancia para permitir la ejecución de scripts. Ejecuta el código* que copiamos debajo (como administrador)*.

EXEC sp_configure 'external scripts enabled', 1;
RECONFIGURE;
GO

Esto permitirá la ejecución de secuencias de comandos mediante el servicio Launchpad y el procedimiento almacenado sp_execute_external_script.

Tu primer programa en Python

Una vez que hemos configurado SQL Server, podemos empezar a ejecutar código Python. Enviamos nuestro código como una variable de cadena, por lo que no trabajaremos con archivos. En su lugar, declaramos una variable, establecemos un valor de cadena que contiene el código y luego lo ejecutamos. Usamos sp_execute_external_script , que toma varios parámetros. En esta sección solo usaremos dos de estos:

@language – el idioma del código en nuestro script
@script – el texto de nuestro programa

Para comenzar, vamos a crear una base de datos que podamos usar. Haremos esto con el siguiente código:

CREATE DATABASE MLDemo
GO
USE MLDemo
GO

Una vez que tengamos esto, creamos un script simple.

for i in {x for x in range(10) if x % 2 == 1}
    print(i)

Si ejecutamos el código Python obtendremos el siguiente mensaje

Ahora ejecutemos esto en SQL Server. Tomaré mi script y lo incluiré como valor para el parámetro del script.

EXEC sys.sp_execute_external_script
 @language = N'Python',
 @script = N'for i in {x for x in range(10) if x % 2 == 1}:
     print(i)
 '

Al ejecutarlo, obtenemos el resultado siguiente

Ya tenemos nuestro primer código Python en SQL Server! 😀

Ejecutar un script sin datos no es muy útil. Veamos ahora cómo podemos pasar datos a un script. El procedimiento sp_execute_external_script incluye un parámetro, llamado @ input_data_1, que proporciona entrada para nuestro código Python. Esta es la consulta que pasamos, o esencialmente el conjunto de resultados de esa consulta. Podemos usar un nombre para esta consulta con otro parámetro, @ input_data_1_name, para crear una variable que usa nuestro código Python.

Esto se vuelve confuso y significa que estamos acoplando tanto el código T-SQL como el código Python. Veamos cómo funciona esto. Escribiré una consulta corta aquí para obtener un valor escalar simple. Haré esto sin un nombre, por lo que accederemos dentro del script como InputDataSet. La salida predeterminada correspondiente es OutputDataSet. Simplemente repetiremos lo que ingresa para verificar que todo esté bien.

exec sp_execute_external_script 
 @language = N 'Python' , @script = N 'OutputDataSet = InputDataSet' , @ input_data_1 = N 'SELECT n from (values (1), (2), (3)) a (n)'

Al ejecutarlo el resultado es el siguiente

Tengamos en cuenta que no tenemos metadatos para los resultados, y simplemente hemos repetido la entrada como salida. Esto muestra que podemos pasar datos a nuestro script como una consulta. Esta debe ser una consulta de tipo NVARCHAR y cómo buena práctica lo mejor es definir la consulta por separado, como tengo aquí:

DECLARE @InputQuery NVARCHAR ( 2000 ) = N ' 
SELECT n from (valores (1), (2), (3)) a (n) 
' 
INSERT dbo . Resultado1 
EXEC sp_execute_external_script @language = N 'Python' , @script = N 'OutputDataSet = InputDataSet' , @ input_data_1 = @InputQuery

Obtener resultados específicos

No teníamos columnas enumeradas anteriormente, pero podemos cambiar esto al incluir la cláusula WITH RESULT SETS en nuestro código. Esto nos permite describir los resultados con metadatos sobre el nombre de una columna y el tipo de datos. Cuando hacemos esto, especificamos el conjunto de resultados entre paréntesis y luego cada columna como lo haríamos para una definición de tabla. En mi caso, tengo una columna arriba, pero agregaré un segundo debajo para mostrar esto.

En este caso, ingresaré dos columnas y definiré aquellas con nuevos nombres como salida.

DECLARE @InputQuery NVARCHAR(2000) = N'
SELECT n, o 
FROM (values (1,10), (2,20), (3,30)) a(n, o)
'
EXEC sp_execute_external_script 
@language =N'Python',
@script=N'OutputDataSet = InputDataSet', 
@input_data_1 = @InputQuery
WITH RESULT SETS
( (  INT NOT NULL,
[iplusTen] INT NULL)
)

La cláusula WITH RESULT SETS es parte de la instrucción EXEC () y nos permite definir múltiples conjuntos de resultados de formas específicas. La razón por la que hay dos conjuntos de paréntesis es para conjuntos múltiples. El conjunto exterior de paréntesis es para la propia cláusula WITH RESULT SETS. El conjunto interior es para el primer conjunto de resultados.

Obtener resultados como salida es bueno, pero a veces quiero guardar esos datos en otro lugar y usarlos en otro conjunto de código. Puedo hacer eso con INSERT .. EXEC. Para mostrar esto, crearé una tabla para almacenar mis datos. En este caso, usaré el conjunto de resultados de dos columnas de arriba.

CREATE TABLE TwoNumbers
(   LowNumber  INT
  , HighNumber NUMERIC(4, 1));

Ahora, usaré este código para insertar datos de mi llamada a procedimiento en esta tabla:

DECLARE @InputQuery NVARCHAR(2000) = N'
SELECT n, cast(o as float) 
 FROM (values (1,10.1), (2,20.2), (3,30.3), (4, 40.4)) a(n, o)
 WHERE n < 4
';
INSERT dbo.TwoNumbers 
EXEC sp_execute_external_script 
@language =N'Python',
@script=N'OutputDataSet = InputDataSet', 
@input_data_1 = @InputQuery
SELECT tn.LowNumber, tn.HighNumber FROM dbo.TwoNumbers AS tn;

Y nos da el siguiente resultado

Usar paquetes de Python

Una de las cosas que a menudo queremos hacer con los datos es procesarlos con funciones de Python. Veamos un pequeño ejemplo de esto. Llamaré a la función SUM () desde el paquete numpy. Para hacer esto, necesito usar el mismo tipo de estructura de código que uso en Python para obtener la función.

DECLARE @InputQuery NVARCHAR(2000) = N'
SELECT n 
 FROM (values (1), (2), (3), (4)) a(n)
';
EXEC sp_execute_external_script 
@language =N'Python',
@script=N'
import numpy as np
import pandas as pd
OutputDataSet = pd.DataFrame(np.sum(InputDataSet))', 
@input_data_1 = @InputQuery

En este caso, necesitamos dos paquetes: numpy y pandas. numpy nos da la función de suma que funciona con un marco de datos. Pandas convierte el resultado escalar de nuevo en un marco de datos. Si no usamos estos paquetes, obtendremos otros errores.

Puede usar cualquiera de los paquetes que vienen con la distribución Python de Anaconda 3.5.2 en SQL Server 2017, o puede agregar un paquete propio.

Este es un breve vistazo a cómo comenzar a ejecutar código Python dentro de SQL Server con SQL Server 2017+. Examinamos los conceptos básicos para habilitar y usar la función sp_execute_external_script que enviará la llamada al entorno de Python en la instancia del host. Analizamos brevemente algunas de las opciones para la entrada y el tratamiento de los resultados.

No Comments

TAGS : Python SQL Server

BI, blog, Business Intelligence, Formación, Integración de Datos, Python, SQL

¿Código Python dentro de SQL Server?

¿Python en SQL Server?

La puesta en marcha

Tu primer programa en Python

Obtener resultados específicos

Usar paquetes de Python

Francisco Rodriguez Alfaro