[jira] [Commented] (HIVE-3939) INSERT INTO behaves like INSERT OVERWRITE if the table name referred is not all lowercase

["Dave Winters (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/HIVE-3939?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13835956#comment-13835956 ] 

Dave Winters commented on HIVE-3939:
------------------------------------

Another case of Insert-Into-Select acting like overwrite.

This case DOES NOT work:

INSERT INTO TABLE marketing.omniture PARTITION( p_country, p_date, p_hour) 
SELECT *,hash(INPUT__FILE__NAME), country, SUBSTR(date_time,1,10), SUBSTR(date_time,12,2) FROM marketing.ingest;

This case works:

use marketing;
INSERT INTO TABLE omniture PARTITION( p_country, p_date, p_hour) 
SELECT *,hash(INPUT__FILE__NAME), country, SUBSTR(date_time,1,10), SUBSTR(date_time,12,2) FROM ingest;


Conclusion:
Table names must be simple and lower case ONLY.

> INSERT INTO behaves like INSERT OVERWRITE if the table name referred is not all lowercase
> -----------------------------------------------------------------------------------------
>
>                 Key: HIVE-3939
>                 URL: https://issues.apache.org/jira/browse/HIVE-3939
>             Project: Hive
>          Issue Type: Bug
>          Components: Database/Schema
>    Affects Versions: 0.9.0
>         Environment: Windows 2012, HDInsight
>            Reporter: mohan dharmarajan
>
> If table referred does not use all lowercase in INSERT INTO command, the data is not appended but overwritten.
> set hive.exec.dynamic.partition.mode=nonstrict;
> set hive.exec.dynamic.partition=true;
> CREATE TABLE test (key int, value string) PARTITIONED BY (ds string);
> SELECT * FROM test;
> INSERT INTO TABLE test  PARTITION (ds) SELECT key, value, value FROM src;
> SELECT * FROM test;
> The following statement works as expected. The data from src is appended to test
> SELECT * FROM test;
> INSERT INTO TABLE test  PARTITION (ds) SELECT key, value, value FROM src;
> SELECT * FROM test;
> The following is copied from the processing log
> Loading data to table default.test partition (ds=null)
>         Loading partition {ds=1}
>         Loading partition {ds=2}
> The following statement does not work. Note the table name referred as Test (not test). INSERT INTO behaves like INSERT OVERWRITE
> SELECT * FROM test;
> INSERT INTO TABLE Test  PARTITION (ds) SELECT key, value, value FROM src;
> SELECT * FROM test;
> The following is copied from the processing log
> Loading data to table default.test partition (ds=null)
> Moved to trash: hdfs://localhost:8020/hive/warehouse/test/ds=1
> Moved to trash: hdfs://localhost:8020/hive/warehouse/test/ds=2
>         Loading partition {ds=1}
>         Loading partition {ds=2}



--
This message was sent by Atlassian JIRA
(v6.1#6144)