You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by acet <ad...@gmail.com> on 2017/09/18 09:31:47 UTC
INSERT into SELECT from Ignite 1.9 or 2.0
Hello,
I would like to insert the result of a select query into a cache in ignite.
Something like:
INSERT INTO "new_cache_name".NewCacheDataType(ID, CUSTOMERID, PRODUCTNAME)
(SELECT {?}, c.id, p.product_name
FROM "customers".CUSTOMER as c
JOIN "products".PRODUCT as p
ON c.id = p.customer_id)
in the place of the {?} i would like to put in something similar to
AtomicSequence, however seeing as this will be work done without using the
client I cannot tell how this is possible.
Can someone advise if this can be done, and if so, how?
Thanks.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: INSERT into SELECT from Ignite 1.9 or 2.0
Posted by Alexander Paschenko <al...@gmail.com>.
Hi,
Here's an example how to implement this using sequences and SQL functions.
Please note how I refer to node name here - most likely you'll have to
tweak this thing.
- Alex
2017-09-18 16:17 GMT+03:00 Alexander Paschenko
<al...@gmail.com>:
> Hello,
>
> Andrey, I believe you're wrong. INSERT from SELECT should work. AUTO
> INCREMENT columns indeed are not supported for now though, it's true.
>
> - Alex
>
> 2017-09-18 16:09 GMT+03:00 Andrey Mashenkov <an...@gmail.com>:
>> Hi,
>>
>> Auto-increment fields are not supported yet. Here is a ticket for this [1]
>> and you can track it's state.
>> Moreover, underlying H2 doesn't support SELECT with JOINs nested into
>> INSERT\UPDATE query.
>>
>> [1] https://issues.apache.org/jira/browse/IGNITE-5625
>>
>> On Mon, Sep 18, 2017 at 12:31 PM, acet <ad...@gmail.com> wrote:
>>>
>>> Hello,
>>> I would like to insert the result of a select query into a cache in
>>> ignite.
>>> Something like:
>>>
>>> INSERT INTO "new_cache_name".NewCacheDataType(ID, CUSTOMERID, PRODUCTNAME)
>>> (SELECT {?}, c.id, p.product_name
>>> FROM "customers".CUSTOMER as c
>>> JOIN "products".PRODUCT as p
>>> ON c.id = p.customer_id)
>>>
>>> in the place of the {?} i would like to put in something similar to
>>> AtomicSequence, however seeing as this will be work done without using the
>>> client I cannot tell how this is possible.
>>> Can someone advise if this can be done, and if so, how?
>>>
>>> Thanks.
>>>
>>>
>>>
>>> --
>>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>
>>
>>
>>
>> --
>> Best regards,
>> Andrey V. Mashenkov
Re: INSERT into SELECT from Ignite 1.9 or 2.0
Posted by Dmitriy Setrakyan <ds...@apache.org>.
To add to Andrey's example, here is how you would use IgniteAtomicSequence
to make IDs unique across the whole distributed cluster:
*public static class CustomSQLFunctions {*
* @QuerySqlFunction*
* public static long nextId(String seqName, long initVal) {*
* return Ignition.ignite().atomicSequence("idGen", 0,
true).incrementAndGet();*
* }*
* }*
On Thu, Sep 21, 2017 at 5:37 AM, Andrey Mashenkov <
andrey.mashenkov@gmail.com> wrote:
> Hi,
>
> As a workaround you can implement custom function [1] for unique number
> generation.
>
> 1.You need to create a class with static functions annotated with
> @QuerySqlFunction.
>
> E.g. for single node grid you can use some AtomicLong static field.
>
>
> public class MyFunctions {
>
> static AtomicLong seq = new AtomicLong();
>
>
> @QuerySqlFunction
> public static long nextID() {
> return seq.getAndIncrement();
> }
> }
>
>
> This class should be added to classpath on all nodes.
>
> 2.Register class with functions.
>
> cacheConfiguration.setSqlFunctionClasses(MyFunctions.class);
>
>
> 3. For multi-node grid you use IgniteAtomicSequence instead and
> initialize static variable on grid start, e.g. manually or via
> LifecycleBean [2].
>
> 4. Now you can run query like "INSERT ... (ID, ...) SELECT nextID(), ..."
>
> [1] https://apacheignite.readme.io/docs/miscellaneous-
> features#custom-sql-functions
> [2] https://apacheignite.readme.io/docs/ignite-life-
> cycle#section-lifecyclebean
>
> On Mon, Sep 18, 2017 at 4:17 PM, Alexander Paschenko <
> alexander.a.paschenko@gmail.com> wrote:
>
>> Hello,
>>
>> Andrey, I believe you're wrong. INSERT from SELECT should work. AUTO
>> INCREMENT columns indeed are not supported for now though, it's true.
>>
>> - Alex
>>
>> 2017-09-18 16:09 GMT+03:00 Andrey Mashenkov <an...@gmail.com>:
>> > Hi,
>> >
>> > Auto-increment fields are not supported yet. Here is a ticket for this
>> [1]
>> > and you can track it's state.
>> > Moreover, underlying H2 doesn't support SELECT with JOINs nested into
>> > INSERT\UPDATE query.
>> >
>> > [1] https://issues.apache.org/jira/browse/IGNITE-5625
>> >
>> > On Mon, Sep 18, 2017 at 12:31 PM, acet <ad...@gmail.com>
>> wrote:
>> >>
>> >> Hello,
>> >> I would like to insert the result of a select query into a cache in
>> >> ignite.
>> >> Something like:
>> >>
>> >> INSERT INTO "new_cache_name".NewCacheDataType(ID, CUSTOMERID,
>> PRODUCTNAME)
>> >> (SELECT {?}, c.id, p.product_name
>> >> FROM "customers".CUSTOMER as c
>> >> JOIN "products".PRODUCT as p
>> >> ON c.id = p.customer_id)
>> >>
>> >> in the place of the {?} i would like to put in something similar to
>> >> AtomicSequence, however seeing as this will be work done without using
>> the
>> >> client I cannot tell how this is possible.
>> >> Can someone advise if this can be done, and if so, how?
>> >>
>> >> Thanks.
>> >>
>> >>
>> >>
>> >> --
>> >> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>> >
>> >
>> >
>> >
>> > --
>> > Best regards,
>> > Andrey V. Mashenkov
>>
>
>
>
> --
> Best regards,
> Andrey V. Mashenkov
>
Re: INSERT into SELECT from Ignite 1.9 or 2.0
Posted by Andrey Mashenkov <an...@gmail.com>.
Hi,
As a workaround you can implement custom function [1] for unique number
generation.
1.You need to create a class with static functions annotated with
@QuerySqlFunction.
E.g. for single node grid you can use some AtomicLong static field.
public class MyFunctions {
static AtomicLong seq = new AtomicLong();
@QuerySqlFunction
public static long nextID() {
return seq.getAndIncrement();
}
}
This class should be added to classpath on all nodes.
2.Register class with functions.
cacheConfiguration.setSqlFunctionClasses(MyFunctions.class);
3. For multi-node grid you use IgniteAtomicSequence instead and initialize
static variable on grid start, e.g. manually or via LifecycleBean [2].
4. Now you can run query like "INSERT ... (ID, ...) SELECT nextID(), ..."
[1]
https://apacheignite.readme.io/docs/miscellaneous-features#custom-sql-functions
[2]
https://apacheignite.readme.io/docs/ignite-life-cycle#section-lifecyclebean
On Mon, Sep 18, 2017 at 4:17 PM, Alexander Paschenko <
alexander.a.paschenko@gmail.com> wrote:
> Hello,
>
> Andrey, I believe you're wrong. INSERT from SELECT should work. AUTO
> INCREMENT columns indeed are not supported for now though, it's true.
>
> - Alex
>
> 2017-09-18 16:09 GMT+03:00 Andrey Mashenkov <an...@gmail.com>:
> > Hi,
> >
> > Auto-increment fields are not supported yet. Here is a ticket for this
> [1]
> > and you can track it's state.
> > Moreover, underlying H2 doesn't support SELECT with JOINs nested into
> > INSERT\UPDATE query.
> >
> > [1] https://issues.apache.org/jira/browse/IGNITE-5625
> >
> > On Mon, Sep 18, 2017 at 12:31 PM, acet <ad...@gmail.com>
> wrote:
> >>
> >> Hello,
> >> I would like to insert the result of a select query into a cache in
> >> ignite.
> >> Something like:
> >>
> >> INSERT INTO "new_cache_name".NewCacheDataType(ID, CUSTOMERID,
> PRODUCTNAME)
> >> (SELECT {?}, c.id, p.product_name
> >> FROM "customers".CUSTOMER as c
> >> JOIN "products".PRODUCT as p
> >> ON c.id = p.customer_id)
> >>
> >> in the place of the {?} i would like to put in something similar to
> >> AtomicSequence, however seeing as this will be work done without using
> the
> >> client I cannot tell how this is possible.
> >> Can someone advise if this can be done, and if so, how?
> >>
> >> Thanks.
> >>
> >>
> >>
> >> --
> >> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
> >
> >
> >
> >
> > --
> > Best regards,
> > Andrey V. Mashenkov
>
--
Best regards,
Andrey V. Mashenkov
Re: INSERT into SELECT from Ignite 1.9 or 2.0
Posted by Alexander Paschenko <al...@gmail.com>.
Hello,
Andrey, I believe you're wrong. INSERT from SELECT should work. AUTO
INCREMENT columns indeed are not supported for now though, it's true.
- Alex
2017-09-18 16:09 GMT+03:00 Andrey Mashenkov <an...@gmail.com>:
> Hi,
>
> Auto-increment fields are not supported yet. Here is a ticket for this [1]
> and you can track it's state.
> Moreover, underlying H2 doesn't support SELECT with JOINs nested into
> INSERT\UPDATE query.
>
> [1] https://issues.apache.org/jira/browse/IGNITE-5625
>
> On Mon, Sep 18, 2017 at 12:31 PM, acet <ad...@gmail.com> wrote:
>>
>> Hello,
>> I would like to insert the result of a select query into a cache in
>> ignite.
>> Something like:
>>
>> INSERT INTO "new_cache_name".NewCacheDataType(ID, CUSTOMERID, PRODUCTNAME)
>> (SELECT {?}, c.id, p.product_name
>> FROM "customers".CUSTOMER as c
>> JOIN "products".PRODUCT as p
>> ON c.id = p.customer_id)
>>
>> in the place of the {?} i would like to put in something similar to
>> AtomicSequence, however seeing as this will be work done without using the
>> client I cannot tell how this is possible.
>> Can someone advise if this can be done, and if so, how?
>>
>> Thanks.
>>
>>
>>
>> --
>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
>
>
>
> --
> Best regards,
> Andrey V. Mashenkov
Re: INSERT into SELECT from Ignite 1.9 or 2.0
Posted by Andrey Mashenkov <an...@gmail.com>.
Hi,
Auto-increment fields are not supported yet. Here is a ticket for this [1]
and you can track it's state.
Moreover, underlying H2 doesn't support SELECT with JOINs nested into
INSERT\UPDATE query.
[1] https://issues.apache.org/jira/browse/IGNITE-5625
On Mon, Sep 18, 2017 at 12:31 PM, acet <ad...@gmail.com> wrote:
> Hello,
> I would like to insert the result of a select query into a cache in ignite.
> Something like:
>
> INSERT INTO "new_cache_name".NewCacheDataType(ID, CUSTOMERID, PRODUCTNAME)
> (SELECT {?}, c.id, p.product_name
> FROM "customers".CUSTOMER as c
> JOIN "products".PRODUCT as p
> ON c.id = p.customer_id)
>
> in the place of the {?} i would like to put in something similar to
> AtomicSequence, however seeing as this will be work done without using the
> client I cannot tell how this is possible.
> Can someone advise if this can be done, and if so, how?
>
> Thanks.
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
--
Best regards,
Andrey V. Mashenkov