Using Custom Function in Ignite SQL

Posted by ChenRiang on June 13, 2020

Apache Ignite: 2.8.1

In this example, we will create a custom function that can be use by SQL Ignite.

Ignite Installation

Refer documentation for more infomation.

In this example, we will install Apache Ignite in a Window enviroment.

Create Custom Function

We will create a Custom Function under a class called MyCustomFunction.

In the class we will define a custom function called Is_Pass(int score) which wil return true when score more than 50; return false when less or equal to 50.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
package com.ignite.example.udf;

import org.apache.ignite.cache.query.annotations.QuerySqlFunction;

public class MyCustomFunction {

    @QuerySqlFunction
    public static boolean Is_Pass(int score) {
        if (score > 50) {
            return true;
        } else {
            return false;
        }
    }
}

Compile it into a jar. In our case, we will generate a jar named ignite-sql-custom-function-example-1.0.0.jar.

Register Custom Function

  1. Move the generated jar that contained custom function into Ignite’s libs folder <path>/apache-ignite-2.8.1-bin/libs.

  2. Register the custom function in a template. Edit the configuration file <path>/apache-ignite-2.8.1-bin/config/default-config.xml as below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="
       http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans.xsd">

 <bean class="org.apache.ignite.configuration.IgniteConfiguration">
   <property name="cacheConfiguration">
       <list>
          <bean id="cache-template-bean" abstract="true" class="org.apache.ignite.configuration.CacheConfiguration">
            <property name="name" value="MyTemplate*"/>
            <property name="cacheMode" value="PARTITIONED" />
            <property name="backups" value="1" />
            <property name="sqlFunctionClasses" value="com.ignite.example.udf.MyCustomFunction"/>
          </bean>
       </list>
      </property>
 </bean>
</beans>

Note: The template name have to end with ‘*’, else the template will not be found in runtime.

Source Code : Github

Run Ignite Server

Run the server with <path>/apache-ignite-2.8.1-bin/bin/ignite.bat

Execute Custom Function with SQL

In this example, we will use a tools called DBeaver. Refer here, for more infomation on using it to interact with Ignite.
Use Case: We want to find out student who passed the exam.

  1. Create table in Ignite with the template we register earlier.
    1
    2
    3
    
    CREATE TABLE Student (
      ID LONG PRIMARY KEY, NAME VARCHAR, SCORE INTEGER)
      WITH "template=MyTemplate"
    
  2. Insert dummy data into table.
    1
    2
    3
    
      INSERT INTO Student (ID, NAME,SCORE) VALUES (1, 'Johny', 80);
      INSERT INTO Student (ID, NAME,SCORE) VALUES (2, 'Mark' ,50);
      INSERT INTO Student (ID, NAME,SCORE) VALUES (3, 'Jason', 30);
    
  3. Query student who had passed the exam:
    1
    2
    3
    
    SELECT * 
    FROM Student
    WHERE Is_Pass(score)
    

    Result: Query Result