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！ 曹 坤
