Apache Ignite supports custom SQL functions to perform utility tasks. The @QuerySqlFunction Java annotation makes a Java method a SQL function. You can call the function from your ANSI-99 SQL queries. In this section, we are going to write a custom SQL function to convert strings. The steps are as follows:

1. Add a Country class to store id, name, and population:

      class Country {
       @QuerySqlField(index = true)
       private long id;
       @QuerySqlField(index = true)
       private String name;
       private long population;

      public Country(long id, String name, long population) {
       super();
       this.id = id;
       this.name = name;
       this.population = population;
       }

      @Override
       public String toString() {
       return "Country [id=" + id + ", name=" + name + ", 
        population=" + population 
       + "]";
       }
      }

2.  Add a test class to verify the SQL functions. We will create a cache, my_country, and store a collection of countries, then use a SQL function in our SqlFieldsQuery to fetch 'India'. Our custom SQL function will convert input strings to uppercase and compare them:

      public class SQLQueryFunctionTest {
        private static final String COUNTRY_CACHE = "my_country";
      
       public static void main(String[] args) {
           IgniteConfiguration cfg = new IgniteConfiguration();
           cfg.setPeerClassLoadingEnabled(true);
           CacheConfiguration<Long, Country> funcCacheConf = new 
           CacheConfiguration<>();
           funcCacheConf.setName(COUNTRY_CACHE);
           funcCacheConf.setIndexedTypes(Long.class, Country.class);
           funcCacheConf.setCacheMode(CacheMode.REPLICATED);

3. In cache configuration, we need to specify the class where the SQL function is written:

      funcCacheConf.setSqlFunctionClasses(SQLQueryFunctionTest.class);
      cfg.setCacheConfiguration(funcCacheConf);

4. Populate the cache:

      try (Ignite ignite = Ignition.start(cfg)) {
       IgniteCache<Long, Country> funcCache =             
      Ignition.ignite().getOrCreateCache(COUNTRY_CACHE);
       long id = 1;
       funcCache.put(id, new Country(id++, "USA", 123456));
       funcCache.put(id, new Country(id++, "India", 23489900));
       funcCache.put(id, new Country(id++, "France", 897633));
       funcCache.put(id, new Country(id++, "England", 666666));

5. Write a SQL field query to select country id and name; in the where clause, we are going to use our custom function, myUpperCase. This function needs to be defined in our class. myUpperCase converts country names to uppercase, which is very similar to the default SQL function upper:

      SqlFieldsQuery fieldQry = new SqlFieldsQuery(
       "select id, name from "my_country".Country where myUpperCase(name) 
        = upper(?) ");

6. We are going to find 'iNdIa' in the cache, but the Country object stores 'India' so they will not match. Our myUpperCase will convert India to INDIA and upper will change the input iNdIa to INDIA:

        System.out.println("Find 'iNdIa' - in cache the name is 'India', 
        so need to make it upper before  comp");
        FieldsQueryCursor<List<?>> result = 
        funcCache.query(fieldQry.setArgs("iNdIa"));
        result.forEach(r -> {
         System.out.println("id=" + r.get(0) + " country=" + r.get(1));
        });
       }
      }

7. Define our custom function. It has to be annotated with @QuerySqlFunction. It takes a string and calls Java String's toUpperCase. When the function is called, it will print the input and converted value:

      @QuerySqlFunction
       public static String myUpperCase(String name) {
       String upperCase = name.toUpperCase();
       System.out.println(String.format("Called the myUpperCase with %s, 
       returning %s", 
       name, upperCase));
       return upperCase;
       }
      }

8. Run the program and see the output:

The next section will cover the compute grid and distributed computation.