SQL queries with BinaryObject

[Eduardo Julian]

Hi, everyone.

I found out about the BinaryObject functionality for storing and manipulating data on Ignite (http://apacheignite.gridgain.org/docs/binary-marshaller).

I decided to adopt it, in order to reduce the hassle during data-migrations.

However, when I try to do SQL queries on a cache that contains data created by building BinaryObjects, I get errors saying that the columns I'm querying are missing.

For example:

Caused by: org.h2.jdbc.JdbcSQLException: Column "IS_ACTIVE" not found; SQL statement:

SELECT "Cache".BinaryObject._key, "Cache".BinaryObject._val FROM "Cache".BinaryObject WHERE is_active = true

I imagined that it was because I had migrated from using Java classes, to directly using BinaryObject, so I set up a QueryEntity with key java.lang.String, and value org.apache.ignite.binary.BinaryObject, and added query-fields for all of the fields I'll be using (including is_active), and yet, I still get the same error.

I looked up on Google, but I've only found examples of ScanQuery with BinaryObject, but no examples of SqlQuery with BinaryObject (https://github.com/apache/ignite/blob/master/examples/src/main/java/org/apache/ignite/examples/datagrid/CacheQueryExample.java).

So I want to ask if it's at all possible to do SQL queries with BinaryObject, and if so, what might I be missing.

Oh, and I forgot to mention that I'm using setStoreKeepBinary and withKeepBinary, so I don't think the problem might be with that.

Thanks in advance for the help!

--

[Alexei Scherbakov]

Hi,

It's possible to do SQL queries with binary objects directly.

You need to set every field using builder.

The fully working example below:

public static void main(String[] args) throws IgniteException {

    Ignite start = Ignition.start("examples/config/example-ignite.xml");
    CacheConfiguration<Integer, BinaryObject> cfg = new CacheConfiguration<>();
    cfg.setQueryEntities(new ArrayList<QueryEntity>() {{
        QueryEntity e = new QueryEntity();
        e.setKeyType("java.lang.Integer");
        e.setValueType("BinaryTest");
        e.setFields(new LinkedHashMap<String, String>(){{
            put("name", "java.lang.String");
        }});
        add(e);
    }});
    IgniteCache<Integer, BinaryObject> cache = start.getOrCreateCache(cfg).withKeepBinary();
    BinaryObjectBuilder builder = start.binary().builder("BinaryTest");
    builder.setField("name", "Test");
    cache.put(1, builder.build());

    QueryCursor<List<?>> query = cache.query(new SqlFieldsQuery("select name from BinaryTest"));
    System.out.println(query.getAll());
}

2016-06-15 10:50 GMT+03:00 Eduardo Julian <[hidden email]>:

Hi, everyone.

I found out about the BinaryObject functionality for storing and manipulating data on Ignite (http://apacheignite.gridgain.org/docs/binary-marshaller).

I decided to adopt it, in order to reduce the hassle during data-migrations.

However, when I try to do SQL queries on a cache that contains data created by building BinaryObjects, I get errors saying that the columns I'm querying are missing.

For example:

Caused by: org.h2.jdbc.JdbcSQLException: Column "IS_ACTIVE" not found; SQL statement:

SELECT "Cache".BinaryObject._key, "Cache".BinaryObject._val FROM "Cache".BinaryObject WHERE is_active = true

I imagined that it was because I had migrated from using Java classes, to directly using BinaryObject, so I set up a QueryEntity with key java.lang.String, and value org.apache.ignite.binary.BinaryObject, and added query-fields for all of the fields I'll be using (including is_active), and yet, I still get the same error.

I looked up on Google, but I've only found examples of ScanQuery with BinaryObject, but no examples of SqlQuery with BinaryObject (https://github.com/apache/ignite/blob/master/examples/src/main/java/org/apache/ignite/examples/datagrid/CacheQueryExample.java).

So I want to ask if it's at all possible to do SQL queries with BinaryObject, and if so, what might I be missing.

Oh, and I forgot to mention that I'm using setStoreKeepBinary and withKeepBinary, so I don't think the problem might be with that.

Thanks in advance for the help!

--

--