DBI sources can't provide sampling implementation

[hannes]

When using src_sql, it is possible to attach a custom class to tbl objects. But the documentation for src_sql states that it is deprecated and that src_dbi should be used instead. When using src_dbi, no custom class can be set for tbl objects (at least not as far as I can tell).

This is mostly fine, since most generics dispatch on the connection class. Unfortunately, sample_n and sample_frac only dispatch on the tbl class and there is no generic for db-backed tables that could be used to provide a custom implementation.

My suggestion would be to add a DBI generic for sample_* that calls a method defined on the connection if available.

[edgararuiz-zz]

Hi @hannesmuehleisen , this will probably have to be implemented for each individual database type. Which database are you working with at this time?

[hannes]

Well I think this should probably be part of the SQL translation method. Indeed the syntax is different for all the systems that I can see. A generic method would be a first step.

I am the author of the MonetDBLite package, which includes a dplyr backend.

[edgararuiz-zz]

What's the syntax that MonetDB uses for sampling?

[hadley]

Seems like tbl.src_dbi should mangle the DBI class name in some way and include it in the tbl class.

Alternatively, sample_n.tbl_dbi could re-dispatch on the class of the connection.

[hannes]

yes both these solutions seem fine to me! I like how dplyr relies more on a cooperative DBI driver nowadays.

[hadley]

Actually this is more complicated than I expected. The right way to implement this is to:

• Add a new sample lazy_ops which both sample_n and sample_frac would use.

• Implement sql_build.sample which would yield a sample_query() object

• Implement sql_render.sample which would call a new sql_sample() generic, which dispatches on the connection.

I think this is better than adding a sample parameter to the existing sql_select generator because I suspect that there's currently very little standardisation across databases.

@hannesmuehleisen could you provide a few examples/links to sampling implementations that you've seen?

[hannes]

I assume you mean sample functionality in SQL? Here you go:

Oracle: SELECT c FROM t SAMPLE(10)
SQL Server: SELECT c FROM t TABLESAMPLE (10 ROWS)
PostgreSQL: SELECT c FROM t TABLESAMPLE BERNOULLI (0.1)
DB2: seems similar to PostgreSQL
MySQL: not really supported, but can be simulated with something like SELECT c FROM t WHERE RAND() <= .1
SQLite: also no direct support, but can be simulated SELECT c FROM t ORDER BY RANDOM() LIMIT 10
MonetDB: SELECT c FROM t SAMPLE 10 (soon to be extended to weighted samples, too)

So indeed some variety and some system use counts, some percentages and others workarounds. A more crucial difference is whether the sample is taken of the base table or the query result (!). We are in the process in switching from the latter to the former, this gets interesting once filters or aggregations or joins are involved.
Hope this helps

[hadley]

What about grouped/stratified samples? Any widespread support for that?

[lock]

This old issue has been automatically locked. If you believe you have found a related problem, please file a new issue (with reprex) and link to this issue. https://reprex.tidyverse.org/