Examples of extending SQL syntax

[alamb]

Is your feature request related to a problem or challenge?

There are some features, such as table sampling, that are challenging to add to DataFusion

The reason it is challenging is that I think the usecase and semantics 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 ecod

Describe the solution you'd like

A clear and well documented example of extending the SQL supported by DataFusion

Describe alternatives you've considered

Note that @theirix already has a great start here

• feat: Add example for implementing SAMPLE using extension APIs #17633

I would like to assist completing this project

Additional context

Related ticket

• Support data source sampling with TABLESAMPLE #13563 from @theirix
• The related PR from @theirix in feat: Add example for implementing SAMPLE using extension APIs #17633
• A blog post on the subject Blog on Extending SQL to create own SQL Dialects datafusion-site#97 from @Adez017

[theirix]

It's a great initiative. The mentioned blog post "Implementing your own SQL dialect and SQL statements with DataFusion" covers numerous extension points related to new DDL statements. After experimenting and improving SqlToRel to support custom SQL without introducing a new statement, I can draft a follow-up post on how to implement DML customisations in an extension, based on tablesample support.

[Adez017]

It's a great initiative. The mentioned blog post "Implementing your own SQL dialect and SQL statements with DataFusion" covers numerous extension points related to new DDL statements. After experimenting and improving SqlToRel to support custom SQL without introducing a new statement, I can draft a follow-up post on how to implement DML customisations in an extension, based on tablesample support.

Thats awesome @theirix , but i think first we need to make the post on the track so that we can come up with others . what do you think @alamb

[geoffreyclaude]

As I already posted in @theirix's PR, I was already experimenting with this in order to make a MATCH_RECOGNIZE extension. I have a draft PR up here.