You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Matija Kejžar <Ma...@marand.si> on 2017/12/18 17:38:32 UTC
SQL and backing cache question
Let’s say I create a table using Ignite DDL:
CREATE TABLE IF NOT EXISTS M_WORK_PLAN (
entity_id VARCHAR(36),
entity_version INTEGER,
owner_id VARCHAR(36),
materialisation_version VARCHAR(20),
ehr_id VARCHAR(36),
materialisation_time TIMESTAMP,
activation_time TIMESTAMP,
PRIMARY KEY (ehr_id, owner_id, entity_id)
) WITH "template=partitioned,affinityKey=ehr_id,backups=1,atomicity=transactional,cache_name=M_WORK_PLAN,key_type=WorkPlanKey,value_type=MWorkPlan";
This creates a backing cache called M_WORK_PLAN. So far, so good.
But if I then do a put into this cache through the Cache API (instead of using an SQL insert) like this:
ignite.cache(“M_WORK_PLAN”).put(keyFromWP(mWorkPlan), mWorkPlan);
After which I then do an SQL query:
select * from M_WORK_PLAN
which returns an empty result set, 0 work plans. Now is this correct behaviour? Should not the cache.put() statement effectively result in a new entry in the M_WORK_PLAN table or am I misunderstanding what is going on here?
Furthermore, should not an SQL insert result in a cache.get() method on the M_WORK_PLAN cache returning a matching entry from the table?
Thanks,
M.
Re: SQL and backing cache question
Posted by "slava.koptilin" <sl...@gmail.com>.
Hi Naveen,
> Does it mean we do not need to develop these classes OR
> just dont need to deployed on server node's classpath.
In case of using BinaryObject you do not need to create the key and value
types.
> How do I resolve this issue??
It seems that "CITY_DETAILS_BINARY" cache is not created.
I've just tried a simple example and it works...
Anyway, I would suggest retrieving data through JDBC or Java API.
Thanks!
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: SQL and backing cache question
Posted by Naveen <na...@gmail.com>.
Hi Slava
Approach suggested by you still requires us to build the key and value
classes right, I was under impression that we dont need to build key and
value classes at all.
This is the snippet I got it from the docs. Does it mean we do not need to
develop these classes OR just dont need to deployed on server node's
classpath.
/It enables you to read an arbitrary field from an object's serialized form
without full object deserialization. This ability completely removes the
requirement to have the cache key and value classes deployed on the server
node's classpath./
Another question, I was trying to test REST API for one of the cache I have
created thru Ignite DDL
This is the DDL
CREATE TABLE city_details_binary (
city_id LONG PRIMARY KEY, city_name VARCHAR, state_name VARCHAR )
WITH "backups=1, cache_name=city_details_binary,
key_type=com.ril.edif.cityKey.binary, value_type=com.ril.edif.city.binary,
wrap_key=true, wrap_value=true" ;
!tables output
| PUBLIC | CITY_DETAILS_BINARY
| TABLE
Data
0: jdbc:ignite:thin://127.0.0.1> select * from city_details_binary;
+--------------------------------+--------------------------------+--------------------------------+
| CITY_ID | CITY_NAME |
STATE_NAME |
+--------------------------------+--------------------------------+--------------------------------+
| 1 | mumbai | MH
|
| 2 | hyd | TS
|
This is the error I could get
[15:31:28,449][SEVERE][rest-#52978%IgnitePOC%][GridCacheCommandHandler]
Failed to execute cache command: GridRestCacheRequest
[cacheName=CITY_DETAILS_BINARY, cacheFlags=0, ttl=null,
super=GridRestRequest [destId=null, clientId=null, addr=/10.77.241.8:59038,
cmd=CACHE_GET]]
class org.apache.ignite.IgniteCheckedException: Failed to find cache for
given cache name: CITY_DETAILS_BINARY
at
org.apache.ignite.internal.processors.rest.handlers.cache.GridCacheCommandHandler.localCache(GridCacheCommandHandler.java:815)
at
org.apache.ignite.internal.processors.rest.handlers.cache.GridCacheCommandHandler.executeCommand(GridCacheCommandHandler.java:738)
at
org.apache.ignite.internal.processors.rest.handlers.cache.GridCacheCommandHandler.handleAsync(GridCacheCommandHandler.java:440)
at
org.apache.ignite.internal.processors.rest.GridRestProcessor.handleRequest(GridRestProcessor.java:266)
at
org.apache.ignite.internal.processors.rest.GridRestProcessor.access$100(GridRestProcessor.java:89)
at
org.apache.ignite.internal.processors.rest.GridRestProcessor$2.body(GridRestProcessor.java:155)
at
org.apache.ignite.internal.util.worker.GridWorker.run(GridWorker.java:110)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
[15:31:28,450][SEVERE][rest-#52978%IgnitePOC%][GridRestProcessor] Failed to
handle request: CACHE_GET
class org.apache.ignite.IgniteCheckedException: Failed to find cache for
given cache name: CITY_DETAILS_BINARY
at
org.apache.ignite.internal.processors.rest.handlers.cache.GridCacheCommandHandler.localCache(GridCacheCommandHandler.java:815)
at
org.apache.ignite.internal.processors.rest.handlers.cache.GridCacheCommandHandler.executeCommand(GridCacheCommandHandler.java:738)
at
org.apache.ignite.internal.processors.rest.handlers.cache.GridCacheCommandHandler.handleAsync(GridCacheCommandHandler.java:440)
at
org.apache.ignite.internal.processors.rest.GridRestProcessor.handleRequest(GridRestProcessor.java:266)
at
org.apache.ignite.internal.processors.rest.GridRestProcessor.access$100(GridRestProcessor.java:89)
at
org.apache.ignite.internal.processors.rest.GridRestProcessor$2.body(GridRestProcessor.java:155)
at
org.apache.ignite.internal.util.worker.GridWorker.run(GridWorker.java:110)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
How do I resolve this issue??
Objective is not to write kep and value classed, just use Ignite DDL and
execute the scripts to create tables and start populating the data thru JDBC
OR Java API and retrieve thru aql JDBC, Java API & Rest API..
Thanks
Naveen
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: SQL and backing cache question
Posted by "slava.koptilin" <sl...@gmail.com>.
Hi Naveen,
Yes, you can get a whole key and value objects.
You need to do the following steps:
1. define a key and value types in the following way:
package com.ril.edif;
public class CityKey {
// Please make sure that the field, which is used as primary key,
annotated by AffinityKeyMapped,
// and uses upper case (it means that 'city_id' is not correct name)
@AffinityKeyMapped
private Long CITY_ID;
public CityKey(Long id) {
CITY_ID = id;
}
...
}
public class ValueType {
private String city_name;
private String state_name;
public ValueType(String city_name, String state_name) {
this.city_name = city_name;
this.state_name = state_name;
}
...
}
2. create 'city_table' and insert some value(s)
cache.query(new SqlFieldsQuery(
"CREATE TABLE city_details (city_id LONG PRIMARY KEY, city_name
VARCHAR, state_name VARCHAR) WITH \"" +
"cache_name=city_details, " +
"key_type=com.ril.edif.KeyType, " +
"value_type=com.ril.edif.ValueType\""));
SqlFieldsQuery query = new SqlFieldsQuery("INSERT INTO city_details
(city_id, city_name, state_name) VALUES (?, ?, ?)");
query.setArgs(1L, "Forest Hill", "California");
cache.query(query).getAll();
3. and final step
IgniteCache<KeyType, ValueType> cache = ignite.cache("city_details");
ValueType value = cache.get(new KeyType(1));
Thanks!
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: SQL and backing cache question
Posted by Naveen Kumar <na...@gmail.com>.
This works, I could query the data.
If we dont have POJOs and use binary objects to read and write, how can
make rest API work.
If I understand correctly, Java classes should be on classpath of the
ignite node to work rest API. How can we make rest API work??
Also, instead of reading field by field from the binary object, can we get
the whole object
Which one works better,
Having POJOs for each table or with binary object to store and write from
the performance point of you.
Thanks
Naveen
On 28-Dec-2017 6:45 PM, "slava.koptilin" <sl...@gmail.com> wrote:
Hello Naveen,
It seems, you need to use BinaryObject for that
BinaryObjectBuilder builder =
ignite.binary().builder("com.ril.edif.cityKey");
builder.setField("city_id", new Long(1));
BinaryObject keyValue = builder.build();
IgniteCache<BinaryObject, BinaryObject> cache =
ignite.cache("city_details").withKeepBinary();
BinaryObject po = cache.get(builder.build());
Thanks!
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: SQL and backing cache question
Posted by "slava.koptilin" <sl...@gmail.com>.
Hello Naveen,
It seems, you need to use BinaryObject for that
BinaryObjectBuilder builder =
ignite.binary().builder("com.ril.edif.cityKey");
builder.setField("city_id", new Long(1));
BinaryObject keyValue = builder.build();
IgniteCache<BinaryObject, BinaryObject> cache =
ignite.cache("city_details").withKeepBinary();
BinaryObject po = cache.get(builder.build());
Thanks!
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: SQL and backing cache question
Posted by Naveen <na...@gmail.com>.
Hi Ilya
I was going thru an example using BinaryObject and trying to follow the
same.
I have created a table with the below SQL DDL
CREATE TABLE city_details (
city_id LONG PRIMARY KEY, city_name VARCHAR, state_name VARCHAR )
WITH "backups=1, cache_name=city_details, key_type=com.ril.edif.cityKey,
value_type=com.ril.edif.city" ;
Inserted 2 records thru SQL line and was trying query data thru Java API,
here is the code
+--------------------------------+--------------------------------+--------------------------------+
| CITY_ID | CITY_NAME |
STATE_NAME |
+--------------------------------+--------------------------------+--------------------------------+
| 1 | Mumbai | MH
|
| 2 | Hyd | TS
|
+--------------------------------+--------------------------------+--------------------------------+
IgniteCache<Long, BinaryObject> binaryCache =
ignite.cache("city_details").withKeepBinary();
BinaryObject po = binaryCache.get(new Long(1));
String name = po.field("city_name");
Does not seems to be working, getting java.lang.NullPointerException at
String name = po.field("city_name")
what is wrong with this code.
As the doc mentioned the reason I am going for binary object is to not to
maintain or write POJOs for all the objects and maintain it under classpath.
Does the rest API work for GET if we dont keep classes under classpath
Thanks
Naveen
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: SQL and backing cache question
Posted by "ilya.kasnacheev" <il...@gmail.com>.
Hello Naveen!
No, Ignite does not create the java class for value_type (and key_type). You
can either create the class yourself, or alternatively you can use
BinaryObject to add rows to backing cache, via get/put or DataStreamer for
example.
Please refer to our docs for more detail:
https://apacheignite.readme.io/docs/binary-marshaller#binaryobject-cache-api
e.g. you can do ignite.binary().builder("some.package.MWorkPlan"); and then
call setField() and put().
Regards,
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: SQL and backing cache question
Posted by Naveen Kumar <na...@gmail.com>.
I do have the same question.
When we execute the DDL statement for creating the table thru SQLLINE
with the value_type as some.package.MWorkPlan, does it create the java
class with this name and load it into JVM.
OR we need to create some.package.MWorkPlan class and refer while
creating the table.
Thanks
Naveen
On Tue, Dec 19, 2017 at 10:12 PM, Ilya Kasnacheev
<il...@gmail.com> wrote:
> Hello!
>
> I think that key_type and value_type should be fully qualified:
>
> key_type=some.package.WorkPlanKey,value_type=some.package.MWorkPlan
>
> If they don't match with type used with put(), you will not see the records
> in SELECT.
>
> Please also share the results of cache.size() after insert is done.
>
> Regards,
>
> --
> Ilya Kasnacheev
>
> 2017-12-18 20:38 GMT+03:00 Matija Kejžar <Ma...@marand.si>:
>>
>> Let’s say I create a table using Ignite DDL:
>>
>>
>>
>> CREATE TABLE IF NOT EXISTS M_WORK_PLAN (
>> entity_id VARCHAR(36),
>> entity_version INTEGER,
>> owner_id VARCHAR(36),
>> materialisation_version VARCHAR(20),
>> ehr_id VARCHAR(36),
>> materialisation_time TIMESTAMP,
>> activation_time TIMESTAMP,
>> PRIMARY KEY (ehr_id, owner_id, entity_id)
>> ) WITH
>> "template=partitioned,affinityKey=ehr_id,backups=1,atomicity=transactional,cache_name=M_WORK_PLAN,key_type=WorkPlanKey,value_type=MWorkPlan";
>>
>>
>>
>> This creates a backing cache called M_WORK_PLAN. So far, so good.
>>
>>
>>
>> But if I then do a put into this cache through the Cache API (instead of
>> using an SQL insert) like this:
>>
>>
>>
>> ignite.cache(“M_WORK_PLAN”).put(keyFromWP(mWorkPlan), mWorkPlan);
>>
>>
>>
>> After which I then do an SQL query:
>>
>>
>>
>> select * from M_WORK_PLAN
>>
>>
>>
>> which returns an empty result set, 0 work plans. Now is this correct
>> behaviour? Should not the cache.put() statement effectively result in a new
>> entry in the M_WORK_PLAN table or am I misunderstanding what is going on
>> here?
>>
>>
>>
>> Furthermore, should not an SQL insert result in a cache.get() method on
>> the M_WORK_PLAN cache returning a matching entry from the table?
>>
>>
>>
>> Thanks,
>>
>>
>>
>> M.
>
>
--
Thanks & Regards,
Naveen Bandaru
Re: SQL and backing cache question
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
I think that key_type and value_type should be fully qualified:
key_type=some.package.WorkPlanKey,value_type=some.package.MWorkPlan
If they don't match with type used with put(), you will not see the records
in SELECT.
Please also share the results of cache.size() after insert is done.
Regards,
--
Ilya Kasnacheev
2017-12-18 20:38 GMT+03:00 Matija Kejžar <Ma...@marand.si>:
> Let’s say I create a table using Ignite DDL:
>
>
>
> *CREATE TABLE IF NOT EXISTS *M_WORK_PLAN (
> entity_id *VARCHAR*(36),
> entity_version *INTEGER*,
> owner_id *VARCHAR*(36),
> materialisation_version *VARCHAR*(20),
> ehr_id *VARCHAR*(36),
> materialisation_time *TIMESTAMP*,
> activation_time *TIMESTAMP*,
> *PRIMARY KEY *(ehr_id, owner_id, entity_id)
> ) *WITH *"template=partitioned,affinityKey=ehr_id,backups=1,
> atomicity=transactional,cache_name=M_WORK_PLAN,key_type=
> WorkPlanKey,value_type=MWorkPlan";
>
>
>
> This creates a backing cache called M_WORK_PLAN. So far, so good.
>
>
>
> But if I then do a put into this cache through the Cache API (instead of
> using an SQL insert) like this:
>
>
>
> ignite.cache(*“M_WORK_PLAN”*).put(*keyFromWP*(mWorkPlan), mWorkPlan);
>
>
>
> After which I then do an SQL query:
>
>
>
> select * from M_WORK_PLAN
>
>
>
> which returns an empty result set, 0 work plans. Now is this correct
> behaviour? Should not the cache.put() statement effectively result in a new
> entry in the M_WORK_PLAN table or am I misunderstanding what is going on
> here?
>
>
>
> Furthermore, should not an SQL insert result in a cache.get() method on
> the M_WORK_PLAN cache returning a matching entry from the table?
>
>
>
> Thanks,
>
>
>
> M.
>