SQL Server 2019 CTP 2.3 Big Data containers e inteligencia artificial todo en uno

Puedes ver un webcast sobre este tema en https://info.microsoft.com/ww-landing-intro-sql-server-2019.html

Hace tiempo (entendiendo esta antigüedad en el contexto de lo que es la informatica) que Spark con su Scala me parece un lenguaje que hay que empezar a dominar , ahora más aún, en SQL 2019 ,  se puede poner todo junto, SQL Server y Spark y puede usarse todos los lenguajes de inteligencia artificial, Python, R, etc para consultar datos, no solo en los tradicionales SQL servers sino también en clusters de bigdata.

En el webcast podreis ver como nos hablan de uno de los retos que más horas nos han hecho invertir en nuestros proyectos, los métodos para integración de datos, tanto los procesos ELT o ETL como lo que llaman Data virtualization, básicamente dos aproximaciones a tener los datos todos en un solo lugar, ya sea real copiando datos, o virtual  dejando los datos en su situación y virtualizando su acceso sin replicar o mover datos aunque es un poco tendencioso, porque cuenta todas las maldades del ETL ELT  pero no los relacionados con la virtualizacion  y son unos cuantos. Aún así es interesantisimo de ver, en este caso PolyBase  es quien acude al rescate puesto que utiliza su potencia para consultar cualquier origen de datos.

Siguiendo con esa virtualizacion podrás ver como con la misma estructura, se pueden consultar no solamente datos de otros gestores de base de datos como teradata u oracle, sino también clusters de spark o ficheros alojados en un hdfs. También veras como pueden funcionar varios SQL Servers en paralelo o con spark. También veras el concepto scale-out data pool para poder crear caches intermedias de estos datos, y el concepto de compute pool, paralelizando y creando clusters de SQL SErver al mas puro estilo hadoop pero con un lenguaje conocido y un interfaz amable para hacerlo.

De lo mas destacable, la promesa de que con Microsoft, todos los componentes que están dentro del cluster big data, van a ser compatibles en los cambios de versión , esto a los que estamos en el mundo Microsoft nos parece natural, pero  en mis propias carnes he sufrido lo poco natural que es en el mundo del big data.

La tecnología Kubernetes al rescate, algo que también podras ver el video, como a través de esta tecnología se puede desplegar un cluster no importa si es en  Azure ya sea en IaaS o en servicio como Azure Kubernetes o es On-premisses.

Los logs de todas los nodos acaban en una base de datos elastic search para poder ser administradas y consultadas. Muy interesante.

Seguridad y mantenimiento, unificado a traves de un single sing-on con directorio activo.

El dibujo de como Microsoft ve la plataforma de Inteligencia Artificial también es muy ilustrativa, puedes usar  Spark STreaming, SSIS,  para leer datos y llevarlos a una instancia de SQL SErver o a HDFS o a unos data pools… entonces puedes empezar a preparar y entrenar tus modelos con Spark Machine Learing o con SQL Server Machine Learning SErvices, y a traves de un modelo  servir esos datos a las herramientas de BI o aplicaciones, también pudes usar notebooks en Azure data Studio, los notebooks son una de las herramientas prefereidas para AI.

 

Disfrutad el webcast

 

 

 

Conceptos Básicos. Common Table Expressions o CTE’s

Una de las funcionalidades que vienen bien tanto a desarrolladores como a administradores de bases de datos son las CTE’s. Una definición , poco rigurosa, pero muy ilustrativa sería describirlas como vistas temporales en el ámbito de una consulta, es decir, a un query mas o menos complejo se le puede poner un alias y usarlo varias veces. Si te has enfrentado a los dialectos SQL y te ha tocado hacer subqueries complejas por ejemplo para sacar las ventas de este año y las del año pasado, haciéndolo con subconsultas da queries muy largas (la complejidad depende más de lo que se busque) en el que hay repetidas varias veces la misma premisa.

Vamos a ver lo que decimos a través de un ejemplo, vamos a calcular las ventas por año de nuestra base de datos adventure works. Para eso lo primero es tomar la cabecera de ventas y las lineas de venta, en la cabecera tenemos el año, en las lineas el importe, para comenzar haríamos algo como esto
<preclass=”lang:tsql decode:true ” title=”cabecera y lineas de venta”>SELECT * FROM Sales.SalesOrderHeader soh INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID;

Obviamente ahí no hay ningún cálculo, solo hemos puesto el join inicial que nos permite unir tablas y obtener los datos  que necesitamos -fecha e importe- en la misma consulta.

El valor por años de las ventas sería algo como esto

Ahora vamos a volver la premisa inicial, vamos a obtener en la misma fila las ventas de un año y las del año anterior, para eso hemos de hacer dos subqueries y unirlos

El resultado sin CTES sería algo así

Si usamos CTE’s la idea es poder ponerle un alias al primero de los conjuntos y volver a usarlo cuantas veces sea necesario, por ejemplo así

Mucho mas limpio, pero no solo eso sino que mucho más versátil para peticiones y cambios, por ejemplo si en lugar de agrupar por año queremos año y mes el cambio sería solamente en el query de la CTE no en toda la parte de abajo, con subqueries habría que cambiarlo en las dos y eso es bastante más lioso

Otro ejemplo imaginemos que queremos un año atras y dos años atras…

 

 

Aunque solamente esta funcionalidad ya sería más que suficiente para resaltar las cualidades de las CTE’s, aún tienen muchas más, ya que pueden ser Recursivas, y de esa forma nos ayudan a recorrer árboles, o a crear datos, dimensiones, en breve publicaré el siguiente apunte de la serie de cte’s en el que veremos esas funciones recursivas con algunos ejemplos y su utilidad.

 

 

¿Quien bloquea? y ¿que hace? II. Agendando la detección

A partir de la consulta que detecta quien bloquea y que hace podemos construir una forma algo más atrevida de monitorización de bloqueos. La idea es la siguiente, con el query que detecta los bloqueos, guardamos el resultado en una tabla temporal. y esperamos un tiempo prudencial -y configurable- y volvemos a ejecutar la misma sentencia. Si los mismos procesos siguen bloqueados por los mismos actores, es tiempo de reportar ese bloqueo. Es decir, es normal que en un momento del tiempo haya un bloqueo, es cosa de la concurrencia, pero no es normal que ese bloqueo dure mucho (el cuanto es ese mucho depende de la aplicación).
En el caso de que el bloqueo persista, se notifica, en nuestro caso a través de mail. Si has entendido todos estos conceptos y solo quieres el código…

Si poor el contrario quieres entenderlo un poquito mejor y ver como funciona y como se agendaría, aquí tienes el video de demostración.

TSQL- ¿Quien bloquea y que hace?

Son muchas las ocasiones en las que ante un problema de bloqueos podemos sentirnos impotentes, la falta de información de que está pasando puede atenazarnos e impedirnos hacer nuestra labor, descubrir quien bloquea y que está haciendo.
Es cierto que a través de procedimientos del sistema, como sp_who o sp_who2 y algunas versiones que circulan por la red podemos tener cierta información de este tipo de cosas. La verdad es que yo nunca fuí muy fan de esos métodos, me parece que podemos personalizar más y mejor la información que nos da el sistema y podemos encontrar exactamente lo que estamos buscando.

Voy a poner aquí el resultado final, es decir el código terminado que determina quien bloquea y que hace, pero en las próximas lineas contaré el porqué de cada una de las decisiones.

lo primero que puede notar un observador detenido es que mientras que las consultas bloqueadas se obtienen de la DMV sys_dm_exec_requests, los bloqueadores se obtienen sin embargo de exec_conections, el motivo es que el bloqueador puede haber terminado su ejecución, al no estar bloqueado y por tanto el comando desaparece de las peticiones actuales, por lo que no podríamos obtener mas datos de la consulta que nos bloquea, mientras que usando el campo most_recent_sql_handle de la dmv connections podemos obtener esa información.

Se podría obtener tambien el plan de ejecución del bloqueador, pero en este momento no me parece relevante.

En los próximos días escribiré algunas mejoras a este procedimiento.

En el video de demostración, verás, no solamente como usar este comando sino también como obtener el plan de ejecución y visualizarlo en SQL Server Management Studio.

Os dejo el video de demostración

 

Hasta el próximo!

Niveles de aislamiento basados en instantáneas. Vídeo comparativa

Como prometiamos, ha llegado el momento de ver una demostración sobre los niveles de aislamiento y sus repercusiones. En la demostración vamos a demostrar cuando se genera un bloqueo, vamos a ver el tema de lecturas repetibles y que significa y vamos a comprobar la diferencia entre read_commited_snapshot y snapshot isolation level

El siguiente script es el script de iniciación de los datos, si quieres probar en tu propio sistema esta demostración, sería bueno que lo copiaras 🙂

En la demostración, podreis comprobar como se comportan los updates, los bloqueos en función del nivel de aislamiento que estemos usando en nuestra conexión. A pesar de no ser muy larga la demostración es bastante intensa, sobre todo para aquellos que seáis noveles en estos aspectos.

Quedo a la espera de vuestros comentarios! Gracias y hasta el próximo vídeo demostración.

 

Niveles de aislamiento basados en instantaneas

Hemos hablado de nolock y ahora vamos a hacer un pequeño viaje por los niveles de aislamiento basados en instantáneas. Primero un poco de contexto.
En el mundo de las bases de datos y los estándares se habla de las propiedades que deben cumplir los motores de bases de datos en cuanto al ACID , atomicidad, consistendia, aislamiento y durabilidad. No voy a entrar en esas explicaciones, porque son largas y quizá no vengan al caso en este artículo. Sin embargo si que necesitamos saber algo de la parte del aislamiento.
Los problemas que se enumeran sobre los niveles de aislamiento son
* Lecturas sucias
* Lecturas no repetibles
* Lecturas fantasma
Aquí podéis encontrar informacion acerca del acid
https://es.wikipedia.org/wiki/Aislamiento_(ACID)

Dicho esto, al final, los motores de bases de datos han de implementar estos atributos estandar, y cada uno lo hace según su propio criterio. En general, y podríamos decir que hay dos metodos, basarnos en bloqueos y basarnos en instantáneas. Intento explicar las diferencias:

Supongamos que tenemos dos conexiones con una transaccion abierta, la transaccion a modifica un registro y la transaccion b
* Si estamos basados en bloqueos , la transacción B tendrá que esperar a que la transacción A acabe, puesto que el registro está modificado, pero no confirmado. Si usamos el hint nolock, veremos el datos sin confirmar (lecturas sucias)

*Si estamos basados en instantáneas  entonces lo que sucede es que cuando se modifica el registro, la ultima versión confirmada de la transacción se guarda para que si otro usuario la reclama pueda obtenerla, esta vez sin bloqueos puesto que hay un registro que tiene un valor ya confirmado (aunque potencialmente vaya a cambiar)

 

Si las instantáneas se destruyen toda vez que la transacción que modifica el registro (transaccion A) acaba, y dentro de la misma transaccion B volvemos a ejecutar la misma sentencia de consulta, el resultado será que dentro de la transacción B hemos visto dos valores distintos para el registro en cuestion, produciendose así lecturas no repetibles.

Las lecturas fantasma sin embargo se producen cuando esa selección se hace sobre todos los registros y pueden aparecer o desaparecer registros del final.

En SQL Server tradicionalmente y por defecto el modelo de implementación está basado en bloqueos, sin embargo desde hace ya bastantes versiones, se puede habilitar para estar basado en instantaneas. La forma de hacerlo sería así

Este comando habilita a la base de datos demo2 para implementación basada en instantáneas, lo que hace que automaticamente se eliminen muchos de los bloqueos que podamos tener, pero OJO no es recomendable cambiar este valor sin probar la aplicacion que use SQL bien antes, ya que puede que los desarrolladores se hayan basado en que hay bloqueos para ciertas implementaciones, y eliminar esos bloqueos podría hacer que la aplicación no funcionara bien. Sin embargo, si eres desarrollador y estas creando una aplicacion nueva y en el pasado los bloqueos te han mareado mucho… no lo dudes, desde el minuto 0 hablita este nivel de aislamiento.

Otros gestores, como Oracle, por defecto se comportan con la implementación basada en instantáneas.

Además del nivel de aislamiento por defecto, se puede habilitar un nivel mas de instantaneas, aunque para hacerlo hay primero que habilitarlo en la base de datos y además usar un comando SET TRANSACTION ISOLATION LEVEL

la forma de habilitarlo es la siguiente

En los proximos dias, publicaré una demostración sobre estos niveles de aislamiento y como se comportan así en unos pocos minutos podremos tener una formación más completa sobre la materia

With NoLock

¿Cuantas veces ante un bloqueo hemos usado with nolock? En muchas ocasiones incluso es posible que lo hayamos recomendado. Lo que dice el sentido común es que es posible que leamos transacciones que no han sido validadas, y esto, no parece que sea muy importante.

Es decir, si por ejemplo usamos una sentencia select sum(ImporteFacturado) from facturas  with(nolock) ¿que puede pasar?

En realidad lo que te van a comentar generalmente es que nada demasiado grave, que si una factura está a medio grabarse en una transacción sin confirmar pues veras ese importe, y si luego hacen rollback desaparecerá. Y tu sensación será.. tampoco es para tanto.. ¿verdad?

En realidad, internamente pasan muchas mas cosas, no solamente se leen transacciones sucias de usuario, sino también transacciones sucias de sistema, esto es, todo lo que el motor necesite para organizar páginas estará bajo lecturas sucias.

Y la cuestión es ¿y eso que implicaciones tiene?

Las implicaciones son a veces imprevisibles,  lo normal es que no pase gran cosa, pero simplemente decir eso es una temeridad puesto que aunque solo variara un registro las implicaciones de negocio son imprevisibles. En el video que pueden ver a continuación tienen una demostración que creo que ilustra bastante bien lo que puede pasar.

Esta demostración está inspirada en una del gran Itzik Ben-Gan https://twitter.com/ItzikBenGan , sirva de agradecimiento a todo lo que hemos aprendido  y seguimos aprendiendo con él.

 

Una solución a esto, sin entrar demasiado en detalle es utilizar niveles de aislamiento basados en instantáneas,  veamos un ejemplo

 

En conclusión. No quiero demonizar el uso de nolock, pero al menos, cuando lo usemos, que seamos conscientes de las alternativas y de sus peligros, de todos ellos.

Construir procesos ETL con SSIS.

Dentro de los básicos del mundo de los datos, la capacidad de construir y desplegar procesos ETL (extracción transformacion y carga) es una de principales características de los sistemas modernos de datos.
Los procesos ETL llevan vigentes muchos años, aunque ahora hay tendencias a usar ELT es decir, construir las estructuras de destino antes de transformar la información, sobre todo con el auge del famoso BIG DATA. Lo hagas antes o despues, en un orden o en otro al final siempre hay un proceso de transformación. Por si alguien está interesado en esto, básicamente el paradigma ELT pretende dar una respuesta al tiempo real, o como decía un cliente al tiempo razonable. Incluso hay iniciativas que simplemente son un envoltorio sobre los datos que no se mueven y por tanto están siempre disponibles en tiempo real, a costa claro, de que la base de datos de origen “sufra” las consultas.

Más allá de todas estas teorías y del reto que puedan suponer, el artículo va orientado a lo que dice su título, Construir, desplegar y agendar procesos ETL con SQL Server Integration Services.

  1. Las herramientas que necesitaremos

Ha pasado por varios nombres, pero en el día de hoy se usan las SQL Server Data Tools, te sugiero que lo busques en tu buscador favorito, puesto que el enlance que te voy a poner a continuación seguramente quede obsoleto sin que yo me de cuenta Descarga la version actual de las SQL server Data Tools

Ojo, porque hay comos dos versiones, una de las data tools es para el mundo relacional y se encarga de la generación y mantenimiento de bases de datos. Es una interesante herramienta pero no es de la que hablamos en este artículo. Síguele la pista sobre todo si quieres automatizar los despliegues de aplicaciones en lo que en el momento de la escritura de este artículo se llama DevOps.

Una vez que las descargues, en tu versión de Visual Studio te aparecerá un nuevo menú de proyectos, en el que tendrás un apartado llamado Business Intelligence (la verdad es que no se que pone si el Visual Studio está en español), con los proyectos tipo Analisys Services, ReportingServices y el que nos interesa a nosotros, Integration Services.

2. Crea tu primer proyecto SSIS

La intención de este artículo no es hacer un tutorial detallado del uso de SSIS, digo esto porque no quiero que el artículo pese 100 páginas y sea medio libro, por eso vamos a mostrar un paquete ya creado con una somera explicación de cada una de las partes .Todo esto con el objeto de dar una visión general del proceso completo, así pues una vez creado nuestro paquete podremos compilarlo y desplegarlo.

Los paquetes de SSIS tienen dos tipos de objetos, los flujos de control y los flujos de datos. Los flujos de control son lo que en una aplicación en cualquiera de tus lenguajes favoritos son los bucles y estructuras de control del flujo del programa. En ellos decides que trozos se ejecutan y cuales no, decides que trozo se paraleliza y que trozo se hace en secuencia, si hay que iterar por tablas etc etc. Para todo esto tiene componentes SSIS y además no se necesitan conocimientos de desarrollo profundos para usarlos, simplemente conocer que hace cada componente y como se va a comportar.

Un ejemplo terminado de flujo de control que yo uso de forma habitual es el siguiente. (verás que es bastante completo). Sería muy costoso mantener ese nivel de calidad, pero en mi caso los genero automáticamente con el Framework que tenemos en SolidQ para desarrollar estas aplicaciones me puedo permitir el que sean así de terminados.

El primero de los objetos “Check load Active” es una consulta SQL a una tabla de meta-datos, en esa tabla guardo un montón de información sobre la ejecución del proceso, uno de los valores que guardo es si está o no activa para ejecutarse. De esta forma si una vez desplegado el paquete quiero que no se ejecute, me basta con cambiar ese valor y no tengo que desplegar de nuevo una solución. De ahí que la flecha verde que une el flujo tenga un fx, ese valor es el que comprueba si ha de seguir ejecutándose o no.

El segundo componente es una llamada a un procedimiento almacenado que marca el inicio de la carga, ahí guardo información como el nombre del paquete la hora de comienzo, el tipo de proceso y más información relevante para hacer después análisis de ejecuciones. Esta información se completará al final con el resultado de la ejecución (satisfactorio o erróneo)

Después lanzo script de pre-ejecución. Estos scripts los uso en ocasiones para hacer tareas previas al movimiento de datos en sí, reconstrucción de estadísticas, creación de falsas tablas temporales, actualización de valores relevantes, resúmenes de información, o cualquier otra tarea que necesite antes de ejecutar mi proceso de sincronizar. En esta ocasión solo hay un comando, pero en procesos más complejos puedo tener varios.

A continuación busco el ultimo valor leído. Esto es porque el proceso, en este caso, es incremental, imaginemos que es por una fecha. En una tabla de configuración tengo almacenada la última fecha que leí, ahora en este componente leo esa fecha y la asigno a una variable. Después usaré esa variable para filtrar la información de origen exclusivamente a la que sea mayor o igual que la última fecha que leí. (si estás pensando que eso no detecta borrados… es correcto.. para eso hay otros procesos pero no es el caso de detallar todos y cada uno de los pormenores en este artículo)

Después se puede observar que entramos en el proceso de carga de datos. Lo primero que se hará es limpiar la tabla de cambios. Esto significa que en mi forma habitual de proceder, para cada tabla que voy a sincronizar creo una tabla paralela para guardar las operaciones que han cambiado, de esta forma lo que hago es siempre insertar cambios en esta tabla, para despues actualizar los datos de la tabla original con los datos de la tabla de cambios. ¿Porqué? El motivo es que los componentes oleDB command de SSIS en un flujo de datos se ejecutan por cada fila, si se actualizaran muchas filas el proceso sería estúpidamente largo en tiempos prefiero hacer un solo comando update, o delete para actualizar todas las filas. Decir también que cuando ha sido necesario he creado proceso que hacen estas actualizaciones por bloques para evitar que sufra el log de transacciones.

Lo más relevante tras esta operación es el flujo de datos, en él también voy a dar una explicación somera de lo que se hace puesto que no pretendo aquí crear un tutorial de creación de flujos de datos en ssis.

En mi caso lo primero que hago es usar un componente OleDB Source (llamado HLP) para leer datos del origen, ese componente es el que filtrará el query de origen para traerme solo los datos que han cambiado.  Una vez que tenemos esos datos, es posible, que como parte de nuestra transformación, algunos tipos de datos cambien, particularmente si nuestro origen es de un sistema gestor de bases de datos distinto al de  destino. Como puede observarse en el camino de error (el rojo) si no se puede hacer esa transformación prevista, simplemente llevaré los registros a una tabla de error, que me permita saber que algo no está funcionando bien en mi proceso. A continuación genero un Hash con todas las columnas de la tabla, excepto las que forman parte de la clave primaria. Uso el componente Multiple hash que os recomiendo encarecidamente. Con eso me ahorro de escribir complejos codigos de comprobacion teniendo en cuenta posibles nulos etc. Simplemente para cada clave primaria que coindice, veo si coincide o no el hash que ya había calculado vs el hash que acabo de calcular.

Una vez hecho ese cálculo uso el componente lookup, para encontrar los registros que ya tengo y poder distinguir entre  insertados y modificados (y no cambiados en absoluto).

Además de esto contaré las filas que pasan por cada flujo y marcaré la operación real (actualizacion o inserción) que sufre el registro, dejando el resultado en la tabla de cambios.

Como podréis imaginar después hay un post-proceso en el que las filas modificadas se modifican realmente y las insertadas se llevan a su lugar y un proceso adicional -cuando es necesario- de detección de eliminados, completando el proceso de sincronización.