Using SQL to query Object field stored in Cache ?

classic Classic list List threaded Threaded
3 messages Options
limabean limabean
Reply | Threaded
Open this post in threaded view
|

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,
Alexei Scherbakov Alexei Scherbakov
Reply | Threaded
Open this post in threaded view
|

Re: Using SQL to query Object field stored in Cache ?

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 <[hidden email]>:
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

ObjectQuery.java (4K) Download Attachment
ParamsComparator.java (730 bytes) Download Attachment
limabean limabean
Reply | Threaded
Open this post in threaded view
|

Re: Using SQL to query Object field stored in Cache ?

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.