GridGain Developers Hub

Table API

To execute table operations on a specific table, you need to get a specific view of the table and use one of its methods. You can only create new tables by using SQL API.

GridGain supports mapping user objects to table tuples. This ensures that objects created in any programming language can be used for key-value operations directly.

Table Views in GridGain 9

Tuple and Key-Value Views

When working with tables, GridGain offers two approaches: directly handling the data or mapping the data to classes. The direct data handling approach handles data tuples. Alternatively, when mapping data to classes, the data is converted to and from these classes as needed for database interactions.

Record and Key-Value Views

When creating views, you can create a RecordView or KeyValueView. The primary difference between these view types is the API used.

In a RecordView, you create a single “record” that includes all the information about a row to be updated or retrieved from the table, and send this record to the server. This record should contain all the fields, including the primary key.

In a KeyValueView, you work with key-value mappings. Think of it as a dictionary where the key object contains the primary key field or fields, and the value object contains the data fields. This approach is useful when primary key is not directly related to the domain object thus you prefer not to add the primary key to it.

Data Type Support

Time and Date Data Types

Only JavaTime API is supported for working with table views. The following data types are not supported:

  • java.util.Date

  • java.sql.Date

  • java.sql.Time

  • java.sql.Timestamp

Use the following data types instead:

  • java.time.LocalDate

  • java.time.LocalTime

  • java.time.LocalDateTime

  • java.time.Instant

Getting a Table Instance

First, get an instance of the table. To obtain an instance of table, use the IgniteTables.table(String) method. You can also use IgniteTables.tables() method to list all existing tables.

IgniteTables tableApi = client.tables();
List<Table> existingTables = tableApi.tables();
Table firstTable = existingTables.get(0);

Table myTable = tableApi.table("MY_TABLE");
var existingTables = await Client.Tables.GetTablesAsync();
var firstTable = existingTables[0];

var myTable = await Client.Tables.GetTableAsync("MY_TABLE");
using namespace ignite;

auto table_api = client.get_tables();
std::vector<table> existing_tables = table_api.get_tables();
table first_table = existing_tables.front();

std::optional<table> my_table = table_api.get_table("MY_TABLE);

By default, if the schema name is not specified, the PUBLIC schema is used. If a qualified name is specified, the table is taken from the specified schema.

Qualified Table Name Object

Instead of using a string to specify table name, you can create a QualifiedName object to hold a fully qualified table name. GridGain provides 2 methods for creating qualified names:

  • You can parse the fully qualified table name with the parse method:

    QualifiedName myTableName = QualifiedName.parse("PUBLIC.MY_QUALIFIED_TABLE");
    Table myTable = tableApi.table(myTableName);
  • You can provide schema name and table name separately with the of method:

    QualifiedName myTableName = QualifiedName.of("PUBLIC", "MY_TABLE");
    Table myTable = tableApi.table(myTableName);

The provided names must follow SQL syntax rules for identifiers:

  • Identifier must start from a character in the “Lu”, “Ll”, “Lt”, “Lm”, “Lo”, or “Nl” Unicode categories or U+0331 (underscore);

  • Identifier characters (expect for the first one) may be U+00B7 (middle dot), or any character in the “Mn”, “Mc”, “Nd”, “Pc”, or “Cf” Unicode categories;

  • Identifiers that contain any other characters must be quoted with U+2033 (double-quotes);

  • Double-quote inside the identifier must be escaped with 2 double-quote characters.

Any unquoted names will be cast to upper case. In this case, Person and PERSON names are equivalent. To avoid this, add escaped quotes around the name. For example, \"Person\" will be encoded as a case-sensitive Person name. If the name contains the U+2033 (double quote) symbol, it must be escaped as "" (2 double quote symbols).

Foe example:

// Case-insensitive table `MY_TABLE` in a case-insensitive `PUBLIC` schema.
QualifiedName.parse("public.my_table"))

// Case-sensitive table `my_table` in a case-sensitive `public` schema.
QualifiedName.parse("\"public\".\"my_table\""))
// Same as above, but with comma as separator that needs to be surrounded by quote characters.
QualifiedName.of("\"public\"","\"my_table\""))

// Case-sensitive name my"table.
QualifiedName.parse("\"my\"\"table\""));

// Case-sensitive table name `public.my_table` in a default schema.
QualifiedName.parse("\"public.my_table\""));

Basic Table Operations

Once you’ve got a table you need to get a specific view to choose how you want to operate table records.

Tuple Record View

A tuple record view. It can be used to operate table tuples directly. When retrieving data from tuple views, you can use a wide variety of methods to retrieve type-specific data stored in tuples. A full list of methods is available in the Tuple object javadoc.


RecordView<Tuple> accounts = client.tables().table("accounts").recordView();


System.out.println("\nInserting a record into the 'accounts' table...");

Tuple newAccountTuple = Tuple.create()
        .set("accountNumber", 123456)
        .set("firstName", "Val")
        .set("lastName", "Kulichenko")
        .set("balance", 100.00d);

accounts.insert(null, newAccountTuple);


System.out.println("\nRetrieving a record using RecordView API...");

Tuple accountNumberTuple = Tuple.create().set("accountNumber", 123456);

Tuple accountTuple = accounts.get(null, accountNumberTuple);

System.out.println(
        "\nRetrieved record:\n"
                + "    Account Number: " + accountTuple.intValue("accountNumber") + '\n'
                + "    Owner: " + accountTuple.stringValue("firstName") + " " + accountTuple.stringValue("lastName") + '\n'
                + "    Balance: $" + accountTuple.doubleValue("balance"));
IRecordView<IIgniteTuple> view = table.RecordBinaryView;

IIgniteTuple fullRecord = new IgniteTuple
{
  ["id"] = 42,
  ["name"] = "John Doe"
};

await view.UpsertAsync(transaction: null, fullRecord);

IIgniteTuple keyRecord = new IgniteTuple { ["id"] = 42 };
(IIgniteTuple value, bool hasValue) = await view.GetAsync(transaction: null, keyRecord);

Debug.Assert(hasValue);
Debug.Assert(value.FieldCount == 2);
Debug.Assert(value["id"] as int? == 42);
Debug.Assert(value["name"] as string == "John Doe");
record_view<ignite_tuple> view = table.get_record_binary_view();

ignite_tuple record{
  {"id", 42},
  {"name", "John Doe"}
};

view.upsert(nullptr, record);
std::optional<ignite_tuple> res_record = view.get(nullptr, {"id", 42});

assert(res_record.has_value());
assert(res_record->column_count() == 2);
assert(res_record->get<std::int64_t>("id") == 42);
assert(res_record->get<std::string>("name") == "John Doe");

Record View

A record view maps to a user-defined type and enables table operations on user objects which are mapped to table tuples.

Create the type converter:

static class BinaryTypeConverter implements TypeConverter<Address, byte[]> {

    @Override
    public Address toObjectType(byte[] bytes) throws IOException, ClassNotFoundException {
        try (var in = new ObjectInputStream(new ByteArrayInputStream(bytes))) {
            return (Address) in.readObject();
        }
    }

    @Override
    public byte[] toColumnType(Address addr) throws IOException {
        try (var bos = new ByteArrayOutputStream();
             var out = new ObjectOutputStream(bos)) {
            out.writeObject(addr);
            return bos.toByteArray();
        }
    }
}

Then build the mapper and get the RecordView:

var mapper = Mapper.builder(Person.class)
.automap()
.map("address", "address", new BinaryTypeConverter())
.build();

RecordView<Person> view = ignite.tables()
.table("person")
.recordView(mapper);

Perform table operations on your custom objects mapped to table tuples:


RecordView<Account> accounts = client.tables()
        .table("accounts")
        .recordView(Account.class);

System.out.println("\nInserting a record into the 'accounts' table...");

Account newAccount = new Account(
        123456,
        "Val",
        "Kulichenko",
        100.00d
);

accounts.insert(null, newAccount);

System.out.println("\nRetrieving a record using RecordView API...");

Account account = accounts.get(null, new Account(123456));

System.out.println(
        "\nRetrieved record:\n"
            + "    Account Number: " + account.accountNumber + '\n'
            + "    Owner: " + account.firstName + " " + account.lastName + '\n'
            + "    Balance: $" + account.balance);
var pocoView = table.GetRecordView<Poco>();
await pocoView.UpsertAsync(transaction: null, new Poco(42, "John Doe"));
var (value, hasValue) = await pocoView.GetAsync(transaction: null, new Poco(42));

Debug.Assert(hasValue);
Debug.Assert(value.Name == "John Doe");

public record Poco(long Id, string? Name = null);
record_view<person> view = table.get_record_view<person>();

person record(42, "John Doe");

view.upsert(nullptr, record);
std::optional<person> res_record = view.get(nullptr, person{42});

assert(res.has_value());
assert(res->id == 42);
assert(res->name == "John Doe");

Key-Value Tuple View

A tuple key-value view. It can be used to operate table using key and value tuples separately. When retrieving data from tuple views, you can use a wide variety of methods to retrieve type-specific data stored in tuples. A full list of methods is available in the Tuple object javadoc.


KeyValueView<Tuple, Tuple> kvView = client.tables().table("accounts").keyValueView();


System.out.println("\nInserting a key-value pair into the 'accounts' table...");

Tuple key = Tuple.create()
        .set("accountNumber", 123456);

Tuple value = Tuple.create()
        .set("firstName", "Val")
        .set("lastName", "Kulichenko")
        .set("balance", 100.00d);

kvView.put(null, key, value);


System.out.println("\nRetrieving a value using KeyValueView API...");

value = kvView.get(null, key);

System.out.println(
        "\nRetrieved value:\n"
                + "    Account Number: " + key.intValue("accountNumber") + '\n'
                + "    Owner: " + value.stringValue("firstName") + " " + value.stringValue("lastName") + '\n'
                + "    Balance: $" + value.doubleValue("balance"));
IKeyValueView<IIgniteTuple, IIgniteTuple> kvView = table.KeyValueBinaryView;

IIgniteTuple key = new IgniteTuple { ["id"] = 42 };
IIgniteTuple val = new IgniteTuple { ["name"] = "John Doe" };

await kvView.PutAsync(transaction: null, key, val);
(IIgniteTuple? value, bool hasValue) = await kvView.GetAsync(transaction: null, key);

Debug.Assert(hasValue);
Debug.Assert(value.FieldCount == 1);
Debug.Assert(value["name"] as string == "John Doe");
key_value_view<ignite_tuple, ignite_tuple> kv_view = table.get_key_value_binary_view();

ignite_tuple key_tuple{{"id", 42}};
ignite_tuple val_tuple{{"name", "John Doe"}};

kv_view.put(nullptr, key_tuple, val_tuple);
std::optional<ignite_tuple> res_tuple = kv_view.get(nullptr, key_tuple);

assert(res_tuple.has_value());
assert(res_tuple->column_count() == 2);
assert(res_tuple->get<std::int64_t>("id") == 42);
assert(res_tuple->get<std::string>("name") == "John Doe");

Key-Value View

A key-value view with user objects. It can be used to operate table using key and value user objects mapped to table tuples.

KeyValueView<AccountKey, Account> kvView = client.tables()
        .table("accounts")
        .keyValueView(AccountKey.class, Account.class);
System.out.println("\nInserting a key-value pair into the 'accounts' table...");

AccountKey key = new AccountKey(123456);

Account value = new Account(
        "Val",
        "Kulichenko",
        100.00d
);

kvView.put(null, key, value);

System.out.println("\nRetrieving a value using KeyValueView API...");

value = kvView.get(null, key);


System.out.println(
        "\nRetrieved value:\n"
            + "    Account Number: " + key.accountNumber + '\n'
            + "    Owner: " + value.firstName + " " + value.lastName + '\n'
            + "    Balance: $" + value.balance);
IKeyValueView<long, Poco> kvView = table.GetKeyValueView<long, Poco>();

await kvView.PutAsync(transaction: null, 42, new Poco(Id: 0, Name: "John Doe"));
(Poco? value, bool hasValue) = await kvView.GetAsync(transaction: null, 42);

Debug.Assert(hasValue);
Debug.Assert(value.Name == "John Doe");

public record Poco(long Id, string? Name = null);
key_value_view<person, person> kv_view = table.get_key_value_view<person, person>();

kv_view.put(nullptr, {42}, {"John Doe"});
std::optional<person> res = kv_view.get(nullptr, {42});

assert(res.has_value());
assert(res->id == 42);
assert(res->name == "John Doe");

Criterion Queries

GridGain 9 provides the criterion queries that can be used to retrieve data from tables. Criterion queries work with any type of view, returning the appropriate data to the query specified.

The example below shows how you can execute a query within an implicit transaction:

try (Cursor<Map.Entry<Tuple, Tuple>> cursor = kvView.query(
        null,
        and(columnValue("name", equalTo("John Doe")), columnValue("age", greaterThan(20)))
)) {
    // ...
}

The comparison query are specified by using the query() method, and providing the comparison criteria in the columnValue method.

You can also specify the specific transaction to execute the query in to perform the query in that specific transaction.

Ignite client = node.api();
Transaction tx = client.transactions().begin();

try (Cursor<Map.Entry<Tuple, Tuple>> cursor = kvView.query(
        tx,
        and(columnValue("name", equalTo("John Doe")), columnValue("age", greaterThan(20)))
)) {
    // ...
}

tx.commit();

Asynchronous Queries

You can also perform the query asynchronously by using the queryAsync method. This way the query is executed without blocking the thread. For example, you can execute the above query asynchronously:

kvView.queryAsync(null, and(columnValue("name", equalTo("John Doe")), columnValue("age", greaterThan(20))))
        .thenCompose(this::fetchAllRowsInto)
        .join();

This operation uses the thenCompose() method to handle the query results asynchronously in the user-defined fetchAllRowsInto() method. Here is how this method may look like:

Table TTL

When inserting values to the table, you can set expiration time for these values if TTL is enabled in the table. You can set the TTL by using the CREATE TABLE or ALTER TABLE statements.

When adding data to the table, it is enough to put the desired TTL timestamp in the TTL column:

public CompletionStage<Void> fetchAllRowsInto(AsyncCursor<Map.Entry<Tuple, Tuple>> cursor) {
    // Process the current page.
    for (var row : cursor.currentPage()) {
        // ...
    }
    // Finish processing if no more data is currently available.
    if (!cursor.hasMorePages()) {
        return CompletableFuture.completedFuture(null);
    }
    // Request for the next page, then subscribe to the response.
    return cursor.fetchNextPage().thenCompose(this::fetchAllRowsInto);
}

Comparison Expressions

The following expressions are supported in criterion queries:

Expression Description Example

equalTo

Checks if the object is equal to the value.

columnValue("City", equalTo("New York"))

notEqualTo

Checks if the object is not equal to the value.

columnValue("City", notEqualTo("New York"))

greaterThan

Checks if the object is greater than the value.

columnValue("Salary", greaterThan(10000))

greaterThanOrEqualTo

Checks if the object is greater than or equal to the value.

columnValue("Salary", greaterThanOrEqualTo(10000))

lessThan

Checks if the object is less than the value.

columnValue("Salary", lessThan(10000))

lessThanOrEqualTo

Checks if the object is less than or equal to the value.

columnValue("Salary", lessThanOrEqualTo(10000))

nullValue

Checks if the object is null.

columnValue("City", nullValue()

notNullValue

Checks if the object is not null.

columnValue("City", notNullValue())

in

Checks if the object is in the collection.

columnValue("City", in("New York", "Washington"))

notIn

Checks if the object is not in the collection.

columnValue("City", notIn("New York", "Washington"))

Comparison Operators

The following operators are supported in criterion queries:

Operator Description Example

not

Negates the condition.

not(columnValue("City", equalTo("New York")))

and

Used to evaluate multiple conditions at the same time.

and(columnValue("City", equalTo("New York")), columnValue("Salary", greaterThan(10000)))

or

Used to evaluate for at least one matching condition.

or(columnValue("City", equalTo("New York")), columnValue("Salary", greaterThan(10000)))