Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-20206

Hive Union all query with two views on the same hbase external table producing incorrect results

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 1.1.0
    • Fix Version/s: None
    • Component/s: Beeline
    • Labels:
      None

      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 red 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
      311 | john3 | hr3 | c |
      | 311 | john3 | hr3 | c |
      | 312 | rambo3 | eng3 | c |
      | 312 | rambo3 | eng3 | c |
      | 313 | alex3 | dev3 | c |
      | 313 | alex3 | dev3 | c |
      -----------------+--------------------+-

        Attachments

        1. hive_view_union_all.txt
          4 kB
          Venkata

          Activity

            People

            • Assignee:
              afan Alice Fan
              Reporter:
              tvvraju Venkata
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: