question Re: OR operator not supported in Impala in Archives of Support Questions (Read Only)

OR operator not supported in Impala

Moazzam — Fri, 16 Sep 2022 09:21:33 GMT

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

Re: OR operator not supported in Impala

alex.behm — Thu, 12 Feb 2015 07:13:08 GMT

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!

Re: OR operator not supported in Impala

Moazzam — Fri, 13 Feb 2015 07:13:34 GMT

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)

Re: OR operator not supported in Impala

alex.behm — Fri, 13 Feb 2015 07:38:43 GMT

Thanks for following up! Glad you solved the issue,