SQL queries with BinaryObject

classic Classic list List threaded Threaded
2 messages Options
Eduardo Julian Eduardo Julian
Reply | Threaded
Open this post in threaded view
|

SQL queries with BinaryObject

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!

--
     
    Eduardo Julian
    Chief Science Officer
    Mobile.1.809.330.4184
    Skype.  eejp1991


     
 
This electronic mail  and its attachments may contain confidential or priviledged information intended only for the address and may be protected by law; they should not be distributed, used or copied without authorization. If you have received this email in error,please notify immediately at 1(809) 330-4184 or send an email to our address:  [hidden email] the sender and delete this message and its attachments. As emails may be altered, Bontix is not liable for messages that have been modified, changed or falsified. Thank you

Este correo electrónico y sus archivos adjuntos pueden contener información confidencial o privilegiada destinado únicamente para la dirección y puede ser protegido por la ley y no deben ser distribuidos, utilizados o copiados sin autorización. Si usted ha recibido este mensaje por error, por favor notifique inmediatamente al 1(809) 330-4184 o envíe un correo electrónico a nuestra dirección: [hidden email] al remitente y borre este mensaje y sus anexos. Como correos electrónicos pueden ser alterado, Bontix no se hace responsable de los mensajes que se han modificado, cambiado o falsificados. Gracias

Alexei Scherbakov Alexei Scherbakov
Reply | Threaded
Open this post in threaded view
|

Re: SQL queries with BinaryObject

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!

--
     
    Eduardo Julian
    Chief Science Officer
    Mobile.1.809.330.4184
    Skype.  eejp1991


     
 
This electronic mail  and its attachments may contain confidential or priviledged information intended only for the address and may be protected by law; they should not be distributed, used or copied without authorization. If you have received this email in error,please notify immediately at 1(809) 330-4184 or send an email to our address:  [hidden email] the sender and delete this message and its attachments. As emails may be altered, Bontix is not liable for messages that have been modified, changed or falsified. Thank you

Este correo electrónico y sus archivos adjuntos pueden contener información confidencial o privilegiada destinado únicamente para la dirección y puede ser protegido por la ley y no deben ser distribuidos, utilizados o copiados sin autorización. Si usted ha recibido este mensaje por error, por favor notifique inmediatamente al 1(809) 330-4184 o envíe un correo electrónico a nuestra dirección: [hidden email] al remitente y borre este mensaje y sus anexos. Como correos electrónicos pueden ser alterado, Bontix no se hace responsable de los mensajes que se han modificado, cambiado o falsificados. Gracias




--

Best regards,
Alexei Scherbakov