仅当查询结果少于n_max行时收集数据

Question

有时候，当我通过 ROracle 和 dbplyr 连接到我的 Oracle 数据库时，我会运行 dplyr::collect 操作，获取比预期更多的数据和 R 可以处理的数据。

这可能会使 R 崩溃，并且通常表明在获取数据之前应进一步过滤或聚合。

在选择是否获取结果之前检查结果的大小将非常有用（而不需要运行两次查询）。

假设我们称 collect2 是允许此操作的 collect 的变体：

期望的行为：

small_t <- con %>% tbl("small_table") %>%
  filter_group_etc %>%
  collect2(n_max = 5e6) # works fine

big_t   <- con %>% tbl("big_table")   %>%
  filter_group_etc %>%
  collect2(n_max = 5e6) # Error: query returned 15.486.245 rows, n_max set to 5.000.000

这可行吗？

我也愿意使用 ROracle / DBI 而不使用 dplyr 来解决问题，例如：

dbGetQuery2(con, my_big_sql_query,n_max = 5e6) # Error: query returned 15.486.245 rows, n_max set to 5.000.000

编辑：

请见下方已发布的部分解决方案作为答案，这并不是最优解，因为有些时间被浪费在获取无用数据上。

- moodymudskipper

---

nrow 的测试不足以吗？ - F. Privé

查询结果行数可以知道，但如何在不重复运行一次查询的情况下获取“r”中的数据呢？ - moodymudskipper

我可以这样做，它将在服务器端执行完整的查询并返回行数，然后根据结果再次执行以获取完整结果，但这样做会在服务器端运行两次。这解决了我的R崩溃问题，但是这是一个很大的开销。 - moodymudskipper

我以为从标签上就可以清楚地看出来，但现在我会编辑一下，让它更明确。 - moodymudskipper

我认为这会导致与我在下面提出的解决方案类似的结果，一些资源被用于获取我不需要的行，并且实际的n_rows从未知道。 - moodymudskipper

Answer 1

这并不能解决你在评论中提到的重复查询的问题，但它似乎是有效的（至少对于我的MySQL数据库来说 - 我没有Oracle数据库可供测试）：

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.")
  }

}

我没有看到任何一种方法可以在不运行查询的情况下测试结果中行数。然而，通过这种方法，您总是强制先在数据库中计算行数，如果超过20,000行（或其他行限制），则拒绝收集。

- crazybilly

---

一种方法是使用dbSendQuery创建一个临时表，例如con %>% tbl("MY_TBL") %>% dplyr_stuff %>% sql_render %>% {dbSendQuery(con,paste0("CREATE TABLE TEMP_TBL(", .,")"))}，第一次执行后，如果行数正确，则获取结果，但我需要写入权限并在服务器上物理写入数据，而将其保留在内存中更为优化，以便在我们选择收集或不收集时使用。 - moodymudskipper

我还是点赞了，因为这个解决方案很好，也许（我不是很了解）如果dbsm能够优化第一个查询，那么在“真正的交易”中它实际上会比第二次运行更快，我将需要对其进行基准测试。 - moodymudskipper

当您输入 warn 时，是否意味着 warning？如果是这样，您可以通过一些小的更改跳过 paste 调用，因为 warning 的行为类似于 paste0。如果不是，您应该提到您从哪个包中获取它。我还建议您将 magrittr::extract2('n') 替换为 pull(n)。 - moodymudskipper

Answer 2

你可以使用一条SQL查询来实现目标：

使用dplyr的mutate添加行计数（n）作为额外的列到数据中，而不是使用summarise，并将n < n_limit作为过滤条件。这个条件对应于SQL中的having子句。如果行数大于列表，则不收集任何数据。否则，收集所有数据。最后你可能想要删除行计数列。

这种方法适用于大多数数据库。我已经在PostgreSQL和Oracle上进行了验证。

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

然而，它在SQLite上无法运行。要了解原因，您可以检查dplyr代码生成的SQL语句：

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)

SQL 包含窗口函数（count(*) over ()），但 SQLite 不支持该功能。

- Till

---

那真是太简单了 :)！ - moodymudskipper

Answer 3

您还可以使用slice_sample()函数。

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

- IHADDADEN MOHAMED EL FODIL

Answer 4

因此，在运行查询之前，您无法检查结果的大小。

现在的问题是要么在服务器端缓存结果并测试其大小，要么在 R 端放置一些“保险”，以便我们永远不会收到太多行。

在后一种情况下，怎么样简单地实现：

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

如果你得到了5e6行，那么你可能已经溢出了；我们无法区分溢出和确切的5e6行，但这似乎是为了在数据库中获得单次执行而付出的小代价？如果你真的很担心，将5e6设置为5000001。 （5000000L或5000001L是更好的选项，因此它们被数据库视为整数。）

如果你担心连接速度慢，那么这种方法可能不太适用，但如果你只是担心在R中溢出内存，那么这是一种廉价的保险措施，而不会给服务器增加额外负载。

- dsz

---

但是当你只想知道是否溢出时，你正在获取这5e6行数据。在某些配置中，包括我的配置，获取数据需要很长时间，通常比在服务器端执行查询要慢得多。所选择的解决方案巧妙地避免了这个问题。 - moodymudskipper