Hive Join returning zero records

Question

I have two Hive tables and I am trying to join both of them. The tables are not clustered or partitioned by any field. Though the tables contain records for common key fields, the join query always returns 0 records. All the data types are 'string' data types.

The join query is simple and looks something like below

select count(*) cnt
from
fsr.xref_1 A join
fsr.ipfile_1 B 
on 
(
    A.co_no = B.co_no 
)
;

Any idea what could be going wrong? I have just one record (same value) in both the tables.

Below are my table definitions

CREATE TABLE xref_1 
(
co_no string
)
clustered by (co_no) sorted by (co_no asc) into 10 buckets
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

CREATE TABLE ipfile_1
(
co_no string
)
clustered by (co_no) sorted by (co_no asc) into 10 buckets
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

This should work, can you maybe give a more complete example using table definitions and real data? — jkovacs

Sandeep Singh Sandeep Singh · Accepted Answer · 2013-08-21T17:17:52

Hi You are using Star Schema Join. Please use your query like this:

SELET COUNT(*) cnt FROM A a JOIN B b ON (a.key1 = b.key1);

If still have issue Then use MAPJOIN:

set hive.auto.convert.join=true;
select count(*) from A join B on (key1 = key2)

Please see Link for more detail.

Hive Join returning zero records

1 Answers