Re: Problem with writeBehind Apache Ignite 2.8.1

[akorensh]

Hi,
  This shouldn't be happening. Please attach  a reproducer and I'll take a
look.
   Describe the steps you take to reproduce scenario. Include version #'s
for all affected components.
Thanks, Alex




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

Re: Tech Talk: Explaining Ignite Through Coding

[Denis Magda]

Yes, it will be recorded and uploaded to YouTube. If you register for the
event, you'll get a follow-up email once the recording is published. Or
ping me later here.

-
Denis


On Thu, Jun 18, 2020 at 2:14 PM aveiros  wrote:

> Denis,
>
> Will this session be recorded and posted on a video content platform (i.e.
> Youtube)?
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>

Re: primary keys

[narges saleh]

Hello Ilya,
Here is the info. In this query only one table is involved.
There are about 4M records in the table and about 40,000 distinct accounts.

thank you.


On Thu, Jun 18, 2020 at 9:50 AM Ilya Kasnacheev 
wrote:

> Hello!
>
> Please show DML for your tables as well as query plans.
>
> Regards,
> --
> Ilya Kasnacheev
>
>
> чт, 18 июн. 2020 г. в 16:11, narges saleh :
>
>> Thanks Ilya.
>> Now I can see the complete plan, and it shows scanning the large tables
>> (but not the others). Increasing index size didn't help.
>> I only have primary keys on the caches and the fields in the primary keys
>> are the  ones used in my where clause, so I am not sure
>> what's going on.
>> Currently, I am testing on one node only, so all the data should be in
>> one place.
>>
>>
>> On Thu, Jun 18, 2020 at 6:17 AM Ilya Kasnacheev <
>> ilya.kasnach...@gmail.com> wrote:
>>
>>> Hello!
>>>
>>> Please use !set outputFormat vertical to see complete execution plan.
>>>
>>> Index is created on primary key. There is no programmatic way to change
>>> its inline size other than specifying IGNITE_MAX_INDEX_PAYLOAD_SIZE
>>> system property or environment variable.
>>>
>>> If it is of complex type, some versions may not be able to search by its
>>> fields.
>>>
>>> Regards,
>>> --
>>> Ilya Kasnacheev
>>>
>>>
>>> чт, 18 июн. 2020 г. в 13:13, narges saleh :
>>>
 Hi All,

 Shouldn't primary keys result in indexes and if so, shouldn't I be able
 to see them when I list indexes?
 Does inline index size applicable to primary keys too?
 Additionally, when I do explain plan on a query which involves tables
 with primary keys, shouldn't I see the primary key/index being used? Or
 lack of a scan statement imply that an index is being used?
 ---+
 |  PLAN  |
 ++
 | SELECT
 ID, NAME,TIMESTAMP
 FROM PUBLIC.table1
 /* PU |

 sql is: select timestamp from table1 where id = 50 and name = 'John';
 //primary key is on id + name

>>>


query
Description: Binary data

Re: Tech Talk: Explaining Ignite Through Coding

[aveiros]

Denis,

Will this session be recorded and posted on a video content platform (i.e.
Youtube)?



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

Tech Talk: Explaining Ignite Through Coding

[Denis Magda]

Igniters,

I'm doing an online tech talk on June 24th

in the format of a coding session. We're going to learn key Ignite
capabilities by building an application that uses SQL, compute,
continuous queries, and other standard APIs.

That's not a dull compile-and-run-example session. The initial application
is left unfinished and we'll be tasked to solve all existing exceptions,
and TODOs scattered across the source code templates. You can even follow
me throughout the talk by finishing the same application on your side

.

Hope to see you and will be glad to get follow-up feedback ;)

-
Denis

RE: 2.7.6 to 2.8.1 migration issue

[Andrey Davydov]

I make some investigation. So I found you update logic in GridCacheProcessor and ClusterCachesInfo. It was some logic of configs priority in old version (see 2.7.6 GridCacheProcessor.java line 901)     if (CU.isPersistenceEnabled(ctx.config()) && ctx.cache().context().pageStore() != null) {    Map storedCaches = ctx.cache().context().pageStore().readCacheConfigurations();     if (!F.isEmpty(storedCaches)) {    for (StoredCacheData storedCacheData : storedCaches.values()) {    String cacheName = storedCacheData.config().getName();     //Ignore stored caches if it already added by static config(static config has higher priority).    if (!caches.containsKey(cacheName))    addStoredCache(caches, storedCacheData, cacheName, cacheType(cacheName), true, false);    else {    CacheConfiguration cfg = caches.get(cacheName).cacheData().config();    CacheConfiguration cfgFromStore = storedCacheData.config(); validateCacheConfigurationOnRestore(cfg, cfgFromStore);    }    }    }    } In new version config check is strict and any modification of cache configuration requires reload data. As I find in my logs, first node starts because it was some crash last month and this node was recreated from scratch. So to start on 2.8.1 I should run 2.7.6 and should node by node restart over empty workdir and wait for rebalance. So I see 2 problems: Migration process described over seem not safe for production, or I should implement some migration logic, i.e. create new cache group, copy data and so on. Then run this login over 2.7.6 and then run 2.8.1, this is looks safe, but more difficult. (Are you have any paper about best practices for this tasks?)New config validation disable ANY configuration update in my example we change topology validator but not atomicity mode. So if I want to change topology validator or some other parameter on 2.8.1 in future, I must repeat difficult migration process. In my opinion, there are cache config parameters which must be same across cluster and Ignite must take it from cache metadata from storage for existing caches, and there are some parameters that affect only local node and may be used from current config. In our case we setup new validator for all caches in group. You may collect mutable properties of cache config in some other configuration. Andrey. От: Ilya KasnacheevОтправлено: 18 июня 2020 г. в 18:59Кому: user@ignite.apache.orgТема: Re: 2.7.6 to 2.8.1 migration issue Hello! I think we restricted configuration of caches that share the same cache group. Previously, you could have e.g. caches with different atomicity mode in the same group, now you can't. If this is the case, you won't be able to upgrade without migration. You need to copy data to a new cache in a new group, then delete the old conflicting cache. Regards,-- Ilya Kasnacheev  чт, 18 июн. 2020 г. в 18:47, Andrey Davydov : Hello, We test migration from 2.7.6 to 2.8.1 in our DEV environment and got problem: new code doesn’t start over old data.  Some history:Initially we had following base cache configuration on 2.7.6:  id="cache-template" abstract="true" class="org.apache.ignite.configuration.CacheConfiguration">     name="atomicityMode" value="TRANSACTIONAL"/>     name="writeSynchronizationMode" value="FULL_SYNC"/>     name="rebalanceMode" value="ASYNC"/>     name="maxConcurrentAsyncOperations" value="500"/>     name="cacheMode" value="PARTITIONED"/>     name="backups" value="2"/>     name="dataRegionName" value="persistDataRegion"/>     name="storeKeepBinary" value="true"/>         name="groupName" value="applicationGroup"/>  name="encryptionEnabled" value="false"/>  name="affinity">     class="org.apache.ignite.cache.affinity.rendezvous.RendezvousAffinityFunction">     name="excludeNeighbors" value="true"/>     name="partitions" value="1024"/>             And create some caches using it.  parent="cache-template" class="org.apache.ignite.configuration.CacheConfiguration">     name="name" value="cache1"/>         parent="cache-template" class="org.apache.ignite.configuration.CacheConfiguration">     name="name" value="cache2"/>         parent="cache-template" class="org.apache.ignite.configuration.CacheConfiguration">     name="indexedTypes">        java.lang.String    some.our.WellAnnotatedClass              Some weeks later we update base cache configuration to use topology validator.   id="base-cache-template"

Re: 2.7.6 to 2.8.1 migration issue

[Ilya Kasnacheev]

Hello!

I think we restricted configuration of caches that share the same cache
group. Previously, you could have e.g. caches with different atomicity mode
in the same group, now you can't.

If this is the case, you won't be able to upgrade without migration. You
need to copy data to a new cache in a new group, then delete the old
conflicting cache.

Regards,
-- 
Ilya Kasnacheev


чт, 18 июн. 2020 г. в 18:47, Andrey Davydov :

>
>
> Hello,
>
>
>
> We test migration from 2.7.6 to 2.8.1 in our DEV environment and got
> problem: new code doesn’t start over old data.
>
>
>
> Some history:
>
> Initially we had following base cache configuration on 2.7.6:
>
>
>
>  "org.apache.ignite.configuration.CacheConfiguration">
>
> 
>
> 
>
> 
>
> 
>
> 
>
> 
>
> 
>
> 
>
> 
>
> 
>
> 
>
>
>
> 
>
>  "org.apache.ignite.cache.affinity.rendezvous.RendezvousAffinityFunction">
>
> 
>
> 
>
> 
>
> 
>
> 
>
>
>
> And create some caches using it.
>
>
>
>  "org.apache.ignite.configuration.CacheConfiguration">
>
> 
>
> 
>
>  "org.apache.ignite.configuration.CacheConfiguration">
>
> 
>
> 
>
>  "org.apache.ignite.configuration.CacheConfiguration">
>
> 
>
> 
>
> java.lang.String
>
> some.our.WellAnnotatedClass
>
> 
>
> 
>
> 
>
>
>
>
>
> Some weeks later we update base cache configuration to use topology
> validator.
>
>
>
>
>
>  "org.apache.ignite.configuration.CacheConfiguration">
>
> 
>
> 
>
> 
>
> 
>
> 
>
> 
>
> 
>
>  class="org.apache.ignite.configuration.CacheConfiguration">
>
> 
>
> 
>
> 
>
> 
>
> 
>
> 
>
> 
>
> 
>
> 
>
> 
>
> 
>
>
>
> 
>
>  "org.apache.ignite.cache.affinity.rendezvous.RendezvousAffinityFunction">
>
> 
>
> 
>
> 
>
> 
>
> 
>
>
>
>
>
> We run new version over old data and everything starts ok.
>
> After that we update our system and create some more caches.
>
>
>
>
>
>  "org.apache.ignite.configuration.CacheConfiguration">
>
> 
>
> 
>
>  "org.apache.ignite.configuration.CacheConfiguration">
>
> 
>
> 
>
>
>
>
>
> And we also start new version of code and configuration over old data
> directory and everything starts ok.
>
> Now we change ignite version from 2.7.6 to 2.8.1 and try start system over
> old data. One node starts ok and two other fails with following error:
>
>
>
> 2020-06-18 15:37:12,598 [main] WARN   o.a.i.i.p.c.GridLocalConfigManager -
> Static configuration for the following caches will be ignored because a
> persistent cache with the same name already exist (see
> https://apacheignite.readme.io/docs/cache-configuration for more
> information): [...]
>
> 2020-06-18 15:37:12,602 [main] INFO   o.a.i.i.p.c.p.f.FilePageStoreManager
> - Cleanup cache stores [total=1, left=0, cleanFiles=false]
>
> 2020-06-18 15:37:12,602 [main] ERROR  o.a.i.i.IgniteKernal%dev_app -
> Exception during start processors, node will be stopped and close
> connections
>
> org.apache.ignite.IgniteCheckedException: Topology validator mismatch for
> caches related to the same group [groupName=applicationGroup,
> existingCache=cache1, existingTopologyValidator=null, startingCache=cache4,
> startingTopologyValidator= our.custom.ValidatorClass]
>
>   at
> org.apache.ignite.internal.processors.cache.GridCacheUtils.validateCacheGroupsAttributesMismatch(GridCacheUtils.java:1032)
> ~[ignite-core-2.8.1.jar:2.8.1]
>
>   at
> org.apache.ignite.internal.processors.cache.ClusterCachesInfo.validateCacheGroupConfiguration(ClusterCachesInfo.java:2305)
> ~[ignite-core-2.8.1.jar:2.8.1]
>
>   at
> org.apache.ignite.internal.processors.cache.ClusterCachesInfo.onStart(ClusterCachesInfo.java:286)
> ~[ignite-core-2.8.1.jar:2.8.1]
>
>
>
> In our development environment only the first node makes write operation
> to cache1 (because we don’t use load balancer in dev and front-end send
> request to the first node) and only this node starts without problem. Two
> other nodes crashes.
>
> We rollback to 2.7.6 and all three nodes start ok.
>
>
>
> If there is way to start 2.8.1 over old data?
>
>
>
>
>
> Andrey.
>
>
>

Re: Ignite persistence and activation

[steve.hostettler]

thanks a lot



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

2.7.6 to 2.8.1 migration issue

[Andrey Davydov]

 Hello, We test migration from 2.7.6 to 2.8.1 in our DEV environment and got problem: new code doesn’t start over old data.  Some history:Initially we had following base cache configuration on 2.7.6:  id="cache-template" abstract="true" class="org.apache.ignite.configuration.CacheConfiguration">     name="atomicityMode" value="TRANSACTIONAL"/>     name="writeSynchronizationMode" value="FULL_SYNC"/>     name="rebalanceMode" value="ASYNC"/>     name="maxConcurrentAsyncOperations" value="500"/>     name="cacheMode" value="PARTITIONED"/>     name="backups" value="2"/>     name="dataRegionName" value="persistDataRegion"/>     name="storeKeepBinary" value="true"/>         name="groupName" value="applicationGroup"/>  name="encryptionEnabled" value="false"/>  name="affinity">     class="org.apache.ignite.cache.affinity.rendezvous.RendezvousAffinityFunction">     name="excludeNeighbors" value="true"/>     name="partitions" value="1024"/>             And create some caches using it.  parent="cache-template" class="org.apache.ignite.configuration.CacheConfiguration">     name="name" value="cache1"/>         parent="cache-template" class="org.apache.ignite.configuration.CacheConfiguration">     name="name" value="cache2"/>         parent="cache-template" class="org.apache.ignite.configuration.CacheConfiguration">     name="indexedTypes">        java.lang.String    some.our.WellAnnotatedClass              Some weeks later we update base cache configuration to use topology validator.   id="base-cache-template" abstract="true" class="org.apache.ignite.configuration.CacheConfiguration">     name="topologyValidator" >     class="our.custom.ValidatorClass">     name="minimalValidTopologyNodes" value="2"/>             name="sqlIndexMaxInlineSize" value="256"/>         id="cache-template" abstract="true" parent="base-cache-template" class="org.apache.ignite.configuration.CacheConfiguration">     name="atomicityMode" value="TRANSACTIONAL"/>     name="writeSynchronizationMode" value="FULL_SYNC"/>     name="rebalanceMode" value="ASYNC"/>     name="maxConcurrentAsyncOperations" value="500"/>     name="cacheMode" value="PARTITIONED"/>     name="backups" value="2"/>     name="dataRegionName" value="persistDataRegion"/>     name="storeKeepBinary" value="true"/>         name="groupName" value="applicationGroup"/>  name="encryptionEnabled" value="false"/>  name="affinity">     class="org.apache.ignite.cache.affinity.rendezvous.RendezvousAffinityFunction">     name="excludeNeighbors" value="true"/>     name="partitions" value="1024"/>              We run new version over old data and everything starts ok. After that we update our system and create some more caches.   parent="cache-template" class="org.apache.ignite.configuration.CacheConfiguration">     name="name" value="cache4"/>         parent="cache-template" class="org.apache.ignite.configuration.CacheConfiguration">     name="name" value="cache5"/>      And we also start new version of code and configuration over old data directory and everything starts ok.Now we change ignite version from 2.7.6 to 2.8.1 and try start system over old data. One node starts ok and two other fails with following error: 2020-06-18 15:37:12,598 [main] WARN   o.a.i.i.p.c.GridLocalConfigManager - Static configuration for the following caches will be ignored because a persistent cache with the same name already exist (see https://apacheignite.readme.io/docs/cache-configuration for more information): [...]2020-06-18 15:37:12,602 [main] INFO   o.a.i.i.p.c.p.f.FilePageStoreManager - Cleanup cache stores [total=1, left=0, cleanFiles=false]2020-06-18 15:37:12,602 [main] ERROR  o.a.i.i.IgniteKernal%dev_app - Exception during start processors, node will be stopped and close connectionsorg.apache.ignite.IgniteCheckedException: Topology validator mismatch for caches related to the same group [groupName=applicationGroup, existingCache=cache1, existingTopologyValidator=null, startingCache=cache4, startingTopologyValidator= our.custom.ValidatorClass]  at org.apache.ignite.internal.processors.cache.GridCacheUtils.validateCacheGroupsAttributesMismatch(GridCacheUtils.java:1032) ~[ignite-core-2.8.1.jar:2.8.1]  at org.apache.ignite.internal.processors.cache.ClusterCachesInfo.validateCacheGroupConfiguration(ClusterCachesInfo.java:2305) ~[ignite-core-2.8.1.jar:2.8.1]  at org.apache.ignite.internal.processors.cache.ClusterCache

Re: Multi-threaded JDBC driver issue/concern

[Stephen Darlington]

Make sure you do EXPLAIN PLAN to see if it’s using an index. I don’t think it 
is. If you put the composite index order the other way around it I suspect it 
would use the index.

> On 18 Jun 2020, at 16:01, R S S Aditya Harish  
> wrote:
> 
> Okay. What I meant is we don't want to give QueryEntities in XML way. We want 
> to give it via Client side Java application way. I understood your point.
> 
> I want to give further explanation
> 
> Following is the value class. We wanted a composite index on score and 
> appname_id so we created in this way.
> 
> class Value{
> 
>   
> 
> @QuerySqlField
> private String url;
> 
> @QuerySqlField (orderedGroups={@QuerySqlField.Group(
> name = "score_app_idx", order = 0)})//No I18N
> private double score;
> 
> @QuerySqlField (index = true, orderedGroups={@QuerySqlField.Group(
> name = "score_app_idx", order = 1)})//No I18N
> private long appname_id;
> 
>   -
> 
> }
> 
> I am inserting as you mentioned in previous mail for "AppDetails" class using 
> datastreamer.
> 
> How I am querying is by using SqlFieldsQuery as following:
> 
> -> new SqlFieldsQuery("SELECT URL FROM URLS WHERE APPNAME_ID = ? ORDER BY 
> SCORE LIMIT ?");
> 
> We have checked by doing this way and found that it is giving an average of 
> 1.5-2 msec for a run of 20 minutes on 0.7 to 1 million record data.
> 
> Now we changed the value class in this format
> 
> class Value{
> 
>   
> 
> @QuerySqlField
> private String url;
> 
> @QuerySqlField
> private double score;
> 
> @QuerySqlField
> private long appname_id;
> 
>   -
> 
> }
> 
> We have checked by doing this way and even now for the same record count we 
> are getting read times of about 2msec.
> 
> Am I missing something? Please shed some light on the way we need to create 
> index for the above read query.
> 
> Thank you,
> R S S Aditya Harish
> +91-8106278997 | Ext No. 4433
> ​<1571817034225000_1337245575.jpeg>
> 
> 
> 
>  On Thu, 18 Jun 2020 20:14:44 +0530 Stephen Darlington 
>  wrote 
> 
> To be clear: query entities is how the SQL engine work (missing out some 
> details but that’s broadly correct as I understand it!). Using CREATE TABLE 
> and IgniteCache#setIndexTypes() with the annotations are both shortcuts that 
> generate the query entities for you. You can verify this for yourself by 
> creating your cache and then viewing the configuration of the created table 
> with IgniteCache#getConfiguration().
> 
> I can’t explain your results. It depends on the number of rows in your table, 
> the number of nodes, what query you were using...
> 
> On 18 Jun 2020, at 14:46, R S S Aditya Harish  > wrote:
> 
> Thanks for the reply.
> 
> I understand. I have one more doubt. I don't want to go with QueryEntities 
> way. I want to go via Annotation based way using java classes. In that case, 
> I want to create index on score and appnameid columns as a composite index. 
> But what I found is, without creating index at all for fields in that class, 
> I am getting good results for sql order by query. How is that possible? I am 
> using whatever way you suggested for inserting rows.. And for querying i'm 
> just using normal SqlFieldsQuery api. I am getting good result without index 
> on fields.
> 
> Thank you,
> R S S Aditya Harish
> +91-8106278997 | Ext No. 4433
> ​<1571817034225000_1337245575.jpeg>
> 
> 
> 
>  On Thu, 18 Jun 2020 16:42:54 +0530 Stephen Darlington 
> mailto:stephen.darling...@gridgain.com>> 
> wrote 
> 
> You don’t need the CREATE statement as well. If you want to do it instead of 
> the Query Entities you can do:
> 
> CREATE TABLE IF NOT EXISTS URLS
> (ID LONG PRIMARY KEY, URL VARCHAR, SCORE DOUBLE, APPNAME_ID LONG)
> WITH ‘key_type=AppDetailsKey,value_type=AppDetails’
> 
> You still need the Java classes but you can skip the annotations.
> 
> https://apacheignite-sql.readme.io/docs/create-table 
> 
> 
> On 18 Jun 2020, at 11:57, R S S Aditya Harish  > wrote:
> 
> Hi Stephen,
> 
> Thank you so much for giving the snippet. I followed and it worked. But I 
> have one more doubt. In the below section the query is insertion query and it 
> works fine. We are using the JDBC for Create statement also. We are having 
> trouble understanding how to execute create a statement using the above 
> QueryEntity way. Can you please shed some light on it? An example snippet for 
> the statement "CREATE TABLE IF NOT EXISTS URLS ID LONG PRIMARY KEY, URL 
> VARCHAR, SCORE DOUBLE, APPNAME_ID LONG)" would be grateful.
> 
> Thank you,
> R S S Aditya Harish
> +91-8106278997 | Ext No. 4433
> ​<1571817034225000_1337245575.jpeg>
> 
> 
> 
>  On Tue, 16 Jun 2020 19:24:44 +0530 Stephen Darlington 
> mailto:stephen.darling...@gridgain.com>> 
> wrote 
> 
> There’s not one, right way of doing it. In Java it’s someth

Re: Multi-threaded JDBC driver issue/concern

[R S S Aditya Harish]

Okay. What I meant is we don't want to give QueryEntities in XML way. We want 
to give it via Client side Java application way. I understood your point.



I want to give further explanation



Following is the value class. We wanted a composite index on score and 
appname_id so we created in this way.


class Value{



      



    @QuerySqlField

    private String url;



    @QuerySqlField (orderedGroups={@QuerySqlField.Group(

    name = "score_app_idx", order = 0)})//No I18N

    private double score;



    @QuerySqlField (index = true, orderedGroups={@QuerySqlField.Group(

    name = "score_app_idx", order = 1)})//No I18N

    private long appname_id;



      -



}



I am inserting as you mentioned in previous mail for "AppDetails" class using 
datastreamer.



How I am querying is by using SqlFieldsQuery as following:



-> new SqlFieldsQuery("SELECT URL FROM URLS WHERE APPNAME_ID = ? ORDER BY SCORE 
LIMIT ?");



We have checked by doing this way and found that it is giving an average of 
1.5-2 msec for a run of 20 minutes on 0.7 to 1 million record data.



Now we changed the value class in this format



class Value{



      



    @QuerySqlField

    private String url;



    @QuerySqlField

    private double score;



    @QuerySqlField

    private long appname_id;



      -



}



We have checked by doing this way and even now for the same record count we are 
getting read times of about 2msec.



Am I missing something? Please shed some light on the way we need to create 
index for the above read query.


Thank you,

R S S Aditya Harish


+91-8106278997 | Ext No. 4433

​








 On Thu, 18 Jun 2020 20:14:44 +0530 Stephen Darlington 
 wrote 



To be clear: query entities is how the SQL engine work (missing out some 
details but that’s broadly correct as I understand it!). Using CREATE TABLE and 
IgniteCache#setIndexTypes() with the annotations are both shortcuts that 
generate the query entities for you. You can verify this for yourself by 
creating your cache and then viewing the configuration of the created table 
with IgniteCache#getConfiguration().



I can’t explain your results. It depends on the number of rows in your table, 
the number of nodes, what query you were using...



On 18 Jun 2020, at 14:46, R S S Aditya Harish 
 wrote:



Thanks for the reply.



I understand. I have one more doubt. I don't want to go with QueryEntities way. 
I want to go via Annotation based way using java classes. In that case, I want 
to create index on score and appnameid columns as a composite index. But what I 
found is, without creating index at all for fields in that class, I am getting 
good results for sql order by query. How is that possible? I am using whatever 
way you suggested for inserting rows.. And for querying i'm just using normal 
SqlFieldsQuery api. I am getting good result without index on fields.



Thank you,

R S S Aditya Harish


+91-8106278997 | Ext No. 4433

​<1571817034225000_1337245575.jpeg>








 On Thu, 18 Jun 2020 16:42:54 +0530 Stephen Darlington 
 wrote 



You don’t need the CREATE statement as well. If you want to do it instead of 
the Query Entities you can do:



CREATE TABLE IF NOT EXISTS URLS

(ID LONG PRIMARY KEY, URL VARCHAR, SCORE DOUBLE, APPNAME_ID LONG)

WITH ‘key_type=AppDetailsKey,value_type=AppDetails’



You still need the Java classes but you can skip the annotations.



https://apacheignite-sql.readme.io/docs/create-table



On 18 Jun 2020, at 11:57, R S S Aditya Harish 
 wrote:



Hi Stephen,



Thank you so much for giving the snippet. I followed and it worked. But I have 
one more doubt. In the below section the query is insertion query and it works 
fine. We are using the JDBC for Create statement also. We are having trouble 
understanding how to execute create a statement using the above QueryEntity 
way. Can you please shed some light on it? An example snippet for the statement 
"CREATE TABLE IF NOT EXISTS URLS ID LONG PRIMARY KEY, URL VARCHAR, SCORE 
DOUBLE, APPNAME_ID LONG)" would be grateful.



Thank you,

R S S Aditya Harish


+91-8106278997 | Ext No. 4433

​<1571817034225000_1337245575.jpeg>








 On Tue, 16 Jun 2020 19:24:44 +0530 Stephen Darlington 
 wrote 



There’s not one, right way of doing it. In Java it’s something like this.



Define your classes:



public class AppDetailsKey {
    @QuerySqlField
    private Long id;

    public AppDetailsKey(Long id) {
        http://this.id/ = id;
    }
}


public class AppDetails {

    @QuerySqlField

    private String url;

    @QuerySqlField

    private Double score;

    @QuerySqlField

    private Long app_name;



    public AppDetails(String url, Double score, Long app_name) {

        this.url = url;

        this.score = score;

        this.app_name = app_name;

Re: primary keys

[Ilya Kasnacheev]

Hello!

Please show DML for your tables as well as query plans.

Regards,
-- 
Ilya Kasnacheev


чт, 18 июн. 2020 г. в 16:11, narges saleh :

> Thanks Ilya.
> Now I can see the complete plan, and it shows scanning the large tables
> (but not the others). Increasing index size didn't help.
> I only have primary keys on the caches and the fields in the primary keys
> are the  ones used in my where clause, so I am not sure
> what's going on.
> Currently, I am testing on one node only, so all the data should be in one
> place.
>
>
> On Thu, Jun 18, 2020 at 6:17 AM Ilya Kasnacheev 
> wrote:
>
>> Hello!
>>
>> Please use !set outputFormat vertical to see complete execution plan.
>>
>> Index is created on primary key. There is no programmatic way to change
>> its inline size other than specifying IGNITE_MAX_INDEX_PAYLOAD_SIZE
>> system property or environment variable.
>>
>> If it is of complex type, some versions may not be able to search by its
>> fields.
>>
>> Regards,
>> --
>> Ilya Kasnacheev
>>
>>
>> чт, 18 июн. 2020 г. в 13:13, narges saleh :
>>
>>> Hi All,
>>>
>>> Shouldn't primary keys result in indexes and if so, shouldn't I be able
>>> to see them when I list indexes?
>>> Does inline index size applicable to primary keys too?
>>> Additionally, when I do explain plan on a query which involves tables
>>> with primary keys, shouldn't I see the primary key/index being used? Or
>>> lack of a scan statement imply that an index is being used?
>>> ---+
>>> |  PLAN  |
>>> ++
>>> | SELECT
>>> ID, NAME,TIMESTAMP
>>> FROM PUBLIC.table1
>>> /* PU |
>>>
>>> sql is: select timestamp from table1 where id = 50 and name = 'John';
>>> //primary key is on id + name
>>>
>>

Re: Multi-threaded JDBC driver issue/concern

[Stephen Darlington]

To be clear: query entities is how the SQL engine work (missing out some 
details but that’s broadly correct as I understand it!). Using CREATE TABLE and 
IgniteCache#setIndexTypes() with the annotations are both shortcuts that 
generate the query entities for you. You can verify this for yourself by 
creating your cache and then viewing the configuration of the created table 
with IgniteCache#getConfiguration().

I can’t explain your results. It depends on the number of rows in your table, 
the number of nodes, what query you were using...

> On 18 Jun 2020, at 14:46, R S S Aditya Harish  
> wrote:
> 
> Thanks for the reply.
> 
> I understand. I have one more doubt. I don't want to go with QueryEntities 
> way. I want to go via Annotation based way using java classes. In that case, 
> I want to create index on score and appnameid columns as a composite index. 
> But what I found is, without creating index at all for fields in that class, 
> I am getting good results for sql order by query. How is that possible? I am 
> using whatever way you suggested for inserting rows.. And for querying i'm 
> just using normal SqlFieldsQuery api. I am getting good result without index 
> on fields.
> 
> Thank you,
> R S S Aditya Harish
> +91-8106278997 | Ext No. 4433
> ​<1571817034225000_1337245575.jpeg>
> 
> 
> 
>  On Thu, 18 Jun 2020 16:42:54 +0530 Stephen Darlington 
>  wrote 
> 
> You don’t need the CREATE statement as well. If you want to do it instead of 
> the Query Entities you can do:
> 
> CREATE TABLE IF NOT EXISTS URLS
> (ID LONG PRIMARY KEY, URL VARCHAR, SCORE DOUBLE, APPNAME_ID LONG)
> WITH ‘key_type=AppDetailsKey,value_type=AppDetails’
> 
> You still need the Java classes but you can skip the annotations.
> 
> https://apacheignite-sql.readme.io/docs/create-table 
> 
> 
> On 18 Jun 2020, at 11:57, R S S Aditya Harish  > wrote:
> 
> Hi Stephen,
> 
> Thank you so much for giving the snippet. I followed and it worked. But I 
> have one more doubt. In the below section the query is insertion query and it 
> works fine. We are using the JDBC for Create statement also. We are having 
> trouble understanding how to execute create a statement using the above 
> QueryEntity way. Can you please shed some light on it? An example snippet for 
> the statement "CREATE TABLE IF NOT EXISTS URLS ID LONG PRIMARY KEY, URL 
> VARCHAR, SCORE DOUBLE, APPNAME_ID LONG)" would be grateful.
> 
> Thank you,
> R S S Aditya Harish
> +91-8106278997 | Ext No. 4433
> ​<1571817034225000_1337245575.jpeg>
> 
> 
> 
>  On Tue, 16 Jun 2020 19:24:44 +0530 Stephen Darlington 
> mailto:stephen.darling...@gridgain.com>> 
> wrote 
> 
> There’s not one, right way of doing it. In Java it’s something like this.
> 
> Define your classes:
> 
> public class AppDetailsKey {
> @QuerySqlField
> private Long id;
> 
> public AppDetailsKey(Long id) {
> this.id  = id;
> }
> }
> 
> public class AppDetails {
> @QuerySqlField
> private String url;
> @QuerySqlField
> private Double score;
> @QuerySqlField
> private Long app_name;
> 
> public AppDetails(String url, Double score, Long app_name) {
> this.url = url;
> this.score = score;
> this.app_name = app_name;
> }
> }
> 
> (I didn’t define your secondary index but you can do that with the 
> annotations, too.)
> 
> Create your cache:
> 
> CacheConfiguration cacheConfiguration = new 
> CacheConfiguration<>();
> cacheConfiguration.setSqlSchema("PUBLIC")
> .setName("APPDETAILS")
> .setIndexedTypes(AppDetailsKey.class, AppDetails.class);
> 
> IgniteCache cache = 
> ignite.getOrCreateCache(cacheConfiguration);
> 
> The annotations and the IndexedTypes tell Ignite to make it available to the 
> SQL engine.
> 
> And then insert stuff into it:
> 
> IgniteDataStreamer ds = 
> ignite.dataStreamer("APPDETAILS");
> ds.addData(new AppDetailsKey(1L), new AppDetails("localhost", 1.0, 10L));
> ds.addData(new AppDetailsKey(2L), new AppDetails("localhost", 1.0, 10L));
> ds.addData(new AppDetailsKey(3L), new AppDetails("localhost", 1.0, 10L));
> ds.flush();
> 
> On 16 Jun 2020, at 06:35, R S S Aditya Harish  > wrote:
> 
> This is my SQL table schema
> 
> ID (Long), URL (Varchar), SCORE (Double), APPNAME_ID (Long)
> 
> We have a composite index on Score, Appname_Id.
> 
> Based on your answer I've two questions.
> 
> 1. How can I insert SQL rows using JCache data streamer API (if possible, 
> with example)? Currently, I'm using jdbc thin with STREAMING ON. But the 
> issue is mentioned above.
> 2. Each row data is -> ID (Long), URL (Varchar), SCORE (Double), APPNAME_ID 
> (Long). How this data is stored as Key-Value? I mean what will be the key and 
> what will be the value?
> 
> Can you please answer these two questions?
> 
> 
>  On Mon, 15 Jun 2020 21:44:38 +0530 Stephen Darlingto

Re: Multi-threaded JDBC driver issue/concern

[R S S Aditya Harish]

Thanks for the reply.



I understand. I have one more doubt. I don't want to go with QueryEntities way. 
I want to go via Annotation based way using java classes. In that case, I want 
to create index on score and appnameid columns as a composite index. But what I 
found is, without creating index at all for fields in that class, I am getting 
good results for sql order by query. How is that possible? I am using whatever 
way you suggested for inserting rows.. And for querying i'm just using normal 
SqlFieldsQuery api. I am getting good result without index on fields.

Thank you,

R S S Aditya Harish


+91-8106278997 | Ext No. 4433

​






 On Thu, 18 Jun 2020 16:42:54 +0530 Stephen Darlington 
 wrote 


You don’t need the CREATE statement as well. If you want to do it instead of 
the Query Entities you can do:

CREATE TABLE IF NOT EXISTS URLS

(ID LONG PRIMARY KEY, URL VARCHAR, SCORE DOUBLE, APPNAME_ID LONG)

WITH ‘key_type=AppDetailsKey,value_type=AppDetails’



You still need the Java classes but you can skip the annotations.



https://apacheignite-sql.readme.io/docs/create-table

On 18 Jun 2020, at 11:57, R S S Aditya Harish 
 wrote:


Hi Stephen,



Thank you so much for giving the snippet. I followed and it worked. But I have 
one more doubt. In the below section the query is insertion query and it works 
fine. We are using the JDBC for Create statement also. We are having trouble 
understanding how to execute create a statement using the above QueryEntity 
way. Can you please shed some light on it? An example snippet for the statement 
"CREATE TABLE IF NOT EXISTS URLS ID LONG PRIMARY KEY, URL VARCHAR, SCORE 
DOUBLE, APPNAME_ID LONG)" would be grateful.


Thank you,

R S S Aditya Harish


+91-8106278997 | Ext No. 4433

​<1571817034225000_1337245575.jpeg>






 On Tue, 16 Jun 2020 19:24:44 +0530 Stephen Darlington 
 wrote 


There’s not one, right way of doing it. In Java it’s something like this.

Define your classes:



public class AppDetailsKey {
    @QuerySqlField
    private Long id;

    public AppDetailsKey(Long id) {
        http://this.id/ = id;
    }
}


public class AppDetails {
    @QuerySqlField
    private String url;
    @QuerySqlField
    private Double score;
    @QuerySqlField
    private Long app_name;

    public AppDetails(String url, Double score, Long app_name) {
        this.url = url;
        this.score = score;
        this.app_name = app_name;
    }
}


(I didn’t define your secondary index but you can do that with the annotations, 
too.)



Create your cache:



CacheConfiguration cacheConfiguration = new 
CacheConfiguration<>();
cacheConfiguration.setSqlSchema("PUBLIC")
        .setName("APPDETAILS")
        .setIndexedTypes(AppDetailsKey.class, AppDetails.class);

IgniteCache cache = 
ignite.getOrCreateCache(cacheConfiguration);


The annotations and the IndexedTypes tell Ignite to make it available to the 
SQL engine.



And then insert stuff into it:

IgniteDataStreamer ds = 
ignite.dataStreamer("APPDETAILS");
ds.addData(new AppDetailsKey(1L), new AppDetails("localhost", 1.0, 10L));
ds.addData(new AppDetailsKey(2L), new AppDetails("localhost", 1.0, 10L));
ds.addData(new AppDetailsKey(3L), new AppDetails("localhost", 1.0, 10L));
ds.flush();


On 16 Jun 2020, at 06:35, R S S Aditya Harish 
 wrote:


This is my SQL table schema



ID (Long), URL (Varchar), SCORE (Double), APPNAME_ID (Long)



We have a composite index on Score, Appname_Id.



Based on your answer I've two questions.



1. How can I insert SQL rows using JCache data streamer API (if possible, with 
example)? Currently, I'm using jdbc thin with STREAMING ON. But the issue is 
mentioned above.

2. Each row data is -> ID (Long), URL (Varchar), SCORE (Double), APPNAME_ID 
(Long). How this data is stored as Key-Value? I mean what will be the key and 
what will be the value?



Can you please answer these two questions?



 On Mon, 15 Jun 2020 21:44:38 +0530 Stephen Darlington 
 wrote 


Do you need the sorting as part of the loading process? If not, the best route 
would be to use the data streamer to load the data. You can still use the SQL 
engine and access your sorted data afterwards — remember that SQL and key-value 
are two different ways of accessing the same underlying data. 
 
> On 15 Jun 2020, at 15:46, adipro  wrote: 
> 
> We have an SQL table which we need because for normal JCache K-V we cannot 
> sort on some column's data. We need that sort feature. That's why we chose 
> SQL table representation. 
> 
> Our application is heavily multi-threaded. 
> 
> Now when trying to insert rows in that table, each thread simultaneously 
> sends 5000-1 rows in bulk. Now if we use, SqlFieldsQuery, it's taking so 
> much of time as we cannot do it in bulk and have to do it in loop one by 
> one. 
> 

Re: primary keys

[narges saleh]

Thanks Ilya.
Now I can see the complete plan, and it shows scanning the large tables
(but not the others). Increasing index size didn't help.
I only have primary keys on the caches and the fields in the primary keys
are the  ones used in my where clause, so I am not sure
what's going on.
Currently, I am testing on one node only, so all the data should be in one
place.


On Thu, Jun 18, 2020 at 6:17 AM Ilya Kasnacheev 
wrote:

> Hello!
>
> Please use !set outputFormat vertical to see complete execution plan.
>
> Index is created on primary key. There is no programmatic way to change
> its inline size other than specifying IGNITE_MAX_INDEX_PAYLOAD_SIZE
> system property or environment variable.
>
> If it is of complex type, some versions may not be able to search by its
> fields.
>
> Regards,
> --
> Ilya Kasnacheev
>
>
> чт, 18 июн. 2020 г. в 13:13, narges saleh :
>
>> Hi All,
>>
>> Shouldn't primary keys result in indexes and if so, shouldn't I be able
>> to see them when I list indexes?
>> Does inline index size applicable to primary keys too?
>> Additionally, when I do explain plan on a query which involves tables
>> with primary keys, shouldn't I see the primary key/index being used? Or
>> lack of a scan statement imply that an index is being used?
>> ---+
>> |  PLAN  |
>> ++
>> | SELECT
>> ID, NAME,TIMESTAMP
>> FROM PUBLIC.table1
>> /* PU |
>>
>> sql is: select timestamp from table1 where id = 50 and name = 'John';
>> //primary key is on id + name
>>
>

Re: sqlline

[Denis Magda]

Yes, that’s right.

On Thursday, June 18, 2020, narges saleh  wrote:

> Hi Denis
> But if all the involved tables/caches have the same affinity key,
> shouldn't they be collocated on the same node, if the data fits on that
> node?
> thanks.
>
> On Wed, Jun 17, 2020 at 10:53 PM Denis Magda  wrote:
>
>> You can always send a compute task via one of IgniteCompute.affinityX()
>> methods and run a *local* SQL query on that node:
>>
>>- affinity calls: https://apacheignite.readme.io/docs/collocate-
>>compute-and-data#affinity-call-and-run-methods
>>
>> 
>>- local SQL queries: https://apacheignite-sql.readme.io/docs/local-
>>queries
>>
>> My selects are complex but they do use the primary keys for the
>>> partitioned tables/caches participating in the query. But they perform
>>> poorly if the data is distributed across multiple nodes.
>>
>>
>> If the data is co-located and scattered across cluster nodes then it's
>> expected that Ignite will execute a query across several nodes. It's a
>> standard approach of distributed databases that support SQL engines. My
>> suggestion would be to investigate the root cause of low performance. You
>> just might need to configure some indexes: https://apacheignite-sql.
>> readme.io/docs/performance-and-debugging
>>
>> -
>> Denis
>>
>>
>> On Wed, Jun 17, 2020 at 7:33 PM narges saleh 
>> wrote:
>>
>>> My selects are complex but they do use the primary keys for the
>>> partitioned tables/caches participating in the query. But they perform
>>> poorly if the data is distributed across multiple nodes.
>>>
>>> On Wed, Jun 17, 2020 at 8:04 PM narges saleh 
>>> wrote:
>>>
 Thanks Denis. In case of selects, can I push ignite to broadcast the
 sql to where the data is, say by using employing services deployed on the
 targeted data nodes, or utilizing affinity calls (with the sql as part of
 the logic)?

 On Wed, Jun 17, 2020 at 7:02 PM Denis Magda  wrote:

> Narges,
>
> INSERTS/UPDATES/DELETES will be forwarded to primary nodes based on
> the value of primary keys you pass into queries. Ignite maps a primary key
> to a partition and, finally, to a primary node that holds that partition.
> All this happens transparently in Ignite. You don't need to do anything
> except for sending those SQL commands.
>
> As for SELECTS, most of them will be broadcasted to your server nodes
> unless Ignite can pre-select a sub-set of target nodes a query needs to be
> executed on. This optimization always takes place for simple queries when
> you try to get records based on the value of a primary key, such as 
> "SELECT
> * FROM Table WHERE primaryKeyColumn = 2".
>
> -
> Denis
>
>
> On Wed, Jun 17, 2020 at 12:43 PM narges saleh 
> wrote:
>
>> Thanks Denis. Will watch the video.
>> One more question, related to SQL execution. If I issue a SQL from a
>> client, how would ignite know which node to run the SQL query on? I 
>> assume
>> the SQL is parsed on the server nodes, and I will still need to direct 
>> the
>> SQL to be executed on the right server node (assuming that all the data 
>> is
>> collocated on a single node). Am I right?
>>
>> On Wed, Jun 17, 2020 at 2:26 PM Denis Magda 
>> wrote:
>>
>>> The collocated flag is an extra optimization hint for SQL queries
>>> with GROUP BYs. The parameter requests to group records locally on the
>>> nodes that participated in the query execution. For instance, if your
>>> application sends a query with GROUP BY to a couple of server nodes, 
>>> then
>>> the nodes will group their portions of the result sets locally not 
>>> making
>>> that the burden of the application that merges/reduces the result sets 
>>> into
>>> one. The description of this parameter here should be helpful as well:
>>> https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters
>>>
>>> 

 Should I assume that if the table is defined with affinity set,
 then compute collocation, including sql execution will happen 
 automatically?
>>>
>>>
>>> That's right. You just need to set up the affinity keys and Ignite
>>> will take care of the rest. Btw, you might be interested in this SQL
>>> webinar we delivered recently. The webinar explains our SQL 
>>> capabilities in
>>> detail: https://www.youtube.com/watch?v=eYV-tNLzIts
>>>
>>> -
>>> Denis
>>>
>>>
>>> On Wed, Jun 17, 2020 at 11:28 AM narges saleh 
>>> wrote:
>>>
 Then can you please explain what the option collocated=true for
 JDBC url is for?
 Should I assume that if the table is defined with affinity set,
 then compute colloca

Re: Achieve uniform data distribution easily

[Ilya Kasnacheev]

Hello!

Data distribution will only be uniform if affinity key distribution is
uniform, and may still go +/- 10% between nodes.

Regards,
-- 
Ilya Kasnacheev


чт, 18 июн. 2020 г. в 09:41, VincentCE :

> Thank you for your answer. We are aware of that our approach is not the
> clean
> one as the one with explicitly defined affinity keys would be. But still
> for
> the sake of data distribution defining affinityKeys, in this example this
> would be cacheName/key for a given key, should be equivalent to overriding
> the partition method in the described way. Hence we would still get a
> data-distribution far from being uniform. To me this is a bit surprising as
> this would imply that even in the case of distinct affinity-keys one does
> not achieve a balanced data-distribution with RendezvousAffinityFunction.
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>

Re:

[Ilya Kasnacheev]

Hello!

I do not recommend relying on this order. It may change if you have more
than one node. It can also change without warning.

Regards,
-- 
Ilya Kasnacheev


пт, 12 июн. 2020 г. в 21:44, Prasad Bhalerao :

> I am executing following SQL in ignite. As you can see there is no order
> by clause in this SQL. Index is present on field name "id".
>
> SqlFieldsQuery sql = new SqlFieldsQuery("select p.id. p.name from Person
> p join table(id bigint = ?) i on p.id = i.id.") .setArgs(new
> Object[]{5,4,1,7,3});
>
>
> What I have observed is ignite always returns query result in an order as
> it is provided in input.
> E.g. I always get resultant rows in following order. This behaviour is
> consistent.
> p.id.p.name
> 5test5
> 4   Test4
> 1Test1
> 7Test7
> 3 Test3
>
> If I change input "id" sequence, the output changes as per the id sequence
> provided.
>
> Can someone please confirm this behaviour?
>
> Also, will it remain same with calcite integration?
>
>
>

Re: ignite web agent issue

[Ilya Kasnacheev]

Hello!

I have 3.6.3 installed.

Regards,
-- 
Ilya Kasnacheev


пт, 12 июн. 2020 г. в 09:40, itsmeravikiran.c :

> Could you please let me know, what is the supported mongodb version.
> Please let me know the mongodb version, which your using.
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>

Re: primary keys

[Ilya Kasnacheev]

Hello!

Please use !set outputFormat vertical to see complete execution plan.

Index is created on primary key. There is no programmatic way to change its
inline size other than specifying IGNITE_MAX_INDEX_PAYLOAD_SIZE
system property or environment variable.

If it is of complex type, some versions may not be able to search by its
fields.

Regards,
-- 
Ilya Kasnacheev


чт, 18 июн. 2020 г. в 13:13, narges saleh :

> Hi All,
>
> Shouldn't primary keys result in indexes and if so, shouldn't I be able to
> see them when I list indexes?
> Does inline index size applicable to primary keys too?
> Additionally, when I do explain plan on a query which involves tables with
> primary keys, shouldn't I see the primary key/index being used? Or lack of
> a scan statement imply that an index is being used?
> ---+
> |  PLAN  |
> ++
> | SELECT
> ID, NAME,TIMESTAMP
> FROM PUBLIC.table1
> /* PU |
>
> sql is: select timestamp from table1 where id = 50 and name = 'John';
> //primary key is on id + name
>

Re: Multi-threaded JDBC driver issue/concern

[Stephen Darlington]

You don’t need the CREATE statement as well. If you want to do it instead of 
the Query Entities you can do:

CREATE TABLE IF NOT EXISTS URLS
(ID LONG PRIMARY KEY, URL VARCHAR, SCORE DOUBLE, APPNAME_ID LONG)
WITH ‘key_type=AppDetailsKey,value_type=AppDetails’

You still need the Java classes but you can skip the annotations.

https://apacheignite-sql.readme.io/docs/create-table

> On 18 Jun 2020, at 11:57, R S S Aditya Harish  
> wrote:
> 
> Hi Stephen,
> 
> Thank you so much for giving the snippet. I followed and it worked. But I 
> have one more doubt. In the below section the query is insertion query and it 
> works fine. We are using the JDBC for Create statement also. We are having 
> trouble understanding how to execute create a statement using the above 
> QueryEntity way. Can you please shed some light on it? An example snippet for 
> the statement "CREATE TABLE IF NOT EXISTS URLS ID LONG PRIMARY KEY, URL 
> VARCHAR, SCORE DOUBLE, APPNAME_ID LONG)" would be grateful.
> 
> Thank you,
> R S S Aditya Harish
> +91-8106278997 | Ext No. 4433
> ​<1571817034225000_1337245575.jpeg>
> 
> 
> 
>  On Tue, 16 Jun 2020 19:24:44 +0530 Stephen Darlington 
>  wrote 
> 
> There’s not one, right way of doing it. In Java it’s something like this.
> 
> Define your classes:
> 
> public class AppDetailsKey {
> @QuerySqlField
> private Long id;
> 
> public AppDetailsKey(Long id) {
> this.id  = id;
> }
> }
> 
> public class AppDetails {
> @QuerySqlField
> private String url;
> @QuerySqlField
> private Double score;
> @QuerySqlField
> private Long app_name;
> 
> public AppDetails(String url, Double score, Long app_name) {
> this.url = url;
> this.score = score;
> this.app_name = app_name;
> }
> }
> 
> (I didn’t define your secondary index but you can do that with the 
> annotations, too.)
> 
> Create your cache:
> 
> CacheConfiguration cacheConfiguration = new 
> CacheConfiguration<>();
> cacheConfiguration.setSqlSchema("PUBLIC")
> .setName("APPDETAILS")
> .setIndexedTypes(AppDetailsKey.class, AppDetails.class);
> 
> IgniteCache cache = 
> ignite.getOrCreateCache(cacheConfiguration);
> 
> The annotations and the IndexedTypes tell Ignite to make it available to the 
> SQL engine.
> 
> And then insert stuff into it:
> 
> IgniteDataStreamer ds = 
> ignite.dataStreamer("APPDETAILS");
> ds.addData(new AppDetailsKey(1L), new AppDetails("localhost", 1.0, 10L));
> ds.addData(new AppDetailsKey(2L), new AppDetails("localhost", 1.0, 10L));
> ds.addData(new AppDetailsKey(3L), new AppDetails("localhost", 1.0, 10L));
> ds.flush();
> 
> On 16 Jun 2020, at 06:35, R S S Aditya Harish  > wrote:
> 
> This is my SQL table schema
> 
> ID (Long), URL (Varchar), SCORE (Double), APPNAME_ID (Long)
> 
> We have a composite index on Score, Appname_Id.
> 
> Based on your answer I've two questions.
> 
> 1. How can I insert SQL rows using JCache data streamer API (if possible, 
> with example)? Currently, I'm using jdbc thin with STREAMING ON. But the 
> issue is mentioned above.
> 2. Each row data is -> ID (Long), URL (Varchar), SCORE (Double), APPNAME_ID 
> (Long). How this data is stored as Key-Value? I mean what will be the key and 
> what will be the value?
> 
> Can you please answer these two questions?
> 
> 
>  On Mon, 15 Jun 2020 21:44:38 +0530 Stephen Darlington 
> mailto:stephen.darling...@gridgain.com>> 
> wrote 
> 
> Do you need the sorting as part of the loading process? If not, the best 
> route would be to use the data streamer to load the data. You can still use 
> the SQL engine and access your sorted data afterwards — remember that SQL and 
> key-value are two different ways of accessing the same underlying data. 
> 
> > On 15 Jun 2020, at 15:46, adipro  > > wrote: 
> > 
> > We have an SQL table which we need because for normal JCache K-V we cannot 
> > sort on some column's data. We need that sort feature. That's why we chose 
> > SQL table representation. 
> > 
> > Our application is heavily multi-threaded. 
> > 
> > Now when trying to insert rows in that table, each thread simultaneously 
> > sends 5000-1 rows in bulk. Now if we use, SqlFieldsQuery, it's taking 
> > so 
> > much of time as we cannot do it in bulk and have to do it in loop one by 
> > one. 
> > 
> > For this case, we are using JDBC thin driver. 
> > 
> > But since it's multi-threaded we can't use single connection to execute in 
> > parallel as it is not thread safe. 
> > 
> > So, what we did is, we added a synchronisation block which contains the 
> > insertion of those rows in bulk using thin driver. The query performance is 
> > good, but so many threads are in wait state as this is happening. 
> > 
> > Can someone please suggest any idea on how to insert those many rows in 
> > bulk 
> > efficiently without threads waiting for so much time to use JDBC 
> > connecti

Re: Multi-threaded JDBC driver issue/concern

[R S S Aditya Harish]

Hi Stephen,



Thank you so much for giving the snippet. I followed and it worked. But I have 
one more doubt. In the below section the query is insertion query and it works 
fine. We are using the JDBC for Create statement also. We are having trouble 
understanding how to execute create a statement using the above QueryEntity 
way. Can you please shed some light on it? An example snippet for the statement 
"CREATE TABLE IF NOT EXISTS URLS ID LONG PRIMARY KEY, URL VARCHAR, SCORE 
DOUBLE, APPNAME_ID LONG)" would be grateful.


Thank you,

R S S Aditya Harish


+91-8106278997 | Ext No. 4433

​






 On Tue, 16 Jun 2020 19:24:44 +0530 Stephen Darlington 
 wrote 


There’s not one, right way of doing it. In Java it’s something like this.

Define your classes:



public class AppDetailsKey {
    @QuerySqlField
    private Long id;

    public AppDetailsKey(Long id) {
        http://this.id = id;
    }
}


public class AppDetails {
    @QuerySqlField
    private String url;
    @QuerySqlField
    private Double score;
    @QuerySqlField
    private Long app_name;

    public AppDetails(String url, Double score, Long app_name) {
        this.url = url;
        this.score = score;
        this.app_name = app_name;
    }
}


(I didn’t define your secondary index but you can do that with the annotations, 
too.)



Create your cache:



CacheConfiguration cacheConfiguration = new 
CacheConfiguration<>();
cacheConfiguration.setSqlSchema("PUBLIC")
        .setName("APPDETAILS")
        .setIndexedTypes(AppDetailsKey.class, AppDetails.class);

IgniteCache cache = 
ignite.getOrCreateCache(cacheConfiguration);


The annotations and the IndexedTypes tell Ignite to make it available to the 
SQL engine.



And then insert stuff into it:

IgniteDataStreamer ds = 
ignite.dataStreamer("APPDETAILS");
ds.addData(new AppDetailsKey(1L), new AppDetails("localhost", 1.0, 10L));
ds.addData(new AppDetailsKey(2L), new AppDetails("localhost", 1.0, 10L));
ds.addData(new AppDetailsKey(3L), new AppDetails("localhost", 1.0, 10L));
ds.flush();


On 16 Jun 2020, at 06:35, R S S Aditya Harish 
 wrote:


This is my SQL table schema



ID (Long), URL (Varchar), SCORE (Double), APPNAME_ID (Long)



We have a composite index on Score, Appname_Id.



Based on your answer I've two questions.



1. How can I insert SQL rows using JCache data streamer API (if possible, with 
example)? Currently, I'm using jdbc thin with STREAMING ON. But the issue is 
mentioned above.

2. Each row data is -> ID (Long), URL (Varchar), SCORE (Double), APPNAME_ID 
(Long). How this data is stored as Key-Value? I mean what will be the key and 
what will be the value?



Can you please answer these two questions?



 On Mon, 15 Jun 2020 21:44:38 +0530 Stephen Darlington 
 wrote 


Do you need the sorting as part of the loading process? If not, the best route 
would be to use the data streamer to load the data. You can still use the SQL 
engine and access your sorted data afterwards — remember that SQL and key-value 
are two different ways of accessing the same underlying data. 
 
> On 15 Jun 2020, at 15:46, adipro  wrote: 
> 
> We have an SQL table which we need because for normal JCache K-V we cannot 
> sort on some column's data. We need that sort feature. That's why we chose 
> SQL table representation. 
> 
> Our application is heavily multi-threaded. 
> 
> Now when trying to insert rows in that table, each thread simultaneously 
> sends 5000-1 rows in bulk. Now if we use, SqlFieldsQuery, it's taking so 
> much of time as we cannot do it in bulk and have to do it in loop one by 
> one. 
> 
> For this case, we are using JDBC thin driver. 
> 
> But since it's multi-threaded we can't use single connection to execute in 
> parallel as it is not thread safe. 
> 
> So, what we did is, we added a synchronisation block which contains the 
> insertion of those rows in bulk using thin driver. The query performance is 
> good, but so many threads are in wait state as this is happening. 
> 
> Can someone please suggest any idea on how to insert those many rows in bulk 
> efficiently without threads waiting for so much time to use JDBC connection. 
> 
> 
> 
> -- 
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: HashMap warning when using PutAll()

[Ilya Kasnacheev]

Hello!

If your Map is a singleton (only one pair) the warning should not be
printed :)

Regards,
-- 
Ilya Kasnacheev


ср, 17 июн. 2020 г. в 23:22, Raymond Wilson :

> Hi Ilya,
>
> Thank you for the SortedDictionary pointer.
>
> I am not sure what you mean by 'size-1 collections'. Is there an issue
> here that requires fixing in Ignite, or just the use of SortedDIctionary in
> my client code?
>
> Thanks,
> Raymond.
>
> On Thu, Jun 18, 2020 at 3:45 AM Ilya Kasnacheev 
> wrote:
>
>> Hello!
>>
>> The warning should not be issued for size-1 collections.
>> In dotnet, you should use SortedDictionary with putAll.
>>
>> Regards,
>> --
>> Ilya Kasnacheev
>>
>>
>> сб, 13 июн. 2020 г. в 09:43, Raymond Wilson :
>>
>>> If the answer is that the consumer of the C# client should order the
>>> items before calling PutAll(), how does the consumer of the C# client
>>> ensure that is the case before calling PutAll()?
>>>
>>> On Sat, Jun 13, 2020 at 4:13 PM Raymond Wilson <
>>> raymond_wil...@trimble.com> wrote:
>>>
 Hi Evgenii,

 Thanks for the quick response and clarification :)

 Does this mean the C# client is looking at the collection provided to
 it and choosing treemap vs hashmap for an internal call to the Ignite Java
 layer based on whether the content of the collection provided is sorted?

 Thanks,
 Raymond.


 On Sat, Jun 13, 2020 at 2:23 AM Evgenii Zhuravlev <
 e.zhuravlev...@gmail.com> wrote:

> The same works for other operations too(like removeAll).
>
> Evgenii
>
> пт, 12 июн. 2020 г. в 07:22, Evgenii Zhuravlev <
> e.zhuravlev...@gmail.com>:
>
>> Raymond,
>>
>> Collections used in putAll should be sorted, because otherwise if
>> they have the same entries but in a different order, it can lead to the
>> classic deadlock. It is expected behavior.
>>
>> Best Regards,
>> Evgenii
>>
>> чт, 11 июн. 2020 г. в 21:38, Raymond Wilson <
>> raymond_wil...@trimble.com>:
>>
>>> We are using Ignite v2.8.0 and the C# client. Some of our operations
>>> use PutAll() to save a collection of items in a single operation. This
>>> operation is emitting the following warning into the log:
>>>
>>> 2020-06-10 15:04:14,199 [77] WRN [ImmutableClientServer]
>>>  Unordered map java.util.HashMap is
>>> used for putAll operation on cache Spatial-SubGridDirectory-Immutable. 
>>> This
>>> can lead to a distributed deadlock. Switch to a sorted map like TreeMap
>>> instead.
>>>
>>> Does this require a Jira ticket?
>>>
>>> Thanks,
>>> Raymond.
>>>
>>>
>>> --
>>> 
>>> Raymond Wilson
>>> Solution Architect, Civil Construction Software Systems (CCSS)
>>> 11 Birmingham Drive | Christchurch, New Zealand
>>> +64-21-2013317 Mobile
>>> raymond_wil...@trimble.com
>>>
>>>
>>> 
>>>
>>

 --
 
 Raymond Wilson
 Solution Architect, Civil Construction Software Systems (CCSS)
 11 Birmingham Drive | Christchurch, New Zealand
 +64-21-2013317 Mobile
 raymond_wil...@trimble.com


 

>>>
>>>
>>> --
>>> 
>>> Raymond Wilson
>>> Solution Architect, Civil Construction Software Systems (CCSS)
>>> 11 Birmingham Drive | Christchurch, New Zealand
>>> +64-21-2013317 Mobile
>>> raymond_wil...@trimble.com
>>>
>>>
>>> 
>>>
>>
>
> --
> 
> Raymond Wilson
> Solution Architect, Civil Construction Software Systems (CCSS)
> 11 Birmingham Drive | Christchurch, New Zealand
> +64-21-2013317 Mobile
> raymond_wil...@trimble.com
>
>
> 
>

primary keys

[narges saleh]

Hi All,

Shouldn't primary keys result in indexes and if so, shouldn't I be able to
see them when I list indexes?
Does inline index size applicable to primary keys too?
Additionally, when I do explain plan on a query which involves tables with
primary keys, shouldn't I see the primary key/index being used? Or lack of
a scan statement imply that an index is being used?
---+
|  PLAN  |
++
| SELECT
ID, NAME,TIMESTAMP
FROM PUBLIC.table1
/* PU |

sql is: select timestamp from table1 where id = 50 and name = 'John';
//primary key is on id + name

RE: history item not found exception

[BEELA GAYATRI]

Hi Team,

Please reply to  the below mail

Sent from Mail for Windows 10

From: BEELA GAYATRI
Sent: Monday, June 15, 2020 1:06 PM
To: user@ignite.apache.org
Subject: RE: history item not found exception




Hi Team,

I am running 3 server nodes  by setting baseline topology for all the 3 servers 
 with persistence enabled

I have stopped one of the nodes and again started the node. I am getting below 
error  so I have remove metastorage folder in the persistence storage path for 
that node. Now the node is up and running perfectly.

Please clarify ,what could be the reason behind the error and why the node is 
up and rrunning after removing the metastorage folder in persistence path of 
the node?

Exception during start processors, node will be stopped and close connections
class org.apache.ignite.IgniteCheckedException: Restoring of BaselineTopology 
history has failed, expected history item not found for id=3
at 
org.apache.ignite.internal.processors.cluster.BaselineTopologyHistory.restoreHistory(BaselineTopologyHistory.java:54)
at 
org.apache.ignite.internal.processors.cluster.GridClusterStateProcessor.onReadyForRead(GridClusterStateProcessor.java:223)
at 
org.apache.ignite.internal.processors.cache.persistence.GridCacheDatabaseSharedManager.notifyMetastorageReadyForRead(GridCacheDatabaseSharedManager.java:396)
at 
org.apache.ignite.internal.processors.cache.persistence.GridCacheDatabaseSharedManager.readMetastore(GridCacheDatabaseSharedManager.java:662)
at 
org.apache.ignite.internal.processors.cache.persistence.GridCacheDatabaseSharedManager.notifyMetaStorageSubscribersOnReadyForRead(GridCacheDatabaseSharedManager.java:4610)
at 
org.apache.ignite.internal.IgniteKernal.start(IgniteKernal.java:1048)
at 
org.apache.ignite.internal.IgnitionEx$IgniteNamedInstance.start0(IgnitionEx.java:2038)
at 
org.apache.ignite.internal.IgnitionEx$IgniteNamedInstance.start(IgnitionEx.java:1730)
at 
org.apache.ignite.internal.IgnitionEx.start0(IgnitionEx.java:1158)
at 
org.apache.ignite.internal.IgnitionEx.startConfigurations(IgnitionEx.java:1076)
at 
org.apache.ignite.internal.IgnitionEx.start(IgnitionEx.java:962)
at 
org.apache.ignite.internal.IgnitionEx.start(IgnitionEx.java:861)
at 
org.apache.ignite.internal.IgnitionEx.start(IgnitionEx.java:731)
at 
org.apache.ignite.internal.IgnitionEx.start(IgnitionEx.java:700)
at org.apache.ignite.Ignition.start(Ignition.java:348


Sent from Mail for Windows 10



=-=-=
Notice: The information contained in this e-mail
message and/or attachments to it may contain 
confidential or privileged information. If you are 
not the intended recipient, any dissemination, use, 
review, distribution, printing or copying of the 
information contained in this e-mail message 
and/or attachments to it are strictly prohibited. If 
you have received this communication in error, 
please notify us by reply e-mail or telephone and 
immediately and permanently delete the message 
and any attachments. Thank you

Re: sqlline

[narges saleh]

Hi Denis
But if all the involved tables/caches have the same affinity key, shouldn't
they be collocated on the same node, if the data fits on that node?
thanks.

On Wed, Jun 17, 2020 at 10:53 PM Denis Magda  wrote:

> You can always send a compute task via one of IgniteCompute.affinityX()
> methods and run a *local* SQL query on that node:
>
>- affinity calls:
>
> https://apacheignite.readme.io/docs/collocate-compute-and-data#affinity-call-and-run-methods
>- local SQL queries:
>https://apacheignite-sql.readme.io/docs/local-queries
>
> My selects are complex but they do use the primary keys for the
>> partitioned tables/caches participating in the query. But they perform
>> poorly if the data is distributed across multiple nodes.
>
>
> If the data is co-located and scattered across cluster nodes then it's
> expected that Ignite will execute a query across several nodes. It's a
> standard approach of distributed databases that support SQL engines. My
> suggestion would be to investigate the root cause of low performance. You
> just might need to configure some indexes:
> https://apacheignite-sql.readme.io/docs/performance-and-debugging
>
> -
> Denis
>
>
> On Wed, Jun 17, 2020 at 7:33 PM narges saleh  wrote:
>
>> My selects are complex but they do use the primary keys for the
>> partitioned tables/caches participating in the query. But they perform
>> poorly if the data is distributed across multiple nodes.
>>
>> On Wed, Jun 17, 2020 at 8:04 PM narges saleh 
>> wrote:
>>
>>> Thanks Denis. In case of selects, can I push ignite to broadcast the sql
>>> to where the data is, say by using employing services deployed on the
>>> targeted data nodes, or utilizing affinity calls (with the sql as part of
>>> the logic)?
>>>
>>> On Wed, Jun 17, 2020 at 7:02 PM Denis Magda  wrote:
>>>
 Narges,

 INSERTS/UPDATES/DELETES will be forwarded to primary nodes based on the
 value of primary keys you pass into queries. Ignite maps a primary key to a
 partition and, finally, to a primary node that holds that partition. All
 this happens transparently in Ignite. You don't need to do anything except
 for sending those SQL commands.

 As for SELECTS, most of them will be broadcasted to your server nodes
 unless Ignite can pre-select a sub-set of target nodes a query needs to be
 executed on. This optimization always takes place for simple queries when
 you try to get records based on the value of a primary key, such as "SELECT
 * FROM Table WHERE primaryKeyColumn = 2".

 -
 Denis


 On Wed, Jun 17, 2020 at 12:43 PM narges saleh 
 wrote:

> Thanks Denis. Will watch the video.
> One more question, related to SQL execution. If I issue a SQL from a
> client, how would ignite know which node to run the SQL query on? I assume
> the SQL is parsed on the server nodes, and I will still need to direct the
> SQL to be executed on the right server node (assuming that all the data is
> collocated on a single node). Am I right?
>
> On Wed, Jun 17, 2020 at 2:26 PM Denis Magda  wrote:
>
>> The collocated flag is an extra optimization hint for SQL queries
>> with GROUP BYs. The parameter requests to group records locally on the
>> nodes that participated in the query execution. For instance, if your
>> application sends a query with GROUP BY to a couple of server nodes, then
>> the nodes will group their portions of the result sets locally not making
>> that the burden of the application that merges/reduces the result sets 
>> into
>> one. The description of this parameter here should be helpful as well:
>> https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters
>>
>> 
>>>
>>> Should I assume that if the table is defined with affinity set, then
>>> compute collocation, including sql execution will happen automatically?
>>
>>
>> That's right. You just need to set up the affinity keys and Ignite
>> will take care of the rest. Btw, you might be interested in this SQL
>> webinar we delivered recently. The webinar explains our SQL capabilities 
>> in
>> detail: https://www.youtube.com/watch?v=eYV-tNLzIts
>>
>> -
>> Denis
>>
>>
>> On Wed, Jun 17, 2020 at 11:28 AM narges saleh 
>> wrote:
>>
>>> Then can you please explain what the option collocated=true for JDBC
>>> url is for?
>>> Should I assume that if the table is defined with affinity set, then
>>> compute collocation, including sql execution will happen automatically?
>>> I am sorry if this is just a basic question.
>>>
>>> On Wed, Jun 17, 2020 at 12:57 PM Denis Magda 
>>> wrote:
>>>
 If the data is already co-located you don't need to pass in any
 other parameters.

 -
 Denis


 On W