collect only if query returns less than n_max rows

This doesn't get around the problem you mention in the comments about spending the resources to get the query twice, but it does seems to work (at least against my MySQL database--I don't have an Oracle database to test it against):

collect2  <- function(query, limit = 20000) {

  query_nrows  <- query %>% 
    ungroup() %>% 
    summarize(n = n()) %>% 
    collect() %>% 
    pull('n')


  if(query_nrows <= limit) {
    collect(query)
  } else {
    warning("Query has ", query_nrows,"; limit is ", limit,". Data will not be collected.")
  }

}

I don't see any way to test the number of rows in the results of a query without actually running the query. With this method, though, you always force the computation of row numbers to happen in the database first and refuse to collect if you're over 20,000 (or whatever your row limit is).

You can actually achieve your goal in one SQL query:

Add the row count (n) as an extra column to the data, using dplyr's mutate rather than summarise, and then set n < n_limit as a filter condition. This condition corresponds to a having clause in SQL. If the row count is larger than the list, then no data are collected. Otherwise all data are collected. You may wish to drop the row count column in the end.

This approach should work on most databases. I have verified this using PostgreSQL and Oracle.

copy_to(dest=con, cars, "cars")
df <- tbl(con, "cars")
n_limit <- 51
df %>% mutate(n=n()) %>% filter(n < n_limit) %>% collect

However, it does not work on SQLite. To see why this is the case, you can check the SQL statement generated by the dplyr code:

df %>% mutate(n=n()) %>% filter(n < n_limit) %>% show_query

<SQL>
SELECT *
FROM (SELECT "speed", "dist", COUNT(*) OVER () AS "n"
FROM "cars") "rdipjouqeu"
WHERE ("n" < 51.0)

The SQL contains a window function (count(*) over ()), which is not supported by SQLite.

So, you can't check the size of the results without running the query.

Now the question is to either cache the results server side and test for the size, or simply put some "insurance" in the R side so that we never receive too many rows.

In the latter case, how about simply:

small_t <- con %>% tbl("small_table") %>%
  filter_group_etc %>%
  head(n=5e6) %>%
  collect()

If you get 5e6 rows, they you probably overflowed; we can't distinguish overflow from exactly 5e6 rows, but that seems a small price to pay to obtain single execution in the DB? Set 5e6 to 5000001 if you're really worried. (And 5000000L or 5000001L would be better options so that they are seen by the DB as integers.)

This doesn't work so well if you're worried about a slow connection, but if you're simply worried about over-flowing memory in R, its a cheap piece of insurance without putting extra load on the server.

You can also use the slice_sample() function.

collected_data <- table %>% 
  slice_sample(n = 30) %>% 
  collect()