Re: Ignite SQL

Why does spark.sql() take more time than client.sql() when query is same and is made to ignite dataframe only?? Regards On Tue, 18 Jul, 2023, 10:40 pm Stephen Darlington, < stephen.darling...@gridgain.com> wrote: > “Correct” is hard to quantify without knowing your use case, but option 1 > is pr

Re: Ignite SQL

Just an example. I mean to say a part of my data resides in Ignite, not the complete data. Rest data is present in Spark. On Wed, 19 Jul, 2023, 2:07 pm Stephen Darlington, < stephen.darling...@gridgain.com> wrote: > Why you would have Ignite, a horizontally scalable, in-memory database, to > sto

Re: Ignite SQL

Why you would have Ignite, a horizontally scalable, in-memory database, to store 100 records? > On 19 Jul 2023, at 04:37, Arunima Barik wrote: > > I have a huge dataset and I am keeping few (say 100) rows in Ignite and the > entire dataset remains in Spark > > When I query Ignite I want to wr

Re: Ignite SQL

I have a huge dataset and I am keeping few (say 100) rows in Ignite and the entire dataset remains in Spark When I query Ignite I want to write an Sql query to perform the same. Does option 1 still hold good? On Tue, 18 Jul, 2023, 10:40 pm Stephen Darlington, < stephen.darling...@gridgain.com> w

Re: Ignite SQL

“Correct” is hard to quantify without knowing your use case, but option 1 is probably what you want. Spark pushes down SQL execution to Ignite, so you get all the distribution, use of indexes, etc. > On 14 Jul 2023, at 16:12, Arunima Barik wrote: > > Hello team > > What is the correct way ou

Re: Ignite SQL table ALTER COLUMN and RENAME COLUMN

Please check out this note: https://www.gridgain.com/docs/8.7.6/perf-troubleshooting-guide/troubleshooting#cluster-doesnt-start-after-field-type-changes There is a hacky way to change the type if you're in development but if you're in prod then you need to add new columns instead. - Denis On Th

Re: Ignite SQL table ALTER COLUMN and RENAME COLUMN

Favas, It’s possible to remove a column and add another one using ALTER COMAND SQL statement, but currently you can't change a column’s type. Note, that removing a column and adding another one with the same name but with a different type can lead to data corruption. Denis On 10 Oct 2019, 09:51

Re: Ignite SQL table schema metadata

Hello! Ignite does not enforce length of VARCHAR fields, you can store any String in VARCHAR fields, so you should neither expect not use that value. Regards, -- Ilya Kasnacheev вт, 8 окт. 2019 г. в 15:22, Muhammed Favas < favas.muham...@expeedsoftware.com>: > Hi, > > > > Is there a way in ig

Re: Ignite SQL : Index on primary Key

Hello! Can you please show cache/table configuration, original query and its EXPLAIN (all rows)? Regards, -- Ilya Kasnacheev ср, 13 февр. 2019 г. в 18:24, mahesh76private : > But we faced index does not exist exception, when joining with a primary > key > into another table on Ignite 2.7.0. >

Re: Ignite SQL : Index on primary Key

But we faced index does not exist exception, when joining with a primary key into another table on Ignite 2.7.0. regards Mahesh -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Ignite SQL : Index on primary Key

Hello! In Apache Ignite 2.7.0 it is now created by default. Regards, -- Ilya Kasnacheev вт, 12 февр. 2019 г. в 08:26, mahesh76private : > looks like a index is not not created automatically on a primary key... > Shouldn't it be automatic? > > faced exceptions while trying to execute join. > >

Re: Ignite SQL Queries not getting all data back in ignite 2.4 and 2.6

The above error appears to be an issue in org.apache.ignite.internal.processors.cache.CacheMetricsSnapshot in a mismatch between the readExternal() and writeExternal() methods. I've made a change locally and it fixed the error and also the testing so far seems to show that the queries return the c

Re: Ignite SQL Queries not getting all data back in ignite 2.4 and 2.6

Thanks Vlad / Dmitry, I was able to download the nightly build from teamcity as a guest. However I'm now getting the following error when trying to add data to the cache. Any ideas? class org.apache.ignite.IgniteException: Failed to get affinity mapping from node: TcpDiscoveryNode [id=eeef1e32-db

Re: Ignite SQL Queries not getting all data back in ignite 2.4 and 2.6

Dmitriy, I wanted to suggest this, but looks like we do not have direct links for regular distribution. User can download it from TeamCity, but username/password is needed for this. May be I missing something. On Thu, Aug 16, 2018 at 11:44 AM Dmitriy Setrakyan wrote: > I also want to point out

Re: Ignite SQL Queries not getting all data back in ignite 2.4 and 2.6

I also want to point out that Ignite has nightly builds, so you can try them instead of doing your own build as well. https://ignite.apache.org/download.cgi#nightly-builds D. On Thu, Aug 16, 2018 at 1:38 AM, Vladimir Ozerov wrote: > Hi, > > There were a lot of changes in the product since 2.3

Re: Ignite SQL Queries not getting all data back in ignite 2.4 and 2.6

Hi, There were a lot of changes in the product since 2.3 which may affect it. Most important change was baseline topology, as already mentioned. I am aware of a case when incorrect result might be returned [1], which is already fixed in *master*. Not sure if this is the same issue, but you may try

Re: Ignite SQL Queries not getting all data back in ignite 2.4 and 2.6

Thanks for getting back, but we do not use Ignite's native persistence. Anything else changed from 2.3 to 2.4 to cause this around SQL Queries? -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Ignite SQL Queries not getting all data back in ignite 2.4 and 2.6

Hi, Do you use persistence? If so, please check and set up Baseline topology for your cluster which describes a set of server nodes that are used to store data. The reason for this behaviour may be a simple fact that some of your nodes are

Re: Ignite Sql query problem using IN clause

There is a suggested workaround in https://apacheignite-sql.readme.io/docs/performance-and-debugging#section-sql-performance-and-usability-considerations, item 2 As a workaround...you can rewrite the query in the following way: select p.name from Person p join table(id bigint = ?) i on p.id = i.i

Re: Ignite Sql query problem using IN clause

Has it been fixed in 2.4 ? Looks like it is not, when I generate the explain plan for a query which has a composite key Here are the queries I ran and explain plan results. When I use IN, it uses the full table scan, otherwise It does look up on primary key explain SELECT * FROM "MapLonglistCach

Re: Ignite Sql Query Execution time is too long

Siva, The execution plan shows, that no indexes are used. If you create an index on *EntityType* field, it should make the performance better. Also if you make all data be stored in memory, then performance of select queries will be better. Otherwise you will be limited by the performance of your

Re: Ignite Sql Query Execution time is too long

Have you tried adding indexes to frequently searched fields ? Indexing can make searching the cache quite faster -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Ignite SQL function questions

There is always one copy stored in Ignite. The key-value store keeps data and Ignite indexes it using B+trees. H2 calls Ignite’s trees when prepares execution plans. This is partially mentioned here [1] and there [2] you can read more about the memory architecture. [1] https://apacheignite.re

Re: Ignite SQL function questions

Hi Ray, Ignite replaces H2 row object with its own stub. So, when you ask for a field value, ignite get entry represented by the row and get entry field according to configured mapping (via Queryentity [1] or via annotations [2]). Ignite stores data in serialized way, as every field can be accesse

Re: Ignite SQL function questions

So when I run a sql in h2 debug console, how does h2 get results from cache store? In other words, how does the key/value cache entry to h2 entry mapping work? -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Ignite SQL function questions

Hi, You will have only one copy of data, tables are needed for correct H2 work. When do some query, H2 builds a query plan that will be delegated to Ignite, which underneath gets data from cache. Thanks! -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Ignite sql queries working transactionally

Denis, I think you provided an incorrect link to the ticket. Here is the correct link: https://issues.apache.org/jira/browse/IGNITE-3478 D. On Wed, Aug 30, 2017 at 5:50 PM, Denis Magda wrote: > Hi, > > The docs are still valid - SQL operations are not fully transactional yet > and, according,

Re: Ignite sql queries working transactionally

Hi, The docs are still valid - SQL operations are not fully transactional yet and, according, to JIRA the works is in progress to make this happen: https://ggsystems.atlassian.net/browse/IGN-4666 — Denis > On Aug 30, 2017, at 12:21 AM, kotamra

Re: Ignite sql queries working transactionally

In the nearest release should be fixed https://issues.apache.org/ jira/browse/IGNITE-6044 and in the subsequent release will be added support for transactional SQl. 2017-08-30 12:36 GMT+03:00 kotamrajuyashasvi : > Hi > > Thanks for the response > > I still do not understand the difference betwee

Re: Ignite sql queries working transactionally

Hi Thanks for the response I still do not understand the difference between using sql queries in ignite Transaction and sql transactions feature that is being mentioned which would be added in upcoming releases. If in future releases, using sql queries in Transactions throws exception, How am I

Re: Ignite sql queries working transactionally

Hi, Currently, Sql is not transactional, support of transactions in SQL should be added in 2.2 release(or it could be added in 2.3 release, if version 2.2 will be released for some hot fixes, it currently descussing on dev list). It's not recommended to use SQL in Transactions, most possible tha

Re: Ignite SQL queries's metadata

Hi Menglei, It is fixed in IGNITE-5252 [1] and already merged to master, Fix will available from 2.1 release coming. [1] https://issues.apache.org/jira/browse/IGNITE-5252 On Wed, Jul 19, 2017 at 12:14 AM, menglei wrote: > Hi all, > > Currently we are heavily using the standard SQL query functi

Re: Ignite SQL query result's metadata

Hi Menglei, Please properly subscribe to the mailing list so that the community can receive email notifications for your messages. To subscribe, send empty email to user-subscr...@ignite.apache.org and follow simple instructions in the reply. menglei wrote > Hi, > > Currently we are heavily usi

Re: Ignite SQL Queries on Lists fields sorting

ok thank you -- View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Ignite-SQL-Queries-on-Lists-fields-sorting-tp14129p14305.html Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Ignite SQL Queries on Lists fields sorting

Hi, To allow fields to be used in queries you should mark them wither with @QuerySqlField annotation or configure QueryEntity in cache configuration. Sorting by collections is not supported. On Wed, Jun 28, 2017 at 12:06 PM, begineer wrote: > hey Guys, > I have a question about Ignite Sql Quer

Re: Ignite SQL Indexing Performance problems

Yes, you have to go with JOIN and create better index for your query. Sergi 2017-05-03 16:27 GMT+03:00 kmandalas : > @Andrey: there is reason that I have selected REPLICATED over PARTITIONED > cache due to my business case. Custom Affinity is under investigation, > however seems over complicated

Re: Ignite SQL Indexing Performance problems

@Andrey: there is reason that I have selected REPLICATED over PARTITIONED cache due to my business case. Custom Affinity is under investigation, however seems over complicated for my scenario for starters. Now, even if PARTITIONED is used and things get done in parallel, the Network I/O is involved

Re: Ignite SQL Indexing Performance problems

Sergi, AFAIK, you suggest to make query run on replicated cache in similar way as on partitioned. Do you mean that query will run on one node in single thread as it works now? On Wed, May 3, 2017 at 3:39 PM, Sergi Vladykin wrote: > Andrey, > > In 2.0 queries over replicated cache still work in

Re: Ignite SQL Indexing Performance problems

Andrey, In 2.0 queries over replicated cache still work in a single thread. Kyriakos, I see that only index on TYPE is used which is not the most selective obviously. I suggest to create a group index on (TYPE, CATEGORYID, GEOCHANNELID, WEEK) and make sure it is used in query plan. Sergi 201

Re: Ignite SQL Indexing Performance problems

Hi, As you use REPLICATED cache, the query will run on one node in single thread. This should be fixed in 2.0 The only workaround for now is to convert cache to PARTITIONED, to make the query to be run on several nodes, that will significantly increase performance. Also, you can try to add more f

Re: Ignite SQL Grid Load

Andrew, Figured out the issue. We created a user defined spatial function and in it we were using an object that's not thread safe. That created an issue. Thank you very much Raja -- View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Ignite-SQL-Grid-Load-tp11072p110

Re: Ignite SQL Grid Load

Hi Raja, Would you please attach full ignite logs with failure? On Wed, Mar 8, 2017 at 9:44 AM, Raja wrote: > Hi, > I have a 12 node ignite cluster with node configuration of 8 core, 10g > memory > I am using the following JVM options as recommended by ignite docs > > IGNITE_JVM_OPTS=-server \

Re: Ignite Sql query problem using IN clause

It looks like an issue in H2 database engine, but it was fixed in newest versions. In Ignite-1.7.0 (which is on the vote right now) we upgraded H2, so it must be ok. Sergi 2016-08-04 11:19 GMT+03:00 Zhengqingzheng : > Hi there, > > When we using ignite sql query, we find that IN clause cannot u

Re: Ignite - SQL queries aggregation

Ignite SQL engine built on top of H2 engine and supports aggregation functions like cnt, max, etc. Also Ignite supports user defined function. See QuerySqlFunction annotation documentation and its usages in Ignite tests (e.g. https://github.com/apache/ignite/blob/68891e89dd0e0f19321d6a4d45ae737227

答复: re: Ignite Sql Query performance problem

[mailto:valentin.kuliche...@gmail.com] 发送时间: 2016年4月11日 13:12 收件人: user@ignite.apache.org 主题: Re: re: Ignite Sql Query performance problem Kevin, Each index add memory overhead. It's hard to estimate without knowing the details of the application, but in general indexes do not add more than 20-30% to the ov

re: re: Ignite Sql Query performance problem

原件- 发件人: vkulichenko [mailto:valentin.kuliche...@gmail.com] 发送时间: 2016年4月11日 13:12 收件人: user@ignite.apache.org 主题: Re: re: Ignite Sql Query performance problem Kevin, Each index add memory overhead. It's hard to estimate without knowing the details of the application, but in general index

Re: re: Ignite Sql Query performance problem

Kevin, Each index add memory overhead. It's hard to estimate without knowing the details of the application, but in general indexes do not add more than 20-30% to the overall data size. BTW, if you can replace query with a simple get(), you should do this. It will be more effective from both perf

re: Ignite Sql Query performance problem

the cost for the memory usage? Best regards, Kevin -邮件原件- 发件人: vkulichenko [mailto:valentin.kuliche...@gmail.com] 发送时间: 2016年4月11日 5:54 收件人: user@ignite.apache.org 主题: Re: Ignite Sql Query performance problem Hi, Most likely you don't have indexes and the query has to scan the

Re: Ignite Sql Query performance problem

Hi, Most likely you don't have indexes and the query has to scan the whole cache to find the data you're requesting. Can you show your configuration and the SQL statement that you're executing? -Val -- View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Ignite-Sql-Que