Failed to collect Lazy Table dbplyr sql connection

[parneetkaur]

I established my connection to sql server using the following:

Con <- odbc::dbconnect(odbc::odbc(),
Driver = …
Server = …
Database = …
Trusted_connection = yes)

Now I am working with the data which about 50 million rows added every year and data begins from something like 2003 to present.

I am trying to pull one variable from a dataset which has condition on data like >2018 to <2023 using the following:

Qkey1822 <- tbl(src=con, ‘table1’) %>%
Filter( x > 2018,
x < 2023) %>%
Collect ()

It gives me error like:
Failed to collect the lazy table

[HanOostdijk]

Does it work when you add a select statement for the variable you want to pull and 'x'?
The message suggests a memory problem.

[AlexisW]

@parneetkaur

Qkey1822 <- tbl(src=con, ‘table1’) %>%
Filter( x > 2018,
x < 2023) %>%
Collect ()

Just to be sure, you are using filter(), not Filter()? The lowercase is the correct {dbplyr} command, the uppercase might default to base's Filter() which would not know how to work with SQL databases. Same for collect() vs Collect() (which I'm not sure exists). R is case-sensitive, unlike SQL.

Anyway, agreed with HanOostdjik, it looks like the filtered table is still too big to fit in memory, you can try to extract less information (either fewer columns with pull() or fewer rows, or do some summarize()ing before collect()ing).