Re: Index usage on Joins

[njcstreet]

Thanks for your suggestion - I tried it but overall the query was slower
using this method. The best approach I have found is to put the items from
the group index directly on the table to avoid having to do the join.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Index usage on Joins

[Ilya Kasnacheev]

Hello!

This sounds like a good application point of *enforceJoinOrder=true*.
Consider:
3: jdbc:ignite:thin://localhost> !connect
jdbc:ignite:thin://localhost?enforceJoinOrder=true
4: jdbc:ignite:thin://localhost>
4: jdbc:ignite:thin://localhost> EXPLAIN SELECT f.Date_key,
. . . . . . . . . . . . . . . .> loc.Location_name,
. . . . . . . . . . . . . . . .> SUM(f.Revenue)
. . . . . . . . . . . . . . . .>
. . . . . . . . . . . . . . . .> FROM DimensionProduct pr,
DimensionLocation loc, FactTableRevenue f
. . . . . . . . . . . . . . . .>
. . . . . . . . . . . . . . . .> WHERE pr._key = f.Product_Key AND loc._key
= f.Location_Key
. . . . . . . . . . . . . . . .> AND f.Date_Key = 20200604
. . . . . . . . . . . . . . . .> AND pr.Product_Name = 'Product 1'
. . . . . . . . . . . . . . . .> AND loc.Location_Name IN ('London',
'Paris')
. . . . . . . . . . . . . . . .>
. . . . . . . . . . . . . . . .> GROUP BY f.Date_Key, loc.Location_name;
PLAN  SELECT
F__Z2.DATE_KEY AS __C0_0,
LOC__Z1.LOCATION_NAME AS __C0_1,
SUM(F__Z2.REVENUE) AS __C0_2
FROM PUBLIC.DIMENSIONPRODUCT PR__Z0
/* PUBLIC.IX_PRODUCT_PRODUCT_NAME: PRODUCT_NAME = 'Product 1' */
/* WHERE PR__Z0.PRODUCT_NAME = 'Product 1'
*/
INNER JOIN PUBLIC.DIMENSIONLOCATION LOC__Z1
/* PUBLIC.IX_LOCATION_LOCATION_NAME: LOCATION_NAME IN('London',
'Paris') */
ON 1=1
/* WHERE LOC__Z1.LOCATION_NAME IN('London', 'Paris')
*/
INNER JOIN PUBLIC.FACTTABLEREVENUE F__Z2
/* PUBLIC.IX_REVENUE_DATE_PRODUCT_LOCATION: DATE_KEY = 20200604
AND PRODUCT_KEY = PR__Z0._KEY
AND LOCATION_KEY = LOC__Z1._KEY
 */
ON 1=1
WHERE (LOC__Z1.LOCATION_NAME IN('London', 'Paris'))
AND ((PR__Z0.PRODUCT_NAME = 'Product 1')
AND ((F__Z2.DATE_KEY = 20200604)
AND ((PR__Z0._KEY = F__Z2.PRODUCT_KEY)
AND (LOC__Z1._KEY = F__Z2.LOCATION_KEY
GROUP BY F__Z2.DATE_KEY, LOC__Z1.LOCATION_NAME

PLAN  SELECT
__C0_0 AS DATE_KEY,
__C0_1 AS LOCATION_NAME,
CAST(CAST(SUM(__C0_2) AS DOUBLE) AS DOUBLE) AS __C0_2
FROM PUBLIC.__T0
/* PUBLIC."merge_scan" */
GROUP BY __C0_0, __C0_1

2 rows selected (0,011 seconds)

Is this what you wanted? First we filter pr and loc by varchar, then join
this small result to facts using secondary index.

Regards,
-- 
Ilya Kasnacheev


чт, 4 июн. 2020 г. в 16:58, njcstreet :

> Hi,
>
> I am evaluating Ignite for a data warehouse style system which would have a
> central very large "fact" table with potentially billions of records, and
> several "dimensions" that describe the data. The fact table would be
> partitioned as it is large, and the dimensions would be replicated across
> all nodes. I am using the latest version 2.8.
>
> My question is about index usage and joins. I need to join between the fact
> table (which has the numerical transaction values), and the dimensions
> which
> describe the data (such as product / location). However it seems that
> indexes on the fact table won't be used when joining. I understand that you
> can only use one index per table in a query, so I was hoping to use a group
> index for the query against the fact table, since there are a few
> attributes
> that users will always filter on. Here is an example schema (heavily
> simplified and with little data, but enough to demonstrate that the Explain
> plan is not using the index on the Fact table)
>
> CREATE TABLE IF NOT EXISTS FactTableRevenue (
>
> id int PRIMARY KEY,
>
> date_key int,
> product_key int,
> location_key int,
> revenue float
>
> ) WITH "template=partitioned,backups=0";
>
>
>
> CREATE TABLE IF NOT EXISTS DimensionProduct (
>
> id int PRIMARY KEY,
> product_name varchar
>
> ) WITH "TEMPLATE=REPLICATED";
>
>
>
> CREATE TABLE IF NOT EXISTS DimensionLocation (
>
> id int PRIMARY KEY,
> location_name varchar
>
> )WITH "TEMPLATE=REPLICATED";
>
>
>
> CREATE INDEX ix_product_product_name ON DimensionProduct(product_name);
> CREATE INDEX ix_location_location_name ON DimensionLocation(location_name);
> CREATE INDEX ix_revenue_date_product_location ON FactTableRevenue(date_key,
> product_key, location_key);
>
>
> INSERT INTO DimensionProduct (id, product_name) VALUES (1, 'Product 1');
> INSERT INTO DimensionProduct (id, product_name) VALUES (2, 'Product 2');
> INSERT INTO DimensionProduct (id, product_name) VALUES (3, 'Product 3');
>
> INSERT INTO DimensionLocation (id, location_name) VALUES (1, 'London');
> INSERT INTO DimensionLocation (id, location_name) VALUES (2, 'Paris');
> INSERT INTO DimensionLocation (id, location_name) VALUES (3, 'New York');
>
> INSERT INTO FactTableRevenue (id, date_key, product_key, location_key,
> revenue) VALUES
> (1, 20200604, 1, 1, 500);
>
> INSERT INTO FactTableRevenue (id, date_key, product_key, location_key,
> revenue) VALUES
> (2, 20200604, 1, 2, 700);
>
> INSERT INTO FactTableRevenue (id, date_key, product_key, location_key,
> revenue) VALUES
> (3, 20200604, 1, 3, 90);
>
> INSERT INTO FactTableRevenue (id, date_key, product_key, location_key,
> revenue) VALUES
> (4, 20200604, 

Re: index corrupted error : org.apache.ignite.internal.processors.cache.persistence.tree.CorruptedTreeException: Runtime failure on search row

[Ilya Kasnacheev]

Hello!

It's recommended to upgrate to 2.7.6 because it contains persistence
corruption fixes.

Regards,
-- 
Ilya Kasnacheev


чт, 26 сент. 2019 г. в 12:04, Shiva Kumar :

> Hi Igor,
> Thanks for the response!
> The version I am using is 2.7.0
> Unfortunately, I do not have logs of all the nodes, but I have much more
> extra logs (along with thread dump) of the node which reported index
> corruption and attached the same.
> Sorry as of now I can't share persistence data here.
> I have 4 cache groups each cache groups having many tables.
>
> Here are all index.bin files under the persistence directory.
>
> [ignite@ignite-cluster-ignite-e-1 persistence]$
> [ignite@ignite-cluster-ignite-esoc-1 persistence]$ find
> /opt/ignite/persistence/ -name index.bin
>
> /opt/ignite/persistence/node00-a6103519-fb67-45fd-8646-2b6d8cfac53e/metastorage/index.bin
>
> /opt/ignite/persistence/node00-a6103519-fb67-45fd-8646-2b6d8cfac53e/cache-ignite-sys-cache/index.bin
>
> /opt/ignite/persistence/node00-a6103519-fb67-45fd-8646-2b6d8cfac53e/cache-PUBLIC/index.bin
>
> /opt/ignite/persistence/node00-a6103519-fb67-45fd-8646-2b6d8cfac53e/cacheGroup-groupEternal/index.bin
>
> /opt/ignite/persistence/node00-a6103519-fb67-45fd-8646-2b6d8cfac53e/cacheGroup-groupmin15/index.bin
>
> /opt/ignite/persistence/node00-a6103519-fb67-45fd-8646-2b6d8cfac53e/cacheGroup-groupmin1/index.bin
>
> /opt/ignite/persistence/node00-a6103519-fb67-45fd-8646-2b6d8cfac53e/cacheGroup-groupmin5/index.bin
> [ignite@ignite-cluster-ignite-e-1 persistence]$
>
>
> In this ticket https://issues.apache.org/jira/browse/IGNITE-11252, the
> steps to recover from index corruption is documented but what exactly
> caused the index corruption in my case is unknown.
>
> I think it would be great If index gets corrupted for some reason then the
> node should delete the index and rebuild it again without shutting down the
> node.
>
>
> On Fri, Sep 20, 2019 at 4:19 PM Igor Belyakov 
> wrote:
>
>> Hi,
>>
>> Could you please clarify what version of Ignite you're currently using?
>> Also can you attach full logs from all nodes and if it's possible provide
>> your persistence data for the cache with corrupted index tree ("
>> epro_model_abcd")?
>> By default Ii should be in ${IGNITE_HOME}/work/db/{node}/{cache}
>> directory.
>>
>> Regards,
>> Igor
>>
>> On Fri, Sep 20, 2019 at 1:21 PM Shiva Kumar 
>> wrote:
>>
>>> Hi all,
>>> I have deployed 3 node Ignite cluster with native persistence on
>>> Kubernetes and one of the node crashed with below error message,
>>>
>>> *org.h2.message.DbException: General error: "class
>>> org.apache.ignite.internal.processors.cache.persistence.tree.CorruptedTreeException:
>>> Runtime failure on search row: Row@8cfe967[ key: epro_model_abcdKey
>>> [idHash=822184780, hash=737706081, NE_ID=, NAME=], val: epro_model_abcd
>>> [idHash=60444003, hash=1539928610, epro_ID=51, LONGITUDE=null,
>>> DELETE_TIME=null, VENDOR=null, CREATE_TIME=2019-09-19T20:38:32.361929Z,
>>> UPDATE_TIME=2019-09-19T20:40:05.821447Z, ADDITIONAL_INFO=null,
>>> VALID_UNTIL=2019-11-18T20:38:32.362036Z, TYPE=null, LATITUDE=null], ver:
>>> GridCacheVersion [topVer=180326822, order=1568925345552, nodeOrder=6] ][
>>> 51, 2019-09-19T20:38:32.361929Z, 2019-09-19T20:40:05.821447Z, null,
>>> 2019-11-18T20:38:32.362036Z, , , null, null, null, null, null ]"
>>> [5-197]|*
>>>
>>> Please find attached file [index_corruption.txt] for complete backtrace.
>>>
>>> It looks like the Index got corrupted, I am not sure what exactly caused
>>> the index to corrupt. Any knows issues related to this?
>>>
>>> In my cluster, many applications write into many tables simultaneously
>>> and some queries run on many tables simultaneously and frequently
>>> application deletes unwanted rows[old data] in the tables using *delete
>>> from table* SQL operation.
>>>
>>>
>>

Re: index corrupted error : org.apache.ignite.internal.processors.cache.persistence.tree.CorruptedTreeException: Runtime failure on search row

[Igor Belyakov]

Hi,

Could you please clarify what version of Ignite you're currently using?
Also can you attach full logs from all nodes and if it's possible provide
your persistence data for the cache with corrupted index tree ("
epro_model_abcd")?
By default Ii should be in ${IGNITE_HOME}/work/db/{node}/{cache} directory.

Regards,
Igor

On Fri, Sep 20, 2019 at 1:21 PM Shiva Kumar 
wrote:

> Hi all,
> I have deployed 3 node Ignite cluster with native persistence on
> Kubernetes and one of the node crashed with below error message,
>
> *org.h2.message.DbException: General error: "class
> org.apache.ignite.internal.processors.cache.persistence.tree.CorruptedTreeException:
> Runtime failure on search row: Row@8cfe967[ key: epro_model_abcdKey
> [idHash=822184780, hash=737706081, NE_ID=, NAME=], val: epro_model_abcd
> [idHash=60444003, hash=1539928610, epro_ID=51, LONGITUDE=null,
> DELETE_TIME=null, VENDOR=null, CREATE_TIME=2019-09-19T20:38:32.361929Z,
> UPDATE_TIME=2019-09-19T20:40:05.821447Z, ADDITIONAL_INFO=null,
> VALID_UNTIL=2019-11-18T20:38:32.362036Z, TYPE=null, LATITUDE=null], ver:
> GridCacheVersion [topVer=180326822, order=1568925345552, nodeOrder=6] ][
> 51, 2019-09-19T20:38:32.361929Z, 2019-09-19T20:40:05.821447Z, null,
> 2019-11-18T20:38:32.362036Z, , , null, null, null, null, null ]"
> [5-197]|*
>
> Please find attached file [index_corruption.txt] for complete backtrace.
>
> It looks like the Index got corrupted, I am not sure what exactly caused
> the index to corrupt. Any knows issues related to this?
>
> In my cluster, many applications write into many tables simultaneously and
> some queries run on many tables simultaneously and frequently application
> deletes unwanted rows[old data] in the tables using *delete from table*
> SQL operation.
>
>

Re: Index not getting applied

[ashishb888]

You can make good use of Index Hints.

e.g. SELECT * FROM Person USE INDEX(index_age) WHERE salary > 15 AND age
< 35;



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Index inline size

[Stanislav Lukyanov]

Depends on the use case. Sometimes you want to save the memory as much as
possible, and then you would use a lower inline size.

However, in most cases you actually need a higher value because that will
greatly improve the performance.
Starting 2.7 there are warnings with a recommended size (calculated based on
your actual data) and a way to set it.
A couple of ways to change inline size are missing in the warnings though -
check https://issues.apache.org/jira/browse/IGNITE-11355.

Stan


colinc wrote
> The documentation that you referenced states that the
> IGNITE_MAX_INDEX_PAYLOAD_SIZE system property defines the default max -
> and
> that this defaults to 10.
> 
> Since it's only a maximum value, is there any reason why it can't be a bit
> higher - say 100? Or is it strongly encouraged to keep indexed fields
> shorter than this?
> 
> Regards,
> Colin.
> 
> 
> 
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/





--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Index inline size

[colinc]

The documentation that you referenced states that the
IGNITE_MAX_INDEX_PAYLOAD_SIZE system property defines the default max - and
that this defaults to 10.

Since it's only a maximum value, is there any reason why it can't be a bit
higher - say 100? Or is it strongly encouraged to keep indexed fields
shorter than this?

Regards,
Colin.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Index fragmentation

[Ilya Kasnacheev]

Hello!

We recommend using SSD for persistent clusters which will make what they
call "Logical" fragmentation a non-issue: on SSD reading any block is
roughly equally fast.

I guess that "Internal" fragmentation is still a thing. You should expect
that Index pages are on average half full, and this is expected. However,
in most of use cases indexes do not dominate memory usage so that's not
that much important. And for data pages we try to avoid segmentation.

Regards,
-- 
Ilya Kasnacheev


вт, 5 февр. 2019 г. в 13:42, Davalb :

> Hello,
>
> we use Ignite as a cache in front of our databases (SQL Server, Oracle) and
> asked ourselves if the problem of index fragmentation, which is known from
> SQL databases, could also occur here. (for example see
> https://blog.devart.com/sql-server-index-fragmentation-in-depth.html).
>
> Is there this danger with in memory or persistent caches? How does Ignite
> deal with this problem?
>
> This is especially interesting for tables that are constantly updated.
>
> Do we have to take appropriate measures for index maintenance such as Index
> Reorg/Rebuild?
>
> Regards
> David
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>

Re: Index inline size

[Denis Mekhanikov]

Prasad,

By default *QuerySqlField#inlineSize *is equal to -1, which means, that it
will be chosen automatically.
*CacheConfiguration#setSqlIndexMaxInlineSize *specifies the maximal
automatically calculated
inline size for a cache.
But if *QuerySqlField#inlineSize *is not -1, then it will be used
regardless of the configured maximum.
It should only be less than 2048. Otherwise 2048 will be used.

Judging by the warning message, value 10 is used for the inline size.
Did you specify it manually or was it calculated automatically?

Index inlining documentation:
https://apacheignite-sql.readme.io/docs/create-index#section-index-inlining

Denis

ср, 26 дек. 2018 г. в 18:27, Prasad Bhalerao :

> Hi,
>
> I have set sqlIndexMaxInline size in cache configuration level as follows.
>
> cacheCfg.setSqlIndexMaxInlineSize(100);
>
> But still  I am getting following warning message in log.
>
> WARN  o.a.i.i.p.q.h2.database.H2TreeIndex -  Indexed
> columns of a row cannot be fully inlined into index what may lead to
> slowdown due to additional data page reads, increase index inline size if
> needed (use INLINE_SIZE option for CREATE INDEX command,
> QuerySqlField.inlineSize for annotated classes, or QueryIndex.inlineSize
> for explicit QueryEntity configuration) [cacheName=USERACCOUNTDATA,
> tableName=USER_ACCOUNT_CACHE, idxName=USER_ACCOUNT_IDX4,
> idxCols=(SUBSCRIPTIONID, UNITID, _KEY, AFFINITYID), idxType=SECONDARY,
> curSize=10, recommendedInlineSize=83]
>
> 1) Is it necessary to set inline size using @QuerySqlField annotation?
>
> 2) How do I set inline size in case of group index in following case?
> Do I need to set inline property inside each @QuerySqlField annotation?
>
> public class Person implements Serializable {
>   /** Indexed in a group index with "salary". */
>   @QuerySqlField(orderedGroups={@QuerySqlField.Group(
> name = "age_salary_idx", order = 0, descending = true)})
>   private int age;
>
>   /** Indexed separately and in a group index with "age". */
>   @QuerySqlField(index = true, orderedGroups={@QuerySqlField.Group(
> name = "age_salary_idx", order = 3)})
>   private double salary;
> }
>
>
>
>
>
>
>
> Thanks,
> Prasad
>

Re: Index question.

[Mikael]

No worries, the value is a hashmap that store some configuration and I 
just use index on the key to get the stuff out of there so should not be 
a problem.


Mikael

Den 2018-01-30 kl. 10:19, skrev Stanislav Lukyanov:


To add a detail, value will have an index created for it if it is a 
primitive (or an “SQL-friendly” type like Date).


I don’t think there is an easy way to avoid that. You could use a 
wrapper for the primitive value, but it will also


have some overhead and it’s hard to say whether it will be more 
efficient than having an index for the value.


Stan

*From: *Amir Akhmedov <mailto:amir.akhme...@gmail.com>
*Sent: *30 января 2018 г. 1:32
*To: *user@ignite.apache.org <mailto:user@ignite.apache.org>
*Subject: *Re: Index question.

Ok, then it makes sense.
You cannot pass null into setIndexedTypes(). But if you don't put any 
@QuerySqlField annotation in value class or declare fields/indexes 
through Java API then no columns/indexes will be created in SQL storage.


On Mon, Jan 29, 2018 at 3:59 PM, Mikael <mikael-arons...@telia.com 
<mailto:mikael-arons...@telia.com>> wrote:


Thanks, the reason I need SQL is that my key is not a primitive,
it's a class made of 2 int and one String and I need to query on
parts of the key and not the entire key, like select all keys
where one of the integers is equal to 55 for example.

Mikael

Den 2018-01-29 kl. 19:05, skrev Amir Akhmedov:

Hi Mikael,

1. This is just a warning informing you that Ignite's object
serialization will differ from yours Externalizable
implementation. By default Ignite will serialize all fields in
object and if you want to customize then you need implement
Binarylizable interface or set custom serializer as stated in
warning message.

Even if you did not specify any @QuerySqlField in your object
Ignite stores the whole serialized object in SQL table under
_val field for internal usage.

The open question is why do you need SQL if you are using only
key based search? You can make exactly the same using Java
Cache API.

2. You can leave Externalizable implementation in the class,
it won't hurt.

3. Please check bullet #1, if you don't want indexes then you
don't need create them.

Thanks,

Amir




--

Sincerely Yours Amir Akhmedov

RE: Index question.

[Stanislav Lukyanov]

To add a detail, value will have an index created for it if it is a primitive 
(or an “SQL-friendly” type like Date).
I don’t think there is an easy way to avoid that. You could use a wrapper for 
the primitive value, but it will also 
have some overhead and it’s hard to say whether it will be more efficient than 
having an index for the value. 

Stan

From: Amir Akhmedov
Sent: 30 января 2018 г. 1:32
To: user@ignite.apache.org
Subject: Re: Index question.

Ok, then it makes sense.
You cannot pass null into setIndexedTypes(). But if you don't put any 
@QuerySqlField annotation in value class or declare fields/indexes through Java 
API then no columns/indexes will be created in SQL storage.

On Mon, Jan 29, 2018 at 3:59 PM, Mikael <mikael-arons...@telia.com> wrote:
Thanks, the reason I need SQL is that my key is not a primitive, it's a class 
made of 2 int and one String and I need to query on parts of the key and not 
the entire key, like select all keys where one of the integers is equal to 55 
for example.
Mikael

Den 2018-01-29 kl. 19:05, skrev Amir Akhmedov:
Hi Mikael,
1. This is just a warning informing you that Ignite's object serialization will 
differ from yours Externalizable implementation. By default Ignite will 
serialize all fields in object and if you want to customize then you need 
implement Binarylizable interface or set custom serializer as stated in warning 
message.
Even if you did not specify any @QuerySqlField in your object Ignite stores the 
whole serialized object in SQL table under _val field for internal usage.
The open question is why do you need SQL if you are using only key based 
search? You can make exactly the same using Java Cache API.

2. You can leave Externalizable implementation in the class, it won't hurt.
3. Please check bullet #1, if you don't want indexes then you don't need create 
them.

Thanks,
Amir




-- 
Sincerely Yours Amir Akhmedov

Re: Index question.

[Amir Akhmedov]

Ok, then it makes sense.
You cannot pass null into setIndexedTypes(). But if you don't put any
@QuerySqlField annotation in value class or declare fields/indexes through
Java API then no columns/indexes will be created in SQL storage.

On Mon, Jan 29, 2018 at 3:59 PM, Mikael  wrote:

> Thanks, the reason I need SQL is that my key is not a primitive, it's a
> class made of 2 int and one String and I need to query on parts of the key
> and not the entire key, like select all keys where one of the integers is
> equal to 55 for example.
>
> Mikael
>
> Den 2018-01-29 kl. 19:05, skrev Amir Akhmedov:
>
> Hi Mikael,
>
> 1. This is just a warning informing you that Ignite's object serialization
> will differ from yours Externalizable implementation. By default Ignite
> will serialize all fields in object and if you want to customize then you
> need implement Binarylizable interface or set custom serializer as stated
> in warning message.
> Even if you did not specify any @QuerySqlField in your object Ignite
> stores the whole serialized object in SQL table under _val field for
> internal usage.
> The open question is why do you need SQL if you are using only key based
> search? You can make exactly the same using Java Cache API.
>
> 2. You can leave Externalizable implementation in the class, it won't hurt.
>
> 3. Please check bullet #1, if you don't want indexes then you don't need
> create them.
>
> Thanks,
> Amir
>
>
>


-- 
Sincerely Yours Amir Akhmedov

Re: Index question.

[Mikael]

Thanks, the reason I need SQL is that my key is not a primitive, it's a 
class made of 2 int and one String and I need to query on parts of the 
key and not the entire key, like select all keys where one of the 
integers is equal to 55 for example.


Mikael


Den 2018-01-29 kl. 19:05, skrev Amir Akhmedov:

Hi Mikael,

1. This is just a warning informing you that Ignite's object 
serialization will differ from yours Externalizable implementation. By 
default Ignite will serialize all fields in object and if you want to 
customize then you need implement Binarylizable interface or set 
custom serializer as stated in warning message.
Even if you did not specify any @QuerySqlField in your object Ignite 
stores the whole serialized object in SQL table under _val field for 
internal usage.
The open question is why do you need SQL if you are using only key 
based search? You can make exactly the same using Java Cache API.


2. You can leave Externalizable implementation in the class, it won't 
hurt.


3. Please check bullet #1, if you don't want indexes then you don't 
need create them.


Thanks,
Amir

Re: Index question.

[Amir Akhmedov]

Hi Mikael,

1. This is just a warning informing you that Ignite's object serialization
will differ from yours Externalizable implementation. By default Ignite
will serialize all fields in object and if you want to customize then you
need implement Binarylizable interface or set custom serializer as stated
in warning message.
Even if you did not specify any @QuerySqlField in your object Ignite stores
the whole serialized object in SQL table under _val field for internal
usage.
The open question is why do you need SQL if you are using only key based
search? You can make exactly the same using Java Cache API.

2. You can leave Externalizable implementation in the class, it won't hurt.

3. Please check bullet #1, if you don't want indexes then you don't need
create them.

Thanks,
Amir

Re: index for like criteria

[slava.koptilin]

Hi Rajesh,

> If I execute the following query , will the index would be applied on the
> name field?
Well, the answer is depended on the position of the wildcard character.
For example, if filter pattern does not use the wildcard character (%) at
the very beginning of the filter ("organization%") then SQL engine can use
the characters before the first wildcard during filtering and so, the index
can be used.
In the opposite case (filter expression starts from %), the index cannot be
used and SQL engine has to scan the entire table.

You can check the plan of execution of your SQL expression with EXPLAIN.
explain select * from table where name like 'organization%';
the output will contain something like as follows:
SELECT ...
FROM ...
/*
PUBLIC.TABLE_FIELD_IDX: NAME >='organization' <-- this line indicates that
index was used
*/
...

Thanks,
Slava.




--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Index on a Long ?

[Andrey Mashenkov]

Hi Michael,

There is no need to create index on keys as ignite key-value storage
already has naturally index for it underneath.
It should be enough to register indexing types [1].

Would you please clarify the use case if there is any other questions?


[1]
https://apacheignite.readme.io/v1.8/docs/indexes#section-registering-indexed-types

On Wed, Jan 10, 2018 at 3:20 PM, Mikael  wrote:

> Hi!
>
> How do I create an index on a cache key that is a Long, I can't use
> annotations and the QueryEntity look like it requires a class and field to
> set an index ?
>
> Mikael
>
>
>


-- 
Best regards,
Andrey V. Mashenkov

Re: Index on a Long ?

[slava.koptilin]

Hi Mikael,

You can specify indexed types via CacheConfiguration#setIndexedTypes()
method.
For instance:
CacheConfiguration ccfg = new CacheConfiguration<>();
ccfg.setIndexedTypes(Long.class, Person.class);

Another possible way is DDL statement:
// Create table based on PARTITIONED template with one backup.
cache.query(new SqlFieldsQuery(
"CREATE TABLE person (id LONG, name VARCHAR, city_id LONG, PRIMARY KEY
(id, city_id)) " +
"WITH \"backups=1, affinity_key=city_id\"")).getAll();
// Create an index.
cache.query(new SqlFieldsQuery("CREATE INDEX on Person
(city_id)")).getAll();

[1]
https://apacheignite-sql.readme.io/docs/schema-and-indexes#section-registering-indexed-types
[2] https://apacheignite-sql.readme.io/docs/create-index
[3]
https://github.com/apache/ignite/blob/master/examples/src/main/java/org/apache/ignite/examples/sql/SqlDdlExample.java

Thanks!



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Index not getting created

[Dmitriy Setrakyan]

Hi Naveen,

Affinity mapping is a critical portion of Ignite data distribution and
cannot be changed. For more information, please refer to this
documentation: https://apacheignite.readme.io/docs/affinity-collocation

D.

On Wed, Dec 6, 2017 at 9:20 PM, Naveen  wrote:

> This issue got fixed after clean restart of the cluster and creating the
> caches again.
> I could create the index.
> Do we have any option to set the affinity mapping for the cache which is
> already created and holding data.
>
> Thanks
> Naveen
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>

Re: Index not getting created

[Naveen]

This issue got fixed after clean restart of the cluster and creating the
caches again. 
I could create the index. 
Do we have any option to set the affinity mapping for the cache which is
already created and holding data.

Thanks
Naveen



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Index performance

[Evgenii Zhuravlev]

So, then share benchmarks code

Evgenii

2017-12-04 18:14 GMT+03:00 Ali :

> I would recommend using the default value for this property - 1. But, if
> you want to run a few of long queries, it could be increased.
>
>
> Doesn’t help
>
> I have already tried to execute test with configurations:
>
>
>- 1 request queryParalellism = 1
>- 100 concurrent request queryParalellism = 1
>- 1 request queryParalellism = 48
>- 100 concurrent request queryParalellism = 48
>
>
> For last three tests performance or CPU usage maximum change on 5%
>
> In parallel discusion here http://apache-ignite-users.70518.x6.nabble.com/
> Ignite-poor-performance-td18670.html one of users suggest to
> use parallelism
>
> With best regards
> Alisher Alimov
> alimovalis...@gmail.com
>
> On 4 Dec 2017, at 17:41, Evgenii Zhuravlev 
> wrote:
>
> Hi,
>
> Please share benchmarks code, it's hard to understand why you can't load
> CPU for 100% without your code.
>
> But I see one thing, that definitely could lead to performance problems -
> you've set   - this could
> lead to a lot of context switching since every query could be executed in
> 48 threads. I'm sure that you run a lot of queries in parallel, so, it's a
> very big value for this property, even if you have 48 CPU on a machine.
>
> I would recommend using the default value for this property - 1. But, if
> you want to run a few of long queries, it could be increased.
>
> Also, this property doesn't work for SQL queries, and, actually, for a
> default off-heap cache since version 2.0 at all:
>
> 
>
> Evgenii
>
> 2017-12-04 16:36 GMT+03:00 Ali :
>
>> Hi!
>>
>> I found poor sql index performance and CPU utilisation in ignite 2.3
>> version when try to benchmark cluster for performance.
>>
>> *Cluster configuration*:
>>
>> 6 x 2 E5-2600v2 256GB 10GB link
>> 1 x 4 E7 node (edge) from which tests are running
>>
>> *Ignite config*
>>
>>
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>>
>> 
>> 
>> 
>>
>> 
>> 
>>
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>>
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>>
>>
>> *Query*
>>
>> public Collection find(String mcc, String mnc, String lac, String cid, 
>> long lastSeenDt, long offset, long limit) {
>> SqlQuery qry = new SqlQuery<>(
>> Entry.class,
>> "select * from Test where field-2 = ? and field-3 = ? and 
>> field-4 = ? and field-5 = ? and field-6 >= ? limit ? offset ?"
>> );
>>
>> qry.setArgs(mcc, mnc, lac, cid, lastSeenDt, limit, offset);
>>
>> try (QueryCursor> query = cache.query(qry)) {
>> return StreamSupport.stream(query.spliterator(), false)
>> .map(Cache.Entry::getValue)
>> .collect(Collectors.toList());
>> }
>> }
>>
>>
>> *Benchmark*
>>
>>
>> Benchmark consist of several java processes that call this query through
>> RMI  in {1..20 000} threads but maximum CPU utilisation doesn’t increase
>> more than 9% on each node and doesn’t depend on load
>>
>> simple IgniteCache#get test fully utilise CPU
>>
>>
>>
>> With best regards
>> Alisher Alimov
>> alimovalis...@gmail.com
>>
>>
>
>

Re: Index performance

[Ali]

> I would recommend using the default value for this property - 1. But, if you 
> want to run a few of long queries, it could be increased.

Doesn’t help

I have already tried to execute test with configurations:

1 request queryParalellism = 1
100 concurrent request queryParalellism = 1
1 request queryParalellism = 48
100 concurrent request queryParalellism = 48

For last three tests performance or CPU usage maximum change on 5%

In parallel discusion here 
http://apache-ignite-users.70518.x6.nabble.com/Ignite-poor-performance-td18670.html
 

 one of users suggest to use parallelism  

With best regards
Alisher Alimov
alimovalis...@gmail.com

> On 4 Dec 2017, at 17:41, Evgenii Zhuravlev  wrote:
> 
> Hi,
> 
> Please share benchmarks code, it's hard to understand why you can't load CPU 
> for 100% without your code.
> 
> But I see one thing, that definitely could lead to performance problems - 
> you've set   - this could lead 
> to a lot of context switching since every query could be executed in 48 
> threads. I'm sure that you run a lot of queries in parallel, so, it's a very 
> big value for this property, even if you have 48 CPU on a machine.
> 
> I would recommend using the default value for this property - 1. But, if you 
> want to run a few of long queries, it could be increased.
> 
> Also, this property doesn't work for SQL queries, and, actually, for a 
> default off-heap cache since version 2.0 at all:
> 
> 
> 
> Evgenii
> 
> 2017-12-04 16:36 GMT+03:00 Ali  >:
> Hi!
> 
> I found poor sql index performance and CPU utilisation in ignite 2.3 version 
> when try to benchmark cluster for performance.
> 
> Cluster configuration:
> 
> 6 x 2 E5-2600v2 256GB 10GB link
> 1 x 4 E7 node (edge) from which tests are running
> 
> Ignite config
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> Query
> 
> public Collection find(String mcc, String mnc, String lac, String cid, 
> long lastSeenDt, long offset, long limit) {
> SqlQuery qry = new SqlQuery<>(
> Entry.class,
> "select * from Test where field-2 = ? and field-3 = ? and field-4 
> = ? and field-5 = ? and field-6 >= ? limit ? offset ?"
> );
> 
> qry.setArgs(mcc, mnc, lac, cid, lastSeenDt, limit, offset);
> 
> try (QueryCursor> query = cache.query(qry)) {
> return StreamSupport.stream(query.spliterator(), false)
> .map(Cache.Entry::getValue)
> .collect(Collectors.toList());
> }
> }
> 
> Benchmark 
> 
> 
> Benchmark consist of several java processes that call this query through RMI  
> in {1..20 000} threads but maximum CPU utilisation doesn’t increase more than 
> 9% on each node and doesn’t depend on load
> 
> simple IgniteCache#get test fully utilise CPU 
> 
> 
> 
> With best regards
> Alisher Alimov
> alimovalis...@gmail.com 
> 

Re: index about jdbc thin mode

[afedotov]

Hi,

Just to clarify, the same result I have while running using the thin JDBC
driver.

Class.forName("org.apache.ignite.IgniteJdbcThinDriver");


try (
Connection conn =
DriverManager.getConnection("jdbc:ignite:thin://localhost:10800");
PreparedStatement preparedStatement =
conn.prepareStatement(
"EXPLAIN SELECT FID,FNUMBER FROM
\"customerCache\".CustomerIgniteInfo WHERE FUSEDSTATUS = 3"
);
ResultSet resultSet = preparedStatement.executeQuery();
) {
while (resultSet.next())
System.out.println(resultSet.getObject(1));
}



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Index performance

[Evgenii Zhuravlev]

Hi,

Please share benchmarks code, it's hard to understand why you can't load
CPU for 100% without your code.

But I see one thing, that definitely could lead to performance problems -
you've set   - this could
lead to a lot of context switching since every query could be executed in
48 threads. I'm sure that you run a lot of queries in parallel, so, it's a
very big value for this property, even if you have 48 CPU on a machine.

I would recommend using the default value for this property - 1. But, if
you want to run a few of long queries, it could be increased.

Also, this property doesn't work for SQL queries, and, actually, for a
default off-heap cache since version 2.0 at all:



Evgenii

2017-12-04 16:36 GMT+03:00 Ali :

> Hi!
>
> I found poor sql index performance and CPU utilisation in ignite 2.3
> version when try to benchmark cluster for performance.
>
> *Cluster configuration*:
>
> 6 x 2 E5-2600v2 256GB 10GB link
> 1 x 4 E7 node (edge) from which tests are running
>
> *Ignite config*
>
>
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
>
> 
> 
> 
>
> 
> 
>
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
>
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
>
>
> *Query*
>
> public Collection find(String mcc, String mnc, String lac, String cid, 
> long lastSeenDt, long offset, long limit) {
> SqlQuery qry = new SqlQuery<>(
> Entry.class,
> "select * from Test where field-2 = ? and field-3 = ? and field-4 
> = ? and field-5 = ? and field-6 >= ? limit ? offset ?"
> );
>
> qry.setArgs(mcc, mnc, lac, cid, lastSeenDt, limit, offset);
>
> try (QueryCursor> query = cache.query(qry)) {
> return StreamSupport.stream(query.spliterator(), false)
> .map(Cache.Entry::getValue)
> .collect(Collectors.toList());
> }
> }
>
>
> *Benchmark*
>
>
> Benchmark consist of several java processes that call this query through
> RMI  in {1..20 000} threads but maximum CPU utilisation doesn’t increase
> more than 9% on each node and doesn’t depend on load
>
> simple IgniteCache#get test fully utilise CPU
>
>
>
> With best regards
> Alisher Alimov
> alimovalis...@gmail.com
>
>

Re: index about jdbc thin mode

[afedotov]

Hi,

Not sure, what's wrong with your code.
Either IX_T_BD_StatusFid or IX_T_BD_CUSTOMER should have been taken.

I removed JPA related annotations and checked your code, it gave me the
following plan:
[[SELECT
__Z0.FID AS __C0_0,
__Z0.FNUMBER AS __C0_1
FROM "customerCache".CUSTOMERIGNITEINFO __Z0
/* "customerCache".IX_T_BD_STATUSFID: FUSEDSTATUS = 3 */
WHERE __Z0.FUSEDSTATUS = 3], [SELECT
__C0_0 AS FID,
__C0_1 AS FNUMBER
FROM PUBLIC.__T0
/* "customerCache"."merge_scan" */]]

As you can see IX_T_BD_STATUSFID index was used.

I used the following code to check the case. Could you check it on your
side?

public class GroupIndexes1Main {

public static class CustomerIgniteInfo implements Serializable {
private static final long serialVersionUID = -8065741098718964203L;

@QuerySqlField(index = true, orderedGroups={@QuerySqlField.Group(
name = "IX_T_BD_StatusFid", order = 1)})
private String FID;

@QuerySqlField( orderedGroups={@QuerySqlField.Group(
name = "IX_T_BD_StatusFid", order = 2)})
private String FNUMBER;

@QuerySqlField(orderedGroups={@QuerySqlField.Group(
name = "IX_T_BD_CUSTOMER", order = 1),@QuerySqlField.Group(
name = "IX_T_BD_StatusFid", order = 0)})
private Integer FUSEDSTATUS;

public CustomerIgniteInfo() {
}

public CustomerIgniteInfo(String FID, String FNUMBER, Integer
FUSEDSTATUS) {
this.FID = FID;
this.FNUMBER = FNUMBER;
this.FUSEDSTATUS = FUSEDSTATUS;
}

public String getFID() {
return FID;
}
public void setFID(String id) {
this.FID = id;
}

public String getFNUMBER() {
return FNUMBER;
}

public void setFNUMBER(String fNUMBER) {
FNUMBER = fNUMBER;
}

public Integer getFUSEDSTATUS() {
return FUSEDSTATUS;
}

public void setFUSEDSTATUS(Integer fUSEDSTATUS) {
FUSEDSTATUS = fUSEDSTATUS;
}
}


public static void main(String[] args) {
Ignite ignite = Ignition.start();

IgniteCache cache =
ignite.getOrCreateCache(
new CacheConfiguration("customerCache")
.setIndexedTypes(Long.class, CustomerIgniteInfo.class)
);

LongStream.range(0, 10).forEach(l -> {
cache.put(l, new CustomerIgniteInfo("FID" + l, "FNUMBER" + l,
(int)l));
});

List> qryResults = cache.query(
new SqlFieldsQuery("EXPLAIN SELECT FID,FNUMBER FROM
\"customerCache\".CustomerIgniteInfo WHERE FUSEDSTATUS = 3")
).getAll();
System.out.println(qryResults);
}
}



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Index not getting created

[Taras Ledkov]

Hi,

I see only that "cache not found" at the server.log. Something is wrong.

Is it possible to provide a test as the standalone java class or standalone
GitHub project so that I can run it and reproduce the problem?

On 30.11.2017 20:34, Naveen Kumar wrote:

Hi


Here is the node logs captured with -v option.


[22:56:41,291][SEVERE][client-connector-#618%IgnitePOC%][JdbcRequestHandler]
Failed to execute SQL query [reqId=0, req=JdbcQueryExecuteRequest
[schemaName=PUBLIC, pageSize=1024, maxRows=0, sqlQry=CREATE INDEX
idx_customer_accountId ON "Customer".CUSTOMER (ACCOUNT_ID_LIST),
args=[], stmtType=ANY_STATEMENT_TYPE]]

class org.apache.ignite.internal.processors.query.IgniteSQLException:
Cache doesn't exist: Customer

 at 
org.apache.ignite.internal.processors.query.h2.ddl.DdlStatementsProcessor.convert(DdlStatementsProcessor.java:343)

 at 
org.apache.ignite.internal.processors.query.h2.ddl.DdlStatementsProcessor.runDdlStatement(DdlStatementsProcessor.java:287)

 at 
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryDistributedSqlFields(IgniteH2Indexing.java:1466)

 at 
org.apache.ignite.internal.processors.query.GridQueryProcessor$6.applyx(GridQueryProcessor.java:1966)

 at 
org.apache.ignite.internal.processors.query.GridQueryProcessor$6.applyx(GridQueryProcessor.java:1962)

 at 
org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)

 at 
org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2445)

 at 
org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFieldsNoCache(GridQueryProcessor.java:1971)

 at 
org.apache.ignite.internal.processors.odbc.jdbc.JdbcRequestHandler.executeQuery(JdbcRequestHandler.java:305)

 at 
org.apache.ignite.internal.processors.odbc.jdbc.JdbcRequestHandler.handle(JdbcRequestHandler.java:164)

 at 
org.apache.ignite.internal.processors.odbc.ClientListenerNioListener.onMessage(ClientListenerNioListener.java:137)

 at 
org.apache.ignite.internal.processors.odbc.ClientListenerNioListener.onMessage(ClientListenerNioListener.java:39)

 at 
org.apache.ignite.internal.util.nio.GridNioFilterChain$TailFilter.onMessageReceived(GridNioFilterChain.java:279)

 at 
org.apache.ignite.internal.util.nio.GridNioFilterAdapter.proceedMessageReceived(GridNioFilterAdapter.java:109)

 at 
org.apache.ignite.internal.util.nio.GridNioAsyncNotifyFilter$3.body(GridNioAsyncNotifyFilter.java:97)

 at 
org.apache.ignite.internal.util.worker.GridWorker.run(GridWorker.java:110)

 at 
org.apache.ignite.internal.util.worker.GridWorkerPool$1.run(GridWorkerPool.java:70)

 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)



Select query works fine

0: jdbc:ignite:thin://127.0.0.1> select ACCOUNT_ID_LIST from
"Customer".CUSTOMER where ACCOUNT_ID_LIST ='A10001';

++

|ACCOUNT_ID_LIST |

++

| A10001 |

++

1 row selected (1.342 seconds)


Create index query failed with the below error

0: jdbc:ignite:thin://127.0.0.1> CREATE INDEX idx_customer_accountId
ON "Customer".CUSTOMER (ACCOUNT_ID_LIST);

Error: Cache doesn't exist: Customer (state=5,code=0)

java.sql.SQLException: Cache doesn't exist: Customer

 at 
org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:671)

 at 
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:130)

 at 
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:299)

 at sqlline.Commands.execute(Commands.java:823)

 at sqlline.Commands.sql(Commands.java:733)

 at sqlline.SqlLine.dispatch(SqlLine.java:795)

 at sqlline.SqlLine.begin(SqlLine.java:668)

 at sqlline.SqlLine.start(SqlLine.java:373)

 at sqlline.SqlLine.main(SqlLine.java:265)


selectc query works fine even after issuing the create index query
which is failed

0: jdbc:ignite:thin://127.0.0.1> select ACCOUNT_ID_LIST from
"Customer".CUSTOMER where ACCOUNT_ID_LIST ='A10001';

++

|ACCOUNT_ID_LIST |

++

| A10001 |

++

1 row selected (1.641 seconds)

0: jdbc:ignite:thin://127.0.0.1>

On Thu, Nov 30, 2017 at 9:04 PM, Taras Ledkov  wrote:

Hi,

I cannot reproduce the issue with described steps.
Please check that the cache wasn't destroyed on the server.

i.e. please execute SELECT query again after failed CREATE INDEX.



On 

Re: Index not getting created

[Naveen Kumar]

Hi


Here is the node logs captured with -v option.


[22:56:41,291][SEVERE][client-connector-#618%IgnitePOC%][JdbcRequestHandler]
Failed to execute SQL query [reqId=0, req=JdbcQueryExecuteRequest
[schemaName=PUBLIC, pageSize=1024, maxRows=0, sqlQry=CREATE INDEX
idx_customer_accountId ON "Customer".CUSTOMER (ACCOUNT_ID_LIST),
args=[], stmtType=ANY_STATEMENT_TYPE]]

class org.apache.ignite.internal.processors.query.IgniteSQLException:
Cache doesn't exist: Customer

at 
org.apache.ignite.internal.processors.query.h2.ddl.DdlStatementsProcessor.convert(DdlStatementsProcessor.java:343)

at 
org.apache.ignite.internal.processors.query.h2.ddl.DdlStatementsProcessor.runDdlStatement(DdlStatementsProcessor.java:287)

at 
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryDistributedSqlFields(IgniteH2Indexing.java:1466)

at 
org.apache.ignite.internal.processors.query.GridQueryProcessor$6.applyx(GridQueryProcessor.java:1966)

at 
org.apache.ignite.internal.processors.query.GridQueryProcessor$6.applyx(GridQueryProcessor.java:1962)

at 
org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)

at 
org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2445)

at 
org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFieldsNoCache(GridQueryProcessor.java:1971)

at 
org.apache.ignite.internal.processors.odbc.jdbc.JdbcRequestHandler.executeQuery(JdbcRequestHandler.java:305)

at 
org.apache.ignite.internal.processors.odbc.jdbc.JdbcRequestHandler.handle(JdbcRequestHandler.java:164)

at 
org.apache.ignite.internal.processors.odbc.ClientListenerNioListener.onMessage(ClientListenerNioListener.java:137)

at 
org.apache.ignite.internal.processors.odbc.ClientListenerNioListener.onMessage(ClientListenerNioListener.java:39)

at 
org.apache.ignite.internal.util.nio.GridNioFilterChain$TailFilter.onMessageReceived(GridNioFilterChain.java:279)

at 
org.apache.ignite.internal.util.nio.GridNioFilterAdapter.proceedMessageReceived(GridNioFilterAdapter.java:109)

at 
org.apache.ignite.internal.util.nio.GridNioAsyncNotifyFilter$3.body(GridNioAsyncNotifyFilter.java:97)

at 
org.apache.ignite.internal.util.worker.GridWorker.run(GridWorker.java:110)

at 
org.apache.ignite.internal.util.worker.GridWorkerPool$1.run(GridWorkerPool.java:70)

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)



Select query works fine

0: jdbc:ignite:thin://127.0.0.1> select ACCOUNT_ID_LIST from
"Customer".CUSTOMER where ACCOUNT_ID_LIST ='A10001';

++

|ACCOUNT_ID_LIST |

++

| A10001 |

++

1 row selected (1.342 seconds)


Create index query failed with the below error

0: jdbc:ignite:thin://127.0.0.1> CREATE INDEX idx_customer_accountId
ON "Customer".CUSTOMER (ACCOUNT_ID_LIST);

Error: Cache doesn't exist: Customer (state=5,code=0)

java.sql.SQLException: Cache doesn't exist: Customer

at 
org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:671)

at 
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:130)

at 
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:299)

at sqlline.Commands.execute(Commands.java:823)

at sqlline.Commands.sql(Commands.java:733)

at sqlline.SqlLine.dispatch(SqlLine.java:795)

at sqlline.SqlLine.begin(SqlLine.java:668)

at sqlline.SqlLine.start(SqlLine.java:373)

at sqlline.SqlLine.main(SqlLine.java:265)


selectc query works fine even after issuing the create index query
which is failed

0: jdbc:ignite:thin://127.0.0.1> select ACCOUNT_ID_LIST from
"Customer".CUSTOMER where ACCOUNT_ID_LIST ='A10001';

++

|ACCOUNT_ID_LIST |

++

| A10001 |

++

1 row selected (1.641 seconds)

0: jdbc:ignite:thin://127.0.0.1>

On Thu, Nov 30, 2017 at 9:04 PM, Taras Ledkov  wrote:
> Hi,
>
> I cannot reproduce the issue with described steps.
> Please check that the cache wasn't destroyed on the server.
>
> i.e. please execute SELECT query again after failed CREATE INDEX.
>
>
>
> On 30.11.2017 11:45, Naveen wrote:
>>
>> Has anyone got a chance to look into into this issue where I am trying to
>> create an index, but its throwing an error saying cache does not exist
>>
>> 0: jdbc:ignite:thin://127.0.0.1>  select ACCOUNT_ID_LIST from
>> "Customer".CUSTOMER 

Re: Index not getting created

[Taras Ledkov]

Hi,

I cannot reproduce the issue with described steps.
Please check that the cache wasn't destroyed on the server.

i.e. please execute SELECT query again after failed CREATE INDEX.


On 30.11.2017 11:45, Naveen wrote:

Has anyone got a chance to look into into this issue where I am trying to
create an index, but its throwing an error saying cache does not exist

0: jdbc:ignite:thin://127.0.0.1>  select ACCOUNT_ID_LIST from
"Customer".CUSTOMER where ACCOUNT_ID_LIST ='A10001';
++
|ACCOUNT_ID_LIST |
++
| A10001 |
++
1 row selected (2.078 seconds)

**0: jdbc:ignite:thin://127.0.0.1> CREATE INDEX idx_customer_accountId ON
"Customer".CUSTOMER (ACCOUNT_ID_LIST);*
*Error: Cache doesn't exist: Customer (state=5,code=0)
java.sql.SQLException: Cache doesn't exist: Customer
 at
org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:671)
 at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:130)
 at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:299)
 at sqlline.Commands.execute(Commands.java:823)
 at sqlline.Commands.sql(Commands.java:733)
 at sqlline.SqlLine.dispatch(SqlLine.java:795)
 at sqlline.SqlLine.begin(SqlLine.java:668)
 at sqlline.SqlLine.start(SqlLine.java:373)
 at sqlline.SqlLine.main(SqlLine.java:265)
0: jdbc:ignite:thin://127.0.0.1>




--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/


--
Taras Ledkov
Mail-To: tled...@gridgain.com

Re: Index not getting created

[Alexey Kukushkin]

Naveen,

Your "CREATE INDEX" syntax seems valid to me. Can you start server nodes
with debugging enabled, reproduce the problem and share the debug log
output from the servers?

Re: Index

[Andrey Mashenkov]

Hi Sam,

Default is 'null' obviously, it is by bad design and here is a ticket [1].
It is already fixed in 2.0, where index is SORTED by default.

[1] https://issues.apache.org/jira/browse/IGNITE-4511


On Fri, Apr 21, 2017 at 10:00 PM, javastuff@gmail.com <
javastuff@gmail.com> wrote:

> Resolved it!
>
> I used default constructor for QueryIndex and missed setting IndexType.
> This
> resulted into missing index.
> Added /"idx.setIndexType(QueryIndexType.SORTED)"/, able to see index on H2
> debug console.
>
> What is the default IndexType?
> Is it expected to not throw error when IndexType is not set and ignore
> index
> creation? With default constructor this is likely to happen.
>
> Thanks,
> -Sam
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/Index-tp11969p12162.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>



-- 
Best regards,
Andrey V. Mashenkov

Re: Index

[javastuff....@gmail.com]

Resolved it!

I used default constructor for QueryIndex and missed setting IndexType. This
resulted into missing index.
Added /"idx.setIndexType(QueryIndexType.SORTED)"/, able to see index on H2
debug console.

What is the default IndexType?
Is it expected to not throw error when IndexType is not set and ignore index
creation? With default constructor this is likely to happen.

Thanks,
-Sam



--
View this message in context: 
http://apache-ignite-users.70518.x6.nabble.com/Index-tp11969p12162.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Index

[javastuff....@gmail.com]

I tried REST API and H2 debug console. Do not see index, not sure what is
wrong.

I am creating cache pragmatically and can not use annotations. Below is the
sample based on CacheQueryExample.java. Removed annotations from Person.java
to create Person2.java

 
   /CacheConfiguration personCacheCfg = new
CacheConfiguration<>(PERSON_CACHE);
personCacheCfg.setCacheMode(CacheMode.PARTITIONED);
Collection indx_fields = new ArrayList<>();
indx_fields.add("salary");
LinkedHashMap fields = new LinkedHashMap<>();
fields.put("id",Long.class.getName());
fields.put("salary",Double.class.getName());
QueryIndex idx = new QueryIndex();
idx.setName("SALARY_IDX");
idx.setFieldNames(indx_fields, true); 
Collection idxCollection = new ArrayList<>();
idxCollection.add(idx);
QueryEntity ent = new QueryEntity();
ent.setKeyType(Long.class.getName());
ent.setValueType(Person2.class.getName());
Collection entCollection = new ArrayList<>();
ent.setIndexes(idxCollection);
ent.setFields(fields);
entCollection.add(ent);
personCacheCfg.setQueryEntities(entCollection);
IgniteCache personCache =
ignite.getOrCreateCache(personCacheCfg);/

With this I was expecting to see index named SALARY_IDX on column SALARY.
Can you hep identifying the issue here?

Thanks,
-Sam



--
View this message in context: 
http://apache-ignite-users.70518.x6.nabble.com/Index-tp11969p12095.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Index

[Alexey Kuznetsov]

Hi!

You may try to use
1) https://apacheignite.readme.io/docs/rest-api#cache-metadata

2) org.apache.ignite.internal.visor.cache.VisorCacheMetadataTask (or see
how it is implemented).
But, PLEASE NOTE, this is internal API and may be changed in future
versions of Ignite.


On Tue, Apr 18, 2017 at 12:42 AM, javastuff@gmail.com <
javastuff@gmail.com> wrote:

> Webconsole is the only way? Visor or JMX or logs or sample JAVA API.
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/Index-tp11969p12004.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>



-- 
Alexey Kuznetsov

Re: Index

[javastuff....@gmail.com]

Webconsole is the only way? Visor or JMX or logs or sample JAVA API. 



--
View this message in context: 
http://apache-ignite-users.70518.x6.nabble.com/Index-tp11969p12004.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Index

[Alexey Kuznetsov]

See attached screenshot - how to check indexes.

 



--
View this message in context: 
http://apache-ignite-users.70518.x6.nabble.com/Index-tp11969p11985.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Index

[dkarachentsev]

Hi Sambhav,

You may find created indexes using web-console [1].

[1] https://ignite.apache.org/addons.html#web-console

-Dmitry



--
View this message in context: 
http://apache-ignite-users.70518.x6.nabble.com/Index-tp11969p11980.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Index on complex value object

[Evgenii Zhuravlev]

Hi,

Looks like I've seen same question on apache ignite users list, it may
helps you. Here it is :
http://apache-ignite-users.70518.x6.nabble.com/Ignite-Using-complex-object-with-nested-attributes-and-search-td6556.html

2017-04-11 1:02 GMT+03:00 waterg <jessie.jianwei@gmail.com>:

> Hi Evgenii, thank you for getting back. When you find the issue in Jira,
> would you do me a favor to post the link on this thread?
> Thank you.
>
> Best,
> Jessie
>
> On Mon, Apr 10, 2017 at 1:37 PM, Evgenii Zhuravlev [via Apache Ignite
> Users] <[hidden email]
> <http:///user/SendEmail.jtp?type=node=11868=0>> wrote:
>
>> No, you couldn't create index like this at now. There is an issue for
>> this improvement in Apache Ignite's Jira, I will find that issue and update
>> thread with it.
>>
>> 2017-04-10 18:16 GMT+03:00 waterg <[hidden email]
>> <http:///user/SendEmail.jtp?type=node=11866=0>>:
>>
>>> Hello,
>>>
>>> If I have the following list as value in cache:
>>>
>>> class Sample{
>>> Integer value1;
>>> Date value2;
>>> String value3;
>>> }
>>>
>>> List list= new ArrayList<>();
>>> cache.put(key, list);
>>>
>>> Could I create index on the Sample.value1? on Ignite?
>>>
>>>
>>>
>>> --
>>> View this message in context: http://apache-ignite-users.705
>>> 18.x6.nabble.com/Index-on-complex-value-object-tp11855.html
>>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>>
>>
>>
>>
>> --
>> If you reply to this email, your message will be added to the discussion
>> below:
>> http://apache-ignite-users.70518.x6.nabble.com/Index-on-comp
>> lex-value-object-tp11855p11866.html
>> To unsubscribe from Index on complex value object, click here.
>> NAML
>> <http://apache-ignite-users.70518.x6.nabble.com/template/NamlServlet.jtp?macro=macro_viewer=instant_html%21nabble%3Aemail.naml=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>>
>
>
> --
> View this message in context: Re: Index on complex value object
> <http://apache-ignite-users.70518.x6.nabble.com/Index-on-complex-value-object-tp11855p11868.html>
>
> Sent from the Apache Ignite Users mailing list archive
> <http://apache-ignite-users.70518.x6.nabble.com/> at Nabble.com.
>

Re: Index on complex value object

[waterg]

Hi Evgenii, thank you for getting back. When you find the issue in Jira,
would you do me a favor to post the link on this thread?
Thank you.

Best,
Jessie

On Mon, Apr 10, 2017 at 1:37 PM, Evgenii Zhuravlev [via Apache Ignite
Users]  wrote:

> No, you couldn't create index like this at now. There is an issue for this
> improvement in Apache Ignite's Jira, I will find that issue and update
> thread with it.
>
> 2017-04-10 18:16 GMT+03:00 waterg <[hidden email]
> >:
>
>> Hello,
>>
>> If I have the following list as value in cache:
>>
>> class Sample{
>> Integer value1;
>> Date value2;
>> String value3;
>> }
>>
>> List list= new ArrayList<>();
>> cache.put(key, list);
>>
>> Could I create index on the Sample.value1? on Ignite?
>>
>>
>>
>> --
>> View this message in context: http://apache-ignite-users.705
>> 18.x6.nabble.com/Index-on-complex-value-object-tp11855.html
>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>
>
>
>
> --
> If you reply to this email, your message will be added to the discussion
> below:
> http://apache-ignite-users.70518.x6.nabble.com/Index-on-
> complex-value-object-tp11855p11866.html
> To unsubscribe from Index on complex value object, click here
> 
> .
> NAML
> 
>




--
View this message in context: 
http://apache-ignite-users.70518.x6.nabble.com/Index-on-complex-value-object-tp11855p11868.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Index on complex value object

[Evgenii Zhuravlev]

No, you couldn't create index like this at now. There is an issue for this
improvement in Apache Ignite's Jira, I will find that issue and update
thread with it.

2017-04-10 18:16 GMT+03:00 waterg :

> Hello,
>
> If I have the following list as value in cache:
>
> class Sample{
> Integer value1;
> Date value2;
> String value3;
> }
>
> List list= new ArrayList<>();
> cache.put(key, list);
>
> Could I create index on the Sample.value1? on Ignite?
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/Index-on-complex-value-object-tp11855.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>

Re: Index Maintenance During Transactions

[vkulichenko]

I think Dmitry meant that indexes are updated synchronously with transaction
commit. However, note that SQL queries are currently not transactional, so
you can still get dirty reads in the result set.

-Val



--
View this message in context: 
http://apache-ignite-users.70518.x6.nabble.com/Index-Maintenance-During-Transactions-tp10088p10129.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Index Maintenance During Transactions

[dkarachentsev]

Hi, 

After each transaction all affected indexes are rebuilt, and this behavior
is not configurable for now.

- Dmitry.



--
View this message in context: 
http://apache-ignite-users.70518.x6.nabble.com/Index-Maintenance-During-Transactions-tp10088p10095.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Index wont't be used with QueryEntity configuration

[Rafael Troilo]

Hello Val,

great answer! thank you!

Ciao,

Rafael



On 14.12.2016 20:41, vkulichenko wrote:

When configuring with QueryEntity, Ignite doesn't now the exact field type,
so it creates a regular sorted index by default. You need to specify that
this is a geospatial index explicitly:

new QueryIndex("coords", QueryIndexType.GEOSPATIAL)

-Val



--
View this message in context: 
http://apache-ignite-users.70518.x6.nabble.com/Index-wont-t-be-used-with-QueryEntity-configuration-tp9538p9545.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.


--
Rafael Troilo, Dipl.-Inform. (FH)
   GIScience Research Group
  
   Heidelberg Institute for Geoinformation Technology


   rafael.tro...@uni-heidelberg.de
   http://giscience.uni-hd.de
   http://www.geog.uni-heidelberg.de/gis/heigit.html
  
   Berliner Str. 45 (Mathematikon), D-69120 Heidelberg, Germany

   fon: +49(0)6221 / 54 / 19704

Re: Index wont't be used with QueryEntity configuration

[vkulichenko]

When configuring with QueryEntity, Ignite doesn't now the exact field type,
so it creates a regular sorted index by default. You need to specify that
this is a geospatial index explicitly:

new QueryIndex("coords", QueryIndexType.GEOSPATIAL)

-Val



--
View this message in context: 
http://apache-ignite-users.70518.x6.nabble.com/Index-wont-t-be-used-with-QueryEntity-configuration-tp9538p9545.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: index/exposing SqlField on a composition object?

[vkulichenko]

Hi Binti,

No, you can't do that. You should store Order and Trade separately in Ignite
as well and join these two table when needed. Basically, it sounds like you
don't need to change the data model at all when moving from DB to Ignite,
which means that you can even use SQL queries that you used with the DB.

-Val



--
View this message in context: 
http://apache-ignite-users.70518.x6.nabble.com/index-exposing-SqlField-on-a-composition-object-tp6243p6246.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: index and query org.apache.ignite.spark.IgniteRDD[String,org.apache.spark.sql.Row]

[vkulichenko]

P.S. Here is the ticket for keepBinary flag in IgniteRDD:
https://issues.apache.org/jira/browse/IGNITE-2821



--
View this message in context: 
http://apache-ignite-users.70518.x6.nabble.com/index-and-query-org-apache-ignite-spark-IgniteRDD-String-org-apache-spark-sql-Row-tp3343p3467.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: index and query org.apache.ignite.spark.IgniteRDD[String,org.apache.spark.sql.Row]

[DmitryB]

Spark SQL queries has the same problem



--
View this message in context: 
http://apache-ignite-users.70518.x6.nabble.com/index-and-query-org-apache-ignite-spark-IgniteRDD-String-org-apache-spark-sql-Row-tp3343p3445.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: index and query org.apache.ignite.spark.IgniteRDD[String,org.apache.spark.sql.Row]

[Alexey Goncharuk]

It looks like we are missing an option to tell IgniteRDD to work with
binary objects. When an iterator is created, it tries to deserialize
objects, and since you do not have a corresponding class, the exception
occurs. I will create a ticket for this shortly.

Despite this, you should still be able to query your data using IgniteSQL
queries or Spark Data Frame SQL. Can you give it a shot?

Re: index and query org.apache.ignite.spark.IgniteRDD[String,org.apache.spark.sql.Row]

[DmitryB]

Hi Alexey,

Thanks for advice, with queryEntity.setValueType("DT1") i can save pairs to
cache, but i get another exception when i try to get my data back:

scala> cache.count
[Stage 3:>   (0 + 0) /
1024]16/03/08 01:36:24 ERROR Executor: Exception in task 1.0 in stage 3.0
(TID 5)
javax.cache.CacheException: class org.apache.ignite.IgniteCheckedException:
Failed to read class name from file [id=99745,
file=/usr/ignite/work/marshaller/99745.classname]
at
org.apache.ignite.internal.processors.cache.GridCacheUtils.convertToCacheException(GridCacheUtils.java:1597)
at
org.apache.ignite.internal.processors.cache.query.GridCacheQueryAdapter$CacheQueryFallbackFuture.retryIfPossible(GridCacheQueryAdapter.java:700)
at
org.apache.ignite.internal.processors.cache.query.GridCacheQueryAdapter$CacheQueryFallbackFuture.next(GridCacheQueryAdapter.java:670)
at
org.apache.ignite.internal.processors.cache.IgniteCacheProxy$5.onHasNext(IgniteCacheProxy.java:529)
at
org.apache.ignite.internal.util.GridCloseableIteratorAdapter.hasNextX(GridCloseableIteratorAdapter.java:53)
at
org.apache.ignite.internal.util.lang.GridIteratorAdapter.hasNext(GridIteratorAdapter.java:45)
at
org.apache.ignite.spark.impl.IgniteQueryIterator.hasNext(IgniteQueryIterator.scala:24)
at org.apache.spark.util.Utils$.getIteratorSize(Utils.scala:1595)
at org.apache.spark.rdd.RDD$$anonfun$count$1.apply(RDD.scala:1143)
at org.apache.spark.rdd.RDD$$anonfun$count$1.apply(RDD.scala:1143)
at
org.apache.spark.SparkContext$$anonfun$runJob$5.apply(SparkContext.scala:1858)
at
org.apache.spark.SparkContext$$anonfun$runJob$5.apply(SparkContext.scala:1858)
at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:66)
at org.apache.spark.scheduler.Task.run(Task.scala:89)
at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:213)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)


I have created docker images (spark 1.6 +ignite 1.6), probably you can have
a quick look 
docker run -it dmitryb/ignite-spark:0.0.1

I build ignite from https://git-wip-us.apache.org/repos/asf/ignite using
scala 2.10
mvn clean package -DskipTests -Dscala-2.10


Regards,
Dmitry



--
View this message in context: 
http://apache-ignite-users.70518.x6.nabble.com/index-and-query-org-apache-ignite-spark-IgniteRDD-String-org-apache-spark-sql-Row-tp3343p3388.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: index and query org.apache.ignite.spark.IgniteRDD[String,org.apache.spark.sql.Row]

[Alexey Goncharuk]

Dmitriy,

You should have used the same entity name in QueryEntity as the one you
used when creating a builder, i.e.
queryEntity.setValueType("DT1")
because you can have multiple value types stored in one cache.

I will create a ticket to throw a proper exception when BinaryObject is
used in query entity configuration, it seems like a usability issue for me.
Let us know if this solves your issue.

​--AG

Re: index and query org.apache.ignite.spark.IgniteRDD[String,org.apache.spark.sql.Row]

[DmitryB]

Hi guys,

Thanks for pointing me to the right direction; Provided links about binary
marshaller was quite useful;
Unfortunately i was not able to achieve my final goal; I get a
GridDhtPartitionsExchangeFuture when i call cache.savePairs method; Here is
a full stack:

scala> cache.savePairs(pairRdd)
16/03/07 05:03:09 ERROR GridDhtPartitionsExchangeFuture: Failed to
reinitialize local partitions (preloading will be stopped):
GridDhtPartitionExchangeId [topVer=AffinityTopologyVersion [topVer=1,
minorTopVer=1], nodeId=3bbda0cf, evt=DISCOVERY_CUSTOM_EVT]
class org.apache.ignite.IgniteCheckedException: Failed to initialize
property 'id' for key class 'class java.lang.Object' and value class
'interface org.apache.ignite.binary.BinaryObject'. Make sure that one of
these classes contains respective getter method or field.
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.buildClassProperty(GridQueryProcessor.java:1638)
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.processClassMeta(GridQueryProcessor.java:1517)
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.initializeCache(GridQueryProcessor.java:279)
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.onCacheStart(GridQueryProcessor.java:462)
at
org.apache.ignite.internal.processors.cache.GridCacheProcessor.startCache(GridCacheProcessor.java:1039)
at
org.apache.ignite.internal.processors.cache.GridCacheProcessor.prepareCacheStart(GridCacheProcessor.java:1649)
at
org.apache.ignite.internal.processors.cache.GridCacheProcessor.prepareCachesStart(GridCacheProcessor.java:1564)
at
org.apache.ignite.internal.processors.cache.distributed.dht.preloader.GridDhtPartitionsExchangeFuture.startCaches(GridDhtPartitionsExchangeFuture.java:961)
at
org.apache.ignite.internal.processors.cache.distributed.dht.preloader.GridDhtPartitionsExchangeFuture.init(GridDhtPartitionsExchangeFuture.java:524)
at
org.apache.ignite.internal.processors.cache.GridCachePartitionExchangeManager$ExchangeWorker.body(GridCachePartitionExchangeManager.java:1297)
at
org.apache.ignite.internal.util.worker.GridWorker.run(GridWorker.java:110)
at java.lang.Thread.run(Thread.java:745)
16/03/07 05:03:09 ERROR GridCachePartitionExchangeManager: Runtime error
caught during grid runnable execution: GridWorker [name=partition-exchanger,
gridName=null, finished=false, isCancelled=false, hashCode=628489503,
interrupted=false, runner=exchange-worker-#47%null%]
java.lang.NullPointerException
at
org.apache.ignite.internal.processors.cache.GridCacheProcessor.onExchangeDone(GridCacheProcessor.java:1724)
at
org.apache.ignite.internal.processors.cache.distributed.dht.preloader.GridDhtPartitionsExchangeFuture.onDone(GridDhtPartitionsExchangeFuture.java:1114)
at
org.apache.ignite.internal.processors.cache.distributed.dht.preloader.GridDhtPartitionsExchangeFuture.onDone(GridDhtPartitionsExchangeFuture.java:88)
at
org.apache.ignite.internal.util.future.GridFutureAdapter.onDone(GridFutureAdapter.java:336)
at
org.apache.ignite.internal.processors.cache.distributed.dht.preloader.GridDhtPartitionsExchangeFuture.init(GridDhtPartitionsExchangeFuture.java:878)
at
org.apache.ignite.internal.processors.cache.GridCachePartitionExchangeManager$ExchangeWorker.body(GridCachePartitionExchangeManager.java:1297)
at
org.apache.ignite.internal.util.worker.GridWorker.run(GridWorker.java:110)
at java.lang.Thread.run(Thread.java:745)
[Stage 2:>  (0 + 2)
/ 2]


Code example:

import scala.util.{Try}
import org.apache.ignite.configuration._
import org.apache.ignite.spark.IgniteContext
import org.apache.ignite.configuration.CacheConfiguration
import org.apache.ignite.binary.BinaryObject
import org.apache.ignite.binary.BinaryObjectBuilder
import org.apache.ignite.cache._
import scala.collection.JavaConversions._
import scala.collection.JavaConverters._
import java.util.LinkedHashMap
import scala.collection.mutable.LinkedHashMap


val queryEntity = new QueryEntity()
queryEntity.setKeyType(classOf[Long].getName)
queryEntity.setValueType(classOf[BinaryObject].getName)

val fields = new java.util.LinkedHashMap[String, String]()
fields.put("id", classOf[String].getName)
fields.put("name", classOf[String].getName)

queryEntity.setFields(fields)
queryEntity.setIndexes(List(new QueryIndex("id"), new
QueryIndex("name")).asJava)


val cacheCfg = new CacheConfiguration[Long, BinaryObject]()
cacheCfg.setName("cache01")
cacheCfg.setQueryEntities(List(queryEntity).asJava)

val igniteContext = new IgniteContext[Long, BinaryObject](sc, () => new
IgniteConfiguration(), false)

val cache = igniteContext.fromCache(cacheCfg)
>>cache:
org.apache.ignite.spark.IgniteRDD[Long,org.apache.ignite.binary.BinaryObject]
= IgniteRDD[1] at RDD at IgniteAbstractRDD.scala:27


scala> val rdd = 

Re: index and query org.apache.ignite.spark.IgniteRDD[String,org.apache.spark.sql.Row]

[Alexey Goncharuk]

Yep, BinaryObjectBuilder should definitely be a solution for this. You can
obtain an instance of Ignite from IgniteContext and use the IgniteBinary
interface to get an instance of BinaryObjectBuilder to build object
structures dynamically. And you can use QueryEntity class to describe the
index configuration for the binary objects being stored in cache. Once the
binary object is built, you can save it to Ignite using the same
savePairs() method.

I am surprised we do not have any examples for BinaryObjectBuilder,
however, you can take a look at the CacheJdbcPojoStore source code [1] to
get an idea how the builder works. You can also take a look at the
documentation [2].

Hope this helps!

[1]
https://github.com/apache/ignite/blob/master/modules/core/src/main/java/org/apache/ignite/cache/store/jdbc/CacheJdbcPojoStore.java
[2] https://apacheignite.readme.io/docs/binary-marshaller

Re: index and query org.apache.ignite.spark.IgniteRDD[String,org.apache.spark.sql.Row]

[Andrey Gura]

Dmitry,

case classes are needed only for Sprak sqlContext.createDataFrame method
because type parameter should be scala.Product. Ignite doesn't imply such
limitations to user types.

I have no ideas how to use dynamic structures.

Alexey Goncharuk,

can we use BinaryObject for this case? May be you have some ideas?

On Fri, Mar 4, 2016 at 6:47 AM, DmitryB  wrote:

> Hi Andrey,
>
> Thanks a lots for your help.
> Unfortunately, i can not use case classes, because a schema information is
> only available at runtime;
> to make it more clear let me add more details. suppose that i have a very
> big data set (~500 Tb) which is stored in AWS s3 in a parquet format; Using
> spark, i can process (filter + join) it and reduce size down to ~200 -500
> Gb; resulted dataset i would like to save in ignite cache using IgniteRdd
> and create indexes for a particular set of fields which will be used later
> for running queries (filter, join, aggregations); My assumption is that
> having this result dataset in ignite + indexes would help to improve the
> performance comparing to using spark DataFrame (persisted);
> Unfortunately, the resulted dataset schema can vary with great number of
> variations; So, it seems impossible to describe all of them with case
> classes;
> This is why an approach to store spark.sql.row + describe query fields and
> indexes using QueryEntity would be preferable;
> Thanks to your explanation, i see that this approach doesn't works;
> Another solutions that is spinning in my head is to generate case classes
> dynamically (at runtime) based on spark data frame schema, then map
> sql.rows
> to RDD[generated_case_class], describe ignite query and index fields using
> QueryEntity, create IgniteContext for generated case class; Im not sure
> that
> this approach is even possible, so i would like to ask for your opinion
> before i go deeper;
> Will be very grateful for advice
>
> Best regards,
> Dmitry
>
>
>
>
>
>
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/index-and-query-org-apache-ignite-spark-IgniteRDD-String-org-apache-spark-sql-Row-tp3343p3363.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>



-- 
Andrey Gura
GridGain Systems, Inc.
www.gridgain.com

Re: index and query org.apache.ignite.spark.IgniteRDD[String,org.apache.spark.sql.Row]

[DmitryB]

Hi Andrey,

Thanks a lots for your help.
Unfortunately, i can not use case classes, because a schema information is
only available at runtime; 
to make it more clear let me add more details. suppose that i have a very
big data set (~500 Tb) which is stored in AWS s3 in a parquet format; Using
spark, i can process (filter + join) it and reduce size down to ~200 -500
Gb; resulted dataset i would like to save in ignite cache using IgniteRdd
and create indexes for a particular set of fields which will be used later
for running queries (filter, join, aggregations); My assumption is that
having this result dataset in ignite + indexes would help to improve the
performance comparing to using spark DataFrame (persisted);
Unfortunately, the resulted dataset schema can vary with great number of
variations; So, it seems impossible to describe all of them with case
classes;
This is why an approach to store spark.sql.row + describe query fields and
indexes using QueryEntity would be preferable;
Thanks to your explanation, i see that this approach doesn't works; 
Another solutions that is spinning in my head is to generate case classes
dynamically (at runtime) based on spark data frame schema, then map sql.rows
to RDD[generated_case_class], describe ignite query and index fields using
QueryEntity, create IgniteContext for generated case class; Im not sure that
this approach is even possible, so i would like to ask for your opinion
before i go deeper; 
Will be very grateful for advice

Best regards,
Dmitry









--
View this message in context: 
http://apache-ignite-users.70518.x6.nabble.com/index-and-query-org-apache-ignite-spark-IgniteRDD-String-org-apache-spark-sql-Row-tp3343p3363.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: index and query org.apache.ignite.spark.IgniteRDD[String,org.apache.spark.sql.Row]

[Andrey Gura]

Dmitry,

When you invoke cacheCfg.setIndexedTypes(classOf[String], classOf[Row])
Ignite will create table with name "Row" because internally simple class
name uses as table name. In fact your data frame contains
GenericRowWithSchema objects, thus table name is "GenericRowWithSchema" in
this case. Since "Row" table is empty then result set size of your SQL
query will be 0.

Storing instances of the Row trait in cache isn't good idea from my point
of view for the following reasons:

- Your code depends on actual Row implementation in Spark data frame that
could be changed in the future;
- Row implementation is wrapper for actual data tuples so it requires more
RAM and additional serialization/deserialization overhead;
- It is impossible to index Row instances because it is generic object
without concrete fields that can be indexed by Ignite because only class
fields can be indexed.

Better way is store TRow1 objects in the cache because you have full
control on implementation details. I've reworked your code a little bit in
order to exlpain my idea.


/** Type alias for `QuerySqlField`. */
type ScalarCacheQuerySqlField = QuerySqlField @field

// query fields should be annotated
case class TRow1(@ScalarCacheQuerySqlField s1: String,
 @ScalarCacheQuerySqlField s2: String) extends
Serializable {

}

val sc: SparkContext = new SparkContext("local[*]", "test")

val sqlCtx: SQLContext = new SQLContext(sc)

// create rdd
val data = List[TRow1](TRow1("1", "test-1"), TRow1("2", "test-2"))
val df = sqlCtx.createDataFrame[TRow1](data)

val col: Array[Row] = df.collect()

// create ignite context (embeded mode)
val igniteContext = new IgniteContext[String, TRow1](sc, () => new
IgniteConfiguration().setPeerClassLoadingEnabled(true), false)

// cache config
val cacheCfg = new CacheConfiguration[String, TRow1]()
cacheCfg.setName("cache01")
cacheCfg.setIndexedTypes(classOf[String], classOf[TRow1]) // table has
"TRow1" name

// ignite cache
val cache = igniteContext.fromCache(cacheCfg)

// df rdd save to cache
val df_rdd = df.rdd.map(r => (r.getAs[String](0),
TRow1(r.getAs[String](0), r.getAs[String](1

cache.savePairs(df_rdd)

// query
val c = cache.count

println(s"cache.count $c")

val result = cache.sql("select * from TRow1").collect

result.foreach(println(_))


I hope it will helpful for you.


On Wed, Mar 2, 2016 at 7:30 PM, DmitryB  wrote:

> Hi team,
>
> I try to save, index and query spark DataFrames with Ignite cache
> this this my code:
>
> import org.apache.spark.sql.Row
> import org.apache.ignite.configuration._
> import org.apache.ignite.spark.IgniteContext
> import org.apache.ignite.configuration.CacheConfiguration
> import sandbox._
>
> // create rdd
> val data = List[TRow1](TRow1("1", "test-1"), TRow1("2", "test-2"))
> val df = sqlContext.createDataFrame[TRow1](data)
> >> df: org.apache.spark.sql.DataFrame = [key: string, name: string]
>
> // create ignite context (embeded mode)
> val igniteContext = new IgniteContext[String, Row](sc, () => new
> IgniteConfiguration(), false)
>
> // cache config
> val cacheCfg = new CacheConfiguration[String, Row]()
> cacheCfg.setName("cache01")
> cacheCfg.setIndexedTypes(classOf[String], classOf[Row])
>
> // ignite cache
> val cache = igniteContext.fromCache(cacheCfg)
> >> cache:
> org.apache.ignite.spark.IgniteRDD[String,org.apache.spark.sql.Row]
> >> = IgniteRDD[1] at RDD at IgniteAbstractRDD.scala:27
>
> // df rdd save to cache
> val df_rdd = df.rdd.map(r => (r.getAs[String](0), r))
> >> df_rdd: org.apache.spark.rdd.RDD[(String, org.apache.spark.sql.Row)] =
> >> MapPartitionsRDD[4] at map at :38
> cache.savePairs(df_rdd)
>
> // query
> val c = cache.count
> >> res3: Long = 2
>
> val result = cache.sql("select * from Row").collect
> >> result: Array[org.apache.spark.sql.Row] = Array()
>
> Unfortunately, running SQL query i don't get any result;
>
> Could you plz recommend the correct way for storing, indexing and querying
> sql.Rows with ignite cache
>
> Best regards,
> Dmitry
>
>
>
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/index-and-query-org-apache-ignite-spark-IgniteRDD-String-org-apache-spark-sql-Row-tp3343.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>



-- 
Andrey Gura
GridGain Systems, Inc.
www.gridgain.com