hive-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From 曹坤 <caofang...@gmail.com>
Subject Re: how to insert data to specified columns
Date Wed, 03 Aug 2011 08:55:45 GMT
Thank you Chinna .


2011/8/3 Chinna <chinnarao@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 <chinnarao@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!
曹 坤

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message