We will start with introduction and then showcase usage of Apache Ignite as running sql queries on grid, compute grid, data grid and service grid.
In this post, we will show few examples on using Apache Ignite as Compute Grid, Data Grid, Service Grid and executing SQL queries on Apache Ignite. These are basic examples and use the basic api available. There will be few posts in near future which explains the available API in Compute Grid, Service Grid and Data Grid.
Apache Ignite comes with JDBC Thin driver support to execute SQL queries on the In memory data grid. In the example below, we will create tables, insert data into tables and get data from tables. I will assume that you are running Apache Ignite on your local environment otherwise please read setup guide for running Apache Ignite server.
try (Connection conn = DriverManager.getConnection("jdbc:ignite:thin://127.0.0.1/");
Statement stmt = conn.createStatement();) {
//Point 1
stmt.executeUpdate("CREATE TABLE City (id LONG PRIMARY KEY, name VARCHAR) WITH \"template=replicated\"");
//Point 2
stmt.executeUpdate("CREATE TABLE Person (id LONG, name VARCHAR, city_id LONG, PRIMARY KEY (id, city_id)) WITH \"backups=1, affinityKey=city_id\"");
stmt.executeUpdate("CREATE INDEX idx_city_name ON City (name)");
stmt.executeUpdate("CREATE INDEX idx_person_name ON Person (name)");
}Point 1: We are creating a
Citytable withCacheModeasreplicatedwhich means it will be replicated on whole cluster. There are three possible values for CacheMode which isLOCAL,REPLICATEDandPARTITIONED. We will discuss about this later in detail.Point 2: We are creating
Persontable. You might have noticedaffinityKeybeing used. The purpose ofaffinityKeyis to collate the data together.
try (PreparedStatement stmt = conn.prepareStatement("INSERT INTO City (id, name) VALUES (?, ?)")) {
stmt.setLong(1, 1L);
stmt.setString(2, "Forest Hill");
stmt.executeUpdate();
stmt.setLong(1, 2L);
stmt.setString(2, "Denver");
stmt.executeUpdate();
stmt.setLong(1, 3L);
stmt.setString(2, "St. Petersburg");
stmt.executeUpdate();
}
try (PreparedStatement stmt = conn.prepareStatement("INSERT INTO Person (id, name, city_id) VALUES (?, ?, ?)")) {
stmt.setLong(1, 1L);
stmt.setString(2, "John Doe");
stmt.setLong(3, 3L);
stmt.executeUpdate();
stmt.setLong(1, 2L);
stmt.setString(2, "Jane Roe");
stmt.setLong(3, 2L);
stmt.executeUpdate();
stmt.setLong(1, 3L);
stmt.setString(2, "Mary Major");
stmt.setLong(3, 1L);
stmt.executeUpdate();
stmt.setLong(1, 4L);
stmt.setString(2, "Richard Miles");
stmt.setLong(3, 2L);
stmt.executeUpdate();
}try (Connection conn = DriverManager.getConnection("jdbc:ignite:thin://127.0.0.1/");
Statement stmt = conn.createStatement()) {
try (ResultSet rs = stmt.executeQuery("SELECT p.name, c.name FROM Person p, City c WHERE p.city_id = c.id")) {
while (rs.next())
System.out.println(rs.getString(1) + ", " + rs.getString(2));
}
}This is how you can run sql queries on Apache Ignite. The full example code is available at Github.
In this example, we will use Ignite's compute grid to fetch data.
try (Ignite ignite = Ignition.start(defaultIgniteCfg("cache-reading-compute-engine"))) {
long cityId = 1;
ignite.compute().affinityCall("SQL_PUBLIC_CITY", cityId, new IgniteCallable<List<String>>() {
private static final long serialVersionUID = -131151815825938052L;
@IgniteInstanceResource
private Ignite currentIgniteInstance;
@Override
public List<String> call() throws Exception {
List<String> names = new ArrayList<>();
IgniteCache<BinaryObject, BinaryObject> personCache = currentIgniteInstance.cache("SQL_PUBLIC_PERSON").withKeepBinary();
IgniteBiPredicate<BinaryObject, BinaryObject> filter = (BinaryObject key, BinaryObject value) -> {
return key.hasField("CITY_ID") && key.<Long>field("CITY_ID") == cityId;
};
ScanQuery<BinaryObject, BinaryObject> query = new ScanQuery<>(filter);
try (QueryCursor<Entry<BinaryObject, BinaryObject>> cursor = personCache.query(query)) {
Iterator<Entry<BinaryObject, BinaryObject>> itr = cursor.iterator();
while (itr.hasNext()) {
Entry<BinaryObject, BinaryObject> cache = itr.next();
names.add(cache.getValue().<String>field("NAME"));
}
}
return names;
}
}).forEach(System.out::println);
}In this example, we are getting list of person residing in same city. We are calling compute grid on SQL_PUBLIC_CITY cache to query with affinitykey cityId and the IgniteCallable task. In the IgniteCallable task, we have @IgniteInstanceResource which will be injected by the Ignite server running this task.
This example will usage of Ignite as in memory data grid.
try (Ignite ignite = Ignition.start(defaultIgniteCfg("ignite-data-grid"))) {
IgniteCache personCache = ignite.getOrCreateCache("personCache");
for (int i = 0; i < 10; i++) {
personCache.put(i, "Gaurav " + i);
}
for (int i = 0; i < 10; i++) {
System.out.println(personCache.get(i));
}
}interface TimeService extends Service {
public LocalDateTime currentDateTime();
}static class TimeServiceImpl implements TimeService {
private static final long serialVersionUID = 3977097368864906176L;
@Override
public void cancel(ServiceContext ctx) {
System.out.println("Service is cancelled!");
}
@Override
public void init(ServiceContext ctx) throws Exception {
System.out.println("Service is initialized!");
}
@Override
public void execute(ServiceContext ctx) throws Exception {
System.out.println("Service is deployed!");
}
@Override
public LocalDateTime currentDateTime() {
return LocalDateTime.now();
}
}try (Ignite ignite = Ignition.start(defaultIgniteCfg("ignite-service-grid"))) {
ignite.services().deployClusterSingleton("timeServiceImpl", new TimeServiceImpl());
TimeService timeService = ignite.services().service("timeServiceImpl");
System.out.println("Current time is: " + timeService.currentDateTime());
}If you want to deploy some service on grid than it should implement Service interface. Also, service grid deployments are not zero deployments. You need to put the compiled jars to the Apache Ignite server instance and than need to restart the instance as well.