You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by narges saleh <sn...@gmail.com> on 2020/06/17 16:32:41 UTC
sqlline
Hi All,
Is collocation enforced when SQL is issues in sqline command line or do I
need to give the option when I start sqlline?
thanks.
Re: sqlline
Posted by Denis Magda <dm...@apache.org>.
Yes, that’s right.
On Thursday, June 18, 2020, narges saleh <sn...@gmail.com> wrote:
> Hi Denis
> But if all the involved tables/caches have the same affinity key,
> shouldn't they be collocated on the same node, if the data fits on that
> node?
> thanks.
>
> On Wed, Jun 17, 2020 at 10:53 PM Denis Magda <dm...@apache.org> wrote:
>
>> You can always send a compute task via one of IgniteCompute.affinityX()
>> methods and run a *local* SQL query on that node:
>>
>> - affinity calls: https://apacheignite.readme.io/docs/collocate-
>> compute-and-data#affinity-call-and-run-methods
>> <https://apacheignite.readme.io/docs/collocate-compute-and-data#affinity-call-and-run-methods>
>> - local SQL queries: https://apacheignite-sql.readme.io/docs/local-
>> queries
>>
>> My selects are complex but they do use the primary keys for the
>>> partitioned tables/caches participating in the query. But they perform
>>> poorly if the data is distributed across multiple nodes.
>>
>>
>> If the data is co-located and scattered across cluster nodes then it's
>> expected that Ignite will execute a query across several nodes. It's a
>> standard approach of distributed databases that support SQL engines. My
>> suggestion would be to investigate the root cause of low performance. You
>> just might need to configure some indexes: https://apacheignite-sql.
>> readme.io/docs/performance-and-debugging
>>
>> -
>> Denis
>>
>>
>> On Wed, Jun 17, 2020 at 7:33 PM narges saleh <sn...@gmail.com>
>> wrote:
>>
>>> My selects are complex but they do use the primary keys for the
>>> partitioned tables/caches participating in the query. But they perform
>>> poorly if the data is distributed across multiple nodes.
>>>
>>> On Wed, Jun 17, 2020 at 8:04 PM narges saleh <sn...@gmail.com>
>>> wrote:
>>>
>>>> Thanks Denis. In case of selects, can I push ignite to broadcast the
>>>> sql to where the data is, say by using employing services deployed on the
>>>> targeted data nodes, or utilizing affinity calls (with the sql as part of
>>>> the logic)?
>>>>
>>>> On Wed, Jun 17, 2020 at 7:02 PM Denis Magda <dm...@apache.org> wrote:
>>>>
>>>>> Narges,
>>>>>
>>>>> INSERTS/UPDATES/DELETES will be forwarded to primary nodes based on
>>>>> the value of primary keys you pass into queries. Ignite maps a primary key
>>>>> to a partition and, finally, to a primary node that holds that partition.
>>>>> All this happens transparently in Ignite. You don't need to do anything
>>>>> except for sending those SQL commands.
>>>>>
>>>>> As for SELECTS, most of them will be broadcasted to your server nodes
>>>>> unless Ignite can pre-select a sub-set of target nodes a query needs to be
>>>>> executed on. This optimization always takes place for simple queries when
>>>>> you try to get records based on the value of a primary key, such as "SELECT
>>>>> * FROM Table WHERE primaryKeyColumn = 2".
>>>>>
>>>>> -
>>>>> Denis
>>>>>
>>>>>
>>>>> On Wed, Jun 17, 2020 at 12:43 PM narges saleh <sn...@gmail.com>
>>>>> wrote:
>>>>>
>>>>>> Thanks Denis. Will watch the video.
>>>>>> One more question, related to SQL execution. If I issue a SQL from a
>>>>>> client, how would ignite know which node to run the SQL query on? I assume
>>>>>> the SQL is parsed on the server nodes, and I will still need to direct the
>>>>>> SQL to be executed on the right server node (assuming that all the data is
>>>>>> collocated on a single node). Am I right?
>>>>>>
>>>>>> On Wed, Jun 17, 2020 at 2:26 PM Denis Magda <dm...@apache.org>
>>>>>> wrote:
>>>>>>
>>>>>>> The collocated flag is an extra optimization hint for SQL queries
>>>>>>> with GROUP BYs. The parameter requests to group records locally on the
>>>>>>> nodes that participated in the query execution. For instance, if your
>>>>>>> application sends a query with GROUP BY to a couple of server nodes, then
>>>>>>> the nodes will group their portions of the result sets locally not making
>>>>>>> that the burden of the application that merges/reduces the result sets into
>>>>>>> one. The description of this parameter here should be helpful as well:
>>>>>>> https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters
>>>>>>>
>>>>>>> <https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters>
>>>>>>>>
>>>>>>>> Should I assume that if the table is defined with affinity set,
>>>>>>>> then compute collocation, including sql execution will happen automatically?
>>>>>>>
>>>>>>>
>>>>>>> That's right. You just need to set up the affinity keys and Ignite
>>>>>>> will take care of the rest. Btw, you might be interested in this SQL
>>>>>>> webinar we delivered recently. The webinar explains our SQL capabilities in
>>>>>>> detail: https://www.youtube.com/watch?v=eYV-tNLzIts
>>>>>>>
>>>>>>> -
>>>>>>> Denis
>>>>>>>
>>>>>>>
>>>>>>> On Wed, Jun 17, 2020 at 11:28 AM narges saleh <sn...@gmail.com>
>>>>>>> wrote:
>>>>>>>
>>>>>>>> Then can you please explain what the option collocated=true for
>>>>>>>> JDBC url is for?
>>>>>>>> Should I assume that if the table is defined with affinity set,
>>>>>>>> then compute collocation, including sql execution will happen automatically?
>>>>>>>> I am sorry if this is just a basic question.
>>>>>>>>
>>>>>>>> On Wed, Jun 17, 2020 at 12:57 PM Denis Magda <dm...@apache.org>
>>>>>>>> wrote:
>>>>>>>>
>>>>>>>>> If the data is already co-located you don't need to pass in any
>>>>>>>>> other parameters.
>>>>>>>>>
>>>>>>>>> -
>>>>>>>>> Denis
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Wed, Jun 17, 2020 at 10:13 AM narges saleh <
>>>>>>>>> snarges124@gmail.com> wrote:
>>>>>>>>>
>>>>>>>>>> Hi Denis. The tables are defined with affinity keys specified.
>>>>>>>>>> What I am asking whether to set the collection option when I insert or
>>>>>>>>>> query the data via SQL line.
>>>>>>>>>>
>>>>>>>>>> thanks
>>>>>>>>>>
>>>>>>>>>> On Wed, Jun 17, 2020 at 11:39 AM Denis Magda <dm...@apache.org>
>>>>>>>>>> wrote:
>>>>>>>>>>
>>>>>>>>>>> Hi,
>>>>>>>>>>>
>>>>>>>>>>> You need to pass the required parameters to the JDBC driver's
>>>>>>>>>>> connection URL:
>>>>>>>>>>> https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters
>>>>>>>>>>>
>>>>>>>>>>> However, I'm not quite sure what you mean under the
>>>>>>>>>>> "collocation" in the following context. All your data needs to be
>>>>>>>>>>> co-located beforehand (ensure you have affinity keys configured) and then
>>>>>>>>>>> just send your queries with joins with no additional connection parameters.
>>>>>>>>>>>
>>>>>>>>>>> -
>>>>>>>>>>> Denis
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> On Wed, Jun 17, 2020 at 9:33 AM narges saleh <
>>>>>>>>>>> snarges124@gmail.com> wrote:
>>>>>>>>>>>
>>>>>>>>>>>> Hi All,
>>>>>>>>>>>>
>>>>>>>>>>>> Is collocation enforced when SQL is issues in sqline command
>>>>>>>>>>>> line or do I need to give the option when I start sqlline?
>>>>>>>>>>>>
>>>>>>>>>>>> thanks.
>>>>>>>>>>>>
>>>>>>>>>>>
--
-
Denis
Re: sqlline
Posted by narges saleh <sn...@gmail.com>.
Hi Denis
But if all the involved tables/caches have the same affinity key, shouldn't
they be collocated on the same node, if the data fits on that node?
thanks.
On Wed, Jun 17, 2020 at 10:53 PM Denis Magda <dm...@apache.org> wrote:
> You can always send a compute task via one of IgniteCompute.affinityX()
> methods and run a *local* SQL query on that node:
>
> - affinity calls:
> https://apacheignite.readme.io/docs/collocate-compute-and-data#affinity-call-and-run-methods
> - local SQL queries:
> https://apacheignite-sql.readme.io/docs/local-queries
>
> My selects are complex but they do use the primary keys for the
>> partitioned tables/caches participating in the query. But they perform
>> poorly if the data is distributed across multiple nodes.
>
>
> If the data is co-located and scattered across cluster nodes then it's
> expected that Ignite will execute a query across several nodes. It's a
> standard approach of distributed databases that support SQL engines. My
> suggestion would be to investigate the root cause of low performance. You
> just might need to configure some indexes:
> https://apacheignite-sql.readme.io/docs/performance-and-debugging
>
> -
> Denis
>
>
> On Wed, Jun 17, 2020 at 7:33 PM narges saleh <sn...@gmail.com> wrote:
>
>> My selects are complex but they do use the primary keys for the
>> partitioned tables/caches participating in the query. But they perform
>> poorly if the data is distributed across multiple nodes.
>>
>> On Wed, Jun 17, 2020 at 8:04 PM narges saleh <sn...@gmail.com>
>> wrote:
>>
>>> Thanks Denis. In case of selects, can I push ignite to broadcast the sql
>>> to where the data is, say by using employing services deployed on the
>>> targeted data nodes, or utilizing affinity calls (with the sql as part of
>>> the logic)?
>>>
>>> On Wed, Jun 17, 2020 at 7:02 PM Denis Magda <dm...@apache.org> wrote:
>>>
>>>> Narges,
>>>>
>>>> INSERTS/UPDATES/DELETES will be forwarded to primary nodes based on the
>>>> value of primary keys you pass into queries. Ignite maps a primary key to a
>>>> partition and, finally, to a primary node that holds that partition. All
>>>> this happens transparently in Ignite. You don't need to do anything except
>>>> for sending those SQL commands.
>>>>
>>>> As for SELECTS, most of them will be broadcasted to your server nodes
>>>> unless Ignite can pre-select a sub-set of target nodes a query needs to be
>>>> executed on. This optimization always takes place for simple queries when
>>>> you try to get records based on the value of a primary key, such as "SELECT
>>>> * FROM Table WHERE primaryKeyColumn = 2".
>>>>
>>>> -
>>>> Denis
>>>>
>>>>
>>>> On Wed, Jun 17, 2020 at 12:43 PM narges saleh <sn...@gmail.com>
>>>> wrote:
>>>>
>>>>> Thanks Denis. Will watch the video.
>>>>> One more question, related to SQL execution. If I issue a SQL from a
>>>>> client, how would ignite know which node to run the SQL query on? I assume
>>>>> the SQL is parsed on the server nodes, and I will still need to direct the
>>>>> SQL to be executed on the right server node (assuming that all the data is
>>>>> collocated on a single node). Am I right?
>>>>>
>>>>> On Wed, Jun 17, 2020 at 2:26 PM Denis Magda <dm...@apache.org> wrote:
>>>>>
>>>>>> The collocated flag is an extra optimization hint for SQL queries
>>>>>> with GROUP BYs. The parameter requests to group records locally on the
>>>>>> nodes that participated in the query execution. For instance, if your
>>>>>> application sends a query with GROUP BY to a couple of server nodes, then
>>>>>> the nodes will group their portions of the result sets locally not making
>>>>>> that the burden of the application that merges/reduces the result sets into
>>>>>> one. The description of this parameter here should be helpful as well:
>>>>>> https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters
>>>>>>
>>>>>> <https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters>
>>>>>>>
>>>>>>> Should I assume that if the table is defined with affinity set, then
>>>>>>> compute collocation, including sql execution will happen automatically?
>>>>>>
>>>>>>
>>>>>> That's right. You just need to set up the affinity keys and Ignite
>>>>>> will take care of the rest. Btw, you might be interested in this SQL
>>>>>> webinar we delivered recently. The webinar explains our SQL capabilities in
>>>>>> detail: https://www.youtube.com/watch?v=eYV-tNLzIts
>>>>>>
>>>>>> -
>>>>>> Denis
>>>>>>
>>>>>>
>>>>>> On Wed, Jun 17, 2020 at 11:28 AM narges saleh <sn...@gmail.com>
>>>>>> wrote:
>>>>>>
>>>>>>> Then can you please explain what the option collocated=true for JDBC
>>>>>>> url is for?
>>>>>>> Should I assume that if the table is defined with affinity set, then
>>>>>>> compute collocation, including sql execution will happen automatically?
>>>>>>> I am sorry if this is just a basic question.
>>>>>>>
>>>>>>> On Wed, Jun 17, 2020 at 12:57 PM Denis Magda <dm...@apache.org>
>>>>>>> wrote:
>>>>>>>
>>>>>>>> If the data is already co-located you don't need to pass in any
>>>>>>>> other parameters.
>>>>>>>>
>>>>>>>> -
>>>>>>>> Denis
>>>>>>>>
>>>>>>>>
>>>>>>>> On Wed, Jun 17, 2020 at 10:13 AM narges saleh <sn...@gmail.com>
>>>>>>>> wrote:
>>>>>>>>
>>>>>>>>> Hi Denis. The tables are defined with affinity keys specified.
>>>>>>>>> What I am asking whether to set the collection option when I insert or
>>>>>>>>> query the data via SQL line.
>>>>>>>>>
>>>>>>>>> thanks
>>>>>>>>>
>>>>>>>>> On Wed, Jun 17, 2020 at 11:39 AM Denis Magda <dm...@apache.org>
>>>>>>>>> wrote:
>>>>>>>>>
>>>>>>>>>> Hi,
>>>>>>>>>>
>>>>>>>>>> You need to pass the required parameters to the JDBC driver's
>>>>>>>>>> connection URL:
>>>>>>>>>> https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters
>>>>>>>>>>
>>>>>>>>>> However, I'm not quite sure what you mean under the "collocation"
>>>>>>>>>> in the following context. All your data needs to be co-located beforehand
>>>>>>>>>> (ensure you have affinity keys configured) and then just send your queries
>>>>>>>>>> with joins with no additional connection parameters.
>>>>>>>>>>
>>>>>>>>>> -
>>>>>>>>>> Denis
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> On Wed, Jun 17, 2020 at 9:33 AM narges saleh <
>>>>>>>>>> snarges124@gmail.com> wrote:
>>>>>>>>>>
>>>>>>>>>>> Hi All,
>>>>>>>>>>>
>>>>>>>>>>> Is collocation enforced when SQL is issues in sqline command
>>>>>>>>>>> line or do I need to give the option when I start sqlline?
>>>>>>>>>>>
>>>>>>>>>>> thanks.
>>>>>>>>>>>
>>>>>>>>>>
Re: sqlline
Posted by Denis Magda <dm...@apache.org>.
You can always send a compute task via one of IgniteCompute.affinityX()
methods and run a *local* SQL query on that node:
- affinity calls:
https://apacheignite.readme.io/docs/collocate-compute-and-data#affinity-call-and-run-methods
- local SQL queries:
https://apacheignite-sql.readme.io/docs/local-queries
My selects are complex but they do use the primary keys for the partitioned
> tables/caches participating in the query. But they perform poorly if the
> data is distributed across multiple nodes.
If the data is co-located and scattered across cluster nodes then it's
expected that Ignite will execute a query across several nodes. It's a
standard approach of distributed databases that support SQL engines. My
suggestion would be to investigate the root cause of low performance. You
just might need to configure some indexes:
https://apacheignite-sql.readme.io/docs/performance-and-debugging
-
Denis
On Wed, Jun 17, 2020 at 7:33 PM narges saleh <sn...@gmail.com> wrote:
> My selects are complex but they do use the primary keys for the
> partitioned tables/caches participating in the query. But they perform
> poorly if the data is distributed across multiple nodes.
>
> On Wed, Jun 17, 2020 at 8:04 PM narges saleh <sn...@gmail.com> wrote:
>
>> Thanks Denis. In case of selects, can I push ignite to broadcast the sql
>> to where the data is, say by using employing services deployed on the
>> targeted data nodes, or utilizing affinity calls (with the sql as part of
>> the logic)?
>>
>> On Wed, Jun 17, 2020 at 7:02 PM Denis Magda <dm...@apache.org> wrote:
>>
>>> Narges,
>>>
>>> INSERTS/UPDATES/DELETES will be forwarded to primary nodes based on the
>>> value of primary keys you pass into queries. Ignite maps a primary key to a
>>> partition and, finally, to a primary node that holds that partition. All
>>> this happens transparently in Ignite. You don't need to do anything except
>>> for sending those SQL commands.
>>>
>>> As for SELECTS, most of them will be broadcasted to your server nodes
>>> unless Ignite can pre-select a sub-set of target nodes a query needs to be
>>> executed on. This optimization always takes place for simple queries when
>>> you try to get records based on the value of a primary key, such as "SELECT
>>> * FROM Table WHERE primaryKeyColumn = 2".
>>>
>>> -
>>> Denis
>>>
>>>
>>> On Wed, Jun 17, 2020 at 12:43 PM narges saleh <sn...@gmail.com>
>>> wrote:
>>>
>>>> Thanks Denis. Will watch the video.
>>>> One more question, related to SQL execution. If I issue a SQL from a
>>>> client, how would ignite know which node to run the SQL query on? I assume
>>>> the SQL is parsed on the server nodes, and I will still need to direct the
>>>> SQL to be executed on the right server node (assuming that all the data is
>>>> collocated on a single node). Am I right?
>>>>
>>>> On Wed, Jun 17, 2020 at 2:26 PM Denis Magda <dm...@apache.org> wrote:
>>>>
>>>>> The collocated flag is an extra optimization hint for SQL queries with
>>>>> GROUP BYs. The parameter requests to group records locally on the nodes
>>>>> that participated in the query execution. For instance, if your application
>>>>> sends a query with GROUP BY to a couple of server nodes, then the nodes
>>>>> will group their portions of the result sets locally not making that the
>>>>> burden of the application that merges/reduces the result sets into one. The
>>>>> description of this parameter here should be helpful as well:
>>>>> https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters
>>>>>
>>>>> <https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters>
>>>>>>
>>>>>> Should I assume that if the table is defined with affinity set, then
>>>>>> compute collocation, including sql execution will happen automatically?
>>>>>
>>>>>
>>>>> That's right. You just need to set up the affinity keys and Ignite
>>>>> will take care of the rest. Btw, you might be interested in this SQL
>>>>> webinar we delivered recently. The webinar explains our SQL capabilities in
>>>>> detail: https://www.youtube.com/watch?v=eYV-tNLzIts
>>>>>
>>>>> -
>>>>> Denis
>>>>>
>>>>>
>>>>> On Wed, Jun 17, 2020 at 11:28 AM narges saleh <sn...@gmail.com>
>>>>> wrote:
>>>>>
>>>>>> Then can you please explain what the option collocated=true for JDBC
>>>>>> url is for?
>>>>>> Should I assume that if the table is defined with affinity set, then
>>>>>> compute collocation, including sql execution will happen automatically?
>>>>>> I am sorry if this is just a basic question.
>>>>>>
>>>>>> On Wed, Jun 17, 2020 at 12:57 PM Denis Magda <dm...@apache.org>
>>>>>> wrote:
>>>>>>
>>>>>>> If the data is already co-located you don't need to pass in any
>>>>>>> other parameters.
>>>>>>>
>>>>>>> -
>>>>>>> Denis
>>>>>>>
>>>>>>>
>>>>>>> On Wed, Jun 17, 2020 at 10:13 AM narges saleh <sn...@gmail.com>
>>>>>>> wrote:
>>>>>>>
>>>>>>>> Hi Denis. The tables are defined with affinity keys specified. What
>>>>>>>> I am asking whether to set the collection option when I insert or query the
>>>>>>>> data via SQL line.
>>>>>>>>
>>>>>>>> thanks
>>>>>>>>
>>>>>>>> On Wed, Jun 17, 2020 at 11:39 AM Denis Magda <dm...@apache.org>
>>>>>>>> wrote:
>>>>>>>>
>>>>>>>>> Hi,
>>>>>>>>>
>>>>>>>>> You need to pass the required parameters to the JDBC driver's
>>>>>>>>> connection URL:
>>>>>>>>> https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters
>>>>>>>>>
>>>>>>>>> However, I'm not quite sure what you mean under the "collocation"
>>>>>>>>> in the following context. All your data needs to be co-located beforehand
>>>>>>>>> (ensure you have affinity keys configured) and then just send your queries
>>>>>>>>> with joins with no additional connection parameters.
>>>>>>>>>
>>>>>>>>> -
>>>>>>>>> Denis
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Wed, Jun 17, 2020 at 9:33 AM narges saleh <sn...@gmail.com>
>>>>>>>>> wrote:
>>>>>>>>>
>>>>>>>>>> Hi All,
>>>>>>>>>>
>>>>>>>>>> Is collocation enforced when SQL is issues in sqline command line
>>>>>>>>>> or do I need to give the option when I start sqlline?
>>>>>>>>>>
>>>>>>>>>> thanks.
>>>>>>>>>>
>>>>>>>>>
Re: sqlline
Posted by narges saleh <sn...@gmail.com>.
My selects are complex but they do use the primary keys for the partitioned
tables/caches participating in the query. But they perform poorly if the
data is distributed across multiple nodes.
On Wed, Jun 17, 2020 at 8:04 PM narges saleh <sn...@gmail.com> wrote:
> Thanks Denis. In case of selects, can I push ignite to broadcast the sql
> to where the data is, say by using employing services deployed on the
> targeted data nodes, or utilizing affinity calls (with the sql as part of
> the logic)?
>
> On Wed, Jun 17, 2020 at 7:02 PM Denis Magda <dm...@apache.org> wrote:
>
>> Narges,
>>
>> INSERTS/UPDATES/DELETES will be forwarded to primary nodes based on the
>> value of primary keys you pass into queries. Ignite maps a primary key to a
>> partition and, finally, to a primary node that holds that partition. All
>> this happens transparently in Ignite. You don't need to do anything except
>> for sending those SQL commands.
>>
>> As for SELECTS, most of them will be broadcasted to your server nodes
>> unless Ignite can pre-select a sub-set of target nodes a query needs to be
>> executed on. This optimization always takes place for simple queries when
>> you try to get records based on the value of a primary key, such as "SELECT
>> * FROM Table WHERE primaryKeyColumn = 2".
>>
>> -
>> Denis
>>
>>
>> On Wed, Jun 17, 2020 at 12:43 PM narges saleh <sn...@gmail.com>
>> wrote:
>>
>>> Thanks Denis. Will watch the video.
>>> One more question, related to SQL execution. If I issue a SQL from a
>>> client, how would ignite know which node to run the SQL query on? I assume
>>> the SQL is parsed on the server nodes, and I will still need to direct the
>>> SQL to be executed on the right server node (assuming that all the data is
>>> collocated on a single node). Am I right?
>>>
>>> On Wed, Jun 17, 2020 at 2:26 PM Denis Magda <dm...@apache.org> wrote:
>>>
>>>> The collocated flag is an extra optimization hint for SQL queries with
>>>> GROUP BYs. The parameter requests to group records locally on the nodes
>>>> that participated in the query execution. For instance, if your application
>>>> sends a query with GROUP BY to a couple of server nodes, then the nodes
>>>> will group their portions of the result sets locally not making that the
>>>> burden of the application that merges/reduces the result sets into one. The
>>>> description of this parameter here should be helpful as well:
>>>> https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters
>>>>
>>>> <https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters>
>>>>>
>>>>> Should I assume that if the table is defined with affinity set, then
>>>>> compute collocation, including sql execution will happen automatically?
>>>>
>>>>
>>>> That's right. You just need to set up the affinity keys and Ignite will
>>>> take care of the rest. Btw, you might be interested in this SQL webinar we
>>>> delivered recently. The webinar explains our SQL capabilities in detail:
>>>> https://www.youtube.com/watch?v=eYV-tNLzIts
>>>>
>>>> -
>>>> Denis
>>>>
>>>>
>>>> On Wed, Jun 17, 2020 at 11:28 AM narges saleh <sn...@gmail.com>
>>>> wrote:
>>>>
>>>>> Then can you please explain what the option collocated=true for JDBC
>>>>> url is for?
>>>>> Should I assume that if the table is defined with affinity set, then
>>>>> compute collocation, including sql execution will happen automatically?
>>>>> I am sorry if this is just a basic question.
>>>>>
>>>>> On Wed, Jun 17, 2020 at 12:57 PM Denis Magda <dm...@apache.org>
>>>>> wrote:
>>>>>
>>>>>> If the data is already co-located you don't need to pass in any other
>>>>>> parameters.
>>>>>>
>>>>>> -
>>>>>> Denis
>>>>>>
>>>>>>
>>>>>> On Wed, Jun 17, 2020 at 10:13 AM narges saleh <sn...@gmail.com>
>>>>>> wrote:
>>>>>>
>>>>>>> Hi Denis. The tables are defined with affinity keys specified. What
>>>>>>> I am asking whether to set the collection option when I insert or query the
>>>>>>> data via SQL line.
>>>>>>>
>>>>>>> thanks
>>>>>>>
>>>>>>> On Wed, Jun 17, 2020 at 11:39 AM Denis Magda <dm...@apache.org>
>>>>>>> wrote:
>>>>>>>
>>>>>>>> Hi,
>>>>>>>>
>>>>>>>> You need to pass the required parameters to the JDBC driver's
>>>>>>>> connection URL:
>>>>>>>> https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters
>>>>>>>>
>>>>>>>> However, I'm not quite sure what you mean under the "collocation"
>>>>>>>> in the following context. All your data needs to be co-located beforehand
>>>>>>>> (ensure you have affinity keys configured) and then just send your queries
>>>>>>>> with joins with no additional connection parameters.
>>>>>>>>
>>>>>>>> -
>>>>>>>> Denis
>>>>>>>>
>>>>>>>>
>>>>>>>> On Wed, Jun 17, 2020 at 9:33 AM narges saleh <sn...@gmail.com>
>>>>>>>> wrote:
>>>>>>>>
>>>>>>>>> Hi All,
>>>>>>>>>
>>>>>>>>> Is collocation enforced when SQL is issues in sqline command line
>>>>>>>>> or do I need to give the option when I start sqlline?
>>>>>>>>>
>>>>>>>>> thanks.
>>>>>>>>>
>>>>>>>>
Re: sqlline
Posted by narges saleh <sn...@gmail.com>.
Thanks Denis. In case of selects, can I push ignite to broadcast the sql to
where the data is, say by using employing services deployed on the targeted
data nodes, or utilizing affinity calls (with the sql as part of the logic)?
On Wed, Jun 17, 2020 at 7:02 PM Denis Magda <dm...@apache.org> wrote:
> Narges,
>
> INSERTS/UPDATES/DELETES will be forwarded to primary nodes based on the
> value of primary keys you pass into queries. Ignite maps a primary key to a
> partition and, finally, to a primary node that holds that partition. All
> this happens transparently in Ignite. You don't need to do anything except
> for sending those SQL commands.
>
> As for SELECTS, most of them will be broadcasted to your server nodes
> unless Ignite can pre-select a sub-set of target nodes a query needs to be
> executed on. This optimization always takes place for simple queries when
> you try to get records based on the value of a primary key, such as "SELECT
> * FROM Table WHERE primaryKeyColumn = 2".
>
> -
> Denis
>
>
> On Wed, Jun 17, 2020 at 12:43 PM narges saleh <sn...@gmail.com>
> wrote:
>
>> Thanks Denis. Will watch the video.
>> One more question, related to SQL execution. If I issue a SQL from a
>> client, how would ignite know which node to run the SQL query on? I assume
>> the SQL is parsed on the server nodes, and I will still need to direct the
>> SQL to be executed on the right server node (assuming that all the data is
>> collocated on a single node). Am I right?
>>
>> On Wed, Jun 17, 2020 at 2:26 PM Denis Magda <dm...@apache.org> wrote:
>>
>>> The collocated flag is an extra optimization hint for SQL queries with
>>> GROUP BYs. The parameter requests to group records locally on the nodes
>>> that participated in the query execution. For instance, if your application
>>> sends a query with GROUP BY to a couple of server nodes, then the nodes
>>> will group their portions of the result sets locally not making that the
>>> burden of the application that merges/reduces the result sets into one. The
>>> description of this parameter here should be helpful as well:
>>> https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters
>>>
>>> <https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters>
>>>>
>>>> Should I assume that if the table is defined with affinity set, then
>>>> compute collocation, including sql execution will happen automatically?
>>>
>>>
>>> That's right. You just need to set up the affinity keys and Ignite will
>>> take care of the rest. Btw, you might be interested in this SQL webinar we
>>> delivered recently. The webinar explains our SQL capabilities in detail:
>>> https://www.youtube.com/watch?v=eYV-tNLzIts
>>>
>>> -
>>> Denis
>>>
>>>
>>> On Wed, Jun 17, 2020 at 11:28 AM narges saleh <sn...@gmail.com>
>>> wrote:
>>>
>>>> Then can you please explain what the option collocated=true for JDBC
>>>> url is for?
>>>> Should I assume that if the table is defined with affinity set, then
>>>> compute collocation, including sql execution will happen automatically?
>>>> I am sorry if this is just a basic question.
>>>>
>>>> On Wed, Jun 17, 2020 at 12:57 PM Denis Magda <dm...@apache.org> wrote:
>>>>
>>>>> If the data is already co-located you don't need to pass in any other
>>>>> parameters.
>>>>>
>>>>> -
>>>>> Denis
>>>>>
>>>>>
>>>>> On Wed, Jun 17, 2020 at 10:13 AM narges saleh <sn...@gmail.com>
>>>>> wrote:
>>>>>
>>>>>> Hi Denis. The tables are defined with affinity keys specified. What I
>>>>>> am asking whether to set the collection option when I insert or query the
>>>>>> data via SQL line.
>>>>>>
>>>>>> thanks
>>>>>>
>>>>>> On Wed, Jun 17, 2020 at 11:39 AM Denis Magda <dm...@apache.org>
>>>>>> wrote:
>>>>>>
>>>>>>> Hi,
>>>>>>>
>>>>>>> You need to pass the required parameters to the JDBC driver's
>>>>>>> connection URL:
>>>>>>> https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters
>>>>>>>
>>>>>>> However, I'm not quite sure what you mean under the "collocation" in
>>>>>>> the following context. All your data needs to be co-located beforehand
>>>>>>> (ensure you have affinity keys configured) and then just send your queries
>>>>>>> with joins with no additional connection parameters.
>>>>>>>
>>>>>>> -
>>>>>>> Denis
>>>>>>>
>>>>>>>
>>>>>>> On Wed, Jun 17, 2020 at 9:33 AM narges saleh <sn...@gmail.com>
>>>>>>> wrote:
>>>>>>>
>>>>>>>> Hi All,
>>>>>>>>
>>>>>>>> Is collocation enforced when SQL is issues in sqline command line
>>>>>>>> or do I need to give the option when I start sqlline?
>>>>>>>>
>>>>>>>> thanks.
>>>>>>>>
>>>>>>>
Re: sqlline
Posted by Denis Magda <dm...@apache.org>.
Narges,
INSERTS/UPDATES/DELETES will be forwarded to primary nodes based on the
value of primary keys you pass into queries. Ignite maps a primary key to a
partition and, finally, to a primary node that holds that partition. All
this happens transparently in Ignite. You don't need to do anything except
for sending those SQL commands.
As for SELECTS, most of them will be broadcasted to your server nodes
unless Ignite can pre-select a sub-set of target nodes a query needs to be
executed on. This optimization always takes place for simple queries when
you try to get records based on the value of a primary key, such as "SELECT
* FROM Table WHERE primaryKeyColumn = 2".
-
Denis
On Wed, Jun 17, 2020 at 12:43 PM narges saleh <sn...@gmail.com> wrote:
> Thanks Denis. Will watch the video.
> One more question, related to SQL execution. If I issue a SQL from a
> client, how would ignite know which node to run the SQL query on? I assume
> the SQL is parsed on the server nodes, and I will still need to direct the
> SQL to be executed on the right server node (assuming that all the data is
> collocated on a single node). Am I right?
>
> On Wed, Jun 17, 2020 at 2:26 PM Denis Magda <dm...@apache.org> wrote:
>
>> The collocated flag is an extra optimization hint for SQL queries with
>> GROUP BYs. The parameter requests to group records locally on the nodes
>> that participated in the query execution. For instance, if your application
>> sends a query with GROUP BY to a couple of server nodes, then the nodes
>> will group their portions of the result sets locally not making that the
>> burden of the application that merges/reduces the result sets into one. The
>> description of this parameter here should be helpful as well:
>> https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters
>>
>> <https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters>
>>>
>>> Should I assume that if the table is defined with affinity set, then
>>> compute collocation, including sql execution will happen automatically?
>>
>>
>> That's right. You just need to set up the affinity keys and Ignite will
>> take care of the rest. Btw, you might be interested in this SQL webinar we
>> delivered recently. The webinar explains our SQL capabilities in detail:
>> https://www.youtube.com/watch?v=eYV-tNLzIts
>>
>> -
>> Denis
>>
>>
>> On Wed, Jun 17, 2020 at 11:28 AM narges saleh <sn...@gmail.com>
>> wrote:
>>
>>> Then can you please explain what the option collocated=true for JDBC url
>>> is for?
>>> Should I assume that if the table is defined with affinity set, then
>>> compute collocation, including sql execution will happen automatically?
>>> I am sorry if this is just a basic question.
>>>
>>> On Wed, Jun 17, 2020 at 12:57 PM Denis Magda <dm...@apache.org> wrote:
>>>
>>>> If the data is already co-located you don't need to pass in any other
>>>> parameters.
>>>>
>>>> -
>>>> Denis
>>>>
>>>>
>>>> On Wed, Jun 17, 2020 at 10:13 AM narges saleh <sn...@gmail.com>
>>>> wrote:
>>>>
>>>>> Hi Denis. The tables are defined with affinity keys specified. What I
>>>>> am asking whether to set the collection option when I insert or query the
>>>>> data via SQL line.
>>>>>
>>>>> thanks
>>>>>
>>>>> On Wed, Jun 17, 2020 at 11:39 AM Denis Magda <dm...@apache.org>
>>>>> wrote:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> You need to pass the required parameters to the JDBC driver's
>>>>>> connection URL:
>>>>>> https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters
>>>>>>
>>>>>> However, I'm not quite sure what you mean under the "collocation" in
>>>>>> the following context. All your data needs to be co-located beforehand
>>>>>> (ensure you have affinity keys configured) and then just send your queries
>>>>>> with joins with no additional connection parameters.
>>>>>>
>>>>>> -
>>>>>> Denis
>>>>>>
>>>>>>
>>>>>> On Wed, Jun 17, 2020 at 9:33 AM narges saleh <sn...@gmail.com>
>>>>>> wrote:
>>>>>>
>>>>>>> Hi All,
>>>>>>>
>>>>>>> Is collocation enforced when SQL is issues in sqline command line or
>>>>>>> do I need to give the option when I start sqlline?
>>>>>>>
>>>>>>> thanks.
>>>>>>>
>>>>>>
Re: sqlline
Posted by narges saleh <sn...@gmail.com>.
Thanks Denis. Will watch the video.
One more question, related to SQL execution. If I issue a SQL from a
client, how would ignite know which node to run the SQL query on? I assume
the SQL is parsed on the server nodes, and I will still need to direct the
SQL to be executed on the right server node (assuming that all the data is
collocated on a single node). Am I right?
On Wed, Jun 17, 2020 at 2:26 PM Denis Magda <dm...@apache.org> wrote:
> The collocated flag is an extra optimization hint for SQL queries with
> GROUP BYs. The parameter requests to group records locally on the nodes
> that participated in the query execution. For instance, if your application
> sends a query with GROUP BY to a couple of server nodes, then the nodes
> will group their portions of the result sets locally not making that the
> burden of the application that merges/reduces the result sets into one. The
> description of this parameter here should be helpful as well:
> https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters
>
> <https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters>
>>
>> Should I assume that if the table is defined with affinity set, then
>> compute collocation, including sql execution will happen automatically?
>
>
> That's right. You just need to set up the affinity keys and Ignite will
> take care of the rest. Btw, you might be interested in this SQL webinar we
> delivered recently. The webinar explains our SQL capabilities in detail:
> https://www.youtube.com/watch?v=eYV-tNLzIts
>
> -
> Denis
>
>
> On Wed, Jun 17, 2020 at 11:28 AM narges saleh <sn...@gmail.com>
> wrote:
>
>> Then can you please explain what the option collocated=true for JDBC url
>> is for?
>> Should I assume that if the table is defined with affinity set, then
>> compute collocation, including sql execution will happen automatically?
>> I am sorry if this is just a basic question.
>>
>> On Wed, Jun 17, 2020 at 12:57 PM Denis Magda <dm...@apache.org> wrote:
>>
>>> If the data is already co-located you don't need to pass in any other
>>> parameters.
>>>
>>> -
>>> Denis
>>>
>>>
>>> On Wed, Jun 17, 2020 at 10:13 AM narges saleh <sn...@gmail.com>
>>> wrote:
>>>
>>>> Hi Denis. The tables are defined with affinity keys specified. What I
>>>> am asking whether to set the collection option when I insert or query the
>>>> data via SQL line.
>>>>
>>>> thanks
>>>>
>>>> On Wed, Jun 17, 2020 at 11:39 AM Denis Magda <dm...@apache.org> wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> You need to pass the required parameters to the JDBC driver's
>>>>> connection URL:
>>>>> https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters
>>>>>
>>>>> However, I'm not quite sure what you mean under the "collocation" in
>>>>> the following context. All your data needs to be co-located beforehand
>>>>> (ensure you have affinity keys configured) and then just send your queries
>>>>> with joins with no additional connection parameters.
>>>>>
>>>>> -
>>>>> Denis
>>>>>
>>>>>
>>>>> On Wed, Jun 17, 2020 at 9:33 AM narges saleh <sn...@gmail.com>
>>>>> wrote:
>>>>>
>>>>>> Hi All,
>>>>>>
>>>>>> Is collocation enforced when SQL is issues in sqline command line or
>>>>>> do I need to give the option when I start sqlline?
>>>>>>
>>>>>> thanks.
>>>>>>
>>>>>
Re: sqlline
Posted by Denis Magda <dm...@apache.org>.
The collocated flag is an extra optimization hint for SQL queries with
GROUP BYs. The parameter requests to group records locally on the nodes
that participated in the query execution. For instance, if your application
sends a query with GROUP BY to a couple of server nodes, then the nodes
will group their portions of the result sets locally not making that the
burden of the application that merges/reduces the result sets into one. The
description of this parameter here should be helpful as well:
https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters
<https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters>
>
> Should I assume that if the table is defined with affinity set, then
> compute collocation, including sql execution will happen automatically?
That's right. You just need to set up the affinity keys and Ignite will
take care of the rest. Btw, you might be interested in this SQL webinar we
delivered recently. The webinar explains our SQL capabilities in detail:
https://www.youtube.com/watch?v=eYV-tNLzIts
-
Denis
On Wed, Jun 17, 2020 at 11:28 AM narges saleh <sn...@gmail.com> wrote:
> Then can you please explain what the option collocated=true for JDBC url
> is for?
> Should I assume that if the table is defined with affinity set, then
> compute collocation, including sql execution will happen automatically?
> I am sorry if this is just a basic question.
>
> On Wed, Jun 17, 2020 at 12:57 PM Denis Magda <dm...@apache.org> wrote:
>
>> If the data is already co-located you don't need to pass in any other
>> parameters.
>>
>> -
>> Denis
>>
>>
>> On Wed, Jun 17, 2020 at 10:13 AM narges saleh <sn...@gmail.com>
>> wrote:
>>
>>> Hi Denis. The tables are defined with affinity keys specified. What I am
>>> asking whether to set the collection option when I insert or query the data
>>> via SQL line.
>>>
>>> thanks
>>>
>>> On Wed, Jun 17, 2020 at 11:39 AM Denis Magda <dm...@apache.org> wrote:
>>>
>>>> Hi,
>>>>
>>>> You need to pass the required parameters to the JDBC driver's
>>>> connection URL:
>>>> https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters
>>>>
>>>> However, I'm not quite sure what you mean under the "collocation" in
>>>> the following context. All your data needs to be co-located beforehand
>>>> (ensure you have affinity keys configured) and then just send your queries
>>>> with joins with no additional connection parameters.
>>>>
>>>> -
>>>> Denis
>>>>
>>>>
>>>> On Wed, Jun 17, 2020 at 9:33 AM narges saleh <sn...@gmail.com>
>>>> wrote:
>>>>
>>>>> Hi All,
>>>>>
>>>>> Is collocation enforced when SQL is issues in sqline command line or
>>>>> do I need to give the option when I start sqlline?
>>>>>
>>>>> thanks.
>>>>>
>>>>
Re: sqlline
Posted by narges saleh <sn...@gmail.com>.
Then can you please explain what the option collocated=true for JDBC url is
for?
Should I assume that if the table is defined with affinity set, then
compute collocation, including sql execution will happen automatically?
I am sorry if this is just a basic question.
On Wed, Jun 17, 2020 at 12:57 PM Denis Magda <dm...@apache.org> wrote:
> If the data is already co-located you don't need to pass in any other
> parameters.
>
> -
> Denis
>
>
> On Wed, Jun 17, 2020 at 10:13 AM narges saleh <sn...@gmail.com>
> wrote:
>
>> Hi Denis. The tables are defined with affinity keys specified. What I am
>> asking whether to set the collection option when I insert or query the data
>> via SQL line.
>>
>> thanks
>>
>> On Wed, Jun 17, 2020 at 11:39 AM Denis Magda <dm...@apache.org> wrote:
>>
>>> Hi,
>>>
>>> You need to pass the required parameters to the JDBC driver's connection
>>> URL:
>>> https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters
>>>
>>> However, I'm not quite sure what you mean under the "collocation" in the
>>> following context. All your data needs to be co-located beforehand (ensure
>>> you have affinity keys configured) and then just send your queries with
>>> joins with no additional connection parameters.
>>>
>>> -
>>> Denis
>>>
>>>
>>> On Wed, Jun 17, 2020 at 9:33 AM narges saleh <sn...@gmail.com>
>>> wrote:
>>>
>>>> Hi All,
>>>>
>>>> Is collocation enforced when SQL is issues in sqline command line or do
>>>> I need to give the option when I start sqlline?
>>>>
>>>> thanks.
>>>>
>>>
Re: sqlline
Posted by Denis Magda <dm...@apache.org>.
If the data is already co-located you don't need to pass in any other
parameters.
-
Denis
On Wed, Jun 17, 2020 at 10:13 AM narges saleh <sn...@gmail.com> wrote:
> Hi Denis. The tables are defined with affinity keys specified. What I am
> asking whether to set the collection option when I insert or query the data
> via SQL line.
>
> thanks
>
> On Wed, Jun 17, 2020 at 11:39 AM Denis Magda <dm...@apache.org> wrote:
>
>> Hi,
>>
>> You need to pass the required parameters to the JDBC driver's connection
>> URL:
>> https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters
>>
>> However, I'm not quite sure what you mean under the "collocation" in the
>> following context. All your data needs to be co-located beforehand (ensure
>> you have affinity keys configured) and then just send your queries with
>> joins with no additional connection parameters.
>>
>> -
>> Denis
>>
>>
>> On Wed, Jun 17, 2020 at 9:33 AM narges saleh <sn...@gmail.com>
>> wrote:
>>
>>> Hi All,
>>>
>>> Is collocation enforced when SQL is issues in sqline command line or do
>>> I need to give the option when I start sqlline?
>>>
>>> thanks.
>>>
>>
Re: sqlline
Posted by narges saleh <sn...@gmail.com>.
Hi Denis. The tables are defined with affinity keys specified. What I am
asking whether to set the collection option when I insert or query the data
via SQL line.
thanks
On Wed, Jun 17, 2020 at 11:39 AM Denis Magda <dm...@apache.org> wrote:
> Hi,
>
> You need to pass the required parameters to the JDBC driver's connection
> URL:
> https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters
>
> However, I'm not quite sure what you mean under the "collocation" in the
> following context. All your data needs to be co-located beforehand (ensure
> you have affinity keys configured) and then just send your queries with
> joins with no additional connection parameters.
>
> -
> Denis
>
>
> On Wed, Jun 17, 2020 at 9:33 AM narges saleh <sn...@gmail.com> wrote:
>
>> Hi All,
>>
>> Is collocation enforced when SQL is issues in sqline command line or do I
>> need to give the option when I start sqlline?
>>
>> thanks.
>>
>
Re: sqlline
Posted by Denis Magda <dm...@apache.org>.
Hi,
You need to pass the required parameters to the JDBC driver's connection
URL:
https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters
However, I'm not quite sure what you mean under the "collocation" in the
following context. All your data needs to be co-located beforehand (ensure
you have affinity keys configured) and then just send your queries with
joins with no additional connection parameters.
-
Denis
On Wed, Jun 17, 2020 at 9:33 AM narges saleh <sn...@gmail.com> wrote:
> Hi All,
>
> Is collocation enforced when SQL is issues in sqline command line or do I
> need to give the option when I start sqlline?
>
> thanks.
>