You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by zuohua zhang <zu...@gmail.com> on 2012/09/12 22:02:42 UTC
most efficient way to concatenate 3 tables into one?
I have 3 tables, each contains monthly data, I would like to make a table
to concatenate all 3 months into 1 table.
What is the most efficient way to do that?
I am using insert into which takes a long time.
Thanks!
Re: most efficient way to concatenate 3 tables into one?
Posted by MiaoMiao <li...@gmail.com>.
Then you will get a copy of each sub table.
My project use external partitions instead, and in this way, you don't
need to cp sub tables.
1) Create a table some_table
2) ALTER TABLE some_table ADD PARTITION (partition_col =
'partition_col_value1') LOCATION '/user/data/subTable1'
ALTER TABLE some_table ADD PARTITION (partition_col =
'partition_col_value2') LOCATION '/user/data/subTable2'
On Thu, Sep 13, 2012 at 4:23 AM, Bejoy KS <be...@yahoo.com> wrote:
> Hi
>
> Just follow the steps
> 1) Create an external table with location as /user/aggregatedTable
> 2) Move the contents of 3 monthly tables to this location
> Hadoop fs -cp /user/monthlyTable1/* /user/aggregatedTable
>
> Hadoop fs -cp /user/monthlyTable2/* /user/aggregatedTable
>
> ...
>
> Replace the hsfs dirs in the above commands with those used in your tables.
> Regards
> Bejoy KS
>
> Sent from handheld, please excuse typos.
> ________________________________
> From: zuohua zhang <zu...@gmail.com>
> Date: Wed, 12 Sep 2012 13:14:20 -0700
> To: <us...@hive.apache.org>; <be...@yahoo.com>
> ReplyTo: user@hive.apache.org
> Subject: Re: most efficient way to concatenate 3 tables into one?
>
> Thanks Bejoy.
> Yes, they have the same schema.
> Can you explain further how to? I am new to hive.
>
>
> On Wed, Sep 12, 2012 at 1:06 PM, Bejoy KS <be...@yahoo.com> wrote:
>>
>> Hi
>>
>> If all the 3 tables have the same. Schema, Create an external table and
>> move the data from all the 3 tables to this new table's location. Just a
>> hdfs copy or move is not that expensive.
>> Regards
>> Bejoy KS
>>
>> Sent from handheld, please excuse typos.
>> ________________________________
>> From: zuohua zhang <zu...@gmail.com>
>> Date: Wed, 12 Sep 2012 13:02:42 -0700
>> To: <us...@hive.apache.org>
>> ReplyTo: user@hive.apache.org
>> Subject: most efficient way to concatenate 3 tables into one?
>>
>> I have 3 tables, each contains monthly data, I would like to make a table
>> to concatenate all 3 months into 1 table.
>> What is the most efficient way to do that?
>> I am using insert into which takes a long time.
>>
>> Thanks!
>
>
Re: most efficient way to concatenate 3 tables into one?
Posted by Bejoy KS <be...@yahoo.com>.
Hi
Just follow the steps
1) Create an external table with location as /user/aggregatedTable
2) Move the contents of 3 monthly tables to this location
Hadoop fs -cp /user/monthlyTable1/* /user/aggregatedTable
Hadoop fs -cp /user/monthlyTable2/* /user/aggregatedTable
...
Replace the hsfs dirs in the above commands with those used in your tables.
Regards
Bejoy KS
Sent from handheld, please excuse typos.
-----Original Message-----
From: zuohua zhang <zu...@gmail.com>
Date: Wed, 12 Sep 2012 13:14:20
To: <us...@hive.apache.org>; <be...@yahoo.com>
Reply-To: user@hive.apache.org
Subject: Re: most efficient way to concatenate 3 tables into one?
Thanks Bejoy.
Yes, they have the same schema.
Can you explain further how to? I am new to hive.
On Wed, Sep 12, 2012 at 1:06 PM, Bejoy KS <be...@yahoo.com> wrote:
> **
> Hi
>
> If all the 3 tables have the same. Schema, Create an external table and
> move the data from all the 3 tables to this new table's location. Just a
> hdfs copy or move is not that expensive.
> Regards
> Bejoy KS
>
> Sent from handheld, please excuse typos.
> ------------------------------
> *From: * zuohua zhang <zu...@gmail.com>
> *Date: *Wed, 12 Sep 2012 13:02:42 -0700
> *To: *<us...@hive.apache.org>
> *ReplyTo: * user@hive.apache.org
> *Subject: *most efficient way to concatenate 3 tables into one?
>
> I have 3 tables, each contains monthly data, I would like to make a table
> to concatenate all 3 months into 1 table.
> What is the most efficient way to do that?
> I am using insert into which takes a long time.
>
> Thanks!
>
Re: most efficient way to concatenate 3 tables into one?
Posted by zuohua zhang <zu...@gmail.com>.
Thanks Bejoy.
Yes, they have the same schema.
Can you explain further how to? I am new to hive.
On Wed, Sep 12, 2012 at 1:06 PM, Bejoy KS <be...@yahoo.com> wrote:
> **
> Hi
>
> If all the 3 tables have the same. Schema, Create an external table and
> move the data from all the 3 tables to this new table's location. Just a
> hdfs copy or move is not that expensive.
> Regards
> Bejoy KS
>
> Sent from handheld, please excuse typos.
> ------------------------------
> *From: * zuohua zhang <zu...@gmail.com>
> *Date: *Wed, 12 Sep 2012 13:02:42 -0700
> *To: *<us...@hive.apache.org>
> *ReplyTo: * user@hive.apache.org
> *Subject: *most efficient way to concatenate 3 tables into one?
>
> I have 3 tables, each contains monthly data, I would like to make a table
> to concatenate all 3 months into 1 table.
> What is the most efficient way to do that?
> I am using insert into which takes a long time.
>
> Thanks!
>
Re: most efficient way to concatenate 3 tables into one?
Posted by Bejoy KS <be...@yahoo.com>.
Hi
If all the 3 tables have the same. Schema, Create an external table and move the data from all the 3 tables to this new table's location. Just a hdfs copy or move is not that expensive.
Regards
Bejoy KS
Sent from handheld, please excuse typos.
-----Original Message-----
From: zuohua zhang <zu...@gmail.com>
Date: Wed, 12 Sep 2012 13:02:42
To: <us...@hive.apache.org>
Reply-To: user@hive.apache.org
Subject: most efficient way to concatenate 3 tables into one?
I have 3 tables, each contains monthly data, I would like to make a table
to concatenate all 3 months into 1 table.
What is the most efficient way to do that?
I am using insert into which takes a long time.
Thanks!