OR operator not supported in Impala

[#1]

Hi,

    I have two tables in impala,in first table one of the column  is mobile number and second table I have country_code and country_name.

   Table events

Column1 Column2 .... CallingNumber        CalledNumber....Columnn

-----------------------------------------------------------------------------

Some Data  ......          917878999777    2347878999777

 Table l_countrycode

Country_Code   Country_Name

------------------------------------------

91                      India

234                    Nigeria

7                        Kazakhastan

....More data

 So I have to join two tables on the basis of country code and country code can be of 1,2,3,and 4 digits.I have to extract country code from the mobile number write the following logic to join two tables

select column1,colum2....

from events e,l_countrycode vcc2
where substring(e.callednumber,1,1)= vcc2.country_code in or substring(e.callednumber,1,2)=vcc2.country_code

or substring(e.callednumber,1,3)=vcc2.country_code or substring(e.callednumber,1,4))=vcc2.country_code

The preceding code does not run,so I try this

select column1,colum2....

from events e,l_countrycode vcc2
where  vcc2.country_code in(substring(e.callednumber,1,1),substring(e.callednumber,1,2),substring(e.callednumber,1,3),
substring(e.callednumber,1,4))

Both query returns error

NotImplementedException: Join between 'e' and 'vcc2' requires at least one conjunctive equality predicate between the two tables.

The only option I left with is to use UNION but the table contains millions of data and it will hinder performance and affect memory. So please help

me how can I achieve my target without UNION as the above query runs fine in MYSQL

[#2]

First, the reason why the query is rejected is because Impala currently has no efficient way to process that join. Impala only implements hash join and disjunctoive conditions (with OR) are not hashable.

That said, since Impala 2.0 we do support executing such queries, albeit inefficiently. They will be run via a CROSS JOIN + filter, so I'd advise against running such queries on very large tables. Earlier Impala versions cannot run your query.

Hope this helps!

[#3 · Accepted solution]

Hi,

  I found an alternative solution

from events e  JOIN l_CountryCode vcc1 on (substring(e.callingnumber,1,1)=substring(vcc1.country_code,1,1)
and substring(e.callingnumber,1,length(vcc1.country_code))=vcc1.country_code)

[#4]

Thanks for following up! Glad you solved the issue,