Thank you Chinna .
2011/8/3 Chinna <[email protected]> > hi,**** > > ** ** > > INSERT OVERWRITE TABLE target_table SELECT s1, s2 FROM source_table;**** > > ** ** > > This query also wont work because target table have 4 columns and source > table have only 2 columns. **** > > To explain I am changing the query like this **** > > ** ** > > INSERT OVERWRITE TABLE source_table SELECT t1, t3 FROM target_table;**** > > ** ** > > The above query will be executed as below steps **** > > 1. First it will execute the sub query (SELECT t1, t3 FROM target_table) > and it will store the out put as file in the HDFS location**** > > 2. From the HDFS location it will move to source_table location.**** > > ** ** > > We can see the query plan using "explain"**** > > "EXPLAIN INSERT OVERWRITE TABLE source_table SELECT t1, t3 FROM > target_table;"**** > > ** ** > > -Chinna Rao Lalam**** > > ** ** > ------------------------------ > > *From:* 曹坤 [mailto:[email protected]] > *Sent:* Wednesday, August 03, 2011 11:00 AM > *To:* [email protected] > *Cc:* [email protected] > *Subject:* Re: how to insert data to specified columns**** > > ** ** > > Chinna :**** > > Thanks for your replay**** > > I also do not understand why.**** > > Could you please give me a more detailed explanation?**** > > ** ** > > *Example:***** > > ________________________________**** > > CREATE TABLE source_table (s1 INT, s2 SRRING);**** > > SELECT * FORM source_table;**** > > s1 s2**** > > 10 tom**** > > 11 jack**** > > __________________________________**** > > CREATE TABLE target_table (t1 INT, t2 INT, t3 STRING, t4 STRING);**** > > SELECT * FROM target_table;**** > > t1 t2 t3 t4**** > > 1 1 bob may**** > > ___________________________________**** > > INSERT OVERWRITE TABLE target_table (t2,t4) SELECT s1, s2 FROM > source_table;**** > > SELECT * FROM target_table;**** > > t1 t2 t3 t4**** > > 1 1 bob may**** > > NULL 10 NULL tom**** > > NULL 11 NULL jack**** > > ** ** > > ** ** > > 2011/8/3 Chinna <[email protected]>**** > > > I think we cannot do this directly by specify the particular target columns > in the insert query because it is dealing with the files. > > And the syntax is > > syntax: > INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 > ...)] select_statement1 FROM from_statement; > INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] > select_statement1 FROM from_statement; > > -Chinna Rao Lalam**** > > > -----Original Message----- > From: 曹坤 [mailto:[email protected]] > Sent: Wednesday, August 03, 2011 8:30 AM > To: [email protected] > Subject: how to insert data to specified columns > > Hello : > Any idea about how to insert data to specified columns? > In a way like follow : > > INSERT OVERWRITE TABLE target_table > PARTITION (day='20110803' , hour='11') > (target_table_column1,target_table_column2) > SELECT source_table_column1, source_table_column2 > FROM source_table; > > -- > Best wishs！**** > > > > > -- > Best wishs！**** > > 曹 坤**** > > ** ** > -- Best wishs！ 曹 坤
