Con la llegada de SQL Server 2017 (aka vNext) junto con el hype del data science Microsoft está «abriendo la puerta» a otros los lenguajes dentro de SQL Server. Si nos remontamos a la época anterior a SQL Server 2005 las opciones disponibles resultaban complejas (código C nativo) y potencialmente peligrosas al basarse en la carga de DLLs sin limitaciones, sin sandboxing, etc. Con la llegada de SQL Server 2005 se abrió la puerta al uso de .NET con la integración CLR que aportaba una mayor sencillez a la hora de programar y mayor seguridad. A día de hoy el uso de la integración con CLR sigue siendo una herramienta muy potente e infravalorada en muchos escenarios.

Con la llegada de SQL Server 2016 el lenguaje R se integró con la finalidad clara de aportar un lenguaje más eficiente para tareas de análisis de datos. En SQL Server 2017, utilizando los mismos mecanismos de extensibilidad, se ha incluido un nuevo lenguaje: Python. Este lenguaje es muy popular y no únicamente desde el punto de vista de data science, donde realmente es relativamente «nuevo», sino como lenguaje de scripting en general. De hecho desde principio de los 90 este lenguaje ha ido ganando popularidad en plataformas Unix especialmente su parte de lenguaje funcional. En este post vamos a analizar cómo es el funcionamiento en la CTP 2.0 de la integración de dicho lenguaje y veremos posibles usos más alejados del data science.

El primer paso para poder utilizar Python en SQL Server es seleccionar esta característica durante la instalación de SQL Server 2017:


Python en SQL Server

Una vez instalada la instancia deberemos habilitar el uso de scripts externos (tanto para R como para Python necesitamos este paso):

exec sp_configure 'external scripts enabled',1
go
reconfigure -- reboot
go

Este cambio de configuración no entrará en efecto tras un reconfigure (como suele ser habitual) ni siquiera usando la opción with override. Por tanto necesitaremos un reinicio de la instancia tras el cambio de configuración.

Otra peculiaridad, al menos en la CTP 2.0, es que existe un proceso de setup que tiene que realizarse al habilitarse la característica y antes del primer uso. Este proceso automático puede retrasarse varios segundos y podremos ver en nuestro monitor de actividad la espera asociada (SATELLITE_SERVICE_SETUP):

Una vez tenemos el lenguaje listo para su uso vamos a plantear tres posibles escenarios de uso. El primero va a ser la suma simple de una lista de valores. Esto nos permitirá medir el rendimiento aritmético de los scripts en Python cuando la fuente de datos es una consulta SQL Server. Para generar una lista de números utilizaremos la archiconocida función GetNums:

CREATE FUNCTION [dbo].[GetNums](@n AS BIGINT) RETURNS TABLE
AS
RETURN
  WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
  SELECT n FROM Nums WHERE n <= @n;

GO

Lanzaremos una suma de 1000 números llamando al procedimiento sp_Execute_External_Script y la compararemos con un SUM sencillo desde TSQL:

-- Suma números
declare @output int

    EXECUTE sp_execute_external_script
      @language = N'Python'
    , @script = N'

retorno = 0
for row in numbers.itertuples():
  retorno= retorno+ (int(row[1]))

output=retorno

'

    , @input_data_1 = N'select n from getnums(1000)'
    , @input_data_1_name = N'numbers'
    , @params = N'@output int OUTPUT'
    , @output = @output OUTPUT;

select @output
go
select sum(n) from getnums(1000)

Cuando analizamos el rendimiento de ambas opciones vemos que tenemos una penalización muy importante por ejecutar un script de Python, que retrasa la ejecución a más de 400 milisegundos:

Esto ya nos dice de entrada que el uso de estos lenguajes de scripting queda relegado a operaciones que no necesiten ser muy rápidas (no las típicas OLTP desde luego). Además el coste computacional del cálculo de enteros resulta menos eficiente que realizarlo dentro de TSQL por lo que no sería recomendable para este tipo de operaciones. A continuación vamos a probar otro escenario donde exista una componente algorítmico más elevado, aunque al final estemos trabajando básicamente con cálculos aritméticos. Concretamente vamos a probar con el cálculo de CRC32 sobre cadenas de texto. Debido a que implementar este cálculo en TSQL es realmente una mala idea, utilizaremos como «contrincante» para Python la librería SQL# (http://www.sqlsharp.com) que lo implementa mediante la CLR:

-- Calcular CRC32
declare @output varchar(max) 
EXECUTE sp_execute_external_script
    @language = N'Python'
, @script = N'

import binascii
retorno = str("")
for row in cadenas.itertuples():
  retorno = retorno + str(binascii.crc32(row[1])) + '',''
output=retorno

'
, @input_data_1 = N'select CONVERT(VARBINARY(MAX),''esto es un prefijo cualquiera'' +  convert(varchar(10),n) + ''y esto es un sufijo cualquiera'') from getnums(1000)'
, @input_data_1_name = N'cadenas'
, @params = N'@output varchar(max) OUTPUT'
, @output = @output OUTPUT;
select @output
go
SELECT 
  SQL#.Util_CRC32(CONVERT(VARBINARY(MAX),
  'esto es un prefijo cualquiera' +  convert(varchar(10),n) + 'y esto es un sufijo cualquiera'
  )
)
from getnums(1000)

Una primera prueba con 1000 filas nos devuelve un resultado similar al que obteníamos con la suma. Es decir, los coste de inicialización son muy elevados y por tanto el coste en el caso de Python se dispara versus SQL#:

Si aumentamos 100 veces el número de filas y calculamos el CRC32 para 100000 filas seguimos viendo que las diferencias de duración siguen siendo muy importantes:

Como curiosidad indicar que el consumo de CPU que aparece en profiler no es fiable para este tipo de ejecuciones ya que únicamente está teniendo en cuenta una parte del proceso. Otra parte, la mayor en este caso, la tendremos en el proceso externo de Python y no quedará contabilizado en profiler.

Por último recurrimos a un escenario donde los lenguajes Python, Perl, etc. siempre han destacado, la gestión de expresiones regulares. Para obtener un único resultado lo que haremos será extraer mediante una expresión regular un valor numérico de dentro de una cadena y sumaremos el resultado:

-- Sacar un número de una cadena con una expresión regular y sumarlos
declare @output int
EXECUTE sp_execute_external_script
    @language = N'Python'
, @script = N'

import re
pattern = re.compile("[a-zA-Z ]*([0-9]+)[a-zA-Z ]*")
retorno = 0
for row in numbers.itertuples():
  retorno= retorno+ int(pattern.match(str(row[1])).group(1))

output=retorno

'
, @input_data_1 = N'select ''esto es un prefijo cualquiera'' +  convert(varchar(10),n) + ''y esto es un sufijo cualquiera'' from getnums(1000)'
, @input_data_1_name = N'numbers'
, @params = N'@output int OUTPUT'
, @output = @output OUTPUT;
select @output
go

SELECT sum(convert(int,
  SQL#.RegEx_CaptureGroup(
  'esto es un prefijo cualquiera' +  convert(varchar(10),n) + 'y esto es un sufijo cualquiera',
  '[a-zA-Z ]*([0-9]+)[a-zA-Z ]*',
  1, NULL, 1, -1, ''))
)
from getnums(1000)

De nuevo pondremos como «contrincante» a la la librería SQL# y más concretamente a la función RegEx_CaptureGroup. Primero testearemos esta extracción con 1000 filas y la ejecutaremos tres veces para obtener valores de tiempos medios:

Podemos ver que de nuevo el coste de inicialización de Python marca la diferencia, haciendo este lenguaje no competitivo en este aspecto. Sin embargo si aumentamos el volumen de datos hasta 100000 filas lo que nos encontramos es justo el escenario contrario:

Podemos ver como en este escenario de parseo de cadenas mediante expresiones regulares Python brilla con fuerza, siendo más rápido que la función CLR. Creemos que la razón de esto es que en Python podemos reutilizar la expresión regular compilada para cada fila mientras que con la CLR, al ser una función escalar, sufrimos con una ejecución menos eficiente en cada llamada. Probablemente con una implementación distinta en la CLR, donde el acceso a datos se encontrara dentro del código CLR y se pudiera también precompilar la expresión, el rendimiento habría sido igual o mejor con la CLR.

La conclusión que sacamos de estas pruebas es que aunque Python se introduce con la intención de ser utilizado para data science también es posible sacarle partido en aquellas tareas donde TSQL no es muy eficiente. Concretamente las expresiones regulares tienen una gran potencia para procesar, extraer, limpiar información en forma de texto. Por otra parte la flexibilidad que tiene un lenguaje de script es un factor a considerar, ya que la CLR por ahora requiere de un proceso de compilación, creación de ensamblado, importación, etc. que lo hace mucho menos ágil. Sería muy interesante que al igual que se ha incluido un compilador de C para los procedimientos almacenados compilados de In-Memory se facilitara la posibilidad de utilizar código CLR al vuelo. Sabemos que en muchos entorno el tener que mantener una librería externa, gestionar el versionado de ésta respecto a la base de datos, etc. es algo que genera reticencias por parte de los administradores de base de datos. Si fuese sencillo embeber código .NET y que el propio motor se encargara de su compilación y carga en memoria de forma dinámica tendríamos a nuestra disposición toda la potencia de .NET dentro de SQL Server tal y como la tenemos disponible desde SQL Server 2017 con Python.

 

Rubén Garrigós

Rubén Garrigós is an expert in high-availability enterprise solutions based on SQL Server design, tuning, and troubleshooting. Over the past fifteen years, he has worked with Microsoft data access technologies in leading companies around the world. He currently is a Microsoft SQL Server and .NET applications architect with SolidQ. Ruben is certified by Microsoft as a Solution Expert on the Microsoft Data Platform (MSCE: Data Platform) and as a Solution Expert on the Microsoft Private Cloud (MSCE: Private Cloud). As a Microsoft Certified Trainer (MCT), Ruben has taught multiple official Microsoft courses as well as other courses specializing in SQL Server. He has also presented sessions at official events for various Microsoft technologies user groups.

Latest posts by Rubén Garrigós (see all)