Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> Multiple Insert with Where Clauses


Copy link to this message
-
Re: Multiple Insert with Where Clauses
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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> wrote:
>
>> It looks like this is a known issue (
>> https://issues.apache.org/jira/browse/HIVE-4173).
>>
>> ------------------------------
>> From: [EMAIL PROTECTED]
>> Date: Thu, 1 Aug 2013 17:36:37 -0700
>>
>> Subject: Re: Multiple Insert with Where Clauses
>> To: [EMAIL PROTECTED]
>>
>>
>> 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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
>> To: [EMAIL PROTECTED]
>>
>> 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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
>> To: [EMAIL PROTECTED]
>>
>>
>> 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 <[EMAIL PROTECTED]> 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
>>
>>
>>
>>
>>
>
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB