Estoy usando el siguiente código para conectarme a una base de datos PostgreSQL 12:
con <- DBI::dbConnect(odbc::odbc(), driver, server, database, uid, pwd, port)Esto me conecta a una base de datos PostgreSQL 12 en Google Cloud SQL. A continuación, se utiliza el siguiente código para cargar los datos:
DBI::dbCreateTable(con, tablename, df) DBI::dbAppendTable(con, tablename, df) donde df es un marco de datos que he creado en R. El marco de datos consta de ~ 550 000 registros con un total de 713 MB de datos.
Cuando se cargó con el método anterior, tomó aproximadamente 9 horas a una velocidad de 40 operaciones de escritura por segundo. ¿Existe una forma más rápida de cargar estos datos en mi base de datos PostgreSQL, preferiblemente a través de R?
Siempre he encontrado que la copia masiva es la mejor, externa a R. La inserción puede ser significativamente más rápida y su sobrecarga es (1) escribir en el archivo y (2) el tiempo de ejecución más corto.
Configuración para esta prueba:
psql en el sistema operativo host (donde se ejecuta R); debería ser fácil con Linux, con Windows tomé el archivo "zip" (no el instalador) de https://www.postgresql.org/download/windows/ y extraje lo que necesitabadata.table::fwrite para guardar el archivo porque es rápido ; en este caso write.table y write.csv siguen siendo mucho más rápidos que usar DBI::dbWriteTable , pero con el tamaño de los datos, es posible que prefiera algo rápido DBI::dbCreateTable(con2, "mt", mtcars) DBI::dbGetQuery(con2, "select count(*) as n from mt") # n # 1 0 z1000 <- data.table::rbindlist(replicate(1000, mtcars, simplify=F)) nrow(z1000) # [1] 32000 system.time({ DBI::dbWriteTable(con2, "mt", z1000, create = FALSE, append = TRUE) }) # user system elapsed # 1.56 1.09 30.90 system.time({ data.table::fwrite(z1000, "mt.csv") URI <- sprintf("postgresql://%s:%s@%s:%s", "postgres", "mysecretpassword", "127.0.0.1", "35432") system( sprintf("psql.exe -U postgres -c \"\\copy %s (%s) from %s (FORMAT CSV, HEADER)\" %s", "mt", paste(colnames(z1000), collapse = ","), sQuote("mt.csv"), URI) ) }) # COPY 32000 # user system elapsed # 0.05 0.00 0.19 DBI::dbGetQuery(con2, "select count(*) as n from mt") # n # 1 64000Si bien esto es mucho más pequeño que sus datos (32 000 filas, 11 columnas, 1,3 MB de datos), no se puede ignorar una aceleración de 30 segundos a menos de 1 segundo.
Nota al margen: también hay una diferencia considerable entre dbAppendTable (lento) y dbWriteTable . Comparando psql y esas dos funciones:
z100 <- rbindlist(replicate(100, mtcars, simplify=F)) system.time({ data.table::fwrite(z100, "mt.csv") URI <- sprintf("postgresql://%s:%s@%s:%s", "postgres", "mysecretpassword", "127.0.0.1", "35432") system( sprintf("/Users/r2/bin/psql -U postgres -c \"\\copy %s (%s) from %s (FORMAT CSV, HEADER)\" %s", "mt", paste(colnames(z100), collapse = ","), sQuote("mt.csv"), URI) ) }) # COPY 3200 # user system elapsed # 0.0 0.0 0.1 system.time({ DBI::dbWriteTable(con2, "mt", z100, create = FALSE, append = TRUE) }) # user system elapsed # 0.17 0.04 2.95 system.time({ DBI::dbAppendTable(con2, "mt", z100, create = FALSE, append = TRUE) }) # user system elapsed # 0.74 0.33 23.59 (No quiero dbAppendTable con z1000 arriba...)
(Por diversión, lo ejecuté con replicate(10000, ...) y ejecuté las pruebas psql y dbWriteTable nuevamente, y tardaron 2 segundos y 372 segundos, respectivamente. Su elección :-) ... ahora tengo más de 650 000 filas de mtcars ... hrmph ... drop table mt ...
Sospecho que dbAppendTable da como resultado una declaración INSERT por fila, lo que puede llevar mucho tiempo para un gran número de filas.
Sin embargo, puede generar una INSERT única para todo el marco de datos utilizando la función sqlAppendTable y ejecutarla utilizando dbSendQuery explícitamente:
res <- DBI::dbSendQuery(con, DBI::sqlAppendTable(con, tablename, df, row.names=FALSE)) DBI::dbClearResult(res)Para mí, esto fue mucho más rápido: una ingesta de 30 segundos se redujo a una ingesta de 0,5 segundos.