


most efficient way to concatenate 3 tables into one?
zuohua zhang 20120912, 20:02
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!
+
zuohua zhang 20120912, 20:02

Re: most efficient way to concatenate 3 tables into one?
Bejoy KS 20120912, 20:06
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 <[EMAIL PROTECTED]> Date: Wed, 12 Sep 2012 13:02:42 To: <[EMAIL PROTECTED]> ReplyTo: [EMAIL PROTECTED] 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!
+
Bejoy KS 20120912, 20:06

Re: most efficient way to concatenate 3 tables into one?
zuohua zhang 20120912, 20:14
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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> > *Date: *Wed, 12 Sep 2012 13:02:42 0700 > *To: *<[EMAIL PROTECTED]> > *ReplyTo: * [EMAIL PROTECTED] > *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! >
+
zuohua zhang 20120912, 20:14

Re: most efficient way to concatenate 3 tables into one?
Bejoy KS 20120912, 20:23
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 <[EMAIL PROTECTED]> Date: Wed, 12 Sep 2012 13:14:20 To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> ReplyTo: [EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> > *Date: *Wed, 12 Sep 2012 13:02:42 0700 > *To: *<[EMAIL PROTECTED]> > *ReplyTo: * [EMAIL PROTECTED] > *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! >
+
Bejoy KS 20120912, 20:23

Re: most efficient way to concatenate 3 tables into one?
MiaoMiao 20120913, 01:25
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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> > Date: Wed, 12 Sep 2012 13:14:20 0700 > To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > ReplyTo: [EMAIL PROTECTED] > 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> >> Date: Wed, 12 Sep 2012 13:02:42 0700 >> To: <[EMAIL PROTECTED]> >> ReplyTo: [EMAIL PROTECTED] >> 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! > >
+
MiaoMiao 20120913, 01:25

