You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "theirix (via GitHub)" <gi...@apache.org> on 2024/11/25 20:11:58 UTC
[I] Support data source sampling with TABLESAMPLE [datafusion]
theirix opened a new issue, #13563:
URL: https://github.com/apache/datafusion/issues/13563
### 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](https://duckdb.org/docs/sql/samples.html#table-samples):
```sql
SELECT * FROM tbl TABLESAMPLE SYSTEM (10%),
```
[PostgreSQL](https://www.postgresql.org/docs/current/sql-select.html#SQL-FROM) and [Trino](https://trino.io/docs/current/sql/select.html#tablesample):
```sql
SELECT * FROM tbl TABLESAMPLE SYSTEM (10),
```
[Spark](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-sampling.html)
```sql
SELECT * FROM tbl TABLESAMPLE SYSTEM (10 PERCENT)
```
[Clickhouse](https://clickhouse.com/docs/en/sql-reference/statements/select/sample) is different:
```sql
SELECT * FROM tbl SAMPLE 0.1
```
### Additional context
Also requested in #11554. The filter for sampling was refined in #13268.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Support data source sampling with TABLESAMPLE [datafusion]
Posted by "theirix (via GitHub)" <gi...@apache.org>.
theirix commented on issue #13563:
URL: https://github.com/apache/datafusion/issues/13563#issuecomment-3198550592
Thank you for the detailed explanation, @alamb ! I remember these design goals from community talks. I agree that having table sampling as an extension to DataFusion could be a great way to implement this feature, and at the same time, can showcase the extensibility of the core and identify any gaps with APIs. If this works out, we could avoid the need for that PR to `datafusion-sql`.
Since the SQL support is already in datafusion-sqlparser-rs, it looks like the main work is on the second part. Let me start exploring what is required to implement this as an extension and get back with any questions.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Support data source sampling with TABLESAMPLE [datafusion]
Posted by "theirix (via GitHub)" <gi...@apache.org>.
theirix commented on issue #13563:
URL: https://github.com/apache/datafusion/issues/13563#issuecomment-3207903603
> > Since the SQL support is already in datafusion-sqlparser-rs, it looks like the main work is on the second part. Let me start exploring what is required to implement this as an extension and get back with any questions.
> I suspect we can do almost everything needed with existing APIs
>
> The high level steps might be:
>
> 1. Parse the relevant SQL into some sort of `Statement::Sample`
>
> 2. Use the LogicalPlanBuilder to create a plan for that new statement
>
> 3. Implement a custom extension node, if necessary, to implement the sampling
>
>
> Does that make sense?
Yes, absolutely, I am on it. If something is missing from the API, it will be apparent soon.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Support data source sampling with TABLESAMPLE [datafusion]
Posted by "theirix (via GitHub)" <gi...@apache.org>.
theirix commented on issue #13563:
URL: https://github.com/apache/datafusion/issues/13563#issuecomment-2499247966
Thank you for the initial analysis. I will first submit a PR to datafusion-sqlparser-rs with extended grammar.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Support data source sampling with TABLESAMPLE [datafusion]
Posted by "theirix (via GitHub)" <gi...@apache.org>.
theirix commented on issue #13563:
URL: https://github.com/apache/datafusion/issues/13563#issuecomment-2892193833
Sorry for the long absence. After datafusion-sqlparser-rs gained SQL support for tablesamples in [0.54](https://github.com/apache/datafusion-sqlparser-rs/pull/1566), I am going to introduce a rewrite phase into the datafusion crate.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Support data source sampling with TABLESAMPLE [datafusion]
Posted by "theirix (via GitHub)" <gi...@apache.org>.
theirix commented on issue #13563:
URL: https://github.com/apache/datafusion/issues/13563#issuecomment-2499248049
take
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Support data source sampling with TABLESAMPLE [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #13563:
URL: https://github.com/apache/datafusion/issues/13563#issuecomment-3196874378
> There are numerous advanced use cases and possible data source-level optimisations for table sampling.
> 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)
This is my core concern with adding any sort of sampling directly to DataFusion -- I think the usecases will vary widely across systems, and thus I worry that anything we build into DataFusion will likely be fairly complicated as well as not what other systems may want
I think it is actually possible to implement table sampling with the existing APIS through a combination of
1. sql planner extension https://github.com/apache/datafusion/blob/main/datafusion-examples/examples/sql_dialect.rs
2. User defined extension nodes (aka add extension logical planning nodes)
I would be willing to help make an example for this usecase, to show it is possible. I think it would be a nice showcase for how to extend systems using DataFusion without having to change the ecode.
If there is broad support for adding table sampling to DataFusion, I think we should try and make it conform to the [design goals of DataFusion](https://docs.rs/datafusion/latest/datafusion/index.html#design-goals):
1. Work “out of the box”: Provide a very fast, world class query engine with minimal setup or required configuration.
1. Customizable everything: All behavior should be customizable by implementing traits.
1. Architecturally boring 🥱: Follow industrial best practice rather than trying cutting edge, but unproven, techniques.
So maybe in this case we can begin with figuring out "how would we add table sampling"
https://datafusion.apache.org/contributor-guide/architecture.html#creating-new-extension-apis
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Support data source sampling with TABLESAMPLE [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #13563:
URL: https://github.com/apache/datafusion/issues/13563#issuecomment-3201702314
> Since the SQL support is already in datafusion-sqlparser-rs, it looks like the main work is on the second part. Let me start exploring what is required to implement this as an extension and get back with any questions.
Thank you @theirix
What I would recommend we do is start working in an example in https://github.com/apache/datafusion/blob/main/datafusion-examples/examples as a way to explore what is possible today. I am happy to take an early look / provide some feedback/ help along the example
I suspect we can do almost everything needed with existing APIs
The high level steps might be:
1. Parse the relevant SQL into some sort of `Statement::Sample`
2. Use the LogicalPlanBuilder to create a plan for that new statement
3. Implement a custom extension node, if necessary, to implement the sampling
Does that make sense?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Support data source sampling with TABLESAMPLE [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #13563:
URL: https://github.com/apache/datafusion/issues/13563#issuecomment-2498989436
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
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Support data source sampling with TABLESAMPLE [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb closed issue #13563: Support data source sampling with TABLESAMPLE
URL: https://github.com/apache/datafusion/issues/13563
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org