HIVE: Both Left and Right Aliases Encountered in Join

Recently was stuck on this error while trying to do a JOIN in HIVE. Below is my SQL
create table ma.internet_v2 as
select A.msisdn, A.imsi, A.dt,
A.start_time, A.end_time, A.url, A.ttl_connection_dur_ms,
A.ttl_upload_bytes, A.ttl_download_bytes, A.ttl_cdr_cnt,
coalesce(B.domain_desc,A.domain_desc) domain_desc,
coalesce(B.subdomain_desc, A.subdomain_desc) subdomain_desc
from
db.internet A
left outer join
hfz_domain_name_mapping B
on instr(A.url, B.url_pattern) > 0;
HIVE then give me and error message "Both Left and Right Aliases Encountered in Join".

After going through some mail threads it seems that HIVE can support certain types of join.
Only equality joins, outer joins, and left semi joins are supported in Hive. Hive does not support join conditions that are not equality conditions as it is very difficult to express such conditions as a map/reduce job.
A poster from Stack Overflow suggested that one should use WHERE instead of ON. Duly following his advice, it seemed to do the trick. Final script is as below.
create table ma.internet_v2 as select A.msisdn, A.imsi, A.dt, A.start_time, A.end_time, A.url, A.ttl_connection_dur_ms,  A.ttl_upload_bytes, A.ttl_download_bytes, A.ttl_cdr_cnt,  coalesce(B.domain_desc,A.domain_desc) domain_desc, coalesce(B.subdomain_desc, A.subdomain_desc) subdomain_desc from db.internet A left outer join hfz_domain_name_mapping B where instr(A.url, B.url_pattern) > 0;
And now to wait for my map reduce to finish. Time to get some coffee :)

Comments

Post a Comment

Popular posts from this blog

Changing Windows Account password using Outlook

http://theessentialexchange.com/blogs/michael/archive/2007/11/24/changing-user-passwords-with-outlook-2003-2007.aspx . 1) Call the helpdesk - the most likely scenario if you are not in a hosted environment 2) Use the control panel - most Hosted Exchange providers provide a function on a user's control panel enabling the user to change their password, using the functionality in item (3). Most large companies will implement a similar capability for their users. Small companies will likely need to implement item (3) directly or their helpdesk will do it (as in item 1). 3) Enable IISADMPWD - use KB 555071 to enable the use of a web page to allow changing the password of an active directory user.
Keep reading

MVNO: Through thick and thin

The below article was taken from Silicon.com. Click here to read the actual article. By Natasha Lomas, 27 October 2008 16:23 MVNO? That'll be an abbreviation - another pointless four letter one no doubt… Right and wrong. MVNO stands for mobile virtual network operator - and it refers to a rather intriguing slice of the mobile pie. I'm listening… Building and operating a mobile network is an expensive business, not least because it requires radio spectrum, so the number of mobile network operators (or MNOs) is limited to, at most, a handful per market. For instance, the UK market has five: 3, O2, Orange, T-Mobile and Vodafone. Spectrum does not come cheap. The auction for 3G licences in the UK - way back in 2000 - ended with five companies shelling out a whopping £22.47bn for the privilege of flogging third-generation services to Brits. So the requirement for large capital expenditure to set up a mobile network goes some way to explaining why actual networks are not 10-a-penny.
Keep reading