Re: using JDBC with Ignite cluster, configured with persistent storage

[soroka21]

Here is what happened:
1) I've configured standalone cluster of 5 nodes with persistent storage
enabled in data storage part.
Following is the section of my configuration file:

 
   
 
   

 
  
  
  
  
 
   
   
 
 


2) I'd like to create Table using JDBC driver using following DDL statement
CREATE TABLE MY_TABLE
(_id varchar,F00 varchar,F01 bigint,F02 double,F03 timestamp,F04
varchar,F05 bigint,
 F06 double,F07 timestamp,F08 varchar,F09 bigint, PRIMARY KEY(_id)) 
WITH "cache_name=MyCacheName, value_type=MyValueTpye";

I'm using SQLLine to run this statement. 
As a result of DDL execution I'm getting following exception:

Error: Failed to handle JDBC request because node is stopping.
(state=5,code=0)
java.sql.SQLException: Failed to handle JDBC request because node is
stopping.
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)

What should be done to avoid this exception?

Alexey




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

using JDBC with Ignite cluster, configured with persistent storage

[soroka21]

Hi,
I'm trying to use JDBC connection to run SQL on cluster, configured with
Persistent storage.
Persistent storage means what I have to make cluster active before I can do
any DDL (or even request list of tables?)
below is the output of sqlline, I'm trying to use, :


0: jdbc:ignite:thin://10.238.42.86/> !tables
Error: Failed to handle JDBC request because node is stopping.
(state=5,code=0)
java.sql.SQLException: Failed to handle JDBC request because node is
stopping.
at
org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:671)
at
org.apache.ignite.internal.jdbc.thin.JdbcThinDatabaseMetadata.getTables(JdbcThinDatabaseMetadata.java:740)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at sqlline.Reflector.invoke(Reflector.java:75)
at sqlline.Commands.metadata(Commands.java:194)
at sqlline.Commands.tables(Commands.java:332)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at
sqlline.ReflectiveCommandHandler.execute(ReflectiveCommandHandler.java:38)
at sqlline.SqlLine.dispatch(SqlLine.java:791)
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://10.238.42.86/> CREATE TABLE table4(_id varchar,F00
varchar,F01 bigint,F02 double,F03 timestamp,F04 varchar,F05 bigint,F06
double,F07 timestamp,F08 varchar,F09 bigint, PRIMARY KEY(_id)) WITH
"cache_name=table4, value_type=table4";
Error: Failed to handle JDBC request because node is stopping.
(state=5,code=0)
java.sql.SQLException: Failed to handle JDBC request because node is
stopping.
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)

Cluster configuration looks like this:


http://www.springframework.org/schema/beans;
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance;
   xsi:schemaLocation="
   http://www.springframework.org/schema/beans
   http://www.springframework.org/schema/beans/spring-beans.xsd;>








xxx.xxx.xxx.xxx:47500..47509
yyy.yyy.yyy.yyy:47500..47509




































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

Re: SQL Count(*) returns incorrect count

[soroka21]

Yes, that worked for me. I guess this will be fixed in version 2.4. 
Final version of JavaIgniteContext construction looks like:

JavaIgniteContext igniteContext
  = new JavaIgniteContext<>(jctx, () -> {
   IgniteConfiguration cfg = null;
try {
cfg =
IgnitionEx.loadConfiguration(igniteConfigPath).get1();
*cfg.setClientMode(true);*
} catch(Exception e){
// Report exception and return null 
logger.error(e.getMessage());
e.printStackTrace();  
 } 
 return cfg;
  }, true);

Thanks a lot for helping with this!
Was able to load 50,000,000 records from parquet files (6.4GB) in less than
4 minutes.

P.S. What would be the correct way to execute DDL (CREATE TABLE) statement
without JDBC/ODBC?





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

Re: SQL Count(*) returns incorrect count

[soroka21]

Thank you for suggestions.
I have 5 node standalone ignite cluster and the main goal is to load data
into it and store it for long time for future use. I can't keep Spark
workers in memory and assume what my data is ending up in cache distributed
inside 5 standalone ignite nodes.

Spark process is starting 5 additional ignite servers ( I can see it from
topology snapshot) 
With standalone=true or standalone=false same issue is happening when I'm
running my spark application using YARN.

However I found what if Spark is working in local, non-distributed mode
(e.g. spark.master=local[2]) records are not getting lost.

So it looks like the issue is when spark workers, processing JavaIgniteRDD,
are going down.



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

Re: SQL Count(*) returns incorrect count

[soroka21]

Switched to Ignite 2.3.0 in hope it has better behavior.
Unfortunately it is not.

During the execution of Spark job number of cache rows is growing but after
Spark job completes - looks like some entries has been removed.
JavaIgniteRDD shows correct count but again final result is incorrect.

I was using sqlline to track count of record during Spark job execution - it
shows some problem also:

0: jdbc:ignite:thin://10.238.42.86/> select count(*) from data3;
++
|COUNT(*)|
++
| 6428   |
++
1 row selected (0.063 seconds)
0: jdbc:ignite:thin://10.238.42.86/> select count(*) from data3;
++
|COUNT(*)|
++
| 62501  |
++
1 row selected (0.096 seconds)
0: jdbc:ignite:thin://10.238.42.86/> select count(*) from data3;
++
|COUNT(*)|
++
| 268183 |
++
1 row selected (0.154 seconds)
0: jdbc:ignite:thin://10.238.42.86/> select count(*) from data3;
++
|COUNT(*)|
++
| 482616 |
++
1 row selected (0.159 seconds)
0: jdbc:ignite:thin://10.238.42.86/> select count(*) from data3;
++
|COUNT(*)|
++
| 722436 |
++
1 row selected (0.159 seconds)
0: jdbc:ignite:thin://10.238.42.86/> select count(*) from data3;
++
|COUNT(*)|
++
| 1017236| <---
Still looks good here
++
1 row selected (0.205 seconds)
0: jdbc:ignite:thin://10.238.42.86/> select count(*) from data3;
++
|COUNT(*)|
++
| 542535 |<---
Ooops!
++






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

SQL Count(*) returns incorrect count

[soroka21]

Hi,

I've loaded 50 million BinaryObjects into TEST cache using Apache Spark 
They look like this:

o.a.i.i.binary.BinaryObjectImpl | DATASET1 [hash=86282065, F01=-206809353,
F00=A1782096681-B2022047863-C554782990, F03=Must be timestamp,
F02=2.6983596317719E8, F05=182918247,
F04=A1997114384-B293944165-C1698394711, F07=Some string,
F06=-9.9171693326279E8, F09=47148799, F08=A1582165880-B1796903902-C784688220

BinaryObject name is DATASET1
I'm trying to get count of those objects using SQL:

String sqlStr = "SELECT COUNT(*) FROM TEST.DATASET1";
Ignite ignite = Ignition.start();
IgniteCache cache =
ignite.cache(cacheName);
logger.info("Starting SQL query : " + sqlStr);
logger.info("ALL Cache size is " +
cache.sizeLong(CachePeekMode.ALL));
logger.info("PRIMARY Cache size is " +
cache.sizeLong(CachePeekMode.PRIMARY));
logger.info("NEAR Cache size is " +
cache.sizeLong(CachePeekMode.NEAR));
logger.info("OFFHEAP Cache size is " +
cache.sizeLong(CachePeekMode.OFFHEAP));
logger.info("ONHEAP Cache size is " +
cache.sizeLong(CachePeekMode.ONHEAP));

SqlFieldsQuery sql = new SqlFieldsQuery(sqlStr).setLocal(false);

FieldsQueryCursor> result = cache.query(sql);
printResults(result);

17/11/27 19:34:18 INFO IgnLoader: Starting SQL query : SELECT COUNT(*) FROM
TEST.DATASET1
17/11/27 19:34:23 INFO IgnLoader: ALL Cache size is 5000
17/11/27 19:34:23 INFO IgnLoader: PRIMARY Cache size is 5000
17/11/27 19:34:23 INFO IgnLoader: NEAR Cache size is 0
17/11/27 19:34:23 INFO IgnLoader: OFFHEAP Cache size is 5000
17/11/27 19:34:23 INFO IgnLoader: ONHEAP Cache size is 5120
17/11/27 19:34:45 INFO IgnLoader: Result size : 1

row 0:[COUNT(*)(Long) :*9859833*; ]

17/11/27 19:34:45 INFO IgnLoader: Done

My function printResults reports only 9,859,833 records even if I use
setLocal(false)

What should I do to run Query against full data set?

Thank you for help.
Alexey






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