Extending SQL Syntax

DataFusion provides a flexible extension system that allows you to customize SQL parsing and planning without modifying the core codebase. This is useful when you need to:

Support custom operators from other SQL dialects (e.g., PostgreSQL’s -> for JSON)
Add custom data types not natively supported
Implement SQL constructs like TABLESAMPLE, PIVOT/UNPIVOT, or MATCH_RECOGNIZE

You can read more about this topic in the Extending SQL in DataFusion: from ->> to TABLESAMPLE blog.

Architecture Overview

When DataFusion processes a SQL query, it goes through these stages:

┌─────────────┐    ┌─────────┐    ┌──────────────────────┐    ┌─────────────┐
│ SQL String  │───▶│ Parser  │───▶│      SqlToRel        │───▶│ LogicalPlan │
└─────────────┘    └─────────┘    │ (SQL to LogicalPlan) │    └─────────────┘
                                  └──────────────────────┘
                                              │
                                              │ uses
                                              ▼
                                  ┌───────────────────────┐
                                  │  Extension Planners   │
                                  │  • ExprPlanner        │
                                  │  • TypePlanner        │
                                  │  • RelationPlanner    │
                                  └───────────────────────┘

The extension planners intercept specific parts of the SQL AST during the SqlToRel phase and allow you to customize how they are converted to DataFusion’s logical plan.

Extension Points

DataFusion provides three planner traits for extending SQL:

Trait	Purpose	Registration Method
`ExprPlanner`	Custom expressions and operators	`ctx.register_expr_planner()`
`TypePlanner`	Custom SQL data types	`SessionStateBuilder::with_type_planner()`
`RelationPlanner`	Custom FROM clause elements (relations)	`ctx.register_relation_planner()`

Planner Precedence: Multiple ExprPlanners and RelationPlanners can be registered; they are invoked in reverse registration order (last registered wins). Return Original(...) to delegate to the next planner. Only one TypePlanner can be active at a time.

ExprPlanner: Custom Expressions and Operators

Use ExprPlanner to customize how SQL expressions are converted to DataFusion logical expressions. This is useful for:

Custom binary operators (e.g., ->, ->>, @>, ?)
Custom field access patterns
Custom aggregate or window function handling

Available Methods

Category	Methods
Operators	`plan_binary_op`, `plan_any`
Literals	`plan_array_literal`, `plan_dictionary_literal`, `plan_struct_literal`
Functions	`plan_extract`, `plan_substring`, `plan_overlay`, `plan_position`, `plan_make_map`
Identifiers	`plan_field_access`, `plan_compound_identifier`
Aggregates/Windows	`plan_aggregate`, `plan_window`

See the ExprPlanner API documentation for full method signatures.

Example: Custom Arrow Operator

This example maps the -> operator to string concatenation:

use datafusion_expr::planner::{ExprPlanner, PlannerResult, RawBinaryExpr};

#[derive(Debug)]
struct MyCustomPlanner;

impl ExprPlanner for MyCustomPlanner {
    fn plan_binary_op(
        &self,
        expr: RawBinaryExpr,
        _schema: &DFSchema,
    ) -> Result<PlannerResult<RawBinaryExpr>> {
        match &expr.op {
            // Map `->` to string concatenation
            BinaryOperator::Arrow => {
                Ok(PlannerResult::Planned(Expr::BinaryExpr(BinaryExpr {
                    left: Box::new(expr.left.clone()),
                    right: Box::new(expr.right.clone()),
                    op: Operator::StringConcat,
                })))
            }
            _ => Ok(PlannerResult::Original(expr)),
        }
    }
}

#[tokio::main]
async fn main() -> Result<()> {
    // Use postgres dialect to enable `->` operator parsing
    let config = SessionConfig::new()
        .set_str("datafusion.sql_parser.dialect", "postgres");
    let mut ctx = SessionContext::new_with_config(config);

    // Register the custom planner
    ctx.register_expr_planner(Arc::new(MyCustomPlanner))?;

    // Now `->` works as string concatenation
    let results = ctx.sql("SELECT 'hello'->'world'").await?.collect().await?;
    // Returns: "helloworld"
    Ok(())
}

For more details, see the ExprPlanner API documentation and the expr_planner test examples.

TypePlanner: Custom Data Types

Use TypePlanner to map SQL data types to Arrow/DataFusion types. This is useful when you need to support SQL types that aren’t natively recognized.

Example: Custom DATETIME Type

use datafusion_expr::planner::TypePlanner;

#[derive(Debug)]
struct MyTypePlanner;

impl TypePlanner for MyTypePlanner {
    fn plan_type_field(&self, sql_type: &ast::DataType) -> Result<Option<FieldRef>> {
        match sql_type {
            // Map DATETIME(precision) to Arrow Timestamp
            ast::DataType::Datetime(precision) => {
                let time_unit = match precision {
                    Some(0) => TimeUnit::Second,
                    Some(3) => TimeUnit::Millisecond,
                    Some(6) => TimeUnit::Microsecond,
                    None | Some(9) => TimeUnit::Nanosecond,
                    _ => return Ok(None), // Let default handling take over
                };
                Ok(Some(
                    DataType::Timestamp(time_unit, None).into_nullable_field_ref()
                ))
            }
            _ => Ok(None), // Return None for types we don't handle
        }
    }
}

#[tokio::main]
async fn main() -> Result<()> {
    let state = SessionStateBuilder::new()
        .with_default_features()
        .with_type_planner(Arc::new(MyTypePlanner))
        .build();

    let ctx = SessionContext::new_with_state(state);

    // Now DATETIME type is recognized
    ctx.sql("CREATE TABLE events (ts DATETIME(3))").await?;
    Ok(())
}

Example: Supporting the UUID Type

use datafusion_expr::planner::TypePlanner;

#[derive(Debug)]
struct MyTypePlanner;

impl TypePlanner for MyTypePlanner {
    fn plan_type_field(&self, sql_type: &ast::DataType) -> Result<Option<FieldRef>> {
        match sql_type {
            sqlparser::ast::DataType::Uuid => Ok(Some(Arc::new(
                Field::new("", DataType::FixedSizeBinary(16), true).with_metadata(
                    [("ARROW:extension:name".to_string(), "arrow.uuid".to_string())]
                        .into(),
                ),
            ))),
            _ => Ok(None),
        }
    }
}

#[tokio::main]
async fn main() -> Result<()> {
    let state = SessionStateBuilder::new()
        .with_default_features()
        .with_type_planner(Arc::new(MyTypePlanner))
        .build();

    let ctx = SessionContext::new_with_state(state);

    // Now UUID type is recognized
    ctx.sql("CREATE TABLE idx (uuid UUID)").await?;
    Ok(())
}

For more details, see the TypePlanner API documentation.

RelationPlanner: Custom FROM Clause Elements

Use RelationPlanner to handle custom relations in the FROM clause. This enables you to implement SQL constructs like:

TABLESAMPLE for sampling data
PIVOT / UNPIVOT for data reshaping
MATCH_RECOGNIZE for pattern matching
Any custom relation syntax parsed by sqlparser

The RelationPlannerContext

When implementing RelationPlanner, you receive a RelationPlannerContext that provides utilities for planning:

Method	Purpose
`plan(relation)`	Recursively plan a nested relation
`get_cte(name)`	Look up a CTE visible in the current query scope
`sql_to_expr(expr, schema)`	Convert SQL expression to DataFusion Expr
`context_provider()`	Access session configuration, tables, functions

See the RelationPlanner API documentation for additional methods like normalize_ident() and object_name_to_table_reference().

Relation planners are called before DataFusion’s built-in CTE and catalog lookup. If your planner recognizes ordinary relations by name, check get_cte() after determining the relation is an ordinary name that your planner would otherwise handle. Deliberately reserved names and custom syntax can keep their existing precedence. Return RelationPlanning::Original when a CTE is visible so the next planner can handle the relation. When every ordinary name-based planner in the chain performs this check, planning reaches DataFusion’s built-in CTE resolution. Normalize the parsed name first so the lookup follows DataFusion’s existing name matching. Only do this for a table without function arguments: a CTE named numbers shadows numbers, but it does not shadow a call such as numbers(10).

// Run this after recognizing `relation` as an ordinary name handled here.
if let TableFactor::Table { name, args: None, .. } = &relation {
    let table_ref = ctx.object_name_to_table_reference(name.clone())?;
    if ctx.get_cte(&table_ref).is_some() {
        return Ok(RelationPlanning::Original(Box::new(relation)));
    }
}

Implementation Strategies

There are two main approaches when implementing a RelationPlanner:

Rewrite to Standard SQL: Transform custom syntax into equivalent standard operations that DataFusion already knows how to execute (e.g., PIVOT → GROUP BY with CASE expressions). This is the simplest approach when possible.
Custom Logical and Physical Nodes: Create a UserDefinedLogicalNode to represent the operation in the logical plan, along with a custom ExecutionPlan to execute it. Both are required for end-to-end execution.

Example: Basic RelationPlanner Structure

use datafusion_expr::planner::{
    PlannedRelation, RelationPlanner, RelationPlannerContext, RelationPlanning,
};
use datafusion_sql::sqlparser::ast::TableFactor;

#[derive(Debug)]
struct MyRelationPlanner;

impl RelationPlanner for MyRelationPlanner {
    fn plan_relation(
        &self,
        relation: TableFactor,
        ctx: &mut dyn RelationPlannerContext,
    ) -> Result<RelationPlanning> {
        match relation {
            // Handle your custom relation
            TableFactor::Pivot { table, alias, .. } => {
                // Plan the input table
                let input = ctx.plan(*table)?;

                // Transform or wrap the plan as needed
                // ...

                Ok(RelationPlanning::Planned(PlannedRelation::new(input, alias)))
            }

            // Return Original for relations you don't handle
            other => Ok(RelationPlanning::Original(other)),
        }
    }
}

#[tokio::main]
async fn main() -> Result<()> {
    let ctx = SessionContext::new();

    // Register the custom planner
    ctx.register_relation_planner(Arc::new(MyRelationPlanner))?;

    Ok(())
}

Complete Examples

The DataFusion repository includes comprehensive examples demonstrating each approach:

TABLESAMPLE (Custom Logical and Physical Nodes)

The table_sample.rs example shows a complete end-to-end implementation of how to support queries such as:

SELECT * FROM table TABLESAMPLE BERNOULLI(10 PERCENT) REPEATABLE(42)

PIVOT/UNPIVOT (Rewrite Strategy)

The pivot_unpivot.rs example demonstrates rewriting custom syntax to standard SQL for queries such as:

SELECT * FROM sales
  PIVOT (SUM(amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))

Recap

Use ExprPlanner for custom operators and expression handling
Use [TypePlanner for custom SQL data types
Use RelationPlanner for custom FROM clause syntax (TABLESAMPLE, PIVOT, etc.)
Register planners via SessionContext or SessionStateBuilder

Extending SQL Syntax

Extending SQL Syntax

Architecture Overview

Extension Points

ExprPlanner: Custom Expressions and Operators

Available Methods

Example: Custom Arrow Operator

TypePlanner: Custom Data Types

Example: Custom DATETIME Type

Example: Supporting the UUID Type

RelationPlanner: Custom FROM Clause Elements

The RelationPlannerContext

Implementation Strategies

Example: Basic RelationPlanner Structure

Complete Examples

TABLESAMPLE (Custom Logical and Physical Nodes)

PIVOT/UNPIVOT (Rewrite Strategy)

Recap

See Also

评论