How many records in (1) memmory? (2) disk?

[joseheitor]

How can I check if all the data in a cache is in memory? Or only a portion of
the data?



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

How to configure primary data in memory and backup data on disk?

[joseheitor]

How do I configure Primary data in RAM and backup copies to disk?

The goal is to store backup copies on disk, leaving max RAM available for
primary partitions only.



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

Re: Ignite ML - Weka integration?

[joseheitor]

Hi,

You also raise some interesting points...

Weka has integrations to Hadoop and Spark via 'plugins' from their package
manager. And Weka's 'sister-project', MOA is specifically intended to
operate on large data streams. These products are extensive, open-source and
extendible.

Regarding Python Notebooks - we can work with Weka's library in Python or
even Java in Notebooks (
https://waikato.github.io/weka-wiki/jupyter_notebooks/
  ). I have played a
little with this and it works great.

I also wondered whether a workbench for Ignite ML would maybe be better
integrated into the Ignite Web Console. But looking at the extent of work
required to provide the kind of functionality and visualization already
available in Weka - one wonders if it isn't more rational to work on
integration between the projects? Weka, being a Java application is of
course also capable of running on any platform that Java supports, so it has
great portability.

I think that the work that the Ignite team are doing on ML is based on the
excellent concept of DML (distributed machine learning), which is extremely
important for future scalability. And it seems to have good momentum as seen
by the number of features being added to the new, upcoming 2.8 release. All
the development so far has understandably been focused on the underlying ML
'infrastructure'. The challenge though is that in order to stimulate
adoption, we need practical interactive environments.

Really looking forward to the evolution of ML on Ignite. And Kudos to the
talented team behind it.

Cheers,
Jose



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

Ignite ML - Weka integration?

[joseheitor]

Hi Ignite-ML Team,

I have recently discovered WEKA - a Java ML workbench and library from the
University of Waikato.

I have observed that Ignite-ML is developing quickly in it's computational
capabilities, but is lacking a 'workbench environment' for interactive
data-science workflows. Surprisingly, I discovered an online Paper (from
2016:  here
  ),
which describes Apache Ignite's early ML implementation, to be rooted in the
WEKA ML library.

My question is thus: Is there any possibility in exploring an integration
between Ignite-ML and WEKA's workbench? (It would be great to leverage
WEKA's interactive exploratory, visualization and workflow capabilities with
Ignite-ML's distributed computational capabilities...)

Or is this concept completely irrational and impractical?

Looking forward to feedback from the talented Ignite-ML gurus!

Thanks,
Jose



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

Re: How to choose Leader manually?

[joseheitor]

Hi there,

Firstly let me clarify that I am just an Ignite user - not an Ignite team
member/expert...so it will be great to get the views of the experts in this
discussion...

Now, in Ignite, all data gets 'stored' in a 'CACHE'. If you want your data
to be persisted, you simply configure your designated Cache to have
'Persistance' set to enabled.

Also there are various ways in which you can distribute your data among
different nodes on a cluster. But for your basic use-case, you would
probably set the 'Mode' configuration of your cache to 'REPLICATED'. This
will ensure that all data arriving at any of your (2) nodes will be 'synced'
to the other node.

In an environment where your connection between datacenters is very good,
like two AWS Availability Zones, the above scenario will probably give you
satisfactory results. But if your inter-datacenter link is flaky as you
mentioned, the above approach would not be recommended. For this scenario,
you would probably be better off looking into GridGain's commercial
(enhanced) version of Apache Ignite, which offers a specialised mechanism
for Datacenter-Replication (over sub-optimal links).

Hope this helps,
Jose



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

Re: How to choose Leader manually?

[joseheitor]

Hi,

As I understand it - if you are connecting via the JDBC Thin Client, in the
connection-string, you would list only the endpoints of the servers in the
'Main Purpose' datacenter.

To failover to servers in the other datacenter however, you would have to
change your connection string and re-establish the connection(s).

Another option to consider if your backups can not tolerate data
inconsistency is to configure the cache SYNC mode to a strict, synchronous
policy type, but this will reduce write-performance. In this instance,
however, it will be safe to establish a connection to any of the nodes, as
the data is guaranteed to be up to date. This would provide automatic
failover.

@Ignite Team - please correct me if my understanding is flawed...(?)

Jose



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

Machine Learning questions

[joseheitor]

Hi Guys,

A few questions as I progress through my ML learning journey with Ignite...

- I assume that I would start by extracting features from my JSON records in
a cache into a vectorizer - how does this impact memory usage? Will origin
cache records be moved to disk, as more memory is required than is available
for the data in the vectorizer? Or will the vectorizer data begin to use
swap? Or will I get OOM exceptions?

- Are there any built-in algorithms or recommended strategies for sampling?

- Are there any dataset statistical functions like those provided by
Python's ML libraries, for high-level evaluation of specific features in a
dataset (to assess things like missing-data, cardinality, min-max, mean,
mode, standard-deviation, percentiles, etc)?

- Is there any doc/video tutorial that would provide a guide for the
complete workflow pipeline for an ML example (encompassing the
abovementioned operations)?

Thanks,
Jose



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

Re: Ignite AI learning resources

[joseheitor]

Awesome - Thanks (have just started looking through the tutorial examples)

Will look through the links to resources too. Being new to AI, it is
difficult to know where to start, but am committed to Ignite for other
reasons, so would like to learn by using the Ignite ML features...

Looking forward to 2.8 and the new docs too.



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

Ignite AI learning resources

[joseheitor]

Hi,

Can anyone recommend some resources to learn the fundamentals of ML and DL,
and how to use these techniques in practical ways with the Apache Ignite AI
platform?

Thanks,
Jose



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

Re: Ignite 2.7.5

[joseheitor]

Hi Dimitry,

Will version 2.7.5 be based on the current (2.8.0) nightly code-base?

Jose



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

Re: H2 SQL query optimiser strategy in Ignite

[joseheitor]

Hi Ignite Team,

Have the system architects explored the option of maintaining table
statistics on each node (as Postgres and other legacy SQL engines do), and
then distributing the raw SQL query to each node and letting each node
execute the query planner locally, optimising the query based on the
statistics on hand for the given node...?

Would this not optimise overall performance of the query, and eliminate the
need for developers and DBAs to have try to guess the optimum JOIN order?
(which may in fact vary on each node...?)

Thanks,
Jose



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

Segmentation Plugin blog or article?

[joseheitor]

Is there a blog or article that describes the use-cases and usage of the
Segmentation Plugin:
https://github.com/luqmanahmad/ignite-plugins
  

Also, does the community have any step-by-step recommendations for managing
and recovering from split-brain conditions without the loss of data?



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

Production network and backup requirements

[joseheitor]

Hi Ignite Team,

- what is the recommended minimum network connection bandwidth between nodes
(100M? 1G? >1G?)

- what is the recommended minimum network latency between nodes (<1ms?
<10ms? <100ms?)

- can one use the same 'affinityBackupFilter' strategy that is recommended
for distributing backup copies to nodes on separate racks, for distributing
backups to nodes in different datacentres (instead of different racks)? In
other words, can we have a cluster that is distributed between multiple
datacentres?

Thanks,
Jose



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

Re: Free Network Segmentation (aka split brain problem) plugin available

[joseheitor]

Hi Luqman,

Does your plugin just detect a segmentation occurrence? Or does it also
resolve the split-brain condition?

Thanks,
Jose



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

Re: H2 SQL query optimiser strategy in Ignite

[joseheitor]

Thanks, Ilya.

The reason that I am trying to better understand the rationale is that I had
a multi-join query that was performing badly on Ignite compared to
PostgreSQL.

When comparing the query plans from the two systems, I discovered that the
Postgres query-planner had re-ordered the joins in my query, but H2 (Ignite)
had not (and does not assign a 'cost').

When I explicitly re-ordered the joins in my query, to resemble the result
of the Postgres plan - the performance on Ignite shot up to match that of
Postgres.

So my question is, whether there is any way to overcome this shortcoming ...
for situations where we cannot evaluate the query layout manually, on an
individual basis?

Thanks,
Jose



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

H2 SQL query optimiser strategy in Ignite

[joseheitor]

Hi,

H2 uses a cost-based query optimisation strategy for the query-planner. But
in the case of Ignite, I suspect that this cannot be leveraged because the
client node does not have access to data statistics (distribution, size,
etc) throughout the various client nodes...

So how is the query plan devised?

Thanks,
Jose



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

Re: Populating tables via IgniteDataStreamer

[joseheitor]

Hi Clay,

With JDBC, I have used the following successfully:

  dbConnection = HikariCPDatasource.getConnection();
  dbConnection.setSchema("public");

  *dbConnection.createStatement().execute("ALTER TABLE tablename
NOLOGGING");*
  *dbConnection.createStatement().execute("SET STREAMING ON");*

  for(...) { ** execute insert statements ** }

  *dbConnection.createStatement().execute("SET STREAMING OFF");*
  *dbConnection.createStatement().execute("ALTER TABLE tablename LOGGING");*

  dbConnection.close();

Hope this helps,
Jose



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

Re: SQL table - calculating memory size?

[joseheitor]

Please help?



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

Capacity planning spreadsheet (RAM)

[joseheitor]

Hi,

The capacity planning spreadsheet available for download from the Ignite
website appears to be a very useful tool - Thanks.

One question:

The RAM value in the /Server Capacity (Adjusted)/ section seems to take the
individual server RAM and subtract 12G (=E6-4-8).

This appears to imply that each server node must have more than 12G RAM as a
*MINIMUM*!

Can someone explain this? Or am I misusing or misinterpreting the
template...?

Thanks,
Jose



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

SQL table - calculating memory size?

[joseheitor]

Hi,

The capacity-planning docs refer to object sizes in a single cache... how do
I calculate the data size in one or more SQL tables, with multiple columns?

Do I estimate and sum the data size in each column multiplied by the maximum
estimated number of records? And sum these results for all tables?

Or is there a different method?

Thanks,
Jose



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

[SOLVED] configure location of 'work' folder?

[joseheitor]

Found the answer in this thread:

http://apache-ignite-users.70518.x6.nabble.com/Apache-Ignite-quot-work-quot-folder-for-clients-td21380.html

  



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

configure location of 'work' folder?

[joseheitor]

Hi,

Can we configure the location of the 'work' folder?

I would like to run Ignite in a Docker container, but set the location of
the 'work' folder to a docker mount, which leaves the work folder accessible
from outside the container...

Thanks,
Jose



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

[SOLVED] Re: Which IgniteCompute function?

[joseheitor]

Got it working - precisely as you suggest.

Many thanks, Max.



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

Re: [BUG REPORT] JDBC Create Index

[joseheitor]

Thanks, Ilya.

1. - I see that this issue has been addressed - should I be able to verify
this by pulling the latest nightly-build?

2. - Issuing the following statement via a JDBC application or with a JDBC
client, such as DBeaver, results in the index being created, but only on the
2nd and 3rd specified columns (only tested with JDBC Thin Client):

Assuming a table with the following structure:
CREATE TABLE public.transactions (pk INT, id INT, k VARCHAR, v VARCHAR,
PRIMARY KEY (pk, id))
  WITH "TEMPLATE=PARTITIONED, BACKUPS=1, ATOMICITY=TRANSACTIONAL_SNAPSHOT,
WRITE_SYNCHRONIZATION_MODE=FULL_SYNC, AFFINITY_KEY=id";


*CREATE INDEX transactions_id_k_v ON public.transactions (id, k, v)
INLINE_SIZE 100;*

 

3. - [Apologies] Please ignore - it appears to be working correctly!



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

[BUG REPORT] JDBC Create Index

[joseheitor]

Hi Ignite Team,

Here are three issues that I have picked up on a 2.8 nightly build (don't
know if these have been reported and logged previously...?):

Version: 2.8
Build: 20181226

*Issue 1:*

CREATE INDEX statements cannot be part of an SQL multi-statement. But they
can be executed individually.

*Issue 2:*

CREATE INDEX ignores first column of a multi-column index. For example...

CREATE INDEX multi_index ON public.table (id, k, v) INLINE_SIZE 100;

results in an index for columns 'k' and 'v' only.

*Issue 3:*

DROP INDEX IF EXISTS... fails
DROP INDEX... works ok



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

Re: [RESOLVED] when JDBC query result larger than heap...?

[joseheitor]

Found the solution... 'Query Lazy Loading'

( https://apacheignite-sql.readme.io/docs/performance-and-debugging
  )



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

when JDBC query result larger than heap...?

[joseheitor]

How can I process a large resultset from a JDBC query, that requires more
memory than is available for heap space?

I have tried 'paging' with SQL 'OFFSET' and 'FETCH FIRST/NEXT ROW(S) ONLY' -
but still run out of memory ...regardless of the 'paging' size.



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

SQL FETCH examples?

[joseheitor]

Hi Ignite Team,

Do you have any examples illustrating the use of SQL:

FETCH FIRST/NEXT ROW(S) ONLY

???

Thanks,
Jose



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

2.8 release date?

[joseheitor]

Hi Ignite Team,

When is version 2.8 expected to be released?

Jose



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

Re: Which IgniteCompute function?

[joseheitor]

Thanks, Karun.

>From my interpretation of the Javadocs, I also presumed that
'broadcastAsync' would probably be the most appropriate...

Do you perhaps know where I might find a related Java-7 code snippet as an
example usage of:

 IgniteFuture> broadcastAsync(IgniteClosure
job, T arg)

Jose



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

Which IgniteCompute function?

[joseheitor]

Which IgniteCompute function would be best suited to this task?...

Cluster has SQL table configured in partitioned cache (records distributed
over several nodes).

On all nodes, I would like to asynchronously execute logic which...

- SQL SELECT query for matching records on each node (retrieving only
records from primary, not backup cache partition)

- process and aggregate data from each record of each local node's
recordset, and

- return the resulting object(s) to the application



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

Which IgniteCompute function?

[joseheitor]

Which IgniteCompute function would be best suited to this task?...

Cluster has SQL table configured in partitioned cache (records distributed
over several nodes).

On all nodes, I would like to asynchronously execute logic which...

- SQL SELECT query for matching records on each node (retrieving only
records from primary, not backup cache partition)

- process and aggregate data from each record of each local node's
recordset, and

- return the resulting object(s) to the application 



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

Re: CAP Theorem (CP? or AP?)

[joseheitor]

Guys, thank you both for your informative and helpful responses.

I have explicitly configured the cache-template with the additional
property:



And have observed the following behaviour:

1. [OK] Attempting to get a specific record(s) which resides in a lost
partition does indeed return an exception, as expected.

2. [?] Doing a SELECT COUNT(*) however, still succeeds without error, but
obviously reports the wrong number of total records (understandably). But
shouldn't any operation against a cache with lost partitions result in an
Exception? How will my application know that the result is valid and can be
trusted to be accurate?

Another question please - Stanislav, what is the issue with Ignite
persistence, that was fixed in 2.7 for in-memory, but will only be fixed for
Ignite native persistence in version 2.8...?

Thanks,
Jose



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

CAP Theorem (CP? or AP?)

[joseheitor]

In this GridGain presentation:

https://www.youtube.com/watch?v=u8BFLDfOdy8&t=1806s
  

Valentin Kulichenko explains the CAP theorem and states that Apache Ignite
is designed to favour Strong-Consistency (CP) over High-Availability (AP).

However, in my test case, my system appears to be behaving as an AP system.
Here is my setup:

4 partitioned nodes in 2 availability-zones [AZa-1, AZa-2] [AZb-3, AZb-4],
configured as described in this post:

http://apache-ignite-users.70518.x6.nabble.com/RESOLVED-Cluster-High-Availability-tp25740.html

  

With 7,000 records loaded into a table in the cluster with JDBC Thin client:

1. [OK] I can connect to any node and verify that there are 7,000 records
with a SELECT COUNT(*)

2. [OK] If I kill all nodes in AZ-a [AZa-1, AZa-2], and connect to one of
the remaining online nodes in AZ-b, I can still verify that there are 7,000
records with a SELECT COUNT(*)

3. [?] I then kill one of the remaining two nodes in AZ-b and connect to the
single remaining node. Now a SELECT COUNT(*) returns a value of 3,444
records.

This seems to illustrate that the partitioning and backup configuration is
working as intended. But if Ignite is strongly-consistent (CP), shouldn't
the final query fail rather than return an inaccurate result (AP)?

Or am I missing some crucial configuration element(s)?



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

[RESOLVED] Cluster High-Availability

[joseheitor]

Okay - I found the answer to my Exception problem in the following post:

http://apache-ignite-users.70518.x6.nabble.com/Default-Cache-template-tp19494p19497.html

  



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

Re: Cluster High-Availability

[joseheitor]

Denis,

This is exactly what I am looking for - Thanks.

I am getting an error though, when attempting to create a table with my
defined cache-template. Please check my config below and kindly let me know
what I am missing...?

NODE CONFIG:

...














AVAILABILITY_ZONE









...

CREATE TABLE statement:

CREATE TABLE transactions (pk INT, id INT, k VARCHAR, v VARCHAR, PRIMARY KEY
(pk, id))
WITH "TEMPLATE=MULTI-AZ-PARTITIONED, AFFINITY_KEY=id"

Exception:

java.sql.SQLException: Cache doesn't exist: MULTI-AZ-PARTITIONED
at
org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:750)
at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:212)
at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:475)
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95)
at
com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
at
com.example.ignite.IgniteApplication.initSQLDatabase(IgniteApplication.java:75)
at com.example.ignite.IgniteApplication.main(IgniteApplication.java:41)



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

Re: Optimizing Collocated Join

[joseheitor]

Try this:

SELECT 
subQuery.coveringId, 
COUNT(*)
FROM (
SELECT 
innerS2.*
FROM 
S2CellCovering AS innerS2
INNER JOIN 
EventTheta AS innerEvent
ON innerEvent.parentS2CellId = innerS2.parentS2CellId
AND innerEvent.s2CellId BETWEEN innerS2.minS2CellId AND
innerS2.maxS2CellId
AND innerEvent.eventDate BETWEEN '2018-10-02' AND '2018-10-02'
AND innerEvent.eventHour BETWEEN -1 AND -1
) AS subQuery
WHERE 
subQuery.coveringId = 166
GROUP BY 
subQuery.coveringId;




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

Re: Cluster High-Availability

[joseheitor]

Thanks for the response, Denis.

I am afraid that I don't really understand how to leverage the feature you
indicated, purely from the Javadocs...

Can you provide a simple set of node-configs, that would illustrate and
accomplish what I am trying to achieve?

Thanks,
Jose



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

Cluster High-Availability

[joseheitor]

Hi,

For the event that an entire datacentre is lost, is there a way to either:

1 - Replicate an entire cluster to a remote datacentre... (with equivalent
nodes)?


 

2 - Or to manage the backup targets in a specifically defined way, that
ensures that there is a full set of data (between Primary and Backup caches)
within a single datacentre...?

 

Thanks,
Jose





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

[RESOLVED] SQL WHERE Query Slow

[joseheitor]

Found  the reason for the problem - and a solution...

Problem is that the query-optimiser performs the final WHERE clause *first*,
which results in a full table scan.

The solution is to structure the query so that the original query (without
the WHERE clause) is forced to be performed first, then finally the WHERE
clause can be run on a small set of data. This can be achieved by setting
the whole original query (without the WHERE clause) as a 'Nested
Sub-query'...

SELECT subQuery.*
FROM (
*SELECT
mainTable.pk, mainTable.id, mainTable.k, mainTable.v
FROM
public.transactions AS mainTable
INNER JOIN
  (SELECT id FROM public.transactions WHERE k = 'trans.cust.last_name'
AND v = 'Smythe-Veall') AS lastName ON lastName.id = mainTable.id
INNER JOIN
  (SELECT id FROM public.transactions WHERE k = 'trans.date' AND v =
'2017-12-21') AS transDate ON transDate.id = mainTable.id
INNER JOIN
  (SELECT id FROM public.transactions WHERE k = 'trans.amount' AND
cast(v AS integer) > 9) AS transAmount ON transAmount.id = mainTable.id*
) AS subQuery
WHERE subQuery.k = 'trans.cust.first_name'
ORDER BY 1



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

SQL Index Payload size?

[joseheitor]

Ho do I find the payload size of an index?



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

Re: IGNITE_MAX_INDEX_PAYLOAD_SIZE

[joseheitor]

Thanks, Ilya.

How can find the payload size of an existing index?

Jose



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

Re: [RESOLVED] JDBC Streaming

[joseheitor]

Awesome - it worked!

Thanks, Ilya.



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

Re: [RESOLVED] JDBC Streaming

[joseheitor]

Thanks Ilya,

Will try your suggestions...

Where in the XML config do I set the 'failure detection timeout'?

Thanks,
Jose



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

Re: [RESOLVED] JDBC Streaming

[joseheitor]

Hi Ilya,

Your recommendation works - thanks.

When loading a few million records, I always seem to experience a node
failure on one of my two nodes.

I have attached the log from a recent run on version 2.7.0, failure occurred
after about 10 minutes...

Please let me know if you see anything I must address to prevent this?

Thanks,
Jose

ignite-66c8e66a.gz
  



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

Re: IGNITE_MAX_INDEX_PAYLOAD_SIZE

[joseheitor]

I have found that issuing the CREATE INDEX commands separately (not as part
of a multiple-statement command) works fine.




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

Re: IGNITE_MAX_INDEX_PAYLOAD_SIZE

[joseheitor]

Thanks, Ilya,

Isn't the IGNITE_MAX_INDEX_PAYLOAD_SIZE setting expecting a number
(byte-count)?

The SQL command(s) I run is as follows:

String cmd = "DROP TABLE IF EXISTS public.transactions;" +
 "DROP INDEX IF EXISTS transactions_id_k_v;" +
 "DROP INDEX IF EXISTS transactions_k_v_id;" +
 "DROP INDEX IF EXISTS transactions_pk_id;" +
 "CREATE TABLE public.transactions (pk INT, id INT, k
VARCHAR, v VARCHAR, PRIMARY KEY (pk, id))" +
 " WITH \"TEMPLATE=REPLICATED, BACKUPS=0,
ATOMICITY=TRANSACTIONAL_SNAPSHOT, WRITE_SYNCHRONIZATION_MODE=FULL_SYNC,
AFFINITY_KEY=id\";" +
 "CREATE INDEX transactions_id_k_v ON
public.transactions (id, k, v) INLINE_SIZE 150;" +
 "CREATE INDEX transactions_k_v_id ON
public.transactions (k, v, id) INLINE_SIZE 150;" +
 "CREATE INDEX transactions_pk_id ON public.transactions
(pk, id) INLINE_SIZE 20;";

-->

The error I get is:

java.sql.SQLException: Failed to parse query. Syntax error in SQL statement
"CREATE INDEX TRANSACTIONS_ID_K_V ON PUBLIC.TRANSACTIONS (ID, K, V)
INLINE_SIZE[*] 150;CREATE INDEX TRANSACTIONS_K_V_ID ON PUBLIC.TRANSACTIONS
(K, V, ID) INLINE_SIZE 150;CREATE INDEX TRANSACTIONS_PK_ID ON
PUBLIC.TRANSACTIONS (PK, ID) INLINE_SIZE 20; "; SQL statement:
CREATE INDEX transactions_id_k_v ON public.transactions (id, k, v)
INLINE_SIZE 150;CREATE INDEX transactions_k_v_id ON public.transactions (k,
v, id) INLINE_SIZE 150;CREATE INDEX transactions_pk_id ON
public.transactions (pk, id) INLINE_SIZE 20; [42000-197]
at
org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:750)
at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:212)
at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:475)
at
com.example.ignite.IgniteApplication.initSQLDatabase(IgniteApplication.java:91)
at com.example.ignite.IgniteApplication.main(IgniteApplication.java:52)

--->

What am I doing wrong?

Jose



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

Re: SQL Query plan confusion

[joseheitor]

My apologies, Vladimir.

The query-plans are swapped around (oops!).

The query-plan for the efficient query is in the 'bad' file. And
vice-versa...

Thanks,
Jose



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

IGNITE_MAX_INDEX_PAYLOAD_SIZE

[joseheitor]

Hi,

How do we set the global default IGNITE_MAX_INDEX_PAYLOAD_SIZE?

(Note: Adding INLINE_SIZE xx to a CREATE INDEX command, as per the docs is
throwing an SqlException!)

Thanks,
Jose



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

[RESOLVED] Re: Where binary downloads for 2.7.0

[joseheitor]

Seems the link should exclude the '-fabric' portion of the URL:

[BAD]: ...apache-ignite-fabric-2.7.0-bin.zip

[GOOD]: ...apache-ignite-2.7.0-bin.zip



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

Re: Free Network Segmentation (aka split brain problem) plugin available

[joseheitor]

Hi Luqman,

Is your Segmentation plugin compatible with Ignite version 2.7.0...?

Thanks,
Jose



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

Where binary downloads for 2.7.0

[joseheitor]

Where can I download the binaries for version 2.7.0 (the links from the usual
Downloads pge seem to be broken...)?



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

[RESOLVED] JDBC Streaming

[joseheitor]

Hooray!!! - It works.

Thanks, Ilya.

Please continue your investigation of the JDBC Client Driver (thick-client),
and let me know what you find...?

What follows should perhaps be posted separately...but here's something I
noticed, which I don't fully understand or know how to deal with:

While bulk-loading data previously without streaming, via the THICK Client,
I had a node go down. Data continued to load though, as my other node
remained active. When I noticed this, I simply started up the failed node
again. Data-loading paused for a while (presumably while the nodes
synced???), and then continued until completed.

Now, while bulk-loading data with streaming, on the THIN Client, I also had
a node go down. Data did not however continue to load. The application did
not see a dropped connection - it simply froze. When I noticed this, I
started up the failed node again, as before. Data-loading did not however
resume loading as before. (I had to kill the application and restart it...).

Should Ignite not throw an exception, to alert the application that the
database connection dropped? Or is there support for a connection timeout
for an SQL execution statement? Or (even better) should the driver not have
automatically detected the connection loss and re-established a new
connection to the other node (as the THICK client presumably does...)? and
continued.

Thanks,
Jose



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

Re: JDBC Streaming

[joseheitor]

Hi Ilya,

Thanks for the response.

My understanding was that Thin JDBC driver was only able to connect to a
single node (not a cluster), so that if that node failed - it was not able
to continue operating on the cluster... It would also only return data
residing on that node (not records residing on other cluster nodes)???

But I see that the docs now mention failover support with a list of hosts
and aggregation of data from multiple nodes on the 'connected' node (was
this added recently?).

I will apply the necessary changes and test your suggestion.

Thanks,
Jose



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

Re: JDBC Streaming

[joseheitor]

Hi Ilya,

Any update on your investigation of this issue...?

Your comments that 'streaming mode' in Client driver and Client driver
itself are near-deprecated - are very surprising and concerning!

1. Are you saying that Apache Ignite SQL will seize to be accessible via
standard JDBC?

2. If 'streaming mode' is to be deprecated - will there be an alternative
method of inserting high-throughput data via SQL?

Thanks,
Jose



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

Re: JDBC Streaming

[joseheitor]

Hi Ilya,

Please see attached archive with simple application demonstrating the
problem.

ignite-streaming.gz
 
 

Once you have extracted the contents of the archive - please refer to the
README for instructions... (and source)

Thanks,
Jose





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

Re: JDBC Streaming

[joseheitor]

Hi Ilya,

Yes - I am using JDBC Client driver to INSERT data into the SQL table. It
works correctly (but slow) without setting 'streaming=true'.

When I set 'streaming=true' in the connection string, as per the Ignite docs
(
https://apacheignite-sql.readme.io/docs/jdbc-client-driver#section-streaming-mode

 
), then my data-insert code runs much faster, and without errors - but the
table remains empty.

I have followed the instructions on the docs carefully and reviewed several
times over the last couple of days.

There is either something else that needs to be done and is undocumented -
or there is a bug in this feature.

Please can you verify this functionality on your end and confirm...?

Thanks,
Jose



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

Misleading docs or bug?

[joseheitor]

JDBC Client connection URL with 'streaming=true' runs (much faster than
without) but no data is inserted into SQL table. No errors are reported. 



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

control.sh from remote terminal?

[joseheitor]

Can control.sh connect to a remote cluster?



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

Re: JDBC Streaming

[joseheitor]

Can anyone help with this?



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

Re: SQL WHERE Query Slow

[joseheitor]

Can anyone help with this?



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

SQL Query plan confusion

[joseheitor]

1. - I have a nested join query on a table of 8,000,000 records which
performs similar or better than PostreSQL (~10ms) on my small test setup (2x
nodes, 8GB, 2CPU):

SELECT
mainTable.pk, mainTable.id, mainTable.k, mainTable.v
FROM
public.test_data AS mainTable
INNER JOIN (
SELECT
lastName.id
FROM
(SELECT id FROM public.test_data WHERE k = 
'trans.cust.last_name' AND v
= 'Smythe-Veall') AS lastName
INNER JOIN
(SELECT id FROM 
public.test_data WHERE k = 'trans.date' AND v =
'2017-12-21') AS transDate ON transDate.id = lastName.id
INNER JOIN
(SELECT id FROM 
public.test_data WHERE k = 'trans.amount' AND cast(v
AS integer) > 9) AS transAmount ON transAmount.id = lastName.id
) AS subTable ON mainTable.id = subTable.id 
ORDER BY 1, 2


2. - By simply adding a WHERE clause at the end, the performance becomes
catastrophic on Ignite (~10s for subsequent queries - first query takes many
minutes). On PostgreSQL performance does not change...

SELECT
mainTable.pk, mainTable.id, mainTable.k, mainTable.v
FROM
public.test_data AS mainTable
INNER JOIN (
SELECT
lastName.id
FROM
(SELECT id FROM public.test_data WHERE k = 
'trans.cust.last_name' AND v
= 'Smythe-Veall') AS lastName
INNER JOIN
(SELECT id FROM 
public.test_data WHERE k = 'trans.date' AND v =
'2017-12-21') AS transDate ON transDate.id = lastName.id
INNER JOIN
(SELECT id FROM 
public.test_data WHERE k = 'trans.amount' AND cast(v
AS integer) > 9) AS transAmount ON transAmount.id = lastName.id
) AS subTable ON mainTable.id = subTable.id 
*WHERE
mainTable.k = 'trans.cust.first_name'*
ORDER BY 1, 2

What can I do to optimise this query for Ignite???

(Table structure and query plans attached for reference)

Thanks,
Jose
table.sql
  
good-join-query.txt
 
 
bad-join-query.txt
  



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

Re: JDBC Streaming

[joseheitor]

Shouldn't I be able to do this through plain JDBC DML operations? The
documentation for the JDBC Client Driver specifies this as a feature...

Don't know whether I am doing something wrong, or if there is a bug?

Is there any example showcasing this feature?

Thanks,
Jose



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

Re: Optimum storage startegy (Advice?)

[joseheitor]

Thanks for the reply and questions, Evgenii. Here are some additional details
(long-winded...sorry), to provide you with some context of the usage.

The reasons for not using a Flat table structure are as follows:

1. - A real-world retail transaction has a much larger and complex structure
than my simple example test-case. A single transaction can have many simple
and complex nested elements with varying structures that house one or more
records for terminal, store,item-sales, item-returns, discounts, rewards,
tenders, taxes, customer, salesperson, etc., etc...

2. - The transaction structure can be (and generally is) extended over time,
without impacting the data store or the code.

3. - If we can attain reasonable performance with this 'meta' key-value type
storage structure, then we can tailor the amount and selection of individual
elements that need to be queried, for each customer's requirement based on
their need and appetite for big data analytics. This could be achieved
purely through configuration (list of keys used in queries), without
requiring custom development. For example:

A) Customer with basic financial reporting needs: we simply store a few
records as follows...

pk=1  id=2195  k='trans.body'  v='{[full JSON transaction]}'
pk=2  id=2195  k='trans.date'  v='2018-12-01'

B) Customer with larger appetite for complex reporting and analytics: we
would then store several records for a number of matching keys required for
querying as follows...

pk=1  id=2195  k='trans.body'  v='{[full JSON transaction]}'
pk=2  id=2195  k='trans.date'  v='2018-12-01'
pk=3  id=2195  k='trans.item.sale[].sku'  v='6005120257329'
pk=4  id=2195  k='trans.item.sale[].sku'  v='2460058410533'
pk=5  id=2195  k='trans.cust.lastname'  v='Smith'
pk=6  id=2195  k='trans.tender.type.card.amount'  v='2595'
pk=7  id=2195  k='trans.amount'  v='2595'
pk=8  id=2195  k='trans.tax.type0.amount'  v='189'
pk=9  id=2195  k='trans.tax.type1.amount'  v='0'
pk=10  id=2195  k='trans.store.id'  v='109574'
... (etc)

The table structure (with all indexes) would be as follows:

CREATE TABLE public.transactions (
  pk UUID, id UUID, k VARCHAR, v VARCHAR, PRIMARY KEY (pk, id))
  WITH "TEMPLATE=PARTITIONED, BACKUPS=1, ATOMICITY=TRANSACTIONAL,
WRITE_SYNCHRONIZATION_MODE=FULL_SYNC, AFFINITY_KEY=id";

CREATE INDEX test_data_id_k_v ON public.test_data (id, k, v);
CREATE INDEX test_data_k_id_v ON public.test_data (k, id, v);
CREATE INDEX test_data_k_v_id ON public.test_data (k, v, id);
 
CREATE INDEX test_data_id ON public.test_data (id);
CREATE INDEX test_data_k ON public.test_data (k);
CREATE INDEX test_data_v ON public.test_data (v);
CREATE INDEX test_data_pk ON public.test_data (pk);

Inserting data through plain JDBC INSERT operations is slow and worrying.
Although the docs for the JDBC Client Driver specify that setting
'streaming=true' in the connection string will enable streaming - I have
found that although no errors occur, the table remains empty. (Don't know
whether I am doing something wrong, or if it is a bug?). Do you know of any
examples for doing this?

Many thanks,
Jose



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

Re: JDBC Streaming

[joseheitor]

Hi Ilya,

I realize that Streaming is not supported via the JDBC Client node interface
(*thanks for that info). But is it possible to stream-import data via (code)
SQL directly and load it into persistent SQL table?

(The Ignite code examples for Streaming only show loading data into grid
caches...not SQL tables)

Thanks



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

Re: JDBC Streaming

[joseheitor]

Is Streaming perhaps not supported with JDBC Client driver...?



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

Optimum storage startegy (Advice?)

[joseheitor]

Given the following model structure:

{
  "trans": {
"cust": {
  "firstname": "Bone",
  "lastname": "Klebes",
  "email": "[hidden email]",
  "gender": "Male"
},
"ipaddress": "104.89.149.184",
"date": "2017-12-01",
"amount": 1217,
"currency": "NOK"
  }
}

What is the recommended storage strategy for optimum querying? 
(example: date='2018-12-01' and lastname='Klebes' and amount > 1000)

I have tried the following strategies on a partitioned, two-node cluster
having 1 000 000 documents, with the following results:

1. - DataGrid: 
  One document per record
  Using IgniteBiPredicate in ScanQuery
  Time taken: ~1.5 sec
  Inserts are quite fast (1 000 000 documents = 1 000 000 records = 35
min)

2. - SQL:
  Table structure - [id(PK)], [doc_id], [key], [val] (all columns
indexed)
  One record for each field (8 records per document)
  Using nested JDBC JOIN query
  Time taken: ~20ms (PostgreSQL comparison = ~10ms)
  Inserts are very slow (1 000 000 documents = 8 000 000 records = 10
hours)

Is there another strategy that I should consider? Or any techniques that I
can use to optimise the queries?

Thanks.



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

Are QuerySqlField indexes used in ScanQueries?

[joseheitor]

If model class fields are annotated with:

@QuerySqlField(index = true)

... will ScanQueries with IgniteBiPredicates on the annotated fields
leverage the indexes?



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

BinaryObject nested fields syntax?

[joseheitor]

Given the following model data structure for a given document record:

{
  "trans": {
"cust": {
  "firstname": "Bone",
  "lastname": "Klebes",
  "email": "bkleb...@usgs.gov",
  "gender": "Male"
},
"ipaddress": "104.89.149.184",
"date": "2017-12-01",
"amount": 1217,
"currency": "NOK"
  }
}

...modelled in Java by a Transaction class and a Customer class,

And the following code to perform a ScanQuery:

String date = "2017-12-01";
int amount = 1000;
String lastname = "Klebes";

IgniteCache cache =
database.getCache().withKeepBinary();
ScanQuery filter = new ScanQuery<>(
  new IgniteBiPredicate() {
@Override
public boolean apply(Integer key, BinaryObject trans)
{
  if (!trans.field("date").equals(date))
return false;
  if (trans.field("amount") <= amount)
return false;
  *(???) if
(!trans.field("customer.lastname").equals(lastname))*
return false;
  return true;
}
  }
);
List result = cache.query(filter).getAll();

What is the correct syntax for accessing the nested 'Customer.lastname'
field?



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

Re: IgniteBiPredicate anonymous class - ClassNotFoundException

[joseheitor]

Thanks, Maxim - it is working.

Tips for others landing on this post... I fixed my problem by:

- Enabling peer classloading () on each node
- Building a JAR with my model classes and manually deploying them on each
server node's /libs folder



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

Re: [RESOLVED] Passing parameters to IgniteBiPredicate

[joseheitor]

Please ignore my previous post - it is working correctly:

String date = "2018-10-21";
ScanQuery filter = new ScanQuery<>(
new IgniteBiPredicate() {
@Override
public boolean apply(Integer key, Transaction trans) {
  return trans.getDate().equals(date);
}
}
);
List result = database.getCache().query(filter).getAll();




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

Re: Passing parameters to IgniteBiPredicate

[joseheitor]

Hi have the same question... did you ever find a solution?



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

IgniteBiPredicate anonymous class - ClassNotFoundException

[joseheitor]

Why am I getting a ClassNotFoundException? ... I can see that the anonymous
class exists inside the JAR archive:


 



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

Re: JDBC Streaming

[joseheitor]

Can anyone spot if I am doing something wrong...?



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

SQL Affinity colocation

[joseheitor]

I have 2 tables with the following primary/foreign key mapping
(transactions.id <-> records.trans_id):

*CREATE TABLE public.transactions (
  id INT PRIMARY KEY, 
  basket VARCHAR
)
WITH "TEMPLATE=PARTITIONED";

CREATE TABLE public.records (
  id INT, 
  trans_id INT,
  last_name VARCHAR,
  email VARCHAR,
  trans_date VARCHAR,
  amount INT,
  PRIMARY KEY (id, trans_id)
)
WITH "TEMPLATE=PARTITIONED, AFFINITY_KEY=trans_id";*

Notes:
- the transactions.basket column contains a full JSON document representing
a transaction
- the records table contains records of selected fields from the
corresponding transactions.basket document

I am assuming that the above table definitions will ensure that all the
records with the same trans_id will be located on the same node?

But will the corresponding transaction also be located on the same node? Or
is there something else that I need to define?

Thanks,
Jose



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

Re: JDBC Streaming

[joseheitor]

Hi Ilya,

Tried executing SQL statement - 'SET STREAMING ON' from code, but this
produces an exception.

Here is my table definition:

CREATE TABLE public.test_data (
  pk LONG, id LONG, k VARCHAR, v VARCHAR,  PRIMARY KEY (pk, id))
  WITH "TEMPLATE=PARTITIONED, BACKUPS=1, ATOMICITY=TRANSACTIONAL,
WRITE_SYNCHRONIZATION_MODE=FULL_SYNC, AFFINITY_KEY=id";

CREATE INDEX test_data_id_k_v ON public.test_data (id, k, v);
CREATE INDEX test_data_k_id_v ON public.test_data (k, id, v);
CREATE INDEX test_data_k_v_id ON public.test_data (k, v, id);
CREATE INDEX test_data_id ON public.test_data (id);
CREATE INDEX test_data_k ON public.test_data (k);
CREATE INDEX test_data_v ON public.test_data (v);
CREATE INDEX test_data_k_v ON public.test_data (k, v);

Here is my code:

public static void streamTestData() throws Exception {

Connection dbConnection = null;
PreparedStatement statement = null;

String insertTableSQL = "INSERT INTO test_data (pk, id, k, v) VALUES
(?, ?, ?, ?)";
try {

Class.forName("org.apache.ignite.IgniteJdbcDriver");
dbConnection = DriverManager.getConnection(
   
"jdbc:ignite:cfg://cache=DATASTORE:streaming=true:streamingFlushFrequency=1000@file:///home/me/ignite-jdbc-client.xml",
"", "");
dbConnection.setSchema("PUBLIC");

statement = dbConnection.prepareStatement(insertTableSQL);
int pk = 0;
for (int id = 0; id < 1; id ++) {

statement.setInt(1, ++pk);
statement.setInt(2, id);
statement.setString(3, "trans.cust.first_name");
statement.setString(4, pk % 2 == 0 ? "Joe" : "Mary");
statement.executeUpdate();

statement.setInt(1, ++pk);
statement.setInt(2, id);
statement.setString(3, "trans.cust.last_name");
statement.setString(4, pk % 2 == 0 ? "Smythe" : "Pearson");
statement.executeUpdate();

statement.setInt(1, ++pk);
statement.setInt(2, id);
statement.setString(3, "trans.cust.email");
statement.setString(4, pk % 2 == 0 ? "j...@work.com" :
"m...@home.org");
statement.executeUpdate();

statement.setInt(1, ++pk);
statement.setInt(2, id);
statement.setString(3, "trans.cust.gender");
statement.setString(4, pk % 2 == 0 ? "Male" : "Female");
statement.executeUpdate();

long now = System.currentTimeMillis();

statement.setInt(1, ++pk);
statement.setInt(2, id);
statement.setString(3, "trans.date");
statement.setString(4, "201" + String.valueOf(now % 10) +
"-12-0" + String.valueOf(pk % 10));
statement.executeUpdate();

statement.setInt(1, ++pk);
statement.setInt(2, id);
statement.setString(3, "trans.amount");
statement.setString(4, String.valueOf((now % 100) + 1));
statement.executeUpdate();
}
} catch (Exception e) {
e.printStackTrace(System.err);
} finally {

if (statement != null) {
statement.close();
}

if (dbConnection != null) {
dbConnection.close();
}
}
}




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

SQL 'affinityKey' or 'AFFINITY_KEY"

[joseheitor]

What is the correct syntax for the affinity key parameter in the WITH clause
in a CREATE TABLE SQL statement?



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

JDBC Streaming

[joseheitor]

JDBC Client connection URL with 'streaming=true' runs (much faster than
without) but no data is inserted into table. No errors are reported.

I have two nodes with one partitioned table. Without 'streaming=true' it
take around 8 hours to load 8,000,000 rows. The insert loop takes only
around 8 minutes with streaming=true, but no data is inserted into the
table.

Any suggestions?



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

Re: configuration for some persistent and some non-persistent caches

[joseheitor]

Hi Denis,

I am struggling to get this properly configured for a persistent cache
(DATASTORE) and a non-persistent cache (SESSIONCACHE). Here is my config ...
(what am I doing wrong?):



  

  

  




  

  

  
  









  
...  




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

Re: Optimum persistent SQL storage and querying strategy

[joseheitor]

Hi Ignite Team,

Any tips and recommendations...?

Jose



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

Optimum persistent SQL storage and querying strategy

[joseheitor]

Hi Ignite team,

We need to store and query retail transaction data that is expected to grow
to over 50 million records. Each basket-transaction is represented as a JSON
object with somewhat varying schema, depending on the type of transaction.
And each transaction will contain a varying amount of 'child' item entries.

What programming strategy and cluster architecture would you recommend to
optimise querying on such a persistent datastore? (preferably using SQL)

Thanks,
Jose



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

Re: Scaling with SQL query

[joseheitor]

Hi Dmitry,

This is a fantastic explanation to better understand scaling strategies for
SQL - Thanks.

A couple of questions:

1. Do these mechanisms apply equally for persistent caches?
2. Regarding your point (2.) - How would one achieve this? (more clients?)
(more connections to node?) Are these configurable options? Or how do we
implement them in our interfacing applications...and how?

Jose



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

Re: Text Query via SQL or REST API?

[joseheitor]

Thanks, Dmitry.

What is the recommended strategy to most efficiently perform searches on a
persistent store table containing a column with text (JSON document)...?

Jose



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

Text Query via SQL or REST API?

[joseheitor]

Is it possible to perform a full text search query operation via either the
SQL interface? Or the REST API interface?

And does this (or the Java API) automatically leverage Lucene indexing as
per the documentation example for a TextQuery? Or are there other specific
operations or configurations required to use the Lucene indexing features?



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

configuration for some persistent and some non-persistent caches

[joseheitor]

How do you configure some caches to be persistent and others not, on the same
cluster?



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

Re: SQL Transactional Support for Commit and rollback operation.

[joseheitor]

Yes - please clarify?

We are also developing a product for which we are keen to use Ignite
(instead of PostgreSQL), but it is a complete BLOCKER if there is no
persistent SQL Transactional support.

So we also require this urgently!
(Please, please, please...)



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

Re: Redis KEYS command?

[joseheitor]

Thanks for the suggestion Andrey.

We are wishing to keep our code-base compatible with Redis, so not
Ignite-specific code.

But we do need support for the Redis KEYS command...

Jose



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

Redis KEYS command?

[joseheitor]

Hi,

Is there any intention of implementing support for the Redis KEYS command in
the near future?

Jose



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

Re: [2.4.0] Cluster unrecoverable after node failure

[joseheitor]

Thanks Arseny - I really appreciate your assistance!

The config files that I use are included in the attached archive.
ignite-replicated.zip

  

Let me know if you need anything else, or any clarification?

Below (for your reference) the SQL commands that I use, to populate the
database with test data:

DROP TABLE PUBLIC.Person
DROP TABLE PUBLIC.City

CREATE TABLE PUBLIC.City (
  id LONG PRIMARY KEY, name VARCHAR)
  WITH "TEMPLATE=REPLICATED, BACKUPS=1, ATOMICITY=TRANSACTIONAL,
WRITE_SYNCHRONIZATION_MODE=FULL_SYNC"

CREATE TABLE PUBLIC.Person (
  id LONG, name VARCHAR, city_id LONG, PRIMARY KEY (id, city_id))
  WITH "TEMPLATE=REPLICATED, BACKUPS=1, ATOMICITY=TRANSACTIONAL,
WRITE_SYNCHRONIZATION_MODE=FULL_SYNC"
 
INSERT INTO PUBLIC.City (id, name) VALUES (1, 'Forest Hill')
INSERT INTO PUBLIC.City (id, name) VALUES (2, 'Denver')
INSERT INTO PUBLIC.City (id, name) VALUES (3, 'St. Petersburg')
INSERT INTO PUBLIC.Person (id, name, city_id) VALUES (1, 'John Doe', 3)
INSERT INTO PUBLIC.Person (id, name, city_id) VALUES (2, 'Jane Roe', 2)
INSERT INTO PUBLIC.Person (id, name, city_id) VALUES (3, 'Mary Major', 1)
INSERT INTO PUBLIC.Person (id, name, city_id) VALUES (4, 'Richard Miles', 2)

SELECT p.name, c.name
FROM PUBLIC.Person p, PUBLIC.City c
WHERE p.city_id = c.id AND c.name = 'Denver'

SELECT COUNT(*) FROM PUBLIC.Person
SELECT COUNT(*) FROM PUBLIC.City

DELETE FROM PUBLIC.Person WHERE name = 'Jane Roe'



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

Re: [2.4.0] Cluster unrecoverable after node failure

[joseheitor]

Hi Arseny,

Regrettably still experiencing the same results.

Could there be something else that I am overlooking? My configurations are
quite basic - I can post them, if it will be helpful for you to duplicate
the issue...

Thanks



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

Re: [2.4.0] Cluster unrecoverable after node failure

[joseheitor]

Hi Arseny,

Can this be set in the configuration file for each node? (like a property?)

Our application is aiming (currently) to use Ignite purely as a distributed,
persistent and cached, fault-tolerant SQL database through the Client JDBC
driver. It does/must not instantiate (depend on) any Ignite library
components directly in the application code.

Thanks



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

Re: [2.4.0] Cluster unrecoverable after node failure

[joseheitor]

Hi Pavel,


1. Disconnect database connection
2. Stop all component processes on all nodes (Ctl+C)
3. I delete the 'work' folder on the node on which I want to simulate an
unrecoverable hardware failure.

When the node is started up anew - it is like deploying a new instance on
the same IP address... (without any data).

Jose



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

Re: [2.4.0] Cluster unrecoverable after node failure

[joseheitor]

I do apologise for the long-winded post earlier (with error stack-traces,
etc.).

And hope that someone can assist me with this issue - it is a basic,
real-world scenario that tests the fundamental integrity of the clustering
system!

Am I perhaps missing something? Or mismanaging the cluster in such an
occurrence? What is the 'best-practice' to recover from such a scenario?

Here is the condensed version of the problem, which is hopefully easier to
read (without the stack-traces):

*Scenario: Secondary (Node-B) Failure*

Environment:
  - 2 nodes (Node-A, Node-B)
  - Ignite native persistence enabled
  - static IP discovery - both node IPs listed
  - JDBC (Client) - DBeaver
  - manual cluster activation

Steps:
  1 - start  both nodes with no data
  2 - activate cluster on same machine as Node-A
  3 - load data via SQL JDBC (...WITH template=replicated, backups=1)
  4 - simulate power-failure ... all components down; Node-B with
unrecoverable damage (hardware)
  5 - start new Node-B instance (with no data)
  6 - attempt to start Node-A (undamaged, with good data)...

PROBLEM: Unable to start Node-A. (Error in previous post below...) 

In an attempt to recover the cluster and data:
  7 - stop Node-B
  8 - start Node-A - first
  9 - start Node-B (starts)
  10 - attempt to activate cluster

PROBLEM: Cluster activation operation Freezes.

Additional notes:
- All data is lost and cannot be recovered.
- This did not occur with Ignite 2.3.0, although data consistency was
unpredictable but would sometimes align after some period of time.
- If Node-A (with data) is started before Node-B (new instance, empty data),
both nodes start, but cluster fails to activate. (See below post for details
of the error observed on Node-A ouput)

...



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

DROP TABLE(s) reimerge after restart

[joseheitor]

With native persistence enabled.

Steps to recreate:

  1 - DROP TABLE(s) containing data via SQL JDBC (Client-Driver) on DBeaver
  2 - confirm tables gone by refreshing DBeaver 'Database Navigator' panel
and observing that previously listed tables are no longer present
  3 - reboot and restart node and cluster
  4 - connect from DBeaver and observe: previously dropped tables are still
listed (but with empty data)



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

[2.4.0] Cluster unrecoverable after node failure

[joseheitor]

*Scenario A: Secondary (Node-B) Failure*

Environment: 
  - 2 nodes (Node-A, Node-B)
  - Ignite native persistence enabled
  - static IP discovery - both node IPs listed
  - JDBC (Client) - DBeaver
  - manual cluster activation

Steps:
  1 - start  both nodes with no data
  2 - activate cluster on same machine as Node-A
  3 - load data via SQL JDBC (...WITH template=replicated, backups=1)
  4 - simulate power-failure ... all components down; Node-B with
unrecoverable damage
  5 - start new Node-B instance (with no data)
  6 - attempt to start Node-A (undamaged, with data)... 

/PROBLEM: Unable to start Node-A. Error follows:

[10:43:37,773][SEVERE][main][IgniteKernal] Failed to start manager:
GridManagerAdapter [enabled=true,
name=o.a.i.i.managers.discovery.GridDiscoveryManager]
class org.apache.ignite.IgniteCheckedException: Failed to start SPI:
TcpDiscoverySpi [addrRslvr=null, sockTimeout=5000, ackTimeout=5000,
marsh=JdkMarshaller
[clsFilter=org.apache.ignite.internal.IgniteKernal$5@cc6460c], reconCnt=10,
reconDelay=2000, maxAckTimeout=60, forceSrvMode=false,
clientReconnectDisabled=false]
at
org.apache.ignite.internal.managers.GridManagerAdapter.startSpi(GridManagerAdapter.java:300)
at
org.apache.ignite.internal.managers.discovery.GridDiscoveryManager.start(GridDiscoveryManager.java:892)
at
org.apache.ignite.internal.IgniteKernal.startManager(IgniteKernal.java:1669)
at
org.apache.ignite.internal.IgniteKernal.start(IgniteKernal.java:983)
at
org.apache.ignite.internal.IgnitionEx$IgniteNamedInstance.start0(IgnitionEx.java:1973)
at
org.apache.ignite.internal.IgnitionEx$IgniteNamedInstance.start(IgnitionEx.java:1716)
at
org.apache.ignite.internal.IgnitionEx.start0(IgnitionEx.java:1144)
at
org.apache.ignite.internal.IgnitionEx.startConfigurations(IgnitionEx.java:1062)
at org.apache.ignite.internal.IgnitionEx.start(IgnitionEx.java:948)
at org.apache.ignite.internal.IgnitionEx.start(IgnitionEx.java:847)
at org.apache.ignite.internal.IgnitionEx.start(IgnitionEx.java:717)
at org.apache.ignite.internal.IgnitionEx.start(IgnitionEx.java:686)
at org.apache.ignite.Ignition.start(Ignition.java:347)
at
org.apache.ignite.startup.cmdline.CommandLineStartup.main(CommandLineStartup.java:302)
Caused by: class org.apache.ignite.spi.IgniteSpiException: Node with set up
BaselineTopology is not allowed to join cluster without one:
b5c7b617-4a34-4e3a-b119-97744f72258e
at
org.apache.ignite.spi.discovery.tcp.TcpDiscoverySpi.checkFailedError(TcpDiscoverySpi.java:1856)
at
org.apache.ignite.spi.discovery.tcp.ServerImpl.joinTopology(ServerImpl.java:932)
at
org.apache.ignite.spi.discovery.tcp.ServerImpl.spiStart(ServerImpl.java:364)
at
org.apache.ignite.spi.discovery.tcp.TcpDiscoverySpi.spiStart(TcpDiscoverySpi.java:1930)
at
org.apache.ignite.internal.managers.GridManagerAdapter.startSpi(GridManagerAdapter.java:297)
... 13 more
[10:43:37,778][SEVERE][main][IgniteKernal] Got exception while starting
(will rollback startup routine).
class org.apache.ignite.IgniteCheckedException: Failed to start manager:
GridManagerAdapter [enabled=true,
name=org.apache.ignite.internal.managers.discovery.GridDiscoveryManager]
at
org.apache.ignite.internal.IgniteKernal.startManager(IgniteKernal.java:1674)
at
org.apache.ignite.internal.IgniteKernal.start(IgniteKernal.java:983)
at
org.apache.ignite.internal.IgnitionEx$IgniteNamedInstance.start0(IgnitionEx.java:1973)
at
org.apache.ignite.internal.IgnitionEx$IgniteNamedInstance.start(IgnitionEx.java:1716)
at
org.apache.ignite.internal.IgnitionEx.start0(IgnitionEx.java:1144)
at
org.apache.ignite.internal.IgnitionEx.startConfigurations(IgnitionEx.java:1062)
at org.apache.ignite.internal.IgnitionEx.start(IgnitionEx.java:948)
at org.apache.ignite.internal.IgnitionEx.start(IgnitionEx.java:847)
at org.apache.ignite.internal.IgnitionEx.start(IgnitionEx.java:717)
at org.apache.ignite.internal.IgnitionEx.start(IgnitionEx.java:686)
at org.apache.ignite.Ignition.start(Ignition.java:347)
at
org.apache.ignite.startup.cmdline.CommandLineStartup.main(CommandLineStartup.java:302)
Caused by: class org.apache.ignite.IgniteCheckedException: Failed to start
SPI: TcpDiscoverySpi [addrRslvr=null, sockTimeout=5000, ackTimeout=5000,
marsh=JdkMarshaller
[clsFilter=org.apache.ignite.internal.IgniteKernal$5@cc6460c], reconCnt=10,
reconDelay=2000, maxAckTimeout=60, forceSrvMode=false,
clientReconnectDisabled=false]
at
org.apache.ignite.internal.managers.GridManagerAdapter.startSpi(GridManagerAdapter.java:300)
at
org.apache.ignite.internal.managers.discovery.GridDiscoveryManager.start(GridDiscoveryManager.java:892)
at
org.apache.ignite.internal.IgniteKernal.startManager(IgniteKernal.java:1669)
... 11 more
Caused by: class org.apache.ignite.spi.Igni

Understanding SQL CREATE TABLE 'WITH' Parameters?

[joseheitor]

Three questions regarding relationship of cached data to persisted data:

1) Do the settings of the parameters in the 'WITH' clause apply only to the
cached data? Or also to the persisted data?
 
2) Can the parameters set in a CREATE TABLE 'WITH' clause be rather
externally and globally set in a config file? (TEMPLATE, BACKUPS, etc)

3) In TEMPLATE=PARTITIONED: Is the persistent data stored to disk on a
particular node the same as that allocated to the node's cache? Or is all
data stored to disk on all nodes in the cluster, and only the cached data
partitioned between nodes?



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

Liquibase with Ignite?

[joseheitor]

Is anyone using Liquibase with Apache Ignite native SQL persistence?



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

Re: Ignite with dBeaver

[joseheitor]

Hi Wilhem,

I have also been evaluating Ignite, and also followed the steps in setting
up SQL tooling with DBeaver...

At that final step, when you choose a driver class - I had a choice (from a
pulldown list, I think) between the two classes. You can then select the
Thin version for simple local connections, as described in the docs.

If you setup a distributed, persistent cluster though - you will need to add
all the jars in the lib folder, to the classpath, and then select the
'client' driver.

Here is the post that followed my progression - you may pick up some useful
tips from the challenges I encountered along the way:

http://apache-ignite-users.70518.x6.nabble.com/How-to-configure-a-cluster-as-a-persistent-replicated-SQL-database-td20251.html

  

Jose



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

Re: How to configure a cluster as a persistent, replicated SQL database

[joseheitor]

Hi Naveen,

I managed to get everything up a and running...but for my testing, I have
not been connecting from code.

I have been using DBeaver to connect using SQL via the JDBC driver(s).

Jose



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

Re: How to configure a cluster as a persistent, replicated SQL database

[joseheitor]

Okay - figured it out...

Had to prefix the 'PUBLIC' schema to the table name during the CREATE TABLE
query:

CREATE TABLE PUBLIC.City(...



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