You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by David Robinson <dr...@gmail.com> on 2016/05/17 17:56:12 UTC
Using SQL to query Object field stored in Cache ?
With Ignite 1.5.0:
I have two caches.
Cache 1 stores a Person object like this:
personCache.put(id, PersonObj1);
The Person class has only a single field in it declared like this:
@QuerySqlField(index = true)
private int personId;
Cache 2 stores a Person Attribute object like this:
AttributeCache.put(id, PersonAttributeObj1);
The Attribute class has 3 fields in it:
@QuerySqlField(index = true)
private int personId;
@QuerySqlField(index = false)
private String attributeName;
@QuerySqlField(index = false)
private Object attributeValue;
A PersonAttribute value can be any object type - for example, if
attributeName is "height", then
attributeValue could be a Float: 182.88
If attributeName is "haircolor", then attributeValue could be a String: "brown".
I need to be able to write a SQL join query between the Person and
Attribute caches and find all
of the people with height > 182.
When I try to use a SQL join query...something like below (it doesn't
matter if the 182 is set
as a attribute or hard coded in the query)
SqlFieldsQuery sql = new SqlFieldsQuery(
"select PersonCache.personId "
+ "\"" + personCacheName + "\"" + "from PersonCache, "
+ "\"" + attributeCacheName + "\"" + ".AttributeCache where "
+ "PersonCache.personId = AttributeCache.personId "
+ "and AttributeCache.propertyName = " + "\'" + "height" + "\' "
+ "and AttributeCache.value > 182");
I received the following exception from the Ignite Server:
Caused by: class org.apache.ignite.binary.BinaryObjectException:
Invalid flag value: -128
at org.apache.ignite.internal.binary.BinaryReaderExImpl.deserialize(BinaryReaderExImpl.java:1632)
at org.apache.ignite.internal.binary.GridBinaryMarshaller.deserialize(GridBinaryMarshaller.java:292)
at org.apache.ignite.internal.binary.BinaryMarshaller.unmarshal(BinaryMarshaller.java:112)
at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing$5.deserialize(IgniteH2Indexing.java:1491)
at org.h2.util.Utils.deserialize(Utils.java:392)
If value > 182 is taken out of the query, it runs fine.
Ignite does not appear to know how to deserialize an "Object" field
correctly to perform a comparison in SQL.
What is the recommended Ignite way to store Object types like this and
be able to compare/query them
in Ignite SQL ? I do not know ahead of time if something will be a
Long or Integer or String, etc.
Thank you,
Re: Using SQL to query Object field stored in Cache ?
Posted by limabean <dr...@gmail.com>.
Hi Alexei,
I wanted to get back to you on this.
Thank you for the detailed examples, they did help.
Based on your recommendation, I ended up using the first approach where
a type field was added and then a different field stores the value depending
on the type of data that is stored.
This design makes things harder, particularly in multi-model situations,
because
I cannot easily wrap the fact that the data might be in any of several
fields
in the "bean" stored in the cache. But it does work.
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Using-SQL-to-query-Object-field-stored-in-Cache-tp5005p5345.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Using SQL to query Object field stored in Cache ?
Posted by Alexei Scherbakov <al...@gmail.com>.
Hi,
Currently Ignite SQL engine does not support Object type in query
conditions.
It doesn't know how to compare any type(which Object can hold) with
Integer, on example.
Possible workarounds for that would be:
1) Use different fields for different types like:
stringValue, intVaue, etc.
This allows you to use conditions like:
AttributeCache.propertyName = " + "\'" + "height" + "\' "
+ "and AttributeCache.intFalue > 182
2) Use user defined SQL functions like:
public class ParamsComparator {
@QuerySqlFunction
public static boolean compareLongParam(Object param, Long arg) {
return param instanceof Long && ((Long)param).equals(arg);
}
@QuerySqlFunction
public static boolean compareStringParam(Object param, String arg) {
return param instanceof String && ((String)param).equals(arg);
}
}
See fully working example in the attachment. Note the function code
must present on all cluster nodes for this to work.
I recommend using first approach, because it's more elegant and allows
to use indexes on *value fields.
Did this help?
2016-05-17 20:56 GMT+03:00 David Robinson <dr...@gmail.com>:
> With Ignite 1.5.0:
>
> I have two caches.
>
> Cache 1 stores a Person object like this:
>
> personCache.put(id, PersonObj1);
>
> The Person class has only a single field in it declared like this:
>
> @QuerySqlField(index = true)
> private int personId;
>
> Cache 2 stores a Person Attribute object like this:
> AttributeCache.put(id, PersonAttributeObj1);
>
> The Attribute class has 3 fields in it:
>
> @QuerySqlField(index = true)
> private int personId;
>
> @QuerySqlField(index = false)
> private String attributeName;
>
> @QuerySqlField(index = false)
> private Object attributeValue;
>
> A PersonAttribute value can be any object type - for example, if attributeName is "height", then
>
> attributeValue could be a Float: 182.88
>
> If attributeName is "haircolor", then attributeValue could be a String: "brown".
>
> I need to be able to write a SQL join query between the Person and Attribute caches and find all
>
> of the people with height > 182.
>
> When I try to use a SQL join query...something like below (it doesn't matter if the 182 is set
>
> as a attribute or hard coded in the query)
>
>
> SqlFieldsQuery sql = new SqlFieldsQuery(
> "select PersonCache.personId "
> + "\"" + personCacheName + "\"" + "from PersonCache, "
> + "\"" + attributeCacheName + "\"" + ".AttributeCache where "
> + "PersonCache.personId = AttributeCache.personId "
> + "and AttributeCache.propertyName = " + "\'" + "height" + "\' "
> + "and AttributeCache.value > 182");
>
> I received the following exception from the Ignite Server:
>
> Caused by: class org.apache.ignite.binary.BinaryObjectException: Invalid flag value: -128
> at org.apache.ignite.internal.binary.BinaryReaderExImpl.deserialize(BinaryReaderExImpl.java:1632)
> at org.apache.ignite.internal.binary.GridBinaryMarshaller.deserialize(GridBinaryMarshaller.java:292)
> at org.apache.ignite.internal.binary.BinaryMarshaller.unmarshal(BinaryMarshaller.java:112)
> at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing$5.deserialize(IgniteH2Indexing.java:1491)
> at org.h2.util.Utils.deserialize(Utils.java:392)
>
> If value > 182 is taken out of the query, it runs fine.
>
> Ignite does not appear to know how to deserialize an "Object" field correctly to perform a comparison in SQL.
> What is the recommended Ignite way to store Object types like this and be able to compare/query them
> in Ignite SQL ? I do not know ahead of time if something will be a Long or Integer or String, etc.
>
> Thank you,
>
>
--
Best regards,
Alexei Scherbakov