Método SQL para Comprimir Archivos de Carga de

Datos.

Utilización de T-SQL para mejorar la administración de ficheros utilizados

para cargar información.

Fecha: Agosto del 2013.

Versión: 1.0

Autor: Sebastián Rodríguez Robotham

e-mail: srodriguez@easybi.cl

Método SQL para Comprimir Archivos de Carga de Datos

Sebastián Rodríguez Robotham. www.EasyBI.cl 2 | P a g e

Introducción. En ambientes de DataWarehouse, debemos interactuar con una gran cantidad de ficheros en

forma periódica, estos ficheros son descargados desde diversas fuentes para luego cargarlos a

nuestro ambiente de base de datos SQL Server. Sin embargo, muchas veces estos ficheros quedan

alojados eternamente en las rutas de carga y no son administrados en forma eficiente,

consumiendo grandes cantidades de espacio en nuestros discos duros.

El objetivo de este documento es entregar una forma simple y eficiente de mejorar la

administración de estos ficheros a través compresión de archivos en formato RAR, todo esto

ejecutado directamente con procedimientos almacenados de SQL Server, sin intervención de

herramientas adicionales, dado que en muchas empresas no existen recursos para adquirir

herramientas especializadas en este tipo de tareas, o simplemente no queremos incluir otros

utilitarios que hagan más complicado nuestro entorno analítico.

En el anexo 1 está la información de configuración requerida en SQL Server y Windows para

implementar correctamente esta solución.

Problema. Supongamos que tenemos un sistema de DataWarehouse implementado bajo la plataforma SQL

Server versión 2008R2 o superior. Este sistema descarga información diaria desde un origen de

datos FTP que pesa aproximadamente 2GB por día (llamaremos a este archivo

VentasYYYYMMDD.TXT, donde YYYY es el año, MM es el mes y DD es el día), y luego lo aloja en

nuestro servidor de base de datos para realizar una carga de tipo Bulk. Una buena práctica es

almacenar los archivos de origen, dado que si por algún motivo necesitamos reprocesar nuestro

ambiente de DW deberemos realizar nuevamente la carga de datos desde estos ficheros. Por

tanto si requerimos almacenar al menos un año de información histórica de archivos diarios, hará

que necesitemos solo para este proceso, un disco de al menos 730GB, lo que claramente es

ineficiente.

Solución. La solucionar óptima para este requerimiento es comprimir los archivos históricos y administrarlos

en un ambiente distinto, pudiendo ser desde unidades de discos independientes en el mismo

servidor, o mejor aún en servidores distintos, para ello necesitamos, de alguna forma, tomar los

ficheros desde el servidor de base de datos, comprimirlos en la nueva unidad y eliminar los

archivos sin comprimir. Como estrategia para facilitar la administración de estos ficheros,

generaremos archivos comprimidos en forma mensual, ya que de esa forma no tendremos tantos

ficheros independientes.

Método SQL para Comprimir Archivos de Carga de Datos

Sebastián Rodríguez Robotham. www.EasyBI.cl 3 | P a g e

Para llevar a cabo esto utilizando solamente T-SQL, deberemos crear el siguiente procedimiento

almacenado que realiza esta tarea en forma muy sencilla:

Figura 1. Código del Procedimiento Almacenado PA_Control_GeneraArchivoZip

Lo que realiza este procedimiento es generar un script de ejecución de una simple sentencia RAR,

indicando la ruta y nombre de los archivos a comprimir, la ruta y nombre del archivo destino y

parámetros opcionales, como por ejemplo eliminar o no los archivos origen, e incluir o no el path

completo de los ficheros.

Finalmente, a través del comando XP_CmdShell ejecutamos dicho script, esto es similar a ejecutar

cualquier comando vía CMD de Windows.

Invocación del Procedimiento Almacenado. La invocación básica se realizaría de la siguiente forma:

Figura 2. Ejecución del Procedimiento Almacenado PA_Control_GeneraArchivoZip

Una observación importante a tener en cuenta es que el segundo parámetro, cuando el nombre

del archivo a comprimir tiene espacios, hay que incluir las comillas dobles (“), de lo contrario el

comando RAR pensará que son parámetros y no lo reconocerá, por tanto no realizará la

compresión del archivo.

Método SQL para Comprimir Archivos de Carga de Datos

Sebastián Rodríguez Robotham. www.EasyBI.cl 4 | P a g e

Ejemplo de Implementación. Siguiendo el escenario planteado inicialmente, implementaremos la rutina para comprimir

diariamente los ficheros que han sido procesados, la siguiente imagen muestra un directorio con

los archivos históricos a comprimir:

Figura 3. Listado de Ficheros a Comprimir

La carpeta “Demo” contiene los ficheros descargados desde una fuente FTP y procesados en el

DataWarehouse, y la carpeta “DemoComprime” contendrá los ficheros comprimidos.

Para realizar la compresión, deberemos ejecutar el siguiente script, que recibe como parámetro la

fecha del proceso diario.

Figura 4. Script para Automatizar Proceso de Compresión de Ficheros.

Método SQL para Comprimir Archivos de Carga de Datos

Sebastián Rodríguez Robotham. www.EasyBI.cl 5 | P a g e

Una vez ejecutado el script, revisamos que en el directorio “DemoComprime” existe un nuevo

archivo con los ficheros diarios, como lo muestra la siguiente figura:

Figura 5. Listado de Ficheros comprimidos por el Script

Al ejecutarlo por segunda vez con parámetro “2012-01-15”, comprimirá los ficheros de enero,

dejando solamente los ficheros de marzo disponibles, tal como lo muestra la figura siguiente:

Figura 6. Listado de Ficheros sin Comprimir

Método SQL para Comprimir Archivos de Carga de Datos

Sebastián Rodríguez Robotham. www.EasyBI.cl 6 | P a g e

Finalmente, podemos mejorar el script inicial, creando un procedimiento almacenado que reciba

como parámetro una fecha, e incluir este nuevo procedimiento en SQL Agent para que la tarea

quede 100% automatizada, la siguiente figura muestra el código SQL final.

Figura 7. Procedimiento Almacenado para la Automatización de Compresión de Archivos.

Conclusiones Finales Este método permite automatizar en forma fácil y eficiente la administración de ficheros que son

utilizados por SQL para cargar información.

Como principales ventajas frente a otras alternativas, podemos destacar:

a. Lo simple de implementar para personas que tienen conocimientos medios de T-SQL.

b. No requiere intervención de utilitarios adicionales, solamente WinRAR

c. No requiere ningún tipo de inversión adicional en términos monetarios (frente a otras

soluciones comerciales)

d. 100% adaptable: podemos agregar complejidad y lógicas de negocios a este código en

forma bastante sencilla, frente a otras herramientas cerradas que no permiten

modificaciones de los parámetros preestablecidos.

Método SQL para Comprimir Archivos de Carga de Datos

Sebastián Rodríguez Robotham. www.EasyBI.cl 7 | P a g e

Anexo 1. Requisitos para la implementación de la solución. A continuación se presentan los pasos a seguir para asegurar la correcta implementación de esta

solución.

Habilitar RAR en Windows Para utilizar esta solución no basta con instalar el componente WinRAR en el computador o

servidor, hay que habilitar la ruta para que pueda ser accedida desde la consola, cuando no está

hecho esto, aparecerá el siguiente mensaje al intentar ejecutarlo:

Figura 8. Pantalla CMD con error de Rar no encontrado.

Pasos a seguir para incluir la ruta en el Path de Windows (versión 7)

a. Clic en menú inicio, luego clic derecho sobre “Equipo”, seleccionar menú “Propiedades”

Figura 9. Pantallas Windows para Configurar path, paso a

Método SQL para Comprimir Archivos de Carga de Datos

Sebastián Rodríguez Robotham. www.EasyBI.cl 8 | P a g e

b. Una vez en la pantalla, dar clic en “configuración avanzada del Sistema”, aparecerá las

propiedades del sistema, dar clic en “Variables de Entorno”

Figura 10. Pantallas Windows para Configurar path, paso b

c. Buscar en “variables del sistema” la variable “Path”, y dar clic en “Editar”.

Figura 11. Pantallas Windows para Configurar path, paso c

Método SQL para Comprimir Archivos de Carga de Datos

Sebastián Rodríguez Robotham. www.EasyBI.cl 9 | P a g e

d. En el campo “Valor de la variable”, agregar al final la ruta encontrada para los archivos de

la aplicación RAR, como aparecen en la siguiente figura

Figura 12. Pantallas Windows para Configurar path, paso d

e. Dar clic en aceptar y cerrar todas las ventanas

Para que SQL Server pueda aceptar los cambios en la variable Path, se deben reiniciar los servicios

de SQL Server o reiniciar el servidor.

Habilitar comando XP_CmdShell en SQL Server Por default la opción de XP_CmdShell viene deshabilitada en SQL Server, por tanto hay que

habilitarla manualmente, para ello seguir los siguientes pasos:

a. En Management Studio, clic derecho sobre el servidor de base de datos, seleccionar la

opción “Facets”

Figura 13. Pantallas SQL Server para Configurar XP_CmdShell, paso a

Método SQL para Comprimir Archivos de Carga de Datos

Sebastián Rodríguez Robotham. www.EasyBI.cl 10 | P a g e

b. Dentro del área de “Facets”, seleccionar “Surface Area Configuration”, como muestra la

siguiente figura.

Figura 14. Pantallas SQL Server para Configurar XP_CmdShell, paso b

c. Una vez desplegada la lista de opciones en “Surface Area Configuration”, ubicar al final de

la grilla la opción “XPCmdShellEnabled”, y setear en “True”

Figura 15. Pantallas SQL Server para Configurar XP_CmdShell, paso c

Finalmente, dar “OK” a todas las ventanas.

Método SQL para Comprimir Archivos de Carga de Datos

Sebastián Rodríguez Robotham. www.EasyBI.cl 11 | P a g e

Anexo 2. Código SQL PA_Control_GeneraArchivoZIP ALTER PROCEDURE PA_Control_GeneraArchivoZip @DirectorioOrigen varchar(150) ,@ArchivosOrigen varchar(150) ,@DirectorioDestino varchar(150) ,@NombreComprimido varchar(150) ,@Password varchar(150) ,@IncluyePath int ,@EliminaArchivosOrigen int WITH ENCRYPTION AS --Variable DECLARE @Script varchar(8000) = '' --Sintaxis para correr RAR SET @Script = 'rar a -y' + case when @IncluyePath = 0 then ' -ep ' else ' -ep2 ' end + case when @EliminaArchivosOrigen = 1 then ' -df ' else ' ' end + case when len(@Password) >= 1 then ' -p' + @Password + ' ' else ' ' end

+ '' + @DirectorioDestino + '\' + @NombreComprimido + ' ' + @DirectorioOrigen + '\' + @ArchivosOrigen

--Ejecución de RAR EXEC XP_CmdShell @Script , NO_OUTPUT

Anexo 3. Código SQL PA_ComprimeFicheroVentas CREATE PROCEDURE PA_ComprimeFicherosVenta @Periodo_Id DATE WITH ENCRYPTION AS --Periodo indica la fecha del día que se procesa --Podría ser GetDate(), pero se deja paramétrico para convertir a SP --DECLARE @Periodo_Id DATE = '2012-01-15' --Se obtiene mes y año en formato texto, --el mes debes ser de dos dígitos, ejemplo "02" para febrero DECLARE @Mes VARCHAR(2) = RIGHT('0' + CONVERT(VARCHAR,Month(@Periodo_Id)),2) ,@Ano VARCHAR(4) = CONVERT(VARCHAR,Year(@Periodo_Id)) --Asigna los nombres de los archivos a comprimir y nombre del archivo destino DECLARE @ArchivosOrigen VARCHAR(100) = 'Ventas' + @Ano + @Mes + '*.*' ,@RarDestino VARCHAR(100) = 'VentasRAR' + @Ano + @Mes --Ejecuta el procedimiento almacenado que comprime EXEC PA_Control_GeneraArchivoZip 'D:\DEMO' -- @DirectorioOrigen ,@ArchivosOrigen -- @ArchivosOrigen ,'D:\DEMOCOMPRIME' -- @DirectorioDestino ,@RarDestino -- @NombreComprimido ,'' -- @Password ,0 -- @IncluyePath

,1 -- @EliminaArchivosOrigen