[BLOG] Blog post about writing your own SQL dialect / extending SQL with DataFusion

[alamb]

Is your feature request related to a problem or challenge?

#14836

@pepijnve asked in discord:

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:

1. You want to add some feature to SQL (maybe use the motivating example from @pepijnve above)
2. Background: How SQL is normally planned (sql text --> sqlparser AST --> LogicalPlan)
3. Explain how to insert your own parser / point to the examples
4. Show relevant excerpts from the example

Additional context

No response

[Adez017]

take

[Adez017]

hi @alamb so as of my understanding i should pull the blog in datafusion-site repo

[alamb]

hi @alamb so as of my understanding i should pull the blog in datafusion-site repo

Yes please

[Adez017]

sure let me begin the work

[Adez017]

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]

@geoffreyclaude made a really nice PR with examples of extending SQL relations to implement PIVOT/UNPIVOT, TABLESAMPLE, etc

• Add relation planner extension support to customize SQL planning #17843

I think two good examples we could use in the blog post are:

1. support custom operators (adapt the example from https://github.com/apache/datafusion/blob/0f83c1d233499e80fd9b0baf89dd624099c1d1ba/datafusion/core/tests/user_defined/expr_planner.rs#L42-L41)
2. Supporting custom syntax (like TABLESAMPLE, PIVOT, etc) from Add relation planner extension support to customize SQL planning #17843

So we could also enumerate the possible places to customize planning:

1. expression planning with ExprPlanner
2. type planning with TypePlanner
3. after Add relation planner extension support to customize SQL planning #17843, relation planning with RelationPlanner

[Adez017]

Sure @alamb , this would really help

[geoffreyclaude]

@Adez017 I've opened a PR to document "adding your own custom SQL syntax":

• Add library user guide for extending SQL syntax #19265

Have you started working on the blog post? Otherwise I'd be happy to pick up the task.

[Adez017]

Sure @geoffreyclaude , you can take this up

[geoffreyclaude]

take