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

Posted to user@hive.apache.org by "Balaraman, Anand" <An...@SYNTELINC.COM> on 2012/08/16 08:06:49 UTC

UNION ALL - what is the simplest form

Hi

 

I am trying to consolidate one of my staged tables with a primary table
(incremental loading).

Simply need to merge 2 tables using UNION ALL.

Tried the following:

 

        select * from main_table UNION ALL select * from stage_table

but, not able to execute it -> Error in semantic analysis: Top level
UNION is not supported currently; use a subquery for the UNION

 

But, HIVE language manual says:

UNION ALL is used to combine the result from multiple SELECT statements
into a single result set.

 

Syntax:

        select_statement UNION ALL select_statement UNION ALL
select_statement ...

 

Have I gone wrong with the syntax anywhere in my query ?

 

Regards

 

Anand B

 


Confidential: This electronic message and all contents contain information from Syntel, Inc. which may be privileged, confidential or otherwise protected from disclosure. The information is intended to be for the addressee only. If you are not the addressee, any disclosure, copy, distribution or use of the contents of this message is prohibited. If you have received this electronic message in error, please notify the sender immediately and destroy the original message and all copies.

RE: UNION ALL - what is the simplest form

Posted by "Balaraman, Anand" <An...@SYNTELINC.COM>.

Thanks for your suggestion Bejoy

I am using hive 0.7.1... So, cant use you first solution...

The second one is a good idea, but - I get a large chunk of files in
staging which clutters my HDFS... Each file sizes from 40 KB to a max
4.4 MB, though my block size is 64MB...

This is one of the reasons why I am merging the staging with the main
table contents through query... So that the output wud be in a single
file, unless block size exceeds...

 

At the moment, I am using the following query as a workaround... Please
do let me know in case better options are there...

 

SELECT

        *

FROM

(

        SELECT * FROM MAIN_TABLE

        UNION ALL

        SELECT * FROM STAGE_TABLE

) FINAL

 

From: Bejoy KS [mailto:bejoy_ks@yahoo.com] 
Sent: 16 August 2012 13:12
To: user@hive.apache.org
Subject: Re: UNION ALL - what is the simplest form

 

Hi Anand

You necessarily don't need to go in for UNION ALL for your requirement.

Use INSERT INTO instead, which has less overhead. It is supported from
hive 0.8 .

INSERT INTO main_table SELECT * FROM stage_table;

Or an even better approach if you are just copying whole data from one
table to another would be to use a hdfs copy/move. 

LOAD DATA INPATH 'location/of/stage_table' INTO TABLE 'main_table';

Regards
Bejoy KS

Sent from handheld, please excuse typos.

________________________________

From: "Balaraman, Anand" <An...@SYNTELINC.COM> 

Date: Thu, 16 Aug 2012 11:36:49 +0530

To: <us...@hive.apache.org>

ReplyTo: user@hive.apache.org 

Subject: UNION ALL - what is the simplest form

 

Hi

 

I am trying to consolidate one of my staged tables with a primary table
(incremental loading).

Simply need to merge 2 tables using UNION ALL.

Tried the following:

 

        select * from main_table UNION ALL select * from stage_table

but, not able to execute it -> Error in semantic analysis: Top level
UNION is not supported currently; use a subquery for the UNION

 

But, HIVE language manual says:

UNION ALL is used to combine the result from multiple SELECT statements
into a single result set.

 

Syntax:

        select_statement UNION ALL select_statement UNION ALL
select_statement ...

 

Have I gone wrong with the syntax anywhere in my query ?

 

Regards

 

Anand B

 

Confidential: This electronic message and all contents contain
information from Syntel, Inc. which may be privileged, confidential or
otherwise protected from disclosure. The information is intended to be
for the addressee only. If you are not the addressee, any disclosure,
copy, distribution or use of the contents of this message is prohibited.
If you have received this electronic message in error, please notify the
sender immediately and destroy the original message and all copies.


Confidential: This electronic message and all contents contain information from Syntel, Inc. which may be privileged, confidential or otherwise protected from disclosure. The information is intended to be for the addressee only. If you are not the addressee, any disclosure, copy, distribution or use of the contents of this message is prohibited. If you have received this electronic message in error, please notify the sender immediately and destroy the original message and all copies.

Re: UNION ALL - what is the simplest form

Posted by Bejoy KS <be...@yahoo.com>.

Hi Anand

You necessarily don't need to go in for UNION ALL for your requirement.

Use INSERT INTO instead, which has less overhead. It is supported from hive 0.8 .

INSERT INTO main_table SELECT * FROM stage_table;

Or an even better approach if you are just copying whole data from one table to another would be to use a hdfs copy/move. 

LOAD DATA INPATH 'location/of/stage_table' INTO TABLE 'main_table';

Regards
Bejoy KS

Sent from handheld, please excuse typos.

-----Original Message-----
From: "Balaraman, Anand" <An...@SYNTELINC.COM>
Date: Thu, 16 Aug 2012 11:36:49 
To: <us...@hive.apache.org>
Reply-To: user@hive.apache.org
Subject: UNION ALL - what is the simplest form

Hi

 

I am trying to consolidate one of my staged tables with a primary table
(incremental loading).

Simply need to merge 2 tables using UNION ALL.

Tried the following:

 

        select * from main_table UNION ALL select * from stage_table

but, not able to execute it -> Error in semantic analysis: Top level
UNION is not supported currently; use a subquery for the UNION

 

But, HIVE language manual says:

UNION ALL is used to combine the result from multiple SELECT statements
into a single result set.

 

Syntax:

        select_statement UNION ALL select_statement UNION ALL
select_statement ...

 

Have I gone wrong with the syntax anywhere in my query ?

 

Regards

 

Anand B

 


Confidential: This electronic message and all contents contain information from Syntel, Inc. which may be privileged, confidential or otherwise protected from disclosure. The information is intended to be for the addressee only. If you are not the addressee, any disclosure, copy, distribution or use of the contents of this message is prohibited. If you have received this electronic message in error, please notify the sender immediately and destroy the original message and all copies.