Custom SQL Functions

❗️

This is a legacy Apache Ignite documentation

The new documentation is hosted here: https://ignite.apache.org/docs/latest/

Apache Ignite SQL Engine allows extending SQL functions' set, defined by ANSI-99 specification, by an addition of custom SQL functions written in Java.

A custom SQL function is no more than a public static method marked by @QuerySqlFunction annotation.

// Defining a custom SQL function.
public class MyFunctions {
    @QuerySqlFunction
    public static int sqr(int x) {
        return x * x;
    }
}

A class that owns the custom SQL function has to be registered in a specific CacheConfiguration using setSqlFunctionClasses(...) method.

// Preparing a cache configuration.
CacheConfiguration cfg = new CacheConfiguration();

// Registering the class that contains custom SQL functions.
cfg.setSqlFunctionClasses(MyFunctions.class);

After a cache with the configuration above is deployed, you're free to call the custom function from SQL queries as shown below.

// Preparing the query that uses customly defined 'sqr' function.
SqlFieldsQuery query = new SqlFieldsQuery(
  "SELECT name FROM Blocks WHERE sqr(size) > 100");

// Executing the query.
cache.query(query).getAll();

🚧

Classes that are registered with CacheConfiguration.setSqlFunctionClasses(...) have to be added to the classpath of all the nodes where defined custom functions might be executed. Otherwise, you'll get a ClassNotFoundException during custom function's execution time.