You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Cong Guo <co...@huawei.com> on 2018/06/18 18:50:17 UTC
A bug in SQL "CREATE TABLE" and its underlying Ignite cache
Hi,
I need to use both SQL and non-SQL APIs (key-value) on a single cache. I follow the document in:
https://apacheignite-sql.readme.io/docs/create-table
I use "CREATE TABLE" to create the table and its underlying cache. I can use both SQL "INSERT" and put to add data to the cache. However, when I run a SqlFieldsQuery, only the row added by SQL "INSERT" can be seen. The Ignite version is 2.4.0.
You can reproduce the bug using the following code:
CacheConfiguration<Integer, Integer> dummyCfg = new CacheConfiguration<>("DUMMY");
dummyCfg.setSqlSchema("PUBLIC");
try(IgniteCache<Integer, Integer> dummyCache = ignite.getOrCreateCache(dummyCfg)){
String createTableSQL = "CREATE TABLE Persons (id LONG, orgId LONG, firstName VARCHAR, lastName VARCHAR, resume VARCHAR, salary FLOAT, PRIMARY KEY(firstName))" +
"WITH \"BACKUPS=1, ATOMICITY=TRANSACTIONAL, WRITE_SYNCHRONIZATION_MODE=PRIMARY_SYNC, CACHE_NAME=" + PERSON_CACHE_NAME +
", KEY_TYPE=String, VALUE_TYPE=BinaryObject\"";
dummyCache.query(new SqlFieldsQuery(createTableSQL)).getAll();
SqlFieldsQuery firstInsert = new SqlFieldsQuery("INSERT INTO Persons (id, orgId, firstName, lastname, resume, salary) VALUES (?,?,?,?,?,?)");
firstInsert.setArgs(1L, 1L, "John", "Smith", "PhD", 10000.0d);
dummyCache.query(firstInsert).getAll();
try(IgniteCache<String, Person> personCache = ignite.cache(PERSON_CACHE_NAME)){
Person p2 = new Person(2L, 1L, "Hello", "World", "Master", 1000.0d);
personCache.put("Hello", p2);
IgniteCache<Long, BinaryObject> binaryCache = personCache.<Long, BinaryObject>withKeepBinary();
System.out.println("Size of the cache is: " + binaryCache.size(CachePeekMode.ALL));
binaryCache.query(new ScanQuery<>(null)).forEach(entry -> System.out.println(entry.getKey()));
System.out.println("Select results: ");
SqlFieldsQuery qry = new SqlFieldsQuery("select * from Persons");
QueryCursor<List<?>> answers = personCache.query(qry);
List<List<?>> personList = answers.getAll();
for(List<?> row : personList) {
String fn = (String)row.get(2);
System.out.println(fn);
}
}
}
The output is:
Size of the cache is: 2
Hello
String [idHash=213193302, hash=-900113201, FIRSTNAME=John]
Select results:
John
The bug is that the SqlFieldsQuery cannot see the data added by "put".
Re: A bug in SQL "CREATE TABLE" and its underlying Ignite cache
Posted by Denis Magda <dm...@apache.org>.
Hi,
Check out this code samples that suggest best practices on sticking
together SQL + k/v if the structure is defined by CREATE TABLE.
https://github.com/dmagda/ignite_world_demo
--
Denis
On Mon, Jun 18, 2018 at 11:50 AM Cong Guo <co...@huawei.com> wrote:
> Hi,
>
>
>
> I need to use both SQL and non-SQL APIs (key-value) on a single cache. I
> follow the document in:
>
> https://apacheignite-sql.readme.io/docs/create-table
>
>
>
> I use “CREATE TABLE” to create the table and its underlying cache. I can
> use both SQL “INSERT” and put to add data to the cache. However, when I run
> a SqlFieldsQuery, only the row added by SQL “INSERT” can be seen. The
> Ignite version is 2.4.0.
>
>
>
> You can reproduce the bug using the following code:
>
>
>
> CacheConfiguration<Integer, Integer> dummyCfg = new
> CacheConfiguration<>("DUMMY");
>
>
> dummyCfg.setSqlSchema("PUBLIC");
>
>
>
> try(IgniteCache<Integer,
> Integer> dummyCache = ignite.getOrCreateCache(dummyCfg)){
>
> String
> createTableSQL = "CREATE TABLE Persons (id LONG, orgId LONG, firstName
> VARCHAR, lastName VARCHAR, resume VARCHAR, salary FLOAT, PRIMARY
> KEY(firstName))" +
>
>
> "WITH \"BACKUPS=1, ATOMICITY=TRANSACTIONAL,
> WRITE_SYNCHRONIZATION_MODE=PRIMARY_SYNC, CACHE_NAME=" + PERSON_CACHE_NAME +
>
>
> ", KEY_TYPE=String, VALUE_TYPE=BinaryObject\"";
>
>
>
>
> dummyCache.query(new SqlFieldsQuery(createTableSQL)).getAll();
>
>
>
>
> SqlFieldsQuery firstInsert = new SqlFieldsQuery("INSERT INTO Persons (id,
> orgId, firstName, lastname, resume, salary) VALUES (?,?,?,?,?,?)");
>
>
> firstInsert.setArgs(1L, 1L, "John", "Smith", "PhD", 10000.0d);
>
>
> dummyCache.query(firstInsert).getAll();
>
>
>
>
> try(IgniteCache<String, Person> personCache =
> ignite.cache(PERSON_CACHE_NAME)){
>
>
> Person p2 = new Person(2L, 1L, "Hello", "World", "Master", 1000.0d);
>
>
> personCache.put("Hello", p2);
>
>
>
>
> IgniteCache<Long, BinaryObject> binaryCache = personCache.<Long,
> BinaryObject>withKeepBinary();
>
>
> System.out.println("Size of the cache is: " +
> binaryCache.size(CachePeekMode.ALL));
>
>
>
>
>
> binaryCache.query(new ScanQuery<>(null)).forEach(entry ->
> System.out.println(entry.getKey()));
>
>
>
>
>
> System.out.println("Select results: ");
>
>
> SqlFieldsQuery qry = new SqlFieldsQuery("select * from Persons");
>
>
> QueryCursor<List<?>> answers = personCache.query(qry);
>
>
> List<List<?>> personList = answers.getAll();
>
>
> for(List<?> row : personList) {
>
>
> String fn = (String)row.get(2);
>
>
> System.out.println(fn);
>
>
> }
>
> }
>
> }
>
>
>
>
>
> The output is:
>
>
>
> Size of the cache is: 2
>
> Hello
>
> String [idHash=213193302, hash=-900113201, FIRSTNAME=John]
>
> Select results:
>
> John
>
>
>
> The bug is that the SqlFieldsQuery cannot see the data added by “put”.
>
RE: A bug in SQL "CREATE TABLE" and its underlying Ignite cache
Posted by "slava.koptilin" <sl...@gmail.com>.
Hello,
> So can I add a field to a nested object dynamically (without restarting
> the cluster) by using annotations?
I don't think so. If you need maximum flexibility then try to use only
BinaryObjects without POJOs.
Thanks!
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
RE: A bug in SQL "CREATE TABLE" and its underlying Ignite cache
Posted by Cong Guo <co...@huawei.com>.
So can I add a field to a nested object dynamically (without restarting the cluster) by using annotations?
-----Original Message-----
From: slava.koptilin [mailto:slava.koptilin@gmail.com]
Sent: 2018年6月21日 10:44
To: user@ignite.apache.org
Subject: RE: A bug in SQL "CREATE TABLE" and its underlying Ignite cache
Hi,
> How can I write the “Create Table” statement to create columns for the
> two members of Address in Table Person?
If I am not mistaken, SQL tables cannot contain nested objects.
Apache Ignite SQL engine allows executing SQL queries of nested fields. In case, a cache was configured via annotations, for example.
Please take a look at this page:
https://apacheignite-sql.readme.io/docs/schema-and-indexes#section-indexing-nested-objects
Thanks.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
RE: A bug in SQL "CREATE TABLE" and its underlying Ignite cache
Posted by "slava.koptilin" <sl...@gmail.com>.
Hi,
> How can I write the “Create Table” statement to create columns for the two
> members of Address in Table Person?
If I am not mistaken, SQL tables cannot contain nested objects.
Apache Ignite SQL engine allows executing SQL queries of nested fields. In
case, a cache was configured via annotations, for example.
Please take a look at this page:
https://apacheignite-sql.readme.io/docs/schema-and-indexes#section-indexing-nested-objects
Thanks.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
RE: A bug in SQL "CREATE TABLE" and its underlying Ignite cache
Posted by Cong Guo <co...@huawei.com>.
Thank you for your reply! I have another question. If the Person class has a member of another class, say Address. Class Address has two members, int streetNo, String streetName. How can I write the “Create Table” statement to create columns for the two members of Address in Table Person? Do I have to create another table for Address? Thank you.
From: Вячеслав Коптилин [mailto:slava.koptilin@gmail.com]
Sent: 2018年6月20日 14:55
To: user@ignite.apache.org
Subject: Re: A bug in SQL "CREATE TABLE" and its underlying Ignite cache
Hi,
> How should I use BinaryObject in the CREATE TABLE statement?
If you want to use binary objects, then there is no need to specify 'VALUE_TYPE'.
Please use the following code:
String createTableSQL = "CREATE TABLE Persons (id LONG, orgId LONG, firstName VARCHAR, lastName VARCHAR, resume VARCHAR, salary FLOAT, PRIMARY KEY(firstName))" +
"WITH \"BACKUPS=1, ATOMICITY=TRANSACTIONAL, WRITE_SYNCHRONIZATION_MODE=PRIMARY_SYNC, CACHE_NAME=" + PERSON_CACHE_NAME + "\"";
Thanks!
вт, 19 июн. 2018 г. в 16:43, Cong Guo <co...@huawei.com>>:
Hi,
How should I use BinaryObject in the CREATE TABLE statement? I try using BinaryObject.class.getName() as the value_type, but get the following exception:
class org.apache.ignite.IgniteCheckedException: Failed to initialize property 'ORGID' of type 'java.lang.Long' for key class 'class java.lang.Long' and value class 'interface org.apache.ignite.binary.BinaryObject'. Make sure that one of these classes contains respective getter method or field.
I want to use BinaryObject here for some flexibility.
Thanks,
Cong
From: Вячеслав Коптилин [mailto:slava.koptilin@gmail.com<ma...@gmail.com>]
Sent: 2018年6月18日 18:10
To: user@ignite.apache.org<ma...@ignite.apache.org>
Subject: Re: A bug in SQL "CREATE TABLE" and its underlying Ignite cache
Hello,
It seems that the root cause of the issue is wrong values of 'KEY_TYPE' and 'VALUE_TYPE' parameters.
In your case, there is no need to specify 'KEY_TYPE' at all, and 'VALUE_TYPE' should Person.class.getName() I think.
Please try the following:
String createTableSQL = "CREATE TABLE Persons (id LONG, orgId LONG, firstName VARCHAR, lastName VARCHAR, resume VARCHAR, salary FLOAT, PRIMARY KEY(firstName))" +
"WITH \"BACKUPS=1, ATOMICITY=TRANSACTIONAL, WRITE_SYNCHRONIZATION_MODE=PRIMARY_SYNC, CACHE_NAME=" + PERSON_CACHE_NAME +
", VALUE_TYPE=" + Person.class.getName() + "\"";
Best regards,
Slava.
пн, 18 июн. 2018 г. в 21:50, Cong Guo <co...@huawei.com>>:
Hi,
I need to use both SQL and non-SQL APIs (key-value) on a single cache. I follow the document in:
https://apacheignite-sql.readme.io/docs/create-table
I use “CREATE TABLE” to create the table and its underlying cache. I can use both SQL “INSERT” and put to add data to the cache. However, when I run a SqlFieldsQuery, only the row added by SQL “INSERT” can be seen. The Ignite version is 2.4.0.
You can reproduce the bug using the following code:
CacheConfiguration<Integer, Integer> dummyCfg = new CacheConfiguration<>("DUMMY");
dummyCfg.setSqlSchema("PUBLIC");
try(IgniteCache<Integer, Integer> dummyCache = ignite.getOrCreateCache(dummyCfg)){
String createTableSQL = "CREATE TABLE Persons (id LONG, orgId LONG, firstName VARCHAR, lastName VARCHAR, resume VARCHAR, salary FLOAT, PRIMARY KEY(firstName))" +
"WITH \"BACKUPS=1, ATOMICITY=TRANSACTIONAL, WRITE_SYNCHRONIZATION_MODE=PRIMARY_SYNC, CACHE_NAME=" + PERSON_CACHE_NAME +
", KEY_TYPE=String, VALUE_TYPE=BinaryObject\"";
dummyCache.query(new SqlFieldsQuery(createTableSQL)).getAll();
SqlFieldsQuery firstInsert = new SqlFieldsQuery("INSERT INTO Persons (id, orgId, firstName, lastname, resume, salary) VALUES (?,?,?,?,?,?)");
firstInsert.setArgs(1L, 1L, "John", "Smith", "PhD", 10000.0d);
dummyCache.query(firstInsert).getAll();
try(IgniteCache<String, Person> personCache = ignite.cache(PERSON_CACHE_NAME)){
Person p2 = new Person(2L, 1L, "Hello", "World", "Master", 1000.0d);
personCache.put("Hello", p2);
IgniteCache<Long, BinaryObject> binaryCache = personCache.<Long, BinaryObject>withKeepBinary();
System.out.println("Size of the cache is: " + binaryCache.size(CachePeekMode.ALL));
binaryCache.query(new ScanQuery<>(null)).forEach(entry -> System.out.println(entry.getKey()));
System.out.println("Select results: ");
SqlFieldsQuery qry = new SqlFieldsQuery("select * from Persons");
QueryCursor<List<?>> answers = personCache.query(qry);
List<List<?>> personList = answers.getAll();
for(List<?> row : personList) {
String fn = (String)row.get(2);
System.out.println(fn);
}
}
}
The output is:
Size of the cache is: 2
Hello
String [idHash=213193302, hash=-900113201, FIRSTNAME=John]
Select results:
John
The bug is that the SqlFieldsQuery cannot see the data added by “put”.
Re: A bug in SQL "CREATE TABLE" and its underlying Ignite cache
Posted by Вячеслав Коптилин <sl...@gmail.com>.
Hi,
> How should I use BinaryObject in the CREATE TABLE statement?
If you want to use binary objects, then there is no need to specify
'VALUE_TYPE'.
Please use the following code:
String createTableSQL = "CREATE TABLE Persons (id LONG, orgId LONG,
firstName VARCHAR, lastName VARCHAR, resume VARCHAR, salary FLOAT, PRIMARY
KEY(firstName))" +
"WITH \"BACKUPS=1, ATOMICITY=TRANSACTIONAL,
WRITE_SYNCHRONIZATION_MODE=PRIMARY_SYNC, CACHE_NAME=" + PERSON_CACHE_NAME +
"\"";
Thanks!
вт, 19 июн. 2018 г. в 16:43, Cong Guo <co...@huawei.com>:
> Hi,
>
>
>
> How should I use BinaryObject in the CREATE TABLE statement? I try using
> BinaryObject.class.getName() as the value_type, but get the following
> exception:
>
>
>
> class org.apache.ignite.IgniteCheckedException: Failed to initialize
> property 'ORGID' of type 'java.lang.Long' for key class 'class
> java.lang.Long' and value class 'interface
> org.apache.ignite.binary.BinaryObject'. Make sure that one of these classes
> contains respective getter method or field.
>
>
>
> I want to use BinaryObject here for some flexibility.
>
>
>
> Thanks,
>
> Cong
>
>
>
> *From:* Вячеслав Коптилин [mailto:slava.koptilin@gmail.com]
> *Sent:* 2018年6月18日 18:10
> *To:* user@ignite.apache.org
> *Subject:* Re: A bug in SQL "CREATE TABLE" and its underlying Ignite cache
>
>
>
> Hello,
>
>
>
> It seems that the root cause of the issue is wrong values of 'KEY_TYPE'
> and 'VALUE_TYPE' parameters.
>
> In your case, there is no need to specify 'KEY_TYPE' at all, and
> 'VALUE_TYPE' should Person.class.getName() I think.
>
>
>
> Please try the following:
>
> String createTableSQL = "CREATE TABLE Persons (id LONG, orgId
> LONG, firstName VARCHAR, lastName VARCHAR, resume VARCHAR, salary FLOAT,
> PRIMARY KEY(firstName))" +
>
> "WITH \"BACKUPS=1, ATOMICITY=TRANSACTIONAL,
> WRITE_SYNCHRONIZATION_MODE=PRIMARY_SYNC, CACHE_NAME=" + PERSON_CACHE_NAME +
>
> ", VALUE_TYPE=" + Person.class.getName() + "\"";
>
>
>
> Best regards,
>
> Slava.
>
>
>
> пн, 18 июн. 2018 г. в 21:50, Cong Guo <co...@huawei.com>:
>
> Hi,
>
>
>
> I need to use both SQL and non-SQL APIs (key-value) on a single cache. I
> follow the document in:
>
> https://apacheignite-sql.readme.io/docs/create-table
>
>
>
> I use “CREATE TABLE” to create the table and its underlying cache. I can
> use both SQL “INSERT” and put to add data to the cache. However, when I run
> a SqlFieldsQuery, only the row added by SQL “INSERT” can be seen. The
> Ignite version is 2.4.0.
>
>
>
> You can reproduce the bug using the following code:
>
>
>
> CacheConfiguration<Integer, Integer> dummyCfg = new
> CacheConfiguration<>("DUMMY");
>
>
> dummyCfg.setSqlSchema("PUBLIC");
>
>
>
> try(IgniteCache<Integer,
> Integer> dummyCache = ignite.getOrCreateCache(dummyCfg)){
>
> String
> createTableSQL = "CREATE TABLE Persons (id LONG, orgId LONG, firstName
> VARCHAR, lastName VARCHAR, resume VARCHAR, salary FLOAT, PRIMARY
> KEY(firstName))" +
>
>
> "WITH \"BACKUPS=1, ATOMICITY=TRANSACTIONAL,
> WRITE_SYNCHRONIZATION_MODE=PRIMARY_SYNC, CACHE_NAME=" + PERSON_CACHE_NAME +
>
>
> ", KEY_TYPE=String, VALUE_TYPE=BinaryObject\"";
>
>
>
>
> dummyCache.query(new SqlFieldsQuery(createTableSQL)).getAll();
>
>
>
>
> SqlFieldsQuery firstInsert = new SqlFieldsQuery("INSERT INTO Persons (id,
> orgId, firstName, lastname, resume, salary) VALUES (?,?,?,?,?,?)");
>
>
> firstInsert.setArgs(1L, 1L, "John", "Smith", "PhD", 10000.0d);
>
>
> dummyCache.query(firstInsert).getAll();
>
>
>
>
> try(IgniteCache<String, Person> personCache =
> ignite.cache(PERSON_CACHE_NAME)){
>
>
> Person p2 = new Person(2L, 1L, "Hello", "World", "Master", 1000.0d);
>
>
> personCache.put("Hello", p2);
>
>
>
>
> IgniteCache<Long, BinaryObject> binaryCache = personCache.<Long,
> BinaryObject>withKeepBinary();
>
>
> System.out.println("Size of the cache is: " +
> binaryCache.size(CachePeekMode.ALL));
>
>
>
>
>
> binaryCache.query(new ScanQuery<>(null)).forEach(entry ->
> System.out.println(entry.getKey()));
>
>
>
>
>
> System.out.println("Select results: ");
>
>
> SqlFieldsQuery qry = new SqlFieldsQuery("select * from Persons");
>
>
> QueryCursor<List<?>> answers = personCache.query(qry);
>
>
> List<List<?>> personList = answers.getAll();
>
>
> for(List<?> row : personList) {
>
>
> String fn = (String)row.get(2);
>
>
> System.out.println(fn);
>
>
> }
>
> }
>
> }
>
>
>
>
>
> The output is:
>
>
>
> Size of the cache is: 2
>
> Hello
>
> String [idHash=213193302, hash=-900113201, FIRSTNAME=John]
>
> Select results:
>
> John
>
>
>
> The bug is that the SqlFieldsQuery cannot see the data added by “put”.
>
>
RE: A bug in SQL "CREATE TABLE" and its underlying Ignite cache
Posted by Cong Guo <co...@huawei.com>.
Hi,
How should I use BinaryObject in the CREATE TABLE statement? I try using BinaryObject.class.getName() as the value_type, but get the following exception:
class org.apache.ignite.IgniteCheckedException: Failed to initialize property 'ORGID' of type 'java.lang.Long' for key class 'class java.lang.Long' and value class 'interface org.apache.ignite.binary.BinaryObject'. Make sure that one of these classes contains respective getter method or field.
I want to use BinaryObject here for some flexibility.
Thanks,
Cong
From: Вячеслав Коптилин [mailto:slava.koptilin@gmail.com]
Sent: 2018年6月18日 18:10
To: user@ignite.apache.org
Subject: Re: A bug in SQL "CREATE TABLE" and its underlying Ignite cache
Hello,
It seems that the root cause of the issue is wrong values of 'KEY_TYPE' and 'VALUE_TYPE' parameters.
In your case, there is no need to specify 'KEY_TYPE' at all, and 'VALUE_TYPE' should Person.class.getName() I think.
Please try the following:
String createTableSQL = "CREATE TABLE Persons (id LONG, orgId LONG, firstName VARCHAR, lastName VARCHAR, resume VARCHAR, salary FLOAT, PRIMARY KEY(firstName))" +
"WITH \"BACKUPS=1, ATOMICITY=TRANSACTIONAL, WRITE_SYNCHRONIZATION_MODE=PRIMARY_SYNC, CACHE_NAME=" + PERSON_CACHE_NAME +
", VALUE_TYPE=" + Person.class.getName() + "\"";
Best regards,
Slava.
пн, 18 июн. 2018 г. в 21:50, Cong Guo <co...@huawei.com>>:
Hi,
I need to use both SQL and non-SQL APIs (key-value) on a single cache. I follow the document in:
https://apacheignite-sql.readme.io/docs/create-table
I use “CREATE TABLE” to create the table and its underlying cache. I can use both SQL “INSERT” and put to add data to the cache. However, when I run a SqlFieldsQuery, only the row added by SQL “INSERT” can be seen. The Ignite version is 2.4.0.
You can reproduce the bug using the following code:
CacheConfiguration<Integer, Integer> dummyCfg = new CacheConfiguration<>("DUMMY");
dummyCfg.setSqlSchema("PUBLIC");
try(IgniteCache<Integer, Integer> dummyCache = ignite.getOrCreateCache(dummyCfg)){
String createTableSQL = "CREATE TABLE Persons (id LONG, orgId LONG, firstName VARCHAR, lastName VARCHAR, resume VARCHAR, salary FLOAT, PRIMARY KEY(firstName))" +
"WITH \"BACKUPS=1, ATOMICITY=TRANSACTIONAL, WRITE_SYNCHRONIZATION_MODE=PRIMARY_SYNC, CACHE_NAME=" + PERSON_CACHE_NAME +
", KEY_TYPE=String, VALUE_TYPE=BinaryObject\"";
dummyCache.query(new SqlFieldsQuery(createTableSQL)).getAll();
SqlFieldsQuery firstInsert = new SqlFieldsQuery("INSERT INTO Persons (id, orgId, firstName, lastname, resume, salary) VALUES (?,?,?,?,?,?)");
firstInsert.setArgs(1L, 1L, "John", "Smith", "PhD", 10000.0d);
dummyCache.query(firstInsert).getAll();
try(IgniteCache<String, Person> personCache = ignite.cache(PERSON_CACHE_NAME)){
Person p2 = new Person(2L, 1L, "Hello", "World", "Master", 1000.0d);
personCache.put("Hello", p2);
IgniteCache<Long, BinaryObject> binaryCache = personCache.<Long, BinaryObject>withKeepBinary();
System.out.println("Size of the cache is: " + binaryCache.size(CachePeekMode.ALL));
binaryCache.query(new ScanQuery<>(null)).forEach(entry -> System.out.println(entry.getKey()));
System.out.println("Select results: ");
SqlFieldsQuery qry = new SqlFieldsQuery("select * from Persons");
QueryCursor<List<?>> answers = personCache.query(qry);
List<List<?>> personList = answers.getAll();
for(List<?> row : personList) {
String fn = (String)row.get(2);
System.out.println(fn);
}
}
}
The output is:
Size of the cache is: 2
Hello
String [idHash=213193302, hash=-900113201, FIRSTNAME=John]
Select results:
John
The bug is that the SqlFieldsQuery cannot see the data added by “put”.
Re: A bug in SQL "CREATE TABLE" and its underlying Ignite cache
Posted by Вячеслав Коптилин <sl...@gmail.com>.
Hello,
It seems that the root cause of the issue is wrong values of 'KEY_TYPE' and
'VALUE_TYPE' parameters.
In your case, there is no need to specify 'KEY_TYPE' at all, and
'VALUE_TYPE' should Person.class.getName() I think.
Please try the following:
String createTableSQL = "CREATE TABLE Persons (id LONG, orgId LONG,
firstName VARCHAR, lastName VARCHAR, resume VARCHAR, salary FLOAT, PRIMARY
KEY(firstName))" +
"WITH \"BACKUPS=1, ATOMICITY=TRANSACTIONAL,
WRITE_SYNCHRONIZATION_MODE=PRIMARY_SYNC, CACHE_NAME=" + PERSON_CACHE_NAME +
", VALUE_TYPE=" + Person.class.getName() + "\"";
Best regards,
Slava.
пн, 18 июн. 2018 г. в 21:50, Cong Guo <co...@huawei.com>:
> Hi,
>
>
>
> I need to use both SQL and non-SQL APIs (key-value) on a single cache. I
> follow the document in:
>
> https://apacheignite-sql.readme.io/docs/create-table
>
>
>
> I use “CREATE TABLE” to create the table and its underlying cache. I can
> use both SQL “INSERT” and put to add data to the cache. However, when I run
> a SqlFieldsQuery, only the row added by SQL “INSERT” can be seen. The
> Ignite version is 2.4.0.
>
>
>
> You can reproduce the bug using the following code:
>
>
>
> CacheConfiguration<Integer, Integer> dummyCfg = new
> CacheConfiguration<>("DUMMY");
>
>
> dummyCfg.setSqlSchema("PUBLIC");
>
>
>
> try(IgniteCache<Integer,
> Integer> dummyCache = ignite.getOrCreateCache(dummyCfg)){
>
> String
> createTableSQL = "CREATE TABLE Persons (id LONG, orgId LONG, firstName
> VARCHAR, lastName VARCHAR, resume VARCHAR, salary FLOAT, PRIMARY
> KEY(firstName))" +
>
>
> "WITH \"BACKUPS=1, ATOMICITY=TRANSACTIONAL,
> WRITE_SYNCHRONIZATION_MODE=PRIMARY_SYNC, CACHE_NAME=" + PERSON_CACHE_NAME +
>
>
> ", KEY_TYPE=String, VALUE_TYPE=BinaryObject\"";
>
>
>
>
> dummyCache.query(new SqlFieldsQuery(createTableSQL)).getAll();
>
>
>
>
> SqlFieldsQuery firstInsert = new SqlFieldsQuery("INSERT INTO Persons (id,
> orgId, firstName, lastname, resume, salary) VALUES (?,?,?,?,?,?)");
>
>
> firstInsert.setArgs(1L, 1L, "John", "Smith", "PhD", 10000.0d);
>
>
> dummyCache.query(firstInsert).getAll();
>
>
>
>
> try(IgniteCache<String, Person> personCache =
> ignite.cache(PERSON_CACHE_NAME)){
>
>
> Person p2 = new Person(2L, 1L, "Hello", "World", "Master", 1000.0d);
>
>
> personCache.put("Hello", p2);
>
>
>
>
> IgniteCache<Long, BinaryObject> binaryCache = personCache.<Long,
> BinaryObject>withKeepBinary();
>
>
> System.out.println("Size of the cache is: " +
> binaryCache.size(CachePeekMode.ALL));
>
>
>
>
>
> binaryCache.query(new ScanQuery<>(null)).forEach(entry ->
> System.out.println(entry.getKey()));
>
>
>
>
>
> System.out.println("Select results: ");
>
>
> SqlFieldsQuery qry = new SqlFieldsQuery("select * from Persons");
>
>
> QueryCursor<List<?>> answers = personCache.query(qry);
>
>
> List<List<?>> personList = answers.getAll();
>
>
> for(List<?> row : personList) {
>
>
> String fn = (String)row.get(2);
>
>
> System.out.println(fn);
>
>
> }
>
> }
>
> }
>
>
>
>
>
> The output is:
>
>
>
> Size of the cache is: 2
>
> Hello
>
> String [idHash=213193302, hash=-900113201, FIRSTNAME=John]
>
> Select results:
>
> John
>
>
>
> The bug is that the SqlFieldsQuery cannot see the data added by “put”.
>