Cómo sacar el máximo partido de Alteryx y SQL Server

[alberto_herni]

En este post se han recopilado algunas estrategias, trucos e ideas que resultan muy útiles cuando tenemos que trabajar con un volumen de datos muy grande y la mejor solución es utilizando la potencia de procesamiento de la base de datos.

En este caso analizaremos como utilizar las herramientas in-DB con un SQL Server, el beneficio mas inmediato es evitar que los datos bajen a local para ser procesados en Alteryx. Estos nunca llegan a salir de la base de datos, de este modo tampoco tendremos problemas con el tipo de dato al descargar aunque si tendremos algunas limitaciones de herramientas disponibles, comparado con el uso en local de Alteryx.

A la hora de trabajar con herramientas In-Data Base de Alteryx debemos tener en cuenta que seguiremos las reglas específicas de funcionamiento de la base de datos a la que conectemos, en este caso SQL Server. Por ello es importante que adaptemos nuestra forma de construir workflows a una lógica de procesamiento más similar a la que seguiríamos en una ETL completamente desarrollada con script en SQL.

Entonces ¿Por qué no desarrollar el proceso directamente con un script de SQL? Mi respuesta es USABILIDAD.

Con Alteryx tenemos la plataforma perfecta para desarrollar un proceso de fácil comprensión visual, en el que es sencillo identificar los pasos que se realizan y el orden de los mismos. Esto es de gran utilidad no solo para el desarrollador inicial del proceso, sino para los siguientes analistas que tengan que realizar tareas de mantenimiento o adaptarlos a nuevos requerimientos y evoluciones con posterioridad.

Mi Top 5 de estrategias que debes tener en cuenta cuando desarrolles en Alteryx con herramientas In-DB son:

1. DESACTIVA MOSTRAR PORCENTAJE COMPLETADO

A todos nos gusta gusta ver como Alteryx va completando los pasos del desarrollo, pero cuando movemos grandes volúmenes de datos esto ralentiza el procesamiento. Por ello lo primero es deshabilitar la opción de mostrar el porcentaje completado en la configuración del workflow y en las herramientas de input. Esto aumentará la velocidad al procesar los datos.

2. SQL SERVER BULK LOADER

A la hora de escribir en BBDD siempre utilizo el SQL Server Bulk Loader, está opción mejora significativamente la velocidad de escritura en la base de datos comparado con una conexión a través de un ODBC habitual.

3. UTILIZA TABLAS INTERMEDIAS

Cuando vamos desarrollando un proceso de transformación de datos con herramientas In-DB, realmente Alteryx va construyendo un script en SQL por debajo, es decir, funciona como una mascara que evita que interactuemos directamente con el código y a medida que las herramientas se encadenan, Alteryx va anidando queries.

La herramienta ‘Write In-DB’ lo que va a hacer es generar tablas intermedias que nos permitan hacer persistencia de los pasos procesado y cortar la query. Es importante que intercalemos estas herramientas en el flujo para ir cortando las queries, de este modo mejoramos el rendimiento al procesar y evitamos llegar al punto en el que la query es tan grande que nuestro driver no es capaz de manejarla.

Podemos hacer que la tabla sea temporal o persistente en la BBDD. Las temporales son creadas en la BBDD temporal (siguiendo la configuración definida en el SQL Server). Cada ejecución del workflow va creando tablas en una sesión y al finalizar el workflow las elimina automáticamente.

Si tu proceso mueve muchos datos y/o tiene muchos pasos es posible que en una sesión consumas todos los recursos de la base de datos temporal y el workflow no finalice o lo haga con errores. Mi recomendación es intercalar tablas temporales con persistentes, de este modo no alcanzas el límite de la BBDD temporal, pero tampoco llenas tu BBDD principal de tablas intermedias.

4. ESCRITURA DE DATOS POR ETAPAS

Cuando trabajamos con volúmenes de datos muy grandes y necesitamos actualizar parte de los datos y/o añadir los nuevos, como puede ocurrir con tablas de hechos, la estrategia ‘Delete & Append’ no resulta la opción más recomendable desde un punto de vista de performance.

Imaginemos que tenemos una tabla con 200M de líneas y debemos eliminar y reemplazar un 10% de estos datos en cada carga. Estaríamos realizando un Delete de 20M y posteriormente un Insert de 20M, aproximadamente, esto consume mucho log y podríamos quedarnos sin espacio de log. Además es más lento que hacerlo con un sencillo procedimiento almacenado en BBDD.

La lógica de la escritura por etapas sería la siguiente:

Primero escribimos los nuevos datos procesados en Alteryx en una nueva tabla X via ‘Overwrite Table (Drop)’ y los dejamos a la espera. (Esta debe tener la misma estructura que la tabla final de hechos para evitar errores)

El procedimiento almacenado realizaría la siguiente transacción:

1. Select * de la tabla final (excluyendo los datos que queremos hacer delete) + Insert de esos datos en una tabla intermedia.
2. Drop de la tabla final de hechos.
3. Renombramos la tabla intermedia con el nombre de la tabla final de hechos.
4. Insertamos los registros de la nueva tabla que habíamos dejado a la espera en la nueva tabla de hechos.
5. Hacemos truncate de la tabla X que creamos al procesar los datos para liberar espacio.

* PRO-TIP: Al finalizar la transacción podemos incluir el comando DBCC SHRINKFILE para reducir el log de transacciones.

5. CUIDADO CON EL ASTERISCO

La herramienta ‘Select In-DB’ es la solución visual de Alteryx para realizar una Select a una tabla. Normalmente hacemos Input utilizando Select *, con lo que traeremos todos los campos y luego seleccionamos solo los que necesitamos seguir utilizando. Esto hace que el tiempo de procesamiento sea mayor que si únicamente incluimos los campos que necesitamos en la ventana SQL Editor de la herramienta 'Input'. De este modo perdemos la facilidad de modificar la Select sin tocar el código, pero mejorará significativamente el tiempo de procesamiento al traer un conjunto de datos menor.

Por esto mi regla es no utilizar el Select * si la tabla que quieres leer tiene más de 10 campos (y no los vas a necesitar todos).

Alberto Hernandez es Alteryx & Tableau Senior Consultant en Keyrus Spain. Mas información y contacto en Linkedin