Insertar registros a Base de Datos a toda Velocidad

Las herramientas para ETL son muy prácticas al momento de realizar procesos de transformación de datos automáticos y recurrentes, estas se caracterizan por realizar tres tareas:
(E) extract: conectarse a una o más fuentes y extraer datos.
(T) Transform: Transformar o manipular los datos
(L) Load: Cargar los datos ya transformados al repositorio final.

De ahí viene el nombre ETL

En general la última etapa (Load), carga los datos a un Data-Lake o un Base de Datos SQL, típicamente Microsoft SQL Server, en esta etapa de carga de datos muchas veces toma tiempo considerable del ETL, debido a que la operación insert, la cual inserta filas a una Base de Datos en la base de datos es relativamente lenta.

Por eso es que existe una forma de insertar los datos de manera masiva llamada Bulk Insert, la cual le permite al servidor SQL leer un archivo de datos e insertarlos directamente en la base de datos.

Hoy vamos a comparar el desempeño de estas dos técnicas (Insert y Bulk Insert) escribiendo desde R a SQL, vamos a comparar el tiempo que toma escribir bases de datos o tablas de distintos tamaños.

El experimento consistirá en insertar 3 datasets de 100.000, 1.000.000 y 10.000.000 filas, cada uno con 10 columnas de 20 caracteres aleatorios.

Para realizar estas pruebas usaremos el driver ODBC oficial de Microsoft en linux con el paquete odbc para R, por otro lado, generaremos el archivo CSV de carga para bulk insert con la librería data.table, la cual es la más rápida que conozco (si saben de otra me dicen). en ambos casos mediremos el tiempo completo de carga, osea desde que se tienen los datos listos hasta que están insertados, consideran el crear y depositar los archivos de datos en el servidor.

La configuración del sistema es bastante simple: el servidor SQL y R están en el mismo datacenter y la comunicación para traspasar archivos es por SAMBA (carpeta compartida de windows).

Bulk Insert VS Insert

En el eje Y tenemos el tiempo y en el X el número de filas, podemos ver que el insert normal es considerablemente más lento y que no tenemos el dato para 10 millones de filas, esto se debe a que la carga falló.

La diferencia es abismante pero según la documentación de SQL server, el bulk insert puede funcionar en paralelo, por lo que compararemos las velocidades para 1, 4 y 8 threads

Inserción en paralelo.

Quiero destacar que la programación para logar insertar en paralelo es algo compleja, pero aca van los resultados:

Podemos ver que para datasets grandes usar 8 threads es conveniente, mientras que para datasets pequeños puede ser problemáticos, esto se debe a que dividir el dataset en y crear los threads toma tiempo (a llamada a bulk insert se hizo en python, por que crear un thread en R toma muchos recursos, para realizar esta interacción entre R y Python se usó el paquete rericulate).

Tipos de Storage

Por último, hoy en día están muy de moda las bases de datos columnares, diseñadas más para el procesamiento de datos que para ser transaccionales.

Podemos ver que el columnstore es un poco más rápido que el clásico rowstore, esto es relevante porque el máximo beneficio del columnstore viene en la extracción de datos más que en la inserción, por lo que es completamente recomendable.

Conclusiones

Bulk Insert es aproximadamente 10 veces más rápido para insertar datos en una base datos

La inserción en paralelo tiene beneficios significativos, pero la programación es compleja, si alguien quiere guia, que no dude en contactarme.

Por último, el columnstore tiene algo de impacto positivo, pero es recomendado por que mejora la velocidad de lectura y es fácil de configurar.

Por ultimo: esta vez no comparto los códigos por que los desarrolle para el ambiente analítico de Anasac donde trabajo actualmente, por lo que no son genéricos, pero si alguien requiere ayuda, que no dude en contactarme.

Print Friendly, PDF & Email