Custom SQL Functions | Ignite Documentation

Ignite Summit 2024 — Call For Speakers Now Open — Learn more

Edit

Custom SQL Functions

The SQL Engine can extend the SQL functions' set, defined by the ANSI-99 specification, via the addition of custom SQL functions written in Java.

A custom SQL function is just a public static method marked by the @QuerySqlFunction annotation.

static class SqlFunctions {
    @QuerySqlFunction
    public static int sqr(int x) {
        return x * x;
    }
}

The class that owns the custom SQL function has to be registered in the CacheConfiguration. To do that, use the setSqlFunctionClasses(…​) method.

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

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

IgniteCache cache = ignite.createCache(cfg);

Once you have deployed a cache with the above configuration, you can call the custom function from within SQL queries:

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

// Executing the query.
cache.query(query).getAll();
Note
Classes registered with CacheConfiguration.setSqlFunctionClasses(…​) must be added to the classpath of all the nodes where the defined custom functions might be executed. Otherwise, you will get a ClassNotFoundException error when trying to execute the custom function.