INSERT into SELECT from Ignite 1.9 or 2.0

classic Classic list List threaded Threaded
6 messages Options
acet acet
Reply | Threaded
Open this post in threaded view
|

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/
Andrew Mashenkov Andrew Mashenkov
Reply | Threaded
Open this post in threaded view
|

Re: INSERT into SELECT from Ignite 1.9 or 2.0

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 <[hidden email]> 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
Regards,
Andrew.
Alexander Paschenko Alexander Paschenko
Reply | Threaded
Open this post in threaded view
|

Re: INSERT into SELECT from Ignite 1.9 or 2.0

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 <[hidden email]>:

> 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 <[hidden email]> 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
Alexander Paschenko Alexander Paschenko
Reply | Threaded
Open this post in threaded view
|

Re: INSERT into SELECT from Ignite 1.9 or 2.0

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
<[hidden email]>:

> 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 <[hidden email]>:
>> 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 <[hidden email]> 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

SequenceTest.java (5K) Download Attachment
Andrew Mashenkov Andrew Mashenkov
Reply | Threaded
Open this post in threaded view
|

Re: INSERT into SELECT from Ignite 1.9 or 2.0

In reply to this post by Alexander Paschenko
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(), ..."


On Mon, Sep 18, 2017 at 4:17 PM, Alexander Paschenko <[hidden email]> 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 <[hidden email]>:
> 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 <[hidden email]> 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
Regards,
Andrew.
dsetrakyan dsetrakyan
Reply | Threaded
Open this post in threaded view
|

Re: INSERT into SELECT from Ignite 1.9 or 2.0

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 <[hidden email]> 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(), ..."


On Mon, Sep 18, 2017 at 4:17 PM, Alexander Paschenko <[hidden email]> 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 <[hidden email]>:
> 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 <[hidden email]> 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