CODEWITHSUNDEEP
joinhadoophive
Sreeram V
Sreeram V

Reputation: 3

Hive Join returning zero records

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;

Upvotes: 0

Views: 2085

Answers (1)

Sandeep Singh
Sandeep Singh

Reputation: 7990

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.

Upvotes: 0

Related Questions