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
-
Move the generated jar that contained custom function into Ignite’s libs folder
<path>/apache-ignite-2.8.1-bin/libs. -
Register the custom function in a template. Edit the configuration file
<path>/apache-ignite-2.8.1-bin/config/default-config.xmlas 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.
- 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"
- 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);
- Query student who had passed the exam:
1 2 3
SELECT * FROM Student WHERE Is_Pass(score)
Result:
