Apache.Calcite.Extensions 2.0.0-pre.9

Apache.Calcite.Extensions

Apache.Calcite.Extensions is what .NET adds on top of Apache Calcite running under IKVM: a calling convention that runs a query plan as compiled .NET code, the prepare pipeline that takes a statement from SQL text to such a plan, and the interop types both need.

No Java compiler runs when a statement is prepared, and a .NET user-defined function can be called from SQL.

Most people get this package as a dependency of Apache.Calcite.Data or Apache.Calcite.Adapter.AdoNet and never call it directly — every statement on a CalciteConnection is already planned and run this way, with nothing to configure. Reference it yourself when you want to drive Calcite's planner without an ADO.NET connection, or want typed access to Calcite's connection properties.

Targets .NET 8, and is verified on .NET 8 and .NET 10.

Install

dotnet add package Apache.Calcite.Extensions

Why you might want it

Calcite normally executes a query by generating Java source and compiling it at runtime with Janino. Under IKVM that works, but a Java compiler runs every time you prepare a statement, and any function you call from SQL has to be reachable by a Java class name.

This package replaces that step. A query plan is compiled into a System.Linq.Expressions tree and turned into a delegate, so:

• No Java compiler runs when you prepare a statement.
• A .NET method can be a SQL function, and no class name is written out. Calcite's own engine reaches one only through the class-loader stamp IKVM.Maven.Sdk puts on calcite-core, which IKVM 8.14.0 and 8.15.0 could not read — under those a .NET user-defined function had no plan under EnumerableConvention at all, Janino refusing the cli.-prefixed name IKVM gives a CLR class. IKVM 8.16.0 fixes it; here it never mattered, because nothing writes a name.

ClrEnumerableConvention mirrors Calcite's EnumerableConvention node for node and uses the same row types, and converter rules exist in both directions. A plan may hold nodes of both conventions: anything this convention has no rule for is planned by Calcite as usual, and rows cross between the two untouched.

Running a plan yourself

Plan into the convention with ClrEnumerablePrograms, then compile the root with ClrEnumerableRelImplementor. This example is executed by a test in the repository, so it cannot go stale silently:

using Apache.Calcite.Extensions.Adapter.Enumerable;
using org.apache.calcite;
using org.apache.calcite.tools;

var config = Frameworks.newConfigBuilder()
    .defaultSchema(rootSchema)
    .programs(ClrEnumerablePrograms.Standard())
    .build();

var planner = Frameworks.getPlanner(config);
var logical = planner.rel(planner.validate(planner.parse(sql))).project();

// Standard() is one program, as Programs.standard() is, so this is one transform.
// the logical root's own traits, not an empty set: they carry the collation the ORDER BY produced,
// and SortRemoveRule takes the sort away as unwanted if the required traits do not ask for it
var traits = ClrEnumerablePrograms.DesiredRootTraitSet(logical.getTraitSet());
var physical = (ClrEnumerableRel)planner.transform(0, traits, logical);

// the root is a node of this convention; build its plan and compile it
var implementor = new ClrEnumerableRelImplementor(
    physical.getCluster().getRexBuilder(), new java.util.HashMap());
var lambda = implementor.ImplementRoot(physical, ClrEnumerablePrefer.Array);
var plan = (Func<DataContext, System.Collections.IEnumerable>)lambda.Compile();

foreach (var current in plan(dataContext))
{
    // a one-column result is the value itself, not a row of one
    var row = current as object[] ?? [current];
    Console.WriteLine(string.Join('\t', row));
}

ClrEnumerableInterpretable.ToBindable(...) is the alternative ending: it does the same work and hands back an IClrBindable, which you bind to a DataContext and enumerate. Use the implementor when you want the LambdaExpression itself.

Three things about ClrEnumerablePrograms.Standard() are deliberate and worth knowing before you substitute your own program:

• The calc rules are a separate pass. VolcanoCost.isLt compares row counts and nothing else, so a project and a calc are never cheaper than one another and the planner keeps whichever it saw first. Rewriting unconditionally afterwards as a hep pass is what makes a project's refusal to implement itself safe. Programs.standard() does the same thing for the same reason.
• The planner pass registers Calcite's rules, then this convention's. Programs.standard() installs none and plans with whatever is on the planner, which works because RelOptUtil.registerDefaultRules has already put Calcite's there. Nothing has heard of this convention, so Rules() registers — but it registers Calcite's set as well as ours, not instead of it. Dropping Calcite's takes with it the logical rewrites that belong to no convention, and AVG, every DISTINCT aggregate and every OVER window each need one of those before any planner sees them. It is also what lets a node this convention has no rule for be planned in EnumerableConvention and carried across a converter.
• The decorrelation is Calcite's and is run. It was left out for a while, on the grounds that it rewrites a correlated sub-query into a join and would leave ClrEnumerableCorrelate unreachable. That is not so: a scalar sub-query and an EXISTS do become joins, but an UNNEST over a correlation variable cannot be decorrelated and keeps its correlate — which is how Calcite reaches its own EnumerableCorrelate under Programs.standard() as well.

A Spark handler is not supported: ToBindable throws UnsupportedOperationException if one is enabled, because a Spark handler compiles generated Java source and a plan of this convention is an expression tree.

Key public types

The nodes (ClrEnumerableCalc, ClrEnumerableHashJoin, ClrEnumerableWindow, and the rest) and their rules are public too, so you can subclass or re-register them.

The SQL-text prepare pipeline is internal to these packages. ClrPrepareImpl, ClrSignature and the rest of Apache.Calcite.Extensions.Prepare are not part of the public API surface — Apache.Calcite.Data reaches them through InternalsVisibleTo. To run SQL text, use Apache.Calcite.Data; to drive the planner directly, use the public types above.

CalciteConnectionProperties

Strongly-typed .NET properties over a Calcite java.util.Properties map. Instead of reading and writing raw string keys, you get compile-time-checked access to Calcite's connection options:

using Apache.Calcite.Extensions.Config;
using java.util;
using org.apache.calcite.avatica.util;

var props = new CalciteConnectionProperties();

// Typed setters — no magic strings needed.
props.Lex                  = Lex.MYSQL_ANSI;
props.CaseSensitive        = false;
props.DefaultNullCollation = NullCollation.LOW;
props.Fun                  = "oracle,spatial";
props.TimeZone             = "UTC";
props.ForceDecorrelate     = true;
props.MaterializationsEnabled = false;

Defaults are Calcite's own, read from CalciteConnectionProperty in the version this package references (1.42).

CalciteConnectionPropertiesSchemaMap

Exposes the schema.*-prefixed sub-properties of a CalciteConnectionProperties instance as a typed dictionary, so operand values can be passed to a custom schema factory:

var props = new CalciteConnectionProperties();
props.SchemaProperties["directory"] = "data/csv";
props.SchemaProperties["flavor"]    = "scannable";

Related packages

Further reading

• Apache Calcite documentation
• Source repository

License

Apache License 2.0.