[
https://issues.apache.org/jira/browse/HIVE-20206?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Venkata updated HIVE-20206:
---------------------------
Description:
We are running this in Cloudera cdh5.13.3 version.
Hive version - 1.1.0-cdh5.13.3
Hbase version - 1.2.0-cdh5.13.3
When running hive union all query with two views created on the same hive
*external* hbase table, the query is returning incorrect results.
The query is returning correct results if *set hive.optimize.ppd=false;*
We don't want to turn off the ppd as it will greatly impact performance.
Note: The union all is working fine for the views created on the same Hive
*Managed* table.
The results marked in {color:#FF0000}red{color} are wrong results.
The below are the queries ( i have attached the same queries as an attachment):
*HBASE:*
create '*test*','default'
put 'test','111','default:name','john1'
put 'test','111','default:dept','hr1'
put 'test','111','default:type','a'
put 'test','112','default:name','rambo1'
put 'test','112','default:dept','eng1'
put 'test','112','default:type','a'
put 'test','113','default:name','alex1'
put 'test','113','default:dept','dev1'
put 'test','113','default:type','a'
put 'test','211','default:name','john2'
put 'test','211','default:dept','hr2'
put 'test','211','default:type','b'
put 'test','212','default:name','rambo2'
put 'test','212','default:dept','eng2'
put 'test','212','default:type','b'
put 'test','213','default:name','alex2'
put 'test','213','default:dept','dev2'
put 'test','213','default:type','b'
put 'test','311','default:name','john3'
put 'test','311','default:dept','hr3'
put 'test','311','default:type','c'
put 'test','312','default:name','rambo3'
put 'test','312','default:dept','eng3'
put 'test','312','default:type','c'
put 'test','313','default:name','alex3'
put 'test','313','default:dept','dev3'
put 'test','313','default:type','c'
################################
*BEELINE*
CREATE external TABLE *hbase_ext_table*(id string, name string,dept string,type
string) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH
SERDEPROPERTIES ("hbase.columns.mapping" =
":key,default:name,default:dept,default:type") TBLPROPERTIES
("hbase.table.name" = "*test*");
create view *view1* as select id ,name,dept,type from *hbase_ext_table* where
id>='1' and id<'2' and type is not null;
create view *view2* as select id,name,dept,type from *hbase_ext_table* where
id>='2' and id<'3' and type is not null;
> select * from view1;
+------------------+------------------++--------------------------------------++--
|view1.id|view1.name|view1.dept|view1.type|
+------------------+------------------++--------------------------------------++--
|111|john1|hr1|a|
|112|rambo1|eng1|a|
|113|alex1|dev1|a|
+------------------+------------------++--------------------------------------++--
> select * from view2;
+------------------+------------------++--------------------------------------++--
|view2.id|view2.name|view2.dept|view2.type|
+------------------+------------------++--------------------------------------++--
|211|john2|hr2|b|
|212|rambo2|eng2|b|
|213|alex2|dev2|b|
+------------------+------------------++--------------------------------------++--
> select id,name,dept,type from *view1* union all select id,name,dept,type from
> *view2*;
+----------+----------++----------------------++--
|_u1.id|_u1.name|_u1.dept|_u1.type|
+----------+----------++----------------------++--
|111|john1|hr1|a|
|111|john1|hr1|a|
|112|rambo1|eng1|a|
|112|rambo1|eng1|a|
|113|alex1|dev1|a|
|113|alex1|dev1|a|
|211|john2|hr2|b|
|211|john2|hr2|b|
|212|rambo2|eng2|b|
|212|rambo2|eng2|b|
|213|alex2|dev2|b|
|213|alex2|dev2|b|
|{color:#ff0000}*311 \| john3 \| hr3 \| c \|*{color}
{color:#ff0000}*\| 311 \| john3 \| hr3 \| c \|*{color}
{color:#ff0000}*\| 312 \| rambo3 \| eng3 \| c \|*{color}
{color:#ff0000}*\| 312 \| rambo3 \| eng3 \| c \|*{color}
{color:#ff0000}*\| 313 \| alex3 \| dev3 \| c \|*{color}
{color:#ff0000}*\| 313 \| alex3 \| dev3 \| c \|*{color}
+----------+----------++----------------------++--|
was:
We are running this in Cloudera cdh5.13.3 version.
Hive version - 1.1.0-cdh5.13.3
Hbase version - 1.2.0-cdh5.13.3
When running hive union all query with two views created on the same hive
*external* hbase table, the query is returning incorrect results.
The query is returning correct results if *set hive.optimize.ppd=false;*
We don't want to turn off the ppd as it will greatly impact performance.
Note: The union all is working fine for the views created on the same Hive
*Managed* table.
The below are the queries ( i have attached the same queries as an attachment):
*HBASE:*
create '*test*','default'
put 'test','111','default:name','john1'
put 'test','111','default:dept','hr1'
put 'test','111','default:type','a'
put 'test','112','default:name','rambo1'
put 'test','112','default:dept','eng1'
put 'test','112','default:type','a'
put 'test','113','default:name','alex1'
put 'test','113','default:dept','dev1'
put 'test','113','default:type','a'
put 'test','211','default:name','john2'
put 'test','211','default:dept','hr2'
put 'test','211','default:type','b'
put 'test','212','default:name','rambo2'
put 'test','212','default:dept','eng2'
put 'test','212','default:type','b'
put 'test','213','default:name','alex2'
put 'test','213','default:dept','dev2'
put 'test','213','default:type','b'
put 'test','311','default:name','john3'
put 'test','311','default:dept','hr3'
put 'test','311','default:type','c'
put 'test','312','default:name','rambo3'
put 'test','312','default:dept','eng3'
put 'test','312','default:type','c'
put 'test','313','default:name','alex3'
put 'test','313','default:dept','dev3'
put 'test','313','default:type','c'
################################
*BEELINE*
CREATE external TABLE *hbase_ext_table*(id string, name string,dept string,type
string) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH
SERDEPROPERTIES ("hbase.columns.mapping" =
":key,default:name,default:dept,default:type") TBLPROPERTIES
("hbase.table.name" = "*test*");
create view *view1* as select id ,name,dept,type from *hbase_ext_table* where
id>='1' and id<'2' and type is not null;
create view *view2* as select id,name,dept,type from *hbase_ext_table* where
id>='2' and id<'3' and type is not null;
> select * from view1;
+-----------------+-------------------+-------------------+-------------------+--+
| view1.id | view1.name | view1.dept | view1.type |
+-----------------+-------------------+-------------------+-------------------+--+
| 111 | john1 | hr1 | a |
| 112 | rambo1 | eng1 | a |
| 113 | alex1 | dev1 | a |
+-----------------+-------------------+-------------------+-------------------+--+
> select * from view2;
+-----------------+-------------------+-------------------+-------------------+--+
| view2.id | view2.name | view2.dept | view2.type |
+-----------------+-------------------+-------------------+-------------------+--+
| 211 | john2 | hr2 | b |
| 212 | rambo2 | eng2 | b |
| 213 | alex2 | dev2 | b |
+-----------------+-------------------+-------------------+-------------------+--+
> select id,name,dept,type from *view1* union all select id,name,dept,type from
> *view2*;
+---------+-----------+-----------+-----------+--+
| _u1.id | _u1.name | _u1.dept | _u1.type |
+---------+-----------+-----------+-----------+--+
| 111 | john1 | hr1 | a |
| 111 | john1 | hr1 | a |
| 112 | rambo1 | eng1 | a |
| 112 | rambo1 | eng1 | a |
| 113 | alex1 | dev1 | a |
| 113 | alex1 | dev1 | a |
| 211 | john2 | hr2 | b |
| 211 | john2 | hr2 | b |
| 212 | rambo2 | eng2 | b |
| 212 | rambo2 | eng2 | b |
| 213 | alex2 | dev2 | b |
| 213 | alex2 | dev2 | b |
| {color:#FF0000}*311 | john3 | hr3 | c |*{color}
{color:#FF0000}*| 311 | john3 | hr3 | c |*{color}
{color:#FF0000}*| 312 | rambo3 | eng3 | c |*{color}
{color:#FF0000}*| 312 | rambo3 | eng3 | c |*{color}
{color:#FF0000}*| 313 | alex3 | dev3 | c |*{color}
{color:#FF0000}*| 313 | alex3 | dev3 | c |*{color}
+---------+-----------+-----------+-----------+--+
> Hive Union all query with two views on the same hbase external table
> producing incorrect results
> ------------------------------------------------------------------------------------------------
>
> Key: HIVE-20206
> URL: https://issues.apache.org/jira/browse/HIVE-20206
> Project: Hive
> Issue Type: Bug
> Components: Beeline
> Affects Versions: 1.1.0
> Reporter: Venkata
> Priority: Major
> Attachments: hive_view_union_all.txt
>
>
> We are running this in Cloudera cdh5.13.3 version.
> Hive version - 1.1.0-cdh5.13.3
> Hbase version - 1.2.0-cdh5.13.3
> When running hive union all query with two views created on the same hive
> *external* hbase table, the query is returning incorrect results.
> The query is returning correct results if *set hive.optimize.ppd=false;*
> We don't want to turn off the ppd as it will greatly impact performance.
> Note: The union all is working fine for the views created on the same Hive
> *Managed* table.
> The results marked in {color:#FF0000}red{color} are wrong results.
>
> The below are the queries ( i have attached the same queries as an
> attachment):
> *HBASE:*
> create '*test*','default'
> put 'test','111','default:name','john1'
> put 'test','111','default:dept','hr1'
> put 'test','111','default:type','a'
> put 'test','112','default:name','rambo1'
> put 'test','112','default:dept','eng1'
> put 'test','112','default:type','a'
> put 'test','113','default:name','alex1'
> put 'test','113','default:dept','dev1'
> put 'test','113','default:type','a'
> put 'test','211','default:name','john2'
> put 'test','211','default:dept','hr2'
> put 'test','211','default:type','b'
> put 'test','212','default:name','rambo2'
> put 'test','212','default:dept','eng2'
> put 'test','212','default:type','b'
> put 'test','213','default:name','alex2'
> put 'test','213','default:dept','dev2'
> put 'test','213','default:type','b'
> put 'test','311','default:name','john3'
> put 'test','311','default:dept','hr3'
> put 'test','311','default:type','c'
> put 'test','312','default:name','rambo3'
> put 'test','312','default:dept','eng3'
> put 'test','312','default:type','c'
> put 'test','313','default:name','alex3'
> put 'test','313','default:dept','dev3'
> put 'test','313','default:type','c'
> ################################
> *BEELINE*
> CREATE external TABLE *hbase_ext_table*(id string, name string,dept
> string,type string) STORED BY
> 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES
> ("hbase.columns.mapping" = ":key,default:name,default:dept,default:type")
> TBLPROPERTIES ("hbase.table.name" = "*test*");
> create view *view1* as select id ,name,dept,type from *hbase_ext_table* where
> id>='1' and id<'2' and type is not null;
> create view *view2* as select id,name,dept,type from *hbase_ext_table* where
> id>='2' and id<'3' and type is not null;
> > select * from view1;
>
> +------------------+------------------++--------------------------------------++--
> |view1.id|view1.name|view1.dept|view1.type|
> +------------------+------------------++--------------------------------------++--
> |111|john1|hr1|a|
> |112|rambo1|eng1|a|
> |113|alex1|dev1|a|
> +------------------+------------------++--------------------------------------++--
> > select * from view2;
>
> +------------------+------------------++--------------------------------------++--
> |view2.id|view2.name|view2.dept|view2.type|
> +------------------+------------------++--------------------------------------++--
> |211|john2|hr2|b|
> |212|rambo2|eng2|b|
> |213|alex2|dev2|b|
> +------------------+------------------++--------------------------------------++--
> > select id,name,dept,type from *view1* union all select id,name,dept,type
> > from *view2*;
> +----------+----------++----------------------++--
> |_u1.id|_u1.name|_u1.dept|_u1.type|
> +----------+----------++----------------------++--
> |111|john1|hr1|a|
> |111|john1|hr1|a|
> |112|rambo1|eng1|a|
> |112|rambo1|eng1|a|
> |113|alex1|dev1|a|
> |113|alex1|dev1|a|
> |211|john2|hr2|b|
> |211|john2|hr2|b|
> |212|rambo2|eng2|b|
> |212|rambo2|eng2|b|
> |213|alex2|dev2|b|
> |213|alex2|dev2|b|
> |{color:#ff0000}*311 \| john3 \| hr3 \| c \|*{color}
> {color:#ff0000}*\| 311 \| john3 \| hr3 \| c \|*{color}
> {color:#ff0000}*\| 312 \| rambo3 \| eng3 \| c \|*{color}
> {color:#ff0000}*\| 312 \| rambo3 \| eng3 \| c \|*{color}
> {color:#ff0000}*\| 313 \| alex3 \| dev3 \| c \|*{color}
> {color:#ff0000}*\| 313 \| alex3 \| dev3 \| c \|*{color}
> +----------+----------++----------------------++--|
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
