You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@hive.apache.org by vinay gupta <vi...@yahoo.com> on 2009/10/06 23:43:12 UTC

INSERT OVERWRITE restriction

Hi,

http://wiki.apache.org/hadoop/Hive/LanguageManual/DML

>From the wiki above.... " Currently the OVERWRITE keyword is mandatory and implies that the contents of the chosen table or partition are replaced with the output of corresponding select statement."

How would one insert (more data) into an existing table(with some data already in it).

If I am reading the correct documentation, then how do people workaround this restriction. I am sure this is common enough use-case.

I am trying to do
INSERT INTO TABLE t1 partition(p1='x', p2='y') select a, b from t2;

and I need to do this repeatedly when more data arrives into t2(or some other table);


Thanks
Vinay

RE: INSERT OVERWRITE restriction

Posted by Namit Jain <nj...@facebook.com>.

That’s right –

There are some bugs in the counter for the number of rows.

From: vinay gupta [mailto:vingup2005@yahoo.com]
Sent: Tuesday, October 06, 2009 4:28 PM
To: hive-user@hadoop.apache.org
Subject: Re: INSERT OVERWRITE restriction

OK Thanks. I believe the correct format is

INSERT INTO TABLE t1 partition(p1='x', p2='y')
select a, b from (select a, b from t2 union all select a, b from t1 where p1=’x’ and p2=’y’) tmp2;

since direct union is not supported, only in subqueries. (per the error msg I got).

Thanks again.

Also I believe the numbers reported (xx Rows loaded into t1) were way off.
Doing a count on the file afterwards yielded correct results though. I wonder why?

Thanks,
Vinay

________________________________
From: Namit Jain <nj...@facebook.com>
To: "hive-user@hadoop.apache.org" <hi...@hadoop.apache.org>
Sent: Tuesday, October 6, 2009 3:10:32 PM
Subject: RE: INSERT OVERWRITE restriction
Currently, the only way to do this is to select the existing data and do a union:

INSERT INTO TABLE t1 partition(p1='x', p2='y')
select a, b from t2 union all select * from t1 where p1=’x’ and p2=’y’

From: vinay gupta [mailto:vingup2005@yahoo.com]
Sent: Tuesday, October 06, 2009 2:43 PM
To: hive-user@hadoop.apache.org
Subject: INSERT OVERWRITE restriction

Hi,

http://wiki.apache.org/hadoop/Hive/LanguageManual/DML

From the wiki above.... " Currently the OVERWRITE keyword is mandatory and implies that the contents of the chosen table or partition are replaced with the output of corresponding select statement."

How would one insert (more data) into an existing table(with some data already in it).

If I am reading the correct documentation, then how do people workaround this restriction. I am sure this is common enough use-case.

I am trying to do
INSERT INTO TABLE t1 partition(p1='x', p2='y') select a, b from t2;

and I need to do this repeatedly when more data arrives into t2(or some other table);

Thanks
Vinay

Re: INSERT OVERWRITE restriction

Posted by vinay gupta <vi...@yahoo.com>.

OK Thanks. I believe the correct format is 

INSERT INTO TABLE t1 partition(p1='x', p2='y')
select a, b from (select a, b from t2 union all select a, b from t1 where p1=’x’ and p2=’y’) tmp2;

since direct union is not supported, only in subqueries. (per the error msg I got).

Thanks again.

Also I believe the numbers reported (xx Rows loaded into t1) were way off.
Doing a count on the file afterwards yielded correct results though. I wonder why?

Thanks,
Vinay




________________________________
From: Namit Jain <nj...@facebook.com>
To: "hive-user@hadoop.apache.org" <hi...@hadoop.apache.org>
Sent: Tuesday, October 6, 2009 3:10:32 PM
Subject: RE: INSERT OVERWRITE restriction

 
Currently, the only way to do this is to select the existing
data and do a union:
 
 
INSERT INTO
TABLE t1 partition(p1='x', p2='y') 
select a, b
from t2 union all select * from t1 where p1=’x’ and p2=’y’
 
 
 
From:vinay gupta
[mailto:vingup2005@yahoo.com] 
Sent: Tuesday, October 06, 2009 2:43 PM
To: hive-user@hadoop.apache.org
Subject: INSERT OVERWRITE restriction
 
Hi,
 
http://wiki.apache.org/hadoop/Hive/LanguageManual/DML
 
From the wiki
above.... " Currently the OVERWRITE keyword is
mandatory and implies that the contents of the chosen table or partition are
replaced with the output of corresponding select statement."
 
How would one
insert (more data) into an existing table(with some data already in it).
 
If I am reading
the correct documentation, then how do people workaround this restriction. I am
sure this is common enough use-case.
 
I am trying to
do
INSERT INTO
TABLE t1 partition(p1='x', p2='y') select a, b from t2;
 
and I need to
do this repeatedly when more data arrives into t2(or some other table);
 
 
Thanks
Vinay

RE: INSERT OVERWRITE restriction

Posted by Namit Jain <nj...@facebook.com>.

Currently, the only way to do this is to select the existing data and do a union:


INSERT INTO TABLE t1 partition(p1='x', p2='y')
select a, b from t2 union all select * from t1 where p1='x' and p2='y'



From: vinay gupta [mailto:vingup2005@yahoo.com]
Sent: Tuesday, October 06, 2009 2:43 PM
To: hive-user@hadoop.apache.org
Subject: INSERT OVERWRITE restriction

Hi,

http://wiki.apache.org/hadoop/Hive/LanguageManual/DML

>From the wiki above.... " Currently the OVERWRITE keyword is mandatory and implies that the contents of the chosen table or partition are replaced with the output of corresponding select statement."

How would one insert (more data) into an existing table(with some data already in it).

If I am reading the correct documentation, then how do people workaround this restriction. I am sure this is common enough use-case.

I am trying to do
INSERT INTO TABLE t1 partition(p1='x', p2='y') select a, b from t2;

and I need to do this repeatedly when more data arrives into t2(or some other table);


Thanks
Vinay