Escribir en Redshift desde R a toda velocidad

Como muchos saben, Redshift es un fork de Postgres hecho por Amazon para entregar un servicio de Data Warehouse, la gran diferencia entre estos 2 productos es que el primero es una base de datos columnar y comprimida, mientras que el segundo no.

Las bases de datos columnares son muy rápidas para realizar agregaciones y cruces, pero no tanto para la inserción de registros (no esperes mas de 4 registros por segundo con un insert normal en Redshift), por lo que en general tienen alguna vía no standard. En el caso de Redshift el método es el siguiente:

1. Si no existe la tabla en la base de datos: créela.
2. Guarde la la tabla que quiere cargar en S3, idealmente en varios archivos CSV comprimidos en gzip.
3. Desde Redshift llame al comando COPY para leer los archivos de S3 y cargarlos en la Base de Datos.

Como ven, no es un proceso standard, por lo que sicarul creó la librería redshiftTools que realiza todo este proceso secuencial mente, pero la librería era bastante lenta, para cargar un un dataset de 1.5 millones de registros y 800 columnas, toma 21 minutos, por lo que decidí revisar el codigo a ver si podía realizar algunas optimizaciones, dentro las las cuales encontré:

1. El paquete usaba write.csv para generar los CSV, por lo que migré a fwrite.
2. La compresión de los CSV era secuencial, por lo que paralelicé compresión de los CSV usando future.
3. Solo existía una función para realizar un update de una tabla, lo que era lento, por lo que creé una función que solo inserta registros en una tabla.

Al realizar todos estos cambios, el proceso completo de carga bajó a menos de 6 minutos, siendo un 350% más rápido que el ya existente (por lo que ya llené un pull request al repositorio oficial).

Mientras el pull request es aceptado, pueden disfrutar de la libreria optimizada en mi GitHub, pueden instalarlo con los siguientes commandos desde R:

install.packages("aws.ec2metadata", repos = c(cloudyr = "http://cloudyr.github.io/drat", getOption("repos")))
packages = c("devtools", "httr", "package_n","aws.s3", "Rcpp","DBI","data.table","future","future.apply","R.utils","dplyr")
for(pack in packages){
  if(!pack %in% rownames(installed.packages())){
    print(paste("installing",pack))
    install.packages(pack)
  }
}
devtools::install_github("danielfm123/redshiftTools")

un pequeño ejemplo de como usarlo en el siguiente código:

library(data.table)
library(RPostgreSQL)
library(toolkitEntel)
setDTthreads(0)

dataset = fread("Ruta a algun archivo que se quiera leer",data.table = F,sep = "|",encoding = "Latin-1",nrows = -1)

#COnectar a SQL
con = dbConnect(
  dbDriver("PostgreSQL"),
  dbname = "nombre base de datos",
  user = "usuario",
  password = "contraseñna",
  host = "ip del servidor"
)

# Crear una tabla, detecta tipos de columnas
rs_create_table(
  dataset,
  dbcon = con,
  table_name = 'table_name',
  bucket = "bucket_name",
  region = "us-east-2",
  keys = "AWS AIM KEY de S3",
  secret_key = "AWS AIM SECRET de S3"
)

# agregar data a una tabla existente
rs_append_table(
  dataset,
  dbcon = con,
  table_name = 'table_name',
  bucket = "bucket_name",
  region = "us-east-2",
  keys = "AWS AIM KEY de S3",
  secret_key = "AWS AIM SECRET de S3"
)

#desconectarse de redshift
dbDisconnect(con)