Carga de datos a SQL: Mal Performance

¡Hola a todos comunidad!

Estoy intentando automatizar un proceso en donde al final la data debe almacenarse en una Base de Datos de PosgreSQL, sin embargo el workflow toma 1hr aproximadamente para ejecutarse por completo.

Se que al utilizar las DB Tools se podria optimizar todo, pero el problema es que el imput proviene de un archivo tipo excel (.xlsx) y que antes de alojar la información en la Base de Datos debe ser tratada/limpiada antes.

¿Saben de alguna alternativa que pueda agilizar/optimizar la carga de datos?

¡Saludos!

Alteryx Designer

Alteryx Tools

db write

Alteryx automation

Accepted answers

Aguisande

Sorry, pero me surge una duda... Mencionaste que estás utilizando el driver de ODBC de PostreSQL v. 3.51. Eso es correcto?

Revisando la página de drivers oficial de PostgreSQL (https://www.postgresql.org/ftp/odbc/versions/msi/), el driver ODBC actual es el 13 y versiones previas a la 7 no tienen más soporte.

Antes que seguir modificando workflows, creo que es conveniente revisar el mismo driver.

Por otro lado, yo uso el de Simba, que si tienes acceso al portal de licencias de Alteryx (o alguien en tu empresa), en la sección drivers lo puedes conseguir.

Adicionalmente, por lo que vi, estás subiendo datos a Azure (Estás utilizando una VPN?)

Podrás medir la velocidad de transferencia de tu conexión?

A mí PostgreSQL me está cargando 1MM de registros en 1:07 minutos

Nota adicional: Tengo entendido que no hay forma de hacer una conexión JDBC desde Alteryx. Si saben cómo hacerla, me encantaría aprenderlo.

xkid_dup_602

Hola de nuevo chicos, gracias a todos por ayudar, considero que la respuesta dew @Aguisande es la solución adecuada, sin embargo por motivos de la empresa no tengo suficientes accesos para actualizar Drivers ni instalar VPN de manera inmediata.

Les comento que hay una segunda alternativa que encontre bastante viable en casos como este, y es cargar la data de manera remota mediante de un archivo batch ejecutado por Run command de Alteryx. La solución que genere me permitio reducir los tiempos de carga de data de 2 horas a 1 segundo, esí es como lo desarrolle:

Requisitos previos: Contar con Sistema Operativo Windows, tener la comunicación TCP/IP al puerto (5432 en mi caso) y PGSQL instalado.

1- Ejecutar prueba manual de conectividad y funcionabilidad de comandos antes de automatizar con Alteryx

A) Ejecutar un comando que nos direccione a la carpeta donde PGSQL esta instalado.

En mi caso:

cd "C:\pgsql\bin"

Generar un comando capaz de conectarse a la Base de Datos y que permita copiar la información ddeseada desde un archivo .csv a una tabla ya creada previamente.

En mi caso:

psql -h suggestedorder.postgres.database.azure.com -d DBName -U User -c "\copy Schema.TableName (Column1, Column2, ColumnN) from 'C:\directory\NameFile.csv' with delimiter as ','

C) Al ejecutar los comandos anteriores por cuestiones de seguirdad y SSL requerira el password del usuario por lo que es requerido ingresarlo manualmente y este es el primer problema para generar una automatización con alteryx, pero existen un par de Workarounds que ayudan a solventar esto:

- Crear Environment Variables: Yo no utilice esta opción porque no cuento con permisos de administrador

Para mayor detalle visita: https://www.postgresql.org/docs/9.1/libpq-envars.html

- Crear un Password File:

El password file se debe generar en una carpeta con el nombre 'postgresql' dentro de la dirección %APPDATA% donde se debera crear un archivo 'pgpass.conf'

donde se deberan escribir los datos de la BD de la siguiente forma:

hostname:port:database:username:password

o desde CMD ejecutar los siguientes comandos:

%appdata%

mkdir postgresql

cd postgresql notepad pgpass.conf

Para mayor detalle visita: https://www.postgresql.org/docs/9.1/libpq-pgpass.html

2. Al hacer esta prueba manual en el servidor nuevamente ya no se requerira de una contraseña debido a que los datos de la BD se encuentran almacenados en el Password File que se genero anteriormente, por lo que el siguiente paso es generar el archivo .bat que ejecutara Alteryx

A) Se requiere abrir un notepad y copiar los comandos usados anteriormente, algo similar a esto:

C:
cd "C:\pgsql\bin"

psql -h suggestedorder.postgres.database.azure.com -d DBName -U User -c "\copy Schema.TableName (Column1, Column2, ColumnN) from 'C:\directory\NameFile.csv' with delimiter as ','

Guardar archivo con la extensión .bat

3. Ejecutar desde alteryx con Run Command, colocando un input "Dummy" para evitar errores en la ejecución:

Espero sea de utilidad para alguien en un futuro, saludos.

All comments

carlosteixeira

@xkid_dup_602

Puedes hacer algunas cosas para mejorar.

Primero, antes de usar o excel y hacer o tratar dos datos, lo ideal sería un conversor o un archivo xls en una yxdb (extensión alteryx)
Después de la conversión, lo usa (yxdb) como entrada. Esta extensión se lee más rápido que xls.

En segundo lugar, es posible que eliminar el mayor volumen de columnas no inicie el flujo de trabajo, lo que le permitirá usar solo el final o el final.

En tercer lugar, si está utilizando las últimas versiones de Alteryx (2020.2), puede habilitar la opción para utilizar el mecanismo amplificador.

https://help.alteryx.com/es-419/current/designer/alteryx-amp-engine

Y finalmente, es posible, no sé mucho sobre postgrado, usar un cargador masivo o un tipo de conexión de mayor rendimiento que ODBC (si está usando), por ejemplo. JDC, OCI, etc.

Espero que estos consejos te ayuden a mejorar el rendimiento.
Avísame aquí si alguno de ellos funcionó para ti

Aguisande

Adicionalmente a lo que @carlosteixeira te comenta, lo que se puede hacer es procesar los excel y luego utilizar un Stream In (En la categoría IN Database), para "subir" los datos de los excel ya procesados y continuar con el proceso de actualización de Base de datos en forma In-DB

Esta herramienta, permite pasar de proceso local a In-DB (Subiendo los datos a una nueva tabla, creando una temporal o reemplazando una existente)

xkid_dup_602

Hola @carlosteixeira y @Aguisande !

Muchas gracias por su respuesta, les comento que comence a utilizar la opción Stream In, dividiendo mi workflow en dos partes de tal manera que la primera solo se encargue de subir la data a la Base de Datos y posteriormente hacer todo con DB-Tools. Sin embargo, sigue tomando mucho tiempo para lograrlo, aproximadamente 1 hora y no se si se deba a las caracteristicas de mi equipo o driver:

Mi equipo es un servidor que cuenta con 12 GB de RAM

El ODBC que estoy utilizando es la versión 3.51

El input tiene 17500 registros y 9 columnas

A mi entender deberia funcionar bien, aqui el primer workflow que tarda 25 minutos

Adicional a esto les comento que ejecutando toda la transformación de manera local y escribiendo el output en un archivo excel tarda unicamente 20 segundos, entonces asumo que el problema esta en la carga de datos a la Base.

¿Saben si podria haber alguna opción más?

dsmdavid

Un apunte más - al subir datos a la base de datos es importante que los campos estén definidos con el tipo correcto (y compatible con la tabla existente, si la hay). P. ej. Si hay campos V_w de un tamaño descomunal (como el que se crea con una fórmula por defecto), puede hacer que la transferencia de datos se ralentice. Un vistazo al select que precede a la herramienta de subida de datos te dirá si esto influye.

xkid_dup_602

Requisitos previos: Contar con Sistema Operativo Windows, tener la comunicación TCP/IP al puerto (5432 en mi caso) y PGSQL instalado.

1- Ejecutar prueba manual de conectividad y funcionabilidad de comandos antes de automatizar con Alteryx

A) Ejecutar un comando que nos direccione a la carpeta donde PGSQL esta instalado.

En mi caso:

cd "C:\pgsql\bin"

Generar un comando capaz de conectarse a la Base de Datos y que permita copiar la información ddeseada desde un archivo .csv a una tabla ya creada previamente.

En mi caso:

psql -h suggestedorder.postgres.database.azure.com -d DBName -U User -c "\copy Schema.TableName (Column1, Column2, ColumnN) from 'C:\directory\NameFile.csv' with delimiter as ','

- Crear Environment Variables: Yo no utilice esta opción porque no cuento con permisos de administrador

Para mayor detalle visita: https://www.postgresql.org/docs/9.1/libpq-envars.html

- Crear un Password File:

El password file se debe generar en una carpeta con el nombre 'postgresql' dentro de la dirección %APPDATA% donde se debera crear un archivo 'pgpass.conf'

donde se deberan escribir los datos de la BD de la siguiente forma:

hostname:port:database:username:password

o desde CMD ejecutar los siguientes comandos:

%appdata%

mkdir postgresql

cd postgresql notepad pgpass.conf

Para mayor detalle visita: https://www.postgresql.org/docs/9.1/libpq-pgpass.html

A) Se requiere abrir un notepad y copiar los comandos usados anteriormente, algo similar a esto:

C:
cd "C:\pgsql\bin"

psql -h suggestedorder.postgres.database.azure.com -d DBName -U User -c "\copy Schema.TableName (Column1, Column2, ColumnN) from 'C:\directory\NameFile.csv' with delimiter as ','

Guardar archivo con la extensión .bat

3. Ejecutar desde alteryx con Run Command, colocando un input "Dummy" para evitar errores en la ejecución:

Espero sea de utilidad para alguien en un futuro, saludos.

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335