How to fetch data in batches using R

Question

I have a dataframe in R with the following structure:

ID      Date
ID-1    2020-02-10 13:12:04
ID-2    2020-02-12 15:02:24
ID-3    2020-02-14 12:25:32

I am using the following query to fetch the data from MySQL, that where I'm getting a problem because I have a large number if ID (i.e ~90K). When I'm passing 500-1000 ID it is working fine but passing 90K Id it throws an error.

Data_frame<-paste0("
SELECT  c.ID, e.name,d.output
FROM Table1 c
left outer join Table2 d ON d.ID=c.ID
LEFT outer JOIN Table1 e ON e.ID_2=d.ID_2
WHERE e.name in ('Name1','Name2') 
AND c.ID IN (", paste(shQuote(DF$ID, type = "sh"),collapse = ', '), ")
                      ;")

The query returns the output in the following manner which I need to rbind with DF using ID.

Query_Output<-

ID       Name     output
ID-1     Name1    23
ID-1     Name2    20
ID-2     Name1    40
ID-2     Name2    97
ID-3     Name1    34
ID-3     Name2    53

Required Output:

ID      Date                   Name1      Name2
ID-1    2020-02-10 13:12:04    23         20
ID-2    2020-02-12 15:02:24    40         97
ID-3    2020-02-14 12:25:32    34         53

I have tried the below-mentioned code:

createIDBatchVector <- function(x, batchSize){
   paste0(
     "'"
     , sapply(
       split(x, ceiling(seq_along(x) / batchSize))
       , paste
       , collapse = "','"
     )
     , "'"
   )
 }
 
# second helper function
 createQueries <- function(IDbatches){
 paste0("
    SELECT  c.ID, e.name,d.output
    FROM Table1 c
    left outer join Table2 d ON d.ID=c.ID
    LEFT outer JOIN Table1 e ON e.ID_2=d.ID_2
    WHERE e.name in ('Name1','Name2') 
    AND c.ID IN (", paste(shQuote(DF$ID, type = "sh"),collapse = ', '), ")
                          ;")}
 
# ------------------------------------------------------------------
 
# and now the actual script
 
# first we create a vector that contains one batch per element
IDbatches <- createIDBatchVector(DF$ID, 2)
 
# It looks like this:
# [1] "'ID-1','ID-2'" "'ID-3','ID-4'" "'ID-5'" 
 
# now we create a vector of SQL-queries out of that
# queries <- createQueries(IDbatches)
 
df_final <- data.frame() # initialize a dataframe
 
conn <- database # open a connection
for (query in queries){ # iterate over the queries
    df_final <- rbind(df_final, dbGetQuery(conn,query))}

user_15114494 · Answer

Surprised 90k rows kills your SQL but such is life

Not sure I understand why you are doing what you are doing rather that looping a for

for (batches in 0:90) {
b = batches*1000
 SELECT ...
 ... WHERE ID > b & < b+1000

rbind(myData, result)
}

(That's not the solution just the method)

But if your method is working then is what you want dplyr::pivot_wider()

How to fetch data in batches using R

1 Answers