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 or 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();

Custom SQL function can be a table function. Result of table function is treated as a row set (a table) and can be used by other SQL operators. Custom SQL function is also a public or public static method marked by annotation @QuerySqlTableFunction. Table function must return an Iterable as a row set. Each row can be represented by an Object[] or by a Collection. Row length must match the defined number of column types. Row value types must match the defined column types or be able assigned to them.

static class SqlTableFunctions {
    @QuerySqlTableFunction(columnTypes = {Integer.class, String.class}, columnNames = {"INT_COL", "STR_COL"})
    public static Iterable<Object[]> table_function(int i) {
        return Arrays.asList(
            new Object[] {i, "" + i},
            new Object[] {i * 10, "empty"}
        );
    }

    @QuerySqlTableFunction(alias = "TABLE_FUNC_WITH_ARRAY", columnTypes = {String.class}, columnNames = {"RES_COL"})
    public static Iterable<Object[]> table_function_with_arr(List<Object> array) {
        return array.stream()
            .map(Object::toString)
            .map(str -> new Object[]{str})
            .collect(Collectors.toList());
    }
}

CacheConfiguration cfg = new CacheConfiguration("myCache");

cfg.setSqlFunctionClasses(SqlTableFunctions.class);

IgniteCache cache = ignite.createCache(cfg);

SqlFieldsQuery query = new SqlFieldsQuery("SELECT STR_COL FROM TABLE(TABLE_FUNCTION(10)) WHERE INT_COL > 50");

cache.query(query).getAll();

query = new SqlFieldsQuery("SELECT RES_COL FROM TABLE(TABLE_FUNC_WITH_ARRAY(?))").setArgs(List.of("row1", "row2"));

cache.query(query).getAll();

With custom SQL function you can get access to application attributes set on the client side via SessionContext API.

Note	Creating custom functions in the system schema is forbidden. Creating a custom function in schema 'PUBLIC' is forbidden if the function name interferes with a standard function like 'TYPEOF' or 'SUBSTRING'.

Note	The table functions and application attributes are currently available only with Calcite.

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.

© 2026 The Apache Software Foundation.
Apache, Apache Ignite, the Apache feather and the Apache Ignite logo are either registered trademarks or trademarks of The Apache Software Foundation.