varios una total sumar resultados registros multitablas examen ejemplos consultas consulta avanzadas agregada sql r postgresql pagination rcurl

una - Manejo de resultados de consulta SQL paginados



total de registros de una consulta sql (3)

Para mi colección de datos de disertación, una de las fuentes es un sistema administrado externamente, que se basa en un formulario web para enviar consultas SQL . Usando R y RCurl , he implementado un marco de recopilación de datos automatizado, donde RCurl formulario mencionado anteriormente. Todo funcionó bien mientras limitaba el tamaño del conjunto de datos resultante. Pero, cuando traté de pasar más de 100000 registros ( RQ_SIZE en el código siguiente), el tándem "my code - their system" comenzó a dejar de responder ("colgado").

Por lo tanto, he decidido utilizar la función de paginación de SQL ( LIMIT ... OFFSET ... ) para enviar una serie de solicitudes, con la esperanza de combinar los resultados paginados en un marco de datos de destino. Sin embargo, después de cambiar mi código en consecuencia, la salida que veo es solo un carácter de progreso de paginación ( * ) y luego no hay más salida. Te agradecería si pudieras ayudarme a identificar la causa probable del comportamiento inesperado. No puedo proporcionar un ejemplo reproducible, ya que es muy difícil extraer la funcionalidad, sin mencionar los datos, pero espero que el siguiente fragmento de código sea suficiente para revelar el problema (o, al menos, una dirección hacia el problema).

# First, retrieve total number of rows for the request srdaRequestData(queryURL, "COUNT(*)", rq$from, rq$where, DATA_SEP, ADD_SQL) assign(dataName, srdaGetData()) # retrieve result data <- get(dataName) numRequests <- as.numeric(data) %/% RQ_SIZE + 1 # Now, we can request & retrieve data via SQL pagination for (i in 1:numRequests) { # setup SQL pagination if (rq$where == '''') rq$where <- ''1=1'' rq$where <- paste(rq$where, ''LIMIT'', RQ_SIZE, ''OFFSET'', RQ_SIZE*(i-1)) # Submit data request srdaRequestData(queryURL, rq$select, rq$from, rq$where, DATA_SEP, ADD_SQL) assign(dataName, srdaGetData()) # retrieve result data <- get(dataName) # some code # add current data frame to the list dfList <- c(dfList, data) if (DEBUG) message("*", appendLF = FALSE) } # merge all the result pages'' data frames data <- do.call("rbind", dfList) # save current data frame to RDS file saveRDS(data, rdataFile)

Dado que esto es para su disertación, aquí hay una mano:

## Folder were to save the results to disk. ## Ideally, use a new, empty folder. Easier then to load from disk folder.out <- "~/mydissertation/sql_data_scrape/" ## Create the folder if not exist. dir.create(folder.out, showWarnings=FALSE, recursive=TRUE) ## The larger this number, the more memory you will require. ## If you are renting a large box on, say, EC2, then you can make this 100, or so NumberOfOffsetsBetweenSaves <- 10 ## The limit size per request RQ_SIZE <- 1000 # First, retrieve total number of rows for the request srdaRequestData(queryURL, "COUNT(*)", rq$from, rq$where, DATA_SEP, ADD_SQL) ## Get the total number of rows TotalRows <- as.numeric(srdaGetData()) TotalNumberOfRequests <- TotalRows %/% RQ_SIZE TotalNumberOfGroups <- TotalNumberOfRequests %/% NumberOfOffsetsBetweenSaves + 1 ## FYI: Total number of rows being requested is ## (NumberOfOffsetsBetweenSaves * RQ_SIZE * TotalNumberOfGroups) for (g in seq(TotalNumberOfGroups)) { ret <- lapply(seq(NumberOfOffsetsBetweenSaves), function(i) { ## function(i) is the same code you have ## inside your for loop, but cleaned up. # setup SQL pagination if (rq$where == '''') rq$where <- ''1=1'' rq$where <- paste(rq$where, ''LIMIT'', RQ_SIZE, ''OFFSET'', RQ_SIZE*g*(i-1)) # Submit data request srdaRequestData(queryURL, rq$select, rq$from, rq$where, DATA_SEP, ADD_SQL) # retrieve result data <- srdaGetData() # some code if (DEBUG) message("*", appendLF = FALSE) ### DONT ASSIGN TO dfList, JUST RETURN `data` # xxxxxx DONT DO: xxxxx dfList <- c(dfList, data) ### INSTEAD: ## return data }) ## save each iteration file.out <- sprintf("%s/data_scrape_%04i.RDS", folder.out, g) saveRDS(do.call(rbind, ret), file=file.out) ## OPTIONAL (this will be slower, but will keep your rams and goats in line) # rm(ret) # gc() }

Luego, una vez que haya terminado de raspar:

library(data.table) folder.out <- "~/mydissertation/sql_data_scrape/" files <- dir(folder.out, full=TRUE, pattern="//.RDS$") ## Create an empty list myData <- vector("list", length=length(files)) ## Option 1, using data.frame for (i in seq(myData)) myData[[i]] <- readRDS(files[[i]]) DT <- do.call(rbind, myData) ## Option 2, using data.table for (i in seq(myData)) myData[[i]] <- as.data.table(readRDS(files[[i]])) DT <- rbindlist(myData)

Estoy respondiendo mi propia pregunta, ya que, finalmente, he descubierto cuál ha sido la verdadera fuente del problema. Mi investigación reveló que el inesperado estado de espera del programa se debía a que PostgreSQL se confundía con consultas SQL mal formadas , que contenían varias palabras clave LIMIT y OFFSET .

El motivo es bastante simple: utilicé rq$where fuera y dentro del bucle for , lo que hizo que paste() concatenara la cláusula WHERE la iteración anterior con la actual. He reparado el código procesando el contenido de la cláusula WHERE y guardándola antes del ciclo y luego usando el valor guardado en cada iteración del ciclo de forma segura, ya que se volvió independiente del valor de la cláusula WHERE original.

Esta investigación también me ayudó a corregir algunas otras deficiencias en mi código y hacer mejoras (como el uso de sub-selecciones para manejar correctamente consultas SQL que devuelven el número de registros para consultas con funciones agregadas). La moraleja de la historia: nunca se puede ser demasiado cuidadoso en el desarrollo de software. Muchas gracias a las buenas personas que me ayudaron con esta pregunta.