Due to an issue I posted about in a previous thread
http://apache-ignite-users.70518.x6.nabble.com/Basic-SQL-pagination-returning-incorrect-results-td35443.html
I've written a work around to use the streamer interface with a ScanQuery
to duplicate a cache.
Both are created from SQL using something like this:
repo.query("create table page1(a varchar, b varchar, c varchar,
PRIMARY KEY (a, b)) WITH \"cache_name=page1\"")
repo.query("create table page2(a varchar, b varchar, c varchar,
PRIMARY KEY (a, b)) WITH \"cache_name=page2\"")
The data is copied, printing the size shows 100 as expected in the test but
a SQL query on page2 table returns 0 rows.
def copied = repo.query("SELECT * FROM page2 LIMIT 101")
Gets nothing. The copy function used is below. I'm presuming I've missed a
step and the SQL index or something else is not being done. How should this
be written to duplicate all data from page1 into page2 table/cache.
public void copy(String fromTableName, String toTableName) {
var ignite = ctx.ignite;
try (
IgniteCache<Object, Object> from = ignite.cache(fromTableName);
IgniteCache<Object, Object> to = ignite.cache(toTableName)
) {
if (from == null || to == null) {
throw new IllegalArgumentException(format("Both from and to
tables must exist. from: %s, to: %s", fromTableName, toTableName));
}
try (
IgniteDataStreamer<Object, Object> strmr =
ignite.dataStreamer(toTableName/*from.getName()*/);
var cursor = from.withKeepBinary().query(new ScanQuery<>())
) {
strmr.allowOverwrite(true);
strmr.keepBinary(true);
//strmr.receiver(StreamVisitor.from((cache, e) ->
to.put(e.getKey(), e.getValue())));
for (Cache.Entry<Object, Object> e : cursor) {
strmr.addData(e.getKey(), e.getValue());
}
//strmr.flush();
}
log.info("Total in target cache {}", to.sizeLong(CachePeekMode.ALL));
}
}
Regards,
Courtney Robinson
Founder and CEO, Hypi
Tel: ++44 208 123 2413 (GMT+0) <https://hypi.io>
<https://hypi.io>
https://hypi.io
Hello!
It will be available in cache's Query Entities so you can try:
((CacheConfiguration<Object, Object>)ignite.cache("SQL_PUBLIC_<tablename>")
.getConfiguration(CacheConfiguration.class)).getQueryEntities().iterator().next().getValueType()
Regards,
--
Ilya Kasnacheev
чт, 18 февр. 2021 г. в 11:40, Courtney Robinson <co...@hypi.io>:
> Hi Illya,
> Thanks for responding.
> That makes sense - I figured something like that but didn't know exactly
> what.
> Is it possible to get the existing key_type and value_type for tables?
> The reason is because we have tables in production and they were not
> created with key_type and value_type. We actually thought this only applied
> when you use Java classes with annotations.
>
> In the SYS table somewhere perhaps?
>
>
> On Mon, Feb 15, 2021 at 11:19 AM Ilya Kasnacheev <
> ilya.kasnacheev@gmail.com> wrote:
>
>> Hello!
>>
>> Two tables have different name of an indexed binary type by default.
>>
>> Try
>> repo.query("create table page1(a varchar, b varchar, c varchar, PRIMARY
>> KEY (a, b)) WITH \"cache_name=page1, key_type=PageKey, value_type=Page\"")
>> repo.query("create table page2(a varchar, b varchar, c varchar, PRIMARY
>> KEY (a, b)) WITH \"cache_name=page2, key_type=PageKey, value_type=Page\"")
>>
>> Regards,
>> --
>> Ilya Kasnacheev
>>
>>
>> сб, 13 февр. 2021 г. в 19:32, Courtney Robinson <
>> courtney.robinson@hypi.io>:
>>
>>> Due to an issue I posted about in a previous thread
>>> http://apache-ignite-users.70518.x6.nabble.com/Basic-SQL-pagination-returning-incorrect-results-td35443.html
>>>
>>> I've written a work around to use the streamer interface with a
>>> ScanQuery to duplicate a cache.
>>> Both are created from SQL using something like this:
>>>
>>> repo.query("create table page1(a varchar, b varchar, c varchar, PRIMARY KEY (a, b)) WITH \"cache_name=page1\"")
>>> repo.query("create table page2(a varchar, b varchar, c varchar, PRIMARY KEY (a, b)) WITH \"cache_name=page2\"")
>>>
>>> The data is copied, printing the size shows 100 as expected in the test
>>> but a SQL query on page2 table returns 0 rows.
>>>
>>> def copied = repo.query("SELECT * FROM page2 LIMIT 101")
>>>
>>> Gets nothing. The copy function used is below. I'm presuming I've missed
>>> a step and the SQL index or something else is not being done. How should
>>> this be written to duplicate all data from page1 into page2 table/cache.
>>>
>>> public void copy(String fromTableName, String toTableName) {
>>> var ignite = ctx.ignite;
>>> try (
>>> IgniteCache<Object, Object> from = ignite.cache(fromTableName);
>>> IgniteCache<Object, Object> to = ignite.cache(toTableName)
>>> ) {
>>> if (from == null || to == null) {
>>> throw new IllegalArgumentException(format("Both from and to tables must exist. from: %s, to: %s", fromTableName, toTableName));
>>> }
>>> try (
>>> IgniteDataStreamer<Object, Object> strmr = ignite.dataStreamer(toTableName/*from.getName()*/);
>>> var cursor = from.withKeepBinary().query(new ScanQuery<>())
>>> ) {
>>> strmr.allowOverwrite(true);
>>> strmr.keepBinary(true);
>>> //strmr.receiver(StreamVisitor.from((cache, e) -> to.put(e.getKey(), e.getValue())));
>>> for (Cache.Entry<Object, Object> e : cursor) {
>>> strmr.addData(e.getKey(), e.getValue());
>>> }
>>> //strmr.flush();
>>> }
>>> log.info("Total in target cache {}", to.sizeLong(CachePeekMode.ALL));
>>> }
>>> }
>>>
>>>
>>>
>>> Regards,
>>> Courtney Robinson
>>> Founder and CEO, Hypi
>>> Tel: ++44 208 123 2413 (GMT+0) <https://hypi.io>
>>>
>>> <https://hypi.io>
>>> https://hypi.io
>>>
>>
Hi Illya,
Thanks for responding.
That makes sense - I figured something like that but didn't know exactly
what.
Is it possible to get the existing key_type and value_type for tables?
The reason is because we have tables in production and they were not
created with key_type and value_type. We actually thought this only applied
when you use Java classes with annotations.
In the SYS table somewhere perhaps?
On Mon, Feb 15, 2021 at 11:19 AM Ilya Kasnacheev <il...@gmail.com>
wrote:
> Hello!
>
> Two tables have different name of an indexed binary type by default.
>
> Try
> repo.query("create table page1(a varchar, b varchar, c varchar, PRIMARY
> KEY (a, b)) WITH \"cache_name=page1, key_type=PageKey, value_type=Page\"")
> repo.query("create table page2(a varchar, b varchar, c varchar, PRIMARY
> KEY (a, b)) WITH \"cache_name=page2, key_type=PageKey, value_type=Page\"")
>
> Regards,
> --
> Ilya Kasnacheev
>
>
> сб, 13 февр. 2021 г. в 19:32, Courtney Robinson <courtney.robinson@hypi.io
> >:
>
>> Due to an issue I posted about in a previous thread
>> http://apache-ignite-users.70518.x6.nabble.com/Basic-SQL-pagination-returning-incorrect-results-td35443.html
>>
>> I've written a work around to use the streamer interface with a ScanQuery
>> to duplicate a cache.
>> Both are created from SQL using something like this:
>>
>> repo.query("create table page1(a varchar, b varchar, c varchar, PRIMARY KEY (a, b)) WITH \"cache_name=page1\"")
>> repo.query("create table page2(a varchar, b varchar, c varchar, PRIMARY KEY (a, b)) WITH \"cache_name=page2\"")
>>
>> The data is copied, printing the size shows 100 as expected in the test
>> but a SQL query on page2 table returns 0 rows.
>>
>> def copied = repo.query("SELECT * FROM page2 LIMIT 101")
>>
>> Gets nothing. The copy function used is below. I'm presuming I've missed
>> a step and the SQL index or something else is not being done. How should
>> this be written to duplicate all data from page1 into page2 table/cache.
>>
>> public void copy(String fromTableName, String toTableName) {
>> var ignite = ctx.ignite;
>> try (
>> IgniteCache<Object, Object> from = ignite.cache(fromTableName);
>> IgniteCache<Object, Object> to = ignite.cache(toTableName)
>> ) {
>> if (from == null || to == null) {
>> throw new IllegalArgumentException(format("Both from and to tables must exist. from: %s, to: %s", fromTableName, toTableName));
>> }
>> try (
>> IgniteDataStreamer<Object, Object> strmr = ignite.dataStreamer(toTableName/*from.getName()*/);
>> var cursor = from.withKeepBinary().query(new ScanQuery<>())
>> ) {
>> strmr.allowOverwrite(true);
>> strmr.keepBinary(true);
>> //strmr.receiver(StreamVisitor.from((cache, e) -> to.put(e.getKey(), e.getValue())));
>> for (Cache.Entry<Object, Object> e : cursor) {
>> strmr.addData(e.getKey(), e.getValue());
>> }
>> //strmr.flush();
>> }
>> log.info("Total in target cache {}", to.sizeLong(CachePeekMode.ALL));
>> }
>> }
>>
>>
>>
>> Regards,
>> Courtney Robinson
>> Founder and CEO, Hypi
>> Tel: ++44 208 123 2413 (GMT+0) <https://hypi.io>
>>
>> <https://hypi.io>
>> https://hypi.io
>>
>
Hello!
Two tables have different name of an indexed binary type by default.
Try
repo.query("create table page1(a varchar, b varchar, c varchar, PRIMARY KEY
(a, b)) WITH \"cache_name=page1, key_type=PageKey, value_type=Page\"")
repo.query("create table page2(a varchar, b varchar, c varchar, PRIMARY KEY
(a, b)) WITH \"cache_name=page2, key_type=PageKey, value_type=Page\"")
Regards,
--
Ilya Kasnacheev
сб, 13 февр. 2021 г. в 19:32, Courtney Robinson <co...@hypi.io>:
> Due to an issue I posted about in a previous thread
> http://apache-ignite-users.70518.x6.nabble.com/Basic-SQL-pagination-returning-incorrect-results-td35443.html
>
> I've written a work around to use the streamer interface with a ScanQuery
> to duplicate a cache.
> Both are created from SQL using something like this:
>
> repo.query("create table page1(a varchar, b varchar, c varchar, PRIMARY KEY (a, b)) WITH \"cache_name=page1\"")
> repo.query("create table page2(a varchar, b varchar, c varchar, PRIMARY KEY (a, b)) WITH \"cache_name=page2\"")
>
> The data is copied, printing the size shows 100 as expected in the test
> but a SQL query on page2 table returns 0 rows.
>
> def copied = repo.query("SELECT * FROM page2 LIMIT 101")
>
> Gets nothing. The copy function used is below. I'm presuming I've missed a
> step and the SQL index or something else is not being done. How should this
> be written to duplicate all data from page1 into page2 table/cache.
>
> public void copy(String fromTableName, String toTableName) {
> var ignite = ctx.ignite;
> try (
> IgniteCache<Object, Object> from = ignite.cache(fromTableName);
> IgniteCache<Object, Object> to = ignite.cache(toTableName)
> ) {
> if (from == null || to == null) {
> throw new IllegalArgumentException(format("Both from and to tables must exist. from: %s, to: %s", fromTableName, toTableName));
> }
> try (
> IgniteDataStreamer<Object, Object> strmr = ignite.dataStreamer(toTableName/*from.getName()*/);
> var cursor = from.withKeepBinary().query(new ScanQuery<>())
> ) {
> strmr.allowOverwrite(true);
> strmr.keepBinary(true);
> //strmr.receiver(StreamVisitor.from((cache, e) -> to.put(e.getKey(), e.getValue())));
> for (Cache.Entry<Object, Object> e : cursor) {
> strmr.addData(e.getKey(), e.getValue());
> }
> //strmr.flush();
> }
> log.info("Total in target cache {}", to.sizeLong(CachePeekMode.ALL));
> }
> }
>
>
>
> Regards,
> Courtney Robinson
> Founder and CEO, Hypi
> Tel: ++44 208 123 2413 (GMT+0) <https://hypi.io>
>
> <https://hypi.io>
> https://hypi.io
>