You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Sha Liu <li...@hotmail.com> on 2013/07/31 05:25:36 UTC
Multiple Insert with Where Clauses
Hi Hive Gurus,
When using the Hive extension of multiple inserts, can we add Where clauses for each Select statement, like the following?
FROM ...INSERT OVERWRITE TABLE ... SELECT col1, col2, col3 WHERE col4='abc'INSERT OVERWRITE TABLE ... SELECT col1, col4, col2 WHERE col3='xyz'
The underlined parts didn't cause any errors, but they didn't seem to be effective either (I'm using Hive 0.9). Note that the columns used in the Where clauses are not among the selected ones, but I'm not sure if that is important. Is this kind of operations supported?
Thanks,Sha Liu
Re: Multiple Insert with Where Clauses
Posted by Stephen Sprague <sp...@gmail.com>.
FWIW. I'm using hive v0.10 and the where clause is honored from the tests
i ran.
On Fri, Aug 2, 2013 at 7:34 PM, Stephen Sprague <sp...@gmail.com> wrote:
> wow. good find Sha.
>
> i see its marked critical. Hello!! I mean, come on, hive is _silently_
> ignoring the where clause??? I'll check this out on my version (v0.10)
> and confirm/deny its behavior there.
>
> In the meantime i guess stay far far away from that form of insert
> statement. Who knows now what else is broken. Sad.
>
>
> On Fri, Aug 2, 2013 at 10:55 AM, Sha Liu <li...@hotmail.com> wrote:
>
>> It looks like this is a known issue (
>> https://issues.apache.org/jira/browse/HIVE-4173).
>>
>> ------------------------------
>> From: spragues@gmail.com
>> Date: Thu, 1 Aug 2013 17:36:37 -0700
>>
>> Subject: Re: Multiple Insert with Where Clauses
>> To: user@hive.apache.org
>>
>>
>> yeah.i agree. that doesn't make any sense. why in the world would the
>> "where clause" not be supported with that syntax? something's rotten in
>> denmark there with your query.
>>
>> what version of hive are you seeing this on?
>>
>>
>> On Tue, Jul 30, 2013 at 9:34 PM, Sha Liu <li...@hotmail.com> wrote:
>>
>> Doesn't INSERT INTO do what you said? I'm not sure I understand
>> "inserting a few records into a table".
>>
>> Anyway here the problem seems different to me. For my cases these where
>> clauses for multiple inserts seem not effective, while Hive doesn't
>> complain about that.
>>
>> -Sha
>>
>> ------------------------------
>> Date: Tue, 30 Jul 2013 21:06:22 -0700
>>
>> Subject: Re: Multiple Insert with Where Clauses
>> From: bruderman@radiumone.com
>> To: user@hive.apache.org
>>
>> Hive doesn't support inserting a few records into a table. You will need
>> to write a query to union your select and then insert. IF you can
>> partition, then you can insert a whole partition at a time instead of the
>> table.
>>
>> Thanks,
>> Brad
>>
>>
>> On Tue, Jul 30, 2013 at 9:04 PM, Sha Liu <li...@hotmail.com> wrote:
>>
>> Yes for the example you gave, it works. It even works when there is a
>> single insert under the from clause, but there there are multiple inserts,
>> the where clauses seem no longer effective.
>>
>> ------------------------------
>> Date: Tue, 30 Jul 2013 20:29:19 -0700
>> Subject: Re: Multiple Insert with Where Clauses
>> From: bruderman@radiumone.com
>> To: user@hive.apache.org
>>
>>
>> Have you simply tried
>>
>> INSERT OVERWRITE TABLE destination
>> SELECT col1, col2, col3
>> FROM source
>> WHERE col4 = 'abc'
>>
>> Thanks!
>>
>>
>>
>> On Tue, Jul 30, 2013 at 8:25 PM, Sha Liu <li...@hotmail.com> wrote:
>>
>> Hi Hive Gurus,
>>
>> When using the Hive extension of multiple inserts, can we add Where
>> clauses for each Select statement, like the following?
>>
>> FROM ...
>> INSERT OVERWRITE TABLE ...
>> SELECT col1, col2, col3
>> *WHERE col4='abc'*
>> INSERT OVERWRITE TABLE ...
>> SELECT col1, col4, col2
>> *WHERE col3='xyz'*
>> *
>> *
>> The underlined parts didn't cause any errors, but they didn't seem to be
>> effective either (I'm using Hive 0.9). Note that the columns used in the
>> Where clauses are not among the selected ones, but I'm not sure if that is
>> important. Is this kind of operations supported?
>>
>> Thanks,
>> Sha Liu
>>
>>
>>
>>
>>
>
Re: Multiple Insert with Where Clauses
Posted by Stephen Sprague <sp...@gmail.com>.
wow. good find Sha.
i see its marked critical. Hello!! I mean, come on, hive is _silently_
ignoring the where clause??? I'll check this out on my version (v0.10)
and confirm/deny its behavior there.
In the meantime i guess stay far far away from that form of insert
statement. Who knows now what else is broken. Sad.
On Fri, Aug 2, 2013 at 10:55 AM, Sha Liu <li...@hotmail.com> wrote:
> It looks like this is a known issue (
> https://issues.apache.org/jira/browse/HIVE-4173).
>
> ------------------------------
> From: spragues@gmail.com
> Date: Thu, 1 Aug 2013 17:36:37 -0700
>
> Subject: Re: Multiple Insert with Where Clauses
> To: user@hive.apache.org
>
>
> yeah.i agree. that doesn't make any sense. why in the world would the
> "where clause" not be supported with that syntax? something's rotten in
> denmark there with your query.
>
> what version of hive are you seeing this on?
>
>
> On Tue, Jul 30, 2013 at 9:34 PM, Sha Liu <li...@hotmail.com> wrote:
>
> Doesn't INSERT INTO do what you said? I'm not sure I understand "inserting
> a few records into a table".
>
> Anyway here the problem seems different to me. For my cases these where
> clauses for multiple inserts seem not effective, while Hive doesn't
> complain about that.
>
> -Sha
>
> ------------------------------
> Date: Tue, 30 Jul 2013 21:06:22 -0700
>
> Subject: Re: Multiple Insert with Where Clauses
> From: bruderman@radiumone.com
> To: user@hive.apache.org
>
> Hive doesn't support inserting a few records into a table. You will need
> to write a query to union your select and then insert. IF you can
> partition, then you can insert a whole partition at a time instead of the
> table.
>
> Thanks,
> Brad
>
>
> On Tue, Jul 30, 2013 at 9:04 PM, Sha Liu <li...@hotmail.com> wrote:
>
> Yes for the example you gave, it works. It even works when there is a
> single insert under the from clause, but there there are multiple inserts,
> the where clauses seem no longer effective.
>
> ------------------------------
> Date: Tue, 30 Jul 2013 20:29:19 -0700
> Subject: Re: Multiple Insert with Where Clauses
> From: bruderman@radiumone.com
> To: user@hive.apache.org
>
>
> Have you simply tried
>
> INSERT OVERWRITE TABLE destination
> SELECT col1, col2, col3
> FROM source
> WHERE col4 = 'abc'
>
> Thanks!
>
>
>
> On Tue, Jul 30, 2013 at 8:25 PM, Sha Liu <li...@hotmail.com> wrote:
>
> Hi Hive Gurus,
>
> When using the Hive extension of multiple inserts, can we add Where
> clauses for each Select statement, like the following?
>
> FROM ...
> INSERT OVERWRITE TABLE ...
> SELECT col1, col2, col3
> *WHERE col4='abc'*
> INSERT OVERWRITE TABLE ...
> SELECT col1, col4, col2
> *WHERE col3='xyz'*
> *
> *
> The underlined parts didn't cause any errors, but they didn't seem to be
> effective either (I'm using Hive 0.9). Note that the columns used in the
> Where clauses are not among the selected ones, but I'm not sure if that is
> important. Is this kind of operations supported?
>
> Thanks,
> Sha Liu
>
>
>
>
>
RE: Multiple Insert with Where Clauses
Posted by Sha Liu <li...@hotmail.com>.
It looks like this is a known issue (https://issues.apache.org/jira/browse/HIVE-4173).
From: spragues@gmail.com
Date: Thu, 1 Aug 2013 17:36:37 -0700
Subject: Re: Multiple Insert with Where Clauses
To: user@hive.apache.org
yeah.i agree. that doesn't make any sense. why in the world would the "where clause" not be supported with that syntax? something's rotten in denmark there with your query.
what version of hive are you seeing this on?
On Tue, Jul 30, 2013 at 9:34 PM, Sha Liu <li...@hotmail.com> wrote:
Doesn't INSERT INTO do what you said? I'm not sure I understand "inserting a few records into a table".
Anyway here the problem seems different to me. For my cases these where clauses for multiple inserts seem not effective, while Hive doesn't complain about that.
-Sha
Date: Tue, 30 Jul 2013 21:06:22 -0700
Subject: Re: Multiple Insert with Where Clauses
From: bruderman@radiumone.com
To: user@hive.apache.org
Hive doesn't support inserting a few records into a table. You will need to write a query to union your select and then insert. IF you can partition, then you can insert a whole partition at a time instead of the table.
Thanks,Brad
On Tue, Jul 30, 2013 at 9:04 PM, Sha Liu <li...@hotmail.com> wrote:
Yes for the example you gave, it works. It even works when there is a single insert under the from clause, but there there are multiple inserts, the where clauses seem no longer effective.
Date: Tue, 30 Jul 2013 20:29:19 -0700
Subject: Re: Multiple Insert with Where Clauses
From: bruderman@radiumone.com
To: user@hive.apache.org
Have you simply tried
INSERT OVERWRITE TABLE destination SELECT col1, col2, col3FROM sourceWHERE col4 = 'abc'
Thanks!
On Tue, Jul 30, 2013 at 8:25 PM, Sha Liu <li...@hotmail.com> wrote:
Hi Hive Gurus,
When using the Hive extension of multiple inserts, can we add Where clauses for each Select statement, like the following?
FROM ...
INSERT OVERWRITE TABLE ... SELECT col1, col2, col3 WHERE col4='abc'INSERT OVERWRITE TABLE ... SELECT col1, col4, col2 WHERE col3='xyz'
The underlined parts didn't cause any errors, but they didn't seem to be effective either (I'm using Hive 0.9). Note that the columns used in the Where clauses are not among the selected ones, but I'm not sure if that is important. Is this kind of operations supported?
Thanks,Sha Liu
"hive -h " option bypasses ROLES and access permissions
?
Posted by Sanjay Subramanian <Sa...@wizecommerce.com>.
Hi
Hive version 0.9.0 (hive-common-0.9.0-cdh4.1.2.jar)
<property>
<name>hive.security.authorization.enabled</name>
<value>true</value>
<description>enable or disable the hive client authorization</description>
</property>
Linux User = hiveuser1 (no hive permissions)
CASE 1
hive -e "select * from outpdir_ptitle_explanation_parsed limit 10"
Authorization failed:No privilege 'Select' found for inputs { database:default, table:outpdir_ptitle_explanation_parsed, columnName:header_servername}. Use show grant to get more details.
CASE 2 (use the -h option)
hive -h localhost -e "select * from outpdir_ptitle_explanation_parsed limit 10"
Shows results !!!
Why does "-h" option bypass authorization
Thanks
sanjay
CONFIDENTIALITY NOTICE
======================
This email message and any attachments are for the exclusive use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message along with any attachments, from your computer system. If you are the intended recipient, please be advised that the content of this message is subject to access, review and disclosure by the sender's Email System Administrator.
CONFIDENTIALITY NOTICE
======================
This email message and any attachments are for the exclusive use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message along with any attachments, from your computer system. If you are the intended recipient, please be advised that the content of this message is subject to access, review and disclosure by the sender's Email System Administrator.
CONFIDENTIALITY NOTICE
======================
This email message and any attachments are for the exclusive use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message along with any attachments, from your computer system. If you are the intended recipient, please be advised that the content of this message is subject to access, review and disclosure by the sender's Email System Administrator.
Is there a way to disable -h option ?
Posted by Sanjay Subramanian <Sa...@wizecommerce.com>.
Hive version 0.9.0 (hive-common-0.9.0-cdh4.1.2.jar)
Is there a way to disable -h <hostname> option ?
This way , I can disable anyone on the network drop/alter tables.
Like I said below , even if I create a ROLE with only SELECT permission , a user on the network can DROP tables with a -h <hostname> option
Thanks
Sanjay
From: Sanjay Subramanian <sa...@wizecommerce.com>>
Reply-To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Date: Thursday, August 1, 2013 6:37 PM
To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Subject: Hive Authorization is bypassed with -h option
Hi
Hive version 0.9.0 (hive-common-0.9.0-cdh4.1.2.jar)
<property>
<name>hive.security.authorization.enabled</name>
<value>true</value>
<description>enable or disable the hive client authorization</description>
</property>
Linux User = hiveuser1 (no hive permissions)
CASE 1
hive -e "select * from outpdir_ptitle_explanation_parsed limit 10"
Authorization failed:No privilege 'Select' found for inputs { database:default, table:outpdir_ptitle_explanation_parsed, columnName:header_servername}. Use show grant to get more details.
CASE 2 (use the -h option)
hive -h localhost -e "select * from outpdir_ptitle_explanation_parsed limit 10"
Shows results !!!
Why does "-h" option bypass authorization
Thanks
sanjay
CONFIDENTIALITY NOTICE
======================
This email message and any attachments are for the exclusive use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message along with any attachments, from your computer system. If you are the intended recipient, please be advised that the content of this message is subject to access, review and disclosure by the sender's Email System Administrator.
CONFIDENTIALITY NOTICE
======================
This email message and any attachments are for the exclusive use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message along with any attachments, from your computer system. If you are the intended recipient, please be advised that the content of this message is subject to access, review and disclosure by the sender's Email System Administrator.
Hive Authorization is bypassed with -h option
Posted by Sanjay Subramanian <Sa...@wizecommerce.com>.
Hi
Hive version 0.9.0 (hive-common-0.9.0-cdh4.1.2.jar)
<property>
<name>hive.security.authorization.enabled</name>
<value>true</value>
<description>enable or disable the hive client authorization</description>
</property>
Linux User = hiveuser1 (no hive permissions)
CASE 1
hive -e "select * from outpdir_ptitle_explanation_parsed limit 10"
Authorization failed:No privilege 'Select' found for inputs { database:default, table:outpdir_ptitle_explanation_parsed, columnName:header_servername}. Use show grant to get more details.
CASE 2 (use the -h option)
hive -h localhost -e "select * from outpdir_ptitle_explanation_parsed limit 10"
Shows results !!!
Why does "-h" option bypass authorization
Thanks
sanjay
CONFIDENTIALITY NOTICE
======================
This email message and any attachments are for the exclusive use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message along with any attachments, from your computer system. If you are the intended recipient, please be advised that the content of this message is subject to access, review and disclosure by the sender's Email System Administrator.
Re: Multiple Insert with Where Clauses
Posted by Stephen Sprague <sp...@gmail.com>.
yeah.i agree. that doesn't make any sense. why in the world would the
"where clause" not be supported with that syntax? something's rotten in
denmark there with your query.
what version of hive are you seeing this on?
On Tue, Jul 30, 2013 at 9:34 PM, Sha Liu <li...@hotmail.com> wrote:
> Doesn't INSERT INTO do what you said? I'm not sure I understand "inserting
> a few records into a table".
>
> Anyway here the problem seems different to me. For my cases these where
> clauses for multiple inserts seem not effective, while Hive doesn't
> complain about that.
>
> -Sha
>
> ------------------------------
> Date: Tue, 30 Jul 2013 21:06:22 -0700
>
> Subject: Re: Multiple Insert with Where Clauses
> From: bruderman@radiumone.com
> To: user@hive.apache.org
>
> Hive doesn't support inserting a few records into a table. You will need
> to write a query to union your select and then insert. IF you can
> partition, then you can insert a whole partition at a time instead of the
> table.
>
> Thanks,
> Brad
>
>
> On Tue, Jul 30, 2013 at 9:04 PM, Sha Liu <li...@hotmail.com> wrote:
>
> Yes for the example you gave, it works. It even works when there is a
> single insert under the from clause, but there there are multiple inserts,
> the where clauses seem no longer effective.
>
> ------------------------------
> Date: Tue, 30 Jul 2013 20:29:19 -0700
> Subject: Re: Multiple Insert with Where Clauses
> From: bruderman@radiumone.com
> To: user@hive.apache.org
>
>
> Have you simply tried
>
> INSERT OVERWRITE TABLE destination
> SELECT col1, col2, col3
> FROM source
> WHERE col4 = 'abc'
>
> Thanks!
>
>
>
> On Tue, Jul 30, 2013 at 8:25 PM, Sha Liu <li...@hotmail.com> wrote:
>
> Hi Hive Gurus,
>
> When using the Hive extension of multiple inserts, can we add Where
> clauses for each Select statement, like the following?
>
> FROM ...
> INSERT OVERWRITE TABLE ...
> SELECT col1, col2, col3
> *WHERE col4='abc'*
> INSERT OVERWRITE TABLE ...
> SELECT col1, col4, col2
> *WHERE col3='xyz'*
> *
> *
> The underlined parts didn't cause any errors, but they didn't seem to be
> effective either (I'm using Hive 0.9). Note that the columns used in the
> Where clauses are not among the selected ones, but I'm not sure if that is
> important. Is this kind of operations supported?
>
> Thanks,
> Sha Liu
>
>
>
>
RE: Multiple Insert with Where Clauses
Posted by Sha Liu <li...@hotmail.com>.
Doesn't INSERT INTO do what you said? I'm not sure I understand "inserting a few records into a table".
Anyway here the problem seems different to me. For my cases these where clauses for multiple inserts seem not effective, while Hive doesn't complain about that.
-Sha
Date: Tue, 30 Jul 2013 21:06:22 -0700
Subject: Re: Multiple Insert with Where Clauses
From: bruderman@radiumone.com
To: user@hive.apache.org
Hive doesn't support inserting a few records into a table. You will need to write a query to union your select and then insert. IF you can partition, then you can insert a whole partition at a time instead of the table.
Thanks,Brad
On Tue, Jul 30, 2013 at 9:04 PM, Sha Liu <li...@hotmail.com> wrote:
Yes for the example you gave, it works. It even works when there is a single insert under the from clause, but there there are multiple inserts, the where clauses seem no longer effective.
Date: Tue, 30 Jul 2013 20:29:19 -0700
Subject: Re: Multiple Insert with Where Clauses
From: bruderman@radiumone.com
To: user@hive.apache.org
Have you simply tried
INSERT OVERWRITE TABLE destination SELECT col1, col2, col3FROM sourceWHERE col4 = 'abc'
Thanks!
On Tue, Jul 30, 2013 at 8:25 PM, Sha Liu <li...@hotmail.com> wrote:
Hi Hive Gurus,
When using the Hive extension of multiple inserts, can we add Where clauses for each Select statement, like the following?
FROM ...
INSERT OVERWRITE TABLE ... SELECT col1, col2, col3 WHERE col4='abc'INSERT OVERWRITE TABLE ... SELECT col1, col4, col2 WHERE col3='xyz'
The underlined parts didn't cause any errors, but they didn't seem to be effective either (I'm using Hive 0.9). Note that the columns used in the Where clauses are not among the selected ones, but I'm not sure if that is important. Is this kind of operations supported?
Thanks,Sha Liu
Re: Multiple Insert with Where Clauses
Posted by Brad Ruderman <br...@radiumone.com>.
Hive doesn't support inserting a few records into a table. You will need to
write a query to union your select and then insert. IF you can partition,
then you can insert a whole partition at a time instead of the table.
Thanks,
Brad
On Tue, Jul 30, 2013 at 9:04 PM, Sha Liu <li...@hotmail.com> wrote:
> Yes for the example you gave, it works. It even works when there is a
> single insert under the from clause, but there there are multiple inserts,
> the where clauses seem no longer effective.
>
> ------------------------------
> Date: Tue, 30 Jul 2013 20:29:19 -0700
> Subject: Re: Multiple Insert with Where Clauses
> From: bruderman@radiumone.com
> To: user@hive.apache.org
>
>
> Have you simply tried
>
> INSERT OVERWRITE TABLE destination
> SELECT col1, col2, col3
> FROM source
> WHERE col4 = 'abc'
>
> Thanks!
>
>
>
> On Tue, Jul 30, 2013 at 8:25 PM, Sha Liu <li...@hotmail.com> wrote:
>
> Hi Hive Gurus,
>
> When using the Hive extension of multiple inserts, can we add Where
> clauses for each Select statement, like the following?
>
> FROM ...
> INSERT OVERWRITE TABLE ...
> SELECT col1, col2, col3
> *WHERE col4='abc'*
> INSERT OVERWRITE TABLE ...
> SELECT col1, col4, col2
> *WHERE col3='xyz'*
> *
> *
> The underlined parts didn't cause any errors, but they didn't seem to be
> effective either (I'm using Hive 0.9). Note that the columns used in the
> Where clauses are not among the selected ones, but I'm not sure if that is
> important. Is this kind of operations supported?
>
> Thanks,
> Sha Liu
>
>
>
RE: Multiple Insert with Where Clauses
Posted by Sha Liu <li...@hotmail.com>.
Yes for the example you gave, it works. It even works when there is a single insert under the from clause, but there there are multiple inserts, the where clauses seem no longer effective.
Date: Tue, 30 Jul 2013 20:29:19 -0700
Subject: Re: Multiple Insert with Where Clauses
From: bruderman@radiumone.com
To: user@hive.apache.org
Have you simply tried
INSERT OVERWRITE TABLE destination SELECT col1, col2, col3FROM sourceWHERE col4 = 'abc'
Thanks!
On Tue, Jul 30, 2013 at 8:25 PM, Sha Liu <li...@hotmail.com> wrote:
Hi Hive Gurus,
When using the Hive extension of multiple inserts, can we add Where clauses for each Select statement, like the following?
FROM ...
INSERT OVERWRITE TABLE ... SELECT col1, col2, col3 WHERE col4='abc'INSERT OVERWRITE TABLE ... SELECT col1, col4, col2 WHERE col3='xyz'
The underlined parts didn't cause any errors, but they didn't seem to be effective either (I'm using Hive 0.9). Note that the columns used in the Where clauses are not among the selected ones, but I'm not sure if that is important. Is this kind of operations supported?
Thanks,Sha Liu
Re: Multiple Insert with Where Clauses
Posted by Brad Ruderman <br...@radiumone.com>.
Have you simply tried
INSERT OVERWRITE TABLE destination
SELECT col1, col2, col3
FROM source
WHERE col4 = 'abc'
Thanks!
On Tue, Jul 30, 2013 at 8:25 PM, Sha Liu <li...@hotmail.com> wrote:
> Hi Hive Gurus,
>
> When using the Hive extension of multiple inserts, can we add Where
> clauses for each Select statement, like the following?
>
> FROM ...
> INSERT OVERWRITE TABLE ...
> SELECT col1, col2, col3
> *WHERE col4='abc'*
> INSERT OVERWRITE TABLE ...
> SELECT col1, col4, col2
> *WHERE col3='xyz'*
> *
> *
> The underlined parts didn't cause any errors, but they didn't seem to be
> effective either (I'm using Hive 0.9). Note that the columns used in the
> Where clauses are not among the selected ones, but I'm not sure if that is
> important. Is this kind of operations supported?
>
> Thanks,
> Sha Liu
>