Support data source sampling with TABLESAMPLE

[theirix]

Is your feature request related to a problem or challenge?

It is helpful to have sampling support for queries to ease the exploration of data.

Describe the solution you'd like

It should be supported on the SQL level (SAMPLE or TABLESAMPLE syntax). The sampling construct should be passed to the table source so the sampling is performed at the scan plan (e.g. in an optimised parquet reader).

This feature could be implemented in three sequential stages:

1. Support additional SQL syntax but fail in the physical plan builder
2. Transparently convert to WHERE RANDOM() < P filter
3. For eligible data sources push the sampling to the table source

Describe alternatives you've considered

It is possible to use WHERE RANDOM() < 0.1 selection (see discussion https://github.com/apache/datafusion/issues/13268 ), but the support in SQL is clearer.

Existing query engines and databases already implement sampling, but it is not in ANSI standard. There are different flavours, but essentially, they allow for specific sampling methods and percentages (or sometimes a number of rows) TABLESAMPLE [SYSTEM | BERNOULLI] (PERCENTAGE | ROWS)

DuckDB:

SELECT * FROM tbl TABLESAMPLE SYSTEM (10%),

PostgreSQL and Trino:

SELECT * FROM tbl TABLESAMPLE SYSTEM (10),

Spark

SELECT * FROM tbl TABLESAMPLE SYSTEM (10 PERCENT)

Clickhouse is different:

SELECT * FROM tbl SAMPLE 0.1

Additional context

Also requested in #11554. The filter for sampling was refined in #13268.

[alamb]

I looked around in sqlparser-rs briefly and it seems this syntax is not yet supported

https://github.com/search?q=repo%3Aapache%2Fdatafusion-sqlparser-rs%20tablesample&type=code

(though the keyword is)

I suggest adding support for this clause in sqlparser first and then we can add a rewrite pass that converts the TABLESAMPLE clause into a where clause, similar to what we do for SHOW TABLES:

https://github.com/apache/datafusion/blob/000288ce3ec4cb11803c5b37680d6cddfa01c57e/datafusion/sql/src/statement.rs#L1068-L1089

[theirix]

Thank you for the initial analysis. I will first submit a PR to datafusion-sqlparser-rs with extended grammar.

[theirix]

take