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 <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 <mailto:aditya.har...@zohocorp.com> 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) { 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<AppDetailsKey, AppDetails> cacheConfiguration = new CacheConfiguration<>(); cacheConfiguration.setSqlSchema("PUBLIC") .setName("APPDETAILS") .setIndexedTypes(AppDetailsKey.class, AppDetails.class); IgniteCache<AppDetailsKey, AppDetails> 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<AppDetailsKey,AppDetails> 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 <mailto:aditya.har...@zohocorp.com> 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 <mailto:aditya.har...@zohocorp.com> 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-10000 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/
