hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bing Li (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (HIVE-16907) "INSERT INTO" overwrite old data when destination table encapsulated by backquote
Date Thu, 13 Jul 2017 09:43:00 GMT

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

Bing Li edited comment on HIVE-16907 at 7/13/17 9:42 AM:
---------------------------------------------------------

[~pxiong] and [~lirui], thank you for your comments.
I tried CREATE TABLE statement in MySQL, and found that it treats the `db.tbl` as the table
name. And "dot" is allowed in the table name. 
e.g.

{code:java}
mysql> create table xxx (col int);
mysql> create table test.yyy (col int);
mysql> create table `test.zzz` (col int);
mysql> create table `test.test.tbl` (col int);
mysql> create table test.test.ooo (col int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near ‘.ooo (col int)’ at line
1

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test.test.tbl  |
| test.zzz       |
| xxx            |
| yyy            |
+----------------+
{code}

Back to Hive, if we would like to make it having the same behavior as MySQL, we should change
the logic of processing it.
My previous patch is NOT enough and can't handle `db.db.tbl` neither.


was (Author: libing):
[~pxiong] and [~lirui], thank you for your comments.
I tried CREATE TABLE statement in MySQL, and found that it treats the `db.tbl` as the table
name. And "dot" is allowed in the table name. 
e.g.

{code:java}
mysql> create table xxx (col int);
mysql> create table test.yyy (col int);
mysql> create table `test.zzz` (col int);
mysql> create table `test.test.tbl` (col int);

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test.test.tbl  |
| test.zzz       |
| xxx            |
| yyy            |
+----------------+
{code}

Back to Hive, if we would like to make it having the same behavior as MySQL, we should change
the logic of processing it.
My previous patch is NOT enough and can't handle `db.db.tbl` neither.

>  "INSERT INTO"  overwrite old data when destination table encapsulated by backquote 
> ------------------------------------------------------------------------------------
>
>                 Key: HIVE-16907
>                 URL: https://issues.apache.org/jira/browse/HIVE-16907
>             Project: Hive
>          Issue Type: Bug
>          Components: Parser
>    Affects Versions: 1.1.0, 2.1.1
>            Reporter: Nemon Lou
>            Assignee: Bing Li
>         Attachments: HIVE-16907.1.patch
>
>
> A way to reproduce:
> {noformat}
> create database tdb;
> use tdb;
> create table t1(id int);
> create table t2(id int);
> explain insert into `tdb.t1` select * from t2;
> {noformat}
> {noformat}
> +---------------------------------------------------------------------------------------------------------------------------------------------------+
> |                                                                      Explain      
                                                               |
> +---------------------------------------------------------------------------------------------------------------------------------------------------+
> | STAGE DEPENDENCIES:                                                               
                                                               |
> |   Stage-1 is a root stage                                                         
                                                               |
> |   Stage-6 depends on stages: Stage-1 , consists of Stage-3, Stage-2, Stage-4      
                                                               |
> |   Stage-3                                                                         
                                                               |
> |   Stage-0 depends on stages: Stage-3, Stage-2, Stage-5                            
                                                               |
> |   Stage-2                                                                         
                                                               |
> |   Stage-4                                                                         
                                                               |
> |   Stage-5 depends on stages: Stage-4                                              
                                                               |
> |                                                                                   
                                                               |
> | STAGE PLANS:                                                                      
                                                               |
> |   Stage: Stage-1                                                                  
                                                               |
> |     Map Reduce                                                                    
                                                               |
> |       Map Operator Tree:                                                          
                                                               |
> |           TableScan                                                               
                                                               |
> |             alias: t2                                                             
                                                               |
> |             Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
                                                            |
> |             Select Operator                                                       
                                                               |
> |               expressions: id (type: int)                                         
                                                               |
> |               outputColumnNames: _col0                                            
                                                               |
> |               Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats:
NONE                                                           |
> |               File Output Operator                                                
                                                               |
> |                 compressed: false                                                 
                                                               |
> |                 Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats:
NONE                                                         |
> |                 table:                                                            
                                                               |
> |                     input format: org.apache.hadoop.hive.ql.io.RCFileInputFormat  
                                                               |
> |                     output format: org.apache.hadoop.hive.ql.io.RCFileOutputFormat
                                                               |
> |                     serde: org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe   
                                                               |
> |                     name: tdb.t1                                                  
                                                               |
> |                                                                                   
                                                               |
> |   Stage: Stage-6                                                                  
                                                               |
> |     Conditional Operator                                                          
                                                               |
> |                                                                                   
                                                               |
> |   Stage: Stage-3                                                                  
                                                               |
> |     Move Operator                                                                 
                                                               |
> |       files:                                                                      
                                                               |
> |           hdfs directory: true                                                    
                                                               |
> |           destination: hdfs://hacluster/user/hive/warehouse/tdb.db/t1/.hive-staging_hive_2017-06-15_15-52-34_017_849305017872068583-1/-ext-10000
 |
> |                                                                                   
                                                               |
> |   Stage: Stage-0                                                                  
                                                               |
> |     Move Operator                                                                 
                                                               |
> |       tables:                                                                     
                                                               |
> |           replace: true                                                           
                                                               |
> |           table:                                                                  
                                                               |
> |               input format: org.apache.hadoop.hive.ql.io.RCFileInputFormat        
                                                               |
> |               output format: org.apache.hadoop.hive.ql.io.RCFileOutputFormat      
                                                               |
> |               serde: org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe         
                                                               |
> |               name: tdb.t1                                                        
                                                               |
> |                                                                                   
                                                               |
> |   Stage: Stage-2                                                                  
                                                               |
> |     Merge File Operator                                                           
                                                               |
> |       Map Operator Tree:                                                          
                                                               |
> |           RCFile Merge Operator                                                   
                                                               |
> |       merge level: block                                                          
                                                               |
> |       input format: org.apache.hadoop.hive.ql.io.RCFileInputFormat                
                                                               |
> |                                                                                   
                                                               |
> |   Stage: Stage-4                                                                  
                                                               |
> |     Merge File Operator                                                           
                                                               |
> |       Map Operator Tree:                                                          
                                                               |
> |           RCFile Merge Operator                                                   
                                                               |
> |       merge level: block                                                          
                                                               |
> |       input format: org.apache.hadoop.hive.ql.io.RCFileInputFormat                
                                                               |
> |                                                                                   
                                                               |
> |   Stage: Stage-5                                                                  
                                                               |
> |     Move Operator                                                                 
                                                               |
> |       files:                                                                      
                                                               |
> |           hdfs directory: true                                                    
                                                               |
> |           destination: hdfs://hacluster/user/hive/warehouse/tdb.db/t1/.hive-staging_hive_2017-06-15_15-52-34_017_849305017872068583-1/-ext-10000
 |
> |                                                                                   
                                                               |
> +---------------------------------------------------------------------------------------------------------------------------------------------------+
> {noformat}
> Note that  'replace: true'  in move operator



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

Mime
View raw message