You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Sriveena Mattaparthi <Sr...@ekaplus.com> on 2018/07/23 12:09:41 UTC
Batch insert into ignite
Hi,
How can we perform batch insert/update into ignite tables (created through sqlquery fields as create table)?
Thanks & Regards,
Sriveena
RE: Batch insert into ignite
Posted by Sriveena Mattaparthi <Sr...@ekaplus.com>.
Thanks Mikhail...will try the same and get back to you..
-----Original Message-----
From: Mikhail [mailto:michael.cherkasov@gmail.com]
Sent: Monday, July 23, 2018 10:32 PM
To: user@ignite.apache.org
Subject: Re: Batch insert into ignite
Hi Sriveena,
for data load you can try to use streaming mode for jdbc:
https://apac01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fapacheignite.readme.io%2Fdocs%2Fjdbc-driver%23section-streaming-mode&data=02%7C01%7CSriveena.Mattaparthi%40ekaplus.com%7Cd2ff72f68e13451756c008d5f0be11d6%7C2a5b4e9716be4be4b2d40f3fcb3d373c%7C1%7C0%7C636679621511638203&sdata=QGfXouNn0Juxv18j1p0rgnBx%2Fle90Ioo3E6W3S6ohR8%3D&reserved=0
it will provide faster insert operation compare to regular insert, also you
can disable WAL:
https://apac01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fapacheignite.readme.io%2Fdocs%2Fwrite-ahead-log%23section-wal-activation-and-deactivation&data=02%7C01%7CSriveena.Mattaparthi%40ekaplus.com%7Cd2ff72f68e13451756c008d5f0be11d6%7C2a5b4e9716be4be4b2d40f3fcb3d373c%7C1%7C0%7C636679621511638203&sdata=pjwp4tCzN9KnUq8s0cvTbmmaIDVj8O4UPylv7acYEUg%3D&reserved=0
to get better throughput.
Thanks,
Mike.
--
Sent from: https://apac01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fapache-ignite-users.70518.x6.nabble.com%2F&data=02%7C01%7CSriveena.Mattaparthi%40ekaplus.com%7Cd2ff72f68e13451756c008d5f0be11d6%7C2a5b4e9716be4be4b2d40f3fcb3d373c%7C1%7C0%7C636679621511638203&sdata=0Z7q%2BpGVuPa2rpB4wjYg4tKt5MPDYi4denqAbskOtZg%3D&reserved=0
RE: Batch insert into ignite
Posted by Sriveena Mattaparthi <Sr...@ekaplus.com>.
Thanks Michael…I have a question here…
1. Will the set/copy options mentioned work for SQL Query fields? Something like
. Cache.query(new SQLQueryFields(“SET STREAMING ON”))
2. IgniteDataStreamer streams data and add to cache without using JDBC driver.
But we would want to add the data stream into insert table. Is this possible?
Regards,
Sriveena
From: Michael Cherkasov [mailto:michael.cherkasov@gmail.com]
Sent: Wednesday, July 25, 2018 6:23 PM
To: user@ignite.apache.org
Subject: Re: Batch insert into ignite
Hi again,
One more option as pure sql solution:
https://apacheignite-sql.readme.io/docs/copy<https://apac01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fapacheignite-sql.readme.io%2Fdocs%2Fcopy&data=02%7C01%7CSriveena.Mattaparthi%40ekaplus.com%7Cb6b82626c30540c84a7208d5f2305410%7C2a5b4e9716be4be4b2d40f3fcb3d373c%7C1%7C0%7C636681211752818655&sdata=jn%2BOF9H0ydUVqQ8reY6dkQAFoBbq%2FpOyQWpkPYgWXLc%3D&reserved=0>
https://apacheignite-sql.readme.io/docs/set<https://apac01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fapacheignite-sql.readme.io%2Fdocs%2Fset&data=02%7C01%7CSriveena.Mattaparthi%40ekaplus.com%7Cb6b82626c30540c84a7208d5f2305410%7C2a5b4e9716be4be4b2d40f3fcb3d373c%7C1%7C0%7C636681211752818655&sdata=18Gr7V7L7mB7Mj9dUbl0d4LqdoDVODrrA6w43XkiJVg%3D&reserved=0>
However, IgniteDataStreamer should work faster, because as I know, these SQL commands have data streamer as underlying implementation.
Thanks,
Mike.
2018-07-25 7:02 GMT+03:00 Sriveena Mattaparthi <Sr...@ekaplus.com>>:
Thanks for the clarification Michael..will try the streamer option.
Regards,
Sriveena
From: Michael Cherkasov [mailto:michael.cherkasov@gmail.com<ma...@gmail.com>]
Sent: Tuesday, July 24, 2018 9:49 PM
To: user@ignite.apache.org<ma...@ignite.apache.org>
Subject: Re: Batch insert into ignite
I can't say for sure which one should be better for sure, it must be benchmarked.
But if you can use SqlFieldsQuery this means that you can use native Ignite API, right? So, in this case, IgniteDataStreamer<https://apac01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fapacheignite.readme.io%2Fdocs%2Fdata-streamers&data=02%7C01%7CSriveena.Mattaparthi%40ekaplus.com%7Cd7a2e5237e04417740a408d5f1814956%7C2a5b4e9716be4be4b2d40f3fcb3d373c%7C1%7C0%7C636680459959631292&sdata=Vm%2BmWgmalYFrA6O9fZEWPzW0XRF%2BOYFVaAIwus%2FNU5k%3D&reserved=0> would be the best choice.
2018-07-24 9:36 GMT+03:00 Sriveena Mattaparthi <Sr...@ekaplus.com>>:
Hi Mikhail,
Do we have this streaming/batch insert option, if we do a bulk insert using
cache.query(new SqlFieldsQuery("sql");
Also could you please confirm which one would perform better
1. Insert using IgniteJdbcDriver
2. Insert using SqlFieldsQuery
Thanks & Regards,
Sriveena
-----Original Message-----
From: Mikhail [mailto:michael.cherkasov@gmail.com<ma...@gmail.com>]
Sent: Monday, July 23, 2018 10:32 PM
To: user@ignite.apache.org<ma...@ignite.apache.org>
Subject: Re: Batch insert into ignite
Hi Sriveena,
for data load you can try to use streaming mode for jdbc:
https://apac01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fapacheignite.readme.io%2Fdocs%2Fjdbc-driver%23section-streaming-mode&data=02%7C01%7CSriveena.Mattaparthi%40ekaplus.com%7Cd2ff72f68e13451756c008d5f0be11d6%7C2a5b4e9716be4be4b2d40f3fcb3d373c%7C1%7C0%7C636679621511638203&sdata=QGfXouNn0Juxv18j1p0rgnBx%2Fle90Ioo3E6W3S6ohR8%3D&reserved=0
it will provide faster insert operation compare to regular insert, also you
can disable WAL:
https://apac01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fapacheignite.readme.io%2Fdocs%2Fwrite-ahead-log%23section-wal-activation-and-deactivation&data=02%7C01%7CSriveena.Mattaparthi%40ekaplus.com%7Cd2ff72f68e13451756c008d5f0be11d6%7C2a5b4e9716be4be4b2d40f3fcb3d373c%7C1%7C0%7C636679621511638203&sdata=pjwp4tCzN9KnUq8s0cvTbmmaIDVj8O4UPylv7acYEUg%3D&reserved=0
to get better throughput.
Thanks,
Mike.
--
Sent from: https://apac01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fapache-ignite-users.70518.x6.nabble.com%2F&data=02%7C01%7CSriveena.Mattaparthi%40ekaplus.com%7Cd2ff72f68e13451756c008d5f0be11d6%7C2a5b4e9716be4be4b2d40f3fcb3d373c%7C1%7C0%7C636679621511638203&sdata=0Z7q%2BpGVuPa2rpB4wjYg4tKt5MPDYi4denqAbskOtZg%3D&reserved=0
Re: Batch insert into ignite
Posted by Michael Cherkasov <mi...@gmail.com>.
Hi again,
One more option as pure sql solution:
https://apacheignite-sql.readme.io/docs/copy
https://apacheignite-sql.readme.io/docs/set
However, IgniteDataStreamer should work faster, because as I know, these
SQL commands have data streamer as underlying implementation.
Thanks,
Mike.
2018-07-25 7:02 GMT+03:00 Sriveena Mattaparthi <
Sriveena.Mattaparthi@ekaplus.com>:
> Thanks for the clarification Michael..will try the streamer option.
>
>
>
> Regards,
>
> Sriveena
>
>
>
> *From:* Michael Cherkasov [mailto:michael.cherkasov@gmail.com]
> *Sent:* Tuesday, July 24, 2018 9:49 PM
>
> *To:* user@ignite.apache.org
> *Subject:* Re: Batch insert into ignite
>
>
>
> I can't say for sure which one should be better for sure, it must be
> benchmarked.
>
> But if you can use SqlFieldsQuery this means that you can use native
> Ignite API, right? So, in this case, IgniteDataStreamer
> <https://apac01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fapacheignite.readme.io%2Fdocs%2Fdata-streamers&data=02%7C01%7CSriveena.Mattaparthi%40ekaplus.com%7Cd7a2e5237e04417740a408d5f1814956%7C2a5b4e9716be4be4b2d40f3fcb3d373c%7C1%7C0%7C636680459959631292&sdata=Vm%2BmWgmalYFrA6O9fZEWPzW0XRF%2BOYFVaAIwus%2FNU5k%3D&reserved=0>
> would be the best choice.
>
>
>
>
>
> 2018-07-24 9:36 GMT+03:00 Sriveena Mattaparthi <
> Sriveena.Mattaparthi@ekaplus.com>:
>
> Hi Mikhail,
>
> Do we have this streaming/batch insert option, if we do a bulk insert
> using
> cache.query(new SqlFieldsQuery("sql");
>
> Also could you please confirm which one would perform better
> 1. Insert using IgniteJdbcDriver
> 2. Insert using SqlFieldsQuery
>
> Thanks & Regards,
> Sriveena
>
> -----Original Message-----
> From: Mikhail [mailto:michael.cherkasov@gmail.com]
> Sent: Monday, July 23, 2018 10:32 PM
> To: user@ignite.apache.org
> Subject: Re: Batch insert into ignite
>
> Hi Sriveena,
>
> for data load you can try to use streaming mode for jdbc:
> https://apac01.safelinks.protection.outlook.com/?url=
> https%3A%2F%2Fapacheignite.readme.io%2Fdocs%2Fjdbc-
> driver%23section-streaming-mode&data=02%7C01%7CSriveena.Mattaparthi%
> 40ekaplus.com%7Cd2ff72f68e13451756c008d5f0be11d6%
> 7C2a5b4e9716be4be4b2d40f3fcb3d373c%7C1%7C0%7C636679621511638203&sdata=
> QGfXouNn0Juxv18j1p0rgnBx%2Fle90Ioo3E6W3S6ohR8%3D&reserved=0
> it will provide faster insert operation compare to regular insert, also you
> can disable WAL:
> https://apac01.safelinks.protection.outlook.com/?url=
> https%3A%2F%2Fapacheignite.readme.io%2Fdocs%2Fwrite-
> ahead-log%23section-wal-activation-and-deactivation&
> amp;data=02%7C01%7CSriveena.Mattaparthi%40ekaplus.com%
> 7Cd2ff72f68e13451756c008d5f0be11d6%7C2a5b4e9716be4be4b2d40f3fcb3d
> 373c%7C1%7C0%7C636679621511638203&sdata=pjwp4tCzN9KnUq8s0cvTbmmaIDVj8O
> 4UPylv7acYEUg%3D&reserved=0
> to get better throughput.
>
> Thanks,
> Mike.
>
>
>
> --
>
> Sent from: https://apac01.safelinks.protection.outlook.com/?url=
> http%3A%2F%2Fapache-ignite-users.70518.x6.nabble.com%2F&
> amp;data=02%7C01%7CSriveena.Mattaparthi%40ekaplus.com%
> 7Cd2ff72f68e13451756c008d5f0be11d6%7C2a5b4e9716be4be4b2d40f3fcb3d
> 373c%7C1%7C0%7C636679621511638203&sdata=0Z7q%
> 2BpGVuPa2rpB4wjYg4tKt5MPDYi4denqAbskOtZg%3D&reserved=0
>
>
>
RE: Batch insert into ignite
Posted by Sriveena Mattaparthi <Sr...@ekaplus.com>.
Thanks for the clarification Michael..will try the streamer option.
Regards,
Sriveena
From: Michael Cherkasov [mailto:michael.cherkasov@gmail.com]
Sent: Tuesday, July 24, 2018 9:49 PM
To: user@ignite.apache.org
Subject: Re: Batch insert into ignite
I can't say for sure which one should be better for sure, it must be benchmarked.
But if you can use SqlFieldsQuery this means that you can use native Ignite API, right? So, in this case, IgniteDataStreamer<https://apac01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fapacheignite.readme.io%2Fdocs%2Fdata-streamers&data=02%7C01%7CSriveena.Mattaparthi%40ekaplus.com%7Cd7a2e5237e04417740a408d5f1814956%7C2a5b4e9716be4be4b2d40f3fcb3d373c%7C1%7C0%7C636680459959631292&sdata=Vm%2BmWgmalYFrA6O9fZEWPzW0XRF%2BOYFVaAIwus%2FNU5k%3D&reserved=0> would be the best choice.
2018-07-24 9:36 GMT+03:00 Sriveena Mattaparthi <Sr...@ekaplus.com>>:
Hi Mikhail,
Do we have this streaming/batch insert option, if we do a bulk insert using
cache.query(new SqlFieldsQuery("sql");
Also could you please confirm which one would perform better
1. Insert using IgniteJdbcDriver
2. Insert using SqlFieldsQuery
Thanks & Regards,
Sriveena
-----Original Message-----
From: Mikhail [mailto:michael.cherkasov@gmail.com<ma...@gmail.com>]
Sent: Monday, July 23, 2018 10:32 PM
To: user@ignite.apache.org<ma...@ignite.apache.org>
Subject: Re: Batch insert into ignite
Hi Sriveena,
for data load you can try to use streaming mode for jdbc:
https://apac01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fapacheignite.readme.io%2Fdocs%2Fjdbc-driver%23section-streaming-mode&data=02%7C01%7CSriveena.Mattaparthi%40ekaplus.com%7Cd2ff72f68e13451756c008d5f0be11d6%7C2a5b4e9716be4be4b2d40f3fcb3d373c%7C1%7C0%7C636679621511638203&sdata=QGfXouNn0Juxv18j1p0rgnBx%2Fle90Ioo3E6W3S6ohR8%3D&reserved=0
it will provide faster insert operation compare to regular insert, also you
can disable WAL:
https://apac01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fapacheignite.readme.io%2Fdocs%2Fwrite-ahead-log%23section-wal-activation-and-deactivation&data=02%7C01%7CSriveena.Mattaparthi%40ekaplus.com%7Cd2ff72f68e13451756c008d5f0be11d6%7C2a5b4e9716be4be4b2d40f3fcb3d373c%7C1%7C0%7C636679621511638203&sdata=pjwp4tCzN9KnUq8s0cvTbmmaIDVj8O4UPylv7acYEUg%3D&reserved=0
to get better throughput.
Thanks,
Mike.
--
Sent from: https://apac01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fapache-ignite-users.70518.x6.nabble.com%2F&data=02%7C01%7CSriveena.Mattaparthi%40ekaplus.com%7Cd2ff72f68e13451756c008d5f0be11d6%7C2a5b4e9716be4be4b2d40f3fcb3d373c%7C1%7C0%7C636679621511638203&sdata=0Z7q%2BpGVuPa2rpB4wjYg4tKt5MPDYi4denqAbskOtZg%3D&reserved=0
Re: Batch insert into ignite
Posted by Michael Cherkasov <mi...@gmail.com>.
I can't say for sure which one should be better for sure, it must be
benchmarked.
But if you can use SqlFieldsQuery this means that you can use native Ignite
API, right? So, in this case, IgniteDataStreamer
<https://apacheignite.readme.io/docs/data-streamers> would be the best
choice.
2018-07-24 9:36 GMT+03:00 Sriveena Mattaparthi <
Sriveena.Mattaparthi@ekaplus.com>:
> Hi Mikhail,
>
> Do we have this streaming/batch insert option, if we do a bulk insert
> using
> cache.query(new SqlFieldsQuery("sql");
>
> Also could you please confirm which one would perform better
> 1. Insert using IgniteJdbcDriver
> 2. Insert using SqlFieldsQuery
>
> Thanks & Regards,
> Sriveena
>
> -----Original Message-----
> From: Mikhail [mailto:michael.cherkasov@gmail.com]
> Sent: Monday, July 23, 2018 10:32 PM
> To: user@ignite.apache.org
> Subject: Re: Batch insert into ignite
>
> Hi Sriveena,
>
> for data load you can try to use streaming mode for jdbc:
> https://apac01.safelinks.protection.outlook.com/?url=
> https%3A%2F%2Fapacheignite.readme.io%2Fdocs%2Fjdbc-
> driver%23section-streaming-mode&data=02%7C01%7CSriveena.Mattaparthi%
> 40ekaplus.com%7Cd2ff72f68e13451756c008d5f0be11d6%
> 7C2a5b4e9716be4be4b2d40f3fcb3d373c%7C1%7C0%7C636679621511638203&sdata=
> QGfXouNn0Juxv18j1p0rgnBx%2Fle90Ioo3E6W3S6ohR8%3D&reserved=0
> it will provide faster insert operation compare to regular insert, also you
> can disable WAL:
> https://apac01.safelinks.protection.outlook.com/?url=
> https%3A%2F%2Fapacheignite.readme.io%2Fdocs%2Fwrite-
> ahead-log%23section-wal-activation-and-deactivation&
> amp;data=02%7C01%7CSriveena.Mattaparthi%40ekaplus.com%
> 7Cd2ff72f68e13451756c008d5f0be11d6%7C2a5b4e9716be4be4b2d40f3fcb3d
> 373c%7C1%7C0%7C636679621511638203&sdata=pjwp4tCzN9KnUq8s0cvTbmmaIDVj8O
> 4UPylv7acYEUg%3D&reserved=0
> to get better throughput.
>
> Thanks,
> Mike.
>
>
>
> --
> Sent from: https://apac01.safelinks.protection.outlook.com/?url=
> http%3A%2F%2Fapache-ignite-users.70518.x6.nabble.com%2F&
> amp;data=02%7C01%7CSriveena.Mattaparthi%40ekaplus.com%
> 7Cd2ff72f68e13451756c008d5f0be11d6%7C2a5b4e9716be4be4b2d40f3fcb3d
> 373c%7C1%7C0%7C636679621511638203&sdata=0Z7q%
> 2BpGVuPa2rpB4wjYg4tKt5MPDYi4denqAbskOtZg%3D&reserved=0
>
RE: Batch insert into ignite
Posted by Sriveena Mattaparthi <Sr...@ekaplus.com>.
Hi Mikhail,
Do we have this streaming/batch insert option, if we do a bulk insert using
cache.query(new SqlFieldsQuery("sql");
Also could you please confirm which one would perform better
1. Insert using IgniteJdbcDriver
2. Insert using SqlFieldsQuery
Thanks & Regards,
Sriveena
-----Original Message-----
From: Mikhail [mailto:michael.cherkasov@gmail.com]
Sent: Monday, July 23, 2018 10:32 PM
To: user@ignite.apache.org
Subject: Re: Batch insert into ignite
Hi Sriveena,
for data load you can try to use streaming mode for jdbc:
https://apac01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fapacheignite.readme.io%2Fdocs%2Fjdbc-driver%23section-streaming-mode&data=02%7C01%7CSriveena.Mattaparthi%40ekaplus.com%7Cd2ff72f68e13451756c008d5f0be11d6%7C2a5b4e9716be4be4b2d40f3fcb3d373c%7C1%7C0%7C636679621511638203&sdata=QGfXouNn0Juxv18j1p0rgnBx%2Fle90Ioo3E6W3S6ohR8%3D&reserved=0
it will provide faster insert operation compare to regular insert, also you
can disable WAL:
https://apac01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fapacheignite.readme.io%2Fdocs%2Fwrite-ahead-log%23section-wal-activation-and-deactivation&data=02%7C01%7CSriveena.Mattaparthi%40ekaplus.com%7Cd2ff72f68e13451756c008d5f0be11d6%7C2a5b4e9716be4be4b2d40f3fcb3d373c%7C1%7C0%7C636679621511638203&sdata=pjwp4tCzN9KnUq8s0cvTbmmaIDVj8O4UPylv7acYEUg%3D&reserved=0
to get better throughput.
Thanks,
Mike.
--
Sent from: https://apac01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fapache-ignite-users.70518.x6.nabble.com%2F&data=02%7C01%7CSriveena.Mattaparthi%40ekaplus.com%7Cd2ff72f68e13451756c008d5f0be11d6%7C2a5b4e9716be4be4b2d40f3fcb3d373c%7C1%7C0%7C636679621511638203&sdata=0Z7q%2BpGVuPa2rpB4wjYg4tKt5MPDYi4denqAbskOtZg%3D&reserved=0
Re: Batch insert into ignite
Posted by Mikhail <mi...@gmail.com>.
Hi Sriveena,
for data load you can try to use streaming mode for jdbc:
https://apacheignite.readme.io/docs/jdbc-driver#section-streaming-mode
it will provide faster insert operation compare to regular insert, also you
can disable WAL:
https://apacheignite.readme.io/docs/write-ahead-log#section-wal-activation-and-deactivation
to get better throughput.
Thanks,
Mike.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/