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 #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:

datafusion/datafusion/sql/src/statement.rs

Lines 1068 to 1089 in 000288c

	fn show_tables_to_plan(
	&self,
	extended: bool,
	full: bool,
	db_name: Option<Ident>,
	filter: Option<ShowStatementFilter>,
	) -> Result<LogicalPlan> {
	if self.has_table("information_schema", "tables") {
	// We only support the basic "SHOW TABLES"
	// https://github.com/apache/datafusion/issues/3188
	if db_name.is_some() || filter.is_some() || full || extended {
	plan_err!("Unsupported parameters to SHOW TABLES")
	} else {
	let query = "SELECT * FROM information_schema.tables;";
	let mut rewrite = DFParser::parse_sql(query)?;
	assert_eq!(rewrite.len(), 1);
	self.statement_to_plan(rewrite.pop_front().unwrap()) // length of rewrite is 1
	}
	} else {
	plan_err!("SHOW TABLES is not supported unless information_schema is enabled")
	}
	}

[theirix]

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

[theirix]

take

[theirix]

Sorry for the long absence. After datafusion-sqlparser-rs gained SQL support for tablesamples in 0.54, I am going to introduce a rewrite phase into the datafusion crate.