Repository navigation
[BLOG] Blog post about writing your own SQL dialect / extending SQL with DataFusion #16756
Description
Is your feature request related to a problem or challenge?
The project I'm working on uses a parquet-files-on-s3 style approach for its storage. In order to tell DataFusion where all those files are located I'm implementing a catalog that's similar in spirit to duck lake. What I bumped into now is that at the moment there's no support for attaching databases using ATTACH .... CREATE EXTERNAL TABLE... is the closest thing. Has the idea of supporting this been explored already? I couldn't find anything in the issue tracker. Seems like it might be useful for Iceberg, DeltaLake, DuckLake, etc.
I found the duckdb attach and sqlite attach variants in the sqlparser in the meantime. What I had in mind was something a bit more flexible/general purpose. Something closer to CREATE EXTERNAL CATALOG ... STORED AS ... LOCATION ... OPTIONS ... with a extension mechanism.
It turns out there is an example of how to extend the SQL parser (and it is the mechanism that datafusio-cli uses):
https://github.com/apache/datafusion/blob/main/datafusion-examples/examples/sql_dialect.rs
However, it would be nice to make this example more visible / easy to find / understand
Describe the solution you'd like
Write a blog post with a title something like "Implementing your own SQL dialect and SQL statements with DataFusion"
Describe alternatives you've considered
I suggest following the "low key technical evangelism" recipe: teach the readers something about how databases work in general (using DataFusion as an example) and then show how to use DataFusion to do something cool
An outline might be
Introduce the idea at a high level:
- You want to add some feature to SQL (maybe use the motivating example from @pepijnve above)
- Background: How SQL is normally planned (sql text --> sqlparser AST --> LogicalPlan)
- Explain how to insert your own parser / point to the examples
- Show relevant excerpts from the example
Additional context
No response
Activity
Adez017 commentedon Jul 25, 2025
take
Adez017 commentedon Jul 25, 2025
hi @alamb so as of my understanding i should pull the blog in datafusion-site repo
alamb commentedon Jul 25, 2025
Yes please
Adez017 commentedon Jul 25, 2025
sure let me begin the work
Adez017 commentedon Aug 9, 2025
I am now part of the Girl Script Summer of Code, and because of this, I am trying to find a proper schedule to manage the example part. It would be really appreciated if anyone could help with this.
alamb commentedon Dec 4, 2025
@geoffreyclaude made a really nice PR with examples of extending SQL relations to implement PIVOT/UNPIVOT, TABLESAMPLE, etc
I think two good examples we could use in the blog post are:
TABLESAMPLE,PIVOT, etc) from Add relation planner extension support to customize SQL planning #17843So we could also enumerate the possible places to customize planning:
ExprPlannerTypePlannerRelationPlannerAdez017 commentedon Dec 4, 2025
Sure @alamb , this would really help
geoffreyclaude commentedon Dec 12, 2025
@Adez017 I've opened a PR to document "adding your own custom SQL syntax":
Have you started working on the blog post? Otherwise I'd be happy to pick up the task.
Adez017 commentedon Dec 12, 2025
Sure @geoffreyclaude , you can take this up
geoffreyclaude commentedon Dec 15, 2025
take
geoffreyclaude commentedon Dec 18, 2025
I've opened a PR to add an example for the
CREATE EXTERNAL CATALOGsyntax, as when testing it out I noticed the original dialect.rs example was actually already supported by DataFusion!geoffreyclaude commentedon Dec 18, 2025
Also I just saw that there was already a pretty in-progress draft at apache/datafusion-site#97, which I originally missed as it isn't linked to this issue. I've applied the comments on my own draft!
alamb commentedon Dec 19, 2025
Thanks! I will review this post later today
examples: replace sql_dialect with custom_sql_parser example (#19383)