You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@hive.apache.org by 曹坤 <ca...@gmail.com> on 2011/08/03 04:59:54 UTC
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！
Re: how to insert data to specified columns
Posted by 曹坤 <ca...@gmail.com>.
Thank you Chinna .
2011/8/3 Chinna <ch...@huawei.com>
> 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:caofangkun@gmail.com]
> *Sent:* Wednesday, August 03, 2011 11:00 AM
> *To:* chinnarao@huawei.com
> *Cc:* dev@hive.apache.org
> *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 <ch...@huawei.com>****
>
>
> 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:caofangkun@gmail.com]
> Sent: Wednesday, August 03, 2011 8:30 AM
> To: dev@hive.apache.org
> 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！
曹 坤
Re: how to insert data to specified columns
Posted by 曹坤 <ca...@gmail.com>.
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 <ch...@huawei.com>
>
> 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:caofangkun@gmail.com]
> Sent: Wednesday, August 03, 2011 8:30 AM
> To: dev@hive.apache.org
> 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！
曹 坤