Hive and External hbase table join returns null value

[#1]

Hi All,

I have 2 table one a hive table and other a external hbase table, so when i query only the hbase table i am able to see the records, but when i do a left join with hive table then i get null values from hbase.

After some test found, when running 

Select * from external hbase_table whery key='abc%'; 

Gives results , but when running 

Select Key, c1 from external hbase_table whery key='abc%'; 

Gives no rows or no output.

[#2]

Hello @Shivam171 

Thanks for using Cloudera Community. Based on the Post, Your team is receiving no rows while joining 2 Hive Tables. To put things in perspective, You have Table_1 (Hive Managed Table) & Table_2 (Hive External Table referencing HBase Table via HBaseStorageHandler. Both Tables have rows. While fetching rows from Table_2 with a filter condition, we receive Output. While joining the Table_1 with Table_2 with the same filter condition, we receive no Output. 

The 2nd SQL referred by you doesn't seems to use any Join Operation. Please share the SQL for reference. Additionally, Verify if the Joining Column has the required match on both Tables both on any Table-Level filter conditions via WHERE Clause. If there are no match in an Inner Join, the Output would be Zero.

- Smarak 

[#3]

Hey @smdas , thanks for your feedback, i will rephrase it for better understanding.

Hive table name =hive_t1

External hive hbase table name= hb_t2

They both have identical data as per now 100 rows each

Senario 1:

Select t1.c1,t1.c2 from hive_t1 t1 left join(select KEY, c1 from hb_t2 where KEY like 'abc%') t2on (t1.c1=t2.c1);

Here the values which i get from hbase external table are null , but expected results should be having matching values. 

When i was analysing found these results also.

Senario 2:

select KEY, c1 from hb_t2 where KEY like 'abc%'; => returns 0 rows or no result

But if i run this 

select * from hb_t2 where KEY like 'abc%'; => Then i am able to see the data all the 100 rows, not able to understand this behaviour.

-Shivam